/ 
Test-DatabaseRow-1.01
River stage two • 8 direct dependents • 13 total dependents
/ Test::DatabaseRow

NAME

Test::DatabaseRow - simple database tests

SYNOPSIS

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

# set the default database handle
$Test::DatabaseRow::dbh = $dbh;

# sql based test
row_ok( sql   => "SELECT * FROM contacts WHERE cid = '123'",
        tests => [ name => "trelane" ],
        label => "contact 123's name is trelane");

# test with shortcuts
row_ok( table => "contacts",
        where => [ cid => 123 ],
        tests => [ name => "trelane" ],
        label => "contact 123's name is trelane");

# complex test
row_ok( table => "contacts",

        where => { '='    => { name   => "trelane"            },
                   'like' => { url    => '%shortplanks.com'   },},

        tests => { '=='   => { cid    => 123,
                               num    => 134                  },
                   'eq'   => { person => "Mark Fowler"        },
                   '=~'   => { road   => qr/Liverpool R.?.?d/ },},

        label => "trelane entered into contacts okay" );

DESCRIPTION

This is a simple module for doing very very simple quick tests on a database, primarily designed to test if a row exists with the correct details in a table or not. For more advanced testing (joins, etc) it's probably easier for you to roll your own tests by hand than use this module.

Exports one test function row_ok. This tests if the first selected row compares to the passed specification.

The row_ok takes named attributes that control which rows in which table it selects, and what tests are carried out on those rows.

dbh

The database handle that the test should use. In lieu of this attribute being passed the test will use whatever handle is set in the $Test::DatabaseRow::dbh global variable.

sql

The sql to select the rows you want to check. Some people may prefer to use the table and where arguments (see below) to have the function build their SQL dynamically for them. This can either be just a plain string, or it can be an array ref of which the first element should contain the SQL with placeholders and the remaining elements will be considered bind variables

# using the plain string version
row_ok(sql   => "SELECT * FROM contacts WHERE cid = '123'",
       tests => [ name => "Trelane" ]);

# using placeholders and bind variables
row_ok(sql   => [ "SELECT * FROM contacts WHERE cid = ?", 123 ],
       tests => [ name => "Trelane" ]);
table

If you're not using the sql option, then the name of the table the select should be carried out on.

where

If you're not using the sql option, then a collection of things that you want combined into a WHERE clause in order to select the row that you want to test.

This is normally a hash of hashes. It's a hashref keyed by SQL comparison operators that has in turn values that are further hashrefs of column name and values pairs. This sounds really complicated, but is quite simple once you've been shown an example. If we could get get the data to test with a SQL like so:

SELECT * FROM foo
  WHERE foo  =    'bar'     AND
        baz  =     23       AND
        fred LIKE 'wilma%'  AND
        age  >=    18

Then we could have the function build that SQL like so:

row_ok(table => "tablename",
       where => { '='    => { foo  => "bar",
                              baz  => 23,       },
                  'LIKE' => { fred => 'wimla%', },
                  '>='   => { age  => '18',     },});

Note how each different type of comparison has it's own little hashref containing the column name and the value for that column that the associated operator SQL should search for.

This syntax is quite flexible, but can be overkill for simple tests. In order to make this simpler, if you are only using '=' tests you may just pass an arrayref of the columnnames / values. For example, just to test

SELECT * FROM tablename
  WHERE foo  =     bar     AND
        baz  =     23;

You can simply pass

row_ok(table => "tablename",
       where => [ foo  => "bar",
                  baz  => 23,    ]);

Which, in a lot of cases, makes things a lot quicker and simpler to write.

NULL values can confuse things in SQL. All you need to remember is that when building SQL statements use undef whenever you want to use a NULL value. Don't use the string "NULL" as that'll be interpreted as the literal string made up of a N, a U and two Ls.

As a special case, using undef either in a = or in the short arrayref form will cause a "IS" test to be used instead of a = test. This means the statements:

row_ok(table => "tablename",
       where => [ foo  => undef ],)

Will produce:

SELECT * FROM tablename
  WHERE foo IS NULL
tests

