Dods: Re: How to dynamically create db tables with DODS?
"Eric Sawler" <[email protected]> Sat, 29 Nov 2003 15:45:33 -0400
| Newsgroups | gmane.comp.java.enhydra.dods |
|---|---|
| Message-ID | <[email protected]> |
This is a multi-part message in MIME format. ------=_NextPart_000_0014_01C3B68F.D30059E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Could you not just generate the .doml file dynamically, and then invoke = the dods build class directly, followed by using the createtables sql to create the table in the db? We do something similarly (at build time) using struts templates and ant = to generate a default biz layer, dods db layer, and Castor presentation = layer to get a prototype app running quickly. Eric ----- Original Message -----=20 From: Mathieu MANGEOT-NAGATA=20 To: [email protected]=20 Cc: Mathieu MANGEOT-NAGATA ; Gilles Serasset=20 Sent: Wednesday, November 26, 2003 11:20 PM Subject: Dods: How to dynamically create db tables with DODS? Dear list members, We are working with DODS/Enhydra for about 3 years now. Our website can be viewed at: http://www.papillon-dictionary.org We began the project with Enhydra 3.1and DODS 3.1. Then we moved to Enhydra 5.0, while keeping DODS 3.1. Our next step would be t use enhydra 5.1 with DODS 5.1 but we have a = small adaptation problem: When we started the project, we were not specialists of the jdbc and = communication with databases in general. Thus, we relied entirely on the DODS generated files for = the data layer. Then the project got complicated and I had to create dynamically = database tables and query them. I did not find a way to generate appropriate code with DODS so I = hacked the DODS output in order to generate dynamically these tables. Our website is managing dictionaries. We create one table for each = dictionary volume and we can dynamically=20 add a dictionary on the server. First, I generated a template table (VolumeEntry) that I clone = afterwards. I hacked the DO class code by using a variable for the name of the table. Then, I must give the name of the table as = an argument when I call the DO methods: public class VolumeEntryDO extends = com.lutris.dods.builder.generator.dataobject.GenericDO implements = VolumeEntryDOI, java.io.Serializable { /** * I added a variable for the name of the table :=20 */ protected String dbtablename; public String getTableName() { return dbtablename; } public void setTableName(String name) { dbtablename =3D name; } /** * static final RDBColumn PrimaryKey for use with QueryBuilder. * See example above. * Then I give the name of the table as a parameter for the methods: */ public static RDBColumn getPrimaryKey(String tablename) { RDBTable table =3D new RDBTable(tablename); return new RDBColumn( table, getPrimaryKeyName() ); } I hacked also the Query class code by giving the table name = (tablename) as a parameter for the constructor: public class VolumeEntryQuery implements Query { private QueryBuilder builder; private String dbtablename; /** * Public constructor. */ public VolumeEntryQuery(String tablename) { dbtablename =3D tablename; builder =3D new QueryBuilder( tablename, tablename + ".*" ); builder.setDatabaseVendor( "PostgreSQL" ); builder.setStringMatchDetails( "LIKE", "%" ); reset(); } Then I created code from the .sql generated files in order to = dynamically create new tables: try { myDbConnection =3D Enhydra.getDatabaseManager().allocateConnection(); myDbConnection.execute("CREATE TABLE" + tablename + " (" + "headword TEXT DEFAULT '\'''\'' ," + "id VARCHAR(255) DEFAULT '\'''\'' ," + "pos VARCHAR(255) DEFAULT '\'''\'' ," + "pronunciation VARCHAR(255) DEFAULT '\'''\'' ," + "reading VARCHAR(255) DEFAULT '\'''\'' ," + "translation TEXT DEFAULT '\'''\'' ," + "key1 VARCHAR(255) DEFAULT '\'''\'' ," + "key2 VARCHAR(255) DEFAULT '\'''\'' ," + "xmlCode TEXT DEFAULT '\'''\'' NOT NULL ," + "ObjectId DECIMAL(19,0) NOT NULL PRIMARY KEY," + "ObjectVersion INTEGER NOT NULL)"; } catch(SQLException se) { se.printStackTrace(); } So far, we had no problem with these code modifications and we were = pleased with that. But the problem comes from the modifications of DODS from the 3.1 = version to the new 5.1 version. The new features: DO LRU caching, Query LRU caching, cache = initialization=20 complicate very much the code generated by DODS and now, it is almost = impossible to hack the new DO and Query codes in order to generate tables dynamically. Thus we are sticked with the = old version of DODS. Here are my questions then: 1) Is there an option in the new DODS that allows one to generate code = for dynamically creating database tables? I maybe did not read carefully the doc. 2) If not, is there anyone who developped his/her own solution for = dynamically creating database tables? 3) Is the DODS team interested in such a feature for a next release in = DODS? In this case, we may try to patch the existing DODS with the help of DODS team and then integrate this patch as an = option in DODS for dynamically creating dbtables. 4) If nothing is possible, is there a way to use the old DODS (3.1) = with the new Enhydra (5.1) Thank you for your answers, Mathieu ------=_NextPart_000_0014_01C3B68F.D30059E0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT face=3DArial size=3D2>Could you not just generate the .doml = file=20 dynamically, and then invoke the dods build class directly,</FONT></DIV> <DIV><FONT face=3DArial size=3D2>followed by using the createtables sql = to create=20 the table in the db?</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>We do something similarly (at build = time) using=20 struts templates and ant to generate a default biz layer, dods db layer, = and=20 Castor presentation layer to get a prototype app running=20 quickly.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Eric</FONT></DIV> <BLOCKQUOTE=20 style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; = BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px"> <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV> <DIV=20 style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: = black"><B>From:</B>=20 <A [email protected] href=3D"mailto:[email protected]">Mathieu = MANGEOT-NAGATA</A> </DIV> <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A [email protected] = href=3D"mailto:[email protected]">[email protected]</A> </DIV> <DIV style=3D"FONT: 10pt arial"><B>Cc:</B> <A = [email protected]=20 href=3D"mailto:[email protected]">Mathieu MANGEOT-NAGATA</A> ; <A=20 [email protected] = href=3D"mailto:[email protected]">Gilles=20 Serasset</A> </DIV> <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Wednesday, November 26, = 2003 11:20=20 PM</DIV> <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> Dods: How to = dynamically create=20 db tables with DODS?</DIV> <DIV><BR></DIV><BR>Dear list members,<BR><BR>We are working with = DODS/Enhydra=20 for about 3 years now.<BR>Our website can be viewed at: <A=20 = href=3D"http://www.papillon-dictionary.org">http://www.papillon-dictionar= y.org</A><BR><BR>We=20 began the project with Enhydra 3.1and DODS 3.1.<BR>Then we moved to = Enhydra=20 5.0, while keeping DODS 3.1.<BR><BR>Our next step would be t use = enhydra 5.1=20 with DODS 5.1 but we have a small<BR>adaptation problem:<BR>When we = started=20 the project, we were not specialists of the jdbc and communication = with=20 databases<BR>in general. Thus, we relied entirely on the DODS = generated files=20 for the data layer.<BR>Then the project got complicated and I had to = create=20 dynamically database tables and query them.<BR>I did not find a way to = generate appropriate code with DODS so I hacked the DODS output in = order<BR>to=20 generate dynamically these tables.<BR>Our website is managing = dictionaries. We=20 create one table for each dictionary volume and we can dynamically = <BR>add a=20 dictionary on the server.<BR><BR>First, I generated a template table=20 (VolumeEntry) that I clone afterwards. I hacked the DO class code by = using a=20 variable<BR>for the name of the table. Then, I must give the name of = the table=20 as an argument when I call the DO = methods:<BR><BR><TT><?smaller><?color><?param = 7676,0F0F,5050>public<?/color> <?color><?param = 7676,0F0F,5050>class<?/color> VolumeEntryDO <?color><?param = 7676,0F0F,5050>extends<?/color>=20 com.lutris.dods.builder.generator.dataobject.GenericDO <?color><?param = 7676,0F0F,5050>implements<?/color> VolumeEntryDOI,=20 java.io.Serializable {<BR><BR><?color><?param = 2322,6E6D,2524>/**<BR><?/color><?color><?param 2321,6E6C,2523>*<?/color> = I added a variable for the name of the table :=20 <?color><?param 2322,6E6D,2524><BR>*/<?/color><BR><?color><?param = 7676,0F0F,5050>protected<?/color>=20 String dbtablename;<BR><BR><?color><?param = 7676,0F0F,5050>public<?/color>=20 String getTableName() {<BR><?color><?param = 7676,0F0F,5050>return<?/color>=20 dbtablename;<BR>}<BR><BR><?color><?param = 7676,0F0F,5050>public<?/color> <?color><?param = 7676,0F0F,5050>void<?/color> setTableName(String name)=20 {<BR>dbtablename =3D=20 name;<BR>}<BR><BR><BR><?color><?param 2323,6E6E,2525>/**<BR>* static = final=20 RDBColumn PrimaryKey for use with QueryBuilder.<BR>* See example = above.<BR><?/color><?color><?param 2322,6E6D,2524>* Then I give the name = of=20 the table as a parameter for the methods:<?/color><?color><?param = 2323,6E6E,2525><BR>*/<?/color><BR><?color><?param = 7676,0F0F,5050>public<?/color>=20 <?color><?param 7676,0F0F,5050>static<?/color> RDBColumn = getPrimaryKey(String=20 tablename) {<BR>RDBTable table =3D <?color><?param = 7676,0F0F,5050>new<?/color>=20 RDBTable(tablename);<BR><?color><?param 7676,0F0F,5050>return<?/color> = <?color><?param 7676,0F0F,5050>new<?/color> RDBColumn(=20 table,<BR>getPrimaryKeyName() );<BR><BR>}<BR><?/smaller></TT><BR>I = hacked also=20 the Query class code by giving the table name = (<TT><?smaller>tablename<?/smaller></TT>) as a parameter for the=20 constructor:<BR><BR><TT><?color><?param = 7676,0F0F,5050><?smaller>public<?/smaller><?/color><?smaller>=20 <?color><?param 7676,0F0F,5050>class<?/color> VolumeEntryQuery = <?color><?param 7676,0F0F,5050>implements<?/color> Query = {<BR><BR><BR><?color><?param 7676,0F0F,5050>private<?/color> = QueryBuilder=20 builder;<BR><?color><?param 7676,0F0F,5050>private<?/color> String=20 dbtablename;<BR><BR><?color><?param 2323,6E6E,2525>/**<BR>* Public=20 constructor.<BR>*/<?/color><BR><?color><?param = 7676,0F0F,5050>public<?/color>=20 VolumeEntryQuery(String tablename) {<BR>dbtablename =3D = tablename;<BR>builder =3D <?color><?param 7676,0F0F,5050>new<?/color> = QueryBuilder( tablename, tablename=20 + <?color><?param 8989,1313,1515>".*"<?/color>=20 );<BR>builder.setDatabaseVendor( <?color><?param = 8989,1313,1515>"PostgreSQL"<?/color>=20 );<BR>builder.setStringMatchDetails( <?color><?param = 8989,1313,1515>"LIKE"<?/color>, <?color><?param = 8989,1313,1515>"%"<?/color> = );<BR>reset();<BR>}<BR><BR><?/smaller></TT>Then I created code from the = .sql=20 generated files in order to dynamically create new = tables:<BR><TT><?smaller><?color><?param 7676,0F0F,5050>try<?/color> = {<?/smaller></TT><BR><TT><?smaller>myDbConnection =3D=20 = Enhydra.getDatabaseManager().allocateConnection();<BR><BR>myDbConnection.= execute(<?color><?param 8989,1313,1515>"CREATE=20 TABLE" <?/color>+ tablename + <?color><?param 8989,1313,1515>" = ("<?/color>=20 +<BR><?color><?param 8989,1313,1515>"headword TEXT DEFAULT '\'''\'' = ,"<?/color> +<BR><?color><?param 8989,1313,1515>"id VARCHAR(255) DEFAULT = '\'''\'' ,"<?/color> +<BR><?color><?param 8989,1313,1515>"pos = VARCHAR(255)=20 DEFAULT '\'''\'' ,"<?/color>=20 +<BR><?color><?param 8989,1313,1515>"pronunciation VARCHAR(255) = DEFAULT=20 '\'''\'' ,"<?/color> +<BR><?color><?param 8989,1313,1515>"reading = VARCHAR(255)=20 DEFAULT '\'''\'' ,"<?/color> +<BR><?color><?param = 8989,1313,1515>"translation=20 TEXT DEFAULT '\'''\'' ,"<?/color> +<BR><?color><?param = 8989,1313,1515>"key1=20 VARCHAR(255) DEFAULT '\'''\'' ,"<?/color>=20 +<BR><?color><?param 8989,1313,1515>"key2 VARCHAR(255) DEFAULT = '\'''\'' ,"<?/color> +<BR><?color><?param 8989,1313,1515>"xmlCode TEXT = DEFAULT '\'''\''=20 NOT NULL ,"<?/color> +<BR><BR><?color><?param 8989,1313,1515>"ObjectId = DECIMAL(19,0) NOT NULL PRIMARY KEY,"<?/color> +<BR><?color><?param = 8989,1313,1515>"ObjectVersion INTEGER NOT=20 NULL)"<?/color>;<BR><BR>}=20 <?color><?param 7676,0F0F,5050>catch<?/color>(SQLException se)=20 = {<BR>se.printStackTrace();<BR><?/smaller></TT><TT><?smaller>}<BR><BR><?/s= maller></TT>So=20 far, we had no problem with these code modifications and we were = pleased with=20 that.<BR><BR>But the problem comes from the modifications of DODS from = the 3.1=20 version to the new 5.1 version.<BR>The new features: = <?fontfamily><?param Arial><?smaller>DO LRU caching, Query LRU caching, = cache=20 initialization <?/smaller><?/fontfamily><BR>complicate very much the = code=20 generated by DODS and now, it is almost impossible to hack the new DO = and=20 Query codes<BR>in order to generate tables dynamically. Thus we are = sticked=20 with the old version of DODS.<BR><BR>Here are my questions = then:<BR><BR>1) Is=20 there an option in the new DODS that allows one to generate code for=20 dynamically creating database tables?<BR>I maybe did not read = carefully the=20 doc.<BR><BR>2) If not, is there anyone who developped his/her own = solution for=20 dynamically creating database tables?<BR><BR>3) Is the DODS team = interested in=20 such a feature for a next release in DODS? In this case, we may try to = patch=20 the existing<BR>DODS with the help of DODS team and then integrate = this patch=20 as an option in DODS for dynamically creating dbtables.<BR><BR>4) If = nothing=20 is possible, is there a way to use the old DODS (3.1) with the new = Enhydra=20 (5.1)<BR><BR>Thank you for your=20 answers,<BR><BR>Mathieu<BR><BR><BR></BLOCKQUOTE></BODY></HTML> ------=_NextPart_000_0014_01C3B68F.D30059E0--