projects / dbsrgits/SQL-Abstract-2.0-ish.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | history | HEAD
Added example of DELETE
[dbsrgits/SQL-Abstract-2.0-ish.git] / lib / SQL / Abstract / Manual / Examples.pod
CommitLineData
37f2cc3f 1=head1 NAME
2
3SQL::Abstract::Manual::Specification
4
5=head1 DESCRIPTION
6
7These are the examples for the AST
8
9=head1 EXAMPLES
10
11The following are example SQL statements and the AST that would represent each
12one. 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',
da93022e 26 subtype => 'Number',
37f2cc3f 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',
da93022e 71 subtype => 'Number',
37f2cc3f 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 => {
da93022e 387 type => 'Value',
388 subtype => 'Number',
da74c1c8 389 value => 1,
390 },
391 },
392 {
393 type => 'GroupbyComponent',
394 value => {
da93022e 395 type => 'Value',
396 subtype => 'Number',
da74c1c8 397 value => 3,
398 },
399 },
400 {
401 type => 'GroupbyComponent',
402 value => {
da93022e 403 type => 'Value',
404 subtype => 'Number',
da74c1c8 405 value => 2,
406 },
407 },
408 ],
409 }
410
753e226d 411=item * UPDATE foo SET col1 = 1
412
413 {
414 type => 'update',
415 ast_version => 0.0001,
416 tables => {
417 type => 'TableIdentifier',
418 value => {
419 type => 'Identifier',
420 element1 => 'foo',
421 },
422 },
423 set => [
424 {
425 type => 'SetComponent',
426 col => {
427 type => 'Identifier,
428 element1 => 'col1',
429 },
430 value => {
da93022e 431 type => 'Value',
432 subtype => 'Number',
753e226d 433 value => 1,
434 },
435 },
436 ],
437 }
438
338df86b 439=item * INSERT INTO foo ( col1, col2 ) VALUES ( 1, 3 )
440
441 {
442 type => 'insert',
443 ast_version => 0.0001,
444 tables => {
445 type => 'TableIdentifier',
446 value => {
447 type => 'Identifier',
448 element1 => 'foo',
449 },
450 },
451 columns => [
452 {
453 type => 'Identifier,
454 element1 => 'col1',
455 },
456 {
457 type => 'Identifier,
458 element1 => 'col2',
459 },
460 ],
461 values => [
462 {
da93022e 463 type => 'Value',
464 subtype => 'Number',
338df86b 465 value => '1',
466 },
467 {
da93022e 468 type => 'Value',
469 subtype => 'Number',
338df86b 470 value => '3',
471 },
472 ],
473 }
474
da93022e 475=item * DELETE FROM foo WHERE col1 = 10
476
477 {
478 type => 'delete',
479 ast_version => 0.0001,
480 tables => {
481 type => 'TableIdentifier',
482 value => {
483 type => 'Identifier',
484 element1 => 'foo',
485 },
486 },
487 where => {
488 type => 'Operator',
489 op => '=',
490 args => [
491 {
492 type => 'Identifier',
493 element1 => 'col1',
494 },
495 {
496 type => 'Value',
497 subtype => 'Number',
498 value => 10,
499 },
500 ],
501 },
502 }
503
37f2cc3f 504=back
505
506=head1 AUTHORS
507
508robkinyon: Rob Kinyon C<< <rkinyon@cpan.org> >>
509
510=head1 LICENSE
511
512You may distribute this code under the same terms as Perl itself.
513
514=cut
SQL::Abstract v2.0