When working on projects involving Conversion of data or Migration/Moving of data from a legacy database. The testing effort is enormous and testing takes a lot of time, some test automation can help this effort.
Since data is moved/changed from a source database to destination database, we can write sql which should provide results for the types of tests you want to perform, for example: write a sql to give us number of customers, write a sql to give us account balance for a specific account.
These sqls can be run on your source database as well as your destination database and the results can be compared programmatically, providing us an easy way to compare the state of the database before and after conversion/migration. This testing can be run through a CI engine to make it a regression test suite.
Here is an example implementation using ruby,
We have two databases SOURCE and DESTINATION and two sql files names source.sql and destination.sql. The ruby program picks up sql from these two files and runs them against their database i.e. sql from source.sql is run against the SOURCE database and sql from destination.sql is run against DESTINATION database. The results of both of those sqls is compared and an failure is raised when the results do not match.
results
statement = get_sql_statement_to_execute
begin
source_statement = statement[0]
destination_statement = statement[1]
source_rows = exec_sql_in_source_return_rows(source_statement)
destination_rows = exec_sql_in_destination_return_rows(destination_statement)
result = compare_rows(source_rows, destination_rows, destination_statement, source_statement)
results << result
rescue
Log.log("Could not process: "+statement)
end
if (results.size > 0)
Log.log("Results do not match in source and destination")
end
The sample ruby code above shows how the solution can be implemented, thus enabling automation of database conversion/migration testing.