Referential integrity scripts
Referential integrity sucks. If you’ve ever had a script you had to run twenty times because the database complained the tables were refering to another table that didn’t exist yet, you understand why. I finally broke down and wrote something that semi-fixes the problem. I’ve had these hanging around for a while, but I don’t think I ever really mentioned them appropriately.
Dynamo has this neat feature that allows you to create the item descriptors first and generate the SQL for it afterwards. Personally, I generate all my schemas this way, as it’s a pain to type out table definitions all the time and remember the design patterns.
The command is something like
startSQLRepository -m MyModule -repository /atg/projects/myproj/MyRepository -database oracle -outputSQL
This exchanges one problem (typing SQL) for another (figuring out the order to create tables in). I’m lazy, so I wrote a perl script that reads in all the tables in an SQL script, keeps track of the references between tables, and writes out the tables in the correct order.
After that, there’s another script which takes that list and reverses it so I can delete the tables without the database complaining at me.
The scripts are available as wref and wdrop.
P.S. Incidentally, there is also sample code on the developer portal which generates a repository from JDBC tables.