Commit | Line | Data |
37f2cc3f |
1 | =head1 NAME |
2 | |
3 | SQL::Abstract::Manual::Specification |
4 | |
5 | =head1 DESCRIPTION |
6 | |
7 | These are the examples for the AST |
8 | |
9 | =head1 EXAMPLES |
10 | |
11 | The following are example SQL statements and the AST that would represent each |
12 | one. The SQL used is from the MySQL dialect. |
13 | |
14 | =over 4 |
15 | |
16 | =item * SELECT 1 |
17 | |
18 | { |
19 | type => 'select', |
20 | ast_version => 0.0001, |
21 | select => [ |
22 | { |
662b716d |
23 | type => 'Value', |
24 | subtype => 'Number', |
25 | value => 1, |
37f2cc3f |
26 | }, |
27 | ], |
28 | } |
29 | |
30 | =item * SELECT NOW() AS time FROM dual AS duality |
31 | |
32 | { |
33 | type => 'select', |
34 | ast_version => 0.0001, |
35 | select => [ |
36 | { |
662b716d |
37 | type => 'Alias', |
37f2cc3f |
38 | value => { |
39 | type => 'Function', |
40 | function => 'NOW', |
41 | }, |
3004ebb7 |
42 | as => { |
43 | type => 'Identifier', |
6df2732a |
44 | args => [ 'time' ], |
3004ebb7 |
45 | }, |
37f2cc3f |
46 | }, |
47 | ], |
48 | tables => { |
662b716d |
49 | type => 'Alias', |
37f2cc3f |
50 | value => { |
51 | type => 'Identifier', |
6df2732a |
52 | args => [ 'dual' ], |
37f2cc3f |
53 | }, |
3004ebb7 |
54 | as => { |
55 | type => 'Identifier', |
6df2732a |
56 | args => [ 'duality' ], |
3004ebb7 |
57 | }, |
37f2cc3f |
58 | }, |
59 | } |
60 | |
61 | =item * SELECT 1 FROM foo LEFT OUTER JOIN bar ON ( foo.col1 = bar.col2 ) |
62 | |
63 | { |
64 | type => 'select', |
65 | ast_version => 0.0001, |
66 | select => [ |
67 | { |
662b716d |
68 | type => 'Value', |
69 | subtype => 'Number', |
70 | value => 1, |
37f2cc3f |
71 | }, |
72 | ], |
73 | tables => { |
74 | type => 'Operator', |
75 | op => 'LEFT OUTER', |
76 | args => [ |
77 | { |
662b716d |
78 | type => 'Identifier', |
6df2732a |
79 | args => [ 'foo' ], |
37f2cc3f |
80 | }, |
81 | { |
662b716d |
82 | type => 'Identifier', |
6df2732a |
83 | args => [ 'bar' ], |
37f2cc3f |
84 | }, |
85 | ], |
86 | on => { |
87 | type => 'Operator', |
88 | op => '=', |
89 | args => [ |
90 | { |
6df2732a |
91 | type => 'Identifier', |
92 | args => [ 'foo', 'col1' ], |
37f2cc3f |
93 | }, |
94 | { |
6df2732a |
95 | type => 'Identifier', |
96 | args => [ 'bar', 'col2' ], |
37f2cc3f |
97 | }, |
98 | ], |
99 | }, |
100 | }, |
101 | } |
102 | |
9c604aeb |
103 | =item SELECT * FROM foo WHERE name = 'John' |
104 | |
105 | { |
106 | type => 'select', |
107 | ast_version => 0.0001, |
108 | select => [ |
109 | { |
6df2732a |
110 | type => 'Identifier', |
111 | args => [ '*' ], |
9c604aeb |
112 | }, |
113 | ], |
114 | tables => { |
662b716d |
115 | type => 'Identifier', |
6df2732a |
116 | args => [ 'foo' ], |
9c604aeb |
117 | }, |
118 | where => { |
119 | type => 'Operator', |
120 | op => '=', |
121 | args => [ |
122 | { |
6df2732a |
123 | type => 'Identifier', |
124 | args => [ 'name' ], |
9c604aeb |
125 | }, |
126 | { |
6df2732a |
127 | type => 'Value', |
128 | subtype => 'String', |
129 | value => 'John', |
9c604aeb |
130 | }, |
131 | ], |
4f6e8987 |
132 | }, |
9c604aeb |
133 | } |
134 | |
4f6e8987 |
135 | =item SELECT COUNT(*) FROM foo WHERE name = 'John' AND ( title = 'Mr' OR abbrev = 'Dr' ) |
136 | |
137 | { |
138 | type => 'select', |
139 | ast_version => 0.0001, |
140 | select => [ |
141 | { |
662b716d |
142 | type => 'Operator', |
143 | op => 'COUNT', |
144 | args => [ |
145 | { |
6df2732a |
146 | type => 'Identifier', |
147 | args => [ '*' ], |
662b716d |
148 | }, |
149 | ], |
4f6e8987 |
150 | }, |
151 | ], |
152 | tables => { |
662b716d |
153 | type => 'Identifier', |
6df2732a |
154 | args => [ 'foo' ], |
4f6e8987 |
155 | }, |
156 | where => { |
157 | type => 'Operator', |
158 | op => 'AND', |
159 | args => [ |
160 | { |
161 | type => 'Operator', |
162 | op => '=', |
163 | args => [ |
164 | { |
6df2732a |
165 | type => 'Identifier', |
166 | args => [ 'name' ], |
4f6e8987 |
167 | }, |
168 | { |
6df2732a |
169 | type => 'Value', |
170 | subtype => 'String', |
171 | value => 'John', |
4f6e8987 |
172 | }, |
173 | ], |
174 | }, |
175 | { |
3d8ddf0b |
176 | type => 'Operator', |
177 | op => 'OR', |
4f6e8987 |
178 | args => [ |
179 | { |
180 | type => 'Operator', |
3d8ddf0b |
181 | op => '=', |
182 | args => [ |
183 | { |
6df2732a |
184 | type => 'Identifier', |
185 | args => [ 'title' ], |
3d8ddf0b |
186 | }, |
187 | { |
6df2732a |
188 | type => 'Value', |
189 | subtype => 'String', |
190 | value => 'Mr', |
3d8ddf0b |
191 | }, |
192 | ], |
193 | }, |
194 | { |
195 | type => 'Operator', |
196 | op => '=', |
4f6e8987 |
197 | args => [ |
198 | { |
6df2732a |
199 | type => 'Identifier', |
200 | args => [ 'abbrev' ], |
4f6e8987 |
201 | }, |
202 | { |
6df2732a |
203 | type => 'Value', |
204 | subtype => 'String', |
205 | vaue => 'Dr', |
4f6e8987 |
206 | }, |
207 | ], |
208 | }, |
209 | ], |
210 | }, |
211 | ], |
212 | }, |
213 | } |
214 | |
de1a11fa |
215 | =item SELECT COUNT(DISTINCT(*)) FROM foo WHERE ( name = 'John' AND title = 'Mr' ) OR abbrev = 'Dr' |
3d8ddf0b |
216 | |
217 | { |
218 | type => 'select', |
219 | ast_version => 0.0001, |
220 | select => [ |
de1a11fa |
221 | { |
662b716d |
222 | type => 'Operator', |
223 | op => 'COUNT', |
224 | args => [ |
225 | { |
226 | type => 'Operator', |
227 | op => 'DISTINCT', |
228 | args => [ |
229 | { |
6df2732a |
230 | type => 'Identifier', |
231 | args => [ '*' ], |
662b716d |
232 | }, |
233 | ], |
234 | }, |
235 | ], |
3d8ddf0b |
236 | }, |
237 | ], |
238 | tables => { |
662b716d |
239 | type => 'Identifier', |
6df2732a |
240 | args => [ 'foo' ], |
3d8ddf0b |
241 | }, |
242 | where => { |
243 | type => 'Operator', |
244 | op => 'OR', |
245 | args => [ |
246 | { |
247 | type => 'Operator', |
248 | op => 'AND', |
249 | args => [ |
250 | { |
251 | type => 'Operator', |
252 | op => '=', |
253 | args => [ |
254 | { |
6df2732a |
255 | type => 'Identifier', |
256 | args => [ 'name' ], |
3d8ddf0b |
257 | }, |
258 | { |
6df2732a |
259 | type => 'Value', |
260 | subtype => 'String', |
261 | value => 'John', |
3d8ddf0b |
262 | }, |
263 | ], |
264 | }, |
265 | { |
266 | type => 'Operator', |
267 | op => '=', |
268 | args => [ |
269 | { |
6df2732a |
270 | type => 'Identifier', |
271 | args => [ 'title' ], |
3d8ddf0b |
272 | }, |
273 | { |
6df2732a |
274 | type => 'Value', |
275 | subtype => 'String', |
276 | value => 'Mr', |
3d8ddf0b |
277 | }, |
278 | ], |
279 | }, |
280 | ], |
281 | }, |
282 | { |
283 | type => 'Operator', |
284 | op => '=', |
285 | args => [ |
286 | { |
6df2732a |
287 | type => 'Identifier', |
288 | args => [ 'abbrev' ], |
3d8ddf0b |
289 | }, |
290 | { |
291 | type => 'Value', |
292 | subtype => 'String', |
6df2732a |
293 | value => 'Dr', |
3d8ddf0b |
294 | }, |
295 | ], |
296 | }, |
297 | ], |
298 | }, |
299 | } |
300 | |
da74c1c8 |
301 | =item * SELECT foo, bar baz FROM foo ORDER BY bar, baz DESC GROUP BY 1,3,2 |
302 | |
303 | { |
304 | type => 'select', |
305 | ast_version => 0.0001, |
306 | select => [ |
307 | { |
662b716d |
308 | type => 'Identifier', |
6df2732a |
309 | args => [ 'foo' ], |
da74c1c8 |
310 | }, |
311 | { |
662b716d |
312 | type => 'Identifier', |
313 | elements => 'bar', |
da74c1c8 |
314 | }, |
315 | { |
662b716d |
316 | type => 'Identifier', |
6df2732a |
317 | args => [ 'baz' ], |
da74c1c8 |
318 | }, |
319 | ], |
320 | tables => { |
662b716d |
321 | type => 'Identifier', |
6df2732a |
322 | args => [ 'foo' ], |
da74c1c8 |
323 | }, |
324 | orderby => [ |
325 | { |
326 | type => 'OrderbyComponent', |
327 | value => { |
328 | type => 'Identifier', |
6df2732a |
329 | args => [ 'bar' ], |
da74c1c8 |
330 | }, |
331 | dir => 'ASC', |
332 | }, |
333 | { |
334 | type => 'OrderbyComponent', |
335 | value => { |
336 | type => 'Identifier', |
6df2732a |
337 | args => [ 'baz' ], |
da74c1c8 |
338 | }, |
339 | dir => 'DESC', |
340 | }, |
341 | ], |
342 | groupby => [ |
343 | { |
344 | type => 'GroupbyComponent', |
345 | value => { |
da93022e |
346 | type => 'Value', |
347 | subtype => 'Number', |
da74c1c8 |
348 | value => 1, |
349 | }, |
350 | }, |
351 | { |
352 | type => 'GroupbyComponent', |
353 | value => { |
da93022e |
354 | type => 'Value', |
355 | subtype => 'Number', |
da74c1c8 |
356 | value => 3, |
357 | }, |
358 | }, |
359 | { |
360 | type => 'GroupbyComponent', |
361 | value => { |
da93022e |
362 | type => 'Value', |
363 | subtype => 'Number', |
da74c1c8 |
364 | value => 2, |
365 | }, |
366 | }, |
367 | ], |
368 | } |
369 | |
7b7ed1cb |
370 | =item * SELECT * FROM ( SELECT 1 ) AS foo |
753e226d |
371 | |
372 | { |
7b7ed1cb |
373 | type => 'select', |
374 | ast_version => 0.0001, |
375 | select => [ |
376 | { |
377 | type => 'Identifier', |
6df2732a |
378 | args => [ '*' ], |
7b7ed1cb |
379 | }, |
380 | ], |
381 | tables => { |
382 | type => 'Identifier', |
6df2732a |
383 | args => [ 'foo' ], |
7b7ed1cb |
384 | value => { |
385 | type => 'select', |
386 | ast_version => 0.0001, |
387 | select => [ |
388 | { |
389 | type => 'Value', |
390 | subtype => 'Number', |
391 | value => 1, |
392 | }, |
393 | ], |
394 | }, |
395 | as => { |
396 | type => 'Identifier', |
6df2732a |
397 | args => [ 'foo' ], |
7b7ed1cb |
398 | }, |
399 | }, |
400 | } |
401 | |
402 | =item * INSERT INTO foo ( col1, col2 ) VALUES ( 1, 3 ) |
403 | |
404 | { |
405 | type => 'insert', |
753e226d |
406 | ast_version => 0.0001, |
407 | tables => { |
662b716d |
408 | type => 'Identifier', |
6df2732a |
409 | args => [ 'foo' ], |
753e226d |
410 | }, |
411 | set => [ |
a08e7c02 |
412 | [ |
413 | { |
753e226d |
414 | type => 'Identifier, |
6df2732a |
415 | args => [ 'col1' ], |
753e226d |
416 | }, |
7b7ed1cb |
417 | { |
418 | type => 'Identifier, |
6df2732a |
419 | args => [ 'col2' ], |
7b7ed1cb |
420 | }, |
a08e7c02 |
421 | ], |
422 | [ |
423 | { |
da93022e |
424 | type => 'Value', |
425 | subtype => 'Number', |
7b7ed1cb |
426 | value => '1', |
427 | }, |
428 | { |
429 | type => 'Value', |
430 | subtype => 'Number', |
431 | value => '3', |
753e226d |
432 | }, |
a08e7c02 |
433 | ], |
753e226d |
434 | ], |
435 | } |
436 | |
7b7ed1cb |
437 | =item * INSERT INTO foo ( col1, col2 ) VALUES ( 1, 3 ), ( 2, 4 ) |
338df86b |
438 | |
439 | { |
440 | type => 'insert', |
441 | ast_version => 0.0001, |
442 | tables => { |
662b716d |
443 | type => 'Identifier', |
6df2732a |
444 | args => [ 'foo' ], |
338df86b |
445 | }, |
a08e7c02 |
446 | set => [ |
447 | [ |
448 | { |
449 | type => 'Identifier, |
6df2732a |
450 | args => [ 'col1' ], |
a08e7c02 |
451 | }, |
452 | { |
453 | type => 'Identifier, |
6df2732a |
454 | args => [ 'col2' ], |
a08e7c02 |
455 | }, |
456 | ], |
457 | [ |
458 | { |
459 | type => 'Value', |
460 | subtype => 'Number', |
461 | value => '1', |
462 | }, |
463 | { |
464 | type => 'Value', |
465 | subtype => 'Number', |
466 | value => '3', |
467 | }, |
468 | ], |
7b7ed1cb |
469 | [ |
470 | { |
471 | type => 'Value', |
472 | subtype => 'Number', |
473 | value => '2', |
474 | }, |
475 | { |
476 | type => 'Value', |
477 | subtype => 'Number', |
478 | value => '3', |
479 | }, |
480 | ], |
481 | ], |
482 | } |
483 | |
484 | =item * UPDATE foo SET col1 = 1 |
485 | |
486 | { |
487 | type => 'update', |
488 | ast_version => 0.0001, |
489 | tables => { |
490 | type => 'Identifier', |
6df2732a |
491 | args => [ 'foo' ], |
7b7ed1cb |
492 | }, |
493 | set => [ |
494 | [ |
495 | { |
496 | type => 'Identifier, |
6df2732a |
497 | args => [ 'col1' ], |
7b7ed1cb |
498 | }, |
499 | ], |
500 | [ |
501 | { |
502 | type => 'Value', |
503 | subtype => 'Number', |
504 | value => 1, |
505 | }, |
506 | ], |
507 | ], |
508 | } |
509 | |
510 | =item * UPDATE foo SET col1 = 1, col2 = 6 |
511 | |
512 | { |
513 | type => 'update', |
514 | ast_version => 0.0001, |
515 | tables => { |
516 | type => 'Identifier', |
6df2732a |
517 | args => [ 'foo' ], |
7b7ed1cb |
518 | }, |
519 | set => [ |
520 | [ |
521 | { |
522 | type => 'Identifier, |
6df2732a |
523 | args => [ 'col1' ], |
7b7ed1cb |
524 | }, |
525 | { |
526 | type => 'Identifier, |
6df2732a |
527 | args => [ 'col2' ], |
7b7ed1cb |
528 | }, |
529 | ], |
530 | [ |
531 | { |
532 | type => 'Value', |
533 | subtype => 'Number', |
534 | value => 1, |
535 | }, |
536 | { |
537 | type => 'Value', |
538 | subtype => 'Number', |
539 | value => 6, |
540 | }, |
541 | ], |
338df86b |
542 | ], |
543 | } |
544 | |
da93022e |
545 | =item * DELETE FROM foo WHERE col1 = 10 |
546 | |
547 | { |
548 | type => 'delete', |
549 | ast_version => 0.0001, |
550 | tables => { |
662b716d |
551 | type => 'Identifier', |
6df2732a |
552 | args => [ 'foo' ], |
da93022e |
553 | }, |
554 | where => { |
555 | type => 'Operator', |
556 | op => '=', |
557 | args => [ |
558 | { |
6df2732a |
559 | type => 'Identifier', |
560 | args => [ 'col1' ], |
da93022e |
561 | }, |
562 | { |
6df2732a |
563 | type => 'Value', |
564 | subtype => 'Number', |
565 | value => 10, |
da93022e |
566 | }, |
567 | ], |
568 | }, |
569 | } |
570 | |
6df2732a |
571 | =item * INSERT INTO foo ( col1, col2 ) SELECT col1, col2 FROM bar; |
572 | |
573 | { |
574 | type => 'insert', |
575 | ast_version => 0.0001, |
576 | tables => { |
577 | type => 'Identifier', |
578 | args => [ 'foo' ], |
579 | }, |
580 | set => [ |
581 | [ |
582 | { |
583 | type => 'Identifier, |
584 | args => [ 'col1' ], |
585 | }, |
586 | { |
587 | type => 'Identifier, |
588 | args => [ 'col2' ], |
589 | }, |
590 | ], |
591 | [ |
592 | { |
593 | type => 'select', |
594 | ast_version => 0.0001, |
595 | select => [ |
596 | { |
597 | type => 'Identifier', |
598 | args => [ 'col1' ], |
599 | }, |
600 | { |
601 | type => 'Identifier', |
602 | args => [ 'col2' ], |
603 | }, |
604 | ], |
605 | tables => { |
606 | type => 'Identifier', |
607 | args => [ 'bar' ], |
608 | }, |
609 | }, |
610 | ], |
611 | ], |
612 | } |
613 | |
614 | |
37f2cc3f |
615 | =back |
616 | |
617 | =head1 AUTHORS |
618 | |
619 | robkinyon: Rob Kinyon C<< <rkinyon@cpan.org> >> |
620 | |
621 | =head1 LICENSE |
622 | |
623 | You may distribute this code under the same terms as Perl itself. |
624 | |
625 | =cut |