/ 
DBIx-Squirrel-v2.0.2-TRIAL
/ DBIx::Squirrel

NAME

DBIx::Squirrel - DBI, minus the boilerplate, plus a pipeline

SYNOPSIS

use DBIx::Squirrel;

my $dbh = DBIx::Squirrel->connect($data_source, $username, $password, \%attr);

# Placeholders in the style you like: ?, ?1, :1, $1 or :name
my $sth = $dbh->prepare('SELECT * FROM artist WHERE id = :id OR name = :name');

# Named placeholders bound by name, as pairs or in a hash reference
$sth->execute(id => 1, name => 'AC/DC');

# A numbered placeholder takes one value, wherever it appears
my $row = $dbh->selectrow_hashref(
    'SELECT * FROM artist WHERE id = $1 OR parent_id = $1', undef, 2,
);

# A placeholder with "..." takes a list: a "?" for each of its values
my $rows = $dbh->selectall_arrayref(
    'SELECT * FROM artist WHERE id IN (...:ids)', ids => [1, 2, 3],
);

# Iterate over the results, each run through a pipeline of stages
my $names = $dbh->iterate(
    'SELECT name FROM artist WHERE name LIKE ?', 'A%',
    sub ($row) { $row->[0] },
);
while (my($name) = $names->next) {
    say $name;
}

# Or have each row as an object, with a method for each column
my $artist = $dbh->results('SELECT * FROM artist WHERE id = :id', id => 1)->single;
say $artist->name;

# A new connection with the same parameters as an existing one
my $other = DBIx::Squirrel->connect($dbh, \%attr);

# Run the database handle through a pipeline of stages
my @results = $dbh->pipe(
    sub ($dbh)   { ... },
    sub ($value) { ... },
);

# The same, as a branch off to the side: get the handle back
$dbh = $dbh->tap(
    sub ($dbh)   { ... },
    sub ($value) { ... },
);

DESCRIPTION

DBIx::Squirrel keeps you writing SQL, as the DBI does, and takes away the friction around it: placeholders in whatever style you like, iterators over results, and a pipeline of transformations declared beside the query.

It subclasses the DBI, so everything the DBI does still works, and where the two do the same thing they give the same result. This page notes where they differ.

This is version 2, a rewrite of DBIx::Squirrel 1.x for Perl 5.36 or later, and code written for 1.x needs changes to run: the reference's "Upgrading from 1.x" lists them. To stay on 1.x, install its last version, cpanm DBIx::Squirrel@1.6.5.

The database handle's own methods are in DBIx::Squirrel::db, and the statement handle's in DBIx::Squirrel::st. DBIx::Squirrel::Iterator and DBIx::Squirrel::Results describe what iterate and results return. DBIx::Squirrel::Sugar gives each database a function of its own name.

Importing

use DBIx::Squirrel takes the names the DBI exports, as use DBI does, and the DBI exports them:

use DBIx::Squirrel qw(:sql_types neat);

METHODS

connect

$dbh = DBIx::Squirrel->connect($data_source, $username, $password);
$dbh = DBIx::Squirrel->connect($data_source, $username, $password, \%attr);
$dbh = DBIx::Squirrel->connect($dbh, \%attr);

Connects to a data source, as the DBI's connect does, and returns a database handle, a DBIx::Squirrel::db. Its statement handles are DBIx::Squirrel::sts. Both are subclasses of the DBI's own, so they do everything the DBI's handles do.

Unlike the DBI's connect, it always croaks if the connection fails, whatever RaiseError is set to, with the message the DBI raises under RaiseError, such as DBIx::Squirrel connect('dbname=music.db','',...) failed: ..., reported at the line that called connect. The DBI returns undef unless RaiseError is set.

Called on a subclass of DBIx::Squirrel, it uses that subclass's database and statement handle classes in place of DBIx::Squirrel's. The subclass needs both, as the DBI requires, each a subclass of DBIx::Squirrel's:

package My::Squirrel     { our @ISA = 'DBIx::Squirrel' }
package My::Squirrel::db { our @ISA = 'DBIx::Squirrel::db' }
package My::Squirrel::st { our @ISA = 'DBIx::Squirrel::st' }

