Automation

From Productiveprogrammer

[edit] A story about automation

We recently moved a bunch of tables and stored procs into a schema, so we had to update the stored procs, to reference the tables & sprocs by their new names. We did it with a ruby script, which looked at all the sproc files (*.PRC, a local convention), checked that the filename matched the sproc name, and swapped out all the table & sproc names. Instead of parsing SQL to find table names, we used a map of old and new names for tables and sprocs, which meant we knew exactly what was changing. We have a spreadsheet for tracking database updates, so from the same script, we generated a .csv file of our changes, to paste into the spreadsheet. We even had a clean-up script that deleted generated files, and ran svn revert. As we tackled each new part of the task, we found a way to fit it into the script, and the work went quickly & smoothly.

One often-missed benefit of this approach is that it's repeatable. If, after running the script a few times, you realize your requirements are wrong, you can change the script to incorporate the change more cheaply than by hand.