Setting foreign keys from autogenerated primary keys

[favetelinguis]

Im working with an Oracle database that uses sequences for primary keys, there are triggers on these primary keys that does not allow me to enter them manually. What I would like do do in DBSetup is to retrive the autogenerated primary key and use it in a different table as a foreign key. From the examples given on your hompage would it be possible with DBsetup to do the following?

Operation insertVendorsAndProducts = 
    sequenceOf(
        insertInto("VENDOR")
            .columns("ID", "CODE", "NAME")
            .values(This is autogenerated, "AMA", "AMAZON")
            .build(),
        insertInto("PRODUCT")
            .columns("ID", "NAME", "VENDOR_ID")
            .values(1L, "Kindle", "1L")
            .build(),
        sql("update PRODUCT set FEATURED_PRODUCT_ID = with autogenerated value from VENDOR where Vendor ID = 1"));

Currently we are running SQL like this to populate the database.

declare id number; begin insert into f (b) VALUES ('J') returning account__id into id; insert into b (fid) VALUES (id); end;

[favetelinguis]

Been trying to solve it with the following code but without any results.

Operation operation = sequenceOf( . . . sql("update A set fid = (select id from B where c = 'ccc') where c = 'ddd'")

Also is it possible to use DBSetup in a spring transaction. Im currently extending AbstractTransactionalJUnit4SpringContextTests but DBUnit does commit its changes not allowing me to roll back the changes.

[jnizet]

Regarding the first issue, DbSetup doesn't have any support for getting or referencing auto-generated keys. I think it's a bad idea to use auto-generated keys in a test, because a test should be the one deciding what the test data is. And PKs are the best way to uniquely identify rows in your assertions:

• insert a bunch of rows
• test a method executing a SQL query
• check that the IDs of the rows returned by the query are correct

If you don't know the IDs, doing the last part is much harder: you need to find some other column identifying the rows. I wouldn't use triggers to auto-generate the keys. Instead, I would ask the next key to the sequence, and then insert the rows with the known key. Which would allow you, in the test, to simply insert hard-coded IDs and use them in all the foreign keys.

I realize that you probably can't change that easily, but adding support for generated keys in DbSetup wouldn't be a trivial task either, and I'm not sure it's worth it.

Regarding using a Spring transaction, no, it's not supported either. I find this strategy suboptimal, because every time a test fails and you want to look what is in the database, you can't: everything has been rollbacked. Moreover, that forces every test to insert the same data again and again, which can be avoided by using a DbSetupTracker.

[favetelinguis]

Ok thanks for the answeres. Using DbSetup has given me alot of knowledge about good DB design. I dont think you should add suport for "badly designed databases" it is better that teams realize the better ways of doing things when they start to trying to test stuff.