About a year ago, I realized there was no good way to test code that claims to be database independent. Even testing code that needs a database is difficult: most modules either use SQLite (but they don't test the database independence) or request some environment variables defining the DSN to be setup (but these are unlikely to exist on a CPAN tester's machine, though).
So I decided to write Test::Database.
It took me a while to get right, but thanks to the isolation and focus that the Birmingham QA Hackathon from March 2009 gave me (and the completely broken state I put the module into at that time), I managed to come up with a satisfying version 1.00 in July this year (the module is now at version 1.06).
Basically, what Test::Database does is simply this: it gives you the information you need to connect to a database that matches your testing needs (implicitely allowing you to do whatever you want in there, including creating and dropping tables).
Test::Database has a single interface for test authors:
my @handles = Test::Database->handles( @requests );
@request is a list of "requests" for databases handles. Requests
must declare the DBD they expect, and can optionaly add version-based
limitations (only available for drivers supported by Test::Database).
Test::Database will return as many matching Test::Database::Handle objects as it can find or create, depending on the locally installed modules and the configuration of the user running the code.
You can then simply use the information from the Test::Database::Handle object:
# $handle is a Test::Database::Handle object
# get all the info you need and DIY
my ( $dsn, $user, $pass ) = $handle->connection_info();
my $dbh = DBI->connect( $dsn, $user, $pass );
# be lazy and let it do the DBI->connect( $dsn, $user, $pass ) for you
my $dbh = $handle->dbh();
So once you've added support for MySQL to your module (in addition to SQLite), you can simply edit the test script like so:
my @handles = Test::Database->handles( 'SQLite', 'mysql' );
And your test suite will pick up a MySQL handle wherever there's one available.
Many thanks to SUKRIA, who'll be the first CPAN author to really use Test::Database to test a CPAN module (namely, Coat::Persistent, when the latest release finally hits CPAN).
Well done on persisting with this module, I'm fairly sure it's going to come in very handy for a project I'm doing shortly.