.\" Automatically generated by Pod::Man 4.14 (Pod::Simple 3.40)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is >0, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{\
.    if \nF \{\
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{\
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\" ========================================================================
.\"
.IX Title "SQL::Abstract 3pm"
.TH SQL::Abstract 3pm "2021-09-30" "perl v5.32.1" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
SQL::Abstract \- Generate SQL from Perl data structures
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
.Vb 1
\&    use SQL::Abstract;
\&
\&    my $sql = SQL::Abstract\->new;
\&
\&    my($stmt, @bind) = $sql\->select($source, \e@fields, \e%where, $order);
\&
\&    my($stmt, @bind) = $sql\->insert($table, \e%fieldvals || \e@values);
\&
\&    my($stmt, @bind) = $sql\->update($table, \e%fieldvals, \e%where);
\&
\&    my($stmt, @bind) = $sql\->delete($table, \e%where);
\&
\&    # Then, use these in your DBI statements
\&    my $sth = $dbh\->prepare($stmt);
\&    $sth\->execute(@bind);
\&
\&    # Just generate the WHERE clause
\&    my($stmt, @bind) = $sql\->where(\e%where, $order);
\&
\&    # Return values in the same order, for hashed queries
\&    # See PERFORMANCE section for more details
\&    my @bind = $sql\->values(\e%fieldvals);
.Ve
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
This module was inspired by the excellent DBIx::Abstract.
However, in using that module I found that what I really wanted
to do was generate \s-1SQL,\s0 but still retain complete control over my
statement handles and use the \s-1DBI\s0 interface. So, I set out to
create an abstract \s-1SQL\s0 generation module.
.PP
While based on the concepts used by DBIx::Abstract, there are
several important differences, especially when it comes to \s-1WHERE\s0
clauses. I have modified the concepts used to make the \s-1SQL\s0 easier
to generate from Perl data structures and, \s-1IMO,\s0 more intuitive.
The underlying idea is for this module to do what you mean, based
on the data structures you provide it. The big advantage is that
you don't have to modify your code every time your data changes,
as this module figures it out.
.PP
To begin with, an \s-1SQL INSERT\s0 is as easy as just specifying a hash
of \f(CW\*(C`key=value\*(C'\fR pairs:
.PP
.Vb 7
\&    my %data = (
\&        name => \*(AqJimbo Bobson\*(Aq,
\&        phone => \*(Aq123\-456\-7890\*(Aq,
\&        address => \*(Aq42 Sister Lane\*(Aq,
\&        city => \*(AqSt. Louis\*(Aq,
\&        state => \*(AqLouisiana\*(Aq,
\&    );
.Ve
.PP
The \s-1SQL\s0 can then be generated with this:
.PP
.Vb 1
\&    my($stmt, @bind) = $sql\->insert(\*(Aqpeople\*(Aq, \e%data);
.Ve
.PP
Which would give you something like this:
.PP
.Vb 5
\&    $stmt = "INSERT INTO people
\&                    (address, city, name, phone, state)
\&                    VALUES (?, ?, ?, ?, ?)";
\&    @bind = (\*(Aq42 Sister Lane\*(Aq, \*(AqSt. Louis\*(Aq, \*(AqJimbo Bobson\*(Aq,
\&             \*(Aq123\-456\-7890\*(Aq, \*(AqLouisiana\*(Aq);
.Ve
.PP
These are then used directly in your \s-1DBI\s0 code:
.PP
.Vb 2
\&    my $sth = $dbh\->prepare($stmt);
\&    $sth\->execute(@bind);
.Ve
.SS "Inserting and Updating Arrays"
.IX Subsection "Inserting and Updating Arrays"
If your database has array types (like for example Postgres),
activate the special option \f(CW\*(C`array_datatypes => 1\*(C'\fR
when creating the \f(CW\*(C`SQL::Abstract\*(C'\fR object.
Then you may use an arrayref to insert and update database array types:
.PP
.Vb 4
\&    my $sql = SQL::Abstract\->new(array_datatypes => 1);
\&    my %data = (
\&        planets => [qw/Mercury Venus Earth Mars/]
\&    );
\&
\&    my($stmt, @bind) = $sql\->insert(\*(Aqsolar_system\*(Aq, \e%data);
.Ve
.PP
This results in:
.PP
.Vb 1
\&    $stmt = "INSERT INTO solar_system (planets) VALUES (?)"
\&
\&    @bind = ([\*(AqMercury\*(Aq, \*(AqVenus\*(Aq, \*(AqEarth\*(Aq, \*(AqMars\*(Aq]);
.Ve
.SS "Inserting and Updating \s-1SQL\s0"
.IX Subsection "Inserting and Updating SQL"
In order to apply \s-1SQL\s0 functions to elements of your \f(CW%data\fR you may
specify a reference to an arrayref for the given hash value. For example,
if you need to execute the Oracle \f(CW\*(C`to_date\*(C'\fR function on a value, you can
say something like this:
.PP
.Vb 4
\&    my %data = (
\&        name => \*(AqBill\*(Aq,
\&        date_entered => \e[ "to_date(?,\*(AqMM/DD/YYYY\*(Aq)", "03/02/2003" ],
\&    );
.Ve
.PP
The first value in the array is the actual \s-1SQL.\s0 Any other values are
optional and would be included in the bind values array. This gives
you:
.PP
.Vb 1
\&    my($stmt, @bind) = $sql\->insert(\*(Aqpeople\*(Aq, \e%data);
\&
\&    $stmt = "INSERT INTO people (name, date_entered)
\&                VALUES (?, to_date(?,\*(AqMM/DD/YYYY\*(Aq))";
\&    @bind = (\*(AqBill\*(Aq, \*(Aq03/02/2003\*(Aq);
.Ve
.PP
An \s-1UPDATE\s0 is just as easy, all you change is the name of the function:
.PP
.Vb 1
\&    my($stmt, @bind) = $sql\->update(\*(Aqpeople\*(Aq, \e%data);
.Ve
.PP
Notice that your \f(CW%data\fR isn't touched; the module will generate
the appropriately quirky \s-1SQL\s0 for you automatically. Usually you'll
want to specify a \s-1WHERE\s0 clause for your \s-1UPDATE,\s0 though, which is
where handling \f(CW%where\fR hashes comes in handy...
.SS "Complex where statements"
.IX Subsection "Complex where statements"
This module can generate pretty complicated \s-1WHERE\s0 statements
easily. For example, simple \f(CW\*(C`key=value\*(C'\fR pairs are taken to mean
equality, and if you want to see if a field is within a set
of values, you can use an arrayref. Let's say we wanted to
\&\s-1SELECT\s0 some data based on this criteria:
.PP
.Vb 5
\&    my %where = (
\&       requestor => \*(Aqinna\*(Aq,
\&       worker => [\*(Aqnwiger\*(Aq, \*(Aqrcwe\*(Aq, \*(Aqsfz\*(Aq],
\&       status => { \*(Aq!=\*(Aq, \*(Aqcompleted\*(Aq }
\&    );
\&
\&    my($stmt, @bind) = $sql\->select(\*(Aqtickets\*(Aq, \*(Aq*\*(Aq, \e%where);
.Ve
.PP
The above would give you something like this:
.PP
.Vb 4
\&    $stmt = "SELECT * FROM tickets WHERE
\&                ( requestor = ? ) AND ( status != ? )
\&                AND ( worker = ? OR worker = ? OR worker = ? )";
\&    @bind = (\*(Aqinna\*(Aq, \*(Aqcompleted\*(Aq, \*(Aqnwiger\*(Aq, \*(Aqrcwe\*(Aq, \*(Aqsfz\*(Aq);
.Ve
.PP
Which you could then use in \s-1DBI\s0 code like so:
.PP
.Vb 2
\&    my $sth = $dbh\->prepare($stmt);
\&    $sth\->execute(@bind);
.Ve
.PP
Easy, eh?
.SH "METHODS"
.IX Header "METHODS"
The methods are simple. There's one for every major \s-1SQL\s0 operation,
and a constructor you use first. The arguments are specified in a
similar order for each method (table, then fields, then a where
clause) to try and simplify things.
.SS "new(option => 'value')"
.IX Subsection "new(option => 'value')"
The \f(CW\*(C`new()\*(C'\fR function takes a list of options and values, and returns
a new \fBSQL::Abstract\fR object which can then be used to generate \s-1SQL\s0
through the methods below. The options accepted are:
.IP "case" 4
.IX Item "case"
If set to 'lower', then \s-1SQL\s0 will be generated in all lowercase. By
default \s-1SQL\s0 is generated in \*(L"textbook\*(R" case meaning something like:
.Sp
.Vb 1
\&    SELECT a_field FROM a_table WHERE some_field LIKE \*(Aq%someval%\*(Aq
.Ve
.Sp
Any setting other than 'lower' is ignored.
.IP "cmp" 4
.IX Item "cmp"
This determines what the default comparison operator is. By default
it is \f(CW\*(C`=\*(C'\fR, meaning that a hash like this:
.Sp
.Vb 1
\&    %where = (name => \*(Aqnwiger\*(Aq, email => \*(Aqnate@wiger.org\*(Aq);
.Ve
.Sp
Will generate \s-1SQL\s0 like this:
.Sp
.Vb 1
\&    WHERE name = \*(Aqnwiger\*(Aq AND email = \*(Aqnate@wiger.org\*(Aq
.Ve
.Sp
However, you may want loose comparisons by default, so if you set
\&\f(CW\*(C`cmp\*(C'\fR to \f(CW\*(C`like\*(C'\fR you would get \s-1SQL\s0 such as:
.Sp
.Vb 1
\&    WHERE name like \*(Aqnwiger\*(Aq AND email like \*(Aqnate@wiger.org\*(Aq
.Ve
.Sp
You can also override the comparison on an individual basis \- see
the huge section on \*(L"\s-1WHERE CLAUSES\*(R"\s0 at the bottom.
.IP "sqltrue, sqlfalse" 4
.IX Item "sqltrue, sqlfalse"
Expressions for inserting boolean values within \s-1SQL\s0 statements.
By default these are \f(CW\*(C`1=1\*(C'\fR and \f(CW\*(C`1=0\*(C'\fR. They are used
by the special operators \f(CW\*(C`\-in\*(C'\fR and \f(CW\*(C`\-not_in\*(C'\fR for generating
correct \s-1SQL\s0 even when the argument is an empty array (see below).
.IP "logic" 4
.IX Item "logic"
This determines the default logical operator for multiple \s-1WHERE\s0
statements in arrays or hashes. If absent, the default logic is \*(L"or\*(R"
for arrays, and \*(L"and\*(R" for hashes. This means that a \s-1WHERE\s0
array of the form:
.Sp
.Vb 4
\&    @where = (
\&        event_date => {\*(Aq>=\*(Aq, \*(Aq2/13/99\*(Aq},
\&        event_date => {\*(Aq<=\*(Aq, \*(Aq4/24/03\*(Aq},
\&    );
.Ve
.Sp
will generate \s-1SQL\s0 like this:
.Sp
.Vb 1
\&    WHERE event_date >= \*(Aq2/13/99\*(Aq OR event_date <= \*(Aq4/24/03\*(Aq
.Ve
.Sp
This is probably not what you want given this query, though (look
at the dates). To change the \*(L"\s-1OR\*(R"\s0 to an \*(L"\s-1AND\*(R",\s0 simply specify:
.Sp
.Vb 1
\&    my $sql = SQL::Abstract\->new(logic => \*(Aqand\*(Aq);
.Ve
.Sp
Which will change the above \f(CW\*(C`WHERE\*(C'\fR to:
.Sp
.Vb 1
\&    WHERE event_date >= \*(Aq2/13/99\*(Aq AND event_date <= \*(Aq4/24/03\*(Aq
.Ve
.Sp
The logic can also be changed locally by inserting
a modifier in front of an arrayref:
.Sp
.Vb 2
\&    @where = (\-and => [event_date => {\*(Aq>=\*(Aq, \*(Aq2/13/99\*(Aq},
\&                       event_date => {\*(Aq<=\*(Aq, \*(Aq4/24/03\*(Aq} ]);
.Ve
.Sp
See the \*(L"\s-1WHERE CLAUSES\*(R"\s0 section for explanations.
.IP "convert" 4
.IX Item "convert"
This will automatically convert comparisons using the specified \s-1SQL\s0
function for both column and value. This is mostly used with an argument
of \f(CW\*(C`upper\*(C'\fR or \f(CW\*(C`lower\*(C'\fR, so that the \s-1SQL\s0 will have the effect of
case-insensitive \*(L"searches\*(R". For example, this:
.Sp
.Vb 2
\&    $sql = SQL::Abstract\->new(convert => \*(Aqupper\*(Aq);
\&    %where = (keywords => \*(AqMaKe iT CAse inSeNSItive\*(Aq);
.Ve
.Sp
Will turn out the following \s-1SQL:\s0
.Sp
.Vb 1
\&    WHERE upper(keywords) like upper(\*(AqMaKe iT CAse inSeNSItive\*(Aq)
.Ve
.Sp
The conversion can be \f(CW\*(C`upper()\*(C'\fR, \f(CW\*(C`lower()\*(C'\fR, or any other \s-1SQL\s0 function
that can be applied symmetrically to fields (actually \fBSQL::Abstract\fR does
not validate this option; it will just pass through what you specify verbatim).
.IP "bindtype" 4
.IX Item "bindtype"
This is a kludge because many databases suck. For example, you can't
just bind values using \s-1DBI\s0's \f(CW\*(C`execute()\*(C'\fR for Oracle \f(CW\*(C`CLOB\*(C'\fR or \f(CW\*(C`BLOB\*(C'\fR fields.
Instead, you have to use \f(CW\*(C`bind_param()\*(C'\fR:
.Sp
.Vb 2
\&    $sth\->bind_param(1, \*(Aqreg data\*(Aq);
\&    $sth\->bind_param(2, $lots, {ora_type => ORA_CLOB});
.Ve
.Sp
The problem is, \fBSQL::Abstract\fR will normally just return a \f(CW@bind\fR array,
which loses track of which field each slot refers to. Fear not.
.Sp
If you specify \f(CW\*(C`bindtype\*(C'\fR in new, you can determine how \f(CW@bind\fR is returned.
Currently, you can specify either \f(CW\*(C`normal\*(C'\fR (default) or \f(CW\*(C`columns\*(C'\fR. If you
specify \f(CW\*(C`columns\*(C'\fR, you will get an array that looks like this:
.Sp
.Vb 2
\&    my $sql = SQL::Abstract\->new(bindtype => \*(Aqcolumns\*(Aq);
\&    my($stmt, @bind) = $sql\->insert(...);
\&
\&    @bind = (
\&        [ \*(Aqcolumn1\*(Aq, \*(Aqvalue1\*(Aq ],
\&        [ \*(Aqcolumn2\*(Aq, \*(Aqvalue2\*(Aq ],
\&        [ \*(Aqcolumn3\*(Aq, \*(Aqvalue3\*(Aq ],
\&    );
.Ve
.Sp
You can then iterate through this manually, using \s-1DBI\s0's \f(CW\*(C`bind_param()\*(C'\fR.
.Sp
.Vb 10
\&    $sth\->prepare($stmt);
\&    my $i = 1;
\&    for (@bind) {
\&        my($col, $data) = @$_;
\&        if ($col eq \*(Aqdetails\*(Aq || $col eq \*(Aqcomments\*(Aq) {
\&            $sth\->bind_param($i, $data, {ora_type => ORA_CLOB});
\&        } elsif ($col eq \*(Aqimage\*(Aq) {
\&            $sth\->bind_param($i, $data, {ora_type => ORA_BLOB});
\&        } else {
\&            $sth\->bind_param($i, $data);
\&        }
\&        $i++;
\&    }
\&    $sth\->execute;      # execute without @bind now
.Ve
.Sp
Now, why would you still use \fBSQL::Abstract\fR if you have to do this crap?
Basically, the advantage is still that you don't have to care which fields
are or are not included. You could wrap that above \f(CW\*(C`for\*(C'\fR loop in a simple
sub called \f(CW\*(C`bind_fields()\*(C'\fR or something and reuse it repeatedly. You still
get a layer of abstraction over manual \s-1SQL\s0 specification.
.Sp
Note that if you set \*(L"bindtype\*(R" to \f(CW\*(C`columns\*(C'\fR, the \f(CW\*(C`\e[ $sql, @bind ]\*(C'\fR
construct (see \*(L"Literal \s-1SQL\s0 with placeholders and bind values (subqueries)\*(R")
will expect the bind values in this format.
.IP "quote_char" 4
.IX Item "quote_char"
This is the character that a table or column name will be quoted
with.  By default this is an empty string, but you could set it to
the character \f(CW\*(C`\`\*(C'\fR, to generate \s-1SQL\s0 like this:
.Sp
.Vb 1
\&  SELECT \`a_field\` FROM \`a_table\` WHERE \`some_field\` LIKE \*(Aq%someval%\*(Aq
.Ve
.Sp
Alternatively, you can supply an array ref of two items, the first being the left
hand quote character, and the second the right hand quote character. For
example, you could supply \f(CW\*(C`[\*(Aq[\*(Aq,\*(Aq]\*(Aq]\*(C'\fR for \s-1SQL\s0 Server 2000 compliant quotes
that generates \s-1SQL\s0 like this:
.Sp
.Vb 1
\&  SELECT [a_field] FROM [a_table] WHERE [some_field] LIKE \*(Aq%someval%\*(Aq
.Ve
.Sp
Quoting is useful if you have tables or columns names that are reserved
words in your database's \s-1SQL\s0 dialect.
.IP "escape_char" 4
.IX Item "escape_char"
This is the character that will be used to escape \*(L"quote_char\*(R"s appearing
in an identifier before it has been quoted.
.Sp
The parameter default in case of a single \*(L"quote_char\*(R" character is the quote
character itself.
.Sp
When opening-closing-style quoting is used (\*(L"quote_char\*(R" is an arrayref)
this parameter defaults to the \fBclosing (right)\fR \*(L"quote_char\*(R". Occurrences
of the \fBopening (left)\fR \*(L"quote_char\*(R" within the identifier are currently left
untouched. The default for opening-closing-style quotes may change in future
versions, thus you are \fBstrongly encouraged\fR to specify the escape character
explicitly.
.IP "name_sep" 4
.IX Item "name_sep"
This is the character that separates a table and column name.  It is
necessary to specify this when the \f(CW\*(C`quote_char\*(C'\fR option is selected,
so that tables and column names can be individually quoted like this:
.Sp
.Vb 1
\&  SELECT \`table\`.\`one_field\` FROM \`table\` WHERE \`table\`.\`other_field\` = 1
.Ve
.IP "injection_guard" 4
.IX Item "injection_guard"
A regular expression \f(CW\*(C`qr/.../\*(C'\fR that is applied to any \f(CW\*(C`\-function\*(C'\fR and unquoted
column name specified in a query structure. This is a safety mechanism to avoid
injection attacks when mishandling user input e.g.:
.Sp
.Vb 2
\&  my %condition_as_column_value_pairs = get_values_from_user();
\&  $sqla\->select( ... , \e%condition_as_column_value_pairs );
.Ve
.Sp
If the expression matches an exception is thrown. Note that literal \s-1SQL\s0
supplied via \f(CW\*(C`\e\*(Aq...\*(Aq\*(C'\fR or \f(CW\*(C`\e[\*(Aq...\*(Aq]\*(C'\fR is \fBnot\fR checked in any way.
.Sp
Defaults to checking for \f(CW\*(C`;\*(C'\fR and the \f(CW\*(C`GO\*(C'\fR keyword (TransactSQL)
.IP "array_datatypes" 4
.IX Item "array_datatypes"
When this option is true, arrayrefs in \s-1INSERT\s0 or \s-1UPDATE\s0 are
interpreted as array datatypes and are passed directly
to the \s-1DBI\s0 layer.
When this option is false, arrayrefs are interpreted
as literal \s-1SQL,\s0 just like refs to arrayrefs
(but this behavior is for backwards compatibility; when writing
new queries, use the \*(L"reference to arrayref\*(R" syntax
for literal \s-1SQL\s0).
.IP "special_ops" 4
.IX Item "special_ops"
Takes a reference to a list of \*(L"special operators\*(R"
to extend the syntax understood by SQL::Abstract.
See section \*(L"\s-1SPECIAL OPERATORS\*(R"\s0 for details.
.IP "unary_ops" 4
.IX Item "unary_ops"
Takes a reference to a list of \*(L"unary operators\*(R"
to extend the syntax understood by SQL::Abstract.
See section \*(L"\s-1UNARY OPERATORS\*(R"\s0 for details.
.SS "insert($table, \e@values || \e%fieldvals, \e%options)"
.IX Subsection "insert($table, @values || %fieldvals, %options)"
This is the simplest function. You simply give it a table name
and either an arrayref of values or hashref of field/value pairs.
It returns an \s-1SQL INSERT\s0 statement and a list of bind values.
See the sections on \*(L"Inserting and Updating Arrays\*(R" and
\&\*(L"Inserting and Updating \s-1SQL\*(R"\s0 for information on how to insert
with those data types.
.PP
The optional \f(CW\*(C`\e%options\*(C'\fR hash reference may contain additional
options to generate the insert \s-1SQL.\s0 Currently supported options
are:
.IP "returning" 4
.IX Item "returning"
Takes either a scalar of raw \s-1SQL\s0 fields, or an array reference of
field names, and adds on an \s-1SQL\s0 \f(CW\*(C`RETURNING\*(C'\fR statement at the end.
This allows you to return data generated by the insert statement
(such as row IDs) without performing another \f(CW\*(C`SELECT\*(C'\fR statement.
Note, however, this is not part of the \s-1SQL\s0 standard and may not
be supported by all database engines.
.SS "update($table, \e%fieldvals, \e%where, \e%options)"
.IX Subsection "update($table, %fieldvals, %where, %options)"
This takes a table, hashref of field/value pairs, and an optional
hashref \s-1WHERE\s0 clause. It returns an \s-1SQL UPDATE\s0 function and a list
of bind values.
See the sections on \*(L"Inserting and Updating Arrays\*(R" and
\&\*(L"Inserting and Updating \s-1SQL\*(R"\s0 for information on how to insert
with those data types.
.PP
The optional \f(CW\*(C`\e%options\*(C'\fR hash reference may contain additional
options to generate the update \s-1SQL.\s0 Currently supported options
are:
.IP "returning" 4
.IX Item "returning"
See the \f(CW\*(C`returning\*(C'\fR option to
insert.
.ie n .SS "select($source, $fields, $where, $order)"
.el .SS "select($source, \f(CW$fields\fP, \f(CW$where\fP, \f(CW$order\fP)"
.IX Subsection "select($source, $fields, $where, $order)"
This returns a \s-1SQL SELECT\s0 statement and associated list of bind values, as
specified by the arguments:
.ie n .IP "$source" 4
.el .IP "\f(CW$source\fR" 4
.IX Item "$source"
Specification of the '\s-1FROM\s0' part of the statement.
The argument can be either a plain scalar (interpreted as a table
name, will be quoted), or an arrayref (interpreted as a list
of table names, joined by commas, quoted), or a scalarref
(literal \s-1SQL,\s0 not quoted).
.ie n .IP "$fields" 4
.el .IP "\f(CW$fields\fR" 4
.IX Item "$fields"
Specification of the list of fields to retrieve from
the source.
The argument can be either an arrayref (interpreted as a list
of field names, will be joined by commas and quoted), or a
plain scalar (literal \s-1SQL,\s0 not quoted).
Please observe that this \s-1API\s0 is not as flexible as that of
the first argument \f(CW$source\fR, for backwards compatibility reasons.
.ie n .IP "$where" 4
.el .IP "\f(CW$where\fR" 4
.IX Item "$where"
Optional argument to specify the \s-1WHERE\s0 part of the query.
The argument is most often a hashref, but can also be
an arrayref or plain scalar \*(--
see section \s-1WHERE\s0 clause for details.
.ie n .IP "$order" 4
.el .IP "\f(CW$order\fR" 4
.IX Item "$order"
Optional argument to specify the \s-1ORDER BY\s0 part of the query.
The argument can be a scalar, a hashref or an arrayref
\&\*(-- see section \s-1ORDER BY\s0 clause
for details.
.SS "delete($table, \e%where, \e%options)"
.IX Subsection "delete($table, %where, %options)"
This takes a table name and optional hashref \s-1WHERE\s0 clause.
It returns an \s-1SQL DELETE\s0 statement and list of bind values.
.PP
The optional \f(CW\*(C`\e%options\*(C'\fR hash reference may contain additional
options to generate the delete \s-1SQL.\s0 Currently supported options
are:
.IP "returning" 4
.IX Item "returning"
See the \f(CW\*(C`returning\*(C'\fR option to
insert.
.ie n .SS "where(\e%where, $order)"
.el .SS "where(\e%where, \f(CW$order\fP)"
.IX Subsection "where(%where, $order)"
This is used to generate just the \s-1WHERE\s0 clause. For example,
if you have an arbitrary data structure and know what the
rest of your \s-1SQL\s0 is going to look like, but want an easy way
to produce a \s-1WHERE\s0 clause, use this. It returns an \s-1SQL WHERE\s0
clause and list of bind values.
.SS "values(\e%data)"
.IX Subsection "values(%data)"
This just returns the values from the hash \f(CW%data\fR, in the same
order that would be returned from any of the other above queries.
Using this allows you to markedly speed up your queries if you
are affecting lots of rows. See below under the \*(L"\s-1PERFORMANCE\*(R"\s0 section.
.ie n .SS "generate($any, 'number', $of, \e@data, $struct, \e%types)"
.el .SS "generate($any, 'number', \f(CW$of\fP, \e@data, \f(CW$struct\fP, \e%types)"
.IX Subsection "generate($any, 'number', $of, @data, $struct, %types)"
Warning: This is an experimental method and subject to change.
.PP
This returns arbitrarily generated \s-1SQL.\s0 It's a really basic shortcut.
It will return two different things, depending on return context:
.PP
.Vb 2
\&    my($stmt, @bind) = $sql\->generate(\*(Aqcreate table\*(Aq, \e$table, \e@fields);
\&    my $stmt_and_val = $sql\->generate(\*(Aqcreate table\*(Aq, \e$table, \e@fields);
.Ve
.PP
These would return the following:
.PP
.Vb 3
\&    # First calling form
\&    $stmt = "CREATE TABLE test (?, ?)";
\&    @bind = (field1, field2);
\&
\&    # Second calling form
\&    $stmt_and_val = "CREATE TABLE test (field1, field2)";
.Ve
.PP
Depending on what you're trying to do, it's up to you to choose the correct
format. In this example, the second form is what you would want.
.PP
By the same token:
.PP
.Vb 1
\&    $sql\->generate(\*(Aqalter session\*(Aq, { nls_date_format => \*(AqMM/YY\*(Aq });
.Ve
.PP
Might give you:
.PP
.Vb 1
\&    ALTER SESSION SET nls_date_format = \*(AqMM/YY\*(Aq
.Ve
.PP
You get the idea. Strings get their case twiddled, but everything
else remains verbatim.
.SH "EXPORTABLE FUNCTIONS"
.IX Header "EXPORTABLE FUNCTIONS"
.SS "is_plain_value"
.IX Subsection "is_plain_value"
Determines if the supplied argument is a plain value as understood by this
module:
.IP "\(bu" 4
The value is \f(CW\*(C`undef\*(C'\fR
.IP "\(bu" 4
The value is a non-reference
.IP "\(bu" 4
The value is an object with stringification overloading
.IP "\(bu" 4
The value is of the form \f(CW\*(C`{ \-value => $anything }\*(C'\fR
.PP
On failure returns \f(CW\*(C`undef\*(C'\fR, on success returns a \fBscalar\fR reference
to the original supplied argument.
.IP "\(bu" 4
Note
.Sp
The stringification overloading detection is rather advanced: it takes
into consideration not only the presence of a \f(CW""\fR overload, but if that
fails also checks for enabled
autogenerated versions of \f(CW""\fR, based
on either \f(CW\*(C`0+\*(C'\fR or \f(CW\*(C`bool\*(C'\fR.
.Sp
Unfortunately testing in the field indicates that this
detection \fBmay tickle a latent bug in perl versions before 5.018\fR,
but only when very large numbers of stringifying objects are involved.
At the time of writing ( Sep 2014 ) there is no clear explanation of
the direct cause, nor is there a manageably small test case that reliably
reproduces the problem.
.Sp
If you encounter any of the following exceptions in \fBrandom places within
your application stack\fR \- this module may be to blame:
.Sp
.Vb 3
\&  Operation "ne": no method found,
\&    left argument in overloaded package <something>,
\&    right argument in overloaded package <something>
.Ve
.Sp
or perhaps even
.Sp
.Vb 1
\&  Stub found while resolving method "???" overloading """" in package <something>
.Ve
.Sp
If you fall victim to the above \- please attempt to reduce the problem
to something that could be sent to the SQL::Abstract developers

(either publicly or privately). As a workaround in the meantime you can
set \f(CW$ENV{SQLA_ISVALUE_IGNORE_AUTOGENERATED_STRINGIFICATION}\fR to a true
value, which will most likely eliminate your problem (at the expense of
not being able to properly detect exotic forms of stringification).
.Sp
This notice and environment variable will be removed in a future version,
as soon as the underlying problem is found and a reliable workaround is
devised.
.SS "is_literal_value"
.IX Subsection "is_literal_value"
Determines if the supplied argument is a literal value as understood by this
module:
.IP "\(bu" 4
\&\f(CW\*(C`\e$sql_string\*(C'\fR
.IP "\(bu" 4
\&\f(CW\*(C`\e[ $sql_string, @bind_values ]\*(C'\fR
.PP
On failure returns \f(CW\*(C`undef\*(C'\fR, on success returns an \fBarray\fR reference
containing the unpacked version of the supplied literal \s-1SQL\s0 and bind values.
.SS "is_undef_value"
.IX Subsection "is_undef_value"
Tests for undef, whether expanded or not.
.SH "WHERE CLAUSES"
.IX Header "WHERE CLAUSES"
.SS "Introduction"
.IX Subsection "Introduction"
This module uses a variation on the idea from DBIx::Abstract. It
is \fB\s-1NOT\s0\fR, repeat \fInot\fR 100% compatible. \fBThe main logic of this
module is that things in arrays are \s-1OR\s0'ed, and things in hashes
are \s-1AND\s0'ed.\fR
.PP
The easiest way to explain is to show lots of examples. After
each \f(CW%where\fR hash shown, it is assumed you used:
.PP
.Vb 1
\&    my($stmt, @bind) = $sql\->where(\e%where);
.Ve
.PP
However, note that the \f(CW%where\fR hash can be used directly in any
of the other functions as well, as described above.
.SS "Key-value pairs"
.IX Subsection "Key-value pairs"
So, let's get started. To begin, a simple hash:
.PP
.Vb 4
\&    my %where  = (
\&        user   => \*(Aqnwiger\*(Aq,
\&        status => \*(Aqcompleted\*(Aq
\&    );
.Ve
.PP
Is converted to \s-1SQL\s0 \f(CW\*(C`key = val\*(C'\fR statements:
.PP
.Vb 2
\&    $stmt = "WHERE user = ? AND status = ?";
\&    @bind = (\*(Aqnwiger\*(Aq, \*(Aqcompleted\*(Aq);
.Ve
.PP
One common thing I end up doing is having a list of values that
a field can be in. To do this, simply specify a list inside of
an arrayref:
.PP
.Vb 4
\&    my %where  = (
\&        user   => \*(Aqnwiger\*(Aq,
\&        status => [\*(Aqassigned\*(Aq, \*(Aqin\-progress\*(Aq, \*(Aqpending\*(Aq];
\&    );
.Ve
.PP
This simple code will create the following:
.PP
.Vb 2
\&    $stmt = "WHERE user = ? AND ( status = ? OR status = ? OR status = ? )";
\&    @bind = (\*(Aqnwiger\*(Aq, \*(Aqassigned\*(Aq, \*(Aqin\-progress\*(Aq, \*(Aqpending\*(Aq);
.Ve
.PP
A field associated to an empty arrayref will be considered a
logical false and will generate 0=1.
.SS "Tests for \s-1NULL\s0 values"
.IX Subsection "Tests for NULL values"
If the value part is \f(CW\*(C`undef\*(C'\fR then this is converted to \s-1SQL\s0 <\s-1IS NULL\s0>
.PP
.Vb 4
\&    my %where  = (
\&        user   => \*(Aqnwiger\*(Aq,
\&        status => undef,
\&    );
.Ve
.PP
becomes:
.PP
.Vb 2
\&    $stmt = "WHERE user = ? AND status IS NULL";
\&    @bind = (\*(Aqnwiger\*(Aq);
.Ve
.PP
To test if a column \s-1IS NOT NULL:\s0
.PP
.Vb 4
\&    my %where  = (
\&        user   => \*(Aqnwiger\*(Aq,
\&        status => { \*(Aq!=\*(Aq, undef },
\&    );
.Ve
.SS "Specific comparison operators"
.IX Subsection "Specific comparison operators"
If you want to specify a different type of operator for your comparison,
you can use a hashref for a given column:
.PP
.Vb 4
\&    my %where  = (
\&        user   => \*(Aqnwiger\*(Aq,
\&        status => { \*(Aq!=\*(Aq, \*(Aqcompleted\*(Aq }
\&    );
.Ve
.PP
Which would generate:
.PP
.Vb 2
\&    $stmt = "WHERE user = ? AND status != ?";
\&    @bind = (\*(Aqnwiger\*(Aq, \*(Aqcompleted\*(Aq);
.Ve
.PP
To test against multiple values, just enclose the values in an arrayref:
.PP
.Vb 1
\&    status => { \*(Aq=\*(Aq, [\*(Aqassigned\*(Aq, \*(Aqin\-progress\*(Aq, \*(Aqpending\*(Aq] };
.Ve
.PP
Which would give you:
.PP
.Vb 1
\&    "WHERE status = ? OR status = ? OR status = ?"
.Ve
.PP
The hashref can also contain multiple pairs, in which case it is expanded
into an \f(CW\*(C`AND\*(C'\fR of its elements:
.PP
.Vb 4
\&    my %where  = (
\&        user   => \*(Aqnwiger\*(Aq,
\&        status => { \*(Aq!=\*(Aq, \*(Aqcompleted\*(Aq, \-not_like => \*(Aqpending%\*(Aq }
\&    );
\&
\&    # Or more dynamically, like from a form
\&    $where{user} = \*(Aqnwiger\*(Aq;
\&    $where{status}{\*(Aq!=\*(Aq} = \*(Aqcompleted\*(Aq;
\&    $where{status}{\*(Aq\-not_like\*(Aq} = \*(Aqpending%\*(Aq;
\&
\&    # Both generate this
\&    $stmt = "WHERE user = ? AND status != ? AND status NOT LIKE ?";
\&    @bind = (\*(Aqnwiger\*(Aq, \*(Aqcompleted\*(Aq, \*(Aqpending%\*(Aq);
.Ve
.PP
To get an \s-1OR\s0 instead, you can combine it with the arrayref idea:
.PP
.Vb 4
\&    my %where => (
\&         user => \*(Aqnwiger\*(Aq,
\&         priority => [ { \*(Aq=\*(Aq, 2 }, { \*(Aq>\*(Aq, 5 } ]
\&    );
.Ve
.PP
Which would generate:
.PP
.Vb 2
\&    $stmt = "WHERE ( priority = ? OR priority > ? ) AND user = ?";
\&    @bind = (\*(Aq2\*(Aq, \*(Aq5\*(Aq, \*(Aqnwiger\*(Aq);
.Ve
.PP
If you want to include literal \s-1SQL\s0 (with or without bind values), just use a
scalar reference or reference to an arrayref as the value:
.PP
.Vb 4
\&    my %where  = (
\&        date_entered => { \*(Aq>\*(Aq => \e["to_date(?, \*(AqMM/DD/YYYY\*(Aq)", "11/26/2008"] },
\&        date_expires => { \*(Aq<\*(Aq => \e"now()" }
\&    );
.Ve
.PP
Which would generate:
.PP
.Vb 2
\&    $stmt = "WHERE date_entered > to_date(?, \*(AqMM/DD/YYYY\*(Aq) AND date_expires < now()";
\&    @bind = (\*(Aq11/26/2008\*(Aq);
.Ve
.SS "Logic and nesting operators"
.IX Subsection "Logic and nesting operators"
In the example above,
there is a subtle trap if you want to say something like
this (notice the \f(CW\*(C`AND\*(C'\fR):
.PP
.Vb 1
\&    WHERE priority != ? AND priority != ?
.Ve
.PP
Because, in Perl you \fIcan't\fR do this:
.PP
.Vb 1
\&    priority => { \*(Aq!=\*(Aq => 2, \*(Aq!=\*(Aq => 1 }
.Ve
.PP
As the second \f(CW\*(C`!=\*(C'\fR key will obliterate the first. The solution
is to use the special \f(CW\*(C`\-modifier\*(C'\fR form inside an arrayref:
.PP
.Vb 2
\&    priority => [ \-and => {\*(Aq!=\*(Aq, 2},
\&                          {\*(Aq!=\*(Aq, 1} ]
.Ve
.PP
Normally, these would be joined by \f(CW\*(C`OR\*(C'\fR, but the modifier tells it
to use \f(CW\*(C`AND\*(C'\fR instead. (Hint: You can use this in conjunction with the
\&\f(CW\*(C`logic\*(C'\fR option to \f(CW\*(C`new()\*(C'\fR in order to change the way your queries
work by default.) \fBImportant:\fR Note that the \f(CW\*(C`\-modifier\*(C'\fR goes
\&\fB\s-1INSIDE\s0\fR the arrayref, as an extra first element. This will
\&\fB\s-1NOT\s0\fR do what you think it might:
.PP
.Vb 1
\&    priority => \-and => [{\*(Aq!=\*(Aq, 2}, {\*(Aq!=\*(Aq, 1}]   # WRONG!
.Ve
.PP
Here is a quick list of equivalencies, since there is some overlap:
.PP
.Vb 3
\&    # Same
\&    status => {\*(Aq!=\*(Aq, \*(Aqcompleted\*(Aq, \*(Aqnot like\*(Aq, \*(Aqpending%\*(Aq }
\&    status => [ \-and => {\*(Aq!=\*(Aq, \*(Aqcompleted\*(Aq}, {\*(Aqnot like\*(Aq, \*(Aqpending%\*(Aq}]
\&
\&    # Same
\&    status => {\*(Aq=\*(Aq, [\*(Aqassigned\*(Aq, \*(Aqin\-progress\*(Aq]}
\&    status => [ \-or => {\*(Aq=\*(Aq, \*(Aqassigned\*(Aq}, {\*(Aq=\*(Aq, \*(Aqin\-progress\*(Aq}]
\&    status => [ {\*(Aq=\*(Aq, \*(Aqassigned\*(Aq}, {\*(Aq=\*(Aq, \*(Aqin\-progress\*(Aq} ]
.Ve
.SS "Special operators: \s-1IN, BETWEEN,\s0 etc."
.IX Subsection "Special operators: IN, BETWEEN, etc."
You can also use the hashref format to compare a list of fields using the
\&\f(CW\*(C`IN\*(C'\fR comparison operator, by specifying the list as an arrayref:
.PP
.Vb 4
\&    my %where  = (
\&        status   => \*(Aqcompleted\*(Aq,
\&        reportid => { \-in => [567, 2335, 2] }
\&    );
.Ve
.PP
Which would generate:
.PP
.Vb 2
\&    $stmt = "WHERE status = ? AND reportid IN (?,?,?)";
\&    @bind = (\*(Aqcompleted\*(Aq, \*(Aq567\*(Aq, \*(Aq2335\*(Aq, \*(Aq2\*(Aq);
.Ve
.PP
The reverse operator \f(CW\*(C`\-not_in\*(C'\fR generates \s-1SQL\s0 \f(CW\*(C`NOT IN\*(C'\fR and is used in
the same way.
.PP
If the argument to \f(CW\*(C`\-in\*(C'\fR is an empty array, 'sqlfalse' is generated
(by default: \f(CW\*(C`1=0\*(C'\fR). Similarly, \f(CW\*(C`\-not_in => []\*(C'\fR generates
\&'sqltrue' (by default: \f(CW\*(C`1=1\*(C'\fR).
.PP
In addition to the array you can supply a chunk of literal sql or
literal sql with bind:
.PP
.Vb 7
\&    my %where = {
\&      customer => { \-in => \e[
\&        \*(AqSELECT cust_id FROM cust WHERE balance > ?\*(Aq,
\&        2000,
\&      ],
\&      status => { \-in => \e\*(AqSELECT status_codes FROM states\*(Aq },
\&    };
.Ve
.PP
would generate:
.PP
.Vb 5
\&    $stmt = "WHERE (
\&          customer IN ( SELECT cust_id FROM cust WHERE balance > ? )
\&      AND status IN ( SELECT status_codes FROM states )
\&    )";
\&    @bind = (\*(Aq2000\*(Aq);
.Ve
.PP
Finally, if the argument to \f(CW\*(C`\-in\*(C'\fR is not a reference, it will be
treated as a single-element array.
.PP
Another pair of operators is \f(CW\*(C`\-between\*(C'\fR and \f(CW\*(C`\-not_between\*(C'\fR,
used with an arrayref of two values:
.PP
.Vb 6
\&    my %where  = (
\&        user   => \*(Aqnwiger\*(Aq,
\&        completion_date => {
\&           \-not_between => [\*(Aq2002\-10\-01\*(Aq, \*(Aq2003\-02\-06\*(Aq]
\&        }
\&    );
.Ve
.PP
Would give you:
.PP
.Vb 1
\&    WHERE user = ? AND completion_date NOT BETWEEN ( ? AND ? )
.Ve
.PP
Just like with \f(CW\*(C`\-in\*(C'\fR all plausible combinations of literal \s-1SQL\s0
are possible:
.PP
.Vb 9
\&    my %where = {
\&      start0 => { \-between => [ 1, 2 ] },
\&      start1 => { \-between => \e["? AND ?", 1, 2] },
\&      start2 => { \-between => \e"lower(x) AND upper(y)" },
\&      start3 => { \-between => [
\&        \e"lower(x)",
\&        \e["upper(?)", \*(Aqstuff\*(Aq ],
\&      ] },
\&    };
.Ve
.PP
Would give you:
.PP
.Vb 7
\&    $stmt = "WHERE (
\&          ( start0 BETWEEN ? AND ?                )
\&      AND ( start1 BETWEEN ? AND ?                )
\&      AND ( start2 BETWEEN lower(x) AND upper(y)  )
\&      AND ( start3 BETWEEN lower(x) AND upper(?)  )
\&    )";
\&    @bind = (1, 2, 1, 2, \*(Aqstuff\*(Aq);
.Ve
.PP
These are the two builtin \*(L"special operators\*(R"; but the
list can be expanded: see section \*(L"\s-1SPECIAL OPERATORS\*(R"\s0 below.
.SS "Unary operators: bool"
.IX Subsection "Unary operators: bool"
If you wish to test against boolean columns or functions within your
database you can use the \f(CW\*(C`\-bool\*(C'\fR and \f(CW\*(C`\-not_bool\*(C'\fR operators. For
example to test the column \f(CW\*(C`is_user\*(C'\fR being true and the column
\&\f(CW\*(C`is_enabled\*(C'\fR being false you would use:\-
.PP
.Vb 4
\&    my %where  = (
\&        \-bool       => \*(Aqis_user\*(Aq,
\&        \-not_bool   => \*(Aqis_enabled\*(Aq,
\&    );
.Ve
.PP
Would give you:
.PP
.Vb 1
\&    WHERE is_user AND NOT is_enabled
.Ve
.PP
If a more complex combination is required, testing more conditions,
then you should use the and/or operators:\-
.PP
.Vb 7
\&    my %where  = (
\&        \-and           => [
\&            \-bool      => \*(Aqone\*(Aq,
\&            \-not_bool  => { two=> { \-rlike => \*(Aqbar\*(Aq } },
\&            \-not_bool  => { three => [ { \*(Aq=\*(Aq, 2 }, { \*(Aq>\*(Aq, 5 } ] },
\&        ],
\&    );
.Ve
.PP
Would give you:
.PP
.Vb 6
\&    WHERE
\&      one
\&        AND
\&      (NOT two RLIKE ?)
\&        AND
\&      (NOT ( three = ? OR three > ? ))
.Ve
.SS "Nested conditions, \-and/\-or prefixes"
.IX Subsection "Nested conditions, -and/-or prefixes"
So far, we've seen how multiple conditions are joined with a top-level
\&\f(CW\*(C`AND\*(C'\fR.  We can change this by putting the different conditions we want in
hashes and then putting those hashes in an array. For example:
.PP
.Vb 10
\&    my @where = (
\&        {
\&            user   => \*(Aqnwiger\*(Aq,
\&            status => { \-like => [\*(Aqpending%\*(Aq, \*(Aqdispatched\*(Aq] },
\&        },
\&        {
\&            user   => \*(Aqrobot\*(Aq,
\&            status => \*(Aqunassigned\*(Aq,
\&        }
\&    );
.Ve
.PP
This data structure would create the following:
.PP
.Vb 3
\&    $stmt = "WHERE ( user = ? AND ( status LIKE ? OR status LIKE ? ) )
\&                OR ( user = ? AND status = ? ) )";
\&    @bind = (\*(Aqnwiger\*(Aq, \*(Aqpending\*(Aq, \*(Aqdispatched\*(Aq, \*(Aqrobot\*(Aq, \*(Aqunassigned\*(Aq);
.Ve
.PP
Clauses in hashrefs or arrayrefs can be prefixed with an \f(CW\*(C`\-and\*(C'\fR or \f(CW\*(C`\-or\*(C'\fR
to change the logic inside:
.PP
.Vb 9
\&    my @where = (
\&         \-and => [
\&            user => \*(Aqnwiger\*(Aq,
\&            [
\&                \-and => [ workhrs => {\*(Aq>\*(Aq, 20}, geo => \*(AqASIA\*(Aq ],
\&                \-or => { workhrs => {\*(Aq<\*(Aq, 50}, geo => \*(AqEURO\*(Aq },
\&            ],
\&        ],
\&    );
.Ve
.PP
That would yield:
.PP
.Vb 4
\&    $stmt = "WHERE ( user = ?
\&               AND ( ( workhrs > ? AND geo = ? )
\&                  OR ( workhrs < ? OR geo = ? ) ) )";
\&    @bind = (\*(Aqnwiger\*(Aq, \*(Aq20\*(Aq, \*(AqASIA\*(Aq, \*(Aq50\*(Aq, \*(AqEURO\*(Aq);
.Ve
.PP
\fIAlgebraic inconsistency, for historical reasons\fR
.IX Subsection "Algebraic inconsistency, for historical reasons"
.PP
\&\f(CW\*(C`Important note\*(C'\fR: when connecting several conditions, the \f(CW\*(C`\-and\-\*(C'\fR|\f(CW\*(C`\-or\*(C'\fR
operator goes \f(CW\*(C`outside\*(C'\fR of the nested structure; whereas when connecting
several constraints on one column, the \f(CW\*(C`\-and\*(C'\fR operator goes
\&\f(CW\*(C`inside\*(C'\fR the arrayref. Here is an example combining both features:
.PP
.Vb 5
\&   my @where = (
\&     \-and => [a => 1, b => 2],
\&     \-or  => [c => 3, d => 4],
\&      e   => [\-and => {\-like => \*(Aqfoo%\*(Aq}, {\-like => \*(Aq%bar\*(Aq} ]
\&   )
.Ve
.PP
yielding
.PP
.Vb 3
\&  WHERE ( (    ( a = ? AND b = ? )
\&            OR ( c = ? OR d = ? )
\&            OR ( e LIKE ? AND e LIKE ? ) ) )
.Ve
.PP
This difference in syntax is unfortunate but must be preserved for
historical reasons. So be careful: the two examples below would
seem algebraically equivalent, but they are not
.PP
.Vb 5
\&  { col => [ \-and =>
\&    { \-like => \*(Aqfoo%\*(Aq },
\&    { \-like => \*(Aq%bar\*(Aq },
\&  ] }
\&  # yields: WHERE ( ( col LIKE ? AND col LIKE ? ) )
\&
\&  [ \-and =>
\&    { col => { \-like => \*(Aqfoo%\*(Aq } },
\&    { col => { \-like => \*(Aq%bar\*(Aq } },
\&  ]
\&  # yields: WHERE ( ( col LIKE ? OR col LIKE ? ) )
.Ve
.SS "Literal \s-1SQL\s0 and value type operators"
.IX Subsection "Literal SQL and value type operators"
The basic premise of SQL::Abstract is that in \s-1WHERE\s0 specifications the \*(L"left
side\*(R" is a column name and the \*(L"right side\*(R" is a value (normally rendered as
a placeholder). This holds true for both hashrefs and arrayref pairs as you
see in the \*(L"\s-1WHERE CLAUSES\*(R"\s0 examples above. Sometimes it is necessary to
alter this behavior. There are several ways of doing so.
.PP
\fI\-ident\fR
.IX Subsection "-ident"
.PP
This is a virtual operator that signals the string to its right side is an
identifier (a column name) and not a value. For example to compare two
columns you would write:
.PP
.Vb 4
\&    my %where = (
\&        priority => { \*(Aq<\*(Aq, 2 },
\&        requestor => { \-ident => \*(Aqsubmitter\*(Aq },
\&    );
.Ve
.PP
which creates:
.PP
.Vb 2
\&    $stmt = "WHERE priority < ? AND requestor = submitter";
\&    @bind = (\*(Aq2\*(Aq);
.Ve
.PP
If you are maintaining legacy code you may see a different construct as
described in \*(L"Deprecated usage of Literal \s-1SQL\*(R"\s0, please use \f(CW\*(C`\-ident\*(C'\fR in new
code.
.PP
\fI\-value\fR
.IX Subsection "-value"
.PP
This is a virtual operator that signals that the construct to its right side
is a value to be passed to \s-1DBI.\s0 This is for example necessary when you want
to write a where clause against an array (for \s-1RDBMS\s0 that support such
datatypes). For example:
.PP
.Vb 3
\&    my %where = (
\&        array => { \-value => [1, 2, 3] }
\&    );
.Ve
.PP
will result in:
.PP
.Vb 2
\&    $stmt = \*(AqWHERE array = ?\*(Aq;
\&    @bind = ([1, 2, 3]);
.Ve
.PP
Note that if you were to simply say:
.PP
.Vb 3
\&    my %where = (
\&        array => [1, 2, 3]
\&    );
.Ve
.PP
the result would probably not be what you wanted:
.PP
.Vb 2
\&    $stmt = \*(AqWHERE array = ? OR array = ? OR array = ?\*(Aq;
\&    @bind = (1, 2, 3);
.Ve
.PP
\fILiteral \s-1SQL\s0\fR
.IX Subsection "Literal SQL"
.PP
Finally, sometimes only literal \s-1SQL\s0 will do. To include a random snippet
of \s-1SQL\s0 verbatim, you specify it as a scalar reference. Consider this only
as a last resort. Usually there is a better way. For example:
.PP
.Vb 4
\&    my %where = (
\&        priority => { \*(Aq<\*(Aq, 2 },
\&        requestor => { \-in => \e\*(Aq(SELECT name FROM hitmen)\*(Aq },
\&    );
.Ve
.PP
Would create:
.PP
.Vb 2
\&    $stmt = "WHERE priority < ? AND requestor IN (SELECT name FROM hitmen)"
\&    @bind = (2);
.Ve
.PP
Note that in this example, you only get one bind parameter back, since
the verbatim \s-1SQL\s0 is passed as part of the statement.
.PP
\s-1CAVEAT\s0
.IX Subsection "CAVEAT"
.PP
.Vb 4
\&  Never use untrusted input as a literal SQL argument \- this is a massive
\&  security risk (there is no way to check literal snippets for SQL
\&  injections and other nastyness). If you need to deal with untrusted input
\&  use literal SQL with placeholders as described next.
.Ve
.PP
\fILiteral \s-1SQL\s0 with placeholders and bind values (subqueries)\fR
.IX Subsection "Literal SQL with placeholders and bind values (subqueries)"
.PP
If the literal \s-1SQL\s0 to be inserted has placeholders and bind values,
use a reference to an arrayref (yes this is a double reference \*(--
not so common, but perfectly legal Perl). For example, to find a date
in Postgres you can use something like this:
.PP
.Vb 3
\&    my %where = (
\&       date_column => \e[ "= date \*(Aq2008\-09\-30\*(Aq \- ?::integer", 10 ]
\&    )
.Ve
.PP
This would create:
.PP
.Vb 2
\&    $stmt = "WHERE ( date_column = date \*(Aq2008\-09\-30\*(Aq \- ?::integer )"
\&    @bind = (\*(Aq10\*(Aq);
.Ve
.PP
Note that you must pass the bind values in the same format as they are returned
by where. This means that if you set \*(L"bindtype\*(R"
to \f(CW\*(C`columns\*(C'\fR, you must provide the bind values in the
\&\f(CW\*(C`[ column_meta => value ]\*(C'\fR format, where \f(CW\*(C`column_meta\*(C'\fR is an opaque
scalar value; most commonly the column name, but you can use any scalar value
(including references and blessed references), SQL::Abstract will simply
pass it through intact. So if \f(CW\*(C`bindtype\*(C'\fR is set to \f(CW\*(C`columns\*(C'\fR the above
example will look like:
.PP
.Vb 3
\&    my %where = (
\&       date_column => \e[ "= date \*(Aq2008\-09\-30\*(Aq \- ?::integer", [ {} => 10 ] ]
\&    )
.Ve
.PP
Literal \s-1SQL\s0 is especially useful for nesting parenthesized clauses in the
main \s-1SQL\s0 query. Here is a first example:
.PP
.Vb 6
\&  my ($sub_stmt, @sub_bind) = ("SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?",
\&                               100, "foo%");
\&  my %where = (
\&    foo => 1234,
\&    bar => \e["IN ($sub_stmt)" => @sub_bind],
\&  );
.Ve
.PP
This yields:
.PP
.Vb 3
\&  $stmt = "WHERE (foo = ? AND bar IN (SELECT c1 FROM t1
\&                                             WHERE c2 < ? AND c3 LIKE ?))";
\&  @bind = (1234, 100, "foo%");
.Ve
.PP
Other subquery operators, like for example \f(CW"> ALL"\fR or \f(CW"NOT IN"\fR,
are expressed in the same way. Of course the \f(CW$sub_stmt\fR and
its associated bind values can be generated through a former call
to \f(CW\*(C`select()\*(C'\fR :
.PP
.Vb 7
\&  my ($sub_stmt, @sub_bind)
\&     = $sql\->select("t1", "c1", {c2 => {"<" => 100},
\&                                 c3 => {\-like => "foo%"}});
\&  my %where = (
\&    foo => 1234,
\&    bar => \e["> ALL ($sub_stmt)" => @sub_bind],
\&  );
.Ve
.PP
In the examples above, the subquery was used as an operator on a column;
but the same principle also applies for a clause within the main \f(CW%where\fR
hash, like an \s-1EXISTS\s0 subquery:
.PP
.Vb 6
\&  my ($sub_stmt, @sub_bind)
\&     = $sql\->select("t1", "*", {c1 => 1, c2 => \e"> t0.c0"});
\&  my %where = ( \-and => [
\&    foo   => 1234,
\&    \e["EXISTS ($sub_stmt)" => @sub_bind],
\&  ]);
.Ve
.PP
which yields
.PP
.Vb 3
\&  $stmt = "WHERE (foo = ? AND EXISTS (SELECT * FROM t1
\&                                        WHERE c1 = ? AND c2 > t0.c0))";
\&  @bind = (1234, 1);
.Ve
.PP
Observe that the condition on \f(CW\*(C`c2\*(C'\fR in the subquery refers to
column \f(CW\*(C`t0.c0\*(C'\fR of the main query: this is \fInot\fR a bind
value, so we have to express it through a scalar ref.
Writing \f(CW\*(C`c2 => {">" => "t0.c0"}\*(C'\fR would have generated
\&\f(CW\*(C`c2 > ?\*(C'\fR with bind value \f(CW"t0.c0"\fR ... not exactly
what we wanted here.
.PP
Finally, here is an example where a subquery is used
for expressing unary negation:
.PP
.Vb 7
\&  my ($sub_stmt, @sub_bind)
\&     = $sql\->where({age => [{"<" => 10}, {">" => 20}]});
\&  $sub_stmt =~ s/^ where //i; # don\*(Aqt want "WHERE" in the subclause
\&  my %where = (
\&        lname  => {like => \*(Aq%son%\*(Aq},
\&        \e["NOT ($sub_stmt)" => @sub_bind],
\&    );
.Ve
.PP
This yields
.PP
.Vb 2
\&  $stmt = "lname LIKE ? AND NOT ( age < ? OR age > ? )"
\&  @bind = (\*(Aq%son%\*(Aq, 10, 20)
.Ve
.PP
\fIDeprecated usage of Literal \s-1SQL\s0\fR
.IX Subsection "Deprecated usage of Literal SQL"
.PP
Below are some examples of archaic use of literal \s-1SQL.\s0 It is shown only as
reference for those who deal with legacy code. Each example has a much
better, cleaner and safer alternative that users should opt for in new code.
.IP "\(bu" 4

.Sp
.Vb 1
\&    my %where = ( requestor => \e\*(AqIS NOT NULL\*(Aq )
\&
\&    $stmt = "WHERE requestor IS NOT NULL"
.Ve
.Sp
This used to be the way of generating \s-1NULL\s0 comparisons, before the handling
of \f(CW\*(C`undef\*(C'\fR got formalized. For new code please use the superior syntax as
described in \*(L"Tests for \s-1NULL\s0 values\*(R".
.IP "\(bu" 4

.Sp
.Vb 1
\&    my %where = ( requestor => \e\*(Aq= submitter\*(Aq )
\&
\&    $stmt = "WHERE requestor = submitter"
.Ve
.Sp
This used to be the only way to compare columns. Use the superior \*(L"\-ident\*(R"
method for all new code. For example an identifier declared in such a way
will be properly quoted if \*(L"quote_char\*(R" is properly set, while the legacy
form will remain as supplied.
.IP "\(bu" 4

.Sp
.Vb 1
\&    my %where = ( is_ready  => \e"", completed => { \*(Aq>\*(Aq, \*(Aq2012\-12\-21\*(Aq } )
\&
\&    $stmt = "WHERE completed > ? AND is_ready"
\&    @bind = (\*(Aq2012\-12\-21\*(Aq)
.Ve
.Sp
Using an empty string literal used to be the only way to express a boolean.
For all new code please use the much more readable
\&\-bool operator.
.SS "Conclusion"
.IX Subsection "Conclusion"
These pages could go on for a while, since the nesting of the data
structures this module can handle are pretty much unlimited (the
module implements the \f(CW\*(C`WHERE\*(C'\fR expansion as a recursive function
internally). Your best bet is to \*(L"play around\*(R" with the module a
little to see how the data structures behave, and choose the best
format for your data based on that.
.PP
And of course, all the values above will probably be replaced with
variables gotten from forms or the command line. After all, if you
knew everything ahead of time, you wouldn't have to worry about
dynamically-generating \s-1SQL\s0 and could just hardwire it into your
script.
.SH "ORDER BY CLAUSES"
.IX Header "ORDER BY CLAUSES"
Some functions take an order by clause. This can either be a scalar (just a
column name), a hashref of \f(CW\*(C`{ \-desc => \*(Aqcol\*(Aq }\*(C'\fR or \f(CW\*(C`{ \-asc => \*(Aqcol\*(Aq }\*(C'\fR, a scalarref, an arrayref-ref, or an arrayref of any of the previous
forms. Examples:
.PP
.Vb 10
\&               Given              |         Will Generate
\&    \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-
\&                                  |
\&    \*(AqcolA\*(Aq                        | ORDER BY colA
\&                                  |
\&    [qw/colA colB/]               | ORDER BY colA, colB
\&                                  |
\&    {\-asc  => \*(AqcolA\*(Aq}             | ORDER BY colA ASC
\&                                  |
\&    {\-desc => \*(AqcolB\*(Aq}             | ORDER BY colB DESC
\&                                  |
\&    [\*(AqcolA\*(Aq, {\-asc => \*(AqcolB\*(Aq}]    | ORDER BY colA, colB ASC
\&                                  |
\&    { \-asc => [qw/colA colB/] }   | ORDER BY colA ASC, colB ASC
\&                                  |
\&    \e\*(AqcolA DESC\*(Aq                  | ORDER BY colA DESC
\&                                  |
\&    \e[ \*(AqFUNC(colA, ?)\*(Aq, $x ]      | ORDER BY FUNC(colA, ?)
\&                                  |   /* ...with $x bound to ? */
\&                                  |
\&    [                             | ORDER BY
\&      { \-asc => \*(AqcolA\*(Aq },         |     colA ASC,
\&      { \-desc => [qw/colB/] },    |     colB DESC,
\&      { \-asc => [qw/colC colD/] },|     colC ASC, colD ASC,
\&      \e\*(AqcolE DESC\*(Aq,               |     colE DESC,
\&      \e[ \*(AqFUNC(colF, ?)\*(Aq, $x ],   |     FUNC(colF, ?)
\&    ]                             |   /* ...with $x bound to ? */
\&    ===============================================================
.Ve
.SH "OLD EXTENSION SYSTEM"
.IX Header "OLD EXTENSION SYSTEM"
.SS "\s-1SPECIAL OPERATORS\s0"
.IX Subsection "SPECIAL OPERATORS"
.Vb 10
\&  my $sqlmaker = SQL::Abstract\->new(special_ops => [
\&     {
\&      regex => qr/.../,
\&      handler => sub {
\&        my ($self, $field, $op, $arg) = @_;
\&        ...
\&      },
\&     },
\&     {
\&      regex => qr/.../,
\&      handler => \*(Aqmethod_name\*(Aq,
\&     },
\&   ]);
.Ve
.PP
A \*(L"special operator\*(R" is a \s-1SQL\s0 syntactic clause that can be
applied to a field, instead of a usual binary operator.
For example:
.PP
.Vb 3
\&   WHERE field IN (?, ?, ?)
\&   WHERE field BETWEEN ? AND ?
\&   WHERE MATCH(field) AGAINST (?, ?)
.Ve
.PP
Special operators \s-1IN\s0 and \s-1BETWEEN\s0 are fairly standard and therefore
are builtin within \f(CW\*(C`SQL::Abstract\*(C'\fR (as the overridable methods
\&\f(CW\*(C`_where_field_IN\*(C'\fR and \f(CW\*(C`_where_field_BETWEEN\*(C'\fR). For other operators,
like the \s-1MATCH .. AGAINST\s0 example above which is specific to MySQL,
you can write your own operator handlers \- supply a \f(CW\*(C`special_ops\*(C'\fR
argument to the \f(CW\*(C`new\*(C'\fR method. That argument takes an arrayref of
operator definitions; each operator definition is a hashref with two
entries:
.IP "regex" 4
.IX Item "regex"
the regular expression to match the operator
.IP "handler" 4
.IX Item "handler"
Either a coderef or a plain scalar method name. In both cases
the expected return is \f(CW\*(C`($sql, @bind)\*(C'\fR.
.Sp
When supplied with a method name, it is simply called on the
SQL::Abstract object as:
.Sp
.Vb 1
\& $self\->$method_name($field, $op, $arg)
\&
\& Where:
\&
\&  $field is the LHS of the operator
\&  $op is the part that matched the handler regex
\&  $arg is the RHS
.Ve
.Sp
When supplied with a coderef, it is called as:
.Sp
.Vb 1
\& $coderef\->($self, $field, $op, $arg)
.Ve
.PP
For example, here is an implementation
of the \s-1MATCH .. AGAINST\s0 syntax for MySQL
.PP
.Vb 1
\&  my $sqlmaker = SQL::Abstract\->new(special_ops => [
\&
\&    # special op for MySql MATCH (field) AGAINST(word1, word2, ...)
\&    {regex => qr/^match$/i,
\&     handler => sub {
\&       my ($self, $field, $op, $arg) = @_;
\&       $arg = [$arg] if not ref $arg;
\&       my $label         = $self\->_quote($field);
\&       my ($placeholder) = $self\->_convert(\*(Aq?\*(Aq);
\&       my $placeholders  = join ", ", (($placeholder) x @$arg);
\&       my $sql           = $self\->_sqlcase(\*(Aqmatch\*(Aq) . " ($label) "
\&                         . $self\->_sqlcase(\*(Aqagainst\*(Aq) . " ($placeholders) ";
\&       my @bind = $self\->_bindtype($field, @$arg);
\&       return ($sql, @bind);
\&       }
\&     },
\&
\&  ]);
.Ve
.SS "\s-1UNARY OPERATORS\s0"
.IX Subsection "UNARY OPERATORS"
.Vb 10
\&  my $sqlmaker = SQL::Abstract\->new(unary_ops => [
\&     {
\&      regex => qr/.../,
\&      handler => sub {
\&        my ($self, $op, $arg) = @_;
\&        ...
\&      },
\&     },
\&     {
\&      regex => qr/.../,
\&      handler => \*(Aqmethod_name\*(Aq,
\&     },
\&   ]);
.Ve
.PP
A \*(L"unary operator\*(R" is a \s-1SQL\s0 syntactic clause that can be
applied to a field \- the operator goes before the field
.PP
You can write your own operator handlers \- supply a \f(CW\*(C`unary_ops\*(C'\fR
argument to the \f(CW\*(C`new\*(C'\fR method. That argument takes an arrayref of
operator definitions; each operator definition is a hashref with two
entries:
.IP "regex" 4
.IX Item "regex"
the regular expression to match the operator
.IP "handler" 4
.IX Item "handler"
Either a coderef or a plain scalar method name. In both cases
the expected return is \f(CW$sql\fR.
.Sp
When supplied with a method name, it is simply called on the
SQL::Abstract object as:
.Sp
.Vb 1
\& $self\->$method_name($op, $arg)
\&
\& Where:
\&
\&  $op is the part that matched the handler regex
\&  $arg is the RHS or argument of the operator
.Ve
.Sp
When supplied with a coderef, it is called as:
.Sp
.Vb 1
\& $coderef\->($self, $op, $arg)
.Ve
.SH "NEW METHODS (EXPERIMENTAL)"
.IX Header "NEW METHODS (EXPERIMENTAL)"
See SQL::Abstract::Reference for the \f(CW\*(C`expr\*(C'\fR versus \f(CW\*(C`aqt\*(C'\fR concept and
an explanation of what the below extensions are extending.
.SS "plugin"
.IX Subsection "plugin"
.Vb 1
\&  $sqla\->plugin(\*(Aq+Foo\*(Aq);
.Ve
.PP
Enables plugin SQL::Abstract::Plugin::Foo.
.SS "render_expr"
.IX Subsection "render_expr"
.Vb 1
\&  my ($sql, @bind) = $sqla\->render_expr($expr);
.Ve
.SS "render_statement"
.IX Subsection "render_statement"
Use this if you may be rendering a top level statement so e.g. a \s-1SELECT\s0
query doesn't get wrapped in parens
.PP
.Vb 1
\&  my ($sql, @bind) = $sqla\->render_statement($expr);
.Ve
.SS "expand_expr"
.IX Subsection "expand_expr"
Expression expansion with optional default for scalars.
.PP
.Vb 2
\&  my $aqt = $self\->expand_expr($expr);
\&  my $aqt = $self\->expand_expr($expr, \-ident);
.Ve
.SS "render_aqt"
.IX Subsection "render_aqt"
Top level means avoid parens on statement \s-1AQT.\s0
.PP
.Vb 2
\&  my $res = $self\->render_aqt($aqt, $top_level);
\&  my ($sql, @bind) = @$res;
.Ve
.SS "join_query_parts"
.IX Subsection "join_query_parts"
Similar to \fBjoin()\fR but will render hashrefs as nodes for both join and parts,
and treats arrayref as a nested \f(CW\*(C`[ $join, @parts ]\*(C'\fR structure.
.PP
.Vb 1
\&  my $part = $self\->join_query_parts($join, @parts);
.Ve
.SH "NEW EXTENSION SYSTEM"
.IX Header "NEW EXTENSION SYSTEM"
.SS "clone"
.IX Subsection "clone"
.Vb 1
\&  my $sqla2 = $sqla\->clone;
.Ve
.PP
Performs a semi-shallow copy such that extension methods won't leak state
but excessive depth is avoided.
.SS "expander"
.IX Subsection "expander"
.SS "expanders"
.IX Subsection "expanders"
.SS "op_expander"
.IX Subsection "op_expander"
.SS "op_expanders"
.IX Subsection "op_expanders"
.SS "clause_expander"
.IX Subsection "clause_expander"
.SS "clause_expanders"
.IX Subsection "clause_expanders"
.Vb 2
\&  $sqla\->expander(\*(Aqname\*(Aq => sub { ... });
\&  $sqla\->expanders(\*(Aqname1\*(Aq => sub { ... }, \*(Aqname2\*(Aq => sub { ... });
.Ve
.SS "expander_list"
.IX Subsection "expander_list"
.SS "op_expander_list"
.IX Subsection "op_expander_list"
.SS "clause_expander_list"
.IX Subsection "clause_expander_list"
.Vb 1
\&  my @names = $sqla\->expander_list;
.Ve
.SS "wrap_expander"
.IX Subsection "wrap_expander"
.SS "wrap_expanders"
.IX Subsection "wrap_expanders"
.SS "wrap_op_expander"
.IX Subsection "wrap_op_expander"
.SS "wrap_op_expanders"
.IX Subsection "wrap_op_expanders"
.SS "wrap_clause_expander"
.IX Subsection "wrap_clause_expander"
.SS "wrap_clause_expanders"
.IX Subsection "wrap_clause_expanders"
.Vb 5
\&  $sqla\->wrap_expander(\*(Aqname\*(Aq => sub { my ($orig) = @_; sub { ... } });
\&  $sqla\->wrap_expanders(
\&    \*(Aqname1\*(Aq => sub { my ($orig1) = @_; sub { ... } },
\&    \*(Aqname2\*(Aq => sub { my ($orig2) = @_; sub { ... } },
\&  );
.Ve
.SS "renderer"
.IX Subsection "renderer"
.SS "renderers"
.IX Subsection "renderers"
.SS "op_renderer"
.IX Subsection "op_renderer"
.SS "op_renderers"
.IX Subsection "op_renderers"
.SS "clause_renderer"
.IX Subsection "clause_renderer"
.SS "clause_renderers"
.IX Subsection "clause_renderers"
.Vb 2
\&  $sqla\->renderer(\*(Aqname\*(Aq => sub { ... });
\&  $sqla\->renderers(\*(Aqname1\*(Aq => sub { ... }, \*(Aqname2\*(Aq => sub { ... });
.Ve
.SS "renderer_list"
.IX Subsection "renderer_list"
.SS "op_renderer_list"
.IX Subsection "op_renderer_list"
.SS "clause_renderer_list"
.IX Subsection "clause_renderer_list"
.Vb 1
\&  my @names = $sqla\->renderer_list;
.Ve
.SS "wrap_renderer"
.IX Subsection "wrap_renderer"
.SS "wrap_renderers"
.IX Subsection "wrap_renderers"
.SS "wrap_op_renderer"
.IX Subsection "wrap_op_renderer"
.SS "wrap_op_renderers"
.IX Subsection "wrap_op_renderers"
.SS "wrap_clause_renderer"
.IX Subsection "wrap_clause_renderer"
.SS "wrap_clause_renderers"
.IX Subsection "wrap_clause_renderers"
.Vb 5
\&  $sqla\->wrap_renderer(\*(Aqname\*(Aq => sub { my ($orig) = @_; sub { ... } });
\&  $sqla\->wrap_renderers(
\&    \*(Aqname1\*(Aq => sub { my ($orig1) = @_; sub { ... } },
\&    \*(Aqname2\*(Aq => sub { my ($orig2) = @_; sub { ... } },
\&  );
.Ve
.SS "clauses_of"
.IX Subsection "clauses_of"
.Vb 7
\&  my @clauses = $sqla\->clauses_of(\*(Aqselect\*(Aq);
\&  $sqla\->clauses_of(select => \e@new_clauses);
\&  $sqla\->clauses_of(select => sub {
\&    my (undef, @old_clauses) = @_;
\&    ...
\&    return @new_clauses;
\&  });
.Ve
.SS "statement_list"
.IX Subsection "statement_list"
.Vb 1
\&  my @list = $sqla\->statement_list;
.Ve
.SS "make_unop_expander"
.IX Subsection "make_unop_expander"
.Vb 1
\&  my $exp = $sqla\->make_unop_expander(sub { ... });
.Ve
.PP
If the op is found as a binop, assumes it wants a default comparison, so
the inner expander sub can reliably operate as
.PP
.Vb 1
\&  sub { my ($self, $name, $body) = @_; ... }
.Ve
.SS "make_binop_expander"
.IX Subsection "make_binop_expander"
.Vb 1
\&  my $exp = $sqla\->make_binop_expander(sub { ... });
.Ve
.PP
If the op is found as a unop, assumes the value will be an arrayref with the
\&\s-1LHS\s0 as the first entry, and converts that to an ident node if it's a simple
scalar. So the inner expander sub looks like
.PP
.Vb 4
\&  sub {
\&    my ($self, $name, $body, $k) = @_;
\&    { \-blah => [ map $self\->expand_expr($_), $k, $body ] }
\&  }
.Ve
.SS "unop_expander"
.IX Subsection "unop_expander"
.SS "unop_expanders"
.IX Subsection "unop_expanders"
.SS "binop_expander"
.IX Subsection "binop_expander"
.SS "binop_expanders"
.IX Subsection "binop_expanders"
The above methods operate exactly like the op_ versions but wrap the coderef
using the appropriate make_ method first.
.SH "PERFORMANCE"
.IX Header "PERFORMANCE"
Thanks to some benchmarking by Mark Stosberg, it turns out that
this module is many orders of magnitude faster than using \f(CW\*(C`DBIx::Abstract\*(C'\fR.
I must admit this wasn't an intentional design issue, but it's a
byproduct of the fact that you get to control your \f(CW\*(C`DBI\*(C'\fR handles
yourself.
.PP
To maximize performance, use a code snippet like the following:
.PP
.Vb 8
\&    # prepare a statement handle using the first row
\&    # and then reuse it for the rest of the rows
\&    my($sth, $stmt);
\&    for my $href (@array_of_hashrefs) {
\&        $stmt ||= $sql\->insert(\*(Aqtable\*(Aq, $href);
\&        $sth  ||= $dbh\->prepare($stmt);
\&        $sth\->execute($sql\->values($href));
\&    }
.Ve
.PP
The reason this works is because the keys in your \f(CW$href\fR are sorted
internally by \fBSQL::Abstract\fR. Thus, as long as your data retains
the same structure, you only have to generate the \s-1SQL\s0 the first time
around. On subsequent queries, simply use the \f(CW\*(C`values\*(C'\fR function provided
by this module to return your values in the correct order.
.PP
However this depends on the values having the same type \- if, for
example, the values of a where clause may either have values
(resulting in sql of the form \f(CW\*(C`column = ?\*(C'\fR with a single bind
value), or alternatively the values might be \f(CW\*(C`undef\*(C'\fR (resulting in
sql of the form \f(CW\*(C`column IS NULL\*(C'\fR with no bind value) then the
caching technique suggested will not work.
.SH "FORMBUILDER"
.IX Header "FORMBUILDER"
If you use my \f(CW\*(C`CGI::FormBuilder\*(C'\fR module at all, you'll hopefully
really like this part (I do, at least). Building up a complex query
can be as simple as the following:
.PP
.Vb 1
\&    #!/usr/bin/perl
\&
\&    use warnings;
\&    use strict;
\&
\&    use CGI::FormBuilder;
\&    use SQL::Abstract;
\&
\&    my $form = CGI::FormBuilder\->new(...);
\&    my $sql  = SQL::Abstract\->new;
\&
\&    if ($form\->submitted) {
\&        my $field = $form\->field;
\&        my $id = delete $field\->{id};
\&        my($stmt, @bind) = $sql\->update(\*(Aqtable\*(Aq, $field, {id => $id});
\&    }
.Ve
.PP
Of course, you would still have to connect using \f(CW\*(C`DBI\*(C'\fR to run the
query, but the point is that if you make your form look like your
table, the actual query script can be extremely simplistic.
.PP
If you're \fB\s-1REALLY\s0\fR lazy (I am), check out \f(CW\*(C`HTML::QuickTable\*(C'\fR for
a fast interface to returning and formatting data. I frequently
use these three modules together to write complex database query
apps in under 50 lines.
.SH "HOW TO CONTRIBUTE"
.IX Header "HOW TO CONTRIBUTE"
Contributions are always welcome, in all usable forms (we especially
welcome documentation improvements). The delivery methods include git\-
or unified-diff formatted patches, GitHub pull requests, or plain bug
reports either via \s-1RT\s0 or the Mailing list. Contributors are generally
granted full access to the official repository after their first several
patches pass successful review.
.PP
This project is maintained in a git repository. The code and related tools are
accessible at the following locations:
.IP "\(bu" 4
Official repo: <git://git.shadowcat.co.uk/dbsrgits/SQL\-Abstract.git>
.IP "\(bu" 4
Official gitweb: <http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/SQL\-Abstract.git>
.IP "\(bu" 4
GitHub mirror: <https://github.com/dbsrgits/sql\-abstract>
.IP "\(bu" 4
Authorized committers: <ssh://dbsrgits@git.shadowcat.co.uk/SQL\-Abstract.git>
.SH "CHANGES"
.IX Header "CHANGES"
Version 1.50 was a major internal refactoring of \f(CW\*(C`SQL::Abstract\*(C'\fR.
Great care has been taken to preserve the \fIpublished\fR behavior
documented in previous versions in the 1.* family; however,
some features that were previously undocumented, or behaved
differently from the documentation, had to be changed in order
to clarify the semantics. Hence, client code that was relying
on some dark areas of \f(CW\*(C`SQL::Abstract\*(C'\fR v1.*
\&\fBmight behave differently\fR in v1.50.
.PP
The main changes are:
.IP "\(bu" 4
support for literal \s-1SQL\s0 through the \f(CW\*(C`\e [ $sql, @bind ]\*(C'\fR syntax.
.IP "\(bu" 4
support for the { operator => \e\*(L"...\*(R" } construct (to embed literal \s-1SQL\s0)
.IP "\(bu" 4
support for the { operator => \e[\*(L"...\*(R", \f(CW@bind\fR] } construct (to embed literal \s-1SQL\s0 with bind values)
.IP "\(bu" 4
optional support for array datatypes
.IP "\(bu" 4
defensive programming: check arguments
.IP "\(bu" 4
fixed bug with global logic, which was previously implemented
through global variables yielding side-effects. Prior versions would
interpret \f(CW\*(C`[ {cond1, cond2}, [cond3, cond4] ]\*(C'\fR
as \f(CW"(cond1 AND cond2) OR (cond3 AND cond4)"\fR.
Now this is interpreted
as \f(CW"(cond1 AND cond2) OR (cond3 OR cond4)"\fR.
.IP "\(bu" 4
fixed semantics of  _bindtype on array args
.IP "\(bu" 4
dropped the \f(CW\*(C`_anoncopy\*(C'\fR of the \f(CW%where\fR tree. No longer necessary,
we just avoid shifting arrays within that tree.
.IP "\(bu" 4
dropped the \f(CW\*(C`_modlogic\*(C'\fR function
.SH "ACKNOWLEDGEMENTS"
.IX Header "ACKNOWLEDGEMENTS"
There are a number of individuals that have really helped out with
this module. Unfortunately, most of them submitted bugs via \s-1CPAN\s0
so I have no idea who they are! But the people I do know are:
.PP
.Vb 10
\&    Ash Berlin (order_by hash term support)
\&    Matt Trout (DBIx::Class support)
\&    Mark Stosberg (benchmarking)
\&    Chas Owens (initial "IN" operator support)
\&    Philip Collins (per\-field SQL functions)
\&    Eric Kolve (hashref "AND" support)
\&    Mike Fragassi (enhancements to "BETWEEN" and "LIKE")
\&    Dan Kubb (support for "quote_char" and "name_sep")
\&    Guillermo Roditi (patch to cleanup "IN" and "BETWEEN", fix and tests for _order_by)
\&    Laurent Dami (internal refactoring, extensible list of special operators, literal SQL)
\&    Norbert Buchmuller (support for literal SQL in hashpair, misc. fixes & tests)
\&    Peter Rabbitson (rewrite of SQLA::Test, misc. fixes & tests)
\&    Oliver Charles (support for "RETURNING" after "INSERT")
.Ve
.PP
Thanks!
.SH "SEE ALSO"
.IX Header "SEE ALSO"
DBIx::Class, DBIx::Abstract, CGI::FormBuilder, HTML::QuickTable.
.SH "AUTHOR"
.IX Header "AUTHOR"
Copyright (c) 2001\-2007 Nathan Wiger <nwiger@cpan.org>. All Rights Reserved.
.PP
This module is actively maintained by Matt Trout <mst@shadowcatsystems.co.uk>
.PP
For support, your best bet is to try the \f(CW\*(C`DBIx::Class\*(C'\fR users mailing list.
While not an official support venue, \f(CW\*(C`DBIx::Class\*(C'\fR makes heavy use of
\&\f(CW\*(C`SQL::Abstract\*(C'\fR, and as such list members there are very familiar with
how to create queries.
.SH "LICENSE"
.IX Header "LICENSE"
This module is free software; you may copy this under the same
terms as perl itself (either the \s-1GNU\s0 General Public License or
the Artistic License)