Using DBD::Mock for testing DBI related code

We recently reworked a large mod_perl application which we have been selling regularly to our clients, and I have been working on the test suite for it whenever I get a chance. A little while ago, I decided to use mock objects to test the database functionality and in particular use DBD::Mock. The more I work with DBD::Mock, the more I find it a very useful tool. So I thought I would share a basic example of how it works with you other monks.

Now in the interest of full disclosure, I have submitted a few patches to DBD::Mock, and lachoy listed me as one of the authors. But truth be told, this is really his baby. Now onto the example.

My most recent test was for a simple event logging class. The class really has two methods; logEvent and getEventReport, the names I think are pretty self explanitory. The simplified code for this module is below:

package EventLogger;

use strict;
use warnings;

sub new {
    my ($class, $dbh) = @_;
    return bless { dbh => $dbh }, $class;
}

sub logEvent {
    my ($self, $session_id, $event) = @_;
    $self->{dbh}->do("INSERT INTO event_table (session_id, datestamp, 
+event) VALUES('$session_id', NOW(), '$event')");
}

sub getEventReport {
    my ($self, $month, $year) = @_;
    my ($num_logins) = $self->{dbh}->selectrow_array("SELECT COUNT(*) 
+FROM event_table WHERE event = 'Successful Login' AND MONTH(date_crea
+ted) = $month AND YEAR(date_created) = $year");
    my ($standard_reports) = $self->{dbh}->selectrow_array("SELECT COU
+NT(*) FROM event_table WHERE event = 'Standard Report' AND MONTH(date
+_created) = $month AND YEAR(date_created) = $year");    
    my ($custom_reports) = $self->{dbh}->selectrow_array("SELECT COUNT
+(*) FROM event_table WHERE event = 'Custom Report' AND MONTH(date_cre
+ated) = $month AND YEAR(date_created) = $year");       
    my $total_reports = $standard_reports + $custom_reports; 
    my $reports_per_login = ($total_reports / $num_logins);
    return (
        'Successful Logins' => $num_logins,
        'Standard Reports'  => $standard_reports,
        'Custom Reports'    => $custom_reports,
        'Total Reports'     => $total_reports,
        'Reports Per Login' => $reports_per_login
    );    
}

1;
[download]

Without interacting with a database, there is little I can test here other than a few can_ok's to see if my instance can call the expected methods.

As far as I am concerned, what I want to test is that my class does what's expected of it (sends SQL to the database and receives results back). For the purposes of the test, I care very little if the database itself actually inserts the values (thats MySQL's problem). And I am even less interested in having my test actually test those insertions (either manually or by writing enough code to put meaningful/useful information into the database). To me that is counter productive and not really adding anything to the test, just adding to what needs to be maintained.

That is where DBD::Mock comes in.

#!/usr/bin/perl

use strict;
use warnings;

use Test::More tests => 13;

BEGIN {
    use_ok('EventLogger');
}

use DBI;

my $dbh = DBI->connect('DBI:Mock:', '', '');

## ----------------------------------------------
## configure DBD::Mock for these tests
## ----------------------------------------------

# add the results for the inserts in logEvent
$dbh->{mock_add_resultset} = [[ 1 ]];
$dbh->{mock_add_resultset} = [[ 2 ]];
$dbh->{mock_add_resultset} = [[ 3 ]];
# add results for the selects in getEventReport
$dbh->{mock_add_resultset} = [[ "count(*)" ], [ 10 ]];
$dbh->{mock_add_resultset} = [[ "count(*)" ], [ 5 ]];
$dbh->{mock_add_resultset} = [[ "count(*)" ], [ 8 ]];            
                
## ----------------------------------------------
## end configure DBD::Mock
## ----------------------------------------------    

can_ok("EventLogger", 'new');
my $logger = EventLogger->new($dbh);
isa_ok($logger, 'EventLogger');

my $session_id = '0123456789';

can_ok($logger, 'logEvent');

# test each one of our even types
$logger->logEvent($session_id, 'Successful Login');
$logger->logEvent($session_id, 'Standard Report');
$logger->logEvent($session_id, 'Custom Report');

can_ok($logger, 'getEventReport');
my %report = $logger->getEventReport(8, 2004);

# check the report that was
# generated by the mock results
is_deeply(
        \%report,
        {
        'Successful Logins' => 10,
        'Standard Reports'  => 5,
        'Custom Reports'    => 8,
        'Total Reports'     => 13,
        'Reports Per Login' => 1.3
        },
        '... this is our report'
        );

## ----------------------------------------------
## now check DBD::Mock's query history
## ----------------------------------------------

# now we get the history of our driver
my $history = $dbh->{mock_all_history};

# check how many statements we executed
cmp_ok(scalar @{$history}, '==', 6, '... we executed 6 statements');

# check each of the statements we 
# expected to execute

is($history->[0]->statement(), 
   "INSERT INTO event_table (session_id, datestamp, event) VALUES('012
+3456789', NOW(), 'Successful Login')",
   '... this the expected statement');    

is($history->[1]->statement(), 
   "INSERT INTO event_table (session_id, datestamp, event) VALUES('012
+3456789', NOW(), 'Standard Report')",
   '... this the expected statement');    
   
is($history->[2]->statement(), 
   "INSERT INTO event_table (session_id, datestamp, event) VALUES('012
+3456789', NOW(), 'Custom Report')",
   '... this the expected statement');             

is($history->[3]->statement(), 
   "SELECT COUNT(*) FROM event_table WHERE event = 'Successful Login' 
+AND MONTH(date_created) = 8 AND YEAR(date_created) = 2004", 
   '... this the expected statement');        
    
is($history->[4]->statement(), 
   "SELECT COUNT(*) FROM event_table WHERE event = 'Standard Report' A
+ND MONTH(date_created) = 8 AND YEAR(date_created) = 2004", 
   '... this the expected statement');    
    
is($history->[5]->statement(), 
   "SELECT COUNT(*) FROM event_table WHERE event = 'Custom Report' AND
+ MONTH(date_created) = 8 AND YEAR(date_created) = 2004", 
   '... this the expected statement');                                
+                            

1;
[download]

With this test I am able to check the number of SQL statements I ran and that my class created the SQL I expected it to. Since I can also feed it results, I can test that it calculates the value for my report correctly as well. The result is that without having to set-up or manage a test database, I was able to get 100% coverage on this test. Here is the Devel::Cover report.

---------------------------- ------ ------ ------ ------ ------ ------
File                           stmt branch   cond    sub   time  total
---------------------------- ------ ------ ------ ------ ------ ------
EventLogger.pm                100.0    n/a    n/a  100.0    7.0  100.0
test.t                        100.0    n/a    n/a  100.0   93.0  100.0
Total                         100.0    n/a    n/a  100.0  100.0  100.0
---------------------------- ------ ------ ------ ------ ------ ------

This is only a small part of what DBD::Mock can do. For instance, something that can be very difficult to test is how your application will respond to a event like the database crashing. You can use the attribute mock_can_connect to test this. If the validity or dialect of your SQL is important to your test, you can use the mock_add_parser attribute to add your own SQL parser/checker. There are a number of other attributes as well, which can be used to check everything from what parameters were bound, to whether or not your result set has been depleted.

And quite possibly the coolest part of DBD::Mock is that it is contained within a single (pure-perl) file. Which means you can easily include it in your module, allowing it to be tested without the need for a test database to be installed/configured/compiled by the user.

I know for me, it has greatly simplified the testing of much of the database portion of my application.

-stvn

Comment on Using DBD::Mock for testing DBI related code Select or Download Code

Back to Meditations