NAME

DBIx::Squirrel::Sugar - a function for each database, and each statement

SYNOPSIS

use DBIx::Squirrel::Sugar database => {
    music => [$ENV{MUSIC_DSN}, $ENV{MUSIC_USER}, $ENV{MUSIC_PASS}],
};

my $dbh = music;                                   # the handle

my $sth = music 'SELECT * FROM artist WHERE id = ?', 1;   # executed
say $sth->fetchrow_hashref->{name};

my @names = music 'SELECT name FROM artist',       # through stages
    sub ($row) { $row->[0] };

# Anywhere in the program
my $handle = database('music');

# Statements, with stages of their own
use DBIx::Squirrel::Sugar statement => {
    artist_names => [music => 'SELECT name FROM artist', sub ($row) { $row->[0] }],
    artist       => [music => 'SELECT * FROM artist WHERE id = :id'],
};

my @all   = artist_names;                             # the results
my @upper = artist_names sub ($name) { uc $name };    # with more stages
my $found = artist id => 1;                           # executed

# Or defined as the program runs
use DBIx::Squirrel::Sugar qw(database statement);
database(archive => 'dbi:SQLite:dbname=archive.db', '', '', \%attr);
statement(albums => archive => 'SELECT * FROM album');

DESCRIPTION

DBIx::Squirrel::Sugar gives each database a name. The package that names it gets a function of that name, which returns the database handle, or runs a statement on it. A statement gets a function too, which runs it, with the bind values and any stages given.

A database is connected to when it's first used, not when it's defined, with "connect_cached" in DBIx::Squirrel. So two databases defined with the same arguments share a handle, as connect_cached shares it.

The handle is kept, and each use has the same one, without a ping to the database each time. It's pinged only while its last call has failed, which leaves the error to be read. It's made again if the connection has gone, or if the handle has been disconnected. So the call after a connection drops fails, as it would with plain DBI, and the call after that has a new connection.

IMPORTING

use DBIx::Squirrel::Sugar qw(database statement);
use DBIx::Squirrel::Sugar database => {$name => [@connect_args], ...};
use DBIx::Squirrel::Sugar statement => {
    $name => [$database, $statement, \%attr, @stages], ...
};

Gives the package "database", or "statement", or both. Given a hash reference after either, it also defines each database or statement named in it, with the arguments it takes, in an array reference. It gives the package each function while the package is compiled, so the function can be called without parentheses.

The arguments are worked out then too. The environment, literals, and what a BEGIN block has set are there, but a variable the program sets as it runs is not yet.

FUNCTIONS

database

$dbh      = database($name);
$function = database($name, @connect_args);

Given a name, returns the handle of the database defined by that name, anywhere in the program. It connects to the database if it isn't connected.

Given a name and the arguments "connect" in DBIx::Squirrel takes, it defines a database by that name. It gives the calling package a function of that name, and returns the function.

A database defined with a database handle, in place of a data source, is a clone of it, as "connect" in DBIx::Squirrel makes. The clone is made when the database is first used. The DBI makes a clone the way the handle was made. So a clone of a handle from connect_cached, such as another database's, is that same handle.

A database is defined once in a program, by a name that can be a function's. The package can't have a function of that name already.

statement

$function = statement($name, $database, $statement, @stages);
$function = statement($name, $database, $statement, \%attr, @stages);

Defines a statement by that name, on the database of that name. The attributes are those to prepare it with, and the stages are its own. It gives the calling package a function of that name, and returns the function. The database is found when the function runs, so it can be defined after the statement.

The function a database is given

$dbh     = music();
$sth     = music($statement, @bind_values);
$sth     = music($statement, \%attr, @bind_values);
@results = music($statement, @bind_values, @stages);
$count   = music($statement, @bind_values, @stages);

Called with nothing, returns the database handle.

Called with a statement, prepares it and executes it with the bind values, in any form "execute" in DBIx::Squirrel::st takes. It returns the statement handle, or nothing if it couldn't be executed. A hash reference, or undef, straight after the statement is the attributes to prepare it with, as with "do" in DBIx::Squirrel::db.

With stages at the end, code references or pipelines, it iterates over the results, as "iterate" in DBIx::Squirrel::db does. It returns them all, or, in scalar context, how many there were.

The statement can be a statement handle, which is itself executed, or iterated over.

The function a statement is given

$sth     = artist(@bind_values);
@results = artist(@bind_values, @stages);
$count   = artist(@bind_values, @stages);

Executes the statement with the bind values, and returns the statement handle, or nothing if it couldn't be executed.

With stages, its own or those given after the bind values, it iterates over the results. They go through its own stages, then those given. It returns them all, or, in scalar context, how many there were. Stages given are for that call only.

The statement is prepared once for each database handle, with "prepare_cached" in DBIx::Squirrel::db. Its handle may be in use: by a statement handle not read to the end, or by a stage calling the same function. A call then prepares one of its own. A statement taking a list (see "Lists" in DBIx::Squirrel::st) is prepared for each call's values instead, and isn't cached.

DIAGNOSTICS

Expected database or statement, not %s

use DBIx::Squirrel::Sugar was given something other than database or statement and their definitions.

Expected the arguments to connect to %s in an ARRAY
Expected the statement %s in an ARRAY

A definition given to use wasn't an array reference.

Expected a name for a statement (%s)

A name can't be a function's.

Expected a database and a statement for %s

A statement given to use didn't name its database, or have its SQL.

Expected only code references or pipelines after the statement for %s

A statement's definition had something other than stages, which are code references or pipelines, after the statement and its attributes.

Expected a name for a database (%s)

A name can't be a function's.

Expected a database defined once (%s)

The name was given to another database already.

Expected no function of the name in %s already (%s)

The package has a function of that name already.

Expected a database defined as %s

"database" was given a name no database has.

SEE ALSO

DBIx::Squirrel, DBIx::Squirrel::Iterator

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.