projects / dbsrgits/SQL-Abstract-2.0-ish.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | history | HEAD
Added INSERT with example
[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',
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
338df86b 435=item * INSERT INTO foo ( col1, col2 ) VALUES ( 1, 3 )
436
437 {
438 type => 'insert',
439 ast_version => 0.0001,
440 tables => {
441 type => 'TableIdentifier',
442 value => {
443 type => 'Identifier',
444 element1 => 'foo',
445 },
446 },
447 columns => [
448 {
449 type => 'Identifier,
450 element1 => 'col1',
451 },
452 {
453 type => 'Identifier,
454 element1 => 'col2',
455 },
456 ],
457 values => [
458 {
459 type => 'Number,
460 value => '1',
461 },
462 {
463 type => 'Number,
464 value => '3',
465 },
466 ],
467 }
468
37f2cc3f 469=back
470
471=head1 AUTHORS
472
473robkinyon: Rob Kinyon C<< <rkinyon@cpan.org> >>
474
475=head1 LICENSE
476
477You may distribute this code under the same terms as Perl itself.
478
479=cut
SQL::Abstract v2.0