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 | |
149 | Not: |
150 | |
151 | # expr |
152 | { -op => [ 'not', { -ident => 'explosive' } ] } |
153 | |
154 | # query |
155 | (NOT explosive) |
156 | [] |
157 | |
158 | Postfix unop: (is_null, is_not_null, asc, desc) |
159 | |
160 | # expr |
161 | { -op => [ 'is_null', { -ident => [ 'bobby' ] } ] } |
162 | |
163 | # query |
164 | bobby IS NULL |
165 | [] |
166 | |
167 | AND and OR: |
168 | |
169 | # expr |
170 | { -op => |
171 | [ 'and', { -ident => 'x' }, { -ident => 'y' }, { -ident => 'z' } ] |
172 | } |
173 | |
174 | # query |
175 | ( x AND y AND z ) |
176 | [] |
177 | |
178 | IN (and NOT IN): |
179 | |
180 | # expr |
181 | { -op => [ |
182 | 'in', { -ident => 'card' }, { -bind => [ 'card', 3 ] }, |
183 | { -bind => [ 'card', 'J' ] }, |
184 | ] } |
185 | |
186 | # query |
187 | card IN ( ?, ? ) |
188 | [ 3, 'J' ] |
189 | |
190 | BETWEEN (and NOT BETWEEN): |
191 | |
192 | # expr |
193 | { -op => [ |
194 | 'between', { -ident => 'pints' }, { -bind => [ 'pints', 2 ] }, |
195 | { -bind => [ 'pints', 4 ] }, |
196 | ] } |
197 | |
198 | # query |
199 | ( pints BETWEEN ? AND ? ) |
200 | [ 2, 4 ] |
201 | |
202 | Comma (use -row for parens): |
203 | |
204 | # expr |
205 | { -op => [ ',', { -literal => [ 1 ] }, { -literal => [ 2 ] } ] } |
206 | |
207 | # query |
208 | 1, 2 |
209 | [] |
210 | |
211 | =head2 values |
212 | |
213 | # expr |
214 | { -values => |
215 | { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] } |
216 | } |
217 | |
218 | # query |
219 | VALUES (?, ?) |
220 | [ 1, 2 ] |
221 | |
222 | # expr |
223 | { -values => [ |
224 | { -row => [ { -literal => [ 1 ] }, { -literal => [ 2 ] } ] }, |
225 | { -row => [ { -literal => [ 3 ] }, { -literal => [ 4 ] } ] }, |
226 | ] } |
227 | |
228 | # query |
229 | VALUES (1, 2), (3, 4) |
230 | [] |
231 | |
232 | =head2 statement types |
233 | |
234 | AQT node types are also provided for C<select>, C<insert>, C<update> and |
235 | C<delete>. These types are handled by the clauses system as discussed later. |
236 | |
237 | =head1 Expressions |
238 | |
29f96af0 |
239 | =head2 node expr |
240 | |
79d30ac3 |
241 | The simplest expression is just an AQT node: |
242 | |
243 | # expr |
244 | { -ident => [ 'foo', 'bar' ] } |
245 | |
246 | # aqt |
247 | { -ident => [ 'foo', 'bar' ] } |
248 | |
249 | # query |
250 | foo.bar |
251 | [] |
252 | |
253 | However, even in the case of an AQT node, the node value will be expanded if |
254 | an expander has been registered for that node type: |
255 | |
256 | # expr |
257 | { -ident => 'foo.bar' } |
258 | |
259 | # aqt |
260 | { -ident => [ 'foo', 'bar' ] } |
261 | |
262 | # query |
263 | foo.bar |
264 | [] |
265 | |
29f96af0 |
266 | =head2 identifier hashpair types |
267 | |
268 | =head3 hashtriple |
269 | |
270 | # expr |
271 | { id => { op => 'value' } } |
272 | |
273 | # aqt |
274 | { -op => |
275 | [ 'op', { -ident => [ 'id' ] }, { -bind => [ 'id', 'value' ] } ] |
276 | } |
277 | |
278 | # query |
279 | id OP ? |
280 | [ 'value' ] |
281 | |
0f4493cb |
282 | If the value is undef, attempts to convert equality and like ops to IS NULL, |
283 | and inequality and not like to IS NOT NULL: |
284 | |
285 | # expr |
286 | { id => { '!=' => undef } } |
287 | |
288 | # aqt |
289 | { -op => [ 'is_not_null', { -ident => [ 'id' ] } ] } |
290 | |
291 | # query |
292 | id IS NOT NULL |
293 | [] |
294 | |
29f96af0 |
295 | =head3 identifier hashpair w/simple value |
296 | |
297 | Equivalent to a hashtriple with an op of '='. |
298 | |
299 | # expr |
300 | { id => 'value' } |
301 | |
302 | # aqt |
303 | { |
304 | -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 'value' ] } ] |
305 | } |
306 | |
307 | # query |
308 | id = ? |
309 | [ 'value' ] |
310 | |
311 | (an object value will also follow this code path) |
312 | |
313 | =head3 identifier hashpair w/undef RHS |
314 | |
315 | Converted to IS NULL : |
316 | |
317 | # expr |
318 | { id => undef } |
319 | |
320 | # aqt |
321 | { -op => [ 'is_null', { -ident => [ 'id' ] } ] } |
322 | |
323 | # query |
324 | id IS NULL |
325 | [] |
326 | |
972c9823 |
327 | (equivalent to the -is operator) : |
328 | |
329 | # expr |
330 | { id => { -is => undef } } |
331 | |
332 | # aqt |
333 | { -op => [ 'is_null', { -ident => [ 'id' ] } ] } |
334 | |
335 | # query |
336 | id IS NULL |
337 | [] |
338 | |
29f96af0 |
339 | =head3 identifier hashpair w/literal RHS |
340 | |
341 | Directly appended to the key, remember you need to provide an operator: |
342 | |
343 | # expr |
344 | { id => \"= dont_try_this_at_home" } |
345 | |
346 | # aqt |
347 | { -literal => [ 'id = dont_try_this_at_home' ] } |
348 | |
349 | # query |
350 | id = dont_try_this_at_home |
351 | [] |
352 | |
353 | # expr |
354 | { id => \[ |
355 | "= seriously(?, ?, ?, ?, ?)", |
356 | "use", |
357 | "-ident", |
358 | "and", |
359 | "-func", |
360 | ] |
361 | } |
362 | |
363 | # aqt |
364 | { -literal => |
365 | [ 'id = seriously(?, ?, ?, ?, ?)', 'use', -ident => 'and', '-func' ] |
366 | } |
367 | |
368 | # query |
369 | id = seriously(?, ?, ?, ?, ?) |
370 | [ 'use', -ident => 'and', '-func' ] |
371 | |
372 | (you may absolutely use this when there's no built-in expression type for |
373 | what you need and registering a custom one would be more hassle than it's |
374 | worth, but, y'know, do try and avoid it) |
375 | |
376 | =head3 identifier hashpair w/arrayref value |
377 | |
378 | Becomes equivalent to a -or over an arrayref of hashrefs with the identifier |
379 | as key and the member of the original arrayref as the value: |
380 | |
381 | # expr |
382 | { id => [ 3, 4, { '>' => 12 } ] } |
383 | |
384 | # aqt |
385 | { -op => [ |
386 | 'or', |
387 | { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] }, |
388 | { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] }, |
389 | { |
390 | -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ] |
391 | }, |
392 | ] } |
393 | |
394 | # query |
395 | ( id = ? OR id = ? OR id > ? ) |
396 | [ 3, 4, 12 ] |
397 | |
398 | # expr |
399 | { -or => [ { id => 3 }, { id => 4 }, { id => { '>' => 12 } } ] } |
400 | |
401 | # aqt |
402 | { -op => [ |
403 | 'or', |
404 | { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] }, |
405 | { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] }, |
406 | { |
407 | -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ] |
408 | }, |
409 | ] } |
410 | |
411 | # query |
412 | ( id = ? OR id = ? OR id > ? ) |
413 | [ 3, 4, 12 ] |
414 | |
415 | Special Case: If the first element of the arrayref is -or or -and, that's |
416 | used as the top level logic op: |
417 | |
418 | # expr |
419 | { id => [ -and => { '>' => 3 }, { '<' => 6 } ] } |
420 | |
421 | # aqt |
422 | { -op => [ |
423 | 'and', |
424 | { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] }, |
425 | { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 6 ] } ] }, |
426 | ] } |
427 | |
428 | # query |
429 | ( id > ? AND id < ? ) |
430 | [ 3, 6 ] |
431 | |
432 | =head3 identifier hashpair w/hashref value |
433 | |
434 | Becomes equivalent to a -and over an arrayref of hashtriples constructed |
435 | with the identifier as the key and each key/value pair of the original |
edf5ac22 |
436 | hashref as the value: |
29f96af0 |
437 | |
438 | # expr |
439 | { id => { '<' => 4, '>' => 3 } } |
440 | |
441 | # aqt |
442 | { -op => [ |
443 | 'and', |
444 | { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] }, |
445 | { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] }, |
446 | ] } |
447 | |
448 | # query |
449 | ( id < ? AND id > ? ) |
450 | [ 4, 3 ] |
451 | |
972c9823 |
452 | is sugar for: |
453 | |
29f96af0 |
454 | # expr |
455 | { -and => [ { id => { '<' => 4 } }, { id => { '>' => 3 } } ] } |
456 | |
457 | # aqt |
458 | { -op => [ |
459 | 'and', |
460 | { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] }, |
461 | { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] }, |
462 | ] } |
463 | |
464 | # query |
465 | ( id < ? AND id > ? ) |
466 | [ 4, 3 ] |
467 | |
972c9823 |
468 | =head2 operator hashpair types |
469 | |
470 | A hashpair whose key begins with a -, or whose key consists entirely of |
471 | nonword characters (thereby covering '=', '>', pg json ops, etc.) is |
472 | processed as an operator hashpair. |
473 | |
474 | =head3 operator hashpair w/node type |
475 | |
476 | If a node type expander is registered for the key, the hashpair is |
477 | treated as a L</node expr>. |
478 | |
479 | =head3 operator hashpair w/registered op |
480 | |
481 | If an expander is registered for the op name, that's run and the |
482 | result returned: |
483 | |
484 | # expr |
485 | { -in => [ 'foo', 1, 2, 3 ] } |
486 | |
487 | # aqt |
488 | { -op => [ |
489 | 'in', { -ident => [ 'foo' ] }, { -bind => [ undef, 1 ] }, |
490 | { -bind => [ undef, 2 ] }, { -bind => [ undef, 3 ] }, |
491 | ] } |
492 | |
493 | # query |
494 | foo IN ( ?, ?, ? ) |
495 | [ 1, 2, 3 ] |
496 | |
497 | =head3 operator hashpair w/not prefix |
498 | |
499 | If the op name starts -not_ this is stripped and turned into a -not |
500 | wrapper around the result: |
501 | |
502 | # expr |
503 | { -not_ident => 'foo' } |
504 | |
505 | # aqt |
506 | { -op => [ 'not', { -ident => [ 'foo' ] } ] } |
507 | |
508 | # query |
509 | (NOT foo) |
510 | [] |
511 | |
512 | is equivalent to: |
513 | |
514 | # expr |
515 | { -not => { -ident => 'foo' } } |
516 | |
517 | # aqt |
518 | { -op => [ 'not', { -ident => [ 'foo' ] } ] } |
519 | |
520 | # query |
521 | (NOT foo) |
522 | [] |
523 | |
524 | =head3 operator hashpair with unknown op |
525 | |
526 | If the C<unknown_unop_always_func> option is set (which is recommended but |
527 | defaults to off for backwards compatibility reasons), an unknown op |
528 | expands into a C<-func> node: |
529 | |
530 | # expr |
531 | { -count => { -ident => '*' } } |
532 | |
533 | # aqt |
534 | { -func => [ 'count', { -ident => [ '*' ] } ] } |
535 | |
536 | # query |
537 | COUNT(*) |
538 | [] |
539 | |
540 | If not, an unknown op will expand into a C<-op> node. |
541 | |
542 | =head2 hashref expr |
543 | |
544 | A hashref with more than one pair becomes a C<-and> over its hashpairs, i.e. |
545 | |
546 | # expr |
547 | { x => 1, y => 2 } |
548 | |
549 | # aqt |
550 | { -op => [ |
551 | 'and', |
552 | { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] }, |
553 | { -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] }, |
554 | ] } |
555 | |
556 | # query |
557 | ( x = ? AND y = ? ) |
558 | [ 1, 2 ] |
559 | |
560 | is short hand for: |
561 | |
562 | # expr |
563 | { -and => [ { x => 1 }, { y => 2 } ] } |
564 | |
565 | # aqt |
566 | { -op => [ |
567 | 'and', |
568 | { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] }, |
569 | { -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] }, |
570 | ] } |
571 | |
572 | # query |
573 | ( x = ? AND y = ? ) |
574 | [ 1, 2 ] |
575 | |
576 | =head2 arrayref expr |
577 | |
24479414 |
578 | An arrayref becomes a C<-or> over its contents. Arrayrefs, hashrefs and |
579 | literals are all expanded and added to the clauses of the C<-or>. If the |
580 | arrayref contains a scalar it's treated as the key of a hashpair and the |
581 | next element as the value. |
582 | |
583 | # expr |
584 | [ { x => 1 }, [ { y => 2 }, { z => 3 } ], 'key', 'value', \"lit()" ] |
585 | |
586 | # aqt |
587 | { -op => [ |
588 | 'or', |
589 | { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] }, |
590 | { -op => [ |
591 | 'or', { |
592 | -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] |
593 | }, { |
594 | -op => [ '=', { -ident => [ 'z' ] }, { -bind => [ 'z', 3 ] } ] |
595 | }, |
596 | ] }, { -op => |
597 | [ |
598 | '=', { -ident => [ 'key' ] }, |
599 | { -bind => [ 'key', 'value' ] }, |
600 | ] |
601 | }, |
602 | { -literal => [ 'lit()' ] }, |
603 | ] } |
604 | |
605 | # query |
606 | ( x = ? OR ( y = ? OR z = ? ) OR key = ? OR lit() ) |
607 | [ 1, 2, 3, 'value' ] |
608 | |
29f96af0 |
609 | =cut |