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' },
152 { -op => [ 'not', { -ident => 'explosive' } ] }
158 Postfix unop: (is_null, is_not_null, asc, desc)
161 { -op => [ 'is_null', { -ident => [ 'bobby' ] } ] }
171 [ 'and', { -ident => 'x' }, { -ident => 'y' }, { -ident => 'z' } ]
182 'in', { -ident => 'card' }, { -bind => [ 'card', 3 ] },
183 { -bind => [ 'card', 'J' ] },
190 BETWEEN (and NOT BETWEEN):
194 'between', { -ident => 'pints' }, { -bind => [ 'pints', 2 ] },
195 { -bind => [ 'pints', 4 ] },
199 ( pints BETWEEN ? AND ? )
202 Comma (use -row for parens):
205 { -op => [ ',', { -literal => [ 1 ] }, { -literal => [ 2 ] } ] }
215 { -row => [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] }
224 { -row => [ { -literal => [ 1 ] }, { -literal => [ 2 ] } ] },
225 { -row => [ { -literal => [ 3 ] }, { -literal => [ 4 ] } ] },
229 VALUES (1, 2), (3, 4)
232 =head2 statement types
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.
241 The simplest expression is just an AQT node:
244 { -ident => [ 'foo', 'bar' ] }
247 { -ident => [ 'foo', 'bar' ] }
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:
257 { -ident => 'foo.bar' }
260 { -ident => [ 'foo', 'bar' ] }
266 =head2 identifier hashpair types
271 { id => { op => 'value' } }
275 [ 'op', { -ident => [ 'id' ] }, { -bind => [ 'id', 'value' ] } ]
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:
286 { id => { '!=' => undef } }
289 { -op => [ 'is_not_null', { -ident => [ 'id' ] } ] }
295 =head3 identifier hashpair w/simple value
297 Equivalent to a hashtriple with an op of '='.
304 -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 'value' ] } ]
311 (an object value will also follow this code path)
313 =head3 identifier hashpair w/undef RHS
315 Converted to IS NULL :
321 { -op => [ 'is_null', { -ident => [ 'id' ] } ] }
327 (equivalent to the -is operator) :
330 { id => { -is => undef } }
333 { -op => [ 'is_null', { -ident => [ 'id' ] } ] }
339 =head3 identifier hashpair w/literal RHS
341 Directly appended to the key, remember you need to provide an operator:
344 { id => \"= dont_try_this_at_home" }
347 { -literal => [ 'id = dont_try_this_at_home' ] }
350 id = dont_try_this_at_home
355 "= seriously(?, ?, ?, ?, ?)",
365 [ 'id = seriously(?, ?, ?, ?, ?)', 'use', -ident => 'and', '-func' ]
369 id = seriously(?, ?, ?, ?, ?)
370 [ 'use', -ident => 'and', '-func' ]
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)
376 =head3 identifier hashpair w/arrayref value
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:
382 { id => [ 3, 4, { '>' => 12 } ] }
387 { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
388 { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
390 -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ]
395 ( id = ? OR id = ? OR id > ? )
399 { -or => [ { id => 3 }, { id => 4 }, { id => { '>' => 12 } } ] }
404 { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
405 { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
407 -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ]
412 ( id = ? OR id = ? OR id > ? )
415 Special Case: If the first element of the arrayref is -or or -and, that's
416 used as the top level logic op:
419 { id => [ -and => { '>' => 3 }, { '<' => 6 } ] }
424 { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
425 { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 6 ] } ] },
429 ( id > ? AND id < ? )
432 =head3 identifier hashpair w/hashref value
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
436 hashref as the value:
439 { id => { '<' => 4, '>' => 3 } }
444 { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
445 { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
449 ( id < ? AND id > ? )
455 { -and => [ { id => { '<' => 4 } }, { id => { '>' => 3 } } ] }
460 { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
461 { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
465 ( id < ? AND id > ? )
468 =head2 operator hashpair types
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.
474 =head3 operator hashpair w/node type
476 If a node type expander is registered for the key, the hashpair is
477 treated as a L</node expr>.
479 =head3 operator hashpair w/registered op
481 If an expander is registered for the op name, that's run and the
485 { -in => [ 'foo', 1, 2, 3 ] }
489 'in', { -ident => [ 'foo' ] }, { -bind => [ undef, 1 ] },
490 { -bind => [ undef, 2 ] }, { -bind => [ undef, 3 ] },
497 =head3 operator hashpair w/not prefix
499 If the op name starts -not_ this is stripped and turned into a -not
500 wrapper around the result:
503 { -not_ident => 'foo' }
506 { -op => [ 'not', { -ident => [ 'foo' ] } ] }
515 { -not => { -ident => 'foo' } }
518 { -op => [ 'not', { -ident => [ 'foo' ] } ] }
524 =head3 operator hashpair with unknown op
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:
531 { -count => { -ident => '*' } }
534 { -func => [ 'count', { -ident => [ '*' ] } ] }
540 If not, an unknown op will expand into a C<-op> node.
544 A hashref with more than one pair becomes a C<-and> over its hashpairs, i.e.
552 { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] },
553 { -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] },
563 { -and => [ { x => 1 }, { y => 2 } ] }
568 { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] },
569 { -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] },
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.
584 [ { x => 1 }, [ { y => 2 }, { z => 3 } ], 'key', 'value', \"lit()" ]
589 { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] },
592 -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ]
594 -op => [ '=', { -ident => [ 'z' ] }, { -bind => [ 'z', 3 ] } ]
598 '=', { -ident => [ 'key' ] },
599 { -bind => [ 'key', 'value' ] },
602 { -literal => [ 'lit()' ] },
606 ( x = ? OR ( y = ? OR z = ? ) OR key = ? OR lit() )