771d222eaa473cf5e3647d7b323267348b5b78db
[dbsrgits/SQL-Abstract.git] / lib / SQL / Abstract / Reference.pm
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
23 C<# aqt>, this is what's being described.
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
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
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
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
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
150 Prefix unop:
151
152   # expr
153   { -op => [ '-', { -ident => 'foo' } ] }
154
155   # query
156   - foo
157   []
158
159 Not as special case parenthesised unop:
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
242 =head2 keyword
243
244   # expr
245   { -keyword => 'insert_into' }
246
247   # query
248   INSERT INTO
249   []
250
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
258 =head2 node expr
259
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
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
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
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
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
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
455 hashref as the value:
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
471 is sugar for:
472
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
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
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
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
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
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
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
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
747 =head2 between op
748
749 The RHS of between must either be a pair of exprs/plain values, or a single
750 literal expr:
751
752   # expr
753   { -between => [ 'size', 3, { -ident => 'max_size' } ] }
754
755   # aqt
756   { -op => [
757       'between', { -ident => [ 'size' ] }, { -bind => [ undef, 3 ] },
758       { -ident => [ 'max_size' ] },
759   ] }
760
761   # query
762   ( size BETWEEN ? AND max_size )
763   [ 3 ]
764
765   # expr
766   { size => { -between => [ 3, { -ident => 'max_size' } ] } }
767
768   # aqt
769   { -op => [
770       'between', { -ident => [ 'size' ] }, { -bind => [ 'size', 3 ] },
771       { -ident => [ 'max_size' ] },
772   ] }
773
774   # query
775   ( size BETWEEN ? AND max_size )
776   [ 3 ]
777
778   # expr
779   { size => { -between => \"3 AND 7" } }
780
781   # aqt
782   { -op =>
783       [
784         'between', { -ident => [ 'size' ] },
785         { -literal => [ '3 AND 7' ] },
786       ]
787   }
788
789   # query
790   ( size BETWEEN 3 AND 7 )
791   []
792
793 not_between is also expanded:
794
795   # expr
796   { size => { -not_between => [ 3, 7 ] } }
797
798   # aqt
799   { -op => [
800       'not_between', { -ident => [ 'size' ] },
801       { -bind => [ 'size', 3 ] }, { -bind => [ 'size', 7 ] },
802   ] }
803
804   # query
805   ( size NOT BETWEEN ? AND ? )
806   [ 3, 7 ]
807
808 =head2 in op
809
810 The RHS of in/not_in is either an expr/value or an arrayref of
811 exprs/values:
812
813   # expr
814   { foo => { -in => [ 1, 2 ] } }
815
816   # aqt
817   { -op => [
818       'in', { -ident => [ 'foo' ] }, { -bind => [ 'foo', 1 ] },
819       { -bind => [ 'foo', 2 ] },
820   ] }
821
822   # query
823   foo IN ( ?, ? )
824   [ 1, 2 ]
825
826   # expr
827   { bar => { -not_in => \"(1, 2)" } }
828
829   # aqt
830   { -op =>
831       [ 'not_in', { -ident => [ 'bar' ] }, { -literal => [ '1, 2' ] } ]
832   }
833
834   # query
835   bar NOT IN ( 1, 2 )
836   []
837
838 A non-trivial LHS is expanded with ident as the default rather than value:
839
840   # expr
841   { -in => [
842       { -row => [ 'x', 'y' ] }, { -row => [ 1, 2 ] },
843       { -row => [ 3, 4 ] },
844   ] }
845
846   # aqt
847   { -op => [
848       'in', { -row => [ { -ident => [ 'x' ] }, { -ident => [ 'y' ] } ] },
849       { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] },
850       { -row => [ { -bind => [ undef, 3 ] }, { -bind => [ undef, 4 ] } ] },
851   ] }
852
853   # query
854   (x, y) IN ( (?, ?), (?, ?) )
855   [ 1, 2, 3, 4 ]
856
857 =head2 and/or ops
858
859 expands the same way as a plain arrayref/hashref expression but with the
860 logic type set to the op name.
861
862 =head2 is op
863
864 Expands is and is_not to null checks, RHS value must be undef:
865
866   # expr
867   { -is => [ 'foo', undef ] }
868
869   # aqt
870   { -op => [ 'is_null', { -ident => [ 'foo' ] } ] }
871
872   # query
873   foo IS NULL
874   []
875
876   # expr
877   { bar => { -is_not => undef } }
878
879   # aqt
880   { -op => [ 'is_not_null', { -ident => [ 'bar' ] } ] }
881
882   # query
883   bar IS NOT NULL
884   []
885
886 =head2 ident op
887
888 Expands a string ident to an arrayref by splitting on the configured
889 separator, almost always '.':
890
891   # expr
892   { -ident => 'foo.bar' }
893
894   # aqt
895   { -ident => [ 'foo', 'bar' ] }
896
897   # query
898   foo.bar
899   []
900
901 =head2 value op
902
903 Expands to a bind node with the currently applicable column name if known:
904
905   # expr
906   { foo => { '=' => { -value => 3 } } }
907
908   # aqt
909   { -op => [ '=', { -ident => [ 'foo' ] }, { -bind => [ 'foo', 3 ] } ] }
910
911   # query
912   foo = ?
913   [ 3 ]
914
915 =cut