1 package SQL::Abstract::Reference;
8 SQL::Abstract::Reference - Reference documentation for L<SQL::Abstract>
12 =head2 Expression (expr)
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.
18 =head2 Abstract Query Tree (aqt)
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.
25 =head2 SQL and Bind Values (query)
27 The final result of L<SQL::Abstract> rendering is generally an SQL statement
28 plus bind values for passing to DBI, ala:
30 my ($sql, @bind) = $sqla->some_method(@args);
31 my @hashes = @{$dbh->do($sql, { Slice => {} }, @bind)};
33 If you see a comment before a code block saying C<# query>, the SQL + bind
34 array is what's being described.
38 An expander subroutine is written as:
41 my ($sqla, $name, $value, $k) = @_;
46 $name is the expr node type for node expanders, the op name for op
47 expanders, and the clause name for clause expanders.
49 $value is the body of the thing being expanded
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
55 This can trivially be converted to an C<ident> type AQT node with:
57 my $ident = $sqla->expand_expr({ -ident => $k });
61 A renderer subroutine looks like:
64 my ($sqla, $type, $value) = @_;
66 $sqla->join_query_parts($join, @parts);
69 and can be registered on a per-type, per-op or per-clause basis.
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.
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.
82 { -literal => [ 'SPANG(?, ?)', 1, 27 ] }
98 { -ident => [ 'foo', 'bar' ] }
107 { -bind => [ 'colname', 'value' ] }
117 -row => [ { -bind => [ 'r', 1 ] }, { -ident => [ 'clown', 'car' ] } ]
128 -func => [ 'foo', { -ident => [ 'bar' ] }, { -bind => [ undef, 7 ] } ]
141 '=', { -ident => [ 'bomb', 'status' ] },
142 { -value => 'unexploded' },
153 { -op => [ '-', { -ident => 'foo' } ] }
159 Not as special case parenthesised unop:
162 { -op => [ 'not', { -ident => 'explosive' } ] }
168 Postfix unop: (is_null, is_not_null, asc, desc)
171 { -op => [ 'is_null', { -ident => [ 'bobby' ] } ] }
181 [ 'and', { -ident => 'x' }, { -ident => 'y' }, { -ident => 'z' } ]
192 'in', { -ident => 'card' }, { -bind => [ 'card', 3 ] },
193 { -bind => [ 'card', 'J' ] },
200 BETWEEN (and NOT BETWEEN):
204 'between', { -ident => 'pints' }, { -bind => [ 'pints', 2 ] },
205 { -bind => [ 'pints', 4 ] },
209 ( pints BETWEEN ? AND ? )
212 Comma (use -row for parens):
215 { -op => [ ',', { -literal => [ 1 ] }, { -literal => [ 2 ] } ] }
225 { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] }
234 { -row => [ { -literal => [ 1 ] }, { -literal => [ 2 ] } ] },
235 { -row => [ { -literal => [ 3 ] }, { -literal => [ 4 ] } ] },
239 VALUES (1, 2), (3, 4)
245 { -keyword => 'insert_into' }
251 =head2 statement types
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.
260 The simplest expression is just an AQT node:
263 { -ident => [ 'foo', 'bar' ] }
266 { -ident => [ 'foo', 'bar' ] }
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:
276 { -ident => 'foo.bar' }
279 { -ident => [ 'foo', 'bar' ] }
285 =head2 identifier hashpair types
290 { id => { op => 'value' } }
294 [ 'op', { -ident => [ 'id' ] }, { -bind => [ 'id', 'value' ] } ]
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:
305 { id => { '!=' => undef } }
308 { -op => [ 'is_not_null', { -ident => [ 'id' ] } ] }
314 =head3 identifier hashpair w/simple value
316 Equivalent to a hashtriple with an op of '='.
323 -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 'value' ] } ]
330 (an object value will also follow this code path)
332 =head3 identifier hashpair w/undef RHS
334 Converted to IS NULL :
340 { -op => [ 'is_null', { -ident => [ 'id' ] } ] }
346 (equivalent to the -is operator) :
349 { id => { -is => undef } }
352 { -op => [ 'is_null', { -ident => [ 'id' ] } ] }
358 =head3 identifier hashpair w/literal RHS
360 Directly appended to the key, remember you need to provide an operator:
363 { id => \"= dont_try_this_at_home" }
366 { -literal => [ 'id = dont_try_this_at_home' ] }
369 id = dont_try_this_at_home
374 "= seriously(?, ?, ?, ?, ?)",
384 [ 'id = seriously(?, ?, ?, ?, ?)', 'use', -ident => 'and', '-func' ]
388 id = seriously(?, ?, ?, ?, ?)
389 [ 'use', -ident => 'and', '-func' ]
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)
395 =head3 identifier hashpair w/arrayref value
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:
401 { id => [ 3, 4, { '>' => 12 } ] }
406 { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
407 { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
409 -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ]
414 ( id = ? OR id = ? OR id > ? )
418 { -or => [ { id => 3 }, { id => 4 }, { id => { '>' => 12 } } ] }
423 { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
424 { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
426 -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ]
431 ( id = ? OR id = ? OR id > ? )
434 Special Case: If the first element of the arrayref is -or or -and, that's
435 used as the top level logic op:
438 { id => [ -and => { '>' => 3 }, { '<' => 6 } ] }
443 { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
444 { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 6 ] } ] },
448 ( id > ? AND id < ? )
451 =head3 identifier hashpair w/hashref value
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:
458 { id => { '<' => 4, '>' => 3 } }
463 { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
464 { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
468 ( id < ? AND id > ? )
474 { -and => [ { id => { '<' => 4 } }, { id => { '>' => 3 } } ] }
479 { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
480 { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
484 ( id < ? AND id > ? )
487 =head2 operator hashpair types
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.
493 =head3 operator hashpair w/node type
495 If a node type expander is registered for the key, the hashpair is
496 treated as a L</node expr>.
498 =head3 operator hashpair w/registered op
500 If an expander is registered for the op name, that's run and the
504 { -in => [ 'foo', 1, 2, 3 ] }
508 'in', { -ident => [ 'foo' ] }, { -bind => [ undef, 1 ] },
509 { -bind => [ undef, 2 ] }, { -bind => [ undef, 3 ] },
516 =head3 operator hashpair w/not prefix
518 If the op name starts -not_ this is stripped and turned into a -not
519 wrapper around the result:
522 { -not_ident => 'foo' }
525 { -op => [ 'not', { -ident => [ 'foo' ] } ] }
534 { -not => { -ident => 'foo' } }
537 { -op => [ 'not', { -ident => [ 'foo' ] } ] }
543 =head3 operator hashpair with unknown op
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:
550 { -count => { -ident => '*' } }
553 { -func => [ 'count', { -ident => [ '*' ] } ] }
559 If not, an unknown op will expand into a C<-op> node.
563 A hashref with more than one pair becomes a C<-and> over its hashpairs, i.e.
571 { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] },
572 { -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] },
582 { -and => [ { x => 1 }, { y => 2 } ] }
587 { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] },
588 { -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] },
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.
603 [ { x => 1 }, [ { y => 2 }, { z => 3 } ], 'key', 'value', \"lit()" ]
608 { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] },
611 -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ]
613 -op => [ '=', { -ident => [ 'z' ] }, { -bind => [ 'z', 3 ] } ]
617 '=', { -ident => [ 'key' ] },
618 { -bind => [ 'key', 'value' ] },
621 { -literal => [ 'lit()' ] },
625 ( x = ? OR ( y = ? OR z = ? ) OR key = ? OR lit() )
628 =head1 Default Expanders
632 Turns the old -bool syntax into the value expression, i.e.
635 { -bool => { -ident => 'foo' } }
638 { -ident => [ 'foo' ] }
644 behaves the same way as the now-directly-supported
650 { -ident => [ 'foo' ] }
658 Expands the elements of the value arrayref:
661 { -row => [ 1, { -ident => 'foo' }, 2, 3 ] }
665 { -bind => [ undef, 1 ] }, { -ident => [ 'foo' ] },
666 { -bind => [ undef, 2 ] }, { -bind => [ undef, 3 ] },
675 If an expander is registered for the op name, delegates to the expander; if
676 not, expands the argument values:
679 { -op => [ 'ident', 'foo.bar' ] }
682 { -ident => [ 'foo', 'bar' ] }
689 { -op => [ '=', { -ident => 'foo' }, 3 ] }
692 { -op => [ '=', { -ident => [ 'foo' ] }, { -bind => [ undef, 3 ] } ] }
700 Expands the argument values:
703 { -func => [ 'coalesce', { -ident => 'thing' }, 'fallback' ] }
707 'coalesce', { -ident => [ 'thing' ] },
708 { -bind => [ undef, 'fallback' ] },
717 A hashref value is expanded as an expression:
720 { -values => { -row => [ 1, 2 ] } }
724 { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] }
731 An arrayref value's elements are either expressions or arrayrefs to be
735 { -values => [ { -row => [ 1, 2 ] }, [ 3, 4 ] ] }
739 { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] },
740 { -row => [ { -bind => [ undef, 3 ] }, { -bind => [ undef, 4 ] } ] },
744 VALUES (?, ?), (?, ?)
749 The RHS of between must either be a pair of exprs/plain values, or a single
753 { -between => [ 'size', 3, { -ident => 'max_size' } ] }
757 'between', { -ident => [ 'size' ] }, { -bind => [ undef, 3 ] },
758 { -ident => [ 'max_size' ] },
762 ( size BETWEEN ? AND max_size )
766 { size => { -between => [ 3, { -ident => 'max_size' } ] } }
770 'between', { -ident => [ 'size' ] }, { -bind => [ 'size', 3 ] },
771 { -ident => [ 'max_size' ] },
775 ( size BETWEEN ? AND max_size )
779 { size => { -between => \"3 AND 7" } }
784 'between', { -ident => [ 'size' ] },
785 { -literal => [ '3 AND 7' ] },
790 ( size BETWEEN 3 AND 7 )
793 not_between is also expanded:
796 { size => { -not_between => [ 3, 7 ] } }
800 'not_between', { -ident => [ 'size' ] },
801 { -bind => [ 'size', 3 ] }, { -bind => [ 'size', 7 ] },
805 ( size NOT BETWEEN ? AND ? )
810 The RHS of in/not_in is either an expr/value or an arrayref of
814 { foo => { -in => [ 1, 2 ] } }
818 'in', { -ident => [ 'foo' ] }, { -bind => [ 'foo', 1 ] },
819 { -bind => [ 'foo', 2 ] },
827 { bar => { -not_in => \"(1, 2)" } }
831 [ 'not_in', { -ident => [ 'bar' ] }, { -literal => [ '1, 2' ] } ]
838 A non-trivial LHS is expanded with ident as the default rather than value:
842 { -row => [ 'x', 'y' ] }, { -row => [ 1, 2 ] },
843 { -row => [ 3, 4 ] },
848 'in', { -row => [ { -ident => [ 'x' ] }, { -ident => [ 'y' ] } ] },
849 { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] },
850 { -row => [ { -bind => [ undef, 3 ] }, { -bind => [ undef, 4 ] } ] },
854 (x, y) IN ( (?, ?), (?, ?) )
859 expands the same way as a plain arrayref/hashref expression but with the
860 logic type set to the op name.
864 Expands is and is_not to null checks, RHS value must be undef:
867 { -is => [ 'foo', undef ] }
870 { -op => [ 'is_null', { -ident => [ 'foo' ] } ] }
877 { bar => { -is_not => undef } }
880 { -op => [ 'is_not_null', { -ident => [ 'bar' ] } ] }
888 Expands a string ident to an arrayref by splitting on the configured
889 separator, almost always '.':
892 { -ident => 'foo.bar' }
895 { -ident => [ 'foo', 'bar' ] }
903 Expands to a bind node with the currently applicable column name if known:
906 { foo => { '=' => { -value => 3 } } }
909 { -op => [ '=', { -ident => [ 'foo' ] }, { -bind => [ 'foo', 3 ] } ] }
919 A select node accepts select, from, where and order_by clauses.
921 The select clause is expanded as a list expression with a -ident default:
924 { -select => { _ => [ 'foo', 'bar', { -count => 'baz' } ] } }
927 { -select => { select => { -op => [
928 ',', { -ident => [ 'foo' ] }, { -ident => [ 'bar' ] },
929 { -func => [ 'count', { -ident => [ 'baz' ] } ] },
933 SELECT foo, bar, COUNT(baz)
936 The from clause is expanded as a list expression with a -ident default:
940 from => [ 'schema1.table1', { -ident => [ 'schema2', 'table2' ] } ]
944 { -select => { from => { -from_list => [
945 { -ident => [ 'schema1', 'table1' ] },
946 { -ident => [ 'schema2', 'table2' ] },
950 FROM schema1.table1, schema2.table2
953 The where clause is expanded as a plain expression:
956 { -select => { where => { foo => 3 } } }
959 { -select => { where => {
960 -op => [ '=', { -ident => [ 'foo' ] }, { -bind => [ 'foo', 3 ] } ]
967 The order_by clause expands as a list expression at top level, but a hashref
968 element may be either an expr or a hashpair with key -asc or -desc to indicate
969 an order by direction:
973 { order_by => [ 'foo', { -desc => 'bar' }, { -max => 'baz' } ] }
977 { -select => { order_by => { -op => [
978 ',', { -ident => [ 'foo' ] }, {
979 -op => [ ',', { -op => [ 'desc', { -ident => [ 'bar' ] } ] } ]
980 }, { -func => [ 'max', { -ident => [ 'baz' ] } ] },
984 ORDER BY foo, bar DESC, MAX(baz)
989 An insert node accepts an into/target clause, a fields clause, a values/from
990 clause, and a returning clause.
992 The target clause is expanded with an ident default.
994 The fields clause is expanded as a list expression if an arrayref, and
995 otherwise passed through.
997 The from clause may either be an expr, a literal, an arrayref of column
998 values, or a hashref mapping colum names to values.
1000 The returning clause is expanded as a list expr with an ident default.
1006 values => { bar => 'yay', baz => 'argh' },
1012 { -row => [ { -ident => [ 'bar' ] }, { -ident => [ 'baz' ] } ] },
1013 from => { -values => [ { -row => [
1014 { -bind => [ 'bar', 'yay' ] },
1015 { -bind => [ 'baz', 'argh' ] },
1017 returning => { -op => [ ',', { -ident => [ 'id' ] } ] },
1018 target => { -op => [ ',', { -ident => [ 'foo' ] } ] },
1022 INSERT INTO foo (bar, baz) VALUES (?, ?) RETURNING id
1027 fields => [ 'bar', 'baz' ],
1028 from => { -select => { _ => [ 'bar', 'baz' ], from => 'other' } },
1034 fields => { -row => [ { -op =>
1035 [ ',', { -ident => [ 'bar' ] }, { -ident => [ 'baz' ] } ]
1037 from => { -select => {
1038 from => { -from_list => [ { -ident => [ 'other' ] } ] },
1040 [ ',', { -ident => [ 'bar' ] }, { -ident => [ 'baz' ] } ]
1043 target => { -op => [ ',', { -ident => [ 'foo' ] } ] },
1047 INSERT INTO foo (bar, baz) SELECT bar, baz FROM other
1052 An update node accepts update/target (either may be used at expansion time),
1053 set, where, and returning clauses.
1055 The target clause is expanded with an ident default.
1057 The set clause (if not already a list expr) is expanded as a hashref where
1058 the keys are identifiers to be set and the values are exprs/values.
1060 The where clauses is expanded as a normal expr.
1062 The returning clause is expanded as a list expr with an ident default.
1067 returning => [ 'id', 'baz' ],
1068 set => { bar => 3, baz => { baz => { '+' => 1 } } },
1069 where => { -not => { -ident => 'quux' } },
1076 -op => [ ',', { -ident => [ 'id' ] }, { -ident => [ 'baz' ] } ]
1080 [ '=', { -ident => [ 'bar' ] }, { -bind => [ 'bar', 3 ] } ]
1082 '=', { -ident => [ 'baz' ] }, { -op => [
1083 '+', { -ident => [ 'baz' ] },
1084 { -bind => [ 'baz', 1 ] },
1088 target => { -from_list => [ { -ident => [ 'foo' ] } ] },
1089 where => { -op => [ 'not', { -ident => [ 'quux' ] } ] },
1093 UPDATE foo SET bar = ?, baz = baz + ? WHERE (NOT quux) RETURNING id, baz
1098 delete accepts from/target, where, and returning clauses.
1100 The target clause is expanded with an ident default.
1102 The where clauses is expanded as a normal expr.
1104 The returning clause is expanded as a list expr with an ident default.
1110 where => { bar => { '<' => 10 } },
1115 returning => { -op => [ ',', { -ident => [ 'id' ] } ] },
1116 target => { -op => [ ',', { -ident => [ 'foo' ] } ] },
1118 [ '<', { -ident => [ 'bar' ] }, { -bind => [ 'bar', 10 ] } ]
1123 DELETE FROM foo WHERE bar < ? RETURNING id