.\" Automatically generated by Pod::Man 2.27 (Pod::Simple 3.28)
.\"
.\" 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 turned on, 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::Statement::Structure 3"
.TH SQL::Statement::Structure 3 "2014-12-02" "perl v5.16.3" "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::Statement::Structure \- parse and examine structure of SQL queries
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
.Vb 10
\&    use SQL::Statement;
\&    my $sql    = "SELECT a FROM b JOIN c WHERE c=? AND e=7 ORDER BY f DESC LIMIT 5,2";
\&    my $parser = SQL::Parser\->new();
\&    $parser\->{RaiseError}=1;
\&    $parser\->{PrintError}=0;
\&    $parser\->parse("LOAD \*(AqMyLib::MySyntax\*(Aq ");
\&    my $stmt = SQL::Statement\->new($sql,$parser);
\&    printf "Command             %s\en",$stmt\->command;
\&    printf "Num of Placeholders %s\en",scalar $stmt\->params;
\&    printf "Columns             %s\en",join( \*(Aq,\*(Aq, map {$_\->name} $stmt\->column_defs() );
\&    printf "Tables              %s\en",join( \*(Aq,\*(Aq, map {$_\->name} $stmt\->tables() );
\&    printf "Where operator      %s\en",join( \*(Aq,\*(Aq, $stmt\->where\->op() );
\&    printf "Limit               %s\en",$stmt\->limit();
\&    printf "Offset              %s\en",$stmt\->offset();
\&
\&    # these will work not before $stmt\->execute()
\&    printf "Order Columns       %s\en",join(\*(Aq,\*(Aq, map {$_\->column} $stmt\->order() );
.Ve
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
The SQL::Statement module can be used by itself, without \s-1DBI\s0 and without
a subclass to parse \s-1SQL\s0 statements and to allow you to examine the structure
of the statement (table names, column names, where clause predicates, etc.).
It will also execute statements using in-memory tables.  That means that
you can create and populate some tables, then query them and fetch the
results of the queries as well as examine the differences between statement
metadata during different phases of prepare, execute, fetch. See the
remainder of this document for a description of how to create and modify
a parser object and how to use it to parse and examine \s-1SQL\s0 statements.
See SQL::Statement for other uses of the module.
.SH "\fBCreating a parser object\fP"
.IX Header "Creating a parser object"
The parser object only needs to be created once per script. It can
then be reused to parse any number of \s-1SQL\s0 statements. The basic
creation of a parser is this:
.PP
.Vb 1
\&    my $parser = SQL::Parser\->new();
.Ve
.PP
You can set the error-reporting for the parser the same way you do in \s-1DBI:\s0
.PP
.Vb 2
\&    $parser\->{RaiseError}=1;   # turn on die\-on\-error behaviour
\&    $parser\->{PrinteError}=1;  # turn on warnings\-on\-error behaviour
.Ve
.PP
As with \s-1DBI,\s0 RaiseError defaults to 0 (off) and PrintError defaults to 1 (on).
.PP
For many purposes, the built-in \s-1SQL\s0 syntax should be sufficient. However, if
you need to, you can change the behaviour of the parser by extending the
supported \s-1SQL\s0 syntax either by loading a file containing definitions; or by
issuing \s-1SQL\s0 commands that modify the way the parser treats types, keywords,
functions, and operators.
.PP
.Vb 2
\&    $parser\->parse("LOAD MyLib::MySyntax");
\&    $parser\->parse("CREATE TYPE myDataType");
.Ve
.PP
See SQL::Statement::Syntax for details of the supported \s-1SQL\s0 syntax and
for methods of extending the syntax.
.SH "\fBParsing SQL statements\fP"
.IX Header "Parsing SQL statements"
While you only need to define a new SQL::Parser object once per script, you
need to define a new SQL::Statment object once for each statement you want
to parse.
.PP
.Vb 1
\&    my $stmt = SQL::Statement\->new($sql, $parser);
.Ve
.PP
The call to \fInew()\fR takes two arguments \- the \s-1SQL\s0 string you want to parse,
and the SQL::Parser object you previously created.  The call to new is the
equivalent of a \s-1DBI\s0 call to \fIprepare()\fR \- it parses the \s-1SQL\s0 into a structure
but does not attempt to execute the \s-1SQL\s0 unless you explicitly call \fIexecute()\fR.
.SH "Examining the structure of SQL statements"
.IX Header "Examining the structure of SQL statements"
The following methods can be used to obtain information about a query:
.SS "\fBcommand\fP"
.IX Subsection "command"
Returns the \s-1SQL\s0 command. See SQL::Statement::Syntax for supported
command. Example:
.PP
.Vb 1
\&    my $command = $stmt\->command();
.Ve
.SS "\fBcolumn definitions\fP"
.IX Subsection "column definitions"
.Vb 3
\&    my $numColumns = $stmt\->column_defs();  # Scalar context
\&    my @columnList = $stmt\->column_defs();  # Array context
\&    my($col1, $col2) = ($stmt\->column_defs(0), $stmt\->column_defs(1));
.Ve
.PP
This method is used to retrieve column lists. The meaning depends on
the query command:
.PP
.Vb 4
\&    SELECT $col1, $col2, ... $colN FROM $table WHERE ...
\&    UPDATE $table SET $col1 = $val1, $col2 = $val2, ...
\&        $colN = $valN WHERE ...
\&    INSERT INTO $table ($col1, $col2, ..., $colN) VALUES (...)
.Ve
.PP
When used without arguments, the method returns a list of the columns
\&\f(CW$col1\fR, \f(CW$col2\fR, ..., \f(CW$colN\fR, you may alternatively use a column number
as argument. Note that the column list may be empty as in
.PP
.Vb 1
\&    INSERT INTO $table VALUES (...)
.Ve
.PP
and in \fI\s-1CREATE\s0\fR or \fI\s-1DROP\s0\fR statements.
.PP
But what does \*(L"returning a column\*(R" mean? It is returning an
\&\f(CW\*(C`SQL::Statement::Util::Column\*(C'\fR instance, a class that implements the methods
\&\f(CW\*(C`table\*(C'\fR and \f(CW\*(C`name\*(C'\fR, both returning the respective scalar. For example,
consider the following statements:
.PP
.Vb 3
\&    INSERT INTO foo (bar) VALUES (1)
\&    SELECT bar FROM foo WHERE ...
\&    SELECT foo.bar FROM foo WHERE ...
.Ve
.PP
In all these cases exactly one column instance would be returned with
.PP
.Vb 2
\&    $col\->name() eq \*(Aqbar\*(Aq
\&    $col\->table() eq \*(Aqfoo\*(Aq
.Ve
.SS "\fBtables\fP"
.IX Subsection "tables"
.Vb 3
\&    my $tableNum = $stmt\->tables();  # Scalar context
\&    my @tables = $stmt\->tables();    # Array context
\&    my($table1, $table2) = ($stmt\->tables(0), $stmt\->tables(1));
.Ve
.PP
Similar to \f(CW\*(C`columns\*(C'\fR, this method returns instances of
\&\f(CW\*(C`SQL::Statement::Table\*(C'\fR. For \fI\s-1UPDATE\s0\fR, \fI\s-1DELETE\s0\fR, \fI\s-1INSERT\s0\fR,
\&\fI\s-1CREATE\s0\fR and \fI\s-1DROP\s0\fR, a single table will always be returned.
\&\fI\s-1SELECT\s0\fR statements can return more than one table, in case
of joins. Table objects offer a single method, \f(CW\*(C`name\*(C'\fR which
returns the table name.
.SS "\fBparams\fP"
.IX Subsection "params"
.Vb 3
\&    my $paramNum = $stmt\->params();  # Scalar context
\&    my @params = $stmt\->params();    # Array context
\&    my($p1, $p2) = ($stmt\->params(0), $stmt\->params(1));
.Ve
.PP
The \f(CW\*(C`params\*(C'\fR method returns information about the input parameters
used in a statement. For example, consider the following:
.PP
.Vb 1
\&    INSERT INTO foo VALUES (?, ?)
.Ve
.PP
This would return two instances of \f(CW\*(C`SQL::Statement::Param\*(C'\fR. Param objects
implement a single method, \f(CW\*(C`$param\-\*(C'\fR\fInum()\fR>, which retrieves the parameter
number. (0 and 1, in the above example). As of now, not very useful ... :\-)
.SS "\fBrow_values\fP"
.IX Subsection "row_values"
.Vb 4
\&    my $rowValueNum = $stmt\->row_values(); # Scalar context
\&    my @rowValues = $stmt\->row_values(0);  # Array context
\&    my($rval1, $rval2) = ($stmt\->row_values(0,0),
\&                          $stmt\->row_values(0,1));
.Ve
.PP
This method is used for statements like
.PP
.Vb 4
\&    UPDATE $table SET $col1 = $val1, $col2 = $val2, ...
\&        $colN = $valN WHERE ...
\&    INSERT INTO $table (...) VALUES ($val1, $val2, ..., $valN),
\&                                    ($val1, $val2, ..., $valN)
.Ve
.PP
to read the values \f(CW$val1\fR, \f(CW$val2\fR, ... \f(CW$valN\fR. It returns (lists of)
scalar values or \f(CW\*(C`SQL::Statement::Param\*(C'\fR instances.
.SS "\fBorder\fP"
.IX Subsection "order"
.Vb 3
\&    my $orderNum = $stmt\->order();   # Scalar context
\&    my @order = $stmt\->order();      # Array context
\&    my($o1, $o2) = ($stmt\->order(0), $stmt\->order(1));
.Ve
.PP
In \fI\s-1SELECT\s0\fR statements you can use this for looking at the \s-1ORDER\s0 clause.
Example:
.PP
.Vb 1
\&    SELECT * FROM FOO ORDER BY id DESC, name
.Ve
.PP
In this case, \f(CW\*(C`order\*(C'\fR could return 2 instances of \f(CW\*(C`SQL::Statement::Order\*(C'\fR.
You can use the methods \f(CW\*(C`$o\->table()\*(C'\fR, \f(CW\*(C`$o\->column()\*(C'\fR,
\&\f(CW\*(C`$o\->direction()\*(C'\fR and \f(CW\*(C`$o\->desc()\*(C'\fR to examine the order object.
.SS "\fBlimit\fP"
.IX Subsection "limit"
.Vb 1
\&    my $limit = $stmt\->limit();
.Ve
.PP
In a \s-1SELECT\s0 statement you can use a \f(CW\*(C`LIMIT\*(C'\fR clause to implement
cursoring:
.PP
.Vb 3
\&    SELECT * FROM FOO LIMIT 5
\&    SELECT * FROM FOO LIMIT 5, 5
\&    SELECT * FROM FOO LIMIT 10, 5
.Ve
.PP
These three statements would retrieve the rows \f(CW0..4\fR, \f(CW5..9\fR, \f(CW10..14\fR
of the table \s-1FOO,\s0 respectively. If no \f(CW\*(C`LIMIT\*(C'\fR clause is used, then the
method \f(CW\*(C`$stmt\->limit\*(C'\fR returns undef. Otherwise it returns the limit
number (the maximum number of rows) from the statement (\f(CW5\fR or \f(CW10\fR for
the statements above).
.SS "\fBoffset\fP"
.IX Subsection "offset"
.Vb 1
\&    my $offset = $stmt\->offset();
.Ve
.PP
If no \f(CW\*(C`LIMIT\*(C'\fR clause is used, then the method \f(CW\*(C`$stmt\->limit\*(C'\fR returns
\&\fIundef\fR. Otherwise it returns the offset number (the index of the first row
to be included in the limit clause).
.SS "\fBwhere_hash\fP"
.IX Subsection "where_hash"
.Vb 1
\&    my $where_hash = $stmt\->where_hash();
.Ve
.PP
To manually evaluate the \fI\s-1WHERE\s0\fR clause, fetch the topmost where clause node
with the \f(CW\*(C`where_hash\*(C'\fR method. Then evaluate the left-hand and right-hand side
of the operation, perhaps recursively. Once that is done, apply the operator
and finally negate the result, if required.
.PP
The where clause nodes have (up to) 4 attributes:
.IP "op" 12
.IX Item "op"
contains the operator, one of \f(CW\*(C`AND\*(C'\fR, \f(CW\*(C`OR\*(C'\fR, \f(CW\*(C`=\*(C'\fR, \f(CW\*(C`<>\*(C'\fR, \f(CW\*(C`>=\*(C'\fR,
\&\f(CW\*(C`>\*(C'\fR, \f(CW\*(C`<=\*(C'\fR, \f(CW\*(C`<\*(C'\fR, \f(CW\*(C`LIKE\*(C'\fR, \f(CW\*(C`CLIKE\*(C'\fR, \f(CW\*(C`IS\*(C'\fR, \f(CW\*(C`IN\*(C'\fR, \f(CW\*(C`BETWEEN\*(C'\fR or
a user defined operator, if any.
.IP "arg1" 12
.IX Item "arg1"
contains the left-hand side of the operator. This can be a scalar value, a
hash containing column or function definition, a parameter definition (hash has
attribute \f(CW\*(C`type\*(C'\fR defined) or another operation (hash has attribute \f(CW\*(C`op\*(C'\fR
defined).
.IP "arg2" 12
.IX Item "arg2"
contains the right-hand side of the operator. This can be a scalar value, a
hash containing column or function definition, a parameter definition (hash has
attribute \f(CW\*(C`type\*(C'\fR defined) or another operation (hash has attribute \f(CW\*(C`op\*(C'\fR
defined).
.IP "neg" 12
.IX Item "neg"
contains a \s-1TRUE\s0 value, if the operation result must be negated after evaluation.
.PP
To illustrate the above, consider the following \s-1WHERE\s0 clause:
.PP
.Vb 1
\&    WHERE NOT (id > 2 AND name = \*(Aqjoe\*(Aq) OR name IS NULL
.Ve
.PP
We can represent this clause by the following tree:
.PP
.Vb 6
\&              (id > 2)   (name = \*(Aqjoe\*(Aq)
\&                     \e   /
\&          NOT         AND
\&                         \e      (name IS NULL)
\&                          \e    /
\&                            OR
.Ve
.PP
Thus the \s-1WHERE\s0 clause would return an SQL::Statement::Op instance with
the \fIop()\fR field set to '\s-1OR\s0'. The \fIarg2()\fR field would return another
SQL::Statement::Op instance with \fIarg1()\fR being the SQL::Statement::Column
instance representing id, the \fIarg2()\fR field containing the value undef
(\s-1NULL\s0) and the \fIop()\fR field being '\s-1IS\s0'.
.PP
The \fIarg1()\fR field of the topmost Op instance would return an Op instance
with \fIop()\fR eq '\s-1AND\s0' and \fIneg()\fR returning \s-1TRUE.\s0 The \fIarg1()\fR and \fIarg2()\fR
fields would be Op's representing \*(L"id > 2\*(R" and \*(L"name = 'joe'\*(R".
.PP
Of course there's a ready-for-use method for \s-1WHERE\s0 clause evaluation:
.PP
The \s-1WHERE\s0 clause evaluation depends on an object being used for
fetching parameter and column values. Usually this can be an
SQL::Statement::RAM::Table object or SQL::Eval object, but in fact it
can be any object that supplies the methods
.PP
.Vb 2
\&    $val = $eval\->param($paramNum);
\&    $val = $eval\->column($table, $column);
.Ve
.PP
Once you have such an object, you can call eval_where;
.PP
.Vb 1
\&    $match = $stmt\->eval_where($eval);
.Ve
.SS "\fBwhere\fP"
.IX Subsection "where"
.Vb 1
\&    my $where = $stmt\->where();
.Ve
.PP
This method is used to examine the syntax tree of the \f(CW\*(C`WHERE\*(C'\fR clause. It
returns \fIundef\fR (if no \f(CW\*(C`WHERE\*(C'\fR clause was used) or an instance of
SQL::Statement::Term.
.PP
The where clause is evaluated automatically on the current selected row of
the table currently worked on when it's \f(CW\*(C`value()\*(C'\fR method is invoked.
.PP
\&\f(CW\*(C`SQL::Statement\*(C'\fR creates the object tree for where clause evaluation
directly after successfully parsing a statement from the given
\&\f(CW\*(C`where_clause\*(C'\fR, if any.
.SH "Executing and fetching data from SQL statements"
.IX Header "Executing and fetching data from SQL statements"
.SS "execute"
.IX Subsection "execute"
When called from a \s-1DBD\s0 or other subclass of SQL::Statement, the \fIexecute()\fR
method will be executed against whatever datasource (persistent storage) is
supplied by the \s-1DBD\s0 or the subclass (e.g. \s-1CSV\s0 files for \s-1DBD::CSV\s0, or
BerkeleyDB for \s-1DBD::DBM\s0). If you are using SQL::Statement directly
rather than as a subclass, you can call the \fIexecute()\fR method and the
statements will be \fIexecuted()\fR using temporary in-memory tables. When used
directly, like that, you need to create a cache hashref and pass it as the
first argument to execute:
.PP
.Vb 4
\&  my $cache  = {};
\&  my $parser = SQL::Parser\->new();
\&  my $stmt   = SQL::Statement\->new(\*(AqCREATE TABLE x (id INT)\*(Aq,$parser);
\&  $stmt\->execute( $cache );
.Ve
.PP
If you are using a statement with placeholders, those can be passed to
execute after the \f(CW$cache\fR:
.PP
.Vb 2
\&  $stmt      = SQL::Statement\->new(\*(AqINSERT INTO y VALUES(?,?)\*(Aq,$parser);
\&  $stmt\->execute( $cache, 7, \*(Aqfoo\*(Aq );
.Ve
.SS "fetch"
.IX Subsection "fetch"
Only a single \f(CW\*(C`fetch()\*(C'\fR method is provided \- it returns a single row of
data as an arrayref. Use a loop to fetch all rows:
.PP
.Vb 3
\& while (my $row = $stmt\->fetch()) {
\&     # ...
\& }
.Ve
.SS "an example of executing and fetching"
.IX Subsection "an example of executing and fetching"
.Vb 3
\& #!/usr/bin/perl \-w
\& use strict;
\& use SQL::Statement;
\&
\& my $cache={};
\& my $parser = SQL::Parser\->new();
\& for my $sql(split /\en/,
\& "  CREATE TABLE a (b INT)
\&    INSERT INTO a VALUES(1)
\&    INSERT INTO a VALUES(2)
\&    SELECT MAX(b) FROM a  "
\& )
\& {
\&    $stmt = SQL::Statement\->new($sql,$parser);
\&    $stmt\->execute($cache);
\&    next unless $stmt\->command eq \*(AqSELECT\*(Aq;
\&    while (my $row=$stmt\->fetch)
\&    {
\&        print "@$row\en";
\&    }
\& }
\& _\|_END_\|_
.Ve
.SH "AUTHOR & COPYRIGHT"
.IX Header "AUTHOR & COPYRIGHT"
Copyright (c) 2005, Jeff Zucker <jzuckerATcpan.org>, all rights reserved.
Copyright (c) 2009, Jens Rehsack <rehsackATcpan.org>, all rights reserved.
.PP
This document may be freely modified and distributed under the same terms
as Perl itself.