Re[2]: [Hibernate-devel] database native keys

[Doug C. <de...@fl...>]

> (1)
> The native key generation rearranges the insertion order of objects and was
> causing foreign key constraint violations. I have made a quick (easy) fix,
> though something more efficient might be better in future. (Nothing changed
> for non-native key generation.)

I'm not so sure...

testLoad...
Opened session
Generated new collection ID 262150 for table foobytes
Generated new collection ID 262151 for table foobytes
Flushing 3 insertions, 3 updates, 0 deletions to 3 objects and updating 6 collections....
[1] insert into foos ( foo, long_, integer_, float_, double_, bytes, date_, timestamp_, boolean_, bool_, null_, short_, zero_, int_, string_, byte_, yesno, blobb_, nullBlob, status_, bin_, first_name, surname, count_, name_, subcount, subname, foo_id, class ) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'cirrus.hibernate.test.Foo' )
[2] insert into quux ( foo, deleted, loaded, stored, created, childKey, stuff, qux_key ) values ( ?, ?, ?, ?, ?, ?, ?, ? )
[3] insert into foos ( baz, bar_string, bar_count, name, importantDates, the_time, foo, long_, integer_, float_, double_, bytes, date_, timestamp_, boolean_, bool_, null_, short_, zero_, int_, string_, byte_, yesno, blobb_, nullBlob, status_, bin_, first_name, surname, count_, name_, subcount, subname, foo_id, class ) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'cirrus.hibernate.test.Bar' )
[4] update foos set foo = ?, long_ = ?, integer_ = ?, float_ = ?, double_ = ?, bytes = ?, date_ = ?, timestamp_ = ?, boolean_ = ?, bool_ = ?, null_ = ?, short_ = ?, zero_ = ?, int_ = ?, string_ = ?, byte_ = ?, yesno = ?, blobb_ = ?, nullBlob = ?, status_ = ?, bin_ = ?, first_name = ?, surname = ?, count_ = ?, name_ = ?, subcount = ?, subname = ? where foo_id = ?
[5] update quux set foo = ?, deleted = ?, loaded = ?, stored = ?, created = ?, childKey = ?, stuff = ? where qux_key = ?
[6] update foos set baz = ?, bar_string = ?, bar_count = ?, name = ?, importantDates = ?, the_time = ?, foo = ?, long_ = ?, integer_ = ?, float_ = ?, double_ = ?, bytes = ?, date_ = ?, timestamp_ = ?, boolean_ = ?, bool_ = ?, null_ = ?, short_ = ?, zero_ = ?, int_ = ?, string_ = ?, byte_ = ?, yesno = ?, blobb_ = ?, nullBlob = ?, status_ = ?, bin_ = ?, first_name = ?, surname = ?, count_ = ?, name_ = ?, subcount = ?, subname = ? where foo_id = ?
Inserting collection: foobytes#262150
[7] insert into foobytes ( id, i, byte ) values ( ?, ?, ? )
Inserting collection: foo_dates#40288286:ec7443c9:00ec:74461cef:0011
[8] insert into foo_dates ( foo_id, i, date_ ) values ( ?, ?, ? )
Inserting collection: foo_times#40288286:ec7443c9:00ec:74461cef:0011
[9] insert into foo_times ( foo_id, i, date_ ) values ( ?, ?, ? )
Inserting collection: foobytes#262151
[10] insert into foobytes ( id, i, byte ) values ( ?, ?, ? )
Inserting collection: foo_times#40288286:ec7443c9:00ec:74461cef:0010
[11] insert into foo_times ( foo_id, i, date_ ) values ( ?, ?, ? )
Inserting collection: foo_dates#40288286:ec7443c9:00ec:74461cef:0010
[12] insert into foo_dates ( foo_id, i, date_ ) values ( ?, ?, ? )
Running Session.finalize()
Opened session
[1] select qux_key, foo, deleted, loaded, stored, created, childKey, stuff from quux where qux_key = ?
[2] select foo_id, class, foo, long_, integer_, float_, double_, bytes, date_, timestamp_, boolean_, bool_, null_, short_, zero_, int_, string_, byte_, yesno, blobb_, nullBlob, status_, bin_, first_name, surname, count_, name_, subcount, subname, the_time, baz, bar_string, bar_count, name, importantDates from foos where foo_id = ?
Loading collection: foobytes#262150
[3] select byte, i from foobytes where id = ? order by i
Loading collection: foo_times#40288286:ec7443c9:00ec:74461cef:0010
[4] select date_, i from foo_times where foo_id = ? order by i
Loading collection: foo_dates#40288286:ec7443c9:00ec:74461cef:0010
[5] select date_, i from foo_dates where foo_id = ? order by i
[6] select foo_id, class, foo, long_, integer_, float_, double_, bytes, date_, timestamp_, boolean_, bool_, null_, short_, zero_, int_, string_, byte_, yesno, blobb_, nullBlob, status_, bin_, first_name, surname, count_, name_, subcount, subname, the_time, baz, bar_string, bar_count, name, importantDates from foos where foo_id = ?
Loading collection: foos#40288286:ec7443c9:00ec:74461cef:0011
[7] select foo_id from foos where abstract_id = ?
Loading collection: foobytes#262151
[8] select byte, i from foobytes where id = ? order by i
Loading collection: foo_times#40288286:ec7443c9:00ec:74461cef:0011
[9] select date_, i from foo_times where foo_id = ? order by i
Loading collection: foo_dates#40288286:ec7443c9:00ec:74461cef:0011
[10] select date_, i from foo_dates where foo_id = ? order by i
[11] select foo_id, class, baz, bar_string, bar_count, name, importantDates, the_time, foo, long_, integer_, float_, double_, bytes, date_, timestamp_, boolean_, bool_, null_, short_, zero_, int_, string_, byte_, yesno, blobb_, nullBlob, status_, bin_, first_name, surname, count_, name_, subcount, subname from foos where foo_id = ?
[12] insert into foos ( foo, long_, integer_, float_, double_, bytes, date_, timestamp_, boolean_, bool_, null_, short_, zero_, int_, string_, byte_, yesno, blobb_, nullBlob, status_, bin_, first_name, surname, count_, name_, subcount, subname, foo_id, class ) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'cirrus.hibernate.test.Foo' )
Loading collection: Fum#327680
[13] select string_, short_, date_, posn from Fum where qux_id = ? order by posn
Loading collection: fums#327680
[14] select fum_string, fum_short, fum_date from fums where qux_id = ?
Flushing 0 insertions, 1 updates, 3 deletions to 3 objects and updating 9 collections....
[15] update quux set foo = ?, deleted = ?, loaded = ?, stored = ?, created = ?, childKey = ?, stuff = ? where qux_key = ?
Deleting collection: foo_times#40288286:ec7443c9:00ec:74461cef:0010
[16] delete from foo_times where foo_id = ?
Deleting collection: Fum#327680
[17] update Fum set qux_id = null where qux_id = ?
Deleting collection: foobytes#262151
[18] delete from foobytes where id = ?
Deleting collection: foo_dates#40288286:ec7443c9:00ec:74461cef:0010
[19] delete from foo_dates where foo_id = ?
Deleting collection: foo_dates#40288286:ec7443c9:00ec:74461cef:0011
[20] delete from foo_dates where foo_id = ?
Deleting collection: foobytes#262150
[21] delete from foobytes where id = ?
Deleting collection: foo_times#40288286:ec7443c9:00ec:74461cef:0011
[22] delete from foo_times where foo_id = ?
Deleting collection: foos#40288286:ec7443c9:00ec:74461cef:0011
[23] update foos set abstract_id = null where abstract_id = ?
Deleting collection: fums#327680
[24] delete from fums where qux_id = ?
[25] delete from foos where foo_id = ?
[26] delete from quux where qux_key = ?
[27] delete from foos where foo_id = ?
java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:324)
        at cirrus.hibernate.test.TestCase.run(TestCase.java:46)
        at cirrus.hibernate.test.FooBarTest.main(FooBarTest.java:1216)
