Commit | Line | Data |
79d30ac3 |
1 | package SQL::Abstract::Reference; |
2 | |
3 | 1; |
4 | |
5 | __END__ |
6 | =head1 NAME |
7 | |
8 | SQL::Abstract::Reference - Reference documentation for L<SQL::Abstract> |
9 | |
10 | =head1 TERMS |
11 | |
12 | =head2 Expression (expr) |
13 | |
14 | The DWIM structure that's passed to most methods by default is referred to |
15 | as expression syntax. If you see a variable with C<expr> in the name, or a |
16 | comment before a code block saying C<# expr>, this is what's being described. |
17 | |
18 | =head2 Abstract Query Tree (aqt) |
19 | |
20 | The explicit structure that an expression is converted into before it's |
21 | rendered into SQL is referred to as an abstract query tree. If you see a |
22 | variable with C<aqt> in the name, or a comment before a code block saying |
edf5ac22 |
23 | C<# aqt>, this is what's being described. |
79d30ac3 |
24 | |
25 | =head2 SQL and Bind Values (query) |
26 | |
27 | The final result of L<SQL::Abstract> rendering is generally an SQL statement |
28 | plus bind values for passing to DBI, ala: |
29 | |
30 | my ($sql, @bind) = $sqla->some_method(@args); |
31 | my @hashes = @{$dbh->do($sql, { Slice => {} }, @bind)}; |
32 | |
33 | If you see a comment before a code block saying C<# query>, the SQL + bind |
34 | array is what's being described. |
35 | |
29f96af0 |
36 | =head2 Expander |
37 | |
38 | An expander subroutine is written as: |
39 | |
40 | sub { |
41 | my ($sqla, $name, $value, $k) = @_; |
42 | ... |
43 | return $aqt; |
44 | } |
45 | |
46 | $name is the expr node type for node expanders, the op name for op |
47 | expanders, and the clause name for clause expanders. |
48 | |
49 | $value is the body of the thing being expanded |
50 | |
51 | If an op expander is being called as the binary operator in a L</hashtriple> |
52 | expression, $k will be the hash key to be used as the left hand side |
53 | identifier. |
54 | |
55 | This can trivially be converted to an C<ident> type AQT node with: |
56 | |
57 | my $ident = $sqla->expand_expr({ -ident => $k }); |
58 | |
59 | =head2 Renderer |
60 | |
61 | A renderer subroutine looks like: |
62 | |
63 | sub { |
64 | my ($sqla, $type, $value) = @_; |
65 | ... |
66 | $sqla->join_query_parts($join, @parts); |
67 | } |
68 | |
69 | and can be registered on a per-type, per-op or per-clause basis. |
70 | |
79d30ac3 |
71 | =head1 AQT node types |
72 | |
73 | An AQT node consists of a hashref with a single key, whose name is C<-type> |
74 | where 'type' is the node type, and whose value is the data for the node. |
75 | |
29f96af0 |
76 | The following is an explanation of the built-in AQT type renderers; |
77 | additional renderers can be registered as part of the extension system. |
78 | |
79d30ac3 |
79 | =head2 literal |
80 | |
81 | # expr |
82 | { -literal => [ 'SPANG(?, ?)', 1, 27 ] } |
83 | |
84 | # query |
85 | SPANG(?, ?) |
86 | [ 1, 27 ] |
87 | |
88 | =head2 ident |
89 | |
90 | # expr |
91 | { -ident => 'foo' } |
92 | |
93 | # query |
94 | foo |
95 | [] |
96 | |
97 | # expr |
98 | { -ident => [ 'foo', 'bar' ] } |
99 | |
100 | # query |
101 | foo.bar |
102 | [] |
103 | |
104 | =head2 bind |
105 | |
106 | # expr |
107 | { -bind => [ 'colname', 'value' ] } |
108 | |
109 | # query |
110 | ? |
111 | [ 'value' ] |
112 | |
113 | =head2 row |
114 | |
115 | # expr |
116 | { |
117 | -row => [ { -bind => [ 'r', 1 ] }, { -ident => [ 'clown', 'car' ] } ] |
118 | } |
119 | |
120 | # query |
121 | (?, clown.car) |
122 | [ 1 ] |
123 | |
124 | =head2 func |
125 | |
126 | # expr |
127 | { |
128 | -func => [ 'foo', { -ident => [ 'bar' ] }, { -bind => [ undef, 7 ] } ] |
129 | } |
130 | |
131 | # query |
132 | FOO(bar, ?) |
133 | [ 7 ] |
134 | |
135 | =head2 op |
136 | |
137 | Standard binop: |
138 | |
139 | # expr |
140 | { -op => [ |
141 | '=', { -ident => [ 'bomb', 'status' ] }, |
142 | { -value => 'unexploded' }, |
143 | ] } |
144 | |
145 | # query |
146 | bomb.status = ? |
147 | [ 'unexploded' ] |
148 | |
d4c5cd24 |
149 | |
150 | Prefix unop: |
151 | |
152 | # expr |
153 | { -op => [ '-', { -ident => 'foo' } ] } |
154 | |
155 | # query |
156 | - foo |
157 | [] |
158 | |
159 | Not as special case parenthesised unop: |
79d30ac3 |
160 | |
161 | # expr |
162 | { -op => [ 'not', { -ident => 'explosive' } ] } |
163 | |
164 | # query |
165 | (NOT explosive) |
166 | [] |
167 | |
168 | Postfix unop: (is_null, is_not_null, asc, desc) |
169 | |
170 | # expr |
171 | { -op => [ 'is_null', { -ident => [ 'bobby' ] } ] } |
172 | |
173 | # query |
174 | bobby IS NULL |
175 | [] |
176 | |
177 | AND and OR: |
178 | |
179 | # expr |
180 | { -op => |
181 | [ 'and', { -ident => 'x' }, { -ident => 'y' }, { -ident => 'z' } ] |
182 | } |
183 | |
184 | # query |
185 | ( x AND y AND z ) |
186 | [] |
187 | |
188 | IN (and NOT IN): |
189 | |
190 | # expr |
191 | { -op => [ |
192 | 'in', { -ident => 'card' }, { -bind => [ 'card', 3 ] }, |
193 | { -bind => [ 'card', 'J' ] }, |
194 | ] } |
195 | |
196 | # query |
197 | card IN ( ?, ? ) |
198 | [ 3, 'J' ] |
199 | |
200 | BETWEEN (and NOT BETWEEN): |
201 | |
202 | # expr |
203 | { -op => [ |
204 | 'between', { -ident => 'pints' }, { -bind => [ 'pints', 2 ] }, |
205 | { -bind => [ 'pints', 4 ] }, |
206 | ] } |
207 | |
208 | # query |
209 | ( pints BETWEEN ? AND ? ) |
210 | [ 2, 4 ] |
211 | |
212 | Comma (use -row for parens): |
213 | |
214 | # expr |
215 | { -op => [ ',', { -literal => [ 1 ] }, { -literal => [ 2 ] } ] } |
216 | |
217 | # query |
218 | 1, 2 |
219 | [] |
220 | |
221 | =head2 values |
222 | |
223 | # expr |
224 | { -values => |
225 | { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] } |
226 | } |
227 | |
228 | # query |
229 | VALUES (?, ?) |
230 | [ 1, 2 ] |
231 | |
232 | # expr |
233 | { -values => [ |
234 | { -row => [ { -literal => [ 1 ] }, { -literal => [ 2 ] } ] }, |
235 | { -row => [ { -literal => [ 3 ] }, { -literal => [ 4 ] } ] }, |
236 | ] } |
237 | |
238 | # query |
239 | VALUES (1, 2), (3, 4) |
240 | [] |
241 | |
d4c5cd24 |
242 | =head2 keyword |
243 | |
244 | # expr |
245 | { -keyword => 'insert_into' } |
246 | |
247 | # query |
248 | INSERT INTO |
249 | [] |
250 | |
79d30ac3 |
251 | =head2 statement types |
252 | |
253 | AQT node types are also provided for C<select>, C<insert>, C<update> and |
254 | C<delete>. These types are handled by the clauses system as discussed later. |
255 | |
256 | =head1 Expressions |
257 | |
29f96af0 |
258 | =head2 node expr |
259 | |
79d30ac3 |
260 | The simplest expression is just an AQT node: |
261 | |
262 | # expr |
263 | { -ident => [ 'foo', 'bar' ] } |
264 | |
265 | # aqt |
266 | { -ident => [ 'foo', 'bar' ] } |
267 | |
268 | # query |
269 | foo.bar |
270 | [] |
271 | |
272 | However, even in the case of an AQT node, the node value will be expanded if |
273 | an expander has been registered for that node type: |
274 | |
275 | # expr |
276 | { -ident => 'foo.bar' } |
277 | |
278 | # aqt |
279 | { -ident => [ 'foo', 'bar' ] } |
280 | |
281 | # query |
282 | foo.bar |
283 | [] |
284 | |
29f96af0 |
285 | =head2 identifier hashpair types |
286 | |
287 | =head3 hashtriple |
288 | |
289 | # expr |
290 | { id => { op => 'value' } } |
291 | |
292 | # aqt |
293 | { -op => |
294 | [ 'op', { -ident => [ 'id' ] }, { -bind => [ 'id', 'value' ] } ] |
295 | } |
296 | |
297 | # query |
298 | id OP ? |
299 | [ 'value' ] |
300 | |
0f4493cb |
301 | If the value is undef, attempts to convert equality and like ops to IS NULL, |
302 | and inequality and not like to IS NOT NULL: |
303 | |
304 | # expr |
305 | { id => { '!=' => undef } } |
306 | |
307 | # aqt |
308 | { -op => [ 'is_not_null', { -ident => [ 'id' ] } ] } |
309 | |
310 | # query |
311 | id IS NOT NULL |
312 | [] |
313 | |
29f96af0 |
314 | =head3 identifier hashpair w/simple value |
315 | |
316 | Equivalent to a hashtriple with an op of '='. |
317 | |
318 | # expr |
319 | { id => 'value' } |
320 | |
321 | # aqt |
322 | { |
323 | -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 'value' ] } ] |
324 | } |
325 | |
326 | # query |
327 | id = ? |
328 | [ 'value' ] |
329 | |
330 | (an object value will also follow this code path) |
331 | |
332 | =head3 identifier hashpair w/undef RHS |
333 | |
334 | Converted to IS NULL : |
335 | |
336 | # expr |
337 | { id => undef } |
338 | |
339 | # aqt |
340 | { -op => [ 'is_null', { -ident => [ 'id' ] } ] } |
341 | |
342 | # query |
343 | id IS NULL |
344 | [] |
345 | |
972c9823 |
346 | (equivalent to the -is operator) : |
347 | |
348 | # expr |
349 | { id => { -is => undef } } |
350 | |
351 | # aqt |
352 | { -op => [ 'is_null', { -ident => [ 'id' ] } ] } |
353 | |
354 | # query |
355 | id IS NULL |
356 | [] |
357 | |
29f96af0 |
358 | =head3 identifier hashpair w/literal RHS |
359 | |
360 | Directly appended to the key, remember you need to provide an operator: |
361 | |
362 | # expr |
363 | { id => \"= dont_try_this_at_home" } |
364 | |
365 | # aqt |
366 | { -literal => [ 'id = dont_try_this_at_home' ] } |
367 | |
368 | # query |
369 | id = dont_try_this_at_home |
370 | [] |
371 | |
372 | # expr |
373 | { id => \[ |
374 | "= seriously(?, ?, ?, ?, ?)", |
375 | "use", |
376 | "-ident", |
377 | "and", |
378 | "-func", |
379 | ] |
380 | } |
381 | |
382 | # aqt |
383 | { -literal => |
384 | [ 'id = seriously(?, ?, ?, ?, ?)', 'use', -ident => 'and', '-func' ] |
385 | } |
386 | |
387 | # query |
388 | id = seriously(?, ?, ?, ?, ?) |
389 | [ 'use', -ident => 'and', '-func' ] |
390 | |
391 | (you may absolutely use this when there's no built-in expression type for |
392 | what you need and registering a custom one would be more hassle than it's |
393 | worth, but, y'know, do try and avoid it) |
394 | |
395 | =head3 identifier hashpair w/arrayref value |
396 | |
397 | Becomes equivalent to a -or over an arrayref of hashrefs with the identifier |
398 | as key and the member of the original arrayref as the value: |
399 | |
400 | # expr |
401 | { id => [ 3, 4, { '>' => 12 } ] } |
402 | |
403 | # aqt |
404 | { -op => [ |
405 | 'or', |
406 | { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] }, |
407 | { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] }, |
408 | { |
409 | -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ] |
410 | }, |
411 | ] } |
412 | |
413 | # query |
414 | ( id = ? OR id = ? OR id > ? ) |
415 | [ 3, 4, 12 ] |
416 | |
417 | # expr |
418 | { -or => [ { id => 3 }, { id => 4 }, { id => { '>' => 12 } } ] } |
419 | |
420 | # aqt |
421 | { -op => [ |
422 | 'or', |
423 | { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] }, |
424 | { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] }, |
425 | { |
426 | -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ] |
427 | }, |
428 | ] } |
429 | |
430 | # query |
431 | ( id = ? OR id = ? OR id > ? ) |
432 | [ 3, 4, 12 ] |
433 | |
434 | Special Case: If the first element of the arrayref is -or or -and, that's |
435 | used as the top level logic op: |
436 | |
437 | # expr |
438 | { id => [ -and => { '>' => 3 }, { '<' => 6 } ] } |
439 | |
440 | # aqt |
441 | { -op => [ |
442 | 'and', |
443 | { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] }, |
444 | { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 6 ] } ] }, |
445 | ] } |
446 | |
447 | # query |
448 | ( id > ? AND id < ? ) |
449 | [ 3, 6 ] |
450 | |
451 | =head3 identifier hashpair w/hashref value |
452 | |
453 | Becomes equivalent to a -and over an arrayref of hashtriples constructed |
454 | with the identifier as the key and each key/value pair of the original |
edf5ac22 |
455 | hashref as the value: |
29f96af0 |
456 | |
457 | # expr |
458 | { id => { '<' => 4, '>' => 3 } } |
459 | |
460 | # aqt |
461 | { -op => [ |
462 | 'and', |
463 | { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] }, |
464 | { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] }, |
465 | ] } |
466 | |
467 | # query |
468 | ( id < ? AND id > ? ) |
469 | [ 4, 3 ] |
470 | |
972c9823 |
471 | is sugar for: |
472 | |
29f96af0 |
473 | # expr |
474 | { -and => [ { id => { '<' => 4 } }, { id => { '>' => 3 } } ] } |
475 | |
476 | # aqt |
477 | { -op => [ |
478 | 'and', |
479 | { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] }, |
480 | { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] }, |
481 | ] } |
482 | |
483 | # query |
484 | ( id < ? AND id > ? ) |
485 | [ 4, 3 ] |
486 | |
972c9823 |
487 | =head2 operator hashpair types |
488 | |
489 | A hashpair whose key begins with a -, or whose key consists entirely of |
490 | nonword characters (thereby covering '=', '>', pg json ops, etc.) is |
491 | processed as an operator hashpair. |
492 | |
493 | =head3 operator hashpair w/node type |
494 | |
495 | If a node type expander is registered for the key, the hashpair is |
496 | treated as a L</node expr>. |
497 | |
498 | =head3 operator hashpair w/registered op |
499 | |
500 | If an expander is registered for the op name, that's run and the |
501 | result returned: |
502 | |
503 | # expr |
504 | { -in => [ 'foo', 1, 2, 3 ] } |
505 | |
506 | # aqt |
507 | { -op => [ |
508 | 'in', { -ident => [ 'foo' ] }, { -bind => [ undef, 1 ] }, |
509 | { -bind => [ undef, 2 ] }, { -bind => [ undef, 3 ] }, |
510 | ] } |
511 | |
512 | # query |
513 | foo IN ( ?, ?, ? ) |
514 | [ 1, 2, 3 ] |
515 | |
516 | =head3 operator hashpair w/not prefix |
517 | |
518 | If the op name starts -not_ this is stripped and turned into a -not |
519 | wrapper around the result: |
520 | |
521 | # expr |
522 | { -not_ident => 'foo' } |
523 | |
524 | # aqt |
525 | { -op => [ 'not', { -ident => [ 'foo' ] } ] } |
526 | |
527 | # query |
528 | (NOT foo) |
529 | [] |
530 | |
531 | is equivalent to: |
532 | |
533 | # expr |
534 | { -not => { -ident => 'foo' } } |
535 | |
536 | # aqt |
537 | { -op => [ 'not', { -ident => [ 'foo' ] } ] } |
538 | |
539 | # query |
540 | (NOT foo) |
541 | [] |
542 | |
543 | =head3 operator hashpair with unknown op |
544 | |
545 | If the C<unknown_unop_always_func> option is set (which is recommended but |
546 | defaults to off for backwards compatibility reasons), an unknown op |
547 | expands into a C<-func> node: |
548 | |
549 | # expr |
550 | { -count => { -ident => '*' } } |
551 | |
552 | # aqt |
553 | { -func => [ 'count', { -ident => [ '*' ] } ] } |
554 | |
555 | # query |
556 | COUNT(*) |
557 | [] |
558 | |
559 | If not, an unknown op will expand into a C<-op> node. |
560 | |
561 | =head2 hashref expr |
562 | |
563 | A hashref with more than one pair becomes a C<-and> over its hashpairs, i.e. |
564 | |
565 | # expr |
566 | { x => 1, y => 2 } |
567 | |
568 | # aqt |
569 | { -op => [ |
570 | 'and', |
571 | { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] }, |
572 | { -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] }, |
573 | ] } |
574 | |
575 | # query |
576 | ( x = ? AND y = ? ) |
577 | [ 1, 2 ] |
578 | |
579 | is short hand for: |
580 | |
581 | # expr |
582 | { -and => [ { x => 1 }, { y => 2 } ] } |
583 | |
584 | # aqt |
585 | { -op => [ |
586 | 'and', |
587 | { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] }, |
588 | { -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] }, |
589 | ] } |
590 | |
591 | # query |
592 | ( x = ? AND y = ? ) |
593 | [ 1, 2 ] |
594 | |
595 | =head2 arrayref expr |
596 | |
24479414 |
597 | An arrayref becomes a C<-or> over its contents. Arrayrefs, hashrefs and |
598 | literals are all expanded and added to the clauses of the C<-or>. If the |
599 | arrayref contains a scalar it's treated as the key of a hashpair and the |
600 | next element as the value. |
601 | |
602 | # expr |
603 | [ { x => 1 }, [ { y => 2 }, { z => 3 } ], 'key', 'value', \"lit()" ] |
604 | |
605 | # aqt |
606 | { -op => [ |
607 | 'or', |
608 | { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] }, |
609 | { -op => [ |
610 | 'or', { |
611 | -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] |
612 | }, { |
613 | -op => [ '=', { -ident => [ 'z' ] }, { -bind => [ 'z', 3 ] } ] |
614 | }, |
615 | ] }, { -op => |
616 | [ |
617 | '=', { -ident => [ 'key' ] }, |
618 | { -bind => [ 'key', 'value' ] }, |
619 | ] |
620 | }, |
621 | { -literal => [ 'lit()' ] }, |
622 | ] } |
623 | |
624 | # query |
625 | ( x = ? OR ( y = ? OR z = ? ) OR key = ? OR lit() ) |
626 | [ 1, 2, 3, 'value' ] |
627 | |
a4e17ee5 |
628 | =head1 Default Expanders |
629 | |
630 | =head2 bool |
631 | |
632 | Turns the old -bool syntax into the value expression, i.e. |
633 | |
634 | # expr |
635 | { -bool => { -ident => 'foo' } } |
636 | |
637 | # aqt |
638 | { -ident => [ 'foo' ] } |
639 | |
640 | # query |
641 | foo |
642 | [] |
643 | |
644 | behaves the same way as the now-directly-supported |
645 | |
646 | # expr |
647 | { -ident => 'foo' } |
648 | |
649 | # aqt |
650 | { -ident => [ 'foo' ] } |
651 | |
652 | # query |
653 | foo |
654 | [] |
655 | |
cc230faa |
656 | =head2 row |
657 | |
658 | Expands the elements of the value arrayref: |
659 | |
660 | # expr |
661 | { -row => [ 1, { -ident => 'foo' }, 2, 3 ] } |
662 | |
663 | # aqt |
664 | { -row => [ |
665 | { -bind => [ undef, 1 ] }, { -ident => [ 'foo' ] }, |
666 | { -bind => [ undef, 2 ] }, { -bind => [ undef, 3 ] }, |
667 | ] } |
668 | |
669 | # query |
670 | (?, foo, ?, ?) |
671 | [ 1, 2, 3 ] |
672 | |
f2b47237 |
673 | =head2 op |
674 | |
675 | If an expander is registered for the op name, delegates to the expander; if |
676 | not, expands the argument values: |
677 | |
678 | # expr |
679 | { -op => [ 'ident', 'foo.bar' ] } |
680 | |
681 | # aqt |
682 | { -ident => [ 'foo', 'bar' ] } |
683 | |
684 | # query |
685 | foo.bar |
686 | [] |
687 | |
688 | # expr |
689 | { -op => [ '=', { -ident => 'foo' }, 3 ] } |
690 | |
691 | # aqt |
692 | { -op => [ '=', { -ident => [ 'foo' ] }, { -bind => [ undef, 3 ] } ] } |
693 | |
694 | # query |
695 | foo = ? |
696 | [ 3 ] |
697 | |
3c700ca3 |
698 | =head2 func |
699 | |
700 | Expands the argument values: |
701 | |
702 | # expr |
703 | { -func => [ 'coalesce', { -ident => 'thing' }, 'fallback' ] } |
704 | |
705 | # aqt |
706 | { -func => [ |
707 | 'coalesce', { -ident => [ 'thing' ] }, |
708 | { -bind => [ undef, 'fallback' ] }, |
709 | ] } |
710 | |
711 | # query |
712 | COALESCE(thing, ?) |
713 | [ 'fallback' ] |
714 | |
a06faabc |
715 | =head2 values |
716 | |
717 | A hashref value is expanded as an expression: |
718 | |
719 | # expr |
720 | { -values => { -row => [ 1, 2 ] } } |
721 | |
722 | # aqt |
723 | { -values => [ |
724 | { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] } |
725 | ] } |
726 | |
727 | # query |
728 | VALUES (?, ?) |
729 | [ 1, 2 ] |
730 | |
731 | An arrayref value's elements are either expressions or arrayrefs to be |
732 | treated as rows: |
733 | |
734 | # expr |
735 | { -values => [ { -row => [ 1, 2 ] }, [ 3, 4 ] ] } |
736 | |
737 | # aqt |
738 | { -values => [ |
739 | { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] }, |
740 | { -row => [ { -bind => [ undef, 3 ] }, { -bind => [ undef, 4 ] } ] }, |
741 | ] } |
742 | |
743 | # query |
744 | VALUES (?, ?), (?, ?) |
745 | [ 1, 2, 3, 4 ] |
746 | |
8480fb63 |
747 | =head2 list |
748 | |
749 | Expects a value or an arrayref of values, expands them, and returns just |
750 | the expanded aqt for a single entry or a comma operator for multiple: |
751 | |
752 | # expr |
753 | { -list => [ { -ident => 'foo' } ] } |
754 | |
755 | # aqt |
756 | { -op => [ ',', { -ident => [ 'foo' ] } ] } |
757 | |
758 | # query |
759 | foo |
760 | [] |
761 | |
762 | # expr |
763 | { -list => [ { -ident => 'foo' }, { -ident => 'bar' } ] } |
764 | |
765 | # aqt |
766 | { -op => [ ',', { -ident => [ 'foo' ] }, { -ident => [ 'bar' ] } ] } |
767 | |
768 | # query |
769 | foo, bar |
770 | [] |
771 | |
dee79057 |
772 | =head2 between op |
773 | |
774 | The RHS of between must either be a pair of exprs/plain values, or a single |
775 | literal expr: |
776 | |
777 | # expr |
778 | { -between => [ 'size', 3, { -ident => 'max_size' } ] } |
779 | |
780 | # aqt |
781 | { -op => [ |
782 | 'between', { -ident => [ 'size' ] }, { -bind => [ undef, 3 ] }, |
783 | { -ident => [ 'max_size' ] }, |
784 | ] } |
785 | |
786 | # query |
787 | ( size BETWEEN ? AND max_size ) |
788 | [ 3 ] |
789 | |
790 | # expr |
791 | { size => { -between => [ 3, { -ident => 'max_size' } ] } } |
792 | |
793 | # aqt |
794 | { -op => [ |
795 | 'between', { -ident => [ 'size' ] }, { -bind => [ 'size', 3 ] }, |
796 | { -ident => [ 'max_size' ] }, |
797 | ] } |
798 | |
799 | # query |
800 | ( size BETWEEN ? AND max_size ) |
801 | [ 3 ] |
802 | |
803 | # expr |
804 | { size => { -between => \"3 AND 7" } } |
805 | |
806 | # aqt |
807 | { -op => |
808 | [ |
809 | 'between', { -ident => [ 'size' ] }, |
810 | { -literal => [ '3 AND 7' ] }, |
811 | ] |
812 | } |
813 | |
814 | # query |
815 | ( size BETWEEN 3 AND 7 ) |
816 | [] |
817 | |
d1e8cbe6 |
818 | not_between is also expanded: |
dee79057 |
819 | |
820 | # expr |
821 | { size => { -not_between => [ 3, 7 ] } } |
822 | |
823 | # aqt |
824 | { -op => [ |
825 | 'not_between', { -ident => [ 'size' ] }, |
826 | { -bind => [ 'size', 3 ] }, { -bind => [ 'size', 7 ] }, |
827 | ] } |
828 | |
829 | # query |
830 | ( size NOT BETWEEN ? AND ? ) |
831 | [ 3, 7 ] |
832 | |
d1e8cbe6 |
833 | =head2 in op |
834 | |
835 | The RHS of in/not_in is either an expr/value or an arrayref of |
836 | exprs/values: |
837 | |
838 | # expr |
839 | { foo => { -in => [ 1, 2 ] } } |
840 | |
841 | # aqt |
842 | { -op => [ |
843 | 'in', { -ident => [ 'foo' ] }, { -bind => [ 'foo', 1 ] }, |
844 | { -bind => [ 'foo', 2 ] }, |
845 | ] } |
846 | |
847 | # query |
848 | foo IN ( ?, ? ) |
849 | [ 1, 2 ] |
850 | |
851 | # expr |
852 | { bar => { -not_in => \"(1, 2)" } } |
853 | |
854 | # aqt |
855 | { -op => |
856 | [ 'not_in', { -ident => [ 'bar' ] }, { -literal => [ '1, 2' ] } ] |
857 | } |
858 | |
859 | # query |
860 | bar NOT IN ( 1, 2 ) |
861 | [] |
862 | |
863 | A non-trivial LHS is expanded with ident as the default rather than value: |
864 | |
865 | # expr |
866 | { -in => [ |
867 | { -row => [ 'x', 'y' ] }, { -row => [ 1, 2 ] }, |
868 | { -row => [ 3, 4 ] }, |
869 | ] } |
870 | |
871 | # aqt |
872 | { -op => [ |
873 | 'in', { -row => [ { -ident => [ 'x' ] }, { -ident => [ 'y' ] } ] }, |
874 | { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] }, |
875 | { -row => [ { -bind => [ undef, 3 ] }, { -bind => [ undef, 4 ] } ] }, |
876 | ] } |
877 | |
878 | # query |
879 | (x, y) IN ( (?, ?), (?, ?) ) |
880 | [ 1, 2, 3, 4 ] |
881 | |
9b49ddc3 |
882 | =head2 and/or ops |
883 | |
884 | expands the same way as a plain arrayref/hashref expression but with the |
885 | logic type set to the op name. |
886 | |
887 | =head2 is op |
888 | |
889 | Expands is and is_not to null checks, RHS value must be undef: |
890 | |
891 | # expr |
892 | { -is => [ 'foo', undef ] } |
893 | |
894 | # aqt |
895 | { -op => [ 'is_null', { -ident => [ 'foo' ] } ] } |
896 | |
897 | # query |
898 | foo IS NULL |
899 | [] |
900 | |
901 | # expr |
902 | { bar => { -is_not => undef } } |
903 | |
904 | # aqt |
905 | { -op => [ 'is_not_null', { -ident => [ 'bar' ] } ] } |
906 | |
907 | # query |
908 | bar IS NOT NULL |
909 | [] |
910 | |
662bd623 |
911 | =head2 ident op |
912 | |
913 | Expands a string ident to an arrayref by splitting on the configured |
914 | separator, almost always '.': |
915 | |
916 | # expr |
917 | { -ident => 'foo.bar' } |
918 | |
919 | # aqt |
920 | { -ident => [ 'foo', 'bar' ] } |
921 | |
922 | # query |
923 | foo.bar |
924 | [] |
925 | |
926 | =head2 value op |
927 | |
928 | Expands to a bind node with the currently applicable column name if known: |
929 | |
930 | # expr |
931 | { foo => { '=' => { -value => 3 } } } |
932 | |
933 | # aqt |
934 | { -op => [ '=', { -ident => [ 'foo' ] }, { -bind => [ 'foo', 3 ] } ] } |
935 | |
936 | # query |
937 | foo = ? |
938 | [ 3 ] |
939 | |
5c04ab13 |
940 | =head1 Query Types |
941 | |
942 | =head2 select |
943 | |
944 | A select node accepts select, from, where and order_by clauses. |
945 | |
946 | The select clause is expanded as a list expression with a -ident default: |
947 | |
948 | # expr |
949 | { -select => { _ => [ 'foo', 'bar', { -count => 'baz' } ] } } |
950 | |
951 | # aqt |
952 | { -select => { select => { -op => [ |
953 | ',', { -ident => [ 'foo' ] }, { -ident => [ 'bar' ] }, |
954 | { -func => [ 'count', { -ident => [ 'baz' ] } ] }, |
955 | ] } } } |
956 | |
957 | # query |
958 | SELECT foo, bar, COUNT(baz) |
959 | [] |
960 | |
961 | The from clause is expanded as a list expression with a -ident default: |
962 | |
963 | # expr |
964 | { -select => { |
965 | from => [ 'schema1.table1', { -ident => [ 'schema2', 'table2' ] } ] |
966 | } } |
967 | |
968 | # aqt |
969 | { -select => { from => { -from_list => [ |
970 | { -ident => [ 'schema1', 'table1' ] }, |
971 | { -ident => [ 'schema2', 'table2' ] }, |
972 | ] } } } |
973 | |
974 | # query |
975 | FROM schema1.table1, schema2.table2 |
976 | [] |
977 | |
978 | The where clause is expanded as a plain expression: |
979 | |
980 | # expr |
981 | { -select => { where => { foo => 3 } } } |
982 | |
983 | # aqt |
984 | { -select => { where => { |
985 | -op => [ '=', { -ident => [ 'foo' ] }, { -bind => [ 'foo', 3 ] } ] |
986 | } } } |
987 | |
988 | # query |
989 | WHERE foo = ? |
990 | [ 3 ] |
991 | |
992 | The order_by clause expands as a list expression at top level, but a hashref |
993 | element may be either an expr or a hashpair with key -asc or -desc to indicate |
994 | an order by direction: |
995 | |
996 | # expr |
997 | { -select => |
998 | { order_by => [ 'foo', { -desc => 'bar' }, { -max => 'baz' } ] } |
999 | } |
1000 | |
1001 | # aqt |
1002 | { -select => { order_by => { -op => [ |
1003 | ',', { -ident => [ 'foo' ] }, { |
1004 | -op => [ ',', { -op => [ 'desc', { -ident => [ 'bar' ] } ] } ] |
1005 | }, { -func => [ 'max', { -ident => [ 'baz' ] } ] }, |
1006 | ] } } } |
1007 | |
1008 | # query |
1009 | ORDER BY foo, bar DESC, MAX(baz) |
1010 | [] |
1011 | |
47a7b2d1 |
1012 | =head2 |
1013 | |
1014 | An insert node accepts an into/target clause, a fields clause, a values/from |
1015 | clause, and a returning clause. |
1016 | |
47a7b2d1 |
1017 | The target clause is expanded with an ident default. |
1018 | |
1019 | The fields clause is expanded as a list expression if an arrayref, and |
1020 | otherwise passed through. |
1021 | |
1022 | The from clause may either be an expr, a literal, an arrayref of column |
1023 | values, or a hashref mapping colum names to values. |
1024 | |
1025 | The returning clause is expanded as a list expr with an ident default. |
1026 | |
1027 | # expr |
1028 | { -insert => { |
1029 | into => 'foo', |
1030 | returning => 'id', |
1031 | values => { bar => 'yay', baz => 'argh' }, |
1032 | } } |
1033 | |
1034 | # aqt |
1035 | { -insert => { |
1036 | fields => |
1037 | { -row => [ { -ident => [ 'bar' ] }, { -ident => [ 'baz' ] } ] }, |
1038 | from => { -values => [ { -row => [ |
1039 | { -bind => [ 'bar', 'yay' ] }, |
1040 | { -bind => [ 'baz', 'argh' ] }, |
1041 | ] } ] }, |
1042 | returning => { -op => [ ',', { -ident => [ 'id' ] } ] }, |
8480fb63 |
1043 | target => { -ident => [ 'foo' ] }, |
47a7b2d1 |
1044 | } } |
1045 | |
1046 | # query |
1047 | INSERT INTO foo (bar, baz) VALUES (?, ?) RETURNING id |
1048 | [ 'yay', 'argh' ] |
1049 | |
1050 | # expr |
1051 | { -insert => { |
1052 | fields => [ 'bar', 'baz' ], |
1053 | from => { -select => { _ => [ 'bar', 'baz' ], from => 'other' } }, |
1054 | into => 'foo', |
1055 | } } |
1056 | |
1057 | # aqt |
1058 | { -insert => { |
1059 | fields => { -row => [ { -op => |
1060 | [ ',', { -ident => [ 'bar' ] }, { -ident => [ 'baz' ] } ] |
1061 | } ] }, |
1062 | from => { -select => { |
8480fb63 |
1063 | from => { -ident => [ 'other' ] }, |
47a7b2d1 |
1064 | select => { -op => |
1065 | [ ',', { -ident => [ 'bar' ] }, { -ident => [ 'baz' ] } ] |
1066 | }, |
1067 | } }, |
8480fb63 |
1068 | target => { -ident => [ 'foo' ] }, |
47a7b2d1 |
1069 | } } |
1070 | |
1071 | # query |
1072 | INSERT INTO foo (bar, baz) SELECT bar, baz FROM other |
1073 | [] |
1074 | |
5c04ab13 |
1075 | =head2 update |
1076 | |
1077 | An update node accepts update/target (either may be used at expansion time), |
1078 | set, where, and returning clauses. |
1079 | |
1080 | The target clause is expanded with an ident default. |
1081 | |
1082 | The set clause (if not already a list expr) is expanded as a hashref where |
1083 | the keys are identifiers to be set and the values are exprs/values. |
1084 | |
1085 | The where clauses is expanded as a normal expr. |
1086 | |
1087 | The returning clause is expanded as a list expr with an ident default. |
1088 | |
1089 | # expr |
1090 | { -update => { |
1091 | _ => 'foo', |
1092 | returning => [ 'id', 'baz' ], |
1093 | set => { bar => 3, baz => { baz => { '+' => 1 } } }, |
1094 | where => { -not => { -ident => 'quux' } }, |
1095 | } } |
1096 | |
1097 | # aqt |
1098 | { -update => { |
1099 | returning => |
1100 | { |
1101 | -op => [ ',', { -ident => [ 'id' ] }, { -ident => [ 'baz' ] } ] |
1102 | }, |
1103 | set => { -op => [ |
1104 | ',', { -op => |
1105 | [ '=', { -ident => [ 'bar' ] }, { -bind => [ 'bar', 3 ] } ] |
1106 | }, { -op => [ |
1107 | '=', { -ident => [ 'baz' ] }, { -op => [ |
1108 | '+', { -ident => [ 'baz' ] }, |
1109 | { -bind => [ 'baz', 1 ] }, |
1110 | ] }, |
1111 | ] }, |
1112 | ] }, |
8480fb63 |
1113 | target => { -ident => [ 'foo' ] }, |
5c04ab13 |
1114 | where => { -op => [ 'not', { -ident => [ 'quux' ] } ] }, |
1115 | } } |
1116 | |
1117 | # query |
1118 | UPDATE foo SET bar = ?, baz = baz + ? WHERE (NOT quux) RETURNING id, baz |
1119 | [ 3, 1 ] |
1120 | |
ef42b9a6 |
1121 | =head2 delete |
1122 | |
1123 | delete accepts from/target, where, and returning clauses. |
1124 | |
1125 | The target clause is expanded with an ident default. |
1126 | |
1127 | The where clauses is expanded as a normal expr. |
1128 | |
1129 | The returning clause is expanded as a list expr with an ident default. |
1130 | |
1131 | # expr |
1132 | { -delete => { |
1133 | from => 'foo', |
1134 | returning => 'id', |
1135 | where => { bar => { '<' => 10 } }, |
1136 | } } |
1137 | |
1138 | # aqt |
1139 | { -delete => { |
1140 | returning => { -op => [ ',', { -ident => [ 'id' ] } ] }, |
1141 | target => { -op => [ ',', { -ident => [ 'foo' ] } ] }, |
1142 | where => { -op => |
1143 | [ '<', { -ident => [ 'bar' ] }, { -bind => [ 'bar', 10 ] } ] |
1144 | }, |
1145 | } } |
1146 | |
1147 | # query |
1148 | DELETE FROM foo WHERE bar < ? RETURNING id |
1149 | [ 10 ] |
1150 | |
29f96af0 |
1151 | =cut |