SQL::A...t::More(3pm)

💖 NAME 💖

SQL::Abstract::More — it's like SQL::Abstract but with extra constructs and a way more flexible API, bestie 🦋

---

✨ SYNOPSIS ✨

okay so here's the whole vibe, straight up, no edits:

  use SQL::Abstract::More;                             # will inherit from SQL::Abstract::Classic;
  #or
  use SQL::Abstract::More -extends => 'SQL::Abstract'; # will inherit from SQL::Abstract;

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

that's nine whole examples, we ate and left no crumbs 🍓

---

🌈 DESCRIPTION 🌈

This module generates SQL from Perl data structures. Yes bestie, you hand it a bunch of Perl hashes and arrayrefs and it hands you back actual SQL. It's a subclass of SQL::Abstract::Classic or SQL::Abstract, and it's fully compatible with the parent class — but it comes with a whole glow-up 💅

Here's what's new and iconic:

• 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. no more counting commas and praying 🙏
• 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 ..)) — nesting like a matryoshka, slay 🪆
• 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] ✨
• several SQL dialects can adapt the generated SQL to various DBMS vendors 🌍

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.

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. 😭 it's giving "we can't sit with them", and it's literally not our choice, that's just how it is fr fr 💀

---

✨ CLASS METHODS ✨

---

💫 import 💫

okay bestie, here's the tea: import() gets called automatically the second somebody writes use SQL::Abstract::More. You never call it yourself, it just happens, it's giving autopilot. ✨

And this is where a little decision has to get made: which class are we inheriting from? 👀 Backstory drop — this module was originally designed as an extension of SQL::Abstract in its versions prior to 1.81. Then SQL::Abstract got a whole rewrite with a largely different architecture and got published under v2.000001. And a fork of the old version is now out here living its best life as SQL::Abstract::Classic. So "SQL::Abstract::More" can inherit from either version. Originally it used SQL::Abstract as the default parent, but now the default is back to SQL::Abstract::Classic for better compatibility with previous behaviours (see for example https://rt.cpan.org/Ticket/Display.html?id=143837). 💅

The choice of the parent class is made according to the following rules :

• SQL::Abstract::Classic is the default parent.
• another parent can be specified through the -extends keyword:

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

• "Classic" is a shorthand to "SQL::Abstract::Classic"

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

• If the environment variable SQL_ABSTRACT_MORE_EXTENDS is defined, its value is used as an implicit -extends

BEGIN {$ENV{SQL_ABSTRACT_MORE_EXTENDS} = 'Classic';
       use SQL::Abstract::More; # will inherit from SQL::Abstract::Classic;
      }

• Multiple calls to import() must all resolve to the same parent; otherwise an exception is raised. 🚨 (no switching parents mid-plot, bestie)

---

🌸 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".

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

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 those are copied from SQL::Abstract::Limit -- see that module for explanations. The "OffsetFetchRows" dialect has been added here and corresponds to Oracle syntax starting from version 12c ("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. 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 (?, ?)) )

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. (iykyk, the separator is doing the heavy lifting here 💅)

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 💖

🦋 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. If "quote_char" in SQL::Abstract is defined, aliased columns will be quoted, unless they contain parentheses, 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], ...);

okay bestie so that's the vibe for sneaking special bits into your column list — you just pass them in as elements of the arrayref and it flows through. ✨

---

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. And listen — the caller is responsible for putting the SQL of the subquery within parenthesis and possibly adding a column alias; fortunately this can be done automatically when generating the subquery through a call to select() with an -as parameter :

  # 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'). ✨ the more you know ✨

---

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 — bestie the -from argument can be a few different things, and here's the whole lineup :
  • 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 according to the join method :

  -from => [-join => qw/Foo fk=pk Bar/],

• a reference to a subquery arrayref, in the form [$sql, @bind]. The caller is responsible for putting the SQL of the subquery within parenthesis and possibly adding a table alias; fortunately this can be done automatically when generating the subquery through a call to select() with an -as parameter :

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

• 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. This syntax is deprecated : use the -join feature instead. 🚨

  $sqla->select(-from => [qw/Foo Bar Buz/], ...) # SELECT FROM Foo, Bar, Buz ..

• a simple scalarref. This is for compatibility with the old SQL::Abstract API. The result is strictly equivalent to passing the scalar directly. This syntax is deprecated. 🚨

  $sqla->select(-from => \ "Foo", ...) # SELECT FROM Foo ..

• -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, including parenthesized subqueries; 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. 🦋 nesting era 🦋
• -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). 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.
• -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. so cute, so useful 🎀

---

