3 SQL::Abstract::Manual::Specification
7 This discusses the specification for the AST provided by L<SQL::Abstract>. It is
8 meant to describe how the AST is structured, various components provided by
9 L<SQL::Abstract> for use with this AST, how to manipulate the AST, and various
10 uses for the AST once it is generated.
14 L<SQL::Abstract> has been in use for many years. Originally created to handle
15 the where-clause formation found in L<DBIx::Abstract>, it was generalized to
16 manage the creation of any SQL statement through the use of Perl structures.
17 Through the beating it received as the SQL generation syntax for L<DBIx::Class>,
18 various deficiencies were found and a generalized SQL AST was designed. This
19 document describes that AST.
23 The goals for this AST are as follows:
25 =head2 SQL-specific semantics
27 Instead of attempting to be an AST to handle any form of query, this will
28 instead be specialized to manage SQL queries (and queries that map to SQL
29 queries). This means that there will be support for SQL-specific features, such
32 =head2 Perl-specific semantics
34 This AST is meant to be used from within Perl5 only. So, it will take advantage
35 of as many Perl-specific features that make sense to use. No attempt whatosever
36 will be made to make this AST work within any other language, including Perl6.
38 =head2 Whole-lifecycle management
40 Whether a query is built out of whole cloth in one shot or cobbled together from
41 several snippets over the lifetime of a process, this AST will support any way
42 to construct the query. Queries can also be built from other queries, so an
43 UPDATE statement could be used as the basis for a SELECT statement, DELETE
44 statement, or even a DDL statement of some kind.
46 =head2 Dialect-agnostic usage
48 Even though SQL itself has several ANSI specifications (SQL-92 and SQL-99 among
49 them), this only serves as a basis for what a given RDBMS will expect. However,
50 every engine has its own specific extensions and specific ways of handling
51 common features. The AST will provide ways of expressing common functionality in
52 a common language. The emitters (objects that follow the Visitor pattern) will
53 be responsible for converting that common language into RDBMS-specific SQL.
57 The following are the restrictions upon the AST:
61 The AST will only support DML (Data Modelling Language). It will not (currently)
62 support DDL (Data Definition Language). Practically, this means that the only
63 statements supported will be:
77 Additional DML statements may be supported by specific Visitors (such as a
78 MySQL visitor supporting REPLACE INTO). q.v. the relevant sections of this
79 specification for details.
81 =head2 Dialect-agnostic construction
83 The AST will not attempt to be immediately readable to a human as SQL. In fact,
84 due to the dialect differences, particularly in terms of which use operators and
85 which use functions for a given action, the AST will provide simple units. It is
86 the responsibility of the Visitor to provide the appropriate SQL. Furthermore,
87 the AST will be very generic and only provide hints for a subset of SQL. If a
88 Visitor is sufficiently intelligent, pretty SQL may be emitted, but that is not
93 There are two major components to SQL::Abstract v2.
99 This is the Abstract Syntax Tree. It is a data structure that represents
100 everything necessary to construct the SQL statement in whatever dialect the
105 This object conforms to the Visitor pattern and is used to generate the SQL
106 represented by the AST. Each dialect will have a different Visitor object. In
107 addition, there will be visitors for at least one of the ANSI specifications.
111 The division of duties between the two components will focus on what the AST
112 can and cannot assume. For example, identifiers do not have 20 components in
113 any dialect, so the AST can validate that. However, determining what
114 constitutes a legal identifier can only be determined by the Visitor object
115 enforcing that dialect's rules.
119 The AST will be a HoHo..oH (hash of hash of ... of hashes). The keys to the
120 outermost hash will be the various clauses of a SQL statement, plus some
121 metadata keys. All metadata keys will be identifiable as such by being prefixed
122 with an underscore. All keys will be in lowercase.
126 These are the additional metadata keys that the AST provides for.
130 This denotes what kind of query this AST should be interpreted as. Different
131 Visitors may accept additional values for _query. For example, a MySQL Visitor
132 may choose to accept 'replace' for REPLACE INTO. If a _query value is
133 unrecognized by the Visitor, the Visitor is expected to throw an error.
135 All Visitors are expected to handle the following values for _query:
141 This is a SELECT statement.
145 This is an INSERT statement.
149 This is an UPDATE statement.
153 This is a DELETE statement.
159 This denotes the version of the AST. Different versions will indicate different
160 capabilities provided. Visitors will choose to respect the _version as needed
163 =head2 Structural units
165 All structural units will be hashes. These hashes will have, at minimum, the
172 This indicates the structural unit that this hash is representing. While this
173 specification provides for standard structural units, different Visitors may
174 choose to accept additional units as desired. If a Visitor encounters a unit it
175 doesn't know how to handle, it is expected to throw an exception.
179 Structural units in the AST are supported by loaded components. L<SQL::Abstract>
180 provides for the following structural units by default:
184 This is a (potentially) fully canonicalized identifier for a elemnt in the
185 query. This element could be a schema, table, or column. The Visitor will
186 determine validity within the context of that SQL dialect. The AST is only
187 responsible for validating that the elements are non-empty Strings.
189 The hash will be structured as follows:
192 type => 'Identifier',
198 If element3 exists, then element2 must exist. element1 must always exist. If a
199 given element exists, then it must be defined and of non-zero length.
201 Visitors are expected to, by default, quote all identifiers according to the SQL
202 dialect's quoting scheme.
206 A Value is a Perl scalar. Depending on the type, a Visitor may be able to make
213 A String is a quoted series of characters. The Visitor is expected to ensure
214 that embedded quotes are properly handled per the SQL dialect's quoting scheme.
218 A Number is an unquoted number in some numeric format.
222 Null is SQL's NULL and corresponds to Perl's C<undef>.
224 =item * BindParameter
226 This corresponds to a value that will be passed in. This value is normally
227 quoted in such a fashion so as to protect against SQL injection attacks. (q.v.
228 L<DBI/quote()> for an example.)
230 BindParameters are normally represented by a '?'.
234 The hash will be structured as follows:
238 subtype => [ 'String' | 'Number' | 'Null' | 'BindParameter' ]
242 The provided subtypes are the ones that all Visitors are expected to support.
243 Visitors may choose to support additional subtypes. Visitors are expected to
244 throw an exception upon encountering an unknown subtype.
248 An Operator would be, in SQL dialect terms, a unary operator, a binary operator,
249 a trinary operator, or a function. Since different dialects may have a given
250 functionality as an operator or a function (such as CONCAT in MySQl vs. || in
251 Oracle for string concatenation), they will be represented in the AST as generic
254 The hash will be structured as follows:
259 args => ExpressionList,
262 Operators have a cardinality, or expected number of arguments. Some operators,
263 such as MAX(), have a cardinality of 1. Others, such as IF(), have a cardinality
264 of N, meaning they can have any number of arguments greater than 0. Others, such
265 as NOW(), have a cardinality of 0. Several operators with the same meaning may
266 have a different cardinality in different SQL dialects as different engines may
267 allow different behaviors. As cardinality may differ between dialects, enforcing
268 cardinality is necessarily left to the Visitor.
270 Operators also have restrictions on the types of arguments they will accept. The
271 first argument may or may not restricted in the same fashion as the other
272 arguments. As with cardinality, this restriction will need to be managed by the
275 The operator name needs to take into account the possibility that the RDBMS may
276 allow UDFs (User-Defined Functions) that have the same name as an operator, such
277 as 'AND'. This will have to be managed by the Visitor.
281 A Subquery is another AST whose _query metadata parameter is set to "SELECT".
283 Most places that a Subquery can be used would require a single value to be
284 returned (single column, single row), but that is not something that the AST can
285 easily enforce. The single-column restriction may possibly be enforced, but the
286 single-row restriction is much more difficult and, in most cases, probably
289 Subqueries, when expressed in SQL, must be bounded by parentheses.
293 An Expression can be any one of the following:
307 An Expression is a meta-syntactic unit. An "Expression" unit will never appear
308 within the AST. It acts as a junction.
310 =head3 ExpressionList
312 An ExpressionList is a list of Expressions, generally separated by commas
313 (though other separators may be appropriate at times or for different SQL
314 dialects). An null separator may also be used.
316 The hash for an ExpressionList is as follows:
319 type => 'ExpressionList',
321 elements => Array of Expressions,
324 An ExpressionList is always rendered in SQL with parentheses around it.
328 These are all the legal and acceptable clauses within the AST that would
329 correpsond to clauses in a SQL statement. Not all clauses are legal within a
330 given RDBMS engine's SQL dialect and some clauses may be required in one and
331 optional in another. Detecting and enforcing those engine-specific restrictions
332 is the responsibility of the Visitor object.
334 The clauses are defined with a yacc-like syntax. The various parts are:
340 This means "defined" and is used to create a new term to be used below.
344 This means optional and indicates that the items within it are optional.
348 This means optional and repeating as many times as desired.
352 This means alternation. It is a binary operator and indicates that either the
353 left or right hand sides may be used, but not both.
357 This is a grouping construct. It means that all elements within this construct
358 are treated together for the purposes of optional, repeating, alternation, etc.
362 The expected clauses are (name and structure):
366 This corresponds to the SELECT clause of a SELECT statement.
368 A select clause unit is an array of one or more SelectComponent units.
370 The hash for a SelectComponent unit is composed as follows:
373 type => 'SelectComponent',
378 The 'as' component is optional. Visitors may choose to make it required in
383 This is a list of tables that this clause is affecting. It corresponds to the
384 FROM clause in a SELECT statement and the INSERT INTO/UPDATE/DELETE clauses in
385 those respective statements. Depending on the _query metadata entry, the
386 appropriate clause name will be used.
388 The tables clause has several RDBMS-specific variations. The AST will support
389 all of them and it is up to the Visitor object constructing the actual SQL to
390 validate and/or use what is provided as appropriate.
392 A TableJoin is a junction of the following elements:
396 =item * TableIdentifier
402 The hash for a TableIdentifier will be composed as follows:
406 type => 'TableIdentifier',
411 The value should be either an Identifier or a SubQuery.
413 The hash for an Operator within a tables clause will be composed as follows:
418 op => '< LEFT|RIGHT|FULL [ OUTER ] > | INNER | CROSS',
422 A USING clause is syntactic sugar for an ON clause and, as such, is not provided
423 for by the AST. A join of a comma is identical to a CROSS JOIN. The on clause is
428 This corresponds to the WHERE clause in a SELECT, UPDATE, or DELETE statement.
430 A where clause is composed as follows:
432 WhereOperator := AND | OR
433 WhereExpression := Expression | Expression WhereOperator Expression
439 This corresponds to the SET clause in an INSERT or UPDATE statement.
441 A set clause is composed as follows:
443 SetComponent := Identifier = Expression
445 SetComponent [ , SetComponent ]*
449 This corresponds to the optional list of columns in an INSERT statement.
451 A columns clause is an IdentifierList and the unit is composed as follows:
460 This corresponds to the VALUES clause in an INSERT statement.
462 A values clause is an ExpressionList and the unit is composed as follows.
469 If there is a columns clause, the number of entries in the values clause must be
470 equal to the number of entries in the columns clause.
474 This corresponds to the ORDER BY clause in a SELECT statement.
476 An orderby clause is composed as follows:
478 OrderByComponent := XXX-TODO-XXX
479 OrderByDirection := ASC | DESC
481 OrderByComponent [ OrderByDirection ]
482 [ , OrderByComponent [ OrderByDirection ] ]*
486 This corresponds to the GROUP BY clause in a SELECT statement.
488 An groupby clause is composed as follows:
490 GroupByComponent := XXX-TODO-XXX
492 GroupByComponent [ , GroupByComponent ]*
496 This corresponds to the clause that is used in some RDBMS engines to limit the
497 number of rows returned by a query. In MySQL, this would be the LIMIT clause.
499 A rows clause is composed as follows:
505 This corresponds to the clause that is used in some RDBMS engines to indicate
506 what locks are to be taken by this SELECT statement.
508 A for clause is composed as follows:
514 This corresponds to the clause that is used in some RDBMS engines to provide for
515 an adjacency-list query.
517 A connectby clause is composed as follows:
519 Identifier, WhereExpression
523 The following are example SQL statements and a possible AST for each one.
531 _ast_version => 0.0001,
534 type => 'SelectComponent',
544 =item * SELECT NOW() AS time FROM dual AS duality
548 _ast_version => 0.0001,
551 type => 'SelectComponent',
557 type => 'Identifier',
563 type => 'TableIdentifier',
565 type => 'Identifier',
572 =item * SELECT 1 FROM foo LEFT OUTER JOIN bar ON ( foo.col1 = bar.col2 )
576 _ast_version => 0.0001,
579 type => 'SelectComponent',
592 type => 'TableIdentifier',
594 type => 'Identifier',
599 type => 'TableIdentifier',
601 type => 'Identifier',
611 type => 'Identifier',
616 type => 'Identifier',
637 robkinyon: Rob Kinyon C<< <rkinyon@cpan.org> >>
641 You may distribute this code under the same terms as Perl itself.