DB best practices should probably be documented somewhere.
Use joins instead of subselects. Use INSERT SELECT instead of INSERT INTO VALUES SUBSELECT
I don't think INSERT SELECT is supported by MySQL, unless maybe the latest release.
Something like this possible? (ms-sql server admin client generates this for a create ddl script - a check prior to calling a drop, then followed by the create:
if exists (select * from dbo.sysobjects where id = object_id (N '[dbo].[TABLENAME]') and OBJECTPROPERTY (id, N 'IsUserTable') = 1) drop table [dbo].[TABLENAME] GO CREATE [TABLE_DEF...]
This could be added to the DDL velocity templates for Oracle. It's not that hard to do, but the full patch process is :
1. Modify the maven plugin
2. Make the patch to the Torque project
3. Hope it gets committed into Torque's CVS
4. Hope Maven packages the updated plugin in a release, and not having to wait too long.
Last time we had to do this it took quite a while for the modification to actually come through. So this process is quite long and unfortunate.
Torque generator probably the better way to go using the maven goals: http://db.apache.org/torque/generator/ http://db.apache.org/torque/maven-plugin/goals.html
This is exactly what Jetspeed 2 is using right now. But this only addresses the problems with the DDL, not the database population scripts, which Torque Gen doesn't seem to have any support for. This is why I suggested that we might look into adding support to Torque Gen for that (yes I know this is side tracking, but if it's a blocker for us, this is one way of addressing it).
Personnally I don't think it's a good idea on the long run to have to maintain database scripts for all the databases. It's a maintenance nightmare. I'd much rather have to maitain database "templates" and have them generate all the scripts (DDL and population), for us.
PS: Right now I'm backed up on some paying work I need to get done. Hopefully, I can contribute somehow next month. Database/SQL might be something I can help with. Database testing? I've used hsql, MySql, Oracle 8i, DB2 7.2/8.1, sql-server 7.x/2K, (As well as Cloudscape, Access, Clipper, and Alpha4, and R-Base, and SQLAnywhere.)
I'm backed-up to the moon but I'll try to do my best to help out, as I also want to improve the DB portability of J2.
Regards, Serge Huber.
- -- --- -----=[ shuber2 at jahia dot com ]=---- --- -- -
www.jahia.org : A collaborative source CMS and Portal Server
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