💖 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,
    -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 => ...},
  );

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

Parameters -into and -values are passed verbatim to the parent method. no extra seasoning, straight through

Parameters -select and -columns are used for selecting from subqueries -- this is incompatible with the -values parameter. 🚨 pick a lane bestie

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

Optional parameter -add_sql is 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

dialect-specific spice, love that for you 🌈

---

💖 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,
          -returning => $return_structure,
          -add_sql   => $keyword,
        );

Same energy as the insert method up above, no notes 💅. You can still hand it positional params — that's there for backwards compatibility with the old API — but honestly? Use the named ones. They make the client code so much more readable and that's the whole point, fr fr.

Little heads up: almost no DBMS out there supports the -order_by and -limit params, but MySQL does — peep <http://dev.mysql.com/doc/refman/5.6/en/update.html> if you wanna see.

The optional -returning param does exactly what it does for insert. Iconic behavior, consistent queen.

The optional -add_sql param is for certain specific SQL dialects, and it injects extra SQL keywords right 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
  ($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 params are supported for backwards compat with the old API — but named params are the move, because they make your client's code readable. We love a readable client 💖.

Again: barely any DBMS supports -order_by and -limit, but MySQL does — see <http://dev.mysql.com/doc/refman/5.6/en/update.html>.

The optional -add_sql param is for some specific SQL dialects, and it injects extra SQL keywords right 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,
     );

This hands you back a brand new instance with an encapsulated common table expression (CTE) baked in — think of it like a local view you can then use as a table name for the rest of your SQL statement. Need the lore? See <https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL> for an explanation of these expressions, or, if you're on Oracle, go read the docs about so-called subquery factoring clauses in SELECT statements.

Any 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. Automatic. We stan ✨.

The arguments to with_recursive() come in as a list of arrayrefs; each arrayref = one table expression, with these named params:

• -table — the name you're assigning to the table expression.
• -columns — an optional list of column aliases to assign to the columns coming out of 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 gets added after the table expression. You might need this for stuff like an Oracle cycle clause, e.g.

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

If there's only one table expression, its arguments can be passed directly as an array instead of wrapping it in a single arrayref. Convenience, bestie 🎀.

---

🏷️ table_alias 🏷️

  my $sql = $sqla->table_alias($table_name, $alias);

Returns the SQL fragment for aliasing a table. And if $alias is empty? It just gives you back $table_name. Simple as that.

---

column_alias

Like table_alias, but for column aliasing. Same vibe, different column. 💅

---

limit_offset

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

Generates ($sql, @bind) for a LIMIT-OFFSET clause. That's it, that's the whole job 🫶.

---

🔗 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 info for a JOIN clause, taking as input a whole collection of joined tables plus their join conditions. This example gives you the whole feel 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
     ]);

And that 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. Odd positions are tables, even positions are joins, iykyk.

Table specifications 🌸

A table spec for join is a string with the table name, possibly followed by a vertical bar and an alias name. So like Table1 or Table1|t1 are both valid table specs.

These get converted into internal hashrefs with keys sql, bind, name, aliased_tables, like this:

  {
    sql            => "Table1 AS t1"
    bind           => [],
    name           => "t1"
    aliased_tables => {"t1" => "Table1"}
  }

And you can pass hashrefs like that directly as arguments, instead of the simple string representation. Your call 🦋.

Join specifications ✨

A join spec is a string with an optional join operator, possibly followed by a pair of curly braces or square brackets containing the join conditions.

The default builtin join operators are <=>, =>, <=, ==, and they map to these 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. Customize away, main character 👑.

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 written explicitly using dot notation, or left implicit — and if you leave them implicit they get filled in automatically from the names of the operands on the left-hand side and right-hand side of the join. So thoughtful of it honestly.

Strings inside quotes get treated as bind values instead of column names; and pairs of quotes inside those values become single quotes. Example:

  {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"

And per the usual SQL::Abstract conventions: if your list of comparisons is inside curly braces, it becomes an AND; if it's inside square brackets, it becomes an OR. Braces = AND, brackets = OR, remember it 💜.

Join specs expressed as strings get 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; its 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 an argument to where in SQL::Abstract. Anywhere the names of the left/right tables (or their aliases) are expected should be written as sprintf placeholders, i.e. %1$s and %2$s respectively. Usually the right-hand side of the condition refers to a column of the right table; in that case it shouldn't belong to the @bind list, which is exactly why we need the -ident operator from SQL::Abstract. Only when the right-hand side is a string constant (a string inside quotes) does it become a bind value. For example

  ->join(qw/Table1 {ab=cd,ef='foobar'}) Table2/)

