NAME

SQL::Abstract::More - Generate SQL from Perl data structures, like SQL::Abstract, with more features

SYNOPSIS

use SQL::Abstract::More;
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 : subqueries
my $subq1 = [ $sqla->select(-columns => 'f|x', -from => 'Foo',
                            -union   => [-columns => 'b|x',
                                         -from    => 'Bar',
                                         -where   => {barbar => 123}],
                            -as      => 'Foo_union_Bar',
                            ) ];
my $subq2 = [ $sqla->select(-columns => 'MAX(amount)',
                            -from    => 'Expenses',
                            -where   => {exp_id => {-ident => 'x'}, date => {">" => '01.01.2024'}},
                            -as      => 'max_amount',
                            ) ];
($sql, @bind) = $sqla->select(
   -columns  => ['x', \$subq2],
   -from     => \$subq1,
   -order_by => 'x',
  );

# ex5: 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;

# ex6: multicolumns-in
$sqla = SQL::Abstract::More->new(
  multicols_sep        => '/',
  has_multicols_in_SQL => 1,
);
($sql, @bind) = $sqla->select(
 -from     => 'Foo',
 -where    => {"foo/bar/buz" => {-in => ['1/a/X', '2/b/Y', '3/c/Z']}},
);

# ex7: merging several criteria
my $merged = $sqla->merge_conditions($cond_A, $cond_B, ...);
($sql, @bind) = $sqla->select(..., -where => $merged, ..);

# ex8: insert / update / delete
($sql, @bind) = $sqla->insert(
  -add_sql => 'OR IGNORE',        # SQLite syntax
  -into    => $table,
  -values  => {col => $val, ...},
);
($sql, @bind) = $sqla->insert(
  -into    => $table,
  -columns => [qw/a b/],
  -select  => {-from => 'Bar', -columns => [qw/x y/], -where => ...},
);
($sql, @bind) = $sqla->update(
  -table => $table,
  -set   => {col => $val, ...},
  -where => \%conditions,
);
($sql, @bind) = $sqla->delete (
  -from  => $table
  -where => \%conditions,
);

# ex9 : initial WITH clause -- example borrowed from https://sqlite.org/lang_with.html
($sql, @bind) = $sqla->with_recursive(
  [ -table     => 'parent_of',
    -columns   => [qw/name parent/],
    -as_select => {-columns => [qw/name mom/],
                   -from    => 'family',
                   -union   => [-columns => [qw/name dad/], -from => 'family']},
   ],

  [ -table     => 'ancestor_of_alice',
    -columns   => [qw/name/],
    -as_select => {-columns   => [qw/parent/],
                   -from      => 'parent_of',
                   -where     => {name => 'Alice'},
                   -union_all => [-columns => [qw/parent/],
                                  -from    => [qw/-join parent_of {name} ancestor_of_alice/]],
               },
   ],
  )->select(
   -columns  => 'family.name',
   -from     => [qw/-join ancestor_of_alice {name} family/],
   -where    => {died => undef},
   -order_by => 'born',
  );

DESCRIPTION

This module generates SQL from Perl data structures. It was heavily inspired by the venerable SQL::Abstract and used to inherit from it, retaining full compatibility with the parent class, but with many improvements. The current version inherits from SQL::Abstract::Classic instead, and the next version will probably stand on its own, without any parent class, but still maintaining the original API, so clients will not be affected. The reasons for this move are developed below under "History of SQL::Abstract and future of SQL::Abstract::More".

Here is a short overview of additional features brought by the present module over the historical SQL::Abstract API:

methods take arguments as named parameters instead of positional parameters. This is more flexible for identifying and assembling various SQL clauses, like -where, -order_by, -group_by, etc.
additional SQL constructs like -union, -group_by, join, -with_recursive, etc. are supported;
subqueries can be used in a column list or as a datasource (i.e SELECT ... FROM (SELECT ..)), even as members of a join;
WHERE .. IN clauses can range over multiple columns (tuples);
values passed to select, insert or update can directly incorporate information about datatypes, in the form of arrayrefs of shape [{dbd_attrs => \%type}, $value];
prefixes - or + can be supplied in front of column names in ORDER BY, for specifying the sorting order;
several SQL dialects can adapt the generated SQL to various DBMS vendors.

