Provided by: libtest-dbic-expectedqueries-perl_2.002-2_all 
NAME
Test::DBIC::ExpectedQueries - Test that only expected DBIx::Class queries are run
VERSION 2.000
Version 2.000 is out with a breaking change. If you're having issues with your test suite,
please see the Changes file for details.
DESCRIPTION
Ensure that only the DBIx::Class SQL queries you expect are executed while a particular
piece of code under test is run. Find the places in your code where the unexpected queries
are executed.
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 finding unexpected queries, where they are being caused, 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 schema
Simple
my @book_rows = expected_queries(
$schema,
sub {
$schema->resultset("Book")->find(34);
$schema->resultset("Author")->create( ... );
$schema->resultset("Book")->search( undef, { join => "author" } )->all;
},
{
book => {
select => "<= 2",
stack_trace => 1,
},
author => { insert => undef },
},
"Doing that stuff runs correct SQL", # optional
);
Flexible
my $queries = Test::DBIC::ExpectedQueries->new({
schema => $schema,
report_subselect_tables => 1,
});
$queries->run(sub {
$schema->resultset("Book")->find(34);
$schema->resultset("Author")->create( ... );
});
my @book_rows = $queries->run(sub {
$schema->resultset("Book")->search( undef, { join => "author" } )->all;
});
$queries->test({
book => { select => "<= 2"},
author => { insert => undef },
});
# or, with test description
$queries->test(
{
book => { select => "<= 2"},
author => { insert => undef },
},
"Doing that stuff runs correct SQL", # optional
);
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.
Finding the unexpected queries
Once you find unexpected queries made by your code, the next step is eliminating them. But
where are they called from?
Chained ResultSets
DBIC has this nice feature of chaining resultsets, which means you can create a resultset
and later modify it by adding things to the WHERE clause, joining in other resultsets, add
prefetching of relations or whatever you need to do.
You can create small logical pieces of queries (and put them on their corresponding
Result/ResultSet classes) and then combine them in to actual queries, expressed in higher
level operation. This is very, very powerful and one of the coolest features of DBIC.
There is a problem with passing around a resultset before finally executing it though, and
that is that it can often be tricky to find exactly where it is being executed.
Following relations
The problem of finding the source of a database call isn't limited to chained queries
though. The same thing happens when you construct a query, and then follow relations off
of the main table. This is what causes the n + 1 problem and you accidentally make n
queries for individual rows on top of the first one.
These additional queries might be a long way off from where the initial query was made.
Show the stack trace
To solve this problem of where the queries originate you can tell
Test::DBIC::ExpectedQueries to show a "stack_trace" for particular tables.
These call stacks may be quite deep, so you'll have to find the unexpected queries first,
and then enable the call stack for each of them. That will also avoid spamming the test
output with things you're not interested in.
Return value from the test
For the subroutine "expected_queries(...)", and the method "$queries-"run(...)>, the
return value is whatever the subroutine under test returned, so it's easy to wrap the DBIC
code under test and still get out the result.
It is context sensitive.
Executed queries vs resultsets
Only queries actually executed inside the test are being monitored. This sounds obvious,
but might be a source of problems.
Many DBIC methods are context sensitive, and in scalar context might just return an
unrealized resultset rather than execute a query and return the resulting rows. If you're
unsure, assigning the query to an array will make it run in list context and therefore
execute the SQL query. Or you can call "->"all> on the resultset object.
DBIC_TRACE
Normally, setting the ENV variable DBIC_TRACE can be used to "warn" the DBIC queries.
Test::DBIC:ExpectedQueries uses the same mechanism as DBIC_TRACE does, so while the code
is run under the test the normal DBIC_TRACE will not happen.
SUBROUTINES
expected_queries( $schema, $sub_ref, $expected_table_operations = {}, $description? ) :
$result | @result
Run $sub_ref and collect stats for queries executed on $schema, then test (using
$description) 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 },
genre => { select => 2, stack_trace => 1 },
},
· 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
· If you need to see where the queries for a table are executed from, use "stack_trace
=" 1>.
METHODS
new({ schema => $schema, report_subselect_tables => 0 }}) : $new_object
Create new test object.
$schema is a DBIx::Class::Schema object.
If "report_subselect_tables" is false (default), any SQL query like
select * from (select abc from def);
will report a select on the table "select". However, if you specify
"report_subselect_tables", it will try to find the "def" table inside the subselect.
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 = {}, $description? ) : $is_passing
Test (using $description) the collected queries against $expected_table_operations (see
above) and either pass or fail a Test::More test.
If the test fails, "diag" all queries relating to the tables with unexpected activity.
If anything failed to be identified as a known query, always "note" 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
stack_trace => 1, # Show stack trace if it fails
},
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.
If you have an anonymous subquery, that query might appear as a table called "SELECT". If
you find anything like this, or similar strange results, please raise an issue on GitHub
and provide the SQL text.
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.