NAME¶
SQL::Abstract::More - extension of SQL::Abstract with more constructs and more
flexible API
DESCRIPTION¶
Generates SQL from Perl data structures. This is a subclass of SQL::Abstract,
fully compatible with the parent class, but with some additions :
- •
- additional SQL constructs like "-union", "-group_by",
"join", etc.
- •
- methods take arguments as named parameters instead of positional
parameters, so that various SQL fragments are more easily identified
- •
- values passed to "select", "insert" or
"update" can directly incorporate information about datatypes,
in the form of arrayrefs of shape "[{dbd_attrs => \%type},
$value]"
This module was designed for the specific needs of DBIx::DataModel, but is
published as a standalone distribution, because it may possibly be useful for
other needs.
SYNOPSIS¶
my $sqla = SQL::Abstract::More->new();
my ($sql, @bind);
# ex1: named parameters, select DISTINCT, ORDER BY, LIMIT/OFFSET
($sql, @bind) = $sqla->select(
-columns => [-distinct => qw/col1 col2/],
-from => 'Foo',
-where => {bar => {">" => 123}},
-order_by => [qw/col1 -col2 +col3/], # BY col1, col2 DESC, col3 ASC
-limit => 100,
-offset => 300,
);
# ex2: column aliasing, join
($sql, @bind) = $sqla->select(
-columns => [ qw/Foo.col_A|a Bar.col_B|b /],
-from => [-join => qw/Foo fk=pk Bar /],
);
# ex3: INTERSECT (or similar syntax for UNION)
($sql, @bind) = $sqla->select(
-columns => [qw/col1 col2/],
-from => 'Foo',
-where => {col1 => 123},
-intersect => [ -columns => [qw/col3 col4/],
-from => 'Bar',
-where => {col3 => 456},
],
);
# ex4: passing datatype specifications
($sql, @bind) = $sqla->select(
-from => 'Foo',
-where => {bar => [{dbd_attrs => {ora_type => ORA_XMLTYPE}}, $xml]},
);
my $sth = $dbh->prepare($sql);
$sqla->bind_params($sth, @bind);
$sth->execute;
# merging several criteria
my $merged = $sqla->merge_conditions($cond_A, $cond_B, ...);
($sql, @bind) = $sqla->select(..., -where => $merged, ..);
# insert / update / delete
($sql, @bind) = $sqla->insert(
-into => $table,
-values => {col => $val, ...},
);
($sql, @bind) = $sqla->update(
-table => $table,
-set => {col => $val, ...},
-where => \%conditions,
);
($sql, @bind) = $sqla->delete (
-from => $table
-where => \%conditions,
);
CLASS METHODS¶
new¶
my $sqla = SQL::Abstract::More->new(%options);
where %options may contain any of the options for the parent class (see
"new" in SQL::Abstract), plus the following :
- table_alias
- A "sprintf" format description for generating table aliasing
clauses. The default is "%s AS %s". Can also be supplied as a
method coderef (see "Overriding methods").
- column_alias
- A "sprintf" format description for generating column aliasing
clauses. The default is "%s AS %s". Can also be supplied as a
method coderef.
- limit_offset
- Name of a "limit-offset dialect", which can be one of
"LimitOffset", "LimitXY", "LimitYX" or
"RowNum"; see SQL::Abstract::Limit for an explanation of those
dialects. Here, unlike the SQL::Abstract::Limit implementation, limit and
offset values are treated as regular values, with placeholders '?' in the
SQL; values are postponed to the @bind list.
The argument can also be a coderef (see below "Overriding
methods"). That coderef takes "$self, $limit, $offset" as
arguments, and should return "($sql, @bind)". If $sql contains
%s, it is treated as a "sprintf" format string, where the
original SQL is injected into %s.
- join_syntax
- A hashref where keys are abbreviations for join operators to be used in
the "join" method, and values are associated SQL clauses with
placeholders in "sprintf" format. The default is described below
under the "join" method.
- join_assoc_right
- A boolean telling if multiple joins should be associative on the right or
on the left. Default is false (i.e. left-associative).
- max_members_IN
- An integer specifying the maximum number of members in a "IN"
clause. If the number of given members is greater than this maximum,
"SQL::Abstract::More" will automatically split it into separate
clauses connected by 'OR' (or connected by 'AND' if used with the
"-not_in" operator).
my $sqla = SQL::Abstract::More->new(max_members_IN => 3);
($sql, @bind) = $sqla->select(
-from => 'Foo',
-where => {foo => {-in => [1 .. 5]}},
bar => {-not_in => [6 .. 10]}},
);
# .. WHERE ( (foo IN (?,?,?) OR foo IN (?, ?))
# AND (bar NOT IN (?,?,?) AND bar NOT IN (?, ?)) )
- sql_dialect
- This is actually a "meta-argument" : it injects a collection of
regular arguments, tuned for a specific SQL dialect. Dialects implemented
so far are :
- MsAccess
- For Microsoft Access. Overrides the "join" syntax to be
right-associative.
- BasisJDBC
- For Livelink Collection Server (formerly "Basis"), accessed
through a JDBC driver. Overrides the "column_alias" syntax. Sets
"max_members_IN" to 255.
- MySQL_old
- For old versions of MySQL. Overrides the "limit_offset" syntax.
Recent versions of MySQL do not need that because they now implement the
regular "LIMIT ? OFFSET ?" ANSI syntax.
- Oracle
- For Oracle. Overrides the "limit_offset" to use the
"RowNum" dialect (beware, this injects an additional column
"rownum__index" into your resultset). Also sets
"max_members_IN" to 999.
Overriding methods
Several arguments to "new()" can be references to method
implementations instead of plain scalars : this allows you to completely
redefine a behaviour without the need to subclass. Just supply a regular
method body as a code reference : for example, if you need another
implementation for LIMIT-OFFSET, you could write
my $sqla = SQL::Abstract::More->new(
limit_offset => sub {
my ($self, $limit, $offset) = @_;
defined $limit or die "NO LIMIT!"; #:-)
$offset ||= 0;
my $last = $offset + $limit;
return ("ROWS ? TO ?", $offset, $last); # ($sql, @bind)
});
INSTANCE METHODS¶
select¶
# positional parameters, directly passed to the parent class
($sql, @bind) = $sqla->select($table, $columns, $where, $order);
# named parameters, handled in this class
($sql, @bind) = $sqla->select(
-columns => \@columns,
# OR: -columns => [-distinct => @columns],
-from => $table || \@joined_tables,
-where => \%where,
-union => [ %select_subargs ], # OR -intersect, -minus, etc
-order_by => \@order,
-group_by => \@group_by,
-having => \%having_criteria,
-limit => $limit, -offset => $offset,
# OR: -page_size => $size, -page_index => $index,
-for => $purpose,
);
my $details = $sqla->select(..., want_details => 1);
# keys in %$details: sql, bind, aliased_tables, aliased_columns
If called with positional parameters, as in SQL::Abstract, "select()"
just forwards the call to the parent class. Otherwise, if called with named
parameters, as in the example above, some additional SQL processing is
performed.
The following named arguments can be specified :
- "-columns => \@columns"
- "\@columns" is a reference to an array of SQL column
specifications (i.e. column names, "*" or "table.*",
functions, etc.).
A '|' in a column is translated into a column aliasing clause: this is
convenient when using perl "qw/.../" operator for columns, as in
-columns => [ qw/table1.longColumn|t1lc table2.longColumn|t2lc/ ]
SQL column aliasing is then generated through the "column_alias"
method.
Initial items in @columns that start with a minus sign are shifted from the
array, i.e. they are not considered as column names, but are re-injected
later into the SQL (without the minus sign), just after the
"SELECT" keyword. This is especially useful for
$sqla->select(..., -columns => [-DISTINCT => @columns], ...);
However, it may also be useful for other purposes, like vendor-specific SQL
variants :
# MySQL features
->select(..., -columns => [-STRAIGHT_JOIN => @columns], ...);
->select(..., -columns => [-SQL_SMALL_RESULT => @columns], ...);
# Oracle hint
->select(..., -columns => ["-/*+ FIRST_ROWS (100) */" => @columns], ...);
The argument to "-columns" can also be a string instead of an
arrayref, like for example "c1 AS foobar, MAX(c2) AS m_c2, COUNT(c3)
AS n_c3"; however this is mainly for backwards compatibility. The
recommended way is to use the arrayref notation as explained above :
-columns => [ qw/ c1|foobar MAX(c2)|m_c2 COUNT(c3)|n_c3 / ]
If omitted, "-columns" takes '*' as default argument.
- "-from => $table || \@joined_tables"
- "-where => $criteria"
- Like in SQL::Abstract, $criteria can be a plain SQL string like "col1
IN (3, 5, 7, 11) OR col2 IS NOT NULL"; but in most cases, it will
rather be a reference to a hash or array of conditions that will be
translated into SQL clauses, like for example "{col1 => 'val1',
col2 => 'val2'}". The structure of that hash or array can be
nested to express complex boolean combinations of criteria; see
"WHERE CLAUSES" in SQL::Abstract for a detailed description.
When using hashrefs or arrayrefs, leaf values can be "bind values with
types"; see the "BIND VALUES WITH TYPES" section
below.
- "-union => [ %select_subargs ]"
- "-union_all => [ %select_subargs ]"
- "-intersect => [ %select_subargs ]"
- "-except => [ %select_subargs ]"
- "-minus => [ %select_subargs ]"
- generates a compound query using set operators such as "UNION",
"INTERSECT", etc. The argument %select_subargs contains a nested
set of parameters like for the main select (i.e. "-columns",
"-from", "-where", etc.); however, arguments
"-columns" and "-from" can be omitted, in which case
they will be copied from the main select(). Several levels of set
operators can be nested.
- "-group_by => "string"" or "-group_by =>
\@array"
- adds a "GROUP BY" clause in the SQL statement. Grouping columns
are specified either by a plain string or by an array of strings.
- "-having => "string"" or "-having =>
\%criteria"
- adds a "HAVING" clause in the SQL statement (only makes sense
together with a "GROUP BY" clause). This is like a
"-where" clause, except that the criteria are applied after
grouping has occured.
- "-order_by => \@order"
- "\@order" is a reference to a list of columns for sorting.
Columns can be prefixed by '+' or '-' for indicating sorting directions,
so for example "-orderBy => [qw/-col1 +col2 -col3/]" will
generate the SQL clause "ORDER BY col1 DESC, col2 ASC, col3
DESC".
Column names "asc" and "desc" are treated as exceptions
to this rule, in order to preserve compatibility with SQL::Abstract. So
"-orderBy => [-desc => 'colA']" yields "ORDER BY colA
DESC" and not "ORDER BY desc DEC, colA". Any other syntax
supported by SQL::Abstract is also supported here; see "ORDER BY
CLAUSES" in SQL::Abstract for examples.
The whole "-order_by" parameter can also be a plain SQL string
like "col1 DESC, col3, col2 DESC".
- "-page_size => $page_size"
- specifies how many rows will be retrieved per "page" of data.
Default is unlimited (or more precisely the maximum value of a short
integer on your system). When specified, this parameter automatically
implies "-limit".
- "-page_index => $page_index"
- specifies the page number (starting at 1). Default is 1. When specified,
this parameter automatically implies "-offset".
- "-limit => $limit"
- limit to the number of rows that will be retrieved. Automatically implied
by "-page_size".
- "-offset => $offset"
- Automatically implied by "-page_index". Defaults to 0.
- "-for => $clause"
- specifies an additional clause to be added at the end of the SQL
statement, like "-for => 'READ ONLY'" or "-for =>
'UPDATE'".
- "-want_details => 1"
- If true, the return value will be a hashref instead of the usual
"($sql, @bind)". The hashref contains the following keys :
- sql
- generated SQL
- bind
- bind values
- aliased_tables
- a hashref of "{table_alias => table_name}" encountered while
parsing the "-from" parameter.
- aliased_columns
- a hashref of "{column_alias => column_name}" encountered
while parsing the "-columns" parameter.
insert¶
# positional parameters, directly passed to the parent class
($sql, @bind) = $sqla->insert($table, \@values || \%fieldvals, \%options);
# named parameters, handled in this class
($sql, @bind) = $sqla->insert(
-into => $table,
-values => {col => $val, ...},
-returning => $return_structure,
);
Like for "select", values assigned to columns can have associated SQL
types; see "BIND VALUES WITH TYPES".
Named parameters to the "insert()" method are just syntactic sugar for
better readability of the client's code. Parameters "-into" and
"-values" are passed verbatim to the parent method. Parameter
"-returning" is optional and only supported by some database vendors
(see "insert" in SQL::Abstract); if the $return_structure is
- •
- a scalar or an arrayref, it is passed directly to the parent method
- •
- a hashref, it is interpreted as a SQL clause "RETURNING .. INTO
..", as required in particular by Oracle. Hash keys are field names,
and hash values are references to variables that will receive the results.
Then it is the client code's responsibility to use
"bind_param_inout" in DBD::Oracle for binding the variables and
retrieving the results, but the "bind_params" method in the
present module is there for help. Example:
($sql, @bind) = $sqla->insert(
-into => $table,
-values => {col => $val, ...},
-returning => {key_col => \my $generated_key},
);
my $sth = $dbh->prepare($sql);
$sqla->bind_params($sth, @bind);
$sth->execute;
print "The new key is $generated_key";
update¶
# positional parameters, directly passed to the parent class
($sql, @bind) = $sqla->update($table, \%fieldvals, \%where);
# named parameters, handled in this class
($sql, @bind) = $sqla->update(
-table => $table,
-set => {col => $val, ...},
-where => \%conditions,
-order_by => \@order,
-limit => $limit,
);
This works in the same spirit as the "insert" method above. Positional
parameters are supported for backwards compatibility with the old API; but
named parameters should be preferred because they improve the readability of
the client's code.
Few DBMS would support parameters "-order_by" and "-limit",
but MySQL does -- see
<
http://dev.mysql.com/doc/refman/5.6/en/update.html>.
delete¶
# positional parameters, directly passed to the parent class
($sql, @bind) = $sqla->delete($table, \%where);
# named parameters, handled in this class
($sql, @bind) = $sqla->delete (
-from => $table
-where => \%conditions,
-order_by => \@order,
-limit => $limit,
);
Positional parameters are supported for backwards compatibility with the old
API; but named parameters should be preferred because they improve the
readability of the client's code.
Few DBMS would support parameters "-order_by" and "-limit",
but MySQL does -- see
<
http://dev.mysql.com/doc/refman/5.6/en/update.html>.
table_alias¶
my $sql = $sqla->table_alias($table_name, $alias);
Returns the SQL fragment for aliasing a table. If $alias is empty, just returns
$table_name.
column_alias¶
Like "table_alias", but for column aliasing.
limit_offset¶
($sql, @bind) = $sqla->limit_offset($limit, $offset);
Generates "($sql, @bind)" for a LIMIT-OFFSET clause.
join¶
my $join_info = $sqla->join(
<table0> <join_1> <table_1> ... <join_n> <table_n>
);
my $sth = $dbh->prepare($join_info->{sql});
$sth->execute(@{$join_info->{bind}})
while (my ($alias, $aliased) = each %{$join_info->{aliased_tables}}) {
say "$alias is an alias for table $aliased";
}
Generates join information for a JOIN clause, taking as input a collection of
joined tables with their join conditions. The following example gives an idea
of the available syntax :
($sql, @bind) = $sqla->join(qw[
Table1|t1 ab=cd Table2|t2
<=>{ef>gh,ij<kl,mn='foobar'} Table3
=>{t1.op=qr} Table4
]);
This will generate
Table1 AS t1 INNER JOIN Table2 AS t2 ON t1.ab=t2.cd
INNER JOIN Table3 ON t2.ef>Table3.gh
AND t2.ij<Table3.kl
AND t2.mn=?
LEFT JOIN Table4 ON t1.op=Table4.qr
with one bind value "foobar".
More precisely, the arguments to "join()" should be a list containing
an odd number of elements, where the odd positions are
table
specifications and the even positions are
join specifications.
Table specifications
A table specification for join is a string containing the table name, possibly
followed by a vertical bar and an alias name. For example "Table1"
or "Table1|t1" are valid table specifications.
These are converted into internal hashrefs with keys "sql",
"bind", "name", "aliased_tables", like this :
{
sql => "Table1 AS t1"
bind => [],
name => "t1"
aliased_tables => {"t1" => "Table1"}
}
Such hashrefs can be passed directly as arguments, instead of the simple string
representation.
Join specifications
A join specification is a string containing an optional
join operator,
possibly followed by a pair of curly braces or square brackets containing the
join conditions.
Default builtin join operators are "<=>", "=>",
"<=", "==", corresponding to the following SQL JOIN
clauses :
'<=>' => '%s INNER JOIN %s ON %s',
'=>' => '%s LEFT OUTER JOIN %s ON %s',
'<=' => '%s RIGHT JOIN %s ON %s',
'==' => '%s NATURAL JOIN %s',
This operator table can be overridden through the "join_syntax"
parameter of the "new" method.
The join conditions are a comma-separated list of binary column comparisons,
like for example
{ab=cd,Table1.ef<Table2.gh}
Table names may be explicitly given using dot notation, or may be implicit, in
which case they will be filled automatically from the names of operands on the
left-hand side and right-hand side of the join.
Strings within quotes will be treated as bind values instead of column names;
pairs of quotes within such values become single quotes. Ex.
{ab=cd,ef='foo''bar',gh<ij}
becomes
ON Table1.ab=Table2.cd AND Table1.ef=? AND Table1.gh<Table2.ij
# bind value: "foo'bar"
In accordance with SQL::Abstract common conventions, if the list of comparisons
is within curly braces, it will become an "AND"; if it is within
square brackets, it will become an "OR".
Join specifications expressed as strings are converted into internal hashrefs
with keys "operator" and "condition", like this :
{
operator => '<=>',
condition => { '%1$s.ab' => {'=' => {-ident => '%2$s.cd'}},
'%1$s.ef' => {'=' => {-ident => 'Table2.gh'}}},
}
The "operator" is a key into the "join_syntax" table; the
associated value is a "sprintf" format string, with placeholders for
the left and right operands, and the join condition. The "condition"
is a structure suitable for being passed as argument to "where" in
SQL::Abstract. Places where the names of left/right tables (or their aliases)
are expected should be expressed as "sprintf" placeholders, i.e.
respectively "%1$s" and "%2$s". Usually the right-hand
side of the condition refers to a column of the right table; in such case it
should
not belong to the @bind list, so this is why we need to use the
"-ident" operator from SQL::Abstract. Only when the right-hand side
is a string constant (string within quotes) does it become a bind value : for
example
->join(qw/Table1 {ab=cd,ef='foobar'}) Table2/)
is parsed into
[ 'Table1',
{ operator => '<=>',
condition => { '%1$s.ab' => {'=' => {-ident => '%2$s.cd'}},
'%1$s.ef' => {'=' => 'foobar'} },
},
'Table2',
]
Hashrefs for join specifications as shown above can be passed directly as
arguments, instead of the simple string representation.
Return value
The structure returned by "join()" is a hashref with the following
keys :
- sql
- a string containing the generated SQL
- bind
- an arrayref of bind values
- aliased_tables
- a hashref where keys are alias names and values are names of aliased
tables.
merge_conditions¶
my $conditions = $sqla->merge_conditions($cond_A, $cond_B, ...);
This utility method takes a list of ""where"" conditions and
merges all of them in a single hashref. For example merging
( {a => 12, b => {">" => 34}},
{b => {"<" => 56}, c => 78} )
produces
{a => 12, b => [-and => {">" => 34}, {"<" => 56}], c => 78});
bind_params¶
$sqla->bind_params($sth, @bind);
For each $value in @bind:
- •
- if the value is a scalarref, call
$sth->bind_param_inout($index, $value, $INOUT_MAX_LEN)
(see "bind_param_inout" in DBI). $INOUT_MAX_LEN defaults to 99,
which should be good enough for most uses; should you need another value,
you can change it by setting
local $SQL::Abstract::More::INOUT_MAX_LEN = $other_value;
- •
- if the value is an arrayref that matches
"is_bind_value_with_type", then call the method and arguments
returned by "is_bind_value_with_type".
- •
- for all other cases, call
$sth->bind_param($index, $value);
This method is useful either as a convenience for Oracle statements of shape
"INSERT ... RETURNING ... INTO ..." (see "insert" method
above), or as a way to indicate specific datatypes to the database driver.
is_bind_value_with_type¶
my ($method, @args) = $sqla->is_bind_value_with_type($value);
If $value is a ref to a pair "[\%args, $orig_value]" :
- •
- if %args is of shape "{dbd_attrs => \%sql_type}", then return
"('bind_param', $orig_value, \%sql_type)".
- •
- if %args is of shape "{sqlt_size => $num}", then return
"('bind_param_inout', $orig_value, $num)".
Otherwise, return "()".
BIND VALUES WITH TYPES¶
At places where SQL::Abstract would expect a plain value,
"SQL::Abstract::More" also accepts a pair, i.e. an arrayref of 2
elements, where the first element is a type specification, and the second
element is the value. This is convenient when the DBD driver needs additional
information about the values used in the statement.
The usual type specification is a hashref "{dbd_attrs => \%type}",
where "\%type" is passed directly as third argument to
"bind_param" in DBI, and therefore is specific to the DBD driver.
Another form of type specification is "{sqlt_size => $num}", where
$num will be passed as buffer size to "bind_param_inout" in DBI.
Here are some examples
($sql, @bind) = $sqla->insert(
-into => 'Foo',
-values => {bar => [{dbd_attrs => {ora_type => ORA_XMLTYPE}}]},
);
($sql, @bind) = $sqla->select(
-from => 'Foo',
-where => {d_begin => {">" => [{dbd_attrs => {ora_type => ORA_DATE}},
$some_date]}},
);
When using this feature, the @bind array will contain references that cannot be
passed directly to DBI methods; so you should use "bind_params" from
the present module to perform the appropriate bindings before executing the
statement.
TODO¶
Future versions may include some of these features :
- •
- support for "WITH" initial clauses, and "WITH
RECURSIVE".
- •
- support for Oracle-specific syntax for recursive queries (START_WITH,
PRIOR, CONNECT_BY NOCYCLE, CONNECT SIBLINGS, etc.)
- •
- support for INSERT variants
INSERT .. DEFAULT VALUES
INSERT .. VALUES(), VALUES()
- •
- support for MySQL "LOCK_IN_SHARE_MODE"
- •
- new constructor option
->new(..., select_implicitly_for => $string, ...)
This would provide a default values for the "-for" parameter.
AUTHOR¶
Laurent Dami, "<laurent.dami at justice.ge.ch>"
BUGS¶
Please report any bugs or feature requests to "bug-sql-abstract-more at
rt.cpan.org", or through the web interface at
<
http://rt.cpan.org/NoAuth/ReportBug.html?Queue=SQL-Abstract-More>. I
will be notified, and then you'll automatically be notified of progress on
your bug as I make changes.
SUPPORT¶
You can find documentation for this module with the perldoc command.
perldoc SQL::Abstract::More
You can also look for information at:
- RT: CPAN's request tracker
- <http://rt.cpan.org/NoAuth/Bugs.html?Dist=SQL-Abstract-More>
- AnnoCPAN: Annotated CPAN documentation
- <http://annocpan.org/dist/SQL-Abstract-More>
- CPAN Ratings
- <http://cpanratings.perl.org/d/SQL-Abstract-More>
- MetaCPAN
- <https://metacpan.org/module/SQL::Abstract::More>
LICENSE AND COPYRIGHT¶
Copyright 2011, 2012 Laurent Dami.
This program is free software; you can redistribute it and/or modify it under
the terms of either: the GNU General Public License as published by the Free
Software Foundation; or the Artistic License.
See
http://dev.perl.org/licenses/ for more information.