Include sql script in the ear and use a gbean to create tables etc

Details

Description

You can use the DatabaseIntitializationGBean (GERONIMO-2396) in a g. plan and include the sql script in the ejb module so the database will get created if not already present. This is way better than the previous hack of including a pre-built database in the car file.

David Jencks
added a comment - 13/Sep/06 23:10 DAYTRADER-14 .patch adds the sql script to the ejb module.
d-j-plan.xml is a plan that works for both g-jetty and g-tomcat that uses the DatabaseInitiializationGBean to create the tables and deploys both the app clients. Works on trunk for me.

David... this sounds great for use on Geronimo. However, we will still need a method for creating the database tables and indexes on other app servers like JBoss, WebLogic, WebSphere, etc.

As a side note, the commands needed to create the tables and indexes are SQL statements themselves and could be executed within the application as JDBC statements. Just brain storming here... we could hook into the buildDB servlet action which populates the database or we could provide another link on the config page to execute the code to create the tables. Once the database is created and we have sufficeint access rights, we should be good to go. The only obsticle I can think of would be if there are any specifics that we need to account for in the schemas for various database vendors (ie. Derby, Oracle, DB2, etc.).

Christopher James Blythe
added a comment - 26/Sep/06 18:58 David... this sounds great for use on Geronimo. However, we will still need a method for creating the database tables and indexes on other app servers like JBoss, WebLogic, WebSphere, etc.
As a side note, the commands needed to create the tables and indexes are SQL statements themselves and could be executed within the application as JDBC statements. Just brain storming here... we could hook into the buildDB servlet action which populates the database or we could provide another link on the config page to execute the code to create the tables. Once the database is created and we have sufficeint access rights, we should be good to go. The only obsticle I can think of would be if there are any specifics that we need to account for in the schemas for various database vendors (ie. Derby, Oracle, DB2, etc.).
Thoughts???

I started looking at that option. Of course we'll need to connect to the DataSource and check which DB vendor we're connecting to. Based on that we could simply keep a set of external files we could read and execute. That would be pretty helpful.

Unfortunately, the database still needs to be created. Not sure if there is a way around that .

Matt Hogstrom
added a comment - 26/Sep/06 23:55 Chris,
I started looking at that option. Of course we'll need to connect to the DataSource and check which DB vendor we're connecting to. Based on that we could simply keep a set of external files we could read and execute. That would be pretty helpful.
Unfortunately, the database still needs to be created. Not sure if there is a way around that .

Matt, Chris, David
DatabaseMetaData is implemented by JDBC drivers and allows you to check which DB you are working against. Based on that db-specific SQL statements for dropping, creating the tables, indexes etc can be executed. Creating the Database via JDBC is not supported by most databases and so that will still need to be created by hand.

In spite of the above tiny shortcoming, this will make the Daytrader app a lot more self-contained and still open to work on all the different app servers.

Piyush Agarwal
added a comment - 28/Sep/06 18:39 Matt, Chris, David
DatabaseMetaData is implemented by JDBC drivers and allows you to check which DB you are working against. Based on that db-specific SQL statements for dropping, creating the tables, indexes etc can be executed. Creating the Database via JDBC is not supported by most databases and so that will still need to be created by hand.
In spite of the above tiny shortcoming, this will make the Daytrader app a lot more self-contained and still open to work on all the different app servers.

After looking at the code I think we can lookup the database product name using DatabaseMetaData and based on that read a specific DDL file to drop and recreate the db tables. This will account for the differences in schemas of the various database vendors like Derby, db2, oracle etc. It will also save us from hard-coding those DDL-SQL in the application and having a nightmare everytime we change/update the database schema.

The DDL files will be packaged at a specific location within the Daytrader web.war file (say something like web.war/WEB-INF/dbscripts/ folder). Right now we can detect and provide DDL files for databases like Db2, derby and oracle ("tested" databases); for any other "un-tested" database we can make a provision for the user to supply the DDL at a location like web.war/WEB-INF/dbscripts/OTHER/Table.ddl and it will be executed.

