| If your application is the only code that ever touches its database, then testing your abstractions is easy: test what you can store against what you can fetch. However, if your application uses the database to communicate with other applications, what's in the database is more important than what your code retrieves from it. In those cases, good testing requires you to examine the contents of the database directly. Suppose that the Users module from Shipping Test Databases" is part of a larger, multilanguage system for managing users in a company. If it were the only code that dealt with the underlying database, the existing tests there would suffice ”the internal representation of the data can change as long as the external interface stays the same. As it is, other applications will rely on specific details of the appropriate tables and, for Users to work properly, it must conform to the expected structure. Fortunately, Test::DatabaseRow provides tests for common database- related tasks . How do I do that? 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 $/ = ";\n"; $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.
Note: This is an actual failure from writing the test code. It happens .
Oops. What just happened ? 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?tt>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.
Note: The where argument is more powerful than these examples suggest. See the documentation for more details .
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:
Note: Installing DBD:: SQLite doesn't install the sqlite program. You have to do that separately .
$ sqlite3 test_data SQLite version 3.0.8 Enter ".help" for instructions sqlite> select * from users; 1Randy27 2Ben29 Emily23
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)
What about... | 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
| |
