[jira] Commented: (DAYTRADER-14) Include sql script in the ear and use a gbean to create tables etc
[ http://issues.apache.org/jira/browse/DAYTRADER-14?page=comments#action_12449738 ] Christopher James Blythe commented on DAYTRADER-14: --- 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 Include sql script in the ear and use a gbean to create tables etc -- Key: DAYTRADER-14 URL: http://issues.apache.org/jira/browse/DAYTRADER-14 Project: DayTrader Issue Type: Improvement Components: EJB Tier Affects Versions: 1.2 Reporter: David Jencks Attachments: d-j-plan.xml, DAYTRADER-14.patch, daytrader-14.patch.1019 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. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DAYTRADER-14) Include sql script in the ear and use a gbean to create tables etc
[ http://issues.apache.org/jira/browse/DAYTRADER-14?page=comments#action_12449775 ] Piyush Agarwal commented on DAYTRADER-14: - 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. Include sql script in the ear and use a gbean to create tables etc -- Key: DAYTRADER-14 URL: http://issues.apache.org/jira/browse/DAYTRADER-14 Project: DayTrader Issue Type: Improvement Components: EJB Tier Affects Versions: 1.2 Reporter: David Jencks Attachments: d-j-plan.xml, DAYTRADER-14.patch, daytrader-14.patch.1019 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. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DAYTRADER-14) Include sql script in the ear and use a gbean to create tables etc
[ http://issues.apache.org/jira/browse/DAYTRADER-14?page=comments#action_12449785 ] Christopher James Blythe commented on DAYTRADER-14: --- To quote the Guinness commercials... Brilliant! Include sql script in the ear and use a gbean to create tables etc -- Key: DAYTRADER-14 URL: http://issues.apache.org/jira/browse/DAYTRADER-14 Project: DayTrader Issue Type: Improvement Components: EJB Tier Affects Versions: 1.2 Reporter: David Jencks Attachments: d-j-plan.xml, DAYTRADER-14.patch, daytrader-14.patch.1019 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. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DAYTRADER-14) Include sql script in the ear and use a gbean to create tables etc
[ http://issues.apache.org/jira/browse/DAYTRADER-14?page=comments#action_12443523 ] Matt Hogstrom commented on DAYTRADER-14: 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. Include sql script in the ear and use a gbean to create tables etc -- Key: DAYTRADER-14 URL: http://issues.apache.org/jira/browse/DAYTRADER-14 Project: DayTrader Issue Type: Improvement Components: EJB Tier Affects Versions: 1.2 Reporter: David Jencks Attachments: d-j-plan.xml, DAYTRADER-14.patch 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. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DAYTRADER-14) Include sql script in the ear and use a gbean to create tables etc
[ http://issues.apache.org/jira/browse/DAYTRADER-14?page=comments#action_12443624 ] Piyush Agarwal commented on DAYTRADER-14: - 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?? Include sql script in the ear and use a gbean to create tables etc -- Key: DAYTRADER-14 URL: http://issues.apache.org/jira/browse/DAYTRADER-14 Project: DayTrader Issue Type: Improvement Components: EJB Tier Affects Versions: 1.2 Reporter: David Jencks Attachments: d-j-plan.xml, DAYTRADER-14.patch 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. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DAYTRADER-14) Include sql script in the ear and use a gbean to create tables etc
[ http://issues.apache.org/jira/browse/DAYTRADER-14?page=comments#action_12443686 ] Matt Hogstrom commented on DAYTRADER-14: I'm open to other ideas but I think that is clear and makes life the easiest. Include sql script in the ear and use a gbean to create tables etc -- Key: DAYTRADER-14 URL: http://issues.apache.org/jira/browse/DAYTRADER-14 Project: DayTrader Issue Type: Improvement Components: EJB Tier Affects Versions: 1.2 Reporter: David Jencks Attachments: d-j-plan.xml, DAYTRADER-14.patch 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. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DAYTRADER-14) Include sql script in the ear and use a gbean to create tables etc
[ http://issues.apache.org/jira/browse/DAYTRADER-14?page=comments#action_12443329 ] Piyush Agarwal commented on DAYTRADER-14: - 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. Include sql script in the ear and use a gbean to create tables etc -- Key: DAYTRADER-14 URL: http://issues.apache.org/jira/browse/DAYTRADER-14 Project: DayTrader Issue Type: Improvement Components: EJB Tier Affects Versions: 1.2 Reporter: David Jencks Attachments: d-j-plan.xml, DAYTRADER-14.patch 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. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DAYTRADER-14) Include sql script in the ear and use a gbean to create tables etc
[ http://issues.apache.org/jira/browse/DAYTRADER-14?page=comments#action_12441226 ] Piyush Agarwal commented on DAYTRADER-14: - 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. Include sql script in the ear and use a gbean to create tables etc -- Key: DAYTRADER-14 URL: http://issues.apache.org/jira/browse/DAYTRADER-14 Project: DayTrader Issue Type: Improvement Components: EJB Tier Affects Versions: 1.2 Reporter: David Jencks Attachments: d-j-plan.xml, DAYTRADER-14.patch 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. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DAYTRADER-14) Include sql script in the ear and use a gbean to create tables etc
[ http://issues.apache.org/jira/browse/DAYTRADER-14?page=comments#action_12437908 ] Christopher James Blythe commented on DAYTRADER-14: --- 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??? Include sql script in the ear and use a gbean to create tables etc -- Key: DAYTRADER-14 URL: http://issues.apache.org/jira/browse/DAYTRADER-14 Project: DayTrader Issue Type: Improvement Components: EJB Tier Affects Versions: 1.2 Reporter: David Jencks Attachments: d-j-plan.xml, DAYTRADER-14.patch 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. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
