DB best practices should probably be documented somewhere.
Use joins instead of subselects.
Use INSERT SELECT instead of INSERT INTO VALUES SUBSELECT
Use Integer fields instead of booleans (same anyhow in the raw)
Ojb has the field conversion available:
<field-descriptor
name="isSelloutArticle"
column="Auslaufartikel"
jdbc-type="INTEGER"
conversion="org.apache.ojb.broker.accesslayer.
conversions.Boolean2IntFieldConversion"
> Even those generated scripts are not fail proof though. For Oracle, the
> generated drop table/create table sequences fail when a table doesn't
> exists yet. For first time usage on Oracle additional steps are required
> (see Known Issues for Oracle at:
> http://portals.apache.org/jetspeed-2/database.html).
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...]
> Seeding scripts (right now default entities and userinfo data) used in
> the fullDeploy goal aren't even generated. Here we now use hand written
> scripts adapted to one or more databases (the latest addition:
> http://issues.apache.org/jira/browse/JS2-49).
>
Another best practice is to use:
http://msdn.microsoft.com/library/en-us/odbc/htm/odbcdate__time__and_timesta
mp_literals.asp?frame=true
ODBC Programmer's Reference
Date, Time, and Timestamp Literals
The escape sequence for date, time, and timestamp literals is
{literal-type 'value'}
So for a date field:
INSERT INTO mytable (field_date, field_time, field_timestamp)
VALUES ({d '1995-01-15'}, {t '23:00:00'}, {ts '1995-01-15 23:00:00'})
> I personally think this is going to get out of our hands. Now we are
> only dealing with Hsqldb, MySql and Oracle.
I would add DB2 7.2/8.1/Stinger, MS-SQL 2K/2005, and Postgres. But torque
has many more supported.
When more databases are
> thrown into this mix I can only imagine how we are going to maintain all
> these differences.
You could try to minimize the differences by steering toward ANSI sql
whenever possible (not always possible or supported though.)
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
Or if you have lots of $ to burn http://www.embarcadero.com/
DB2, Oracle, Postgres, MySQL are downloadable developer eds.
MS-SQL developer edition is $50. 120-Day Eval is downloadable. MSDE is free
and redistributable, anything that works for it should work for ms-sql 2k
(data engine built and based on core SQL Server technology, but there's no
Admin Client or Query Analyzer)
http://www.microsoft.com/sql/msde/
Thanks
-TR
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.)
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
