Location for module generic but Oracle specific SQL scripts?

[mlavergn]

I'm looking for some kind of consensus as to an appropriate location for database specific but module generic one-time use SQL scripts? The reasoning is that any changes to indexing, triggers, etc would be reflected in the setup.xml file -but- for existing installations, the end user would obviously not want to re-run setup.ddl so we need a way to provide an "upgrade" path. For example, I would create a file like 'nukes10.sql' in cvs head (2.0) to indicate an upgrade script from Nukes 1.0 to 2.0. My current train of thought is that these should be in:

nukes/nukes/src/resources/oracle

since the prepare.ddl file lives there. Let me know your thoughts on that. Thanks!

[jae77]

i don't see anything wrong w/ that location. i would recommend using a similiar format to the setup.xml file so that either a .sql file and/or a beanshell script can be generated via xlst to help make things a bit easier.

in some cases if the table structure have actually changed, it may be easier to write export/import scripts and have the user just re-create the tables.

[mlavergn]

Good point, it would be in the form "nukes10.xml" in a setup.xml flavor, not a plain SQL script, my bad there.

I also agree with your second point, Oracle's ability to deal with table structure changes in-db varies quite a bit by Oracle version so exp/imp is the easiest catch-alll solution for those. Although, in theory, an Ant script that detects Oracle version and determines a course of action (in-db vs. exp/imp) could be more efficient ... but hopefully that's nothing we need to deal with in the immediate future!

[theute]

setup.xml allows us to use them in the installer for an even easier way to install nukes.

We should look for something database independent. Julien told me about LDBC http://ldbc.sourceforge.net/ and it might be a good point, they don't support SAPDB yet though... ( i saw users using Nukes with SAPDB on the forum)

But they support:
Oracle 8.1.7
Oracle 9.2.0
Microsoft SQL server
MySQL
IDM DB2
Postgresql
PointBase
HSQLDB
and few others not tested

It would greatly help us as we would have to write only 1 setup.xml for all those databases. All the queries are very simple table creation and insertions so LDBC should not have any trouble.

[jae77]

LDBC looks interesting and may help solve this issue perminantly.

off the top of my head, i think ppl would still be able to use any database of their choosing, but there would be a bit more work involved b/c they would have to manually create the tables and load the data themselves (in addition to changing the datasource db driver, etc).

[julien1]

loading the data is a pain, because each database as a different way to express it.

boolean is expressed as "true|false" in hsqldb whereas it is expressed as 0/1 in mysql for instance.

[jae77]

i think LDBC should handle this through their grammar mapping, so it may make it a non-issue. since the wife is going to be out of town this weekend and i don't have anything to do as of right now, i may be able to prototype something real quick to see what happens.

i'm thinking that if it does work we may be able to continue to use a 'pure' cmp approach and build a "default data" module that will prompt you to load default data if necessary, but this would need to be flushed out a bit more.

[julien1]

You have to check first that something like : "INSERT INTO BLAH ('aaa',1,true) works, i.e work with Statement.

Because if it does rely on PreparedStatement when you execute something your statement looks like "INSERT INTO BLAH (?,?,?)" then the proprietary driver takes care of marshalling the types when it talks to the database.

LDBC could be used only to create portable CREATE TABLE/DROP TABLE statements. Perhaps it could work for Statement.