Commit | Line | Data |
d6e108eb |
1 | =head1 NAME |
2 | |
3 | SQL::Abstract::Manual::Specification |
4 | |
5 | =head1 SYNOPSIS |
6 | |
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. |
11 | |
12 | =head1 MOTIVATIONS |
13 | |
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. |
20 | |
21 | =head1 GOALS |
22 | |
23 | The goals for this AST are as follows: |
24 | |
25 | =head2 SQL-specific semantics |
26 | |
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 |
30 | as placeholders. |
31 | |
32 | =head2 Perl-specific semantics |
33 | |
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. |
37 | |
38 | =head2 Whole-lifecycle management |
39 | |
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. |
45 | |
46 | =head2 Dialect-agnostic usage |
47 | |
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 |
393a4eb8 |
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. |
54 | |
55 | =head1 COMPONENTS |
56 | |
57 | There are two major components to SQL::Abstract v2. |
58 | |
59 | =over 4 |
60 | |
61 | =item * AST |
62 | |
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 |
65 | user requires. |
66 | |
67 | =item * Visitor |
68 | |
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. |
72 | |
73 | =back |
d6e108eb |
74 | |
df35a525 |
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. |
80 | |
d6e108eb |
81 | =head1 AST STRUCTURE |
82 | |
393a4eb8 |
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. |
d6e108eb |
87 | |
88 | =head2 Metadata keys |
89 | |
90 | These are the additional metadata keys that the AST provides for. |
91 | |
df35a525 |
92 | =head3 _query |
93 | |
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. |
98 | |
99 | All Visitors are expected to handle the following values for _query: |
100 | |
d6e108eb |
101 | =over 4 |
102 | |
df35a525 |
103 | =item * select |
104 | |
105 | This is a SELECT statement. |
d6e108eb |
106 | |
df35a525 |
107 | =item * insert |
d6e108eb |
108 | |
df35a525 |
109 | This is an INSERT statement. |
393a4eb8 |
110 | |
df35a525 |
111 | =item * update |
112 | |
113 | This is an UPDATE statement. |
114 | |
115 | =item * delete |
116 | |
117 | This is a DELETE statement. |
d6e108eb |
118 | |
119 | =back |
120 | |
df35a525 |
121 | =head3 _version |
122 | |
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 |
125 | and desired. |
126 | |
d6e108eb |
127 | =head2 Structural units |
128 | |
df35a525 |
129 | All structural units will be hashes. These hashes will have, at minimum, the |
130 | following keys: |
131 | |
132 | =over 4 |
133 | |
134 | =item * _name |
135 | |
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. |
140 | |
141 | =back |
142 | |
d6e108eb |
143 | Structural units in the AST are supported by loaded components. L<SQL::Abstract> |
144 | provides for the following structural units by default: |
145 | |
146 | =head3 Identifier |
147 | |
df35a525 |
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. |
152 | |
153 | The hash will be structured as follows: |
154 | |
155 | { |
a3872878 |
156 | _name => 'Identifier', |
df35a525 |
157 | items => [String], |
158 | } |
d6e108eb |
159 | |
df35a525 |
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. |
d6e108eb |
162 | |
10000e9e |
163 | =head3 Value |
d6e108eb |
164 | |
10000e9e |
165 | A Value is a Perl scalar. It may either be a: |
166 | |
167 | =over 4 |
168 | |
169 | =item * String |
170 | |
171 | A String is a quoted series of characters |
172 | |
173 | =item * Number |
174 | |
175 | A Number is an unquoted number in some numeric format |
176 | |
177 | =item * NULL |
178 | |
393a4eb8 |
179 | NULL is SQL's NULL and corresponds to Perl's C<undef>. |
10000e9e |
180 | |
181 | =item * BindParameter |
182 | |
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.) |
186 | |
187 | =back |
188 | |
a3872878 |
189 | The hash will be structured as follows: |
190 | |
191 | { |
192 | _name => 'Value' |
193 | _subtype => [ 'String' | 'Number' | 'NULL' | 'BindParameter' ] |
194 | value => [Scalar] |
195 | } |
196 | |
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. |
d6e108eb |
200 | |
201 | =head3 Function |
202 | |
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. |
205 | |
81cd86f1 |
206 | Yes, a Subquery is legal as an argument for many functions. Some example |
207 | functions are: |
208 | |
209 | =over 4 |
210 | |
81cd86f1 |
211 | =item * C<< MAX >> |
212 | |
213 | =item * C<< MIN >> |
214 | |
215 | =item * C<< SUM >> |
216 | |
217 | =back |
d6e108eb |
218 | |
219 | =head3 Subquery |
220 | |
221 | A Subquery is another AST whose _query metadata parameter is set to "SELECT". |
222 | |
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 |
227 | impossible. |
228 | |
81cd86f1 |
229 | Subqueries, when expressed in SQL, must bounded by parentheses. |
230 | |
d6e108eb |
231 | =head3 Unary Operator |
232 | |
233 | A UnaryOperator takes a single argument on the RHS and is one of the following: |
234 | |
235 | =over 4 |
236 | |
237 | =item * C<< NOT >> |
238 | |
239 | =back |
240 | |
241 | =head3 BinaryOperator |
242 | |
a3872878 |
243 | A BinaryOperator takes two arguments (one on the LHS and one on the RHS). |
244 | |
245 | Examples of BinaryOperators would include: |
d6e108eb |
246 | |
247 | =over 4 |
248 | |
a3872878 |
249 | =item * X = Y |
250 | |
251 | =item * X != Y |
d6e108eb |
252 | |
a3872878 |
253 | =item * X > Y |
d6e108eb |
254 | |
a3872878 |
255 | =item * X < Y |
d6e108eb |
256 | |
a3872878 |
257 | =item * X >= Y |
d6e108eb |
258 | |
a3872878 |
259 | =item * X <= Y |
d6e108eb |
260 | |
a3872878 |
261 | =item * X IS Y |
d6e108eb |
262 | |
a3872878 |
263 | =item * X IS NOT Y |
d6e108eb |
264 | |
a3872878 |
265 | =item * X IN Y |
d6e108eb |
266 | |
d6e108eb |
267 | =back |
268 | |
269 | Note that an operator can comprise of what would be multiple tokens in a normal |
270 | parsing effort. |
271 | |
a3872878 |
272 | =head3 TrinaryOperator |
273 | |
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 |
276 | of the elements. |
277 | |
278 | Examples of TrinaryOperators would include: |
279 | |
280 | =over 4 |
281 | |
282 | =item * X BETWEEN Y AND Z |
283 | |
284 | =back |
285 | |
d6e108eb |
286 | =head3 Expression |
287 | |
288 | An expression can be any one of the following: |
289 | |
290 | =over 4 |
291 | |
10000e9e |
292 | =item * Value |
d6e108eb |
293 | |
294 | =item * Function |
295 | |
296 | =item * Subquery |
297 | |
298 | =item * UnaryOperator Expression |
299 | |
300 | =item * Expression BinaryOperator Expression |
301 | |
81cd86f1 |
302 | =item * ( Expression ) |
303 | |
d6e108eb |
304 | =back |
305 | |
81cd86f1 |
306 | Parentheses indicate precedence and, in some situations, are necessary for |
307 | certain operators. |
308 | |
d6e108eb |
309 | =head2 SQL clauses |
310 | |
10000e9e |
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. |
316 | |
317 | The clauses are defined with a yacc-like syntax. The various parts are: |
318 | |
319 | =over 4 |
320 | |
321 | =item * := |
322 | |
323 | This means "defined" and is used to create a new term to be used below. |
324 | |
325 | =item * [] |
326 | |
327 | This means optional and indicates that the items within it are optional. |
328 | |
329 | =item * []* |
330 | |
331 | This means optional and repeating as many times as desired. |
332 | |
333 | =item * | |
334 | |
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. |
337 | |
338 | =item * C<< <> >> |
339 | |
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. |
342 | |
343 | =back |
344 | |
d6e108eb |
345 | The expected clauses are (name and structure): |
346 | |
347 | =head3 select |
348 | |
81cd86f1 |
349 | This corresponds to the SELECT clause of a SELECT statement. |
350 | |
351 | A select clause is composed as follows: |
352 | |
353 | SelectComponent := Expression [ [ AS ] String ] |
354 | |
355 | SelectComponent |
356 | [ , SelectComponent ]* |
d6e108eb |
357 | |
358 | =head3 tables |
359 | |
360 | This is a list of tables that this clause is affecting. It corresponds to the |
81cd86f1 |
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. |
d6e108eb |
364 | |
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. |
368 | |
369 | A table clause is composed as follows: |
370 | |
371 | TableIdentifier := Identifier [ [ AS ] String ] |
81cd86f1 |
372 | JoinType := < LEFT|RIGHT [ OUTER ] > | INNER | CROSS |
d6e108eb |
373 | |
374 | TableIdentifier |
375 | [ |
376 | < , TableIdentifier > |
377 | | < |
378 | [ JoinType ] JOIN TableIdentifier |
379 | [ |
380 | < USING ( Identifier [ , Identifier ] ) > |
381 | | < ON [ ( ] Expression [ , Expression ] [ ) ] > |
382 | ] |
383 | > |
384 | ]* |
385 | |
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. |
389 | |
390 | =head3 where |
391 | |
81cd86f1 |
392 | This corresponds to the WHERE clause in a SELECT, UPDATE, or DELETE statement. |
393 | |
394 | A where clause is composed as follows: |
395 | |
396 | WhereOperator := AND | OR |
397 | WhereExpression := Expression | Expression WhereOperator Expression |
398 | |
399 | WhereExpression |
400 | |
d6e108eb |
401 | =head3 set |
402 | |
81cd86f1 |
403 | This corresponds to the SET clause in an INSERT or UPDATE statement. |
404 | |
405 | A set clause is composed as follows: |
406 | |
407 | SetComponent := Identifier = Expression |
408 | |
409 | SetComponent [ , SetComponent ]* |
410 | |
411 | =head3 columns |
412 | |
413 | This corresponds to the optional list of columns in an INSERT statement. |
414 | |
415 | A columns clause is composed as follows: |
416 | |
417 | ( Identifier [ , Identifier ]* ) |
418 | |
d6e108eb |
419 | =head3 values |
420 | |
81cd86f1 |
421 | This corresponds to the VALUES clause in an INSERT statement. |
422 | |
423 | A values clause is composed as follows: |
424 | |
425 | ( Expression [ , Expression ]* ) |
426 | |
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. |
429 | |
d6e108eb |
430 | =head3 orderby |
431 | |
81cd86f1 |
432 | This corresponds to the ORDER BY clause in a SELECT statement. |
433 | |
434 | An orderby clause is composed as follows: |
435 | |
10000e9e |
436 | OrderByComponent := XXX-TODO-XXX |
81cd86f1 |
437 | OrderByDirection := ASC | DESC |
438 | |
439 | OrderByComponent [ OrderByDirection ] |
440 | [ , OrderByComponent [ OrderByDirection ] ]* |
441 | |
d6e108eb |
442 | =head3 groupby |
443 | |
81cd86f1 |
444 | This corresponds to the GROUP BY clause in a SELECT statement. |
445 | |
446 | An groupby clause is composed as follows: |
447 | |
10000e9e |
448 | GroupByComponent := XXX-TODO-XXX |
81cd86f1 |
449 | |
450 | GroupByComponent [ , GroupByComponent ]* |
451 | |
d6e108eb |
452 | =head3 rows |
453 | |
81cd86f1 |
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. |
456 | |
457 | A rows clause is composed as follows: |
458 | |
459 | Number [, Number ] |
460 | |
d6e108eb |
461 | =head3 for |
462 | |
81cd86f1 |
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. |
465 | |
466 | A for clause is composed as follows: |
467 | |
468 | UPDATE | DELETE |
469 | |
470 | =head3 connectby |
471 | |
472 | This corresponds to the clause that is used in some RDBMS engines to provide for |
473 | an adjacency-list query. |
474 | |
475 | A connectby clause is composed as follows: |
476 | |
477 | Identifier, WhereExpression |
478 | |
d6e108eb |
479 | =head1 AUTHORS |
480 | |
81cd86f1 |
481 | robkinyon: Rob Kinyon C<< <rkinyon@cpan.org> >> |
d6e108eb |
482 | |
483 | =head1 LICENSE |
484 | |
485 | You may distribute this code under the same terms as Perl itself. |
486 | |
487 | =cut |