| Many modern applications store data in databases for reasons of security, abstraction, and maintainability. This is often good programming, but it presents another challenge for testing; anything outside of the application itself is harder to test. How do you know how to connect to the database? How do you know which database the user will use? Fortunately, Perl's DBI module, a few testing tools, and a little cleverness make it possible to be confident that your code does what it should do both inside the database and out. Often, it's enough to run the tests against a very simple database full of testable data. DBI works with several database driver modules that are small and easy to use, including DBD::CSV and DBD::AnyData . The driver and DBI work together to provide the same interface that you'd have with a fully relational database system. If you've abstracted away creating and connecting to the database in a single place that you can control or mock, you can create a database handle in your test and make the code use that instead of the actual connection. How do I do that? 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 :
Note: For a better version of the Users module, see Class::DBI from the CPAN .
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: A better ”if longer ”version of this code would add a constructor to the Users object and set a per-object database handle .
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 $/ = ";\n"; $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)
What just happened ?
Note: SQLite is a simple but powerful relational database that stores all of its data in a single file .
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. What about... | 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.) | |
