What a good strategy or tool to unit test database conversions? (Not unit testing databases, but the conversion)

Question

I am working on a project where we are converted the old system to a brand new system. We are currently converted from the database of the old system to the database of the new system, and a team is working on creating a set of maps and transforms to run at the end of development to convert over the old system.

I would like to implement unit testing, so that we can know that new changes to the maps and transforms, don't break existing work, and we can use a test driven development approach.

Any suggestions or ideas on how to do this? I don't imagine there is a framework for this, but if there is I should would like to know about it.

(Updated)

This is an Oracle database, that is probably useful information.

Answer 1

This is Oracle specific, but if that is your database of choice, then you should try Ounit.

Answer 2

The first thing you need to do in this situation is decide what your 'Unit' is. And then figure out exactly what its functionality should be. After that you can test it like anything else.

I guess you will want to test that data in your old system is still in the new system, which strikes me as a little big to be called a unit test. You will probably want to insert data into your old database. Apply the mapping. Then select it from your new database, this at least ensures you can get back what you put in. This will need to be done with every entity or set of entities that you want to test.

Answer 3

You could try dbUnit or SqlUnit. While MS-specific, this article may be helpful in understanding some DB testing patterns.

Caveat: I haven't used any of these and so cannot make any recommendations.

Answer 4

I've done this sort of thing in the past; I don't see how testing your whole migration can be a "unit test". Maybe you can consider "Total of all sales in the new database must the old database". Unit tests are not really the go here, unless you are writing individual PL/SQL functions to assist with the conversion (ie. converting from one way of storing a column to another). These should be trivial to unit test.

Some of the tests that I have done in the past:-

• Report Comparision Usually there are at least a couple of reports that carry over from one system to the other. Compare the output of these. Half the time you'll find bugs in the old system.
• Totals Calculate totals and hash totals on your data to determine if it is correct. Split and dice your data in different ways.
• Spot Test Investigate in great details of random records.

As a developer, it is very useful to have an abundance of test data as you write the system. Unit tests that the developers write should be run against both newly created data as well as historic data that you have imported.

Answer 5

This doesn't really lend itself to unit testing.

The "new" database is usually logically inconsistant until all the transformations are complete. Also most errors will be due to inconsistencies in the mappings and the way they handle nulls foriegn keys etc.

I would recommend you define a specific unit test sets of the old and new tables with a limited set of data to test specific cases and a test for the correctness of the new tables, but, you still need to run the whole set of transformations/mapping every time you change something. As you are running on a small set of data this should not be too painful, plus, you can add in specific data to test for known/possible bugs.