All the Perl that's Practical to Extract and Report

Test::Database to the CPAN?

Ovid on 2007-07-09T09:46:33

Here's something I've written for work and it's been extremely useful:

use Test::Database;
my $database = Test::Database->new;
my $dbh      = $database->dbh;
$database->load_static_data;

What this does is read a config file which provides connection information for a database and the first thing it does is build a brand new test database. Internally, it instantiates a $dbh and loads a schema file, using Template Toolkit. The beginning of the file looks something like this:

DROP DATABASE IF EXISTS `[% database %]`;
CREATE DATABASE `[% database %]`;
use `[% database %]`;

CREATE TABLE `accounts` (
...

The load_static_data() method reads another SQL file which adds the static (non-changing) data to the database. This, of course, is optional and that's why it's a separate command. Then you can use your test database all you like. Further, when $database goes out of scope, the database is dropped -- but only if it's the parent process. Forking children and having them drop the database is disappointing.

Also, because you can specify your own test database name, every developer can easily use database tests and not worry about clashing with someone else.

It needs work. Specifically, it needs to not be *nix specific or MySQL specific. I'm thinking that the easiest thing to do would be to make it a base class and provide instructions on how to override certain methods.

Of course, it has helper methods for dumping table information and applying arbitrary SQL from files. It's saved us a huge amount of trouble at work and has the nice benefit that if you have a database server running, you're not just hacking up a quick SQLite database for testing (as many folks do).

My boss has agreed that it's not a core-business tool, so we can potentially release this to the CPAN. I'm surprised that I haven't found anything like this already.

Of course, there's also the matter of spare tuits. If anyone is willing to, I'd happily send the code along for you to fix up for a CPAN release under your own name.

Shameless plug - Test::TempDatabase :)

bosu on 2007-07-09T11:50:37

Hi,

There is Test::TempDatabase module on CPAN which is unix specific and PostgreSQL only :)

Unfortunately, I don't work with MySQL so I can be of no help porting it.

Boris.

Why MySQL?

Alias on 2007-07-09T12:24:28

Why exactly is it MySQL specific? It's just templated SQL for the setup, so I assume that's no big deal...

what's the main problem stopping multi-database support?

Re:Why MySQL?

Ovid on 2007-07-09T12:38:15

There's no significant problem in fixing it to support multiple databases. It's a matter of refactoring a couple of things to make anything database-specific be easy to override in a subclass.

interesting

markjugg on 2007-07-09T17:57:46

I'm been thinking about adopting this kind of solution more lately. I seem to recall that Rails has something like this built-in.

A related thing I've done is store to "fixture" data (default data) in YAML files, instead of as a bunch of SQL "insert" statements. I find this easier to read and edit, because the name/value pairs line up.

I have a small script "yml2insert" which filters the YAML syntax back into SQL insert statements.

I've thought about releasing this bit, but it makes most sense to me as part of a complete "test database" solution, which I don't have now.

Also commonly, I use pairs of insert/delete routines for test data. The insert routines automatically add the stored IDs to a package-scope array. The package includes an END block, which calls the "delete" routine.

The result is that I have an easy to create test data in a development database that is automatically cleaned up, without using a separate test db.

For projects where the DELETE statements take a long time to run, I have the delete routine add the IDs to tracking table, and a cron script does the actual deletes in the middle of the night.

Tie in with DBIx::Admin::CreateTable?

Ron Savage on 2007-07-10T00:48:01

Perhaps your module could work with DBIx::Admin::CreateTable, which handles Oracle, MySQL and Postgres.

Re:Tie in with DBIx::Admin::CreateTable?

Ovid on 2007-07-10T08:22:22

That's an interesting thought. Right now, I bulk load things through large SQL scripts. They give me fine-grained control, but your suggestion would make it easier to test code on a variety of databases (a great tool for migration!).

Re:Tie in with DBIx::Admin::CreateTable?

Ron Savage on 2007-07-26T02:09:22

Oops. I can't find DBIx::Admin::CreateTable on CPAN! I'll do something about that. In the meantime, try: http://savage.net.au/Perl-modules.html