Caused by: com.mckoi.database.jdbc.MSQLException: Deferred foreign key constraint violation on delete (foo_datesFK0) Columns = APP.foo_dates( foo_id ) -> APP.foos( foo_id )
        at com.mckoi.database.jdbcserver.LocalDatabaseInterface.exec(Unknown Source)
        at com.mckoi.database.jdbcserver.LocalDatabaseInterface.execQuery(Unknown Source)
        at com.mckoi.database.jdbc.MConnection.executeQuery(Unknown Source)
        at com.mckoi.database.jdbc.MStatement.executeQuery(Unknown Source)
        at com.mckoi.database.jdbc.MStatement.executeQuery(Unknown Source)
        at com.mckoi.database.jdbc.MConnection.commit(Unknown Source)
        at cirrus.hibernate.impl.RelationalDatabaseSession.close(RelationalDatabaseSession.java:1903)
        at cirrus.hibernate.impl.RelationalDatabaseSession.commit(RelationalDatabaseSession.java:1867)
        at cirrus.hibernate.test.FooBarTest.testLoad(FooBarTest.java:185)
        ... 6 more

> (2)
> I also added support for DB2 native key generation. So the list so far is:

> MySQL
> Sybase
> MS SQL
> DB2

> What other dbs support this? Oracle and Interbase don't have identity
> columns and I think the same goes for Postgres. This leaves:

> HSQL _does_ have identity columns.

> McKoi and Progress I'm not sure about.

Mckoi has a function UNIQUEKEY which generates a uniqueid, but it
doesn't seem to fit the model employed by Hibernate...

     SELECT UNIQUEKEY('Orders')

     INSERT INTO Orders
        ( id, number, division, date_made, quantity,
          value )
       VALUES
        ( UNIQUEKEY('Orders'), CONCAT('Order-', id),
          'Bio Engineering', DATEOB(), 25, 1900.00 )


e