1SQL::Statement::Syntax(U3s)er Contributed Perl DocumentatSiQoLn::Statement::Syntax(3)
2
3
4

NAME

6       SQL::Statement::Syntax - documentation of SQL::Statement's SQL Syntax
7

SYNOPSIS

9       See SQL::Statement for usage.
10

DESCRIPTION

12       The SQL::Statement module can be used either from a DBI driver like
13       DBD::CSV or directly.  The syntax below applies to both situations.  In
14       the case of DBDs, each DBD can implement its own sub-dialect so be sure
15       to check the DBD documentation also.
16
17       SQL::Statement is meant primarily as a base class for DBD drivers and
18       as such concentrates on a small but useful subset of SQL.  It does
19       *not* in any way pretend to be a complete SQL parser for all dialects
20       of SQL.  The module will continue to add new supported syntax, and
21       users may also extend the syntax (see "#Extending the SQL syntax").
22

USAGE

24   Default Supported SQL syntax - Summary
25       SQL Statements
26
27          CALL <function>
28          CREATE [TEMP] TABLE <table> <column_def_clause>
29          CREATE [TEMP] TABLE <table> AS <select statement>
30          CREATE [TEMP] TABLE <table> AS IMPORT()
31          CREATE FUNCTION <user_defined_function> [ NAME <perl_subroutine> ]
32          CREATE KEYWORD  <user_defined_keyword>  [ NAME <perl_subroutine> ]
33          CREATE OPERATOR <user_defined_operator> [ NAME <perl_subroutine> ]
34          CREATE TYPE     <user_defined_type>     [ NAME <perl_subroutine> ]
35          DELETE FROM <table> [<where_clause>]
36          DROP TABLE [IF EXISTS] <table>
37          DROP FUNCTION <function>
38          DROP KEYWORD  <keyword>
39          DROP OPERATOR <operator>
40          DROP TYPE     <type>
41          INSERT [INTO] <table> [<column_list>] VALUES <value_list>
42          LOAD <user_defined_functions_module>
43          SELECT <function>
44          SELECT <select_clause>
45                 <from_clause>
46                 [<where_clause>]
47                 [ ORDER BY ocol1 [ASC|DESC], ... ocolN [ASC|DESC]] ]
48                 [ GROUP BY gcol1 [, ... gcolN] ]
49                 [ LIMIT [start,] length ]
50          UPDATE <table> SET <set_clause> [<where_clause>]
51
52       Explicit Join Qualifiers
53
54          NATURAL, INNER, OUTER, LEFT, RIGHT, FULL
55
56       Built-in Functions
57
58          * Aggregate : MIN, MAX, AVG, SUM, COUNT
59          * Date/Time : CURRENT_DATE, CURDATE, CURRENT_TIME, CURTIME, CURRENT_TIMESTAMP, NOW,
60                        UNIX_TIMESTAMP
61          * String    : ASCII, CHAR, BIT_LENGTH, CHARACTER_LENGTH, CHAR_LENGTH, COALESCE,
62                        NVL, IFNULL, CONV, CONCAT, DECODE, HEX, OCT, BIN, INSERT, LEFT, RIGHT,
63                        LOCATE, POSITION, LOWER, UPPER, LCASE, UCASE, LTRIM, RTRIM, OCTET_LENGTH,
64                        REGEX, REPEAT, REPLACE, SOUNDEX, SPACE, SUBSTITUTE, SUBSTRING, SUBSTR,
65                        TRANSLATE, TRIM, UNHEX
66          * Numeric   : ABS, CEILING, CEIL, FLOOR, ROUND, EXP, LOG, LN, LOG10, MOD, POWER,
67                        RAND, SIGN, SQRT, TRUNCATE, TRUNC
68          * Trig      : ACOS, ACOSEC, ACOSECH, ACOSH, ACOT, ACOTAN, ACOTANH, ACOTH, ACSC,
69                        ACSCH, ASEC, ASECH, ASIN, ASINH, ATAN, ATAN2, ATANH, COS, COSEC,
70                        COSECH, COSH, COT, COTAN, COTANH, COTH, CSC, CSCH, DEG2DEG, DEG2GRAD,
71                        DEG2RAD, DEGREES, GRAD2DEG, GRAD2GRAD, GRAD2RAD, PI, RAD2DEG, RAD2GRAD,
72                        RAD2RAD, RADIANS, SEC, SECH, SIN, SINH, TAN, TANH
73          * System    : DBNAME, USERNAME, USER
74
75       Special Utility Functions
76
77         * IMPORT  - imports a table from an external RDBMS or perl structure
78         * RUN     - prepares and executes statements in a file of SQL statements
79
80       Operators and Predicates
81
82          = , <> , < , > , <= , >= , IS [NOT] (NULL|TRUE|FALSE) , LIKE , CLIKE , IN , BETWEEN
83
84       Identifiers and Aliases
85
86          * regular identifiers are case insensitive (though see note on table names)
87          * delimited identifiers (inside double quotes) are case sensitive
88          * column and table aliases are supported
89
90       Concatenation
91
92          * use either ANSI SQL || or the CONCAT() function
93          * e.g. these are the same:  {foo || bar} {CONCAT(foo,bar)}
94
95       Comments
96
97          * comments must occur before or after statements, cannot be embedded
98          * SQL-style single line -- and C-style multi-line /* */ comments are supported
99
100       NULLs
101
102          * currently NULLs and empty strings are identical in non-ANSI dialect.
103          * use {col IS NULL} to find NULLs, not {col=''} (though both may work depending on dialect)
104
105       See below for further details.
106
107   Syntax - Details
108       CREATE TABLE
109
110       Creates permanent and in-memory tables.
111
112        CREATE [TEMP] TABLE <table_name> ( <column_definitions> )
113        CREATE [TEMP] TABLE <table_name> AS <select statement>
114        CREATE [TEMP] TABLE <table_name> AS IMPORT()
115
116       Column definitions are standard SQL column names, types, and
117       constraints, see "Column Definitions".
118
119         # create a permanent table
120         #
121         $dbh->do("CREATE TABLE qux (id INT PRIMARY KEY,word VARCHAR(30))");
122
123       The "AS SELECT" clause creates and populates the new table using the
124       data and column structure specified in the select statement.
125
126         # create and populate a table from a query to two other tables
127         #
128         $dbh->do("CREATE TABLE qux AS SELECT id,word FROM foo NATURAL JOIN bar");
129
130       If the optional keyword TEMP (or its synonym TEMPORARY) is used, the
131       table will be an in-memory table, available  for the life of the
132       current database handle or until  a DROP TABLE command is issued.
133
134         # create a temporary table
135         #
136         $dbh->do("CREATE TEMP TABLE qux (id INT PRIMARY KEY,word VARCHAR(30))");
137
138       TEMP tables can be modified with SQL commands but the updates are not
139       automatically reflected back to any permanent tables they may be
140       associated with.  To save a TEMP table - just use an AS SELECT clause:
141
142        $dbh = DBI->connect( 'dbi:CSV:' );
143        $dbh->do("CREATE TEMP TABLE qux_temp AS (id INT, word VARCHAR(30))");
144        #
145        # ... modify qux_temp with INSERT, UPDATE, DELETE statements, then save it
146        #
147        $dbh->do("CREATE TABLE qux_permanent AS SELECT * FROM qux_temp");
148
149       Tables, both temporary and permanent may also be created directly from
150       perl arrayrefs and from heterogeneous queries to any DBI accessible
151       data source, see the IMPORT() function.
152
153        CREATE [ {LOCAL|GLOBAL} TEMPORARY ] TABLE $table
154               (
155                  $col_1 $col_type1 $col_constraints1,
156                  ...,
157                  $col_N $col_typeN $col_constraintsN,
158               )
159               [ ON COMMIT {DELETE|PRESERVE} ROWS ]
160
161            * col_type must be a valid data type as defined in the
162              "valid_data_types" section of the dialect file for the
163              current dialect
164
165            * col_constraints may be "PRIMARY KEY" or one or both of
166              "UNIQUE" and/or "NOT NULL"
167
168            * IMPORTANT NOTE: temporary tables, data types and column
169              constraints are checked for syntax violations but are
170              currently otherwise *IGNORED* -- they are recognized by
171              the parser, but not by the execution engine
172
173            * The following valid ANSI SQL92 options are not currently
174              supported: table constraints, named constraints, check
175              constraints, reference constraints, constraint
176              attributes, collations, default clauses, domain names as
177              data types
178
179       DROP TABLE
180
181        DROP TABLE $table [ RESTRICT | CASCADE ]
182
183            * IMPORTANT NOTE: drop behavior (cascade or restrict) is
184              checked for valid syntax but is otherwise *IGNORED* -- it
185              is recognized by the parser, but not by the execution
186              engine
187
188       INSERT INTO
189
190        INSERT INTO $table [ ( $col1, ..., $colN ) ] VALUES ( $val1, ... $valN )
191
192            * default values are not currently supported
193            * inserting from a subquery is not currently supported
194
195       DELETE FROM
196
197        DELETE FROM $table [ WHERE search_condition ]
198
199            * see "search_condition" below
200
201       UPDATE
202
203        UPDATE $table SET $col1 = $val1, ... $colN = $valN [ WHERE search_condition ]
204
205            * default values are not currently supported
206            * see "search_condition" below
207
208       SELECT
209
210             SELECT select_clause
211               FROM from_clause
212            [ WHERE search_condition ]
213         [ ORDER BY $ocol1 [ASC|DESC], ... $ocolN [ASC|DESC] ]
214            [ LIMIT [start,] length ]
215
216             * select clause ::=
217                    [DISTINCT|ALL] *
218                  | [DISTINCT|ALL] col1 [,col2, ... colN]
219                  | set_function1 [,set_function2, ... set_functionN]
220
221             * set function ::=
222                    COUNT ( [ALL] * )
223                  | COUNT | MIN | MAX | AVG | SUM ( [DISTINCT|ALL] col_name )
224
225             * from clause ::=
226                    table1 [, table2, ... tableN]
227                  | table1 NATURAL [join_type] JOIN table2
228                  | table1 [join_type] table2 USING (col1,col2, ... colN)
229                  | table1 [join_type] JOIN table2 ON table1.colA = table2.colB
230
231             * join type ::=
232                    INNER
233                  | [OUTER] LEFT | RIGHT | FULL
234
235             * if join_type is not specified, INNER is the default
236             * if DISTINCT or ALL is not specified, ALL is the default
237             * if start position is omitted from LIMIT clause, position 0 is
238               the default
239             * ON clauses may only contain equal comparisons and AND combiners
240             * self-joins are not currently supported
241             * if implicit joins are used, the WHERE clause must contain
242               an equijoin condition for each table
243             * multiple ANSI joins are not supported; use implicit joins for these
244             * this also means that combinations of INNER and non-INNER joins are
245               not supported
246
247       SEARCH CONDITION
248
249              [NOT] $val1 $op1 $val1 [ ... AND|OR $valN $opN $valN ]
250
251       OPERATORS
252
253              $op  = |  <> |  < | > | <= | >=
254                     | IS [NOT] NULL | IS [NOT] TRUE | IS [NOT] FALSE
255                     | LIKE | CLIKE | BETWEEN | IN
256
257         The "CLIKE" operator works exactly the same as the "LIKE"
258         operator, but is case insensitive.  For example:
259
260             WHERE foo LIKE 'bar%'   # succeeds if foo is "barbaz"
261                                     # fails if foo is "BARBAZ" or "Barbaz"
262
263             WHERE foo CLIKE 'bar%'  # succeeds for "barbaz", "Barbaz", and "BARBAZ"
264
265       BUILT-IN AND USER-DEFINED FUNCTIONS
266
267       There are many built-in functions and you can also create your own new
268       functions from perl subroutines.  See SQL::Statement::Functions for
269       documentation of functions.
270
271       Identifiers (table & column names)
272
273       Regular identifiers (table and column names *without* quotes around
274       them) are case INSENSITIVE so column foo, fOo, FOO all refer to the
275       same column.  Internally they are used in their lower case
276       representation, so do not rely on SQL::Statement retaining your case.
277
278       Delimited identifiers (table and column names *with* quotes around
279       them) are case SENSITIVE so column "foo", "fOo", "FOO" each refer to
280       different columns.
281
282       A delimited identifier is *never* equal to a regular identifier (so
283       "foo" and foo are two different columns).  But do not do that :-).
284
285       Remember thought that, in DBD::CSV if table names are used directly as
286       file names, the case sensitivity depends on the OS e.g. on Windows
287       files named foo, FOO, and fOo are the same as each other while on Unix
288       they are different.
289
290       Special Utility SQL Functions
291
292       IMPORT()
293
294       Imports the data and structure of a table from an external data source
295       into a permanent or temporary table.
296
297        $dbh->do("CREATE TABLE qux AS IMPORT(?)",{},$oracle_sth);
298
299        $dbh->do("CREATE TABLE qux AS IMPORT(?)",{},$AoA);
300
301        $dbh->do("CREATE TABLE qux AS IMPORT(?)",{},$AoH);
302
303       IMPORT() can also be used anywhere that table_names can:
304
305        $sth=$dbh->prepare("
306           SELECT * FROM IMPORT(?) AS T1 NATURAL JOIN IMPORT(?) AS T2 WHERE T1.id ...
307        ");
308        $sth->execute( $pg_sth, $mysql_sth );
309
310       The IMPORT() function imports the data and structure of a table from an
311       external data source.  The IMPORT() function is always used with a
312       placeholder parameter which may be 1) a prepared and executed statement
313       handle for any DBI accessible data source;  or 2) an AoA whose first
314       row is column names and whose succeeding rows are data 3) an AoH.
315
316       The IMPORT() function may be used in the AS clause of a CREATE
317       statement, and in the FROM clause of any statement.  When used in a
318       FROM clause, it should be used with a column alias e.g. SELECT * FROM
319       IMPORT(?) AS TableA WHERE ...
320
321       You can also write your own IMPORT() functions to treat anything as a
322       data source.  See User-Defined Function in SQL::Statement::Functions.
323
324       Examples:
325
326        # create a CSV file from an Oracle query
327        #
328        $dbh = DBI->connect('dbi:CSV:');
329        $oracle_sth = $oracle_dbh->prepare($any_oracle_query);
330        $oracle_sth->execute(@params);
331        $dbh->do("CREATE TABLE qux AS IMPORT(?)",{},$oracle_sth);
332
333        # create an in-memory table from an AoA
334        #
335        $dbh      = DBI->connect( 'dbi:File:' );
336        $arrayref = [['id','word'],[1,'foo'],[2,'bar'],];
337        $dbh->do("CREATE TEMP TABLE qux AS IMPORT(?)",{},$arrayref);
338
339        # query a join of a PostgreSQL table and a MySQL table
340        #
341        $dbh        = DBI->connect( 'dbi:File:' );
342        $pg_dbh     = DBI->connect( ... DBD::pg connect params );
343        $mysql_dbh  = DBI->connect( ... DBD::mysql connect params );
344        $pg_sth     = $pg_dbh->prepare( ... any pg query );
345        $pg_sth     = $pg_dbh->prepare( ... any mysql query );
346        #
347        $sth=$dbh->prepare("
348           SELECT * FROM IMPORT(?) AS T1 NATURAL JOIN IMPORT(?) AS T2
349        ");
350        $sth->execute( $pg_sth, $mysql_sth );
351
352       RUN()
353
354       Run SQL statements from a user supplied file.  Please Note: this
355       function is experimental, please let me know if you have problems.
356
357        RUN( sql_file )
358
359       If the file contains non-SELECT statements such as CREATE and INSERT,
360       use the RUN() function with $dbh->do().  For example, this prepares and
361       executes all of the SQL statements in a file called "populate.sql":
362
363        $dbh->do(" CALL RUN( 'populate.sql') ");
364
365       If the file contains SELECT statements, the RUN() function may be used
366       anywhere a table name may be used, for example, if you have a file
367       called "query.sql" containing "SELECT * FROM Employee", then these two
368       lines are exactly the same:
369
370        my $sth = $dbh->prepare(" SELECT * FROM Employee ");
371
372        my $sth = $dbh->prepare(" SELECT * FROM RUN( 'query.sql' ) ");
373
374       If the file contains a statement with placeholders, the values for the
375       placeholders can be passed in the call to $sth->execute() as normal. If
376       the query.sql file contains "SELECT id,name FROM x WHERE id=?", then
377       these two are the same:
378
379        my $sth = $dbh->prepare(" SELECT id,name FROM x WHERE id=?");
380        $sth->execute(64);
381
382        my $sth = $dbh->prepare(" SELECT * FROM RUN( 'query.sql' ) ");
383        $sth->execute(64);
384
385       Note This function assumes that the SQL statements in the file are
386       separated by a semi-colon+newline combination (/;\n/).  If you wish to
387       use different separators or import SQL from a different source, just
388       override the RUN() function with your own user-defined-function.
389
390   Further details
391       Integers
392       Reals   Syntax obvious
393
394       Strings Surrounded by either single quotes; some characters need to be
395               escaped with a backslash, in particular the backslash itself
396               (\\), the NULL byte (\0), Line feeds (\n), Carriage return
397               (\r), and the quotes (\').
398
399               Note: Quoting "Strings" using double quotes are recognized as
400               quoted identifiers (column or table names).
401
402       Parameters
403               Parameters represent scalar values, like Integers, Reals and
404               Strings do. However, their values are read inside Execute() and
405               not inside Prepare(). Parameters are represented by question
406               marks (?).
407
408       Identifiers
409               Identifiers are table or column names. Syntactically they
410               consist of alphabetic characters, followed by an arbitrary
411               number of alphanumeric characters. Identifiers like SELECT,
412               INSERT, INTO, ORDER, BY, WHERE, ... are forbidden and reserved
413               for other tokens.  Identifiers are always compared case-
414               insensitively, i.e. "select foo from bar" will be evaluated the
415               same as "SELECT FOO FROM BAR" ("FOO" will be evaluated as
416               "foo", similar for "BAR").
417
418               Since SQL::Statement is internally using lower cased
419               identifiers (unquoted), everytime a wildcard is used, the
420               delivered names of the identifiers are lower cased.
421

Extending SQL syntax using SQL

423       The Supported SQL syntax shown above is the default for SQL::Statement
424       but it can be extended (or contracted) either on-the-fly or on a
425       permanent basis.  In other words, you can modify the SQL syntax
426       accepted as valid by the parser and accepted as executable by the
427       executer.  There are two methods for extending the syntax - 1) with SQL
428       commands that can be issued directly in SQL::Statement or form a DBD or
429       2) by subclassing SQL::Parser.
430
431       The following SQL commands modify the default SQL syntax:
432
433         CREATE/DROP FUNCTION
434         CREATE/DROP KEYWORD
435         CREATE/DROP TYPE
436         CREATE/DROP OPERATOR
437
438       A simple example would be a situation in which you have a table named
439       'TABLE'.  Since table is an ANSI reserved key word, by default
440       SQL::Statement will produce an error when you attempt to create or
441       access it.  You could put the table name inside double quotes since
442       quoted identifiers can validly be reserved words, or you could rename
443       the table.  If neither of those are options, you would do this:
444
445         DROP KEYWORD table
446
447       Once that statement is issued, the parser will no longer object to
448       'table' as a table name.  Careful though, if you drop too many keywords
449       you may confuse the parser, especially keywords like FROM and WHERE
450       that are central to parsing the statement.
451
452       In the reverse situation, suppose you want to parse some SQL that
453       defines a column as type BIG_BLOB.  Since 'BIG_BLOB' is not a
454       recognized ANSI data type, an error will be produced by default.  To
455       make the parser treat it as a valid data type, you do this:
456
457        CREATE TYPE big_blob
458
459       Keywords and types are case-insensitive.
460
461       Suppose you are working with some SQL that contains the cosh() function
462       (an Oracle function for hyperbolic cosine, whatever that is :-).  The
463       cosh() function is not currently implemented in SQL::Statement so the
464       parser would die with an error.  But you can easily trick the parser
465       into accepting the function:
466
467        CREATE FUNCTION cosh
468
469       Once the parser has read that CREATE FUNCTION statement, it will no
470       longer object to the use of the cosh() function in SQL statements.
471
472       If your only interest is in parsing SQL statements, then "CREATE
473       FUNCTION cosh" is sufficient.  But if you actually want to be able to
474       use the cosh() function in executable statements, you need to supply a
475       perl subroutine that performs the cosh() function:
476
477         CREATE FUNCTION cosh AS perl_subroutine_name
478
479       The subroutine name can refer to a subroutine in your current script,
480       or to a subroutine in any available package.  See
481       SQL::Statement::Functions for details of how to create and load
482       functions.
483
484       Functions can be used as  predicates in search clauses, for example:
485
486        SELECT * FROM x WHERE c1=7 AND SOUNDEX(c3,'foo') AND c8='bar'
487
488       In the SQL above, the "SOUNDEX()" function full predicate - it plays
489       the same role as "c1=7 or c8='bar'".
490
491       Functions can also serve as predicate operators.  An operator, unlike a
492       full predicate, has something on the left and right sides.  An equal
493       sign is an operator, so is LIKE.  If you really want to you can get the
494       parser to not accept LIKE as an operator with
495
496        DROP OPERATOR like
497
498       Or, you can invent your own operator.  Suppose you have an operator
499       "REVERSE_OF" that is true if the string on its left side when reversed
500       is equal to the string on the right side:
501
502         CREATE OPERATOR reverse_of
503         SELECT * FROM x WHERE c1=7 AND c3 REVERSE_OF 'foo'
504
505       The operator could just as well have been written as a function:
506
507         CREATE FUNCTION reverse_of
508         SELECT * FROM x WHERE c1=7 AND REVERSE_OF(c3,'foo')
509
510       Like functions, if you want to actually execute a user-defined operator
511       as distinct from just parsing it, you need to assign the operator to a
512       perl subroutine.  This is done exactly like assigning functions:
513
514         CREATE OPERATOR reverse_of AS perl_subroutine_name
515

Extending SQL syntax using subclasses

517       In addition to using the SQL shown above to modify the parser's
518       behavior, you can also extend the SQL syntax by subclassing
519       SQL::Parser.  See SQL::Parser for details.
520
522       Copyright (c) 2005, Jeff Zucker <jzuckerATcpan.org>, all rights
523       reserved.  Copyright (c) 2009-2020, Jens Rehsack <rehsackATcpan.org>,
524       all rights reserved.
525
526       This document may be freely modified and distributed under the same
527       terms as Perl itself.
528
529
530
531perl v5.32.1                      2021-01-27         SQL::Statement::Syntax(3)
Impressum