.\" 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::Parser 3"
.TH SQL::Parser 3 "2016-04-11" "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"
.Vb 1
\& SQL::Parser \-\- validate and parse SQL strings
.Ve
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
.Vb 2
\& use SQL::Parser;                                     # CREATE A PARSER OBJECT
\& my $parser = SQL::Parser\->new();
\&
\& $parser\->feature( $class, $name, $value );           # SET OR FIND STATUS OF
\& my $has_feature = $parser\->feature( $class, $name ); # A PARSER FEATURE
\&
\& $parser\->dialect( $dialect_name );                   # SET OR FIND STATUS OF
\& my $current_dialect = $parser\->dialect;              # A PARSER DIALECT
.Ve
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
SQL::Parser is part of the SQL::Statement distribution and, most
interaction with the parser should be done through SQL::Statement.
The methods shown above create and modify a parser object.  To use the
parser object to parse \s-1SQL\s0 and to examine the resulting structure, you
should use SQL::Statement.
.PP
\&\fBImportant Note\fR: Previously SQL::Parser had its own hash-based
interface for parsing, but that is now deprecated and will eventually
be phased out in favor of the object-oriented parsing interface of
SQL::Statement.  If you are unable to transition some features to the
new interface or have concerns about the phase out, please contact me.
See \*(L"The Parse Structure\*(R" for details of the now-deprecated hash
method if you still need them.
.SH "METHODS"
.IX Header "METHODS"
.SS "\fInew()\fP"
.IX Subsection "new()"
Create a new parser object
.PP
.Vb 2
\& use SQL::Parser;
\& my $parser = SQL::Parser\->new();
.Ve
.PP
The \fInew()\fR method creates a SQL::Parser object which can then be
used to parse and validate the syntax of \s-1SQL\s0 strings. It takes two
optional parameters \- 1) the name of the \s-1SQL\s0 dialect that will define
the syntax rules for the parser and 2) a reference to a hash which can
contain additional attributes of the parser.  If no dialect is specified,
\&'AnyData' is the default.
.PP
.Vb 2
\& use SQL::Parser;
\& my $parser = SQL::Parser\->new( $dialect_name, \e%attrs );
.Ve
.PP
The dialect_name parameter is a string containing any valid
dialect such as '\s-1ANSI\s0', 'AnyData', or '\s-1CSV\s0'.  See the section on
the \fIdialect()\fR method below for details.
.PP
The \f(CW\*(C`attrs\*(C'\fR parameter is a reference to a hash that can
contain error settings for the PrintError and RaiseError
attributes.
.PP
An example:
.PP
.Vb 2
\&  use SQL::Parser;
\&  my $parser = SQL::Parser\->new(\*(AqAnyData\*(Aq, {RaiseError=>1} );
\&
\&  This creates a new parser that uses the grammar rules
\&  contained in the .../SQL/Dialects/AnyData.pm file and which
\&  sets the RaiseError attribute to true.
.Ve
.SS "\fIdialect()\fP"
.IX Subsection "dialect()"
.Vb 2
\& $parser\->dialect( $dialect_name );     # load a dialect configuration file
\& my $dialect = $parser\->dialect;        # get the name of the current dialect
\&
\& For example:
\&
\&   $parser\->dialect(\*(AqAnyData\*(Aq);  # loads the AnyData config file
\&   print $parser\->dialect;       # prints \*(AqAnyData\*(Aq
.Ve
.PP
The \f(CW$dialect_name\fR parameter may be the name of any dialect
configuration file on your system.  Use the
\&\f(CW$parser\fR\->list('dialects') method to see a list of available
dialects.  At a minimum it will include \*(L"\s-1ANSI\*(R", \*(L"CSV\*(R",\s0 and
\&\*(L"AnyData\*(R".  For backwards compatibility 'Ansi' is accepted as a
synonym for '\s-1ANSI\s0', otherwise the names are case sensitive.
.PP
Loading a new dialect configuration file erases all current
parser features and resets them to those defined in the
configuration file.
.SS "\fIfeature()\fP"
.IX Subsection "feature()"
Features define the rules to be used by a specific parser
instance.  They are divided into the following classes:
.PP
.Vb 5
\&    * valid_commands
\&    * valid_options
\&    * valid_comparison_operators
\&    * valid_data_types
\&    * reserved_words
.Ve
.PP
Within each class a feature name is either enabled or
disabled. For example, under \*(L"valid_data_types\*(R" the name \*(L"\s-1BLOB\*(R"\s0
may be either disabled or enabled.  If it is not enabled
(either by being specifically disabled, or simply by not being
specified at all) then any \s-1SQL\s0 string using \*(L"\s-1BLOB\*(R"\s0 as a data
type will throw a syntax error \*(L"Invalid data type: '\s-1BLOB\s0'\*(R".
.PP
The \fIfeature()\fR method allows you to enable, disable, or check the
status of any feature.
.PP
.Vb 1
\& $parser\->feature( $class, $name, 1 );             # enable a feature
\&
\& $parser\->feature( $class, $name, 0 );             # disable a feature
\&
\& my $feature = $parser\->feature( $class, $name );  # return status of a feature
\&
\& For example:
\&
\& $parser\->feature(\*(Aqreserved_words\*(Aq,\*(AqFOO\*(Aq,1);       # make \*(AqFOO\*(Aq a reserved word
\&
\& $parser\->feature(\*(Aqvalid_data_types\*(Aq,\*(AqBLOB\*(Aq,0);    # disallow \*(AqBLOB\*(Aq as a
\&                                                   # data type
\&
\&                                                   # determine if the LIKE
\&                                                   # operator is supported
\& my $LIKE = $parser\->feature(\*(Aqvalid_comparison_operators\*(Aq,\*(AqLIKE\*(Aq);
.Ve
.PP
See the section below on \*(L"Backwards Compatibility\*(R" for use of
the \fIfeature()\fR method with SQL::Statement 0.1x style parameters.
.SH "Supported SQL syntax"
.IX Header "Supported SQL syntax"
The SQL::Statement distribution can be used to either just parse \s-1SQL\s0
statements or to execute them against actual data.  A broader set of
syntax is supported in the parser than in the executor.  For example
the parser allows you to specify column constraints like \s-1PRIMARY KEY.\s0
Currently, these are ignored by the execution engine.  Likewise syntax
such as \s-1RESTRICT\s0 and \s-1CASCADE\s0 on \s-1DROP\s0 statements or \s-1LOCAL GLOBAL TEMPORARY\s0
tables in \s-1CREATE\s0 are supported by the parser but ignored by the executor.
.PP
To see the list of Supported \s-1SQL\s0 syntax formerly kept in this pod, see
SQL::Statement.
.SH "Subclassing SQL::Parser"
.IX Header "Subclassing SQL::Parser"
In the event you need to either extend or modify SQL::Parser's
default behavior, the following methods may be overridden:
.ie n .IP """$self\-"">""get_btwn($string)""" 4
.el .IP "\f(CW$self\-\fR>\f(CWget_btwn($string)\fR" 4
.IX Item "$self->get_btwn($string)"
Processes the \s-1BETWEEN...AND...\s0 predicates; default converts to
2 range predicates.
.ie n .IP """$self\-"">""get_in($string)""" 4
.el .IP "\f(CW$self\-\fR>\f(CWget_in($string)\fR" 4
.IX Item "$self->get_in($string)"
Process the \s-1IN \s0(...list...) predicates; default converts to
a series of \s-1OR\s0'd '=' predicate, or \s-1AND\s0'd '<>' predicates for
\&\s-1NOT IN.\s0
.ie n .IP """$self\-"">""transform_syntax($string)""" 4
.el .IP "\f(CW$self\-\fR>\f(CWtransform_syntax($string)\fR" 4
.IX Item "$self->transform_syntax($string)"
Abstract method; default simply returns the original string.
Called after \fIrepl_btwn()\fR and \fIrepl_in()\fR, but before any further
predicate processing is applied. Possible uses include converting
other predicate syntax not recognized by SQL::Parser into user-defined
functions.
.SH "The parse structure"
.IX Header "The parse structure"
This section outlines the \fBnow-deprecated\fR hash interface to the
parsed structure.  It is included \fBfor backwards compatibility only\fR.
You should use the SQL::Statement object interface to the structure
instead.  See SQL::Statement.
.PP
\&\fBParse Structures\fR
.PP
Here are some further examples of the data structures returned
by the \fIstructure()\fR method after a call to \fIparse()\fR.  Only
specific details are shown for each \s-1SQL\s0 instance, not the entire
structure.
.PP
\&\fB\f(BIparse()\fB\fR
.PP
Once a SQL::Parser object has been created with the \fInew()\fR
method, the \fIparse()\fR method can be used to parse any number of
\&\s-1SQL\s0 strings.  It takes a single required parameter \*(-- a string
containing a \s-1SQL\s0 command.  The \s-1SQL\s0 string may optionally be
terminated by a semicolon.  The \fIparse()\fR method returns a true
value if the parse is successful and a false value if the parse
finds \s-1SQL\s0 syntax errors.
.PP
Examples:
.PP
.Vb 1
\&  1) my $success = $parser\->parse(\*(AqSELECT * FROM foo\*(Aq);
\&
\&  2) my $sql = \*(AqSELECT * FROM foo\*(Aq;
\&     my $success = $parser\->parse( $sql );
\&
\&  3) my $success = $parser\->parse(qq!
\&         SELECT id,phrase
\&           FROM foo
\&          WHERE id < 7
\&            AND phrase <> \*(Aqbar\*(Aq
\&       ORDER BY phrase;
\&   !);
\&
\&  4) my $success = $parser\->parse(\*(AqSELECT * FRoOM foo \*(Aq);
.Ve
.PP
In examples #1,#2, and #3, the value of \f(CW$success\fR will be true
because the strings passed to the \fIparse()\fR method are valid \s-1SQL\s0
strings.
.PP
In example #4, however, the value of \f(CW$success\fR will be false
because the string contains a \s-1SQL\s0 syntax error ('FRoOM' instead
of '\s-1FROM\s0').
.PP
In addition to checking the return value of \fIparse()\fR with a
variable like \f(CW$success\fR, you may use the PrintError and
RaiseError attributes as you would in a \s-1DBI\s0 script:
.PP
.Vb 5
\& * If PrintError is true, then SQL syntax errors will be sent as
\&   warnings to STDERR (i.e. to the screen or to a file if STDERR
\&   has been redirected).  This is set to true by default which
\&   means that unless you specifically turn it off, all errors
\&   will be reported.
\&
\& * If RaiseError is true, then SQL syntax errors will cause the
\&   script to die, (i.e. the script will terminate unless wrapped
\&   in an eval).  This is set to false by default which means
\&   that unless you specifically turn it on, scripts will
\&   continue to operate even if there are SQL syntax errors.
.Ve
.PP
Basically, you should leave PrintError on or else you will not
be warned when an error occurs.  If you are simply validating a
series of strings, you will want to leave RaiseError off so that
the script can check all strings regardless of whether some of
them contain \s-1SQL\s0 errors.  However, if you are going to try to
execute the \s-1SQL\s0 or need to depend that it is correct, you should
set RaiseError on so that the program will only continue to
operate if all \s-1SQL\s0 strings use correct syntax.
.PP
\&\s-1IMPORTANT NOTE\s0 #1: The \fIparse()\fR method only checks syntax, it
does \s-1NOT\s0 verify if the objects listed actually exist.  For
example, given the string \*(L"\s-1SELECT\s0 model \s-1FROM\s0 cars\*(R", the \fIparse()\fR
method will report that the string contains valid \s-1SQL\s0 but that
will not tell you whether there actually is a table called
\&\*(L"cars\*(R" or whether that table contains a column called 'model'.
Those kinds of verifications are performed by the
SQL::Statement module, not by SQL::Parser by itself.
.PP
\&\s-1IMPORTANT NOTE\s0 #2: The \fIparse()\fR method uses rules as defined by
the selected dialect configuration file and the \fIfeature()\fR
method.  This means that a statement that is valid in one
dialect may not be valid in another.  For example the '\s-1CSV\s0' and
\&'AnyData' dialects define '\s-1BLOB\s0' as a valid data type but the
\&'\s-1ANSI\s0' dialect does not.  Therefore the statement '\s-1CREATE TABLE\s0
foo (picture \s-1BLOB\s0)' would be valid in the first two dialects but
would produce a syntax error in the '\s-1ANSI\s0' dialect.
.PP
\&\fB\f(BIstructure()\fB\fR
.PP
After a SQL::Parser object has been created and the \fIparse()\fR
method used to parse a \s-1SQL\s0 string, the \fIstructure()\fR method
returns the data structure of that string.  This data structure
may be passed on to other modules (e.g. SQL::Statement) or it
may be printed out using, for example, the Data::Dumper module.
.PP
The data structure contains all of the information in the \s-1SQL\s0
string as parsed into its various components.  To take a simple
example:
.PP
.Vb 3
\& $parser\->parse(\*(AqSELECT make,model FROM cars\*(Aq);
\& use Data::Dumper;
\& print Dumper $parser\->structure;
.Ve
.PP
Would produce:
.PP
.Vb 12
\& $VAR1 = {
\&          \*(Aqcolumn_defs\*(Aq => [
\&                              { \*(Aqtype\*(Aq  => \*(Aqcolumn\*(Aq,
\&                                \*(Aqvalue\*(Aq => \*(Aqmake\*(Aq, },
\&                              { \*(Aqtype\*(Aq  => \*(Aqcolumn\*(Aq,
\&                                \*(Aqvalue\*(Aq => \*(Aqmodel\*(Aq, },
\&                            ],
\&          \*(Aqcommand\*(Aq => \*(AqSELECT\*(Aq,
\&          \*(Aqtable_names\*(Aq => [
\&                             \*(Aqcars\*(Aq
\&                           ]
\&        };
\&
\&
\& \*(AqSELECT make,model, FROM cars\*(Aq
\&
\&      command => \*(AqSELECT\*(Aq,
\&      table_names => [ \*(Aqcars\*(Aq ],
\&      column_names => [ \*(Aqmake\*(Aq, \*(Aqmodel\*(Aq ],
\&
\& \*(AqCREATE TABLE cars ( id INTEGER, model VARCHAR(40) )\*(Aq
\&
\&      column_defs => {
\&          id    => { data_type => INTEGER     },
\&          model => { data_type => VARCHAR(40) },
\&      },
\&
\& \*(AqSELECT DISTINCT make FROM cars\*(Aq
\&
\&      set_quantifier => \*(AqDISTINCT\*(Aq,
\&
\& \*(AqSELECT MAX (model) FROM cars\*(Aq
\&
\&    set_function   => {
\&        name => \*(AqMAX\*(Aq,
\&        arg  => \*(Aqmodels\*(Aq,
\&    },
\&
\& \*(AqSELECT * FROM cars LIMIT 5,10\*(Aq
\&
\&    limit_clause => {
\&        offset => 5,
\&        limit  => 10,
\&    },
\&
\& \*(AqSELECT * FROM vars ORDER BY make, model DESC\*(Aq
\&
\&    sort_spec_list => [
\&        { make  => \*(AqASC\*(Aq  },
\&        { model => \*(AqDESC\*(Aq },
\&    ],
\&
\& "INSERT INTO cars VALUES ( 7, \*(AqChevy\*(Aq, \*(AqImpala\*(Aq )"
\&
\&    values => [ 7, \*(AqChevy\*(Aq, \*(AqImpala\*(Aq ],
.Ve
.SH "SUPPORT"
.IX Header "SUPPORT"
You can find documentation for this module with the perldoc command.
.PP
.Vb 2
\&    perldoc SQL::Parser
\&    perldoc SQL::Statement
.Ve
.PP
You can also look for information at:
.IP "\(bu" 4
\&\s-1RT: CPAN\s0's request tracker
.Sp
<http://rt.cpan.org/NoAuth/Bugs.html?Dist=SQL\-Statement>
.IP "\(bu" 4
AnnoCPAN: Annotated \s-1CPAN\s0 documentation
.Sp
<http://annocpan.org/dist/SQL\-Statement>
.IP "\(bu" 4
\&\s-1CPAN\s0 Ratings
.Sp
<http://cpanratings.perl.org/s/SQL\-Statement>
.IP "\(bu" 4
Search \s-1CPAN\s0
.Sp
<http://search.cpan.org/dist/SQL\-Statement/>
.SS "Where can I go for help?"
.IX Subsection "Where can I go for help?"
For questions about installation or usage, please ask on the
dbi\-users@perl.org mailing list or post a question on PerlMonks
(<http://www.perlmonks.org/>, where Jeff is known as jZed).
Jens does not visit PerlMonks on a regular basis.
.PP
If you have a bug report, a patch or a suggestion, please open a new
report ticket at \s-1CPAN \s0(but please check previous reports first in case
your issue has already been addressed). You can mail any of the module
maintainers, but you are more assured of an answer by posting to the
dbi-users list or reporting the issue in \s-1RT.\s0
.PP
Report tickets should contain a detailed description of the bug or
enhancement request and at least an easily verifiable way of
reproducing the issue or fix. Patches are always welcome, too.
.SS "Where can I go for help with a concrete version?"
.IX Subsection "Where can I go for help with a concrete version?"
Bugs and feature requests are accepted against the latest version
only. To get patches for earlier versions, you need to get an
agreement with a developer of your choice \- who may or not report the
the issue and a suggested fix upstream (depends on the license you
have chosen).
.SS "Business support and maintenance"
.IX Subsection "Business support and maintenance"
For business support you can contact Jens via his \s-1CPAN\s0 email
address rehsackATcpan.org. Please keep in mind that business
support is neither available for free nor are you eligible to
receive any support based on the license distributed with this
package.
.SH "AUTHOR & COPYRIGHT"
.IX Header "AUTHOR & COPYRIGHT"
.Vb 1
\& This module is
\&
\& copyright (c) 2001,2005 by Jeff Zucker and
\& copyright (c) 2007\-2016 by Jens Rehsack.
\&
\& All rights reserved.
.Ve
.PP
The module may be freely distributed under the same terms as
Perl itself using either the \*(L"\s-1GPL\s0 License\*(R" or the \*(L"Artistic
License\*(R" as specified in the Perl \s-1README\s0 file.
.PP
Jeff can be reached at: jzuckerATcpan.org
Jens can be reached at: rehsackATcpan.org or via dbi\-devATperl.org