Terse Systems : Referential integrity scripts

February 04, 2005 23:42

Programming, ATG Dynamo

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

from the command line, and that will spit out all the tables that make up the repository. The problem is that it doesn't spit them out in order, and it spits them out with referential integrity enabled.

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.

« A good JSP editor | Home | Desktop Searches with Copernic »

name
url