Find file
Fetching contributors…
Cannot retrieve contributors at this time
233 lines (164 sloc) 8.51 KB
SQL::Pico - Prebuilt Raw SQL Statement Builder
use SQL::Pico ();
$dbh = DBI->connect(...);
$sp = SQL::Pico->new->dbh($dbh);
$quoted = $sp->quote($val); # $dbh->quote($val)
$quoted = $sp->quote_identifier($key); # $dbh->quote_identifier($key)
$select = $sp->bind("SELECT * FROM ?? WHERE id = ?", $table, $id);
$where = join(" AND " => $sp->bind("?? = ?", %hash));
$select = $sp->bind("SELECT * FROM mytbl WHERE ???", $where);
$keys = join(", " => $sp->quote_identifier(keys %hash));
$vals = join(", " => $sp->quote(values %hash));
$insert = $sp->bind("INSERT INTO mytbl (???) VALUES (???)", $keys, $vals);
$sets = join(", " => $sp->bind("?? = ?", %hash));
$update = $sp->bind("UPDATE mytbl SET ??? WHERE id = ?", $sets, $id);
$in = join(", " => $sp->quote(@list));
$delete = $sp->bind("DELETE mytbl WHERE id IN (???)", $in);
This provides a simple but safe way to build raw SQL statements without
learning any other languages than Perl and SQL. "SQL::Pico" is
lightweight and doesn't require any non-core modules except for DBI.
"SQL::Pico"'s "bind()" method generates a SQL statement which
placeholders are filled with immediate values bound. This makes you free
from handling bind values and calling "prepare()". See "RECIPES" section
Why "Prebuilt" SQL?
Because SQL is the most simplest language to comunicate with RDBMS.
The most of ORM modules and something *SQL::Builder*-ish modules would
have required you to understand its complex class structure or dialectal
This module simply provides just one of new method, "bind()", which
allows you to build SQL statements using placeholders, as well as
"quote()" and "quote_identifier()" methods which are wrappers for
"DBI"'s methods with the same name you already know.
The most of modern Web applications would not cache prepared $sth
instances though they would cache a connected $dbh instance. It means
you don't need to worry about its performance (dis)advantage when you
don't use bind values at "execute()".
This creates a "SQL::Pico" instance.
$sp = SQL::Pico->new;
This accepts key/value pair(s) as its initial parameters. Only "dbh"
parameter is available at this module.
$sp = SQL::Pico->new(dbh => $dbh);
This is an accessor to specify a "DBI" instance.
$sp = SQL::Pico->new;
The setter returns the current "SQL::Pico" instance for you to chain
method calls.
$quoted = SQL::Pico->new->dbh($dbh)->quote($val);
Please note that quoting formats for literals and identifiers depend on
RDBMS server you connect. For example, a literal string "Don't" would be
quoted as 'Don''t' in a server, as well as 'Don\'t', "Don't", etc. in
This supports SQL-92 Standard's quoting format, per default, under
DBD::NullP driver which is included in "DBI" distribution. You could
specify a "DBI" instance to make string quoted properly for your RDBMS
This calls "DBI"'s "quote()" method internally.
$quoted = $sp->quote($val); # $dbh->quote($val)
This doesn't accept literal's data type specified at its second
argument. The other difference to original is that this accept multiple
arguments and returns them quoted.
@list = $sp->quote(@vals); # multiple quotes at once
This calls "DBI"'s "quote_identifier()" method internally.
$quoted = $sp->quote_identifier($key); # $dbh->quote_identifier($key)
Multiple arguments are allowed as well.
@list = $sp->quote_identifier(@keys); # multiple quotes at once
bind(SQL, VALUES...)
This builds a SQL statement by using placeholders with bind values.
$sql = $sp->bind("SELECT * FROM ?? WHERE id = ?", $table, $id);
Note that this returns a SQL statement built with values bound at the
method prior to "DBI"'s <execute()> method called.
Three types of placeholders are allowed at the first argument:
Single character of "?" represents a placeholder for a literal which
will be escaped by "quote()".
Double characters of "??" represents a placeholder for an identifier
which will be escaped by "quote_identifier()".
Triple characters of "???" represents a placeholder for a raw SQL string
which will not be escaped.
$hash = {"category" => "foo", "price" => "100"};
@list = $sp->bind("?? = ?", %$hash);
$where = join(" AND ", @list);
$select = "SELECT * FROM mytable WHERE $where";
# SELECT * FROM mytable WHERE "category" = 'foo' AND "price" = '100'
# Note that the order of key/value pairs vary.
In list context, this returns a list of strings repeatedly built with
parameters following.
In addition to the OO style described above, this also supports the
functional style below by exporting three shortcut functions: "v()",
"k()" and "sql()" per default.
use SQL::Pico; # functions exported
This is a shortcut for "quote()" method which quotes a literal, e.g.
string, number, etc.
$quoted = v("string"); # quotes literal
@quoted = v("foo", "bar", "100"); # multiple literals
This is a shortcut for "quote_identifier()" method which quotes an
identifier, e.g. table name, column name, etc.
$quoted = k("table_name"); # quotes identifier
@quoted = k("category", "name", "price"); # multiple identifiers
sql(SQL, VALUES...)
This is a shortcut for "bind()" method which builds a SQL statement by
using placeholders with bind values.
$select = sql("SELECT * FROM ?? WHERE id = ?", $table, $id);
Call "dbh()" class method to specify the database handle for those
"v()", "k()" and "sql()" functions above.
$dbh = DBI->connect(...);
Note that "dbh()" method is not exported.
The recipes show which "DBI" methods would follow SQL statements built
by "sql()" function.
# fetch a single record as a hahsref
$sql = sql("SELECT * FROM mytable WHERE id = ?", $id);
$hash = $dbh->selectrow_hashref($sql);
# fetch multiple records as an arrayref of hashrefs
$in = join(", " => v(@list));
$sql = sql("SELECT * FROM mytable WHERE id IN (???)", $in);
$array = $dbh->selectall_arrayref($sql, {Slice=>{}});
# fetch a list of a single column as an arrayref
$sql = sql("SELECT id FROM mytable WHERE price < ?", $price);
$array = $dbh->selectcol_arrayref($sql);
# fetch a list of a pair column as an arrayref then a hashref
$sql = sql("SELECT id, name FROM mytable WHERE price < ?", $price);
$array = $dbh->selectall_arrayref($sql);
$hash = +{ map { $_->[0] => $_->[1] } @$array };
# check a record exists as a scalar
$sql = sql("SELECT count(*) FROM mytable WHERE id = ?", $id);
$exist = $dbh->selectrow_array($sql);
# count total number of records as a scalar
$sql = sql("SELECT count(*) FROM mytable WHERE price < ?", $price);
$count = $dbh->selectrow_array($sql);
# insert a record with a hashref
$keys = join(", " => k(keys %$hash));
$vals = join(", " => v(values %$hash));
$sql = sql("INSERT INTO mytable (???) VALUES (???)", $keys, $vals);
$dbh->do($sql) or die "insert failed";
# update a record with a hashref
$sets = join(", " => sql("?? = ?", %$hash));
$sql = sql("UPDATE mytable SET ??? WHERE id = ?", $sets, $id);
$dbh->do($sql) or die "update failed";
# delete a record
$sql = sql("DELETE mytable WHERE id = ?", $id);
$dbh->do($sql) or die "delete failed";
Yusuke Kawasaki
The following copyright notice applies to all the files provided in this
distribution, including binary files, unless explicitly noted otherwise.
Copyright 2012 Yusuke Kawasaki
This library is free software; you can redistribute it and/or modify it
under the same terms as Perl itself.
DBI executes SQL statements built by the module.