Slash Boxes
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Ovid (2709)

  (email not shown publicly)
AOL IM: ovidperl (Add Buddy, Send Message)

Stuff with the Perl Foundation. A couple of patches in the Perl core. A few CPAN modules. That about sums it up.

Journal of Ovid (2709)

Monday July 09, 2007
04:46 AM

Test::Database to the CPAN?

[ #33755 ]

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;

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.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
More | Login | Reply
Loading... please wait.
  • 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.


  • 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?

    • 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.

  • 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 o
  • Perhaps your module could work with DBIx::Admin::CreateTable, which handles Oracle, MySQL and Postgres.
    • 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!).