This module was originally designed for the specific needs of DBIx::DataModel, but is published as a standalone distribution, because it may possibly be useful for other needs.

Unfortunately, this module cannot be used with DBIx::Class, because DBIx::Class creates its own instance of SQL::Abstract and has no API to let the client instantiate from any other class.

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::Classic), plus the following :

table_alias

A sprintf format description for generating table aliasing clauses. The default is %s AS %s.

The argument can also be a method coderef :

SQL::Abstract::More->new(table_alias => sub {
  my ($self, $table, $alias) = @_;
  my $syntax_for_aliased_table = ...;
  return $syntax_for_aliased_table;
 })

That method should not call $self->_quote() because arguments $table and $alias are already quoted if necessary.

column_alias

A sprintf format description for generating column aliasing clauses. The default is %s AS %s.

Like for table_alias, the argument can also be a method coderef.

limit_offset

Name of a "limit-offset dialect", which can be one of LimitOffset, LimitXY, LimitYX, OffsetFetchRows or RowNum. Most of thoses are copied from SQL::Abstract::Limit -- see that module for explanations. The OffsetFetchRows dialect is a local addition and corresponds to Oracle syntax in version 12c or above (OFFSET ? ROWS FETCH ? ROWS ONLY).

Unlike the SQL::Abstract::Limit implementation, limit and offset values are treated here as regular values, with placeholders '?' in the SQL; values are postponed to the @bind list.

The argument can also be a coderef. 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. When the supplied value list exceeds that number, SQL::Abstract::More automatically splits 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 (?, ?)) )

multicols_sep

A string or compiled regular expression used as a separator for "multicolumns". This separator can then be used on the left-hand side and right-hand side of an IN operator, like this :

my $sqla = SQL::Abstract::More->new(multicols_sep => '/');
($sql, @bind) = $sqla->select(
 -from     => 'Foo',
 -where    => {"x/y/z" => {-in => ['1/A/foo', '2/B/bar']}},
);

Alternatively, tuple values on the right-hand side can also be given as arrayrefs instead of plain scalars with separators :

-where    => {"x/y/z" => {-in => [[1, 'A', 'foo'], [2, 'B', 'bar']]}},

but the left-hand side must stay a plain scalar because an array reference wouldn't be a proper key for a Perl hash; in addition, the presence of the separator in the string is necessary to trigger the special algorithm for multicolumns.

The generated SQL depends on the boolean flag has_multicols_in_SQL, as explained in the next paragraph.

has_multicols_in_SQL

A boolean flag that controls which kind of SQL will be generated for multicolumns. If the flag is true, this means that the underlying DBMS supports multicolumns in SQL, so we just generate tuple expressions. In the example from the previous paragraph, the SQL and bind values would be :

# $sql  : "WHERE (x, y, z) IN ((?, ?, ?), (?, ?, ?))"
# @bind : [ qw/1 A foo 2 B bar/ ]

It is also possible to use a subquery, like this :

($sql, @bind) = $sqla->select(
 -from     => 'Foo',
 -where    => {"x/y/z" => {-in => \[ 'SELECT (a, b, c) FROM Bar '
                                     . 'WHERE a > ?', 99]}},
);
# $sql  : "WHERE (x, y, z) IN (SELECT (a, b, c) FROM Bar WHERE a > ?)"
# @bind : [ 99 ]

If the flag is false, the condition on tuples will be automatically converted using boolean logic :

# $sql  : "WHERE (   (x = ? AND y = ? AND z = ?) 
                  OR (x = ? AND y = ? AND z = ?))"
# @bind : [ qw/1 A foo 2 B bar/ ]

If the flag is false, subqueries are not allowed.

select_implicitly_for

A value that will be automatically added as a -for clause in calls to "select". This default clause can always be overridden by an explicit -for in a given select :

my $sqla = SQL::Abstract->new(-select_implicitly_for => 'READ ONLY');
($sql, @bind) = $sqla->select(-from => 'Foo');
  # SELECT * FROM FOO FOR READ ONLY
($sql, @bind) = $sqla->select(-from => 'Foo', -for => 'UPDATE');
  # SELECT * FROM FOO FOR UPDATE
