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