Imagine that you store user information in a database. The Users module creates and fetches user information from a single table; it is a factory for User objects. Save the following code in your library directory as Users.pm:

    package Users;

    use strict;
    use warnings;

    my $dbh;

    sub set_db
    {
        my ($self, $connection) = @_;
        $dbh                    = $connection;
    }

    sub fetch
    {
        my ($self, $column, $value) = @_;

        my $sth = $dbh->prepare( 
            "SELECT id, name, age FROM users WHERE $column = ?" );

        $sth->execute( $value );

        return unless my ($id, $name, $age) = $sth->fetchrow_array();
        bless { id => $id, name => $name, age => $age, _db => $self }, 'User';
    }

    sub create
    {
        my ($self, %attributes) = @_;
        my $sth                 = $dbh->prepare(
            'INSERT INTO users (name, age) VALUES (?, ?)'
        );

        $sth->execute( @attributes{qw( name age )} );
        $attributes{id} = $dbh->last_insert_id( undef, undef, 'users', 'id' );
        bless \%attributes, 'User';
    }

    package User;

    our $AUTOLOAD;

    sub AUTOLOAD
    {
        my $self     = shift;
        my ($member) = $AUTOLOAD =~ /::(w+)z/;
        return $self->{$member} if exists $self->{$member};
    }

    1;

Note the use of the set_db() function at the start of User. It stores a single database handle for the entire class.

The Users package is simple; it contains accessors for the name, age, and id fields associated with the user. The code itself is just a thin layer around a few database calls. Testing it should be easy. Save the following test file as users.t:

    #!perl

    use strict;
    use warnings;

    use DBI;

    my $dbh = DBI->connect( 'dbi:SQLite:dbname=test_data' );
    {
        local $/ = ";
";
        $dbh->do( $_ ) while <DATA>;
    }

    use Test::More tests => 10;

    my $module = 'Users';
    use_ok( $module ) or exit;

    can_ok( $module, 'set_db' );
    $module->set_db( $dbh );

    can_ok( $module, 'fetch'  );
    my $user = $module->fetch( id => 1 );
    isa_ok( $user, 'User' );
    is( $user->name(), 'Randy', 'fetch() should fetch proper user by id' );

    $user    = $module->fetch( name => 'Ben' );
    is( $user->id(), 2, '... or by name' );

    can_ok( $module, 'create' );
    $user    = $module->create( name => 'Emily', age => 23 );
    isa_ok( $user, 'User' );
    is( $user->name(), 'Emily', 'create() should create and return new User' );
    is( $user->id(), 3, '... with the correct id' );

    _ _END_ _
    BEGIN TRANSACTION;
    DROP TABLE users;
    CREATE TABLE users (
        id   int,
        name varchar(25),
        age  int
    );
    INSERT INTO "users" VALUES(1, 'Randy', 27);
    INSERT INTO "users" VALUES(2, 'Ben', 29);
    COMMIT;

Run it with prove to see:

    $ prove users.t
    users....ok
    All tests successful.
    Files=1, Tests=10,  0 wallclock secs ( 0.17 cusr +  0.00 csys =  0.17 CPU)

The test starts off by loading the DBI module and connecting to a SQLite database with the DBD::SQLite driver. Then it reads in SQL stored at the end of the test file and executes each SQL command, separated by semicolons, individually. These commands create a users table and insert some sample data.

By the time the test calls Users->set_db(), $dbh holds a connection to the SQLite database stored in test_data. All subsequent calls to Users will use this handle. From there, the rest of the tests call methods and check their return values.

Q: This works great for testing code that uses a database, but what about code that changes information in the database?

A: Suppose that you want to prove that Users::create() actually inserts information into the database. See Testing Database Data,” next.

Q: Only simple SQL queries are compatible across databases. What if my code uses unportable or database-specific features?