($sql, @bind) = $sqla->select(-from => 'Foo', -for => undef);
  # SELECT * FROM FOO

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 old versions of 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 and has_multicols_in_SQL to true.
Oracle12c: For Oracle starting from version 12c. Like the "Oracle" dialect, except for limit_offset which uses OffsetFetchRows.

INSTANCE METHODS

Main API methods

select

# positional parameters, directly passed to the parent class (deprecated API)
($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,
  -as       => $subquery_alias,
 );

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, *, SQL functions or expressions, etc.).

A '|' in a column is translated into a column aliasing clause: this syntax without spaces is convenient when using Perl's qw/.../ operator, as in

-columns => [ qw/table1.longColumn|t1lc table2.longColumn|t2lc/ ]

Note: if the qw/.../ operator contains expressions with commas, Perl will issue a warning "Possible attempt to separate words with commas"; this can be avoided by specifying no warnings 'qw'. Another solution is of course to use an arrayref of explicit quoted strings instead of the qw/.../ operator. Explicit quoted strings are also necessary when a column name or alias name contains spaces.

SQL column aliasing is generated through the "column_alias" method. If "quote_char" in SQL::Abstract is defined, aliased columns will be quoted, unless they contain parentheses or commas, in which case they are considered as SQL expressions for which the user should handle the quoting himself. For example if quote_char is "`",

-columns => [ qw/foo.bar|fb length(buz)|lbuz/ ]

will produce

SELECT `foo`.`bar` AS `fb`, length(buz) AS `lbuz`

and not

SELECT `foo`.`bar` AS `fb`, length(`buz`) AS `lbuz`

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], ...);

Within the columns array, it is also possible to insert a subquery expressed as a reference to an arrayref, as explained in "Literal SQL with placeholders and bind values (subqueries)" in SQL::Abstract. It is recommanded to generate the subquery through another call to select() with an "-as" parameter (explained below), which will enclose the SQL into parenthesis and add an alias for that column:

# build the subquery -- stored in an arrayref
my $subquery = [ $sqla->select(
    -columns => 'COUNT(*)',
    -from    => 'Foo',
    -where   => {bar_id => {-ident => 'Bar.bar_id'},
                 height => {-between => [100, 200]}},
    -as      => 'count_foos',
  ) ];

# main query
my ($sql, @bind) = $sqla->select(
       -from    => 'Bar',
       -columns => ['col1', 'col2', \$subquery, , 'col4'], # reference to an arrayref !
       -where   => {color => 'green'},
     );

This will produce SQL :

SELECT col1, col2,
       (SELECT COUNT(*) FROM Foo WHERE bar_id=Bar.bar_id and height BETWEEN ? AND ?) AS count_foos,
       col4
  FROM Bar WHERE color = ?

The resulting @bind array combines bind values coming from both the subquery and from the main query, i.e. (100, 200, 'green').

Instead of an arrayref, the argument to -columns can also be just a string, 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 || \$subquery

The argument to -from can be :

a plain string, interpreted as a table name. Like for column aliases, a table alias can be given, using a vertical bar as separator:
```
-from => 'Foobar|fb', # SELECT .. FROM Foobar AS fb
```
a join specification, given as an arrayref starting with the keyword -join, followed by a list of table and join conditions as expected by the "join" method (explained below):
```
-from => [-join => qw/Foo fk=pk Bar/],
```

a reference to a subquery arrayref, in the form [$sql, @bind]. Like for subqueries in columns, it is recommanded to generate the subquery through another call to select() with an "-as" parameter, interpreted this time as a table alias instead of a column alias:

my $subq = [ $sqla->select(-columns => 'f|x', -from => 'Foo',
                           -union   => [-columns => 'b|x',
                                        -from    => 'Bar',
                                        -where   => {barbar => 123}],
                           -as      => 'Foo_union_Bar',
                           ) ];
my ($sql, @bind) = $sqla->select(-from     => \$subq,
                                 -order_by => 'x');

which produces

SELECT * FROM (      SELECT f AS x FROM Foo 
               UNION SELECT b AS x FROM Bar WHERE ( barbar = ? )
              ) AS Foo_union_Bar 
ORDER BY x