Without them, the DBI warns that the subclass isn't set up, and returns its own handles.

Attributes starting ekorn_ are DBIx::Squirrel's own. They aren't passed on to the DBI, but kept in the database handle's private_ekorn attribute, as the defaults for each statement it prepares. Attributes passed to "prepare" in DBIx::Squirrel::db override them, for that statement. There are four:

ekorn_dialect

The driver whose rules statements are read by, described below.

ekorn_finish_active

Finish a statement still active before executing it again, as "execute" in DBIx::Squirrel::st describes.

ekorn_slice

The shape an iterator fetches rows in, as DBIx::Squirrel::Iterator describes.

ekorn_result_class

The base class of the rows a result set hands out, as DBIx::Squirrel::Result describes.

ekorn_dialect names the driver whose rules statements are read by, and connect works it out when it isn't given: it's the driver's own name, such as mysql or Pg. A driver that passes the connection on to another database (DBD::ADO, DBD::Gofer, DBD::JDBC, DBD::ODBC or DBD::Proxy) is asked, through the DBI's get_info, what the database is, and MariaDB, MySQL, PostgreSQL and SQLite are recognised. Any other database, or one that doesn't say, is read by the standard rules. Name the dialect when connect can't tell:

# MySQL, through a driver that doesn't say what's behind it
$dbh = DBIx::Squirrel->connect($data_source, $username, $password, {
    ekorn_dialect => 'mysql',
});

Connecting with an existing handle

Given a database handle in place of a data source, connect makes a new connection with the same parameters, using the DBI's clone. Any attributes passed are applied to the new connection. The handle can come from the DBI itself, or from code that made its own connection. The new handle keeps the first one's ekorn_ defaults, with any passed in their place.

The new handle is a separate connection, not a second name for the first: it doesn't share the first one's transaction, temporary tables or session settings. An in-memory SQLite database, for example, gives a new, empty database.

connect_cached

connect_cached is the DBI's own, unchanged. The DBI's connect_cached calls connect when it needs a new connection, so it croaks on failure too.

HANDLE METHODS

Database and statement handles both have these methods, inherited from DBIx::Squirrel::Pipes. The examples use a database handle; on a statement handle, the statement handle goes through the stages instead.

pipe

@results = $dbh->pipe(@stages);
$result  = $dbh->pipe(@stages);

Runs the database handle through a pipeline of stages, and returns what comes out of the last one.

Each stage is a code reference, called with one value, as its argument and in $_, so it can name the value with a signature, or leave the signature out and use $_:

sub ($row) { $row->[1] }    # the value, named
sub { $_->[1] }             # the same, in $_

$_ is localised for each call, so the caller's is left as it was. The first stage is called with the database handle. A stage returns nothing to drop the value, one value to pass it on, or several, each of which goes through the rest of the stages on its own:

my @odd_doubled = $dbh->pipe(
    sub ($dbh) { (3, 4, 5) },
    sub { $_ % 2 ? $_ : () },
    sub { $_ * 2 },
);    # (6, 10)

To end the pipeline early, call "Stop". With no stages, pipe returns the database handle.

Called in list context, pipe returns all the values. In scalar context, it returns the first, or undef if there are none. To count the values instead, assign them to an empty list, and take that in scalar context:

my $count = () = $dbh->pipe(@stages);

If a stage dies, pipe croaks at the line that called it, naming the stage by its position, from #1:

Pipeline stage #2 failed at script.pl line 12:
    ...

DBIx::Squirrel::Pipeline says more about stages.

tap

$dbh = $dbh->tap(@stages);

Runs the database handle through a pipeline of stages, as a branch off to the side for their side effects, and returns the handle, so that a chain of method calls can carry on after it.

$dbh ───┬───▶ $dbh
        │
        └───▶ stage #1 ───▶ stage #2 ───▶ ...

The stages work as they do in "pipe"; what comes out of the last one is dropped. For example, to log how many rows a table has, then carry on with the handle:

$dbh->tap(
    sub ($dbh)   { $dbh->selectrow_array('SELECT COUNT(*) FROM artist') },
    sub ($count) { log_count($count) },
)->do('DELETE FROM artist WHERE id = ?', undef, $id);

