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 API to the AST will provide ways of expressing common
52 functionality in a common language. The emitters (objects that follow the
53 Visitor pattern) will be responsible for converting that common language into
58 The AST will be a HoA (hash of arrays). The keys to the hash will be the various
59 clauses of a SQL statement, plus some metadata keys. All metadata keys will be
60 identifiable as such by being prefixed with an underscore. All keys will be in
65 These are the additional metadata keys that the AST provides for.
71 This denotes what kind of query this AST should be interpreted as.
77 =head2 Structural units
79 Structural units in the AST are supported by loaded components. L<SQL::Abstract>
80 provides for the following structural units by default:
84 This is a (potentially) fully canonicalized identifier for a table or column. Is
85 is of the structure C< [schema][sep][table][sep]column > or
86 C< [schema][sep]table >.
88 In the case of a two-element identifier which could be C< table[sep]column > or
89 C< schema[sep]table >, context will determine which it is. However, the AST
90 doesn't care which it is, only that it properly parses.
94 A Constant is a Perl scalar. It may either be a String (quoted series of
95 characters) or a number (unquoted).
99 A Function is anything of the form C< name( arglist ) > where C<name> is a
100 string and C<arglist> is a comma-separated list of Expressions.
102 Yes, a Subquery is legal as an argument for many functions.
106 A Subquery is another AST whose _query metadata parameter is set to "SELECT".
108 Most places that a Subquery can be used would require a single value to be
109 returned (single column, single row), but that is not something that the AST can
110 easily enforce. The single-column restriction can possibly be enforced, but the
111 single-row restriction is much more difficult and, in most cases, probably
114 =head3 Unary Operator
116 A UnaryOperator takes a single argument on the RHS and is one of the following:
124 =head3 BinaryOperator
126 A BinaryOperator takes two arguments (one on the LHS and one on the RHS) and is
127 one of the following:
145 =item * C<< IS NOT >>
149 =item * C<< NOT IN >>
153 Note that an operator can comprise of what would be multiple tokens in a normal
158 An expression can be any one of the following:
168 =item * UnaryOperator Expression
170 =item * Expression BinaryOperator Expression
176 The expected clauses are (name and structure):
180 This corresponds to the SELECT clause of a SELECT statement. It maps to a comma-
181 separated list of the following construct C< Expression [ [ AS ] String ] >
182 (where the [] indicate optional items).
186 This is a list of tables that this clause is affecting. It corresponds to the
187 FROM clause in a SELECT statement and the UPDATE/DELETE clauses in those
188 respective statements. Depending on the _query metadata entry, the appropriate
189 clause name will be used.
191 The tables clause has several RDBMS-specific variations. The AST will support
192 all of them and it is up to the Visitor object constructing the actual SQL to
193 validate and/or use what is provided as appropriate.
195 A table clause is composed as follows:
197 TableIdentifier := Identifier [ [ AS ] String ]
198 JoinType := < LEFT|RIGHT [ OUTER ] > | < INNER >
202 < , TableIdentifier >
204 [ JoinType ] JOIN TableIdentifier
206 < USING ( Identifier [ , Identifier ] ) >
207 | < ON [ ( ] Expression [ , Expression ] [ ) ] >
212 Additionally, where aliases are provided for in the TableIdentifier, those
213 aliases must be used as the tablename in subsequent Identifiers that identify a
214 column of that table.
234 robkinyon: Rob Kinyon <rkinyon@cpan.org>
238 You may distribute this code under the same terms as Perl itself.