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 There are two major components to SQL::Abstract v2.
63 This is the Abstract Syntax Tree. It is a data structure that represents
64 everything necessary to construct the SQL statement in whatever dialect the
69 This object conforms to the Visitor pattern and is used to generate the SQL
70 represented by the AST. Each dialect will have a different Visitor object. In
71 addition, there will be visitors for at least one of the ANSI specifications.
75 The division of duties between the two components will focus on what the AST
76 can and cannot assume. For example, identifiers do not have 20 components in
77 any dialect, so the AST can validate that. However, determining what
78 constitutes a legal identifier can only be determined by the Visitor object
79 enforcing that dialect's rules.
83 The AST will be a HoHo..oH (hash of hash of ... of hashes). The keys to the
84 outermost hash will be the various clauses of a SQL statement, plus some
85 metadata keys. All metadata keys will be identifiable as such by being prefixed
86 with an underscore. All keys will be in lowercase.
90 These are the additional metadata keys that the AST provides for.
94 This denotes what kind of query this AST should be interpreted as. Different
95 Visitors may accept additional values for _query. For example, a MySQL Visitor
96 may choose to accept 'replace'. If a _query value is unrecognized by the
97 Visitor, the Visitor is expected to throw an error.
99 All Visitors are expected to handle the following values for _query:
105 This is a SELECT statement.
109 This is an INSERT statement.
113 This is an UPDATE statement.
117 This is a DELETE statement.
123 This denotes the version of the AST. Different versions will indicate different
124 capabilities provided. Visitors will choose to respect the _version as needed
127 =head2 Structural units
129 All structural units will be hashes. These hashes will have, at minimum, the
136 This indicates the structural unit that this hash is representing. While this
137 specification provides for standard structural units, different Visitors may
138 choose to accept additional units as desired. If a Visitor encounters a unit it
139 doesn't know how to handle, it is expected to throw an exception.
143 Structural units in the AST are supported by loaded components. L<SQL::Abstract>
144 provides for the following structural units by default:
148 This is a (potentially) fully canonicalized identifier for a elemnt in the
149 query. This element could be a schema, table, or column. The Visitor will
150 determine validity within the context of that SQL dialect. The AST is only
151 responsible for validating that the elements are non-empty Strings.
153 The hash will be structured as follows:
156 _name => 'Identifier',
160 The items will always be quoted per the SQL dialect's quoting scheme. It is the
161 responsibility of the Visitor to do this.
165 A Value is a Perl scalar. It may either be a:
171 A String is a quoted series of characters
175 A Number is an unquoted number in some numeric format
179 NULL is SQL's NULL and corresponds to Perl's C<undef>.
181 =item * BindParameter
183 This corresponds to a value that will be passed in. This value is normally
184 quoted in such a fashion so as to protect against SQL injection attacks. (q.v.
185 L<DBI/quote()> for an example.)
189 The hash will be structured as follows:
193 _subtype => [ 'String' | 'Number' | 'NULL' | 'BindParameter' ]
197 The provided subtypes are the ones that all Visitors are expected to support.
198 Visitors may choose to support additional subtypes. Visitors are expected to
199 throw an exception upon encountering an unknown subtype.
203 A Function is anything of the form C< name( arglist ) > where C<name> is a
204 string and C<arglist> is a comma-separated list of Expressions.
206 Yes, a Subquery is legal as an argument for many functions. Some example
221 A Subquery is another AST whose _query metadata parameter is set to "SELECT".
223 Most places that a Subquery can be used would require a single value to be
224 returned (single column, single row), but that is not something that the AST can
225 easily enforce. The single-column restriction can possibly be enforced, but the
226 single-row restriction is much more difficult and, in most cases, probably
229 Subqueries, when expressed in SQL, must bounded by parentheses.
231 =head3 Unary Operator
233 A UnaryOperator takes a single argument on the RHS and is one of the following:
241 =head3 BinaryOperator
243 A BinaryOperator takes two arguments (one on the LHS and one on the RHS).
245 Examples of BinaryOperators would include:
269 Note that an operator can comprise of what would be multiple tokens in a normal
272 =head3 TrinaryOperator
274 A TrinaryOperator takes three arguments. It generally is composed of two
275 elements with one argument to the LHS, one to the RHS, and a third in the middle
278 Examples of TrinaryOperators would include:
282 =item * X BETWEEN Y AND Z
288 An expression can be any one of the following:
298 =item * UnaryOperator Expression
300 =item * Expression BinaryOperator Expression
302 =item * ( Expression )
306 Parentheses indicate precedence and, in some situations, are necessary for
311 These are all the legal and acceptable clauses within the AST that would
312 correpsond to clauses in a SQL statement. Not all clauses are legal within a
313 given RDBMS engine's SQL dialect and some clauses may be required in one and
314 optional in another. Detecting and enforcing those engine-specific restrictions
315 is the responsibility of the Visitor object.
317 The clauses are defined with a yacc-like syntax. The various parts are:
323 This means "defined" and is used to create a new term to be used below.
327 This means optional and indicates that the items within it are optional.
331 This means optional and repeating as many times as desired.
335 This means alternation. It is a binary operator and indicates that either the
336 left or right hand sides may be used, but not both.
340 This is a grouping construct. It means that all elements within this construct
341 are treated together for the purposes of optional, repeating, alternation, etc.
345 The expected clauses are (name and structure):
349 This corresponds to the SELECT clause of a SELECT statement.
351 A select clause is composed as follows:
353 SelectComponent := Expression [ [ AS ] String ]
356 [ , SelectComponent ]*
360 This is a list of tables that this clause is affecting. It corresponds to the
361 FROM clause in a SELECT statement and the INSERT INTO/UPDATE/DELETE clauses in
362 those respective statements. Depending on the _query metadata entry, the
363 appropriate clause name will be used.
365 The tables clause has several RDBMS-specific variations. The AST will support
366 all of them and it is up to the Visitor object constructing the actual SQL to
367 validate and/or use what is provided as appropriate.
369 A table clause is composed as follows:
371 TableIdentifier := Identifier [ [ AS ] String ]
372 JoinType := < LEFT|RIGHT [ OUTER ] > | INNER | CROSS
376 < , TableIdentifier >
378 [ JoinType ] JOIN TableIdentifier
380 < USING ( Identifier [ , Identifier ] ) >
381 | < ON [ ( ] Expression [ , Expression ] [ ) ] >
386 Additionally, where aliases are provided for in the TableIdentifier, those
387 aliases must be used as the tablename in subsequent Identifiers that identify a
388 column of that table.
392 This corresponds to the WHERE clause in a SELECT, UPDATE, or DELETE statement.
394 A where clause is composed as follows:
396 WhereOperator := AND | OR
397 WhereExpression := Expression | Expression WhereOperator Expression
403 This corresponds to the SET clause in an INSERT or UPDATE statement.
405 A set clause is composed as follows:
407 SetComponent := Identifier = Expression
409 SetComponent [ , SetComponent ]*
413 This corresponds to the optional list of columns in an INSERT statement.
415 A columns clause is composed as follows:
417 ( Identifier [ , Identifier ]* )
421 This corresponds to the VALUES clause in an INSERT statement.
423 A values clause is composed as follows:
425 ( Expression [ , Expression ]* )
427 If there is a columns clause, the number of entries in the values clause must be
428 equal to the number of entries in the columns clause.
432 This corresponds to the ORDER BY clause in a SELECT statement.
434 An orderby clause is composed as follows:
436 OrderByComponent := XXX-TODO-XXX
437 OrderByDirection := ASC | DESC
439 OrderByComponent [ OrderByDirection ]
440 [ , OrderByComponent [ OrderByDirection ] ]*
444 This corresponds to the GROUP BY clause in a SELECT statement.
446 An groupby clause is composed as follows:
448 GroupByComponent := XXX-TODO-XXX
450 GroupByComponent [ , GroupByComponent ]*
454 This corresponds to the clause that is used in some RDBMS engines to limit the
455 number of rows returned by a query. In MySQL, this would be the LIMIT clause.
457 A rows clause is composed as follows:
463 This corresponds to the clause that is used in some RDBMS engines to indicate
464 what locks are to be taken by this SELECT statement.
466 A for clause is composed as follows:
472 This corresponds to the clause that is used in some RDBMS engines to provide for
473 an adjacency-list query.
475 A connectby clause is composed as follows:
477 Identifier, WhereExpression
481 robkinyon: Rob Kinyon C<< <rkinyon@cpan.org> >>
485 You may distribute this code under the same terms as Perl itself.