Piyush Agarwal
added a comment - 10/Oct/06 17:49 After looking at the code I think we can lookup the database product name using DatabaseMetaData and based on that read a specific DDL file to drop and recreate the db tables. This will account for the differences in schemas of the various database vendors like Derby, db2, oracle etc. It will also save us from hard-coding those DDL-SQL in the application and having a nightmare everytime we change/update the database schema.
The DDL files will be packaged at a specific location within the Daytrader web.war file (say something like web.war/WEB-INF/dbscripts/ folder). Right now we can detect and provide DDL files for databases like Db2, derby and oracle ("tested" databases); for any other "un-tested" database we can make a provision for the user to supply the DDL at a location like web.war/WEB-INF/dbscripts/OTHER/Table.ddl and it will be executed.

I almost have the entire changes done which are required to make the above work. On testing the drop tables and repopulate against Daytrader I came across a problem in repopulation part which was causing DuplicateKeyException to be thrown. On analyzing I realized that the repopulate db part which my code was calling after recreating the tables never deleted/repopulated the KeyGen table. The KeySequenceDirect and KeySequenceBean cache blocks of Ids from this keygen table and use them for holdings, orders etc. When the table is deleted, the caches dont realize this and continue allocating ids from the cache until they run-out and go to the table to get new block of ids. Since the table is re-created, the ids which were used before from the cache are re-generated causing DuplicateKeyExceptions.

To solve this I thought of two options -
1) Do not drop the keyGen table in my ddl script. The attempt to create the KeyGen table will fail with SQLException in case it already exists. This will cause the repopulate code to work as-is and prevent the DuplicateKeyException.
2) The cleaner way (avoiding all exceptions) will be to signal the KeySequenceDirect and KeySequenceBean (depending on Direct or EJB mode) to drop their cached blocks when the repopulation of the tables is being done. This will require some code to be added to the above classes but I think its still do-able without a lot of new code. Also this will be the right way of doing it.

So currently I am going to evaluate the Option 2 unless anyone has something better in mind.

Piyush Agarwal
added a comment - 18/Oct/06 18:25 I almost have the entire changes done which are required to make the above work. On testing the drop tables and repopulate against Daytrader I came across a problem in repopulation part which was causing DuplicateKeyException to be thrown. On analyzing I realized that the repopulate db part which my code was calling after recreating the tables never deleted/repopulated the KeyGen table. The KeySequenceDirect and KeySequenceBean cache blocks of Ids from this keygen table and use them for holdings, orders etc. When the table is deleted, the caches dont realize this and continue allocating ids from the cache until they run-out and go to the table to get new block of ids. Since the table is re-created, the ids which were used before from the cache are re-generated causing DuplicateKeyExceptions.
To solve this I thought of two options -
1) Do not drop the keyGen table in my ddl script. The attempt to create the KeyGen table will fail with SQLException in case it already exists. This will cause the repopulate code to work as-is and prevent the DuplicateKeyException.
2) The cleaner way (avoiding all exceptions) will be to signal the KeySequenceDirect and KeySequenceBean (depending on Direct or EJB mode) to drop their cached blocks when the repopulation of the tables is being done. This will require some code to be added to the above classes but I think its still do-able without a lot of new code. Also this will be the right way of doing it.
So currently I am going to evaluate the Option 2 unless anyone has something better in mind.

The challenge as you pointed out is to somehow invalidate the KeySequence SLSBs. Since their most likely pooled it makes it a bit more tricky. The simplest way to do this which is non-standard would be to use a static int or some other fingerprint on the integrity of the tables. However, this doesn't help the situation when your running a cluster of the application so its applicability would be really to a single server. One possibility is to put in a timestamp as static that indicates the last time the table was valid. Then, for each SLSB keep a last used time and compare the two. If the last used is less than the valid then the beans need to be refreshed so all values could be reset at that time.

The easiest solution I think is to allow the tables to be created...if they already exist warn the user that they need to recycle their environment. I'd be happy with that solution as its way better than what we have. If someone is repopulating...they should be fine with recycling. If possible, I'd add some text when a duplicate key exception occurs that would tip off the user of the possible problem with the Key entity.

