.\" 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::Syntax 3"
.TH SQL::Statement::Syntax 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::Syntax \- documentation of SQL::Statement's SQL Syntax
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
See SQL::Statement for usage.
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
The SQL::Statement module can be used either from a \s-1DBI\s0 driver like \s-1DBD::CSV\s0
or directly.  The syntax below applies to both situations.  In the case of
DBDs, each \s-1DBD\s0 can implement its own sub-dialect so be sure to check the \s-1DBD\s0
documentation also.
.PP
SQL::Statement is meant primarily as a base class for \s-1DBD\s0 drivers
and as such concentrates on a small but useful subset of \s-1SQL.\s0
It does *not* in any way pretend to be a complete \s-1SQL\s0 parser for
all dialects of \s-1SQL. \s0 The module will continue to add new supported syntax,
and users may also extend the syntax (see \*(L"#Extending the \s-1SQL\s0 syntax\*(R").
.SH "USAGE"
.IX Header "USAGE"
.SS "Default Supported \s-1SQL\s0 syntax \- Summary"
.IX Subsection "Default Supported SQL syntax - Summary"
\&\fB\s-1SQL\s0 Statements\fR
.PP
.Vb 10
\&   CALL <function>
\&   CREATE [TEMP] TABLE <table> <column_def_clause>
\&   CREATE [TEMP] TABLE <table> AS <select statement>
\&   CREATE [TEMP] TABLE <table> AS IMPORT()
\&   CREATE FUNCTION <user_defined_function> [ NAME <perl_subroutine> ]
\&   CREATE KEYWORD  <user_defined_keyword>  [ NAME <perl_subroutine> ]
\&   CREATE OPERATOR <user_defined_operator> [ NAME <perl_subroutine> ]
\&   CREATE TYPE     <user_defined_type>     [ NAME <perl_subroutine> ]
\&   DELETE FROM <table> [<where_clause>]
\&   DROP TABLE [IF EXISTS] <table>
\&   DROP FUNCTION <function>
\&   DROP KEYWORD  <keyword>
\&   DROP OPERATOR <operator>
\&   DROP TYPE     <type>
\&   INSERT [INTO] <table> [<column_list>] VALUES <value_list>
\&   LOAD <user_defined_functions_module>
\&   SELECT <function>
\&   SELECT <select_clause>
\&          <from_clause>
\&          [<where_clause>]
\&          [ ORDER BY ocol1 [ASC|DESC], ... ocolN [ASC|DESC]] ]
\&          [ GROUP BY gcol1 [, ... gcolN] ]
\&          [ LIMIT [start,] length ]
\&   UPDATE <table> SET <set_clause> [<where_clause>]
.Ve
.PP
\&\fBExplicit Join Qualifiers\fR
.PP
.Vb 1
\&   NATURAL, INNER, OUTER, LEFT, RIGHT, FULL
.Ve
.PP
\&\fBBuilt-in Functions\fR
.PP
.Vb 10
\&   * Aggregate : MIN, MAX, AVG, SUM, COUNT
\&   * Date/Time : CURRENT_DATE, CURDATE, CURRENT_TIME, CURTIME, CURRENT_TIMESTAMP, NOW,
\&                 UNIX_TIMESTAMP
\&   * String    : ASCII, CHAR, BIT_LENGTH, CHARACTER_LENGTH, CHAR_LENGTH, COALESCE,
\&                 NVL, IFNULL, CONV, CONCAT, DECODE, HEX, OCT, BIN, INSERT, LEFT, RIGHT,
\&                 LOCATE, POSITION, LOWER, UPPER, LCASE, UCASE, LTRIM, RTRIM, OCTET_LENGTH,
\&                 REGEX, REPEAT, REPLACE, SOUNDEX, SPACE, SUBSTITUTE, SUBSTRING, SUBSTR,
\&                 TRANSLATE, TRIM, UNHEX
\&   * Numeric   : ABS, CEILING, CEIL, FLOOR, ROUND, EXP, LOG, LN, LOG10, MOD, POWER,
\&                 RAND, SIGN, SQRT, TRUNCATE, TRUNC
\&   * Trig      : ACOS, ACOSEC, ACOSECH, ACOSH, ACOT, ACOTAN, ACOTANH, ACOTH, ACSC,
\&                 ACSCH, ASEC, ASECH, ASIN, ASINH, ATAN, ATAN2, ATANH, COS, COSEC,
\&                 COSECH, COSH, COT, COTAN, COTANH, COTH, CSC, CSCH, DEG2DEG, DEG2GRAD,
\&                 DEG2RAD, DEGREES, GRAD2DEG, GRAD2GRAD, GRAD2RAD, PI, RAD2DEG, RAD2GRAD,
\&                 RAD2RAD, RADIANS, SEC, SECH, SIN, SINH, TAN, TANH
\&   * System    : DBNAME, USERNAME, USER
.Ve
.PP
\&\fBSpecial Utility Functions\fR
.PP
.Vb 2
\&  * IMPORT  \- imports a table from an external RDBMS or perl structure
\&  * RUN     \- prepares and executes statements in a file of SQL statements
.Ve
.PP
\&\fBOperators and Predicates\fR
.PP
.Vb 1
\&   = , <> , < , > , <= , >= , IS [NOT] (NULL|TRUE|FALSE) , LIKE , CLIKE , IN , BETWEEN
.Ve
.PP
\&\fBIdentifiers\fR and \fBAliases\fR
.PP
.Vb 3
\&   * regular identifiers are case insensitive (though see note on table names)
\&   * delimited identifiers (inside double quotes) are case sensitive
\&   * column and table aliases are supported
.Ve
.PP
\&\fBConcatenation\fR
.PP
.Vb 2
\&   * use either ANSI SQL || or the CONCAT() function
\&   * e.g. these are the same:  {foo || bar} {CONCAT(foo,bar)}
.Ve
.PP
\&\fBComments\fR
.PP
.Vb 2
\&   * comments must occur before or after statements, cannot be embedded
\&   * SQL\-style single line \-\- and C\-style multi\-line /* */ comments are supported
.Ve
.PP
\&\fBNULLs\fR
.PP
.Vb 2
\&   * currently NULLs and empty strings are identical in non\-ANSI dialect.
\&   * use {col IS NULL} to find NULLs, not {col=\*(Aq\*(Aq} (though both may work depending on dialect)
.Ve
.PP
See below for further details.
.SS "Syntax \- Details"
.IX Subsection "Syntax - Details"
\fI\s-1CREATE TABLE\s0\fR
.IX Subsection "CREATE TABLE"
.PP
Creates permanent and in-memory tables.
.PP
.Vb 3
\& CREATE [TEMP] TABLE <table_name> ( <column_definitions> )
\& CREATE [TEMP] TABLE <table_name> AS <select statement>
\& CREATE [TEMP] TABLE <table_name> AS IMPORT()
.Ve
.PP
Column definitions are standard \s-1SQL\s0 column names, types, and
constraints, see \*(L"Column Definitions\*(R".
.PP
.Vb 3
\&  # create a permanent table
\&  #
\&  $dbh\->do("CREATE TABLE qux (id INT PRIMARY KEY,word VARCHAR(30))");
.Ve
.PP
The \*(L"\s-1AS SELECT\*(R"\s0 clause creates and populates the new table using the
data and column structure specified in the select statement.
.PP
.Vb 3
\&  # create and populate a table from a query to two other tables
\&  #
\&  $dbh\->do("CREATE TABLE qux AS SELECT id,word FROM foo NATURAL JOIN bar");
.Ve
.PP
If the optional keyword \s-1TEMP \s0(or its synonym \s-1TEMPORARY\s0) is used, the table
will be an in-memory table, available  for the life of the current
database handle or until  a \s-1DROP TABLE\s0 command is issued.
.PP
.Vb 3
\&  # create a temporary table
\&  #
\&  $dbh\->do("CREATE TEMP TABLE qux (id INT PRIMARY KEY,word VARCHAR(30))");
.Ve
.PP
\&\s-1TEMP\s0 tables can be modified with \s-1SQL\s0 commands but the updates are not
automatically reflected back to any permanent tables they may be
associated with.  To save a \s-1TEMP\s0 table \- just use an \s-1AS SELECT\s0 clause:
.PP
.Vb 6
\& $dbh = DBI\->connect( \*(Aqdbi:CSV:\*(Aq );
\& $dbh\->do("CREATE TEMP TABLE qux_temp AS (id INT, word VARCHAR(30))");
\& #
\& # ... modify qux_temp with INSERT, UPDATE, DELETE statements, then save it
\& #
\& $dbh\->do("CREATE TABLE qux_permanent AS SELECT * FROM qux_temp");
.Ve
.PP
Tables, both temporary and permanent may also be created directly from
perl arrayrefs and from heterogeneous queries to any \s-1DBI\s0 accessible
data source, see the \s-1\fIIMPORT\s0()\fR function.
.PP
.Vb 7
\& CREATE [ {LOCAL|GLOBAL} TEMPORARY ] TABLE $table
\&        (
\&           $col_1 $col_type1 $col_constraints1,
\&           ...,
\&           $col_N $col_typeN $col_constraintsN,
\&        )
\&        [ ON COMMIT {DELETE|PRESERVE} ROWS ]
\&
\&     * col_type must be a valid data type as defined in the
\&       "valid_data_types" section of the dialect file for the
\&       current dialect
\&
\&     * col_constraints may be "PRIMARY KEY" or one or both of
\&       "UNIQUE" and/or "NOT NULL"
\&
\&     * IMPORTANT NOTE: temporary tables, data types and column
\&       constraints are checked for syntax violations but are
\&       currently otherwise *IGNORED* \-\- they are recognized by
\&       the parser, but not by the execution engine
\&
\&     * The following valid ANSI SQL92 options are not currently
\&       supported: table constraints, named constraints, check
\&       constraints, reference constraints, constraint
\&       attributes, collations, default clauses, domain names as
\&       data types
.Ve
.PP
\fI\s-1DROP TABLE\s0\fR
.IX Subsection "DROP TABLE"
.PP
.Vb 1
\& DROP TABLE $table [ RESTRICT | CASCADE ]
\&
\&     * IMPORTANT NOTE: drop behavior (cascade or restrict) is
\&       checked for valid syntax but is otherwise *IGNORED* \-\- it
\&       is recognized by the parser, but not by the execution
\&       engine
.Ve
.PP
\fI\s-1INSERT INTO\s0\fR
.IX Subsection "INSERT INTO"
.PP
.Vb 1
\& INSERT INTO $table [ ( $col1, ..., $colN ) ] VALUES ( $val1, ... $valN )
\&
\&     * default values are not currently supported
\&     * inserting from a subquery is not currently supported
.Ve
.PP
\fI\s-1DELETE FROM\s0\fR
.IX Subsection "DELETE FROM"
.PP
.Vb 1
\& DELETE FROM $table [ WHERE search_condition ]
\&
\&     * see "search_condition" below
.Ve
.PP
\fI\s-1UPDATE\s0\fR
.IX Subsection "UPDATE"
.PP
.Vb 1
\& UPDATE $table SET $col1 = $val1, ... $colN = $valN [ WHERE search_condition ]
\&
\&     * default values are not currently supported
\&     * see "search_condition" below
.Ve
.PP
\fI\s-1SELECT\s0\fR
.IX Subsection "SELECT"
.PP
.Vb 5
\&      SELECT select_clause
\&        FROM from_clause
\&     [ WHERE search_condition ]
\&  [ ORDER BY $ocol1 [ASC|DESC], ... $ocolN [ASC|DESC] ]
\&     [ LIMIT [start,] length ]
\&
\&      * select clause ::=
\&             [DISTINCT|ALL] *
\&           | [DISTINCT|ALL] col1 [,col2, ... colN]
\&           | set_function1 [,set_function2, ... set_functionN]
\&
\&      * set function ::=
\&             COUNT ( [ALL] * )
\&           | COUNT | MIN | MAX | AVG | SUM ( [DISTINCT|ALL] col_name )
\&
\&      * from clause ::=
\&             table1 [, table2, ... tableN]
\&           | table1 NATURAL [join_type] JOIN table2
\&           | table1 [join_type] table2 USING (col1,col2, ... colN)
\&           | table1 [join_type] JOIN table2 ON table1.colA = table2.colB
\&
\&      * join type ::=
\&             INNER
\&           | [OUTER] LEFT | RIGHT | FULL
\&
\&      * if join_type is not specified, INNER is the default
\&      * if DISTINCT or ALL is not specified, ALL is the default
\&      * if start position is omitted from LIMIT clause, position 0 is
\&        the default
\&      * ON clauses may only contain equal comparisons and AND combiners
\&      * self\-joins are not currently supported
\&      * if implicit joins are used, the WHERE clause must contain
\&        an equijoin condition for each table
\&      * multiple ANSI joins are not supported; use implicit joins for these
\&      * this also means that combinations of INNER and non\-INNER joins are
\&        not supported
.Ve
.PP
\fI\s-1SEARCH CONDITION\s0\fR
.IX Subsection "SEARCH CONDITION"
.PP
.Vb 1
\&       [NOT] $val1 $op1 $val1 [ ... AND|OR $valN $opN $valN ]
.Ve
.PP
\fI\s-1OPERATORS\s0\fR
.IX Subsection "OPERATORS"
.PP
.Vb 3
\&       $op  = |  <> |  < | > | <= | >=
\&              | IS [NOT] NULL | IS [NOT] TRUE | IS [NOT] FALSE
\&              | LIKE | CLIKE | BETWEEN | IN
\&
\&  The "CLIKE" operator works exactly the same as the "LIKE"
\&  operator, but is case insensitive.  For example:
\&
\&      WHERE foo LIKE \*(Aqbar%\*(Aq   # succeeds if foo is "barbaz"
\&                              # fails if foo is "BARBAZ" or "Barbaz"
\&
\&      WHERE foo CLIKE \*(Aqbar%\*(Aq  # succeeds for "barbaz", "Barbaz", and "BARBAZ"
.Ve
.PP
\fIBUILT-IN \s-1AND\s0 USER-DEFINED \s-1FUNCTIONS\s0\fR
.IX Subsection "BUILT-IN AND USER-DEFINED FUNCTIONS"
.PP
There are many built-in functions and you can also create your
own new functions from perl subroutines.  See SQL::Statement::Functions
for documentation of functions.
.PP
\fIIdentifiers (table & column names)\fR
.IX Subsection "Identifiers (table & column names)"
.PP
Regular identifiers (table and column names *without* quotes around
them) are case \s-1INSENSITIVE\s0 so column foo, fOo, \s-1FOO\s0 all refer to the
same column.  Internally they are used in their lower case
representation, so do not rely on SQL::Statement retaining your case.
.PP
Delimited identifiers (table and column names *with* quotes around them) are
case \s-1SENSITIVE\s0 so column \*(L"foo\*(R", \*(L"fOo\*(R", \*(L"\s-1FOO\*(R"\s0 each refer to different columns.
.PP
A delimited identifier is *never* equal to a regular identifier (so \*(L"foo\*(R" and
foo are two different columns).  But don't do that :\-).
.PP
Remember thought that, in \s-1DBD::CSV\s0 if table names are used directly as file
names, the case sensitivity depends on the \s-1OS\s0 e.g. on Windows files named foo,
\&\s-1FOO,\s0 and fOo are the same as each other while on Unix they are different.
.PP
\fISpecial Utility \s-1SQL\s0 Functions\fR
.IX Subsection "Special Utility SQL Functions"
.PP
\s-1\fIIMPORT\s0()\fR
.IX Subsection "IMPORT()"
.PP
Imports the data and structure of a table from an external data source into a
permanent or temporary table.
.PP
.Vb 1
\& $dbh\->do("CREATE TABLE qux AS IMPORT(?)",{},$oracle_sth);
\&
\& $dbh\->do("CREATE TABLE qux AS IMPORT(?)",{},$AoA);
\&
\& $dbh\->do("CREATE TABLE qux AS IMPORT(?)",{},$AoH);
.Ve
.PP
\&\s-1\fIIMPORT\s0()\fR can also be used anywhere that table_names can:
.PP
.Vb 4
\& $sth=$dbh\->prepare("
\&    SELECT * FROM IMPORT(?) AS T1 NATURAL JOIN IMPORT(?) AS T2 WHERE T1.id ...
\& ");
\& $sth\->execute( $pg_sth, $mysql_sth );
.Ve
.PP
The \s-1\fIIMPORT\s0()\fR function imports the data and structure of a table from an
external data source.  The \s-1\fIIMPORT\s0()\fR function is always used with a placeholder
parameter which may be 1) a prepared and executed statement handle for any \s-1DBI\s0
accessible data source;  or 2) an AoA whose first row is column names and
whose succeeding rows are data 3) an AoH.
.PP
The \s-1\fIIMPORT\s0()\fR function may be used in the \s-1AS\s0 clause of a \s-1CREATE\s0 statement, and
in the \s-1FROM\s0 clause of any statement.  When used in a \s-1FROM\s0 clause, it should
be used with a column alias e.g. \s-1SELECT\s0 * \s-1FROM IMPORT\s0(?) \s-1AS\s0 TableA \s-1WHERE ...\s0
.PP
You can also write your own \s-1\fIIMPORT\s0()\fR functions to treat anything as a data
source.  See User-Defined Function in SQL::Statement::Functions.
.PP
Examples:
.PP
.Vb 6
\& # create a CSV file from an Oracle query
\& #
\& $dbh = DBI\->connect(\*(Aqdbi:CSV:\*(Aq);
\& $oracle_sth = $oracle_dbh\->prepare($any_oracle_query);
\& $oracle_sth\->execute(@params);
\& $dbh\->do("CREATE TABLE qux AS IMPORT(?)",{},$oracle_sth);
\&
\& # create an in\-memory table from an AoA
\& #
\& $dbh      = DBI\->connect( \*(Aqdbi:File:\*(Aq );
\& $arrayref = [[\*(Aqid\*(Aq,\*(Aqword\*(Aq],[1,\*(Aqfoo\*(Aq],[2,\*(Aqbar\*(Aq],];
\& $dbh\->do("CREATE TEMP TABLE qux AS IMPORT(?)",{},$arrayref);
\&
\& # query a join of a PostgreSQL table and a MySQL table
\& #
\& $dbh        = DBI\->connect( \*(Aqdbi:File:\*(Aq );
\& $pg_dbh     = DBI\->connect( ... DBD::pg connect params );
\& $mysql_dbh  = DBI\->connect( ... DBD::mysql connect params );
\& $pg_sth     = $pg_dbh\->prepare( ... any pg query );
\& $pg_sth     = $pg_dbh\->prepare( ... any mysql query );
\& #
\& $sth=$dbh\->prepare("
\&    SELECT * FROM IMPORT(?) AS T1 NATURAL JOIN IMPORT(?) AS T2
\& ");
\& $sth\->execute( $pg_sth, $mysql_sth );
.Ve
.PP
\s-1\fIRUN\s0()\fR
.IX Subsection "RUN()"
.PP
Run \s-1SQL\s0 statements from a user supplied file.  \fBPlease Note:\fR this
function is experimental, please let me know if you have problems.
.PP
.Vb 1
\& RUN( sql_file )
.Ve
.PP
If the file contains non-SELECT statements such as \s-1CREATE\s0 and \s-1INSERT,\s0 use
the \s-1\fIRUN\s0()\fR function with \f(CW$dbh\fR\->\fIdo()\fR.  For example, this prepares and
executes all of the \s-1SQL\s0 statements in a file called \*(L"populate.sql\*(R":
.PP
.Vb 1
\& $dbh\->do(" CALL RUN( \*(Aqpopulate.sql\*(Aq) ");
.Ve
.PP
If the file contains \s-1SELECT\s0 statements, the \s-1\fIRUN\s0()\fR function may be used
anywhere a table name may be used, for example, if you have a file called
\&\*(L"query.sql\*(R" containing \*(L"\s-1SELECT\s0 * \s-1FROM\s0 Employee\*(R", then these two lines
are exactly the same:
.PP
.Vb 1
\& my $sth = $dbh\->prepare(" SELECT * FROM Employee ");
\&
\& my $sth = $dbh\->prepare(" SELECT * FROM RUN( \*(Aqquery.sql\*(Aq ) ");
.Ve
.PP
If the file contains a statement with placeholders, the values for the
placehoders can be passed in the call to \f(CW$sth\fR\->\fIexecute()\fR as normal. If the
query.sql file contains \*(L"\s-1SELECT\s0 id,name \s-1FROM\s0 x \s-1WHERE\s0 id=?\*(R", then these
two are the same:
.PP
.Vb 2
\& my $sth = $dbh\->prepare(" SELECT id,name FROM x WHERE id=?");
\& $sth\->execute(64);
\&
\& my $sth = $dbh\->prepare(" SELECT * FROM RUN( \*(Aqquery.sql\*(Aq ) ");
\& $sth\->execute(64);
.Ve
.PP
\&\fBNote\fR This function assumes that the \s-1SQL\s0 statements in the file are
separated by a semi\-colon+newline combination (/;\en/).  If you wish to use
different separators or import \s-1SQL\s0 from a different source, just override
the \s-1\fIRUN\s0()\fR function with your own user-defined-function.
.SS "Further details"
.IX Subsection "Further details"
.IP "Integers" 8
.IX Item "Integers"
.PD 0
.IP "Reals" 8
.IX Item "Reals"
.PD
Syntax obvious
.IP "Strings" 8
.IX Item "Strings"
Surrounded by either single quotes; some characters need to
be escaped with a backslash, in particular the backslash itself (\e\e),
the \s-1NUL\s0 byte (\e0), Line feeds (\en), Carriage return (\er), and the
quotes (\e').
.Sp
\&\fBNote:\fR Quoting \*(L"Strings\*(R" using double quotes are recognized as
quoted identifiers (column or table names).
.IP "Parameters" 8
.IX Item "Parameters"
Parameters represent scalar values, like Integers, Reals and Strings
do. However, their values are read inside \fIExecute()\fR and not inside
\&\fIPrepare()\fR. Parameters are represented by question marks (?).
.IP "Identifiers" 8
.IX Item "Identifiers"
Identifiers are table or column names. Syntactically they consist of
alphabetic characters, followed by an arbitrary number of alphanumeric
characters. Identifiers like \s-1SELECT, INSERT, INTO, ORDER, BY, WHERE,
\&...\s0 are forbidden and reserved for other tokens.  Identifiers are always
compared case-insensitively, i.e. \f(CW\*(C`select foo from bar\*(C'\fR will be evaluated
the same as \f(CW\*(C`SELECT FOO FROM BAR\*(C'\fR (\f(CW\*(C`FOO\*(C'\fR will be evaluated as \f(CW\*(C`foo\*(C'\fR,
similar for \f(CW\*(C`BAR\*(C'\fR).
.Sp
Since SQL::Statement is internally using lower cased identifiers (unquoted),
everytime a wildcard is used, the delivered names of the identifiers are
lower cased.
.SH "Extending SQL syntax using SQL"
.IX Header "Extending SQL syntax using SQL"
The Supported \s-1SQL\s0 syntax shown above is the default for SQL::Statement but
it can be extended (or contracted) either on-the-fly or on a permanent basis.
In other words, you can modify the \s-1SQL\s0 syntax accepted as valid by the parser
and accepted as executable by the executer.  There are two methods for
extending the syntax \- 1) with \s-1SQL\s0 commands that can be issued directly in
SQL::Statement or form a \s-1DBD\s0 or 2) by subclassing SQL::Parser.
.PP
The following \s-1SQL\s0 commands modify the default \s-1SQL\s0 syntax:
.PP
.Vb 4
\&  CREATE/DROP FUNCTION
\&  CREATE/DROP KEYWORD
\&  CREATE/DROP TYPE
\&  CREATE/DROP OPERATOR
.Ve
.PP
A simple example would be a situation in which you have a table named
\&'\s-1TABLE\s0'.  Since table is an \s-1ANSI\s0 reserved key word, by default
SQL::Statement will produce an error when you attempt to create or
access it.  You could put the table name inside double quotes since
quoted identifiers can validly be reserved words, or you could rename
the table.  If neither of those are options, you would do this:
.PP
.Vb 1
\&  DROP KEYWORD table
.Ve
.PP
Once that statement is issued, the parser will no longer object to 'table' as
a table name.  Careful though, if you drop too many keywords you may confuse
the parser, especially keywords like \s-1FROM\s0 and \s-1WHERE\s0 that are central to
parsing the statement.
.PP
In the reverse situation, suppose you want to parse some \s-1SQL\s0 that defines a
column as type \s-1BIG_BLOB. \s0 Since '\s-1BIG_BLOB\s0' isn't a recognized \s-1ANSI\s0 data type,
an error will be produced by default.  To make the parser treat it as a valid
data type, you do this:
.PP
.Vb 1
\& CREATE TYPE big_blob
.Ve
.PP
Keywords and types are case-insensitive.
.PP
Suppose you are working with some \s-1SQL\s0 that contains the \fIcosh()\fR
function (an Oracle function for hyperbolic cosine, whatever that is
:\-).  The \fIcosh()\fR function is not currently implemented in
SQL::Statement so the parser would die with an error.  But you can
easily trick the parser into accepting the function:
.PP
.Vb 1
\& CREATE FUNCTION cosh
.Ve
.PP
Once the parser has read that \s-1CREATE FUNCTION\s0 statement, it will no longer
object to the use of the \fIcosh()\fR function in \s-1SQL\s0 statements.
.PP
If your only interest is in parsing \s-1SQL\s0 statements, then \f(CW\*(C`CREATE FUNCTION
cosh\*(C'\fR is sufficient.  But if you actually want to be able to use the \fIcosh()\fR
function in executable statements, you need to supply a perl subroutine
that performs the \fIcosh()\fR function:
.PP
.Vb 1
\&  CREATE FUNCTION cosh AS perl_subroutine_name
.Ve
.PP
The subroutine name can refer to a subroutine in your current script, or to
a subroutine in any available package.  See SQL::Statement::Functions for
details of how to create and load functions.
.PP
Functions can be used as  predicates in search clauses, for example:
.PP
.Vb 1
\& SELECT * FROM x WHERE c1=7 AND SOUNDEX(c3,\*(Aqfoo\*(Aq) AND c8=\*(Aqbar\*(Aq
.Ve
.PP
In the \s-1SQL\s0 above, the \f(CW\*(C`SOUNDEX()\*(C'\fR function full predicate \- it plays the
same role as \f(CW\*(C`c1=7 or c8=\*(Aqbar\*(Aq\*(C'\fR.
.PP
Functions can also serve as predicate operators.  An operator, unlike a
full predicate, has something on the left and right sides.  An equal sign
is an operator, so is \s-1LIKE. \s0 If you really want to you can get the parser
to not accept \s-1LIKE\s0 as an operator with
.PP
.Vb 1
\& DROP OPERATOR like
.Ve
.PP
Or, you can invent your own operator.  Suppose you have an operator
\&\f(CW\*(C`REVERSE_OF\*(C'\fR that is true if the string on its left side when reversed
is equal to the string on the right side:
.PP
.Vb 2
\&  CREATE OPERATOR reverse_of
\&  SELECT * FROM x WHERE c1=7 AND c3 REVERSE_OF \*(Aqfoo\*(Aq
.Ve
.PP
The operator could just as well have been written as a function:
.PP
.Vb 2
\&  CREATE FUNCTION reverse_of
\&  SELECT * FROM x WHERE c1=7 AND REVERSE_OF(c3,\*(Aqfoo\*(Aq)
.Ve
.PP
Like functions, if you want to actually execute a user-defined operator
as distinct from just parsing it, you need to assign the operator to a
perl subroutine.  This is done exactly like assigning functions:
.PP
.Vb 1
\&  CREATE OPERATOR reverse_of AS perl_subroutine_name
.Ve
.SH "Extending SQL syntax using subclasses"
.IX Header "Extending SQL syntax using subclasses"
In addition to using the \s-1SQL\s0 shown above to modify the parser's behavior,
you can also extend the \s-1SQL\s0 syntax by subclassing SQL::Parser.
See SQL::Parser for details.
.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.