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 =head3 identifier hashpair w/literal RHS
316 Directly appended to the key, remember you need to provide an operator:
319 { id => \"= dont_try_this_at_home" }
322 { -literal => [ 'id = dont_try_this_at_home' ] }
325 id = dont_try_this_at_home
330 "= seriously(?, ?, ?, ?, ?)",
340 [ 'id = seriously(?, ?, ?, ?, ?)', 'use', -ident => 'and', '-func' ]
344 id = seriously(?, ?, ?, ?, ?)
345 [ 'use', -ident => 'and', '-func' ]
347 (you may absolutely use this when there's no built-in expression type for
348 what you need and registering a custom one would be more hassle than it's
349 worth, but, y'know, do try and avoid it)
351 =head3 identifier hashpair w/arrayref value
353 Becomes equivalent to a -or over an arrayref of hashrefs with the identifier
354 as key and the member of the original arrayref as the value:
357 { id => [ 3, 4, { '>' => 12 } ] }
362 { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
363 { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
365 -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ]
370 ( id = ? OR id = ? OR id > ? )
374 { -or => [ { id => 3 }, { id => 4 }, { id => { '>' => 12 } } ] }
379 { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
380 { -op => [ '=', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
382 -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 12 ] } ]
387 ( id = ? OR id = ? OR id > ? )
390 Special Case: If the first element of the arrayref is -or or -and, that's
391 used as the top level logic op:
394 { id => [ -and => { '>' => 3 }, { '<' => 6 } ] }
399 { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
400 { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 6 ] } ] },
404 ( id > ? AND id < ? )
407 =head3 identifier hashpair w/hashref value
409 Becomes equivalent to a -and over an arrayref of hashtriples constructed
410 with the identifier as the key and each key/value pair of the original
414 { id => { '<' => 4, '>' => 3 } }
419 { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
420 { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
424 ( id < ? AND id > ? )
428 { -and => [ { id => { '<' => 4 } }, { id => { '>' => 3 } } ] }
433 { -op => [ '<', { -ident => [ 'id' ] }, { -bind => [ 'id', 4 ] } ] },
434 { -op => [ '>', { -ident => [ 'id' ] }, { -bind => [ 'id', 3 ] } ] },
438 ( id < ? AND id > ? )