Matt Hogstrom
added a comment - 19/Oct/06 13:42 The challenge as you pointed out is to somehow invalidate the KeySequence SLSBs. Since their most likely pooled it makes it a bit more tricky. The simplest way to do this which is non-standard would be to use a static int or some other fingerprint on the integrity of the tables. However, this doesn't help the situation when your running a cluster of the application so its applicability would be really to a single server. One possibility is to put in a timestamp as static that indicates the last time the table was valid. Then, for each SLSB keep a last used time and compare the two. If the last used is less than the valid then the beans need to be refreshed so all values could be reset at that time.
The easiest solution I think is to allow the tables to be created...if they already exist warn the user that they need to recycle their environment. I'd be happy with that solution as its way better than what we have. If someone is repopulating...they should be fine with recycling. If possible, I'd add some text when a duplicate key exception occurs that would tip off the user of the possible problem with the Key entity.

Matt, thanks for the headsup and valuable insight. So inorder to keep things simple and not have to change the logic inside the KeySequnceBeans and also avoid catching the DuplicateKeyExceptions I propose doing the following – Drop and Recreate the tables, at end of that the user will get a prominent message to Stop and Restart their application and then use the link to Repopulate the daytrader database.

Since the user will be recycling his environment before the repopulation of the tables the SLSBs will get recycled and the DuplicateKeyExceptions will be avoided.

Piyush Agarwal
added a comment - 19/Oct/06 19:23 Matt, thanks for the headsup and valuable insight. So inorder to keep things simple and not have to change the logic inside the KeySequnceBeans and also avoid catching the DuplicateKeyExceptions I propose doing the following – Drop and Recreate the tables, at end of that the user will get a prominent message to Stop and Restart their application and then use the link to Repopulate the daytrader database.
Since the user will be recycling his environment before the repopulation of the tables the SLSBs will get recycled and the DuplicateKeyExceptions will be avoided.
Thoughts??

Attaching the patch which will allow the creation of Daytrader database tables from within Daytrader configuration panels. The patch updates the Configuration panel to include a new link "Recreate Daytrader tables and indexes" which will automatically detect Derby, Db2 and Oracle databases and use the ddl that I have provided for those databases to drop and recreate the database. any other unsupported database ddl can be placed under <AppDeploypath>/web.war/dbscripts/other/Table.ddl and it will be picked up and used. Users will need to stop the Application and restart it after doing this step and then use the "Repopulate Daytrader Database" link to populate the newly created tables. The Stop and restart is needed to flush the cached ids from KeySequenceDirect and KeySequenceBean and prevent DuplicateKeyExceptions (refer to Daytrader-14 JIRA comments for more details).

With the implementation of this feature the createDB.sh script provided under the modules/derby can be changed to just create the empty database and no longer needs to run the modules/derby/src/sql/daytrader.sql to establish the tables. The README should be changed to instruct the users to use this new feature to establish their daytrader database tables. But I will leave that as an open item to be discussed and taken care of later.

Piyush Agarwal
added a comment - 20/Oct/06 17:25 Attaching the patch which will allow the creation of Daytrader database tables from within Daytrader configuration panels. The patch updates the Configuration panel to include a new link "Recreate Daytrader tables and indexes" which will automatically detect Derby, Db2 and Oracle databases and use the ddl that I have provided for those databases to drop and recreate the database. any other unsupported database ddl can be placed under <AppDeploypath>/web.war/dbscripts/other/Table.ddl and it will be picked up and used. Users will need to stop the Application and restart it after doing this step and then use the "Repopulate Daytrader Database" link to populate the newly created tables. The Stop and restart is needed to flush the cached ids from KeySequenceDirect and KeySequenceBean and prevent DuplicateKeyExceptions (refer to Daytrader-14 JIRA comments for more details).
With the implementation of this feature the createDB.sh script provided under the modules/derby can be changed to just create the empty database and no longer needs to run the modules/derby/src/sql/daytrader.sql to establish the tables. The README should be changed to instruct the users to use this new feature to establish their daytrader database tables. But I will leave that as an open item to be discussed and taken care of later.

I really like what you have here... It's clean, simple, and makes working with the default derby database so much easier. We no long have to run and external sql script or provide a pre-built derby database.

Actually, I went ahead and changed the deployment plans in Daytrader-24 to create the derby db by default.

Just a few more comments...

