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, and the package that names it a function of that name, which returns the database handle, or runs a statement on it. It gives a statement 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, and made again if the connection has gone, or if the handle has been disconnected. So a connection that drops between uses is noticed by the call after it, which 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, and gives the package its function, while the package is compiled, so it 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, connecting to it if it isn't connected.

Given a name and the arguments "connect" in DBIx::Squirrel takes, defines a database by that name, 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, made when it's 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, and 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, with the attributes to prepare it with, and stages, if given, 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, executes it with the bind values, in any form "execute" in DBIx::Squirrel::st takes, and 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, the code references at the end, it iterates over the results, as "iterate" in DBIx::Squirrel::db does, and returns them all, or how many there were in scalar context.

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

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, through its own stages and then those given, and returns them all, or how many there were in scalar context. Stages given are for that call only.

The statement is prepared once for each database handle, with "prepare_cached" in DBIx::Squirrel::db. While its handle is in use, by a statement handle not read to the end, or by a stage calling the same function, a call prepares one of its own. A statement taking a list, as "Lists" in DBIx::Squirrel::st describes, 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 stages after the statement for %s

A statement's definition had something other than code references 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.