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
125 These are the additional metadata keys that the AST provides for.
129 This denotes what kind of query this AST should be interpreted as. Different
130 Visitors may accept additional values for type. For example, a MySQL Visitor
131 may choose to accept 'replace' for REPLACE INTO. If a type value is
132 unrecognized by the Visitor, the Visitor is expected to throw an error.
134 All Visitors are expected to handle the following values for type:
140 This is a SELECT statement.
144 This is an INSERT statement.
148 This is an UPDATE statement.
152 This is a DELETE statement.
158 This denotes the version of the AST. Different versions will indicate different
159 capabilities provided. Visitors will choose to respect the ast_version as needed
162 =head2 Structural units
164 All structural units will be hashes. These hashes will have, at minimum, the
171 This indicates the structural unit that this hash is representing. While this
172 specification provides for standard structural units, different Visitors may
173 choose to accept additional units as desired. If a Visitor encounters a unit it
174 doesn't know how to handle, it is expected to throw an exception.
178 Structural units in the AST are supported by loaded components. L<SQL::Abstract>
179 provides for the following structural units by default:
183 This is a (potentially) fully canonicalized identifier for a elemnt in the
184 query. This element could be a schema, table, or column. The Visitor will
185 determine validity within the context of that SQL dialect. The AST is only
186 responsible for validating that the elements are non-empty Strings.
188 The hash will be structured as follows:
191 type => 'Identifier',
192 elements => [ Scalar ],
195 All values in elements must be defined.
197 Visitors are expected to, by default, quote all identifiers according to the SQL
198 dialect's quoting scheme.
200 Any of the elements may be '*', as in SELECT * or SELECT COUNT(*). Visitors must
201 be careful to I<not> quote asterisks.
205 A Value is a Perl scalar. Depending on the subtype, a Visitor may be able to
206 make certain decisions. The following are the minimally-valid subtypes:
212 A String is a quoted series of characters. The Visitor is expected to ensure
213 that embedded quotes are properly handled per the SQL dialect's quoting scheme.
217 A Number is an unquoted number in some numeric format.
221 A Null is SQL's NULL and corresponds to Perl's C<undef>.
225 A Boolean is a two-value entity - true or false. Some DBMSes provide an explicit
226 boolean while others do not.
230 A Date represents (generally) a year, month, and day.
234 A Time represents (generally) an hour, minute, and second.
238 A DateTime represents the complete description necessary to determine a specific point in
239 time. This could correspond to a L<DateTime/> object in Perl.
243 The hash will be structured as follows:
247 subtype => [ 'String' | 'Number' | 'Null' | 'Boolean' | 'Date' | 'Time' | 'DateTime' ]
252 The provided subtypes are the ones that all Visitors are expected to support.
253 Visitors may choose to support additional subtypes. Visitors are expected to
254 throw an exception upon encountering an unknown subtype.
256 C<is_bind> defaults to true. It determines whether or not the Visitor should
257 attempt to treat this value as a BindParameter or not.
261 An Operator would be, in SQL dialect terms, a unary operator, a binary operator,
262 a trinary operator, or a function. Since different dialects may have a given
263 functionality as an operator or a function (such as CONCAT in MySQl vs. || in
264 Oracle for string concatenation), they will be represented in the AST as generic
267 The hash will be structured as follows:
277 Operators have a cardinality, or expected number of arguments. Some operators,
278 such as MAX(), have a cardinality of 1. Others, such as IF(), have a cardinality
279 of N, meaning they can have any number of arguments greater than 0. Others, such
280 as NOW(), have a cardinality of 0. Several operators with the same meaning may
281 have a different cardinality in different SQL dialects as different engines may
282 allow different behaviors. As cardinality may differ between dialects, enforcing
283 cardinality is necessarily left to the Visitor.
285 Operators also have restrictions on the types of arguments they will accept. The
286 first argument may or may not restricted in the same fashion as the other
287 arguments. As with cardinality, this restriction will need to be managed by the
290 The operator name needs to take into account the possibility that the RDBMS may
291 allow UDFs (User-Defined Functions) that have the same name as an operator, such
292 as 'AND'. This will have to be managed by the Visitor.
296 A Subquery is another AST whose type metadata parameter is set to "SELECT".
298 Most places that a Subquery can be used would require a single value to be
299 returned (single column, single row), but that is not something that the AST can
300 easily enforce. The single-column restriction may possibly be enforced, but the
301 single-row restriction is much more difficult and, in most cases, probably
304 Subqueries, when expressed in SQL, must be bounded by parentheses.
308 An Alias is any place where the construct "X as Y" appears; it is the "AS Y" part.
310 The hash will be structured as follows:
320 An Expression can be any one of the following:
336 An Expression is a meta-syntactic unit. An "Expression" unit will never appear
337 within the AST. It acts as a junction.
341 There is no specific operator or nodetype for nesting. Instead, nesting is
342 explicitly specified by node descent in the AST.
346 These are all the legal and acceptable clauses within the AST that would
347 correpsond to clauses in a SQL statement. Not all clauses are legal within a
348 given RDBMS engine's SQL dialect and some clauses may be required in one and
349 optional in another. Detecting and enforcing those engine-specific restrictions
350 is the responsibility of the Visitor object.
352 The following clauses are expected to be handled by Visitors for each statement:
382 There are RDBMS-specific variations of the INSERT statement, such the one in
409 The expected clauses are (name and structure):
413 This corresponds to the SELECT clause of a SELECT statement.
415 A select clause unit is an array of one or more Expressions.
419 This is a list of tables that this clause is affecting. It corresponds to the
420 FROM clause in a SELECT statement and the INSERT INTO/UPDATE/DELETE clauses in
421 those respective statements. Depending on the type metadata entry, the
422 appropriate clause name will be used.
424 The tables clause has several RDBMS-specific variations. The AST will support
425 all of them and it is up to the Visitor object constructing the actual SQL to
426 validate and/or use what is provided as appropriate.
428 A tables clause is an Expression that also allows for Joins.
430 The hash for an Join within a tables clause will be composed as follows:
434 op => '< LEFT|RIGHT|FULL [ OUTER ] > | INNER | CROSS',
436 args => [ Expression ],
439 A USING clause is syntactic sugar for an ON clause and, as such, is not provided
440 for by the AST. A join of a comma is identical to a CROSS JOIN and, as such, is
441 not provided for by the AST. The on clause is optional.
445 This corresponds to the WHERE clause in a SELECT, UPDATE, or DELETE statement.
447 A where clause is composed of an Expression.
451 This corresponds to the SET clause in an INSERT or UPDATE statement.
453 The hash for an set clause will be composed as follows:
463 The args is an array that is organized as follows: The first element is an array of
464 Identifiers for the columns being set. The following arrays are Expressions describing
465 the values. The various arrays should be the same length. The array of Identifiers can
470 This corresponds to the ORDER BY clause in a SELECT statement.
472 A orderby clause unit is an array of one or more OrderbyComponent units.
474 The hash for a OrderbyComponent unit is composed as follows:
477 type => 'OrderbyComponent',
479 dir => '< ASC | DESC >',
482 The value should either be an Identifier or a Number. The dir element, if
483 omitted, will be defaulted to ASC by the AST. The number corresponds to a column
484 in the select clause.
488 This corresponds to the GROUP BY clause in a SELECT statement.
490 A groupby clause unit is an array of one or more GroupbyComponent units.
492 The hash for a GroupbyComponent unit is composed as follows:
495 type => 'GroupbyComponent',
499 The value should either be an Identifier or a Number. The number corresponds to
500 a column in the select clause.
502 =head2 Possible RDBMS-specific clauses
504 The following clauses are provided as examples for RDBMS-specific elements. They
505 are B<not> expected to be supported by all Visitors. Visitors may choose whether
506 or not to throw on an unexpected clause, though it is strongly recommended.
510 This corresponds to the clause that is used in some RDBMS engines to limit the
511 number of rows returned by a SELECT statement. In MySQL, this would be the LIMIT
514 The hash for a rows clause is composed as follows:
521 The start attribute, if ommitted, will default to 0. The count attribute is
526 This corresponds to the clause that is used in some RDBMS engines to indicate
527 what locks are to be taken by this SELECT statement.
529 The hash for a for clause is composed as follows:
532 value => '< UPDATE | DELETE >',
541 =item * UNION, UNION ALL, and MINUS
543 =item * start the API guidelines
547 Convert INSERT and UPDATE into ->populate form.
551 robkinyon: Rob Kinyon C<< <rkinyon@cpan.org> >>
555 You may distribute this code under the same terms as Perl itself.