Using Test :: MockDBI several times with different results

Question

I am trying to test the code in different situations (for different result sets). My first test works well, but the next one tries to reuse the first "table".

My result sets:

my $usernames_many = [ { username => '1234567' }, { username => '2345678' }, ]; my $usernames_empty = [ ]; 

but now when I try these calls:

 $mock_dbi->set_retval_scalar(MOCKDBI_WILDCARD, "SELECT username FROM location", $usernames_many); is_deeply(find_multiple_registrations($mock_db, 15), [ '1234567', '2345678' ], "many entries"); $mock_dbi->set_retval_scalar(MOCKDBI_WILDCARD, "SELECT username FROM location", $usernames_empty); is_deeply(find_multiple_registrations($mock_db, 15), [ ], "no entries"); 

The first test passes, but the second result:

 not ok 3 - no entries # Failed test 'no entries' # at ./report_many_registrations_test.pl line 28. # Structures begin differing at: # $got->[0] = '1234567' # $expected->[0] = Does not exist 

It seems that the first set of results was used again. How to clear a result set? Or reset the state in some other way?

Answer 1

the implementation of set_retval_scalar may be discouraging at first:

 sub set_retval_scalar { my $self = shift; # my blessed self my $type = shift; # type number from --dbitest=TYPE my $sql = shift; # SQL pattern for badness push @{ $scalar_retval{$type} }, { "SQL" => $sql, "retval" => $_[0] }; } 

The reason the first results were used again is to call set_retval_scalar . After the second call to set_retval_scalar , just before the second test, the internal account for Test :: MockDBI resembles

 [ # first resultset { SQL => "SELECT username ...", retval => [{ username => '1234567' }, ...] }, # second resultset { SQL => "SELECT username ...", retval => [] } ] 

Under the hood, when your second test query SELECT username ... , _force_retval_scalar in Test :: MockDBI looks for this data structure for the current query and stops when it first hits. Both result sets are associated with the same query, so the second has no chance of matching.

But there is hope! Please note that set_retval_scalar only copies the most external link - the link to the array you control!

Change your test a little:

 my @usernames_many = ( { username => '1234567' }, { username => '2345678' }, ); my @usernames_empty = (); my $usernames = []; $mock_dbi->set_retval_scalar( MOCKDBI_WILDCARD, "SELECT username FROM location", $usernames); 

With this tool, you need to modify the contents of @$usernames (i.e. the array referenced by $usernames ) in order to modify the finished query result:

 @$usernames = @usernames_many; is_deeply(find_multiple_registrations($mock_db, 15), [ '1234567', '2345678' ], "many entries"); @$usernames = @usernames_empty; is_deeply(find_multiple_registrations($mock_db, 15), [ ], "no entries"); 

With these changes, both tests pass.

IMPORTANT: Always assign @$usernames ! You may be tempted to save a few keystrokes by writing

 $usernames = []; # empty usernames is_deeply(find_multiple_registrations($mock_db, 15), [ ], "no entries"); 

but this will lead to a test failure for the same reason as the test from your question: the device will continue to have the same link as in the set_retval_scalar call. Performing this method would be a wrong and misleading, unpleasant combination.

---

For completeness, below is a complete working example.

 #! /usr/bin/perl use warnings; use strict; BEGIN { push @ARGV, "--dbitest" } use Test::MockDBI qw/ :all /; use Test::More tests => 2; my @usernames_many = ( { username => '1234567' }, { username => '2345678' }, ); my @usernames_empty = (); my $usernames = []; my $mock_dbi = get_instance Test::MockDBI; my $mock_db = DBI->connect("dbi:SQLite:dbname=:memory:", "", ""); $mock_db->{RaiseError} = 1; $mock_db->do(q{CREATE TABLE location (username char(10))}); sub find_multiple_registrations { my($dbh,$limit) = @_; my $sth = $dbh->prepare("SELECT username FROM location"); $sth->execute; [ map $_->{username} => @{ $sth->fetchall_arrayref } ]; } $mock_dbi->set_retval_scalar( MOCKDBI_WILDCARD, "SELECT username FROM location", $usernames); @$usernames = @usernames_many; is_deeply(find_multiple_registrations($mock_db, 15), [ '1234567', '2345678' ], "many entries"); @$usernames = (); is_deeply(find_multiple_registrations($mock_db, 15), [ ], "no entries"); 

Output:

  1..2

 connect () 'CONNECT TO dbi: SQLite: dbname =: memory: AS WITH'

 do () 'CREATE TABLE location (username char (10))'

 prepare () 'SELECT username FROM location'

 execute ()

 fetchall_arrayref ()
 ok 1 - many entries

 prepare () 'SELECT username FROM location'

 execute ()

 fetchall_arrayref ()
 ok 2 - no entries 

Answer 2

If you (can) change the second test to something like:

 $mock_dbi->set_retval_scalar( MOCKDBI_WILDCARD, "Get me username stuff", # <= something different $usernames_empty ); 

then you may find that the test now works.

This is because Test::MockDBI uses only the provided SQL text, has a placeholder for which it returns a DBI object after matching dbi->prepare( 'Get me username stuff' );

Refresh . This is a workaround that does not require a SQL change:

 BEGIN { push @ARGV, "--dbitest=1"; } use 5.012; use warnings; use Test::More; use Test::MockDBI ':all'; my $mock_dbi = Test::MockDBI::get_instance; my $dbh = DBI->connect(q{}, q{}, q{}); my $sql = 'SELECT username FROM location'; my $Data = [ { username => '1234567' }, { username => '2345678' }, ]; $mock_dbi->set_retval_scalar( MOCKDBI_WILDCARD, $sql, sub { $Data } ); is_deeply( get_mock_user($dbh, $sql), [1234567,2345678], 'many entries' ); $Data = []; # change the data! is_deeply( get_mock_user($dbh, $sql), [], 'no entries' ); done_testing; sub get_mock_user { my ($dbh, $sql) = @_; $dbh->prepare( $sql ); [ map { $_->{username} } @{ $dbh->fetchrow_arrayref } ]; } 

/ I3az /