Provided by: libsql-abstract-more-perl_1.17-2_all bug

NAME

       SQL::Abstract::More - extension of SQL::Abstract with more constructs and more flexible
       API

DESCRIPTION

       Generates SQL from Perl datastructures.  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 explation 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 abreviations 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 responsability
           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,
         );

       This works in the same spirit as the "insert" method above.  Named parameters to the
       "update()" method are just syntactic sugar for better readability of the client's code;
       they are passed verbatim to the parent method.

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

       Named parameters to the "delete()" method are just syntactic sugar for better readability
       of the client's code; they are passed verbatim to the parent method.

   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}  Table3
                         =>{t1.mn=op}     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
                       LEFT JOIN Table4       ON t1.mn=Table4.op

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

       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 sprinf
       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". In most cases the right-hand side of the condition should not belong to the @bind
       list, so this is why we need to use the "-ident" operator from SQL::Abstract.

       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.

       ==head2 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.