Complete db schema transformation - how to test re

Question

Our database is poorly designed all the way around (we inherited it). I've reworked the schema to something useable and maintainable. Quite a few tables and columns have been dropped, many columns have moved and most tables and columns have been renamed. Some datatypes have been changed also.

I've extracted all the queries from our webapps and we've started rewriting them. Our DBA is able to migrate the old data to the new schema, we think. To be sure we need to test each query by comparing the old results with the new.

How can we test such a wholesale migration? I need to be able to specify parameters, and map old tables/columns to new tables/columns. With hundreds of queries this is a daunting task. I could write something myself but I already have a lot of demands on my time so using an existing tool is preferable.

Thanks!

Answer 1

I've had to do this ... and well it was easy because i rewrote the entire application ;)

Many queries sounds like basic operations such as select,insert,updates have not been abstracted in functions - maybe that can help clean up the mess before adapting.

Now for the testing:

You need a test script that will a) run all your queries b) store output of all selects for comparison

1. backup your test db @ state 0, clear the general query log

2. play around your application using all the deletes, selects and updates,

3. copy paste that log, take every single select and precede it with a "Create table temptable_xyz" (or of course SELECT into temptable_xyz .. depends on the available syntax)

4. run on both databases, test db @ state 0 and test db @ state 0 after migration script

5. compare

This should do it if you can make sure you used every feature in every app.

GL - nothing like making existing stuff better ;)

Answer 2

This would be my approach:

1. Restore a test db that has data, run all the known queries.
2. Restore another test db, run all the new queries.

3. Create a sql script that joins each database's table and compare the results. This could be done off information_schema or other system tables (depending on the vendor.)

   insert into temp table select (select count(1) from db1..name) , (select count(1) from db2..name) , (Select count(1) from db1.name t1 join db2.name t2 on t1.col1 = t2.col1 and t1.colx = t2.colx) , tablename

You could then run through the queries that have the tablename in the query. It would give you starting point of where to look.

Answer 3

Sometimes simple solutions do the job.

If it is just SELECTs, you could just put the new and old queries in text files, run them with a script and diff the output.

cd newqueries
for queryfile in *; do
    psql -f $queryfile migrateddb > /tmp/newresult
    psql -f ../oldqueries/$queryfile olddb > /tmp/oldresult

    if ! diff /tmp/oldresult /tmp/newresult; then
        echo "Difference in $queryfile"
        exit 1
    fi
done

Or you could write a unit test based result comparison