A stage passes on whatever its last statement gives, even one run for its side effects, so each stage except the last should return what the next one needs.

tap returns the handle however the stages end: having run out of values, been stopped with "Stop", or with no stages at all. If a stage dies, tap croaks at the line that called it, as "pipe" does.

FUNCTIONS

Stop

use DBIx::Squirrel::Stop;
Stop();
Stop(@values);

DBIx::Squirrel::Stop->throw(@values);

Called from a stage of a pipeline, ends the pipeline quietly. The rest of that stage doesn't run. The values already out of the pipeline are kept, @values go on through the stages after it, and nothing else is processed. Stop is exported automatically by DBIx::Squirrel::Stop; DBIx::Squirrel::Stop->throw does the same without importing it.

Stop works by throwing an exception, which the pipeline catches. So a stage that catches errors itself, with eval or try, should throw a stop on again:

sub ($row) {
    try { ... }
    catch ($e) {
        die $e if $e isa 'DBIx::Squirrel::Stop';
        ...
    }
}

Called outside a stage, it dies with "Stop used outside a pipeline". DBIx::Squirrel::Stop says more, including what a stop carries.

gather

use DBIx::Squirrel::Gather;

my $joined = gather {
    my @names;    # fresh on each run
    return (
        sub ($name) { push @names, $name; () },
        sub ()      { join ', ', @names },    # once the values run out
    );
};

Returns a stage that a pipeline makes afresh for each run, by calling the block. The block returns the stage, and optionally a function called once the values run out, whose values go through the rest of the stages. A stage that gathers values, to give them in batches or as one, or that keeps state of any kind, is made this way. gather is exported automatically by DBIx::Squirrel::Gather, which says more, including what happens after a stop.

CANNED STAGES

Four modules of ready-made stages ship with DBIx::Squirrel, each exporting the stages it's usually used for:

use DBIx::Squirrel::Stages::Flow;
use DBIx::Squirrel::Stages::Rows;

my @names = $dbh->iterate('SELECT name FROM artist', pluck('name'), take(10))->all;

The reference's "Writing stages" says how to write stages of your own that work alongside them.

DIAGNOSTICS

Errors are reported at the line of your code that called DBIx::Squirrel, never at a line inside it or the DBI. That includes the DBI's own errors under RaiseError, such as DBD::SQLite::db prepare failed: near "SELEC": syntax error, which keep the DBI's message. An error DBIx::Squirrel raises in the DBI's place, where it croaks and the DBI would return undef, has the message the DBI raises under RaiseError, so an error reads the same whatever RaiseError is set to. Each names the method you called, as plain DBI does: do and the select methods, not the prepare or execute they call. An error from a HandleError callback, or one that isn't a string, is passed on as it was raised. The DBI's warnings, under PrintError, are reported the same way, as plain DBI reports them: at your line, naming the method you called.

Pipeline stage #%d failed at %s line %d:

A stage passed to "pipe" or "tap", or its end function, died. The message it died with follows, indented.

Expected stage #%d to be a code reference

Something passed to "pipe" or "tap" wasn't a code reference. No stage runs.

Expected stage #%d, made with gather, to return one or two code references

The block given to "gather" for that stage returned something other than a stage, or a stage and an end function.

Stop used outside a pipeline

"Stop" was called where no pipeline was running to catch it.

SEE ALSO

DBI, DBIx::Squirrel::db, DBIx::Squirrel::st, DBIx::Squirrel::Iterator, DBIx::Squirrel::Results, DBIx::Squirrel::Result, DBIx::Squirrel::Context, DBIx::Squirrel::Sugar, DBIx::Squirrel::Pipes, DBIx::Squirrel::Pipeline, DBIx::Squirrel::Stop, DBIx::Squirrel::Gather, DBIx::Squirrel::Stages::Flow, DBIx::Squirrel::Stages::Rows, DBIx::Squirrel::Stages::Batch, DBIx::Squirrel::Stages::Taps

The reference describes every feature in detail, with examples, and the how-to shows how to do common tasks with them.

AUTHOR

Iain Campbell <cpanic@cpan.org>

LICENSE

Copyright (c) 2024-2026 by Iain Campbell.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.