In on of our major projects we store some of our business logic data as database metadata, but provide GUI interface for updates. Since clicking through the project’s GUI is much easier, than creating and verifying scripts for each tiny modification, our developers got lazy and we had to find a way to create SQL scripts to bring releases up to date. This basically means, we had to create a script generator that compares the data in source and destination databases and creates SQL INSERT, UPDATE and DELETE statements that can be used to modify the destination to source.

For the script we didn’t need to be very precise, as the generated statements are always validated manually to avoid problems. This means that there was no need to care about table constraints, but the table content comparison is enough. Still there were situations where some fields were to be ignored, and situations where the tables had composite keys.

As usual this was not a task that was planed in the project in advance, so we had to hack together a code that didn’t require too much programming. This is usually the case when one takes two strong coffees and switches to script writing mode.

The databases in question are DB2 8.x patched up to a version equivalent to DB2 9.x, the environment the script must run in was GNU Linux, Cygwin and possibly even AIX, I had about 4 hours to write the original version.

On the end I whipped up something like this.

After this script was done I just had to add the table specific invocations to another script and voila it was able to create the scripts we needed to create the deploy scripts.

The simplest case of invoking the function, when the tables are to be compared with all their fields.

compare_content $SRC_DB_ALIAS $SRC_DB_USER $SRC_DB_PASSWORD $DST_DB_ALIAS $DST_DB_USER $DST_DB_PASSWORD 'MY_SCHEMA' 'MY_TABLE' 'ID';

But the script is also good for cases, where you would like to ignore some fields for the comparison would like to conditionally compare the dbs (MY_FILTER IS NOT NULL), use composite alternate key (MY_ALTERNATE_KEY1,MY_ALTERNATE_KEY2,MY_ALTERNATE_KEY3) for identifying a row and want to use a synthetic key as primary key (NEXT VALUE FOR MY_COMPLEX_TABLE_ID_SEQUENCE) on inserts.

compare_content $SRC_DB_ALIAS $SRC_DB_USER $SRC_DB_PASSWORD $DST_DB_ALIAS $DST_DB_USER $DST_DB_PASSWORD
'MY_SCHEMA' 'MY_COMPLEX_TABLE' 'MY_ALTERNATE_KEY1,MY_ALTERNATE_KEY2,MY_ALTERNATE_KEY3' 'MY_FILTER IS NOT NULL' 'ID' 'NEXT VALUE FOR MY_COMPLEX_TABLE_ID_SEQUENCE';
For this the script will automatically create the required statements, where the updates will omit the field ID, the inserts will insert the next value for the sequence in ID, but all comparison will be performed among values that have the same alternate key.

I’m sure this is a script that has a lot to improve, but still this is a good example to show the power of script languages when it comes to hacking together a utility that saves plenty of time, and would be a real pain to create using conventional programming techniques.

If you replace the db2 commands with the equivalents of your favorite DBMS’s command line tool you might see that this can be easily adapted to your needs as well.

NB. There are some other aspects in the original script I didn’t include. Namely it’s able to compare the database structures of the DB’s using the meta data tables of DB2. Since these modifications are quite rare in our project and it’s hard to compare data for some cases, that part of the script is not finished.