/ 
SQL-Abstract-More-0.01
River stage one • 6 direct dependents • 7 total dependents
/ SQL::Abstract::More

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 it handles a few additional SQL constructs, and provides a different API with named parameters instead of positional parameters, so that various SQL fragments are more easily identified.

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.

This software is not yet fully stable; future versions may introduce some minor differences in the calling interface.

SYNOPSIS

my $sqla = SQL::Abstract::More->new();

my ($sql, @bind) = $sqla->select(
 -columns  => [-distinct => qw/col1 col2/],
 -from     => 'Foo',
 -where    => {bar => {">" => 123}},
 -order_by => ['bar'],
 -limit    => 100,
 -offset   => 300,
);

my ($sql, @bind) = $sqla->select(
  -columns => [         qw/Foo.col_A|a           Bar.col_B|b /],
  -from    => [-join => qw/Foo           fk=pk   Bar         /],
);

my $merged = $sqla->merge_conditions($cond_A, $cond_B, ...);
my ($sql, @bind) = $sqla->select(..., -where => $merged, ..);


TODO
$sqla->insert(
  -into   => ..
  -values => 
)
$sqla->update(
  -table => 
  -set   =>
  -where =>
)
$sqla->delete (
  -from  => 
  -where =>
)

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 or LimitYX; 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.

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

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.

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.

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
my ($sql, @bind) = $sqla->select($columns, $table, $where, $order);

# named parameters, handled in this class 
my ($sql, @bind) = $sqla->select(
  -columns  => \@columns,
    # OR: -columns => [-distinct => @columns],
  -from     => $table || \@joined_tables,
  -where    => \%where,
  -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 => \%where

\%where is a reference to a hash or array of criteria that will be translated into SQL clauses. In most cases, this will just be something like {col1 => 'val1', col2 => 'val2'}; see SQL::Abstract::select for detailed description of the structure of that hash or array. It can also be a plain SQL string like "col1 IN (3, 5, 7, 11) OR col2 IS NOT NULL".

-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. Alternatively, columns can be specified as hashrefs in the form {-asc => $column_name} or {-desc => $column_name}.

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.

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

my ($sql, @bind) = $sqla->limit_offset($limit, $offset);

Generates ($sql, @bind) for a LIMIT-OFFSET clause.

join

my ($sql, @bind) = $sqla->join(
  <table0> <join_1> <table_1> ... <join_n> <table_n>
);

Generates ($sql, @bind) 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' => \'= %2$s.cd',
                 '%1$s.ef' => \'= 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. Beware that the right-hand side of the condition should most likely not belong to the @bind list, so in order to prevent that you need to prepend a backslash in front of strings on the right-hand side ... but then you also need to supply the '=' comparison operator.

Hashrefs for join specifications can be passed directly as arguments, instead of the simple string representation.

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

Future versions may include some of these features :

  • maybe named parameters for insert/update/delete. These would not be extremely useful; but for the sake of consistency it's probably worth implementing.

  • support for WITH initial clauses, and WITH RECURSIVE.

  • suport 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()
INSERT .. RETURNING

  • support for MySQL LOCK_IN_SHARE_MODE

  • support for UNION, INTERSECT, EXCEPT|MINUS, etc. The syntax will probably be something like

    select(-columns => ..
       -from    => ...
       -union => [-columns =>
                  -from   => 
                  -intersect => [
                      ..
                    ]
# beware : ORDER_BY / LIMIT come after the union

  • 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:

LICENSE AND COPYRIGHT

Copyright 2011 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.

2 POD Errors

The following errors were encountered while parsing the POD:

Around line 889:

Unknown directive: =head

Around line 947:

=end TODO without matching =begin. (Stack: [empty])