The comparisons that you want to run between the expected data and the data in the first line returned from the database. If you do not specify any tests then the test will simply check if any row is returned from the database.

Normally this is a hash of hashes in a similar vein to where. This time the outer hash is keyed by Perl comparison operators, and the inner hashes contain column names and the expected values for these columns. For example:

row_ok(sql   => $sql,
       tests => { "eq" => { wibble => "wobble",
                            fish   => "fosh",    },
                  "==" => { bob    => 4077       },
                  "=~" => { fred   => qr/barney/ },},);

This checks that the column wibble is the string "wobble", column fish is the string "fosh", column bob is equal numerically to 4077, and that fred contains the text "barney". You may use any infix comparison operator (e.g. "<", ">", "&&", etc, etc) as a test key.

The first comparison to fail (to return false) will cause the whole test to fail, and debug information will be printed out on that comparison.

In a similar fashion to where you can also pass a arrayref for simple comparisons. The function will try and Do The Right Thing with regard to the expected value for that comparison. Any expected value that looks like a number will be compared numerically, a regular expression will be compared with the =~ operator, and anything else will undergo string comparison. The above example therefore could be rewritten:

row_ok(sql   => $sql,
       tests => [ wibble => "wobble",
                  fish   => "fosh",
                  bob    => 4077,
                  fred   => qr/barney/ ]);
verbose

Setting this option to a true value will cause verbose diagnostics to be printed out during any failing tests. You may also enable this feature by setting either $Test::DatabaseRow::verbose variable the TEST_DBROW_VERBOSE environmental variable to a true value.

Checking the number of results

By default row_ok just checks the first row returned from the database matches the criteria passed. By setting the parameters below you can also cause the module to check that the correct number of rows are returned from by the select statment (though only the first row will be tested against the test conditions.)

results

Setting this parameter causes the test to ensure that the database returns exactly this number of rows when the select statement is executed. Setting this to zero allows you to ensure that no matching rows were found by the database, hence this parameter can be used for negative assertions about the database.

# assert that Trelane is _not_ in the database
row_ok(sql     => "SELECT * FROM contacts WHERE name = 'Trelane'",
       results => 0 );

# convience function that does the same thing
no_row_ok(sql => "SELECT * FROM contacts WHERE name = 'Trelane'")
min_results / max_results

This parameter allows you to test that the database returns at least or no more than the passed number of rows when the select statement is executed.

Other SQL modules

The SQL creation routines that are part of this module are designed primarily with the concept of getting simple single rows out of the database with as little fuss as possible. This having been said, it's quite possible that you need to use a more complicated SQL generation scheme than the one provided.

This module is designed to work (hopefully) reasonably well with the other modules on CPAN that can automatically create SQL for you. For example, SQL::Abstract is a module that can manufacture much more complex select statements that can easily be 'tied in' to row_ok:

use SQL::Abstract;
use Test::DatabaseRow;
my $sql = SQL::Abstract->new();

# more complex routine to find me heuristically by looking
# for any one of my nicknames and my street address
row_ok(sql   => [ $sql->select("contacts",
                               "*",
                               { name => [ "Trelane",
                                           "Trel",
                                           "MarkF" ],
                                 road => { 'like' => "Liverpool%" },
                               })],
       tests => [ email => 'mark@twoshortplanks.com' ],
       label => "check mark's email address");

BUGS

You must pass a sql or where argument to limit what is returned from the table. The case where you don't want to is so unlikely (and it's much more likely that you've written a bug in your test script) that omitting both of these is treated as an error. If you really need to not pass a sql or where argument, do where => [ 1 => 1 ].

AUTHOR

Written by Mark Fowler gtmark@twoshortplanks.com>. Copyright Profero 2003.

Some code taken from Test::Builder, written by Michael Schwern. Some code taken from Regexp::Common, written by Damian Conway. Neither objected to it's inclusion in this module.

This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

Bugs (and requests for new features) can be reported to the open source development team at Profero though the CPAN RT system: http://rt.cpan.org/NoAuth/ReportBug.html?Queue=Test-DatabaseRow

SEE ALSO

Test::More, DBI