gets parsed into

  [ 'Table1',
    { operator  => '<=>',
      condition => { '%1$s.ab' => {'=' => {-ident => '%2$s.cd'}},
                     '%1$s.ef' => {'=' => 'foobar'} },
    },
    'Table2',
  ]

Hashrefs for join specs like the ones shown above can be passed directly as arguments, instead of the simple string representation. For example the DBIx::DataModel ORM uses hashrefs for communicating with SQL::Abstract::More. Fancy 💅.

joins with USING clause instead of ON

In most DBMS, when the column names on both sides of a join are identical, you can express the join 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 the USING syntax is that the joined columns appear only once in the results, and they don't need to be prefixed by a table name if they're needed in the select list or in the WHERE part of the SQL. Lowkey a glow-up for your query 🫶.

To express joins with the USING syntax in SQL::Abstract::More, just drop the column names inside curly braces with no equality operator at all. 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 looks like this:

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

When these internal hashrefs are generated directly by the client code, they must have either a condition field or a using field; having both is an error. Pick a lane bestie, you literally cannot have both 🚫.

💫 Return value 💫

So like, when you call join() you don't just get a string back, bestie, you get a whole hashref with these keys 💖:

• sql — a string with the generated SQL in it, the actual glow-up ✨
• bind — an arrayref of bind values, the little passengers riding along 🎀
• aliased_tables — a hashref where the keys are alias names and the values are names of the aliased tables. iykyk 🦋

---

merge_conditions 🌸

my $conditions = $sqla->merge_conditions($cond_A, $cond_B, ...);

This utility method is lowkey a lifesaver: you hand it a list of "where" conditions and it merges every single one of them into a single hashref. Like, watch — merging this

( {a => 12, b => {">" => 34}},
  {b => {"<" => 56}, c => 78} )

produces this

{a => 12, b => [-and => {">" => 34}, {"<" => 56}], c => 78});

ate, no crumbs. 💅

---

bind_params 🫶

$sqla->bind_params($sth, @bind);

For each $value in @bind, here's the vibe check it runs through:

• if the value is a scalarref, it calls

$sth->bind_param_inout($index, $value, $INOUT_MAX_LEN)

(peep "bind_param_inout" in DBI for the lore). $INOUT_MAX_LEN defaults to 99, which should be good enough for most uses, fr. But 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 it calls the method and arguments that is_bind_value_with_type hands back 🎀
• for all other cases, it calls

$sth->bind_param($index, $value);

This method is useful either as a convenience for Oracle statements shaped like "INSERT ... RETURNING ... INTO ..." (see the insert method above, babe), or as a way to tell the database driver about specific datatypes. iconic behavior honestly 💖

---

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 shaped like {dbd_attrs => \%sql_type}, then it returns ('bind_param', $orig_value, \%sql_type).
• if %args is shaped like {sqlt_size => $num}, then it returns ('bind_param_inout', $orig_value, $num).

Otherwise, it returns () — nothing, nada, no vibes detected. 🙈

---

💖 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 extra info about the values used in the statement — it's giving thoughtful, it's giving prepared. ✨

The usual type specification is a hashref {dbd_attrs => \%type}, where \%type is passed directly as the 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, bestie:

($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 you use 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. Don't skip this step, it's not optional, periodt. 💀

---

🌈 UTILITY FUNCTIONS 🌈

shallow_clone 🦋

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

Returns a shallow copy of the object you passed in. 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. Not to be confused with the full recursive copy performed by "clone" in Clone — this one is shallow, like, surface-level only, no deep dive. 🫶

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. Main character energy, honestly. 👑

---

does() 💅

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

A 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 — it's not trying to be everything, it's just doing its little job. ✨

For more complete implementations of a does() method, go check out Scalar::Does, UNIVERSAL::DOES or Class::DOES. iykyk 😌

---

💜 AUTHOR 💜

Laurent Dami, <laurent dot dami at cpan dot org> — the actual legend, no cap. 👑

---

🎀 ACKNOWLEDGEMENTS 🎀

Shoutout to the besties who made this better:

• <https://github.com/djerius> — support for table aliases in update() and delete() 🔥
• <https://github.com/rouzier> — support for -having without -order_by ✨
• <https://github.com/ktat> — pull request for fixing -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> — bookmark it, babe. 🎀

---

⭐ LICENSE AND COPYRIGHT ⭐

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

---

And that's a wrap, bestie 💖 this page was served up by perl v5.40.1 on 2025-09-28 — go forth and write SQL that slays, you absolute icon. 🦋✨👑