NAME

DBIx::Squirrel::Placeholders - find the placeholders in a statement, and match values to them

SYNOPSIS

use DBIx::Squirrel::Placeholders qw(study_sql trim_sql);

my($positions, $normalised, $trimmed)
    = study_sql('SELECT * FROM artist WHERE id = :id');
# ({1 => ':id'}, 'SELECT * FROM artist WHERE id = ?', ...)

my($positions, $normalised, $trimmed, $lists, $pieces)
    = study_sql('SELECT * FROM artist WHERE id IN (...:ids)');
# (..., {1 => 1}, ['SELECT * FROM artist WHERE id IN (', ')'])

DESCRIPTION

The functions DBIx::Squirrel::db and DBIx::Squirrel::st use to find the placeholders in a statement, rewrite them as ? for the DBI, and match the values given to them. They're for DBIx::Squirrel itself, and none is exported by default.

Several take a statement's map of its placeholders' numbers or names to their positions, from 1, such as {id => [1, 3], name => [2]}, called $binds below; or what a statement handle keeps about its statement, called $state, which holds that map as Binds.

FUNCTIONS

study_sql

($positions, $normalised, $trimmed) = study_sql($statement);
($positions, $normalised, $trimmed) = study_sql($statement, \%options);
($positions, $normalised, $trimmed, $lists, $pieces) = study_sql($statement);

Returns a hash reference mapping each placeholder's position, from 1, to the placeholder as written (?, ?1, :1, $1 or :name); the statement with each placeholder rewritten as ?; and the statement as "trim_sql" returns it. Returns nothing if the statement is blank.

A placeholder with ... before it, such as ...:ids, takes a list. The ... isn't part of the placeholder as mapped. A statement with any such placeholders returns two more values: a hash reference whose keys are the positions taking a list, and an array reference of the pieces of the statement around its placeholders, one more than there are placeholders, for "expand_lists".

Croaks if the statement uses more than one style of placeholder: one of ?, ?1, :1, $1, :name or $name, used throughout, with ... or without it.

Placeholders are looked for only in the SQL itself, not in quoted strings or identifiers, comments or PostgreSQL's dollar-quoted bodies, and not in casts, such as x::int, or identifiers containing $, such as v$session. A ?, : or $ with a backslash before it isn't a placeholder either, and both are left as they are, for DBD::Pg, which removes the backslash.

%options can hold:

ekorn_dialect

The SQL dialect the statement is written in, named as its DBI driver is, as in $dbh->{Driver}{Name}. For mysql and MariaDB, a backslash escapes a character in a string, "..." is a string, and # starts a comment. For any other dialect, or none, a backslash is an ordinary character in a string, except in PostgreSQL's E'...' strings.

pg_placeholder_dollaronly

With ekorn_dialect Pg, only $1-style placeholders are found, and the statement is returned as it is, for DBD::Pg to read. It croaks if one of them takes a list, as the statement isn't rewritten.

pg_placeholder_nocolons

With ekorn_dialect Pg, the :1 and :name styles aren't placeholders.

Anything else in %options is ignored, so the attributes passed to prepare can be given as they are.

trim_sql

$trimmed = trim_sql($statement);

Returns the statement without the blank space around it, or an empty string if it was all blank or undef. Comments are kept.

key_of

$key = key_of($placeholder);
$key = key_of('?', $position);

Returns a placeholder's number or name, as a statement's map of its placeholders keys it: without its sigil, and a number as a number, so :id and id give id, and $02 gives 2. A bare ? is keyed by its position, given with it.

is_numbered

$bool = is_numbered($binds);

Returns true if a statement's placeholders are numbered, not named.

values_taken

$count = values_taken($binds);

Returns how many values a statement takes in order: its highest number, or 0 if its placeholders are named.

values_for

%values = values_for($binds, @bind_values);

Returns each of a statement's placeholders' numbers or names, with the value given for it: in order, for numbered placeholders, or by name, as pairs or in a hash reference, for named ones. Croaks unless there's a value for each, and no other, as "bind" in DBIx::Squirrel::st describes.

expand_lists

($statement, $binds, $lengths) = expand_lists($state, \%values);

Returns a statement with list placeholders, with each list's ? written once for each of its values, given what a statement handle keeps about it (its Placeholders, the Pieces "study_sql" returns, and Lists, a map of each list placeholder's number or name to the placeholder as written, for messages) and the values for its placeholders, as "values_for" returns them. Also returns the map of each number or name to its positions in the statement returned, and the number of values in each list.

Croaks unless each list is a reference to an ARRAY of at least one plain value: not undef or a value on its own, nor a list containing an unblessed reference.

place_values

%placed = place_values($state, %values);

Returns each position of a statement's placeholders, from 1, with the value for it, given what its statement handle keeps about it and the values for its placeholders' numbers or names. A list placeholder's positions take the values in its list in turn, wherever it's used, and the list must have as many values as the statement was prepared for, in Lengths.

SEE ALSO

DBIx::Squirrel::db

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.