A Framework for automated DB testing

Mutant has asked for the wisdom of the Perl Monks concerning the following question:

I'm attempting to put together a framework for testing a DB application. I've done this before for other apps, but am wondering if there's a cleaner way of doing things.

The scenario is I'm building a web-based app, and for tests I want to have a real DB with a known set of data to test against. I need to be able to:

Easily revert to this known state
Deal with changes in the schema
Add more data to the known state as I add more tests (since the data structures are complex, this will mostly be done by adding things via the app itself).

I don't need it to be DB independant (I'm using MySQL).

My current approach is to script a reload of the DB schema, and my known set of data as insert statements.

Each time I update the schema, I have to refresh the DB as if I was about to run the tests (but don't actually run the tests, since they may add data), apply the patch (I keep each change to the schema as a patch file containing alter table, etc. statements), then re-dump the known set of data. This makes sure the insert statements will work with the new schema.

To add more data, I also have to refresh the DB, then add data using the app, and re-dump it.

One enhancement I'm looking at is to use YAML to store the data set (which I think I can do with SQL::Translator). This should make small changes and tweaks fairly easy. But apart from that, does anyone have any suggestions on how I can improve this?

Comment on A Framework for automated DB testing

Replies are listed 'Best First'.
Re: A Framework for automated DB testing by gam3 (Curate) on Nov 03, 2006 at 14:17 UTC
I use mysqldump to do my testing. If I already have a database I start with `mysqldump -d database > test_database.schema echo create database test_database \| mysql mysql test_database < test_database.schema` [download] Now when I am creating/updating tests I can: `mysqldump test_database > test_database.schema` [download] Then I can recursively do `mysql test_database < test_database.schema mysql test_database < alter_database mysql test_database < add_test_data ./Build test` [download] until I am happy with the tests. Then when I install of the new code base I should only need to run `mysql database < alter_database` [download] as an additional step. I then use this code to make sure that the databases have the same structure. use Algorithm::Diff; my @a = grep( !/Database:/, split('\n', `mysqldump -d tasker`)); my @b = grep( !/Database:/, split('\n', `mysqldump -d test_tasker`)); my $diff = Algorithm::Diff->new( \@a, \@b ); while($diff->Next()) { next if $diff->Same(); next if /Database:/; print "< $_\n" for $diff->Items(1); print "> $_\n" for $diff->Items(2); } [download] -- gam3 A picture is worth a thousand words, but takes 200K.	[reply] [d/l] [select]
Re^2: A Framework for automated DB testing by Mutant (Priest) on Nov 03, 2006 at 15:02 UTC
Hi.. that's similar to what I'm doing now.. it just seems a little long winded to me, especially when changing the schema... but I guess there's not really an easier way of doing it.	[reply]
Re: A Framework for automated DB testing by derby (Abbot) on Nov 03, 2006 at 14:08 UTC
This doesn't exactly answer your question but there's a whole chapter about testing databases in Perl Testing - A Developer's Notebook. -derby	[reply]
Re: A Framework for automated DB testing by tinita (Parson) on Nov 03, 2006 at 18:48 UTC
about comparing two schemas: try out mysqldiff. it generates necessary alter-table statements, too.	[reply]
Re: A Framework for automated DB testing by duckyd (Hermit) on Nov 03, 2006 at 19:29 UTC
I would recommend not depending on a fixed set of data, but rather having your tests setup the data they're going to work with, and clean up after themselves. Forcing them to do the setup means you'll be inclined to write your main code such that that's easy, and when you need to change your test data it's right alongside your test code. The cleanup part can be a little trickier, mainly because you may or not want to cleanup when you fail (if you want to inspect the data at the time of failure, you'll want to leave it, but most of the time you won't need it and you'll want it cleaned up for you).	[reply]
Re: A Framework for automated DB testing by wjw (Priest) on Nov 03, 2006 at 15:31 UTC
I think I might consider making use of CVS or SVN or something similar in a case like this. One could use tags to get to known states of both data and schema. Your known state is a moving target here as you develop, so tagging across the schema/data sets would allow you to achieve numerous known past states. Should not be too tough to script a series of archive/db events to get to a known state using the tags. ...the majority is always wrong, and always the last to know about it...	[reply]