A: This technique works for the subset of SQL and database use that’s portable across major databases. If your application uses things such as additions to SQL, special schema types, or stored procedures, using DBD::SQLite or DBD::AnyData may be inappropriate. In that case, testing against an equivalent database with test data or mocking the database is better. (See Using Temporary Databases" and Mocking Databases,” later in this chapter.)

Save the following file as users_db.t:

    #!perl

    use lib 'lib';

    use strict;
    use warnings;

    use DBI;

    my $dbh = DBI->connect( 'dbi:SQLite:dbname=test_data' );
    {
        local $/ = ";
";
        $dbh->do( $_ ) while <DATA>;
    }

    use Test::More tests => 4;
    use Test::DatabaseRow;

    my $module = 'Users';
    use_ok( $module ) or exit;
    $module->set_db( $dbh );
    $module->create( name => 'Emily', age => 23 );

    local $Test::DatabaseRow::dbh = $dbh;

    row_ok(
        sql   => 'SELECT count(*) AS count FROM users',
        tests => [ count => 3 ],
        label => 'create() should insert a row',
    );

    row_ok(
        table   => 'users',
        where   => [ name => 'Emily', age => 23 ],
        results => 1,
        label   => '... with the appropriate data',
    );

    row_ok(
        table => 'users',
        where => [ id => 3 ],
        tests => [ name => 'Emily', age => 23 ],
        label => '... and a new id',
    );

    _ _END_ _
    BEGIN TRANSACTION;
    DROP TABLE users;
    CREATE TABLE users (
    id   int,
    name varchar(25),
    age  int
    );
    INSERT INTO "users" VALUES(1, 'Randy', 27);
    INSERT INTO "users" VALUES(2, 'Ben', 29);
    COMMIT;

Run it with prove:

    $ prove users_db.t
    users_db....ok 1/0#     Failed test (users_db.t at line 39)
    # No matching row returned
    # The SQL executed was:
    #   SELECT * FROM users WHERE id = '3'
    # on database 'dbname=test_data'
    # Looks like you failed 1 tests of 4.
    users_db....dubious
            Test returned status 1 (wstat 256, 0x100)
    DIED. FAILED test 4
            Failed 1/4 tests, 75.00% okay
    Failed Test Stat Wstat Total Fail  Failed  List of Failed
    ----------------------------------------------------------------------------
    users_db.t     1   256     4    1  25.00%  4
    Failed 1/1 test scripts, 0.00% okay. 1/4 subtests failed, 75.00% okay.

Oops.

For some reason, the test failed. Fortunately, Test::DatabaseRow gives diagnostics on the SQL that failed. Before delving into the failure, it’s important to understand how to use the module.

Test::DatabaseRow builds on Test::Builder and exports two functions, row_ok() and not_row_ok(). Both functions take several pieces of data, use them to build and execute a SQL statement, and test its results. To run the tests, the module needs a database handle. The localization and assignment to $Test::DatabaseRow::dbh accomplishes this.

The testing functions accept two different kinds of calls. The first call to row_ok() passes raw SQL as the sql parameter to execute. This test creates a user for Emily and checks that there are now three rows in the users table with the SQL count(*) function. The second argument, tests, is an array reference of checks to perform against the returned row. In effect, this asks the question, “Is the count column in this row equal to 3?” Finally, the label parameter is the test’s description used in its output.

Passing raw SQL to row_ok() isn’t always much of an advantage over performing the query directly. The technique in the second and third calls to row_ok is better—Test::DatabaseRow generates a query from the table and where arguments and sends the query. The table argument identifies the table to query. The where argument contains an array reference of columns and values to use to narrow down the query.

There is another difference between the second and the third tests: the second passes a results argument. Test::DatabaseRow uses this as the number of results that the query should produce for the test to fail. There should be only one Emily of age 23 in the database.

Why, then, did the third test fail? Looking at the debug output, the generated SQL looks correct. Keeping the sample SQLite database around at the end of the test allows you to use the sqlite program to browse the data. If you have SQLite installed, run it with:

    $ sqlite3 test_data
    SQLite version 3.0.8
    Enter ".help" for instructions
    sqlite> select * from users;
    1|Randy|27
    2|Ben|29
    |Emily|23

Ahh, this reveals that the row for Emily has an empty id column. Looking at the table definition again (and searching the SQLite documentation), the bug is clear. SQLite only generates a unique identifier for INTEGER columns marked as primary key. Depending on the characteristics of the actual database, this may be a significant difference in the test database that might mask an actual bug in the application!

Revise the table definition in users_db.t to:

    CREATE TABLE users (
    id   INTEGER primary key,
    name varchar(25),
    age  int
    );

Then run the tests again:

    $ prove users_db.t
    users_db....ok
    All tests successful.
    Files=1, Tests=4,  0 wallclock secs ( 0.17 cusr +  0.00 csys =  0.17 CPU)

Q: What if there are other differences between the live database and the test database?

A: Sometimes the differences between a simple database such as SQLite and a larger database such as PostgreSQL or MySQL are more profound than changing the column types. In these cases, the technique shown here won’t work. Fear not, though. The next section, Using Temporary Databases,” shows another approach.

Q: Is keeping the test database around between invocations a good idea?

A: The DROP TABLE command is useful, but if there’s no database there, it can cause spurious warnings. Also, it’s bad practice to leave test-created files lying around for someone else to clean up. Although they’re sometimes helpful for debugging, most of the time they’re just clutter.

Another option is to delete the test database at the end of the test:

    END
    {
        1 while unlink 'test_data' unless $ENV{TEST_DEBUG};
    }

This will delete the database file completely, even on versioned filesystems, unless you explicitly ask for debugging. Running the test normally will leave no trace. To keep the database around, use a command such as:

                        $ TEST_DEBUG=1 prove users_db.t

Assume that you have an application named My::App (saved as lib/My/App.pm) and a file sql/schema.sql that holds your database schema and some basic data. You want to create both the live and test database tables during the installation process, and you need to know how to connect to the database to do so. One way to do this is to create a custom Module::Build subclass that asks the user for configuration information and installs the database along with the application.

Save the following file to build_lib/MyBuild.pm:

    package MyBuild;

    use base 'Module::Build';

    use DBI;
    use File::Path;
    use Data::Dumper;
    use File::Spec::Functions;

    sub create_config_file
    {
        my $self     = shift;
        my $config   = 
        {
            db_type  => $self->prompt( 'Database type ',       'SQLite'   ),
            user     => $self->prompt( 'Database user: ',      'root'     ),
            password => $self->prompt( 'Database password: ',  's3kr1+'   ),
            db_name  => $self->prompt( 'Database name: ',      'app_data' ),
            test_db  => $self->prompt( 'Test database name: ', 'test_db'  ),
        };
        $self->notes( db_config    => $config );

        mkpath( catdir( qw( lib My App ) ) );

        my $dd       = Data::Dumper->new( [ $config ], [ 'db_config' ] );
        my $path     = catfile(qw( lib My App Config.pm ));

        open( my $file, '>', $path ) or die "Cannot write to '$path': $!
";

        printf $file <<'END_HERE', $dd->Dump();
    package My::App::Config;

    my $db_config;
    %s

    sub config
    {
        my ($self, $key) = @_;
        return $db_config->{$key} if exists $db_config->{$key};
    }

    1;
    END_HERE
    }

    sub create_database
    {
        my ($self, $dbname) = @_;
        my $config          = $self->notes( 'db_config' );
        my $dbpath          = catfile( 't', $dbname );

        local $/            = ";
";
        local @ARGV         = catfile(qw( sql schema.sql ));
        my @sql             = <>;

        my $dbh             = DBI->connect(
            "DBI:$config->{db_type}:dbname=$dbpath",
            @$config{qw( user password )}
        );
        $dbh->do( $_ ) for @sql;
      }

    sub ACTION_build
    {
        my $self   = shift;
        my $config = $self->notes( 'db_config' );
        $self->create_database( $config->{db_name} );
        $self->SUPER::ACTION_build( @_ );
    }

    sub ACTION_test
    {
        my $self   = shift;
        my $config = $self->notes( 'db_config' );
        $self->create_database( $config->{test_db} );
        $self->SUPER::ACTION_test( @_ );
    }

    1;

Save the following file to Build.PL:

    #!perl

    use strict;
    use warnings;

    use lib 'build_lib';
    use MyBuild;

    my $build = MyBuild->new(
        module_name    => 'My::App',
        requires       =>
        {
            'DBI'         => '',
            'DBD::SQLite' => '',
        },
        build_requires =>
        {
            'Test::Simple' => '',
        },
    );

    $build->create_config_file();
    $build->create_build_script();

Now run Build.PL:

    $ perl Build.PL
    Database type  [SQLite] 
    SQLite
    Database user:  [root] 
    root
    Database password:  [s3kr1+] 
    s3kr1+
    Database name:  [app_data] 
    app_data
    Test database name:  [test_db] 
    test_db
    Deleting Build
    Removed previous script 'Build'
    Creating new 'Build' script for 'My-App' version '1.00'

Then build and test the module as usual:

    $ perl Build
    Created database 'app_data'
    lib/My/App/Config.pm -> blib/lib/My/App/Config.pm
    lib/My/App.pm -> blib/lib/My/App.pm

There aren’t any tests yet, so save the following as t/myapp.t:

    #!perl

    BEGIN
    {
        chdir 't' if -d 't';
    }

    use strict;
    use warnings;

    use Test::More 'no_plan'; # tests => 1;

    use DBI;
    use My::App::Config;

    my $user    = My::App::Config->config( 'user'     );
    my $pass    = My::App::Config->config( 'password' );
    my $db_name = My::App::Config->config( 'test_db'  );
    my $db_type = My::App::Config->config( 'db_type'  );

    my $dbh     = DBI->connect( "DBI:$db_type:dbname=$db_name", $user, $pass );

    my $module  = 'My::App';
    use_ok( $module ) or exit;

Run the (simple) test:

    $ perl Build test
    Created database 'test_db'
    t/myapp....ok                                                                
    All tests successful.
    Files=1, Tests=1,  0 wallclock secs ( 0.20 cusr +  0.00 csys =  0.20 CPU)

The initial build asked a few questions about the destination database before creating Build.PL. The MyBuild::create_config_file() method handles this, prompting for input while specifying sane defaults. If the user presses Enter or runs the program from an automated session such as a CPAN or a CPANPLUS shell, the program will accept the defaults.

More importantly, this also created a new file, lib/My/App/Config.pm. That’s why running perl Build copied it into blib/.

Both perl Build and perl Build test created databases, as seen in the Created database... output. This is the purpose of the MyBuild::ACTION_build() and MyBuild::ACTION_test() methods, which create the database with the appropriate name from the configuration data. The former builds the production database and the latter the testing database. If the user only runs perl Build, the program will not create the test database. It will create the test database only if the user runs the tests through perl Build test.

MyBuild::create_database() resembles the SQL handler seen earlier in Shipping Test Databases.”

At the end of the program, the test file loads My::App::Config as a regular module and calls its config() method to retrieve information about the testing database. Then it creates a new DBI connection for that database, and it can run any tests that it wants.

Q: What if the test runs somewhere without permission to create databases?

A: That’s a problem; the best you can do is to bail out early with a decent error message and suggestions to install things manually. You can run parts of your test suite if you haven’t managed to create the test database; some tests are better than none.

Q: Is it a good idea to use fake data in the test database?

A: The further your test environment is from the live environment, the more difficult it is to have confidence that you’ve tested the right things. You may have genuine privacy or practicality concerns, especially if you have a huge dataset or if your test data includes confidential information. For the sake of speed and simplicity, consider testing a subset of the live data, but be sure to include edge cases and oddities that you expect to encounter.

InsertWrapper is a simple module that logs database connections and inserts, perhaps for diagnostics or an audit trail while developing. If it cannot connect to a database—or if the database connection goes away mysteriously—it cannot do its work, so it throws exceptions for the invoking code to handle.

Save the following example in your library directory as InsertWrapper.pm:

    package InsertWrapper;

    use strict;
    use warnings;

    use DBI;

    sub new
    {
        my ($class, %args) = @_;
        my $dbh            = DBI->connect(
            @args{qw( dsn user password )},
            { RaiseError => 1, PrintError => 0 }
        );

        my $self = bless { dbh => $dbh, logfh => $args{logfh} }, $class;
        $self->log( 'CONNECT', dsn => $args{dsn} );
        return $self;
    }

    sub dbh
    {
        my $self = shift;
        return $self->{dbh};
    }

    sub log
    {
        my ($self, $type, %args) = @_;
        my $logfh                = $self->{logfh};

        printf {$logfh} "[%s] %s
", scalar( localtime() ), $type;

        while (my ($column, $value) = each %args)
        {
            printf {$logfh} "	%s => %s
", $column, $value;
        }
    }

    sub insert
    {
        my ($self, $table, %args) = @_;
        my $dbh                   = $self->dbh();
        my $columns               = join(', ', keys %args);
        my $placeholders          = join(', ', ('?') x values %args);
        my $sth                   = $dbh->prepare(
            "INSERT INTO $table ($columns) VALUES ($placeholders)"
        );

        $sth->execute( values %args );
        $self->log( INSERT => %args );
    }

    1;

The important tests are that connect() and insert() do the right thing when the database is present as well as when it is absent, and that they log the appropriate messages when the database calls succeed. Save the following code as insert_wrapper.t:

    #!perl

    use strict;
    use warnings;

    use IO::Scalar;

    use Test::More tests => 15;
    use DBD::Mock;
    use Test::Exception;

    my $module      = 'InsertWrapper';
    use_ok( $module ) or exit;

    my $log_message = '';
    my $fh          = IO::Scalar->new( $log_message );
    my $drh         = DBI->install_driver( 'Mock' );

    can_ok( $module, 'new' );

    $drh->{mock_connect_fail} = 1;

    my %args = ( dsn => 'dbi:Mock:', logfh => $fh, user => '', password => '' );
    throws_ok { $module->new( %args ) } qr/Could not connect/,
        'new() should fail if DB connection fails';

    $drh->{mock_connect_fail} = 0;
    my $wrap;
    lives_ok { $wrap = $module->new( %args ) }
        '... or should succeed if connection works';
    isa_ok( $wrap, $module );

    like( $log_message, qr/CONNECT/,            '... logging connect message' );
    like( $log_message, qr/	dsn => $args{dsn}/, '... with dsn'               );
    $log_message = '';

    can_ok( $module, 'dbh' );
    isa_ok( $wrap->dbh(), 'DBI::db' );

    can_ok( $module, 'insert' );
    $wrap->dbh()->{mock_can_connect} = 0;

    throws_ok { $wrap->insert( 'users', name => 'Jerry', age => 44 ) }
        qr/prepare failed/,
        'insert() should throw exception if prepare fails';

    $wrap->dbh()->{mock_can_connect} = 1;
    lives_ok { $wrap->insert( 'users', name => 'Jerry', age => 44 ) }
        '... but should continue if it succeeds';

    like( $log_message, qr/INSERT/,          '... logging insert message' );
    like( $log_message, qr/	name => Jerry/, '... with inserted data'     );
    like( $log_message, qr/	age => 44/,     '... for each column'        );

Then run it with prove:

    $ prove insert_wrapper.t
    insert_wrapper....ok
    All tests successful.
    Files=1, Tests=15,  0 wallclock secs ( 0.22 cusr +  0.02 csys =  0.24 CPU)

One difference between InsertWrapper and the previous examples in this chapter is that this module creates its own database connection. It’s much harder to intercept the call to DBI->connect() without faking the module (see "Mocking Modules" in Chapter 5). Fortunately, the DBD::Mock module provides a mock object that acts as a database driver.

The test starts by setting up the testing environment and creating an IO::Scalar object that acts like a filehandle but actually writes to the $log_message variable. Then it loads DBD::Mock and tells the DBI to consider it a valid database driver.

InsertWrapper::new() connects to the database, if possible, setting the RaiseError flag to true. If the connection fails, DBI will throw an exception. The constructor doesn’t handle this, so any exception thrown will propagate to the calling code.

To simulate a connection failure, the test sets the mock_connection_fail flag on the driver returned from the install_driver() code. This flag controls the connection status of every DBD::Mock object created after it; any call to DBI->connect() using DBD::Mock will fail.

new() needs only one failure to prove its point, so the test then disables the connection failures by returning the flag to zero. At that point, with the connection succeeding, the code should log a success message and the connection parameters. The test checks those too.

That leaves forcing failures for InsertWrapper::insert(). The driver-wide flag has no effect on these variables, so the test grabs the database handle of the InsertWrapper object and sets its individual mock_can_connect flag to false. DBD::Mock consults this before handling any prepare() or execute() calls, so it’s the perfect way to pretend that the database connection has gone away.

As before, it takes only one test to ensure that the failures propagate to the calling code correctly. After the failure, the test code reenables the connection flag and calls insert() again. This time, because the statements should succeed, the test then checks the logged information.

Q: Would it work to override DBI::connect() to force failures manually?

A: Yes! There’s nothing DBD::Mock does that you can’t emulate with techniques shown earlier. However, the convenience of not having to write that code yourself is a big benefit.

Q: Can you set the results of queries with DBD::Mock?

A: Absolutely. The module has more power than shown here, including the ability to return predefined results for specific queries. Whether you prefer that or shipping a simple test database is a matter of taste. With practice, you’ll understand which parts of your code need which types of tests.

Q: What’s the difference between DBD::Mock and Test::MockDBI?

A: Both modules do similar things from different angles. Test::MockDBI is better when you want very fine-grained control over which statements succeed and which fail. It’s also more complicated to learn and to use. However, it works wonderfully as a development tool for tracing the database calls, especially if you generate your SQL.

Perl.com has an introduction to Test::MockDBI at http://www.perl.com/pub/a/2005/03/31/lightning2.html?page=2#mockdbi and a more complete tutorial at http://www.perl.com/pub/a/2005/07/21/test_mockdbi.html.