DEV Community

Johan Lindstrom
Johan Lindstrom

Posted on

DBIC testing: rs_with_new_rows

Here's a handy little DBIx::Class ResultSet method for when you need to write a test verifying database changes.

=head2 rs_with_new_rows() : $resultset_with_new_data

Resultset with only PK ids which are greater than the currently
largest one, i.e. it will only return rows created going forwards.

Great for testing.

=cut

sub rs_with_new_rows {
    my $self = shift;
    my $max_id = $self->get_column("id")->max();
    return $self->search({ id => { ">" => $max_id } });
}

Put that in the base class for your ResultSet classes to make it available to all of them in one go.

As you can see, the basic operation is to look for the highest id column value and then find rows with an id larger than that.

This brings along a few caveats. First off, the PK column needs to be an ever increasing value (probably an auto_increment or sequence depending on your database).

Second, the PK column obviously needs to be called id. If that's not the case, you could solve this by using the primary_columns method of the ResultSource class and use that.

Third, if this were a proper library it should probably be flexible and and robust enough to be used as part of a more complex query, and we should use the ResultSet current_source_alias to fully qualify the column names.

For this simple use case, it works fine though.

Finally, let's write that test

Here's how the rs_with_new_rows method would be used in a test:

note "*** Setup";
my $tree_node_rs = $schema->resultset("TreeNode")->rs_with_new_rows();

note "*** Run";
# Run code under test, which creates tree nodes

note "*** Test";
is($tree_node_rs->count, 3, "Correct number of tree nodes created");
ok(
    ! $tree_node_rs
        ->search(undef, { order_by => "id" })->first
        ->parent_id,
    "First (top) tree node has no parent",
);

This works because of the suuuper nice way we can chain resultsets in DBIC without actually executing any SQL query.

The method rs_with_new_rows ends with a call to ->search, which returns a new resultset with the fresh search criteria added. The query doesn't run until we ask for some data, e.g. using ->all, or ->count.

Top comments (0)