1SQL::Statement::Syntax(U3s)er Contributed Perl DocumentatSiQoLn::Statement::Syntax(3)
2
6 SQL::Statement::Syntax - documentation of SQL::Statement's SQL Syntax
7
9 See SQL::Statement for usage.
10
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 SQL::Statement is meant primarly as a base class for DBD drivers and as
18 such concentrates on a small but useful subset of SQL. It does *not*
19 in any way pretend to be a complete SQL parser for all dialects of SQL.
20 The module will continue to add new supported syntax, and users may
21 also extend the syntax (see "#Extending the SQL syntax").
22
24 Default Supported SQL syntax - Summary
25 SQL Statements
26 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 Explicit Join Qualifiers
53 NATURAL, INNER, OUTER, LEFT, RIGHT, FULL
55 Built-in Functions
57 * Aggregate : MIN, MAX, AVG, SUM, COUNT
59 * Date/Time : CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
60 * String : CHAR_LENGTH, CONCAT, COALESCE, DECODE, LOWER, POSITION,
61 REGEX, REPLACE, SOUNDEX, SUBSTRING, TRIM, UPPER
62 Special Utility Functions
64 * IMPORT - imports a table from an external RDBMS or perl structure
66 * RUN - prepares and executes statements in a file of SQL statements
67 Operators and Predicates
69 = , <> , < , > , <= , >= , IS [NOT] (NULL|TRUE|FALSE) , LIKE , CLIKE , IN , BETWEEN
71 Identifiers and Aliases
73 * regular identifiers are case insensitive (though see note on table names)
75 * delimited identifiers (inside double quotes) are case sensitive
76 * column and table aliases are supported
77 Concatenation
79 * use either ANSI SQL || or the CONCAT() function
81 * e.g. these are the same: {foo || bar} {CONCAT(foo,bar)}
82 Comments
84 * comments must occur before or after statements, cannot be embedded
86 * SQL-style single line -- and C-style multi-line /* */ comments are supported
87 NULLs
89 * currently NULLs and empty strings are identical in non-ANSI dialect.
91 * use {col IS NULL} to find NULLs, not {col=''} (though both may work depending on dialect)
92 See below for further details.
94 Syntax - Details
96 CREATE TABLE
97 Creates permanent and in-memory tables.
99 CREATE [TEMP] TABLE <table_name> ( <column_definitions> )
101 CREATE [TEMP] TABLE <table_name> AS <select statement>
102 CREATE [TEMP] TABLE <table_name> AS IMPORT()
103 Column definitions are standard SQL column names, types, and
105 constraints, see "Column Definitions".
106 # create a permanent table
108 #
109 $dbh->do("CREATE TABLE qux (id INT PRIMARY KEY,word VARCHAR(30))");
110 The "AS SELECT" clause creates and populates the new table using the
112 data and column structure specified in the select statement.
113 # create and populate a table from a query to two other tables
115 #
116 $dbh->do("CREATE TABLE qux AS SELECT id,word FROM foo NATURAL JOIN bar");
117 If the optional keyword TEMP (or its synonym TEMPORARY) is used, the
119 table will be an in-memory table, available for the life of the
120 current database handle or until a DROP TABLE command is issued.
121 # create a temporary table
123 #
124 $dbh->do("CREATE TEMP TABLE qux (id INT PRIMARY KEY,word VARCHAR(30))");
125 TEMP tables can be modified with SQL commands but the updates are not
127 automatically reflected back to any permanent tables they may be
128 associated with. To save a TEMP table - just use an AS SELECT clause:
129 $dbh = DBI->connect( 'dbi:CSV:' );
131 $dbh->do("CREATE TEMP TABLE qux_temp AS (id INT, word VARCHAR(30))");
132 #
133 # ... modify qux_temp with INSERT, UPDATE, DELETE statements, then save it
134 #
135 $dbh->do("CREATE TABLE qux_permanent AS SELECT * FROM qux_temp");
136 Tables, both temporary and permanent may also be created directly from
138 perl arrayrefs and from heterogeneous queries to any DBI accessible
139 data source, see the IMPORT() function.
140 CREATE [ {LOCAL|GLOBAL} TEMPORARY ] TABLE $table
142 (
143 $col_1 $col_type1 $col_constraints1,
144 ...,
145 $col_N $col_typeN $col_constraintsN,
146 )
147 [ ON COMMIT {DELETE|PRESERVE} ROWS ]
148 * col_type must be a valid data type as defined in the
150 "valid_data_types" section of the dialect file for the
151 current dialect
152 * col_constraints may be "PRIMARY KEY" or one or both of
154 "UNIQUE" and/or "NOT NULL"
155 * IMPORTANT NOTE: temporary tables, data types and column
157 constraints are checked for syntax violations but are
158 currently otherwise *IGNORED* -- they are recognized by
159 the parser, but not by the execution engine
160 * The following valid ANSI SQL92 options are not currently
162 supported: table constraints, named constraints, check
163 constraints, reference constraints, constraint
164 attributes, collations, default clauses, domain names as
165 data types
166 DROP TABLE
168 DROP TABLE $table [ RESTRICT | CASCADE ]
170 * IMPORTANT NOTE: drop behavior (cascade or restrict) is
172 checked for valid syntax but is otherwise *IGNORED* -- it
173 is recognized by the parser, but not by the execution
174 engine
175 INSERT INTO
177 INSERT INTO $table [ ( $col1, ..., $colN ) ] VALUES ( $val1, ... $valN )
179 * default values are not currently supported
181 * inserting from a subquery is not currently supported
182 DELETE FROM
184 DELETE FROM $table [ WHERE search_condition ]
186 * see "search_condition" below
188 UPDATE
190 UPDATE $table SET $col1 = $val1, ... $colN = $valN [ WHERE search_condition ]
192 * default values are not currently supported
194 * see "search_condition" below
195 SELECT
197 SELECT select_clause
199 FROM from_clause
200 [ WHERE search_condition ]
201 [ ORDER BY $ocol1 [ASC|DESC], ... $ocolN [ASC|DESC] ]
202 [ LIMIT [start,] length ]
203 * select clause ::=
205 [DISTINCT|ALL] *
206 | [DISTINCT|ALL] col1 [,col2, ... colN]
207 | set_function1 [,set_function2, ... set_functionN]
208 * set function ::=
210 COUNT ( [ALL] * )
211 | COUNT | MIN | MAX | AVG | SUM ( [DISTINCT|ALL] col_name )
212 * from clause ::=
214 table1 [, table2, ... tableN]
215 | table1 NATURAL [join_type] JOIN table2
216 | table1 [join_type] table2 USING (col1,col2, ... colN)
217 | table1 [join_type] JOIN table2 ON table1.colA = table2.colB
218 * join type ::=
220 INNER
221 | [OUTER] LEFT | RIGHT | FULL
222 * if join_type is not specified, INNER is the default
224 * if DISTINCT or ALL is not specified, ALL is the default
225 * if start position is omitted from LIMIT clause, position 0 is
226 the default
227 * ON clauses may only contain equal comparisons and AND combiners
228 * self-joins are not currently supported
229 * if implicit joins are used, the WHERE clause must contain
230 an equijoin condition for each table
231 SEARCH CONDITION
233 [NOT] $val1 $op1 $val1 [ ... AND|OR $valN $opN $valN ]
235 OPERATORS
237 $op = | <> | < | > | <= | >=
239 | IS [NOT] NULL | IS [NOT] TRUE | IS [NOT] FALSE
240 | LIKE | CLIKE | BETWEEN | IN
241 The "CLIKE" operator works exactly the same as the "LIKE"
243 operator, but is case insensitive. For example:
244 WHERE foo LIKE 'bar%' # succeeds if foo is "barbaz"
246 # fails if foo is "BARBAZ" or "Barbaz"
247 WHERE foo CLIKE 'bar%' # succeeds for "barbaz", "Barbaz", and "BARBAZ"
249 BUILT-IN AND USER-DEFINED FUNCTIONS
251 There are many built-in functions and you can also create your own new
253 functions from perl subroutines. See SQL::Statement::Functions for
254 documentation of functions.
255 Identifiers (table & column names)
257 Regular identifiers (table and column names *without* quotes around
259 them) are case INSENSITIVE so column foo, fOo, FOO all refer to the
260 same column. Internally they are used in their lower case
261 representation, so do not rely on SQL::Statement retaining your case.
262 Delimited identifiers (table and column names *with* quotes around
264 them) are case SENSITIVE so column "foo", "fOo", "FOO" each refer to
265 different columns.
266 A delimited identifier is *never* equal to a regular identifer (so
268 "foo" and foo are two different columns). But don't do that :-).
269 Remember thought that, in DBD::CSV if table names are used directly as
271 file names, the case sensitivity depends on the OS e.g. on Windows
272 files named foo, FOO, and fOo are the same as each other while on Unix
273 they are different.
274 Special Utility SQL Functions
276 IMPORT()
278 Imports the data and structure of a table from an external data source
280 into a permanent or temporary table.
281 $dbh->do("CREATE TABLE qux AS IMPORT(?)",{},$oracle_sth);
283 $dbh->do("CREATE TABLE qux AS IMPORT(?)",{},$AoA);
285 $dbh->do("CREATE TABLE qux AS IMPORT(?)",{},$AoH);
287 IMPORT() can also be used anywhere that table_names can:
289 $sth=$dbh->prepare("
291 SELECT * FROM IMPORT(?) AS T1 NATURAL JOIN IMPORT(?) AS T2 WHERE T1.id ...
292 ");
293 $sth->execute( $pg_sth, $mysql_sth );
294 The IMPORT() function imports the data and structure of a table from an
296 external data source. The IMPORT() function is always used with a
297 placeholder parameter which may be 1) a prepared and executed statement
298 handle for any DBI accessible data source; or 2) an AoA whose first
299 row is column names and whose succeeding rows are data 3) an AoH.
300 The IMPORT() function may be used in the AS clause of a CREATE
302 statement, and in the FROM clause of any statement. When used in a
303 FROM clause, it should be used with a column alias e.g. SELECT * FROM
304 IMPORT(?) AS TableA WHERE ...
305 You can also write your own IMPORT() functions to treat anything as a
307 data source. See User-Defined Function in SQL::Statement::Functions.
308 Examples:
310 # create a CSV file from an Oracle query
312 #
313 $dbh = DBI->connect('dbi:CSV:');
314 $oracle_sth = $oracle_dbh->prepare($any_oracle_query);
315 $oracle_sth->execute(@params);
316 $dbh->do("CREATE TABLE qux AS IMPORT(?)",{},$oracle_sth);
317 # create an in-memory table from an AoA
319 #
320 $dbh = DBI->connect( 'dbi:File:' );
321 $arrayref = [['id','word'],[1,'foo'],[2,'bar'],];
322 $dbh->do("CREATE TEMP TABLE qux AS IMPORT(?)",{},$arrayref);
323 # query a join of a PostgreSQL table and a MySQL table
325 #
326 $dbh = DBI->connect( 'dbi:File:' );
327 $pg_dbh = DBI->connect( ... DBD::pg connect params );
328 $mysql_dbh = DBI->connect( ... DBD::mysql connect params );
329 $pg_sth = $pg_dbh->prepare( ... any pg query );
330 $pg_sth = $pg_dbh->prepare( ... any mysql query );
331 #
332 $sth=$dbh->prepare("
333 SELECT * FROM IMPORT(?) AS T1 NATURAL JOIN IMPORT(?) AS T2
334 ");
335 $sth->execute( $pg_sth, $mysql_sth );
336 RUN()
338 Run SQL statements from a user supplied file. Please Note: this
340 function is experimental, please let me know if you have problems.
341 RUN( sql_file )
343 If the file contains non-SELECT statements such as CREATE and INSERT,
345 use the RUN() function with $dbh->do(). For example, this prepares and
346 executes all of the SQL statements in a file called "populate.sql":
347 $dbh->do(" CALL RUN( 'populate.sql') ");
349 If the file contains SELECT statements, the RUN() function may be used
351 anywhere a table name may be used, for example, if you have a file
352 called "query.sql" containing "SELECT * FROM Employee", then these two
353 lines are exactly the same:
354 my $sth = $dbh->prepare(" SELECT * FROM Employee ");
356 my $sth = $dbh->prepare(" SELECT * FROM RUN( 'query.sql' ) ");
358 If the file contains a statement with placeholders, the values for the
360 placehoders can be passed in the call to $sth->execute() as normal. If
361 the query.sql file contains "SELECT id,name FROM x WHERE id=?", then
362 these two are the same:
363 my $sth = $dbh->prepare(" SELECT id,name FROM x WHERE id=?");
365 $sth->execute(64);
366 my $sth = $dbh->prepare(" SELECT * FROM RUN( 'query.sql' ) ");
368 $sth->execute(64);
369 Note This function assumes that the SQL statements in the file are
371 separated by a semi-colon+newline combination (/;\n/). If you wish to
372 use different separators or import SQL from a different source, just
373 override the RUN() function with your own user-defined-function.
374 Further details
376 Integers
377 Reals Syntax obvious
378 Strings Surrounded by either single quotes; some characters need to be
380 escaped with a backslash, in particular the backslash itself
381 (\\), the NUL byte (\0), Line feeds (\n), Carriage return (\r),
382 and the quotes (\').
383 Note: Quoting "Strings" using double quotes are recognized as
385 quoted identifiers (column or table names).
386 Parameters
388 Parameters represent scalar values, like Integers, Reals and
389 Strings do. However, their values are read inside Execute() and
390 not inside Prepare(). Parameters are represented by question
391 marks (?).
392 Identifiers
394 Identifiers are table or column names. Syntactically they
395 consist of alphabetic characters, followed by an arbitrary
396 number of alphanumeric characters. Identifiers like SELECT,
397 INSERT, INTO, ORDER, BY, WHERE, ... are forbidden and reserved
398 for other tokens. Identifiers are always compared case-
399 insensitively, i.e. "select foo from bar" will be evaluated the
400 same as "SELECT FOO FROM BAR" ("FOO" will be evaluated as
401 "foo", similar for "BAR").
402 Since SQL::Statement is internally using lower cased
404 identifiers (unquoted), everytime a wildcard is used, the
405 delivered names of the identifiers are lower cased.
406
408 The Supported SQL syntax shown above is the default for SQL::Statement
409 but it can be extended (or contracted) either on-the-fly or on a
410 permanent basis. In other words, you can modify the SQL syntax
411 accepted as valid by the parser and accepted as executable by the
412 executer. There are two methods for extending the syntax - 1) with SQL
413 commands that can be issued directly in SQL::Statement or form a DBD or
414 2) by subclassing SQL::Parser.
415 The following SQL commands modify the default SQL syntax:
417 CREATE/DROP FUNCTION
419 CREATE/DROP KEYWORD
420 CREATE/DROP TYPE
421 CREATE/DROP OPERATOR
422 A simple example would be a situation in which you have a table named
424 'TABLE'. Since table is an ANSI reserved key word, by default
425 SQL::Statement will produce an error when you attempt to create or
426 access it. You could put the table name inside double quotes since
427 quoted identifiers can validly be reserved words, or you could rename
428 the table. If neither of those are options, you would do this:
429 DROP KEYWORD table
431 Once that statement is issued, the parser will no longer object to
433 'table' as a table name. Careful though, if you drop too many keywords
434 you may confuse the parser, especially keywords like FROM and WHERE
435 that are central to parsing the statement.
436 In the reverse situation, suppose you want to parse some SQL that
438 defines a column as type BIG_BLOB. Since 'BIG_BLOB' isn't a recognized
439 ANSI data type, an error will be produced by default. To make the
440 parser treat it as a valid data type, you do this:
441 CREATE TYPE big_blob
443 Keywords and types are case-insensitive.
445 Suppose you are working with some SQL that contains the cosh() function
447 (an Oracle function for hyperbolic cosine, whatever that is :-). The
448 cosh() function is not currently implemented in SQL::Statement so the
449 parser would die with an error. But you can easily trick the parser
450 into accepting the function:
451 CREATE FUNCTION cosh
453 Once the parser has read that CREATE FUNCTION statement, it will no
455 longer object to the use of the cosh() function in SQL statements.
456 If your only interest is in parsing SQL statements, then "CREATE
458 FUNCTION cosh" is sufficient. But if you actually want to be able to
459 use the cosh() function in executable statements, you need to supply a
460 perl subroutine that performs the cosh() function:
461 CREATE FUNCTION cosh AS perl_subroutine_name
463 The subroutine name can refer to a subroutine in your current script,
465 or to a subroutine in any available package. See
466 SQL::Statement::Functions for details of how to create and load
467 functions.
468 Functions can be used as predicates in search clauses, for example:
470 SELECT * FROM x WHERE c1=7 AND SOUNDEX(c3,'foo') AND c8='bar'
472 In the SQL above, the "SOUNDEX()" function full predicate - it plays
474 the same role as "c1=7 or c8='bar'".
475 Functions can also serve as predicate operators. An operator, unlike a
477 full predicate, has something on the left and right sides. An equal
478 sign is an operator, so is LIKE. If you really want to you can get the
479 parser to not accept LIKE as an operator with
480 DROP OPERATOR like
482 Or, you can invent your own operator. Suppose you have an operator
484 "REVERSE_OF" that is true if the string on its left side when reversed
485 is equal to the string on the right side:
486 CREATE OPERATOR reverse_of
488 SELECT * FROM x WHERE c1=7 AND c3 REVERSE_OF 'foo'
489 The operator could just as well have been written as a function:
491 CREATE FUNCTION reverse_of
493 SELECT * FROM x WHERE c1=7 AND REVERSE_OF(c3,'foo')
494 Like functions, if you want to actually execute a user-defined operator
496 as distinct from just parsing it, you need to assign the operator to a
497 perl subroutine. This is done exactly like assigning functions:
498 CREATE OPERATOR reverse_of AS perl_subroutine_name
500
502 In addition to using the SQL shown above to modify the parser's
503 behavior, you can also extend the SQL syntax by subclassing
504 SQL::Parser. See SQL::Parser for details.
505
507 Copyright (c) 2005, Jeff Zucker <jzuckerATcpan.org>, all rights
508 reserved. Copyright (c) 2009, Jens Rehsack <rehsackATcpan.org>, all
509 rights reserved.
510 This document may be freely modified and distributed under the same
512 terms as Perl itself.
513perl v5.12.1 2010-07-12 SQL::Statement::Syntax(3)