Complete db schema transformation - how to test rewritten queries?

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!

Fireplace

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 ;)

Undrape

That's essentially what I ended up doing. I wrote an app to test before and after queries and to compare the results. The queries, old-to-new mapping, and assertions are specified in XML. – Fireplace

Answer 2

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

Daphnedaphnis

Unfortunately we've got inserts, updates, and deletes as well. – Fireplace

Last time I just changed the database and went manually through all of the queries. Search and replace helped. I tried to test the application but some migration bugs were not found until later. – Daphnedaphnis

jkj, that may be the future for us too, as depressing as it is. We have a tester but he's incompetent and retiring in a few months so now it really falls to the developers. Thanks. By the way, I tried to mention you in the comment but it kept getting filtered out - must be your name is too short? – Fireplace

I learned why your name was stripped...it's by design: meta.stackexchange.com/questions/97098/… At least I learned something today! – Fireplace

Yeah. Sorry I couldn't help you out. Hope someone else comes up with proper tools. Can I ask which language the web app is btw? – Daphnedaphnis

Java, with Tomcat over Oracle. – Fireplace

Just use php+mysql and you can forget all that foreign key, transaction, data integrity and personal sanity nonsense ;) – Daphnedaphnis

Answer 3

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.

Epicedium