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 drive 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 documentations 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
27 CALL <function>
29 CREATE [TEMP] TABLE <table> <column_def_clause>
30 CREATE [TEMP] TABLE <table> AS <select statement>
31 CREATE [TEMP] TABLE <table> AS IMPORT()
32 CREATE FUNCTION <user_defined_function> [ NAME <perl_subroutine> ]
33 CREATE KEYWORD <user_defined_keyword> [ NAME <perl_subroutine> ]
34 CREATE OPERATOR <user_defined_operator> [ NAME <perl_subroutine> ]
35 CREATE TYPE <user_defined_type> [ NAME <perl_subroutine> ]
36 DELETE FROM <table> [<where_clause>]
37 DROP TABLE [IF EXISTS] <table>
38 DROP FUNCTION <function>
39 DROP KEYWORD <keyword>
40 DROP OPERATOR <operator>
41 DROP TYPE <type>
42 INSERT [INTO] <table> [<column_list>] VALUES <value_list>
43 LOAD <user_defined_functions_module>
44 SELECT <function>
45 SELECT <select_clause>
46 <from_clause>
47 [<where_clause>]
48 [ ORDER BY ocol1 [ASC⎪DESC], ... ocolN [ASC⎪DESC]] ]
49 [ GROUP BY gcol1 [, ... gcolN] ]
50 [ LIMIT [start,] length ]
51 UPDATE <table> SET <set_clause> [<where_clause>]
52 Explict Join Qualifiers
54 NATURAL, INNER, OUTER, LEFT, RIGHT, FULL
56 Built-in Functions
58 * Aggregate : MIN, MAX, AVG, SUM, COUNT
60 * Date/Time : CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
61 * String : CHAR_LENGTH, CONCAT, COALESCE, DECODE, LOWER, POSITION,
62 REGEX, REPLACE, SOUNDEX, SUBSTRING, TRIM, UPPER
63 Special Utility Functions
65 * IMPORT - imports a table from an external RDBMS or perl structure
67 * RUN - prepares & executes statements in a file of SQL statements
68 Operators and Predicates
70 = , <> , < , > , <= , >= , IS [NOT] NULL , LIKE , CLIKE , IN , BETWEEN
72 Identifiers and Aliases
74 * regular identifiers are case insensitive (though see note on table names)
76 * delimited identifiers (inside double quotes) are case sensitive
77 * column and table aliases are supported
78 Concatenation
80 * use either ANSI SQL ⎪⎪ or the CONCAT() function
82 * e.g. these are the same: {foo ⎪⎪ bar} {CONCAT(foo,bar)}
83 Comments
85 * comments must occur before or after statements, can't be embedded
87 * SQL-style single line -- and C-style multi-line /* */ comments are supported
88 NULLs
90 * currently NULLs and empty strings are identical, but this will change
92 * use {col IS NULL} to find NULLs, not {col=''} (though both currently work)
93 See below for further details.
95 Syntax - Details
97 CREATE TABLE
99 Creates permanenet and in-memory tables.
101 CREATE [TEMP] TABLE <table_name> ( <column_definitions> )
103 CREATE [TEMP] TABLE <table_name> AS <select statement>
104 CREATE [TEMP] TABLE <table_name> AS IMPORT()
105 Column definitions are standard SQL column names, types, and con‐
107 straints, see "Column Definitions".
108 # create a permanent table
110 #
111 $dbh->do("CREATE TABLE qux (id INT PRIMARY KEY,word VARCHAR(30))");
112 The "AS SELECT" clause creates and populates the new table using the
114 data and column structure specified in the select statement.
115 # create and populate a table from a query to two other tables
117 #
118 $dbh->do("CREATE TABLE qux AS SELECT id,word FROM foo NATURAL JOIN bar");
119 If the optional keyword TEMP (or its synonym TEMPORARY) is used, the
121 table will be an in-memory table, available for the life of the cur‐
122 rent database handle or until a DROP TABLE command is issued.
123 # create a temporary table
125 #
126 $dbh->do("CREATE TEMP TABLE qux (id INT PRIMARY KEY,word VARCHAR(30))");
127 TEMP tables can be modified with SQL commands but the updates are not
129 automatically reflected back to any permanent tables they may be asso‐
130 ciated with. To save a TEMP table - just use an AS SELECT clause:
131 $dbh = DBI->connect( 'dbi:CSV:' );
133 $dbh->do("CREATE TEMP TABLE qux_temp AS (id INT, word VARCHAR(30))");
134 #
135 # ... modify qux_temp with INSERT, UPDATE, DELETE statements, then save it
136 #
137 $dbh->do("CREATE TABLE qux_permanent AS SELECT * FROM qux_temp");
138 Tables, both temporary and permanent may also be created directly from
140 perl arrayrefs and from heterogeneous queries to any DBI accessible
141 data source, see the IMPORT() function.
142 CREATE [ {LOCAL⎪GLOBAL} TEMPORARY ] TABLE $table
144 (
145 $col_1 $col_type1 $col_constraints1,
146 ...,
147 $col_N $col_typeN $col_constraintsN,
148 )
149 [ ON COMMIT {DELETE⎪PRESERVE} ROWS ]
150 * col_type must be a valid data type as defined in the
152 "valid_data_types" section of the dialect file for the
153 current dialect
154 * col_constriaints may be "PRIMARY KEY" or one or both of
156 "UNIQUE" and/or "NOT NULL"
157 * IMPORTANT NOTE: temporary tables, data types and column
159 constraints are checked for syntax violations but are
160 currently otherwise *IGNORED* -- they are recognized by
161 the parser, but not by the execution engine
162 * The following valid ANSI SQL92 options are not currently
164 supported: table constraints, named constraints, check
165 constriants, reference constraints, constraint
166 attributes, collations, default clauses, domain names as
167 data types
168 DROP TABLE
170 DROP TABLE $table [ RESTRICT ⎪ CASCADE ]
172 * IMPORTANT NOTE: drop behavior (cascade or restrict) is
174 checked for valid syntax but is otherwise *IGNORED* -- it
175 is recognized by the parser, but not by the execution
176 engine
177 INSERT INTO
179 INSERT INTO $table [ ( $col1, ..., $colN ) ] VALUES ( $val1, ... $valN )
181 * default values are not currently supported
183 * inserting from a subquery is not currently supported
184 DELETE FROM
186 DELETE FROM $table [ WHERE search_condition ]
188 * see "search_condition" below
190 UPDATE
192 UPDATE $table SET $col1 = $val1, ... $colN = $valN [ WHERE search_condition ]
194 * default values are not currently supported
196 * see "search_condition" below
197 SELECT
199 SELECT select_clause
201 FROM from_clause
202 [ WHERE search_condition ]
203 [ ORDER BY $ocol1 [ASC⎪DESC], ... $ocolN [ASC⎪DESC] ]
204 [ LIMIT [start,] length ]
205 * select clause ::=
207 [DISTINCT⎪ALL] *
208 ⎪ [DISTINCT⎪ALL] col1 [,col2, ... colN]
209 ⎪ set_function1 [,set_function2, ... set_functionN]
210 * set function ::=
212 COUNT ( [DISTINCT⎪ALL] * )
213 ⎪ COUNT ⎪ MIN ⎪ MAX ⎪ AVG ⎪ SUM ( [DISTINCT⎪ALL] col_name )
214 * from clause ::=
216 table1 [, table2, ... tableN]
217 ⎪ table1 NATURAL [join_type] JOIN table2
218 ⎪ table1 [join_type] table2 USING (col1,col2, ... colN)
219 ⎪ table1 [join_type] JOIN table2 ON table1.colA = table2.colB
220 * join type ::=
222 INNER
223 ⎪ [OUTER] LEFT ⎪ RIGHT ⎪ FULL
224 * if join_type is not specified, INNER is the default
226 * if DISTINCT or ALL is not specified, ALL is the default
227 * if start position is omitted from LIMIT clause, position 0 is
228 the default
229 * ON clauses may only contain equal comparisons and AND combiners
230 * self-joins are not currently supported
231 * if implicit joins are used, the WHERE clause must contain
232 and equijoin condition for each table
233 SEARCH CONDITION
235 [NOT] $val1 $op1 $val1 [ ... AND⎪OR $valN $opN $valN ]
237 OPERATORS
239 $op = ⎪ <> ⎪ < ⎪ > ⎪ <= ⎪ >=
241 ⎪ IS NULL ⎪ IS NOT NULL ⎪ LIKE ⎪ CLIKE ⎪ BETWEEN ⎪ IN
242 The "CLIKE" operator works exactly the same as the "LIKE"
244 operator, but is case insensitive. For example:
245 WHERE foo LIKE 'bar%' # succeeds if foo is "barbaz"
247 # fails if foo is "BARBAZ" or "Barbaz"
248 WHERE foo CLIKE 'bar%' # succeeds for "barbaz", "Barbaz", and "BARBAZ"
250 BUILT-IN AND USER-DEFINED FUNCTIONS
252 There are many built-in functions and you can also create your own new
254 functions from perl subroutines. See SQL::Statement::Functions for
255 documentation of functions.
256 Identifiers (table & column names)
258 Regular identifiers (table and column names *without* quotes around
260 them) are case INSENSITIVE so column foo, fOo, FOO all refer to the
261 same column.
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 state‐
302 ment, and in the FROM clause of any statement. When used in a FROM
303 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. <b>Please Note:</b> 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 sep‐
371 arated by a semi-colon+newline combination (/;\n/). If you wish to use
372 different separators or import SQL from a different source, just over-
373 ride the RUN() function with your own user-defined-function.
374 Further details
376 Integers
378 Reals Syntax obvious
379 Strings Surrounded by either single or double quotes; some characters
381 need to be escaped with a backslash, in particular the back‐
382 slash itself (\\), the NUL byte (\0), Line feeds (\n), Carriage
383 return (\r), and the quotes (\' or \").
384 Parameters
386 Parameters represent scalar values, like Integers, Reals and
387 Strings do. However, their values are read inside Execute() and
388 not inside Prepare(). Parameters are represented by question
389 marks (?).
390 Identifiers
392 Identifiers are table or column names. Syntactically they con‐
393 sist of alphabetic characters, followed by an arbitrary number
394 of alphanumeric characters. Identifiers like SELECT, INSERT,
395 INTO, ORDER, BY, WHERE, ... are forbidden and reserved for
396 other tokens. Identifiers are always compared case-inseni‐
397 tively, i.e. "select foo from bar" will be evaluated the same
398 as "SELECT FOO FROM BAR". One exception is that if the module
399 is used in conjunction with a file storage system, the names of
400 tables are case sensitive.
401
403 The Supported SQL syntax shown above is the default for SQL::Statement
404 but it can be extended (or contracted) either on-the-fly or on a perma‐
405 nent basis. In other words, you can modify the SQL syntax accepted as
406 valid by the parser and accepted as executable by the executer. There
407 are two methods for extending the syntax - 1) with SQL commands that
408 can be issued directly in SQL::Statement or form a DBD or 2) by sub‐
409 classing SQL::Parser.
410 The following SQL commands modify the default SQL syntax:
412 CREATE/DROP FUNCTION
414 CREATE/DROP KEYWORD
415 CREATE/DROP TYPE
416 CREATE/DROP OPERATOR
417 A simple example would be a situation in which you have a table named
419 'TABLE'. Since table is an ANSI reserved key word, by default
420 SQL::Statement will produce an error when you attempt to create or
421 access it. You could put the table name inside double quotes since
422 quoted identifiers can validly be reserved words, or you could rename
423 the table. But if those aren't options, you would do this:
424 DROP KEYWORD table
426 Once that statement is issued, the parser will no longer object to 'ta‐
428 ble' as a table name. Careful though, if you drop too many keywords
429 you may confuse the parser, especially keywords like FROM and WHERE
430 that are central to parsing the statement.
431 In the reverse situation, suppose you want to parse some SQL that
433 defines a column as type BIG_BLOB. Since 'BIG_BLOB' isn't a recognized
434 ANSI data type, an error will be produced by default. To make the
435 parser treat it as a valid data type, you'd do this:
436 CREATE TYPE big_blob
438 Keywords and types are case-insensitive, so it doesn't matter what case
440 you define it or use it with.
441 Suppose you're working with some SQL that contains the cosh() function
443 (an Oracle function for hyperbolic cosine, whatever that is :-). The
444 cosh() function isn't currently implemented in SQL::Statement so the
445 parser would die with an error. But you can easily trick the parser
446 into accepting the function:
447 CREATE FUNCTION cosh
449 Once the parser has read that CREATE FUNCTION statement, it will no
451 longer object to the use of the cosh() function in SQL statements.
452 If your only interest is in parsing SQL statements, then 'CREATE FUNC‐
454 TION cosh' is sufficient. But if you actually want to be able to use
455 the cosh() function in executable statements, you need to supply a perl
456 subroutine that performs the cosh() function:
457 CREATE FUNCTION cosh AS perl_subroutine_name
459 The subroutine name can refer to a subroutine in your current script,
461 or to a subroutine in any available package. See SQL::Statement::Func‐
462 tions for details of how to create and load functions.
463 Functions can be used as predicates in search clauses, for example:
465 SELECT * FROM x WHERE c1=7 AND SOUNDEX(c3,'foo') AND c8='bar'
467 In the SQL above, the SOUNDEX() function full predicate - it plays the
469 same role as "c1=7" or "c8='bar'".
470 Functions can also serve as predicate operators. An operator, unlike a
472 full predicate, has something on the left and right sides. An equal
473 sign is an operator, so is LIKE. If you really want to you can get the
474 parser to not accept LIKE as an operator with
475 DROP OPERATOR like
477 Or, you can invent your own operator. Suppose you have an operator
479 "REVERSE_OF" that is true if the string on its left side when reversed
480 is equal to the string on the right side:
481 CREATE OPERATOR reverse_of
483 SELECT * FROM x WHERE c1=7 AND c3 REVERSE_OF 'foo'
484 The operator could just as well have been written as a function:
486 CREATE FUNCTION reverse_of
488 SELECT * FROM x WHERE c1=7 AND REVERSE_OF(c3,'foo')
489 Like functions, if you want to actually execute a user-defined operator
491 as didistinct form just parsing it, you need to assign the operator to
492 a perl subroutine. This is done exactly like assignin functions:
493 CREATE OPERATOR reverse_of AS perl_subroutine_name
495
497 In addition to using the SQL shown above to modify the parser's behav‐
498 ior, you can also extend the SQL syntax by subclassing SQL::Parser.
499 See SQL::Parser for details.
500
502 Copyright (c) 2005, Jeff Zucker <jzuckerATcpan.org>, all rights
503 reserved.
504 This document may be freely modified and distributed under the same
506 terms as Perl itself.
507perl v5.8.8 2005-04-18 SQL::Statement::Syntax(3)