I like how feedback is written to the page while created the db. How do you feel about writing out the status for each sql command that is executed instead of waiting for all of them to complete. If for some reason, one were to fail it would be nice to see it in the browser screen. That way we don't have to dig through the logs to see what happened.

Looks like there might be a type of two in your trace/error statements... In TradeDirect.java - "thrown on executing the [foll] sql command:"

We need to sync up the ddl files - specifically, the indexes. I will create another JIRA to address this.

Christopher James Blythe
added a comment - 14/Nov/06 19:24 Piyush...
I really like what you have here... It's clean, simple, and makes working with the default derby database so much easier. We no long have to run and external sql script or provide a pre-built derby database.
Actually, I went ahead and changed the deployment plans in Daytrader-24 to create the derby db by default.
Just a few more comments...
I like how feedback is written to the page while created the db. How do you feel about writing out the status for each sql command that is executed instead of waiting for all of them to complete. If for some reason, one were to fail it would be nice to see it in the browser screen. That way we don't have to dig through the logs to see what happened.
Looks like there might be a type of two in your trace/error statements... In TradeDirect.java - "thrown on executing the [foll] sql command:"
We need to sync up the ddl files - specifically, the indexes. I will create another JIRA to address this.
Chris

I had originally coded to have the SQL exceptions write out to both the browser and the logs, but later I went ahead and disabled the output to the browser output. There were two reasons -
1) There are some SQL statements that we expect to fail. for eg - the scripts have Drop tables commands at the start and then the create table commands. The first time the daytrader db is created all the Drop table commands will fail as no tables exist. I didnt want to write out these exceptions to the browser and confuse the user.
2) In niost cases the SQL Exception is quite verbose and if you have couple of them happen... they can quickly fill up the browser screen completely. So imagine the drop commands above failing and your screen getting flooded with SQL exceptions, plus this is bound to happen on every first install of the Daytrader app and creation of the database.

But it does give me an idea. How about I write out the failed SQL command to the browser screen and mention to the user to look up this SQL command and its associated SQL exception in the logs. This way the user will exactly know which SQL command failed, if its one of the SQL commands which shouldn't have failed they can look up the Exception in the logs, if its one of the commands which can generally fail they can safely ignore the message.

Piyush Agarwal
added a comment - 14/Nov/06 20:34 Chris, Glad you like the new feature!
I had originally coded to have the SQL exceptions write out to both the browser and the logs, but later I went ahead and disabled the output to the browser output. There were two reasons -
1) There are some SQL statements that we expect to fail. for eg - the scripts have Drop tables commands at the start and then the create table commands. The first time the daytrader db is created all the Drop table commands will fail as no tables exist. I didnt want to write out these exceptions to the browser and confuse the user.
2) In niost cases the SQL Exception is quite verbose and if you have couple of them happen... they can quickly fill up the browser screen completely. So imagine the drop commands above failing and your screen getting flooded with SQL exceptions, plus this is bound to happen on every first install of the Daytrader app and creation of the database.
But it does give me an idea. How about I write out the failed SQL command to the browser screen and mention to the user to look up this SQL command and its associated SQL exception in the logs. This way the user will exactly know which SQL command failed, if its one of the SQL commands which shouldn't have failed they can look up the Exception in the logs, if its one of the commands which can generally fail they can safely ignore the message.

Based on the last comments by Chris, I have added the facility for failed SQL statements to be written out on the browser screen. The user is instructed to look up the exception details related to this SQL failure in the log.

Piyush Agarwal
added a comment - 15/Nov/06 16:43 Based on the last comments by Chris, I have added the facility for failed SQL statements to be written out on the browser screen. The user is instructed to look up the exception details related to this SQL failure in the log.

Just figured out that due to some error with the TortoiseSVN's patch creation my last patch has the ddl files included twice.
I have fixed this and uploading the patch again. So please ignore the previous files and use the file daytrader-14.patch.11202006

Piyush Agarwal
added a comment - 20/Nov/06 21:41 Just figured out that due to some error with the TortoiseSVN's patch creation my last patch has the ddl files included twice.
I have fixed this and uploading the patch again. So please ignore the previous files and use the file daytrader-14.patch.11202006