Perl Before Swine

Posted By Michael on August 31, 2011

Within about the last year unit testing became a big thing at the company I’ve been working for. Although the company has been around for awhile, most of its growth has been in the last year to two which means a lot of process has been evolving to ensure quality standards on a larger team. Of course this also means we’re dealing with trying to retrofit a lot of legacy code with unit tests. It’s a joy I tell you.

Now I’m not quite one of the proselytizers of unit testing, but I’ve been forced to admit it has helped me out so it’s a tool I’ll continue to use. (On the other hand, just today someone on our team hit the shortcomings of unit testing where all his unit tests passed but his program didn’t.) Of course for our perl code our main testing tool is the great Test::More module which makes use of TAP (Test Anything Protocol). With a small make script we’ve been able to integrate these tests with Gerrit and Jenkins so they run with every commit and must pass before code can be merged.

When we first started this I found database related code to be the most irritating to test. Trying to mock it out with mock objects was too much of a pain. This is where DBD::Mock came to the rescue. If your code is written so that database handles can easily be passed to objects then using DBD::Mock takes all the pain of mocking out a database away. When that doesn’t work we make use of some homegrown code to mock specific subs and return the DBD::Mock database handle object instead of the regular dbh.

The module installs just like any other DBD and you access it with a regular DBI->connect:

my $dbh_mock = DBI->connect("DBI:Mock:", "", "");

From there you can execute any database statement you normally would against the handle. That in itself is pretty helpful as it removes any need for further mocking or actual database connectivity. It also ensures you’re not dirtying a database with unit test artifacts. But it’s far more powerful than that. To begin with you can populate the object with some result sets. It’s as easy as:

@res = (
  ['col1','col2','col3'],
  ['foo','bar','baz'],
  ['bee','baa','boo'],
);
$dbh_mock->{mock_add_resultset} = \@res;

The first set functions as column headers. Further sets function as row results which could be iterated over with$sth->fetchrow or similar. Multiple result sets can be added. The first statement requiring results will return from the first set, the second from the second set, and so on. But what if you want a result set returned for a specific statement?

As a basic example, we have some functions built around determining free space on a server so we know if we need to drop old data before loading in new data. There’s a few things that happen on the way to the statement that actually affects what we want to test, so we want to make sure the result set is returned for our specific database size query (you’ll see I’m using postgres here):

$dbh_mock->{mock_add_resultset} = {
  sql => "SELECT pg_database_size('dbname')",
  results => [["size"], [10000]]
};

Other queries can return from the general result set pool in the object, but only the size query will return this set.

Another feature I’ve found quite useful is the ability to access the history for the mock object. The history stores all the sql queries that have been executed against it and can easily be accessed through an array ref:

my $history=$dbh_mock->{mock_all_history};

Basic tricks can be performed such as testing the size of the array ( is(scalar(@{$history}), 5) ) to make sure the expected number of queries were run, or searching the array for specific queries or running is_deeply tests against the params:

is_deeply($history->[0]->{bound_params}, ['foo',5555,'2099-01-01',11]);

There’s a lot more DBD::Mock can do and I highly recommend it for anyone trying to unit test objects that deal with databases.

Categories: perl, Unit Testing
Tags: modules