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 | |
282 | =head3 identifier hashpair w/simple value |
283 | |
284 | Equivalent to a hashtriple with an op of '='. |
285 | |
286 | # expr |
287 | { id => 'value' } |
288 | |
289 | # aqt |
290 | { |
291 | -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 'value' ] } ] |
292 | } |
293 | |
294 | # query |
295 | id = ? |
296 | [ 'value' ] |
297 | |
298 | (an object value will also follow this code path) |
299 | |
300 | =head3 identifier hashpair w/undef RHS |
301 | |
302 | Converted to IS NULL : |
303 | |
304 | # expr |
305 | { id => undef } |
306 | |
307 | # aqt |
308 | { -op => [ 'is_null', { -ident => [ 'id' ] } ] } |
309 | |
310 | # query |
311 | id IS NULL |
312 | [] |
313 | |
972c9823 |
314 | (equivalent to the -is operator) : |
315 | |
316 | # expr |
317 | { id => { -is => undef } } |
318 | |
319 | # aqt |
320 | { -op => [ 'is_null', { -ident => [ 'id' ] } ] } |
321 | |
322 | # query |
323 | id IS NULL |
324 | [] |
325 | |
29f96af0 |
326 | =head3 identifier hashpair w/literal RHS |
327 | |
328 | Directly appended to the key, remember you need to provide an operator: |
329 | |
330 | # expr |
331 | { id => \"= dont_try_this_at_home" } |
332 | |
333 | # aqt |
334 | { -literal => [ 'id = dont_try_this_at_home' ] } |
335 | |
336 | # query |
337 | id = dont_try_this_at_home |
338 | [] |
339 | |
340 | # expr |
341 | { id => \[ |
342 | "= seriously(?, ?, ?, ?, ?)", |
343 | "use", |
344 | "-ident", |
345 | "and", |
346 | "-func", |
347 | ] |
348 | } |
349 | |
350 | # aqt |
351 | { -literal => |
352 | [ 'id = seriously(?, ?, ?, ?, ?)', 'use', -ident => 'and', '-func' ] |
353 | } |
354 | |
355 | # query |
356 | id = seriously(?, ?, ?, ?, ?) |
357 | [ 'use', -ident => 'and', '-func' ] |
358 | |
359 | (you may absolutely use this when there's no built-in expression type for |
360 | what you need and registering a custom one would be more hassle than it's |
361 | worth, but, y'know, do try and avoid it) |
362 | |
363 | =head3 identifier hashpair w/arrayref value |
364 | |
365 | Becomes equivalent to a -or over an arrayref of hashrefs with the identifier |
366 | as key and the member of the original arrayref as the value: |
367 | |
368 | # expr |
369 | { id => [ 3, 4, { '>' => 12 } ] } |
370 | |
371 | # aqt |
372 | { -op => [ |
373 | 'or', |
374 | { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] }, |
375 | { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] }, |
376 | { |
377 | -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ] |
378 | }, |
379 | ] } |
380 | |
381 | # query |
382 | ( id = ? OR id = ? OR id > ? ) |
383 | [ 3, 4, 12 ] |
384 | |
385 | # expr |
386 | { -or => [ { id => 3 }, { id => 4 }, { id => { '>' => 12 } } ] } |
387 | |
388 | # aqt |
389 | { -op => [ |
390 | 'or', |
391 | { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] }, |
392 | { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] }, |
393 | { |
394 | -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ] |
395 | }, |
396 | ] } |
397 | |
398 | # query |
399 | ( id = ? OR id = ? OR id > ? ) |
400 | [ 3, 4, 12 ] |
401 | |
402 | Special Case: If the first element of the arrayref is -or or -and, that's |
403 | used as the top level logic op: |
404 | |
405 | # expr |
406 | { id => [ -and => { '>' => 3 }, { '<' => 6 } ] } |
407 | |
408 | # aqt |
409 | { -op => [ |
410 | 'and', |
411 | { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] }, |
412 | { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 6 ] } ] }, |
413 | ] } |
414 | |
415 | # query |
416 | ( id > ? AND id < ? ) |
417 | [ 3, 6 ] |
418 | |
419 | =head3 identifier hashpair w/hashref value |
420 | |
421 | Becomes equivalent to a -and over an arrayref of hashtriples constructed |
422 | with the identifier as the key and each key/value pair of the original |
edf5ac22 |
423 | hashref as the value: |
29f96af0 |
424 | |
425 | # expr |
426 | { id => { '<' => 4, '>' => 3 } } |
427 | |
428 | # aqt |
429 | { -op => [ |
430 | 'and', |
431 | { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] }, |
432 | { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] }, |
433 | ] } |
434 | |
435 | # query |
436 | ( id < ? AND id > ? ) |
437 | [ 4, 3 ] |
438 | |
972c9823 |
439 | is sugar for: |
440 | |
29f96af0 |
441 | # expr |
442 | { -and => [ { id => { '<' => 4 } }, { id => { '>' => 3 } } ] } |
443 | |
444 | # aqt |
445 | { -op => [ |
446 | 'and', |
447 | { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] }, |
448 | { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] }, |
449 | ] } |
450 | |
451 | # query |
452 | ( id < ? AND id > ? ) |
453 | [ 4, 3 ] |
454 | |
972c9823 |
455 | =head2 operator hashpair types |
456 | |
457 | A hashpair whose key begins with a -, or whose key consists entirely of |
458 | nonword characters (thereby covering '=', '>', pg json ops, etc.) is |
459 | processed as an operator hashpair. |
460 | |
461 | =head3 operator hashpair w/node type |
462 | |
463 | If a node type expander is registered for the key, the hashpair is |
464 | treated as a L</node expr>. |
465 | |
466 | =head3 operator hashpair w/registered op |
467 | |
468 | If an expander is registered for the op name, that's run and the |
469 | result returned: |
470 | |
471 | # expr |
472 | { -in => [ 'foo', 1, 2, 3 ] } |
473 | |
474 | # aqt |
475 | { -op => [ |
476 | 'in', { -ident => [ 'foo' ] }, { -bind => [ undef, 1 ] }, |
477 | { -bind => [ undef, 2 ] }, { -bind => [ undef, 3 ] }, |
478 | ] } |
479 | |
480 | # query |
481 | foo IN ( ?, ?, ? ) |
482 | [ 1, 2, 3 ] |
483 | |
484 | =head3 operator hashpair w/not prefix |
485 | |
486 | If the op name starts -not_ this is stripped and turned into a -not |
487 | wrapper around the result: |
488 | |
489 | # expr |
490 | { -not_ident => 'foo' } |
491 | |
492 | # aqt |
493 | { -op => [ 'not', { -ident => [ 'foo' ] } ] } |
494 | |
495 | # query |
496 | (NOT foo) |
497 | [] |
498 | |
499 | is equivalent to: |
500 | |
501 | # expr |
502 | { -not => { -ident => 'foo' } } |
503 | |
504 | # aqt |
505 | { -op => [ 'not', { -ident => [ 'foo' ] } ] } |
506 | |
507 | # query |
508 | (NOT foo) |
509 | [] |
510 | |
511 | =head3 operator hashpair with unknown op |
512 | |
513 | If the C<unknown_unop_always_func> option is set (which is recommended but |
514 | defaults to off for backwards compatibility reasons), an unknown op |
515 | expands into a C<-func> node: |
516 | |
517 | # expr |
518 | { -count => { -ident => '*' } } |
519 | |
520 | # aqt |
521 | { -func => [ 'count', { -ident => [ '*' ] } ] } |
522 | |
523 | # query |
524 | COUNT(*) |
525 | [] |
526 | |
527 | If not, an unknown op will expand into a C<-op> node. |
528 | |
529 | =head2 hashref expr |
530 | |
531 | A hashref with more than one pair becomes a C<-and> over its hashpairs, i.e. |
532 | |
533 | # expr |
534 | { x => 1, y => 2 } |
535 | |
536 | # aqt |
537 | { -op => [ |
538 | 'and', |
539 | { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] }, |
540 | { -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] }, |
541 | ] } |
542 | |
543 | # query |
544 | ( x = ? AND y = ? ) |
545 | [ 1, 2 ] |
546 | |
547 | is short hand for: |
548 | |
549 | # expr |
550 | { -and => [ { x => 1 }, { y => 2 } ] } |
551 | |
552 | # aqt |
553 | { -op => [ |
554 | 'and', |
555 | { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] }, |
556 | { -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] }, |
557 | ] } |
558 | |
559 | # query |
560 | ( x = ? AND y = ? ) |
561 | [ 1, 2 ] |
562 | |
563 | =head2 arrayref expr |
564 | |
24479414 |
565 | An arrayref becomes a C<-or> over its contents. Arrayrefs, hashrefs and |
566 | literals are all expanded and added to the clauses of the C<-or>. If the |
567 | arrayref contains a scalar it's treated as the key of a hashpair and the |
568 | next element as the value. |
569 | |
570 | # expr |
571 | [ { x => 1 }, [ { y => 2 }, { z => 3 } ], 'key', 'value', \"lit()" ] |
572 | |
573 | # aqt |
574 | { -op => [ |
575 | 'or', |
576 | { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] }, |
577 | { -op => [ |
578 | 'or', { |
579 | -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] |
580 | }, { |
581 | -op => [ '=', { -ident => [ 'z' ] }, { -bind => [ 'z', 3 ] } ] |
582 | }, |
583 | ] }, { -op => |
584 | [ |
585 | '=', { -ident => [ 'key' ] }, |
586 | { -bind => [ 'key', 'value' ] }, |
587 | ] |
588 | }, |
589 | { -literal => [ 'lit()' ] }, |
590 | ] } |
591 | |
592 | # query |
593 | ( x = ? OR ( y = ? OR z = ? ) OR key = ? OR lit() ) |
594 | [ 1, 2, 3, 'value' ] |
595 | |
29f96af0 |
596 | =cut |