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