Perl Code Kata: Testing Databases

by Stevan Little
February 10, 2005

Testing code that uses a database can be tricky. The most common solution is to set up a test database with test data and run your tests against this. This, of course, requires bookkeeping code to keep your test database in the proper state for all your tests to run without adversely affecting one another. This can range from dropping and recreating the test database for each test, to a more granular adding and deleting at the row level. Either way, you are introducing non-test code into your tests that open up possibilities for contamination. Ultimately, because you have control over the environment in which your tests run, you can manage this despite the occasional headache.

The real fun only starts when you decide that you should release your masterpiece unto the world at large. As any CPAN author will tell you, it is absolutely impossible to control the environment other people will run your code in once you release it. Testing database code in such a hostile environment can be frustrating for both the module developer and the module installer. A common approach is to allow the user to specify the specific database connection information as either environment variables or command-line arguments, skipping the tests unless those variables are present. Another approach is to use the lightweight and very portable SQLite as your test database (of course, testing first that the user has installed SQLite). While these solutions do work, they can often be precarious, and in the end will increase the number of possible installation problems you, as module author, could face.

What is a module author to do?

DBD::Mock Testing Kata

This code kata introduces an alternate approach to testing database code, that of using mock-objects, and specifically of using the DBD::Mock mock DBI driver. Before showing off any code, I want to explain the basic philosophy of Mock Objects as well as where DBD::Mock fits in.

Related Reading Computer Science & Perl Programming Best of The Perl Journal Table of Contents Sample Chapter Read Online--Safari Search this book on Safari:   Only This Book All of Safari Code Fragments only

What are Mock Objects?

When writing unit tests, it is best to try to isolate what you are testing as much as possible. You want to be sure that not only are you only testing the code in question, but that a bug or issue in code outside what you are testing will not introduce false negatives in your tests. Unfortunately, this ideal of a completely decoupled design is just an ideal. In real-world practice, code has dependencies that you cannot remove for testing. This is where Mock Objects come in.

Mock Objects are exactly what they sound like; they are "mocked" or "fake" objects. Good polymorphic thought says that you should be able to swap out one object for another object implementing the same interface. Mock Objects take advantage of this by allowing you to substitute the most minimally mocked implementation of an object possible for the real one during testing. This allows you to concentrate on the code being tested without worrying about silly things, such as whether your database is still running or if there is a database available to test against.

Where Does DBD::Mock Fit In?

DBD::Mock is a mock DBI Driver that allows you to test code which uses DBI without needing to worry about the who, what, when, and where of a database. DBD::Mock also helps to reduce the amount of database bookkeeping code by doing away with the database entirely, instead keeping a detailed record of all the actions performed by your code through DBI. Of course, database interaction/communication is not only one way, so DBD::Mock also allows you to seed the driver with mock record sets. DBD::Mock makes it possible to fake most (non-vendor specific) database interaction for the purpose of writing tests. For more detailed documentation I suggest reading the DBD::Mock POD documentation itself.

Sample DBI Code

In the tradition of past Perl Code katas here is some simplified code to write your tests against. This code should be simple enough to understand, but also complex enough to show the real usefulness of DBD::Mock.

package MyApp::Login;

use DBI;

my $MAX_LOGIN_FAILURES = 3;

sub login {
  my ($dbh, $u, $p) = @_;
  # look for the right username and password
  my ($user_id) = $dbh->selectrow_array(
      "SELECT user_id FROM users WHERE username = '$u' AND password = '$p'"
  );
  # if we find one, then ...
  if ($user_id) {
      # log the event and return success      
      $dbh->do(
          "INSERT INTO event_log (event) VALUES('User $user_id logged in')"
      );
      return 'LOGIN SUCCESSFUL';
  }
  # if we don't find one then ...
  else {
      # see if the username exists ...
      my ($user_id, $login_failures) = $dbh->selectrow_array(
          "SELECT user_id, login_failures FROM users WHERE username = '$u'"
      );
      # if we do have a username, and the password doesnt match then
      if ($user_id) {
          # if we have not reached the max allowable login failures then 
          if ($login_failures < $MAX_LOGIN_FAILURES) {
              # update the login failures
              $dbh->do(qq{
                  UPDATE users 
                  SET login_failures = (login_failures + 1)
                  WHERE user_id = $user_id
              });
              return 'BAD PASSWORD';                  
          }
          # otherwise ...
          else {
              # we must update the login failures, and lock the account
              $dbh->do(
                  "UPDATE users SET login_failures = (login_failures + 1), " .
                  "locked = 1 WHERE user_id = $user_id"
              );                                                              
              return 'USER ACCOUNT LOCKED';
          }
      }
      else {
          return 'USERNAME NOT FOUND';
      }
  }
}

There are four distinct paths through this code, each one resulting in one of the four return messages; LOGIN SUCCESSFUL, BAD PASSWORD, USER ACCOUNT LOCKED, and USERNAME NOT FOUND. See if you can write tests enough to cover all four paths. Feel free to use Devel::Cover to verify this.

Armed with your knowledge of DBD::Mock, go forth and write tests! The next page describes DBD::Mock in more detail and gives some strategies for writing the appropriate tests. You should spend between 30 and 45 minutes writing the tests before continuing.

Pages: 1, 2

Next Page