Dods: How to dynamically create db tables with DODS?

--Apple-Mail-2--745760829
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
	charset=US-ASCII;
	format=flowed


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
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 :
   */
      protected String dbtablename;

      public String getTableName() {
          return dbtablename;
      }

      public void setTableName(String name) {
         dbtablename = 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 = 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 = tablename;
         builder = 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 = 
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
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



--Apple-Mail-2--745760829
Content-Transfer-Encoding: 7bit
Content-Type: text/enriched;
	charset=US-ASCII



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 

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:


<fixed><smaller> <color><param>7676,0F0F,5050</param>public</color>
<color><param>7676,0F0F,5050</param>class</color> VolumeEntryDO
<color><param>7676,0F0F,5050</param>extends</color>
com.lutris.dods.builder.generator.dataobject.GenericDO
<color><param>7676,0F0F,5050</param>implements</color> VolumeEntryDOI,
java.io.Serializable {


 <color><param>2322,6E6D,2524</param>/**

  </color><color><param>2321,6E6C,2523</param>*</color> I  added a
variable for the name of the table :
<color><param>2322,6E6D,2524</param> 

  */</color>

     <color><param>7676,0F0F,5050</param>protected</color> String
dbtablename;


     <color><param>7676,0F0F,5050</param>public</color> String
getTableName() {

         <color><param>7676,0F0F,5050</param>return</color>
dbtablename;

     }


     <color><param>7676,0F0F,5050</param>public</color>
<color><param>7676,0F0F,5050</param>void</color> setTableName(String
name) {

        dbtablename = name;

     }

     

     

   <color><param>2323,6E6E,2525</param>/**

    * static final RDBColumn PrimaryKey for use with QueryBuilder.

    * See example above.

    </color><color><param>2322,6E6D,2524</param>* Then I give the name
of the table as a parameter for the methods:</color><color><param>2323,6E6E,2525</param>

		*/</color>

     <color><param>7676,0F0F,5050</param>public</color>
<color><param>7676,0F0F,5050</param>static</color> RDBColumn
getPrimaryKey(String tablename) {

         RDBTable  table =
<color><param>7676,0F0F,5050</param>new</color> RDBTable(tablename);

         <color><param>7676,0F0F,5050</param>return</color>
<color><param>7676,0F0F,5050</param>new</color> RDBColumn( table,

                               getPrimaryKeyName() );


     }

</smaller></fixed> 

I hacked also the Query class code by giving the table name
(<fixed><smaller>tablename</smaller></fixed>) as a parameter for the
constructor:


<fixed><color><param>7676,0F0F,5050</param><smaller>public</smaller></color><smaller>
<color><param>7676,0F0F,5050</param>class</color> VolumeEntryQuery
<color><param>7676,0F0F,5050</param>implements</color> Query {



    <color><param>7676,0F0F,5050</param>private</color> QueryBuilder
builder;

    <color><param>7676,0F0F,5050</param>private</color> String
dbtablename;


    <color><param>2323,6E6E,2525</param>/**

    * Public constructor.

     */</color>

    <color><param>7676,0F0F,5050</param>public</color>
VolumeEntryQuery(String tablename) {

        dbtablename = tablename;

        builder = <color><param>7676,0F0F,5050</param>new</color>
QueryBuilder( tablename, tablename +
<color><param>8989,1313,1515</param>".*"</color> );

        builder.setDatabaseVendor(
<color><param>8989,1313,1515</param>"PostgreSQL"</color> );

        builder.setStringMatchDetails(
<color><param>8989,1313,1515</param>"LIKE"</color>,
<color><param>8989,1313,1515</param>"%"</color> );

        reset();

    }


</smaller></fixed>Then I created code from the .sql generated files in
order to dynamically create new tables:

<fixed><smaller>       
<color><param>7676,0F0F,5050</param>try</color> {</smaller></fixed>

<fixed><smaller>               myDbConnection =
Enhydra.getDatabaseManager().allocateConnection();


               
myDbConnection.execute(<color><param>8989,1313,1515</param>"CREATE
TABLE" </color>+ tablename + <color><param>8989,1313,1515</param>"
("</color> +

        <color><param>8989,1313,1515</param>"headword TEXT DEFAULT
'\'''\''    ,"</color> +

        <color><param>8989,1313,1515</param>"id VARCHAR(255) DEFAULT
'\'''\''    ,"</color> +

        <color><param>8989,1313,1515</param>"pos VARCHAR(255) DEFAULT
'\'''\''    ,"</color> +

        <color><param>8989,1313,1515</param>"pronunciation
VARCHAR(255) DEFAULT '\'''\''    ,"</color> +

        <color><param>8989,1313,1515</param>"reading VARCHAR(255)
DEFAULT '\'''\''    ,"</color> +

        <color><param>8989,1313,1515</param>"translation TEXT DEFAULT
'\'''\''    ,"</color> +

        <color><param>8989,1313,1515</param>"key1 VARCHAR(255) DEFAULT
'\'''\''     ,"</color> +

        <color><param>8989,1313,1515</param>"key2 VARCHAR(255) DEFAULT
'\'''\''    ,"</color> +

        <color><param>8989,1313,1515</param>"xmlCode TEXT DEFAULT
'\'''\'' NOT NULL   ,"</color> +


        <color><param>8989,1313,1515</param>"ObjectId DECIMAL(19,0)
NOT NULL PRIMARY KEY,"</color> +

        <color><param>8989,1313,1515</param>"ObjectVersion INTEGER NOT
NULL)"</color>;

				

				 }  <color><param>7676,0F0F,5050</param>catch</color>(SQLException
se) {

                se.printStackTrace();

</smaller></fixed>		<fixed><smaller>}


</smaller></fixed>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: <fontfamily><param>Arial</param><smaller>DO LRU
caching, Query LRU caching, cache initialization </smaller></fontfamily>

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

 



--Apple-Mail-2--745760829--