NAME
Test::DBIC::ExpectedQueries - Test that only expected DBIx::Class queries are run
DESCRIPTION
Ensure that only the DBIx::Class SQL queries you expect are executed while a particular piece of code under test is run.
Avoiding the n+1 problem
When following a relation off a DBIC row object it's easy to overlook the fact that it might be causing one query for each and every row in the resultset. This can easily be solved by prefetching those relations, but you have to know it happens first.
This module will help you with that, and to ensure you don't accidentally start running many single-row queries in the future.
SYNOPSIS
Setup
use Test::More;
use Test::DBIC::ExpectedQueries;
my $schema = ...; # Connect to a DBIx::Class schemaSimple
expected_queries(
$schema,
sub {
$schema->resultset("Book")->find(34);
$schema->resultset("Author")->create( ... );
$schema->resultset("Book")->search( undef, { join => "author" } )->all;
},
{
book => { select => "<= 2"},
author => { insert => undef },
},
);Flexible
my $queries = Test::DBI::ExpectedQueries->new({ schema => $schema }});
$queries->run(sub {
$schema->resultset("Book")->find(34);
$schema->resultset("Author")->create( ... );
});
$queries->run(sub {
$schema->resultset("Book")->search( undef, { join => "author" } )->all;
});
$queries->test({
book => { select => "<= 2"},
author => { insert => undef },
});USAGE
You might already have a good idea of what queries are/should be run. But often that's not the case.
Start by wrapping some DBIC application code in a test without any specific limits. The default expectation for all tables is 0 queries run. So the test will fail, and report all the executed queries it didn't expect.
Now you know what's going on. Now you can add prefetches or caching for queries that shouldn't happen and specify query limits for the currently known behaviour.
Whether you want to nail down the expected queries with exact counts, or just put wide-margin comparisons in place is up to you.
SUBROUTINES
expected_queries( $schema, $sub_ref, $expected_table_operations = {} ) : $result | @result
Run $sub_ref and collect stats for queries executed on $schema, then test that they match the $expected_table_operations.
Return the return value of $sub_ref->().
See the ANNOTATED EXAMPLES below for examples on how the $expected_table_operations is used, but here's a simple example:
{
book => { select => "<= 2", update => 3 },
author => { insert => undef },
},Use table names as found in the raw SQL, not DBIC terms like resultset and relation names. For relational queries, only the first main table is collected.
Use SQL terms like "select", "insert", "update", "delete", not DBIC terms like "create" and "search".
A number means exact match. Comparisons in a string means, well that.
Undef means any number of queries
METHODS
new({ schema => $schema }}) : $new_object
Create new test object.
$schema is a DBIx::Class::Schema object.
run( $sub_ref ) : $result | @result
Run $sub_ref->() and collect all DBIC queries being run.
Return the return value of $sub_ref->().
You can call $queries->run() multiple times to add to the collected stats before finally calling $queries->test().
test( $expected_table_operations = {} ) : $is_passing
Test the collected queries against $expected_table_operations (see above) and either pass or fail a Test::More test.
If the test fails, list all queries relating to the tables with unexpected activity.
If anything failed to be identified as a known query, always list those queries. But don't fail the test just because of it.
Reset the collected stats, so subsequent calls to ->run() start with a clean slate.
ANNOTATED EXAMPLES
Simple interface
use Test::More;
use Test::DBIC::ExpectedQueries;
my $schema = ...; # A DBIx::Class schema object
# The return value of the subref is returned
my $author_rows = expected_queries(
# Collect stats for this schema
$schema,
# when running this code
sub {
$author_tree->create_authors_for_tabs($schema),
},
# and ensure these are the expected queries
{
# For the "tree_node" table
tree_node => {
update => ">= 1", # Number of updates must be >= 1
select => undef, # Any number of selects are fine
},
# For the "author" table
author => {
update => 8, # Number of updates must be exactly 8
},
user_session => {
delete => "< 10", # No more than 9 deletes allowed
},
# Any query on any other table will fail the test
},
);Flexible interface
Using the OO interface allows you to collect stats for many separate queries.
It is also useful for when you care about individual return values from methods called, and when you don't know the expected number of queries until after they have been run.
use Test::More;
use Test::DBIC::ExpectedQueries;
my $queries = Test::DBIC::ExpectedQueries->new({ schema => $schema });
my $author_rows = $queries->run(
sub { $author_tree->create_authors_for_tabs($schema) },
);
# Add more stats in a second run
$queries->run( sub { $author_tree->check_stuff() } );
# ... test other things
my $total_author_count = @{$author_rows} + 1; # or whatever
# This resets the collected stats
$queries->test(
{
author => {
insert => $total_author_count,
update => undef,
},
field => { select => "<= 1" },
tree_node => { select => 2 },
},
);DEVELOPMENT
Author
Johan Lindstrom, <johanl [AT] cpan.org>
Contributors
Many thanks to:
Syohei YOSHIDA (syohex)
Source code
https://github.com/jplindstrom/p5-Test-DBIC-ExpectedQueries
Bug reports
Please report any bugs or feature requests on GitHub:
https://github.com/jplindstrom/p5-Test-DBIC-ExpectedQueries/issues.
Caveats
SQL queries are identified using quick-n-dirty regexes, to that might be a bit brittle (and yet database agnostic, so there's that). Please report cases with example SQL.
COPYRIGHT & LICENSE
Copyright 2015- Johan Lindstrom, All Rights Reserved.
This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.