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 =head3 identifier hashpair w/simple value
284 Equivalent to a hashtriple with an op of '='.
291 -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 'value' ] } ]
298 (an object value will also follow this code path)
300 =head3 identifier hashpair w/undef RHS
302 Converted to IS NULL :
308 { -op => [ 'is_null', { -ident => [ 'id' ] } ] }
314 (equivalent to the -is operator) :
317 { id => { -is => undef } }
320 { -op => [ 'is_null', { -ident => [ 'id' ] } ] }
326 =head3 identifier hashpair w/literal RHS
328 Directly appended to the key, remember you need to provide an operator:
331 { id => \"= dont_try_this_at_home" }
334 { -literal => [ 'id = dont_try_this_at_home' ] }
337 id = dont_try_this_at_home
342 "= seriously(?, ?, ?, ?, ?)",
352 [ 'id = seriously(?, ?, ?, ?, ?)', 'use', -ident => 'and', '-func' ]
356 id = seriously(?, ?, ?, ?, ?)
357 [ 'use', -ident => 'and', '-func' ]
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)
363 =head3 identifier hashpair w/arrayref value
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:
369 { id => [ 3, 4, { '>' => 12 } ] }
374 { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
375 { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
377 -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ]
382 ( id = ? OR id = ? OR id > ? )
386 { -or => [ { id => 3 }, { id => 4 }, { id => { '>' => 12 } } ] }
391 { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
392 { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
394 -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ]
399 ( id = ? OR id = ? OR id > ? )
402 Special Case: If the first element of the arrayref is -or or -and, that's
403 used as the top level logic op:
406 { id => [ -and => { '>' => 3 }, { '<' => 6 } ] }
411 { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
412 { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 6 ] } ] },
416 ( id > ? AND id < ? )
419 =head3 identifier hashpair w/hashref value
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
423 hashref as the value:
426 { id => { '<' => 4, '>' => 3 } }
431 { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
432 { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
436 ( id < ? AND id > ? )
442 { -and => [ { id => { '<' => 4 } }, { id => { '>' => 3 } } ] }
447 { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
448 { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
452 ( id < ? AND id > ? )
455 =head2 operator hashpair types
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.
461 =head3 operator hashpair w/node type
463 If a node type expander is registered for the key, the hashpair is
464 treated as a L</node expr>.
466 =head3 operator hashpair w/registered op
468 If an expander is registered for the op name, that's run and the
472 { -in => [ 'foo', 1, 2, 3 ] }
476 'in', { -ident => [ 'foo' ] }, { -bind => [ undef, 1 ] },
477 { -bind => [ undef, 2 ] }, { -bind => [ undef, 3 ] },
484 =head3 operator hashpair w/not prefix
486 If the op name starts -not_ this is stripped and turned into a -not
487 wrapper around the result:
490 { -not_ident => 'foo' }
493 { -op => [ 'not', { -ident => [ 'foo' ] } ] }
502 { -not => { -ident => 'foo' } }
505 { -op => [ 'not', { -ident => [ 'foo' ] } ] }
511 =head3 operator hashpair with unknown op
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:
518 { -count => { -ident => '*' } }
521 { -func => [ 'count', { -ident => [ '*' ] } ] }
527 If not, an unknown op will expand into a C<-op> node.
531 A hashref with more than one pair becomes a C<-and> over its hashpairs, i.e.
539 { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] },
540 { -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] },
550 { -and => [ { x => 1 }, { y => 2 } ] }
555 { -op => [ '=', { -ident => [ 'x' ] }, { -bind => [ 'x', 1 ] } ] },
556 { -op => [ '=', { -ident => [ 'y' ] }, { -bind => [ 'y', 2 ] } ] },