a simple arrayref that does not start with -join. This is for compatibility with the old SQL::Abstract API. Members of the array are interpreted as a list of table names, that will be joined by ", ". Join conditions should then be expressed separately in the -where part.
```
$sqla->select(-from => [qw/Foo Bar Buz/], ...) # SELECT FROM Foo, Bar, Buz ..
```
This syntax is deprecated; preferably use the -join feature instead.
a simple scalarref. This is for compatibility with the old SQL::Abstract API. The result is equivalent to passing the scalar directly, except that no quoting will be applied.
```
$sqla->select(-from => \ "Foo", ...) # SELECT FROM Foo ..
```
This syntax is deprecated.

-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 the recommanded way is to rather supply 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, including parenthesized subqueries; see "WHERE CLAUSES" in SQL::Abstract for a detailed description.

When using hashrefs or arrayrefs, values can be "bind values with types" to be passed to the underlying DBI layer; 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. In most cases this is used together with a GROUP BY clause. This is like a -where clause, except that the criteria are applied after grouping has occurred.

-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).

-page_index => $page_index

specifies the page number (starting at 1). Default is 1. When present, the combination of C-page_size and C-page_index is automatically translated into a combination of C-limit and C-offset.

-limit => $limit

limit to the number of rows that will be retrieved.

-offset => $offset

offest to the first row that will be part of the result. 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'.

-as => $alias

The $sql part is rewritten as ($sql)|$alias. This is convenient when the result is to be used as a subquery within another select() call.

-want_details => 1

If true, the return value will be a hashref instead of the usual ($sql, @bind), containing more details:

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.

This method is mainly for use by DBIx::DataModel.

insert

# positional parameters, directly passed to the parent class (deprecated API)
($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,
  -add_sql   => $keyword,
);

# insert from a subquery
($sql, @bind) = $sqla->insert(
  -into    => $destination_table,
  -columns => \@columns_into
  -select  => {-from => $source_table, -columns => \@columns_from, -where => ...},
);

When using named arguments, -into is mandatory and either -values or -select must be present. Legal arguments are:

-into => $table

String containing the name of the table to insert into.

-values

A hashref that maps column names to the values to insert, like for example

$sqla->insert(-into => 'Foo', -values => {foo => 123, bar => 'buz'});

The generated SQL will have column names in alphabetical order, i.e.

INSERT INTO Foo(bar, foo) VALUES (?, ?) # @bind contains ['buz', 123]

A less common variant is to supply an arrayref instead of a hashref, in which case the generated SQL will have no column names

$sqla->insert(-into => 'Users', -values => [qw/John Doe/]);
# INSERT INTO Users VALUES (?, ?) # @bind contains ['John', 'Doe']

Like for "select", values can have associated SQL types; see "BIND VALUES WITH TYPES".

-select

Used to generate SQL of shape INSERT INTO ... SELECT ..., i.e. the values to insert come from a subquery. The argument to -select is a hashref containing arguments to be passed to the "select" method.

-columns

The -columns parameter takes an arrayref; it usually goes together with -select and specifies the names of the columns to insert into.

This parameter can also be used with the arrayref form of -values:

$sqla->insert(-into => 'Foo', -columns => [qw/foo bar/], -values => [123, 'buz']);

-returning

Optional parameter 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";
```

-add_sql

Optional parameter used with some specific SQL dialects for injecting additional SQL keywords after the INSERT keyword. Examples:

$sqla->insert(..., -add_sql => 'IGNORE')     # produces "INSERT IGNORE ..."    -- MySQL
$sqla->insert(..., -add_sql => 'OR IGNORE')  # produces "INSERT OR IGNORE ..." -- SQLite

update

# positional parameters, directly passed to the parent class (deprecated API)
($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,
  -returning => $return_structure,
  -add_sql   => $keyword,
);

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.

The optional parameter -returning works like for the "insert" method.

The optional parameter -add_sql is used with some specific SQL dialects, for injecting additional SQL keywords after the UPDATE keyword. Examples :

$sqla->update(..., -add_sql => 'IGNORE')     # produces "UPDATE IGNORE ..."    -- MySQL
$sqla->update(..., -add_sql => 'OR IGNORE')  # produces "UPDATE OR IGNORE ..." -- SQLite

delete

# positional parameters, directly passed to the parent class (deprecated API)
($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,
  -add_sql  => $keyword,
);

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.

The optional parameter -add_sql is used with some specific SQL dialects, for injecting additional SQL keywords after the DELETE keyword. Examples :

$sqla->delete(..., -add_sql => 'IGNORE')     # produces "DELETE IGNORE ..."    -- MySQL
$sqla->delete(..., -add_sql => 'OR IGNORE')  # produces "DELETE OR IGNORE ..." -- SQLite

with_recursive, with

my $new_sqla = $sqla->with_recursive( # or: $sqla->with(

  [ -table     => $CTE_table_name,
    -columns   => \@CTE_columns,
    -as_select => \%select_args ],

  [ -table     => $CTE_table_name2,
    -columns   => \@CTE_columns2,
    -as_select => \%select_args2 ],
  ...

 );
 ($sql, @bind) = $new_sqla->insert(...);

# or, if there is only one table expression
my $new_sqla = $sqla->with_recursive(
    -table     => $CTE_table_name,
    -columns   => \@CTE_columns,
    -as_select => \%select_args,
   );

Returns a cloned instance enriched with an encapsulated common table expression (CTE). The CTE is kind of local view that can be used as a table name for the rest of the SQL statement - see https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL for an explanation of such expressions. Oracle uses a different term for the same concept: subquery factoring clauses.

Further calls to select, insert, update and delete on that new instance will automatically prepend a WITH or WITH RECURSIVE clause before the usual SQL statement.

Arguments to with_recursive() are expressed as a list of arrayrefs; each arrayref corresponds to one table expression, with the following named parameters :

-table

The name to be assigned to the table expression

-columns

An optional list of column aliases to be assigned to the columns resulting from the internal select

-as_select

The implementation of the table expression, given as a hashref of arguments following the same syntax as the "select" method.

-final_clause

An optional SQL clause that will be added after the table expression. This may be needed for example for an Oracle cycle clause, like

($sql, @bind) = $sqla->with_recursive(
  -table        => ...,
  -as_select    => ...,
  -final_clause => "CYCLE x SET is_cycle TO '1' DEFAULT '0'",
 )->select(...);

If there is only one table expression, its arguments can be passed directly as an array instead of a single arrayref.

CTEs can be recursive, so that a single SQL query is able to traverse a tree of nested data. See the example from the "SYNOPSIS", borrowed from https://sqlite.org/lang_with.html and translated into SQL::Abstract::More syntax.

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";
}

Returns a hashref of information to be passed as a datasource to the "select" method, for generating a SQL JOIN clause. Therefore this method is seldom called directly, but rather indirectly through

$sqla->select(-from => [-join => ....], ...);

It takes as input a list containing an odd number of elements, where the odd positions indicate tables and the even positions indicate join conditions. Both table and join conditions can be given either in the form of hashrefs or as plain strings in a mini domain-specific language. The second option is more convenient because all items in the list can be written together as members of a qw/.../ operator. 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.

Details are explained in the next subsections.

tables participating in the join

Tables participating in a join are given as strings containing the table name, possibly followed by a vertical bar and an alias name. For example Table1 or Table1|t1 are valid table specifications.

Table specifications 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 conditions

Join conditions between two tables are expressed as strings containing an optional join operator, possibly followed by a pair of curly braces or square brackets containing specific 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',
'>=<' => '%s FULL OUTER JOIN %s ON %s',

This operator table can be overridden through the join_syntax parameter of the "new" method.

The join conditions are expressed as 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 either condition or using, like this :

{
  operator  => '<=>',
  condition => { '%1$s.ab' => {'=' => {-ident => '%2$s.cd'}},
                 '%1$s.ef' => {'<' => {-ident => '%2$s.gh'}}},
}

The operator value 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 value 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 the -ident operator must be present to indicate that this is an identifier, not a value. 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. For example the DBIx::DataModel ORM uses such hashrefs for communicating with SQL::Abstract::More.

joins with USING clause instead of ON

In most DBMS, when column names on both sides of a join are identical, the join can be expressed as

SELECT * FROM T1 INNER JOIN T2 USING (A, B)

instead of

SELECT * FROM T1 INNER JOIN T2 ON T1.A=T2.A AND T1.B=T2.B

The advantage of this syntax with a USING clause is that the joined columns will appear only once in the results, and they do not need to be prefixed by a table name when mentioned in the select list or in the WHERE part of the SQL.

To express joins with the USING syntax in SQL::Abstract::More, just mention the column names within curly braces, without any equality operator. For example

->join(qw/Table1 {a,b} Table2 {c} Table3/)

will generate

SELECT * FROM Table1 INNER JOIN Table2 USING (a,b)
                     INNER JOIN Table3 USING (c)

In this case the internal hashref representation has the following shape :

{
  operator  => '<=>',
  using     => [ 'a', 'b'],
}

Internal hashrefs must have either a condition field or a using field; it is an error to have both.

hashref returned by join()

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.

Auxiliary methods

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.

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 two 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.

UTILITY FUNCTIONS

shallow_clone

my $clone = SQL::Abstract::More::shallow_clone($some_object, %override);

Returns a shallow copy of the object passed as argument. A new hash is created with copies of the top-level keys and values, and it is blessed into the same class as the original object. This is different from the full recursive copy performed by "clone" in Clone.

The optional %override hash is also copied into $clone; it can be used to add other attributes or to override existing attributes in $some_object.

does()

if (SQL::Abstract::More::does $ref, 'ARRAY') {...}

Very cheap version of a does() method, that checks whether a given reference can act as an ARRAY, HASH, SCALAR or CODE. This was designed for the limited internal needs of this module and of DBIx::DataModel; for more complete implementations of a does() method, see Scalar::Does, UNIVERSAL::DOES or Class::DOES.

History of SQL::Abstract and future of SQL::Abstract::More

Historical versions of SQL::Abstract

SQL::Abstract was initially designed by Nathan Wiger. Several people later contributed to various improvements until version 1.81, but the basic architecture remained essentially the same.

Then the maintenance of SQL::Abstract was passed to Matt Trout who after many years rewrote it with a largely different architecture, published under v2.000001. Matt's ambition was to provide a cleaner internal representation of SQL fragments through an abstract syntax tree (AST) on which various transformations could be performed before generating the final SQL code. These new AST features are quite powerful, but it is not clear if anybody ever used them -- and since Matt passed away, we cannot ask him.

Despite the fact that Matt took great care to preserve backwards compatibility with previous versions of SQL::Abstract, the changes were deep, and some differences in behavior were observable. Not everybody agreed with the changes, so in 2019 Peter Rabbitson published a fork of the old version 1.81 under the name SQL::Abstract::Classic.

Evolution strategy for SQL::Abstract::More

Several years later, neither SQL::Abstract nor SQL::Abstract::Classic are actively maintained, so they will probably stay in a frozen state. Under these conditions it no longer makes sense for SQL::Abstract::More to try to support both. So the strategy is to drop SQL::Abstract v2, relying on sole SQL::Abstract::Classic for this version, and then to drop it too, forking the relevant code into a standalone SQL::Abstract::More. In the present version, however, it is still possible (but deprecated) to inherit from SQL::Abstract v2 through the environment variable SQL_ABSTRACT_MORE_EXTENDS or through an -extends parameter:

use SQL::Abstract::More -extends => 'SQL::Abstract';

Readers in search for a SQL generation module may also want to review SQL::Wizard, a 2026 proposal with a fresh implementation around an internal abstract tree, but offering an external API quite close to SQL::Abstract::More.

AUTHOR

Laurent Dami, dami@cpan.org

ACKNOWLEDGEMENTS

https://github.com/djerius : support for table aliases in update() and delete(); tests and pull requests for better handling of quote_char
https://github.com/rouzier : support for -having without -order_by
https://github.com/ktat : fix for -from => ['table']
https://metacpan.org/author/DAKKAR : signaling a regression for -from => \ 'table'

SUPPORT

You can find documentation for this module with the perldoc command.

perldoc SQL::Abstract::More

The same documentation is also available at https://metacpan.org/module/SQL::Abstract::More

LICENSE AND COPYRIGHT

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 https://dev.perl.org/licenses/ for more information.

To install SQL::Abstract::More, copy and paste the appropriate command in to your terminal.

cpanm

cpanm SQL::Abstract::More

CPAN shell

perl -MCPAN -e shell
install SQL::Abstract::More

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	Go to GitHub issues (only if GitHub is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)