Re: DBD::Mock -- Giving back wrong data and wrong *amounts* of data

[andreas1234567]

mock_add_resultset used with an array ref will return the given resultset regardless of what the sql query is. The documentation contains an example of how to make it return a particular resultset for a given query (see the sql_and_resultset option). Note that the sql can be anything, it does not have to be valid SQL. E.g. like this

$ cat 790142.pl
use strict;
use warnings;

use Test::More qw(no_plan);
use Data::Dumper;
use DBI;
my $dbh = DBI->connect('DBI:Mock:', '', '');

$dbh->{'mock_add_resultset'} = {
  sql     => 'QUERY_ONE',
  results => [
    [ 'foo',        'bar' ],
    [ 'this_one',   'this_two' ],
    [ 'this_three', 'this_four' ],
  ],
};

$dbh->{'mock_add_resultset'} = {
  sql     => 'QUERY_TWO',
  results => [
    [ 'moo',      'baah' ],
    [ 'that_one',   'that_two' ],
    [ 'that_three', 'that_four' ],
  ],
};

my $sth = $dbh->prepare('QUERY_ONE');
is_deeply( $sth->fetchrow_arrayref(), [ 'this_one',   'this_two' ] );
is_deeply( $sth->fetchrow_arrayref(), [ 'this_three', 'this_four' ] );
ok( !defined $sth->fetchrow_arrayref() ); #  no more data

$sth = $dbh->prepare('QUERY_TWO');
is_deeply( $sth->fetchrow_arrayref(), [ 'that_one',   'that_two' ] );
is_deeply( $sth->fetchrow_arrayref(), [ 'that_three', 'that_four' ] );
ok( !defined $sth->fetchrow_arrayref() ); #  no more data

$sth = $dbh->prepare('QUERY_THREE');
ok( !defined $sth->fetchrow_arrayref() ); #  no matching query

__END__
 
$ perl 790142.pl
ok 1
ok 2
ok 3
ok 4
ok 5
ok 6
ok 7
1..7
$

Update: Added code sample.

[sirrobert]

Ok, I see... I'm doing this testing within a framework that handles much of the SQL behind the scenes. I'll try to capture some of the queries being produced and associate them with good records.

Is there a better approach to it than that?

[andreas1234567]

Is there a better approach to it than that?

I would start by asking myself what is it I want to test? Write a test strategy, and take it on from there. Then break it down to individual tests e.g.
• Does the application behave as I expect when the database is unavailable?
• Does the application behave as I expect when the database returns unexpected results (e.g. no data, wrong charset, wrong number of columns, wrong data types, ..)?
• Does the application behave as I expect in a foreign environment (e.g. on a different OS)?
• ..

Suggested reading: Perl Testing.

You may also want to take a look at Test::MockDBI (Disclaimer: I one filed a patch to the given module and ended up as its' co-maintainer).

Best of luck.