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', |
44 | element1 => 'time', |
45 | }, |
37f2cc3f |
46 | }, |
47 | ], |
48 | tables => { |
662b716d |
49 | type => 'Alias', |
37f2cc3f |
50 | value => { |
51 | type => 'Identifier', |
52 | element1 => 'dual', |
53 | }, |
3004ebb7 |
54 | as => { |
55 | type => 'Identifier', |
56 | element1 => 'duality', |
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', |
79 | element1 => 'foo', |
37f2cc3f |
80 | }, |
81 | { |
662b716d |
82 | type => 'Identifier', |
83 | element1 => 'bar', |
37f2cc3f |
84 | }, |
85 | ], |
86 | on => { |
87 | type => 'Operator', |
88 | op => '=', |
89 | args => [ |
90 | { |
91 | type => 'Identifier', |
92 | element1 => 'foo', |
93 | element2 => 'col1', |
94 | }, |
95 | { |
96 | type => 'Identifier', |
97 | element1 => 'bar', |
98 | element2 => 'col2', |
99 | }, |
100 | ], |
101 | }, |
102 | }, |
103 | } |
104 | |
9c604aeb |
105 | =item SELECT * FROM foo WHERE name = 'John' |
106 | |
107 | { |
108 | type => 'select', |
109 | ast_version => 0.0001, |
110 | select => [ |
111 | { |
662b716d |
112 | type => 'Identifier', |
113 | element1 => '*', |
9c604aeb |
114 | }, |
115 | ], |
116 | tables => { |
662b716d |
117 | type => 'Identifier', |
118 | element1 => 'foo', |
9c604aeb |
119 | }, |
120 | where => { |
121 | type => 'Operator', |
122 | op => '=', |
123 | args => [ |
124 | { |
125 | type => 'Identifier', |
126 | element1 => 'name', |
127 | }, |
128 | { |
129 | type => 'Value', |
130 | subtype => 'String', |
131 | element1 => 'John', |
132 | }, |
133 | ], |
4f6e8987 |
134 | }, |
9c604aeb |
135 | } |
136 | |
4f6e8987 |
137 | =item SELECT COUNT(*) FROM foo WHERE name = 'John' AND ( title = 'Mr' OR abbrev = 'Dr' ) |
138 | |
139 | { |
140 | type => 'select', |
141 | ast_version => 0.0001, |
142 | select => [ |
143 | { |
662b716d |
144 | type => 'Operator', |
145 | op => 'COUNT', |
146 | args => [ |
147 | { |
148 | type => 'Identifier', |
149 | element1 => '*', |
150 | }, |
151 | ], |
4f6e8987 |
152 | }, |
153 | ], |
154 | tables => { |
662b716d |
155 | type => 'Identifier', |
156 | element1 => 'foo', |
4f6e8987 |
157 | }, |
158 | where => { |
159 | type => 'Operator', |
160 | op => 'AND', |
161 | args => [ |
162 | { |
163 | type => 'Operator', |
164 | op => '=', |
165 | args => [ |
166 | { |
167 | type => 'Identifier', |
168 | element1 => 'name', |
169 | }, |
170 | { |
171 | type => 'Value', |
172 | subtype => 'String', |
173 | element1 => 'John', |
174 | }, |
175 | ], |
176 | }, |
177 | { |
3d8ddf0b |
178 | type => 'Operator', |
179 | op => 'OR', |
4f6e8987 |
180 | args => [ |
181 | { |
182 | type => 'Operator', |
3d8ddf0b |
183 | op => '=', |
184 | args => [ |
185 | { |
186 | type => 'Identifier', |
187 | element1 => 'title', |
188 | }, |
189 | { |
190 | type => 'Value', |
191 | subtype => 'String', |
192 | element1 => 'Mr', |
193 | }, |
194 | ], |
195 | }, |
196 | { |
197 | type => 'Operator', |
198 | op => '=', |
4f6e8987 |
199 | args => [ |
200 | { |
3d8ddf0b |
201 | type => 'Identifier', |
202 | element1 => 'abbrev', |
4f6e8987 |
203 | }, |
204 | { |
3d8ddf0b |
205 | type => 'Value', |
206 | subtype => 'String', |
207 | element1 => 'Dr', |
4f6e8987 |
208 | }, |
209 | ], |
210 | }, |
211 | ], |
212 | }, |
213 | ], |
214 | }, |
215 | } |
216 | |
de1a11fa |
217 | =item SELECT COUNT(DISTINCT(*)) FROM foo WHERE ( name = 'John' AND title = 'Mr' ) OR abbrev = 'Dr' |
3d8ddf0b |
218 | |
219 | { |
220 | type => 'select', |
221 | ast_version => 0.0001, |
222 | select => [ |
de1a11fa |
223 | { |
662b716d |
224 | type => 'Operator', |
225 | op => 'COUNT', |
226 | args => [ |
227 | { |
228 | type => 'Operator', |
229 | op => 'DISTINCT', |
230 | args => [ |
231 | { |
232 | type => 'Identifier', |
233 | element1 => '*', |
234 | }, |
235 | ], |
236 | }, |
237 | ], |
3d8ddf0b |
238 | }, |
239 | ], |
240 | tables => { |
662b716d |
241 | type => 'Identifier', |
242 | element1 => 'foo', |
3d8ddf0b |
243 | }, |
244 | where => { |
245 | type => 'Operator', |
246 | op => 'OR', |
247 | args => [ |
248 | { |
249 | type => 'Operator', |
250 | op => 'AND', |
251 | args => [ |
252 | { |
253 | type => 'Operator', |
254 | op => '=', |
255 | args => [ |
256 | { |
257 | type => 'Identifier', |
258 | element1 => 'name', |
259 | }, |
260 | { |
261 | type => 'Value', |
262 | subtype => 'String', |
263 | element1 => 'John', |
264 | }, |
265 | ], |
266 | }, |
267 | { |
268 | type => 'Operator', |
269 | op => '=', |
270 | args => [ |
271 | { |
272 | type => 'Identifier', |
273 | element1 => 'title', |
274 | }, |
275 | { |
276 | type => 'Value', |
277 | subtype => 'String', |
278 | element1 => 'Mr', |
279 | }, |
280 | ], |
281 | }, |
282 | ], |
283 | }, |
284 | { |
285 | type => 'Operator', |
286 | op => '=', |
287 | args => [ |
288 | { |
289 | type => 'Identifier', |
290 | element1 => 'abbrev', |
291 | }, |
292 | { |
293 | type => 'Value', |
294 | subtype => 'String', |
295 | element1 => 'Dr', |
296 | }, |
297 | ], |
298 | }, |
299 | ], |
300 | }, |
301 | } |
302 | |
da74c1c8 |
303 | =item * SELECT foo, bar baz FROM foo ORDER BY bar, baz DESC GROUP BY 1,3,2 |
304 | |
305 | { |
306 | type => 'select', |
307 | ast_version => 0.0001, |
308 | select => [ |
309 | { |
662b716d |
310 | type => 'Identifier', |
311 | element1 => 'foo', |
da74c1c8 |
312 | }, |
313 | { |
662b716d |
314 | type => 'Identifier', |
315 | elements => 'bar', |
da74c1c8 |
316 | }, |
317 | { |
662b716d |
318 | type => 'Identifier', |
319 | element1 => 'baz', |
da74c1c8 |
320 | }, |
321 | ], |
322 | tables => { |
662b716d |
323 | type => 'Identifier', |
324 | element1 => 'foo', |
da74c1c8 |
325 | }, |
326 | orderby => [ |
327 | { |
328 | type => 'OrderbyComponent', |
329 | value => { |
330 | type => 'Identifier', |
331 | element1 => 'bar', |
332 | }, |
333 | dir => 'ASC', |
334 | }, |
335 | { |
336 | type => 'OrderbyComponent', |
337 | value => { |
338 | type => 'Identifier', |
339 | element1 => 'baz', |
340 | }, |
341 | dir => 'DESC', |
342 | }, |
343 | ], |
344 | groupby => [ |
345 | { |
346 | type => 'GroupbyComponent', |
347 | value => { |
da93022e |
348 | type => 'Value', |
349 | subtype => 'Number', |
da74c1c8 |
350 | value => 1, |
351 | }, |
352 | }, |
353 | { |
354 | type => 'GroupbyComponent', |
355 | value => { |
da93022e |
356 | type => 'Value', |
357 | subtype => 'Number', |
da74c1c8 |
358 | value => 3, |
359 | }, |
360 | }, |
361 | { |
362 | type => 'GroupbyComponent', |
363 | value => { |
da93022e |
364 | type => 'Value', |
365 | subtype => 'Number', |
da74c1c8 |
366 | value => 2, |
367 | }, |
368 | }, |
369 | ], |
370 | } |
371 | |
753e226d |
372 | =item * UPDATE foo SET col1 = 1 |
373 | |
374 | { |
375 | type => 'update', |
376 | ast_version => 0.0001, |
377 | tables => { |
662b716d |
378 | type => 'Identifier', |
379 | element1 => 'foo', |
753e226d |
380 | }, |
381 | set => [ |
382 | { |
383 | type => 'SetComponent', |
384 | col => { |
385 | type => 'Identifier, |
386 | element1 => 'col1', |
387 | }, |
388 | value => { |
da93022e |
389 | type => 'Value', |
390 | subtype => 'Number', |
753e226d |
391 | value => 1, |
392 | }, |
393 | }, |
394 | ], |
395 | } |
396 | |
338df86b |
397 | =item * INSERT INTO foo ( col1, col2 ) VALUES ( 1, 3 ) |
398 | |
399 | { |
400 | type => 'insert', |
401 | ast_version => 0.0001, |
402 | tables => { |
662b716d |
403 | type => 'Identifier', |
404 | element1 => 'foo', |
338df86b |
405 | }, |
406 | columns => [ |
407 | { |
408 | type => 'Identifier, |
409 | element1 => 'col1', |
410 | }, |
411 | { |
412 | type => 'Identifier, |
413 | element1 => 'col2', |
414 | }, |
415 | ], |
416 | values => [ |
417 | { |
da93022e |
418 | type => 'Value', |
419 | subtype => 'Number', |
338df86b |
420 | value => '1', |
421 | }, |
422 | { |
da93022e |
423 | type => 'Value', |
424 | subtype => 'Number', |
338df86b |
425 | value => '3', |
426 | }, |
427 | ], |
428 | } |
429 | |
da93022e |
430 | =item * DELETE FROM foo WHERE col1 = 10 |
431 | |
432 | { |
433 | type => 'delete', |
434 | ast_version => 0.0001, |
435 | tables => { |
662b716d |
436 | type => 'Identifier', |
437 | element1 => 'foo', |
da93022e |
438 | }, |
439 | where => { |
440 | type => 'Operator', |
441 | op => '=', |
442 | args => [ |
443 | { |
444 | type => 'Identifier', |
445 | element1 => 'col1', |
446 | }, |
447 | { |
448 | type => 'Value', |
449 | subtype => 'Number', |
450 | value => 10, |
451 | }, |
452 | ], |
453 | }, |
454 | } |
455 | |
3004ebb7 |
456 | =item * SELECT * FROM ( SELECT 1 ) AS foo |
457 | |
458 | { |
459 | type => 'select', |
460 | ast_version => 0.0001, |
461 | select => [ |
462 | { |
463 | type => 'Identifier', |
464 | element1 => '*', |
465 | }, |
466 | ], |
467 | tables => { |
468 | type => 'Identifier', |
469 | element1 => 'foo', |
470 | value => { |
471 | type => 'select', |
472 | ast_version => 0.0001, |
473 | select => [ |
474 | { |
475 | type => 'Value', |
476 | subtype => 'Number', |
477 | value => 1, |
478 | }, |
479 | ], |
480 | }, |
481 | as => { |
482 | type => 'Identifier', |
483 | element1 => 'foo', |
484 | }, |
485 | }, |
486 | } |
487 | |
37f2cc3f |
488 | =back |
489 | |
490 | =head1 AUTHORS |
491 | |
492 | robkinyon: Rob Kinyon C<< <rkinyon@cpan.org> >> |
493 | |
494 | =head1 LICENSE |
495 | |
496 | You may distribute this code under the same terms as Perl itself. |
497 | |
498 | =cut |