1SQL::Abstract::Plugin::UEsxetrraCColnatursiebsu(t3e)d PeSrQlL:D:oAcbusmternatcatt:i:oPnlugin::ExtraClauses(3)
2
6 SQL::Abstract::ExtraClauses - new/experimental additions to
7 SQL::Abstract
8
10 my $sqla = SQL::Abstract->new;
11 SQL::Abstract::ExtraClauses->apply_to($sqla);
12
14 This module is basically a nursery for things that seem like a good
15 idea to live in until we figure out if we were right about that.
16
18 apply_to
19 Applies the plugin to an SQL::Abstract object.
20 register_extensions
22 Registers the extensions described below
23 cb
25 For plugin authors, creates a callback to call a method on the plugin.
26 register
28 For plugin authors, registers callbacks more easily.
29 sqla
31 Available only during plugin callback executions, contains the
32 currently active SQL::Abstract object.
33
35 alias
36 Represents a table alias. Expands name and column names with ident as
37 default.
38 # expr
40 { -alias => [ 't', 'x', 'y', 'z' ] }
41 # aqt
43 { -alias => [
44 { -ident => [ 't' ] }, { -ident => [ 'x' ] },
45 { -ident => [ 'y' ] }, { -ident => [ 'z' ] },
46 ] }
47 # query
49 t(x, y, z)
50 []
51 as
53 Represents an sql AS. LHS is expanded with ident as default, RHS is
54 treated as a list of arguments for the alias node.
55 # expr
57 { foo => { -as => 'bar' } }
58 # aqt
60 { -as => [ { -ident => [ 'foo' ] }, { -ident => [ 'bar' ] } ] }
61 # query
63 foo AS bar
64 []
65 # expr
67 { -as => [ { -select => { _ => 'blah' } }, 't', 'blah' ] }
68 # aqt
70 { -as => [
71 { -select =>
72 { select => { -op => [ ',', { -ident => [ 'blah' ] } ] } }
73 },
74 { -alias => [ { -ident => [ 't' ] }, { -ident => [ 'blah' ] } ] },
75 ] }
76 # query
78 (SELECT blah) AS t(blah)
79 []
80 cast
82 # expr
83 { -cast => [ { -ident => 'birthday' }, 'date' ] }
84 # aqt
86 { -func => [
87 'cast', {
88 -as => [ { -ident => [ 'birthday' ] }, { -ident => [ 'date' ] } ]
89 },
90 ] }
91 # query
93 CAST(birthday AS date)
94 []
95 join
97 If given an arrayref, pretends it was given a hashref with the first
98 element of the arrayref as the value for 'to' and the remaining pairs
99 copied.
100 Given a hashref, the 'as' key is if presented expanded to wrap the
102 'to'.
103 If present the 'using' key is expanded as a list of idents.
105 Known keys are: 'from' (the left hand side), 'type' ('left', 'right',
107 or nothing), 'to' (the right hand side), 'on' and 'using'.
108 # expr
110 { -join => {
111 from => 'lft',
112 on => { 'lft.bloo' => { '>' => 'rgt.blee' } },
113 to => 'rgt',
114 type => 'left',
115 } }
116 # aqt
118 { -join => {
119 from => { -ident => [ 'lft' ] },
120 on => { -op => [
121 '>', { -ident => [ 'lft', 'bloo' ] },
122 { -ident => [ 'rgt', 'blee' ] },
123 ] },
124 to => { -ident => [ 'rgt' ] },
125 type => 'left',
126 } }
127 # query
129 lft LEFT JOIN rgt ON lft.bloo > rgt.blee
130 []
131 from_list
133 List of components of the FROM clause; -foo type elements indicate a
134 pair with the next element; this is easiest if I show you:
135 # expr
137 { -from_list => [
138 't1', -as => 'table_one', -join =>
139 [ 't2', 'on', { 'table_one.x' => 't2.x' } ],
140 ] }
141 # aqt
143 { -join => {
144 from =>
145 {
146 -as => [ { -ident => [ 't1' ] }, { -ident => [ 'table_one' ] } ]
147 },
148 on => { -op => [
149 '=', { -ident => [ 'table_one', 'x' ] },
150 { -ident => [ 't2', 'x' ] },
151 ] },
152 to => { -ident => [ 't2' ] },
153 type => undef,
154 } }
155 # query
157 t1 AS table_one JOIN t2 ON table_one.x = t2.x
158 []
159 Or with using:
161 # expr
163 { -from_list =>
164 [ 't1', -as => 'table_one', -join => [ 't2', 'using', [ 'x' ] ] ]
165 }
166 # aqt
168 { -join => {
169 from =>
170 {
171 -as => [ { -ident => [ 't1' ] }, { -ident => [ 'table_one' ] } ]
172 },
173 to => { -ident => [ 't2' ] },
174 type => undef,
175 using =>
176 { -op => [ 'or', { -op => [ 'or', { -ident => [ 'x' ] } ] } ] },
177 } }
178 # query
180 t1 AS table_one JOIN t2 USING ( x )
181 []
182 With oddities:
184 # expr
186 { -from_list => [
187 'x', -join =>
188 [ [ 'y', -join => [ 'z', 'type', 'left' ] ], 'type', 'left' ],
189 ] }
190 # aqt
192 { -join => {
193 from => { -ident => [ 'x' ] },
194 to => { -join => {
195 from => { -ident => [ 'y' ] },
196 to => { -ident => [ 'z' ] },
197 type => 'left',
198 } },
199 type => 'left',
200 } }
201 # query
203 x LEFT JOIN ( y LEFT JOIN z )
204 []
205 setops
207 Expanders are provided for union, union_all, intersect, intersect_all,
208 except and except_all, and each takes an arrayref of queries:
209 # expr
211 { -union => [
212 { -select => { _ => { -value => 1 } } },
213 { -select => { _ => { -value => 2 } } },
214 ] }
215 # aqt
217 { -union => { queries => [
218 { -select =>
219 { select => { -op => [ ',', { -bind => [ undef, 1 ] } ] } }
220 },
221 { -select =>
222 { select => { -op => [ ',', { -bind => [ undef, 2 ] } ] } }
223 },
224 ] } }
225 # query
227 (SELECT ?) UNION (SELECT ?)
228 [ 1, 2 ]
229 # expr
231 { -union_all => [
232 { -select => { _ => { -value => 1 } } },
233 { -select => { _ => { -value => 2 } } },
234 { -select => { _ => { -value => 1 } } },
235 ] }
236 # aqt
238 { -union => {
239 queries => [
240 { -select =>
241 { select => { -op => [ ',', { -bind => [ undef, 1 ] } ] } }
242 },
243 { -select =>
244 { select => { -op => [ ',', { -bind => [ undef, 2 ] } ] } }
245 },
246 { -select =>
247 { select => { -op => [ ',', { -bind => [ undef, 1 ] } ] } }
248 },
249 ],
250 type => 'all',
251 } }
252 # query
254 (SELECT ?) UNION ALL (SELECT ?) UNION ALL (SELECT ?)
255 [ 1, 2, 1 ]
256
258 group by clause for select
259 Expanded as a list with an ident default:
260 # expr
262 { -select => { group_by => [ 'foo', 'bar' ] } }
263 # aqt
265 { -select => { group_by =>
266 {
267 -op => [ ',', { -ident => [ 'foo' ] }, { -ident => [ 'bar' ] } ]
268 }
269 } }
270 # query
272 GROUP BY foo, bar
273 []
274 having clause for select
276 Basic expr, just like where, given having is pretty much post-group-by
277 where clause:
278 # expr
280 { -select =>
281 { having => { '>' => [ { -count => { -ident => 'foo' } }, 3 ] } }
282 }
283 # aqt
285 { -select => { having => { -op => [
286 '>', { -func => [ 'count', { -ident => [ 'foo' ] } ] },
287 { -bind => [ undef, 3 ] },
288 ] } } }
289 # query
291 HAVING COUNT(foo) > ?
292 [ 3 ]
293 setop clauses
295 If a select query contains a clause matching any of the setop node
296 types, clauses that appear before the setop would in the resulting
297 query are gathered together and moved into an inner select node:
298 # expr
300 { -select => {
301 _ => '*',
302 from => 'foo',
303 order_by => 'baz',
304 union =>
305 {
306 -select => { _ => '*', from => 'bar', where => { thing => 1 } }
307 },
308 where => { thing => 1 },
309 } }
310 # aqt
312 { -select => {
313 order_by => { -op => [ ',', { -ident => [ 'baz' ] } ] },
314 setop => { -union => { queries => [
315 { -select => {
316 from => { -ident => [ 'foo' ] },
317 select => { -op => [ ',', { -ident => [ '*' ] } ] },
318 where => { -op => [
319 '=', { -ident => [ 'thing' ] },
320 { -bind => [ 'thing', 1 ] },
321 ] },
322 } }, ] },
323 { -select => {
324 from => { -ident => [ 'bar' ] },
325 select => { -op => [ ',', { -ident => [ '*' ] } ] },
326 where => { -op => [
327 '=', { -ident => [ 'thing' ] },
328 { -bind => [ 'thing', 1 ] },
329 } },
330 ] } },
331 } }
332 # query
334 (SELECT * FROM foo WHERE thing = ?) UNION (
335 SELECT * FROM bar WHERE thing = ?
336 )
337 ORDER BY baz
338 [ 1, 1 ]
339 update from clause
341 Some databases allow an additional FROM clause to reference other
342 tables for the data to update; this clause is expanded as a normal from
343 list, check your database for what is and isn't allowed in practice.
344 # expr
346 { -update => {
347 _ => 'employees',
348 from => 'accounts',
349 set => { sales_count => { sales_count => { '+' => \1 } } },
350 where => {
351 'accounts.name' => { '=' => \"'Acme Corporation'" },
352 'employees.id' => { -ident => 'accounts.sales_person' },
353 },
354 } }
355 # aqt
357 { -update => {
358 from => { -ident => [ 'accounts' ] },
359 set => { -op => [
360 ',', { -op => [
361 '=', { -ident => [ 'sales_count' ] }, { -op => [
362 '+', { -ident => [ 'sales_count' ] },
363 { -literal => [ 1 ] },
364 ] },
365 ] },
366 ] },
367 target => { -ident => [ 'employees' ] },
368 where => { -op => [
369 'and', { -op => [
370 '=', { -ident => [ 'accounts', 'name' ] },
371 { -literal => [ "'Acme Corporation'" ] },
372 ] }, { -op => [
373 '=', { -ident => [ 'employees', 'id' ] },
374 { -ident => [ 'accounts', 'sales_person' ] },
375 ] },
376 ] },
377 } }
378 # query
380 UPDATE employees SET sales_count = sales_count + 1 FROM accounts
381 WHERE (
382 accounts.name = 'Acme Corporation'
383 AND employees.id = accounts.sales_person
384 )
385 []
386 delete using clause
388 Some databases allow an additional USING clause to reference other
389 tables for the data to update; this clause is expanded as a normal from
390 list, check your database for what is and isn't allowed in practice.
391 # expr
393 { -delete => {
394 from => 'x',
395 using => 'y',
396 where => { 'x.id' => { -ident => 'y.x_id' } },
397 } }
398 # aqt
400 { -delete => {
401 target => { -op => [ ',', { -ident => [ 'x' ] } ] },
402 using => { -ident => [ 'y' ] },
403 where => { -op => [
404 '=', { -ident => [ 'x', 'id' ] },
405 { -ident => [ 'y', 'x_id' ] },
406 ] },
407 } }
408 # query
410 DELETE FROM x USING y WHERE x.id = y.x_id
411 []
412 insert rowvalues and select clauses
414 rowvalues and select are shorthand for
415 { from => { -select ... } }
417 and
419 { from => { -values ... } }
421 respectively:
423 # expr
425 { -insert =>
426 { into => 'numbers', rowvalues => [ [ 1, 2 ], [ 3, 4 ], [ 5, 6 ] ] }
427 }
428 # aqt
430 { -insert => {
431 from => { -values => [
432 { -row =>
433 [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ]
434 },
435 { -row =>
436 [ { -bind => [ undef, 3 ] }, { -bind => [ undef, 4 ] } ]
437 },
438 { -row =>
439 [ { -bind => [ undef, 5 ] }, { -bind => [ undef, 6 ] } ]
440 },
441 ] },
442 target => { -ident => [ 'numbers' ] },
443 } }
444 # query
446 INSERT INTO numbers VALUES (?, ?), (?, ?), (?, ?)
447 [ 1, 2, 3, 4, 5, 6 ]
448 # expr
450 { -insert =>
451 { into => 'numbers', select => { _ => '*', from => 'old_numbers' } }
452 }
453 # aqt
455 { -insert => {
456 from => { -select => {
457 from => { -ident => [ 'old_numbers' ] },
458 select => { -op => [ ',', { -ident => [ '*' ] } ] },
459 } },
460 target => { -ident => [ 'numbers' ] },
461 } }
462 # query
464 INSERT INTO numbers SELECT * FROM old_numbers
465 []
466 with and with_recursive clauses
468 These clauses are available on select/insert/update/delete queries;
469 check your database for applicability (e.g. mysql supports all four but
470 mariadb only select).
471 The value should be an arrayref of name/query pairs:
473 # expr
475 { -select => {
476 from => 'foo',
477 select => '*',
478 with => [ 'foo', { -select => { select => \1 } } ],
479 } }
480 # aqt
482 { -select => {
483 from => { -ident => [ 'foo' ] },
484 select => { -op => [ ',', { -ident => [ '*' ] } ] },
485 with => { queries => [ [
486 { -ident => [ 'foo' ] }, { -select =>
487 { select => { -op => [ ',', { -literal => [ 1 ] } ] } }
488 },
489 ] ] },
490 } }
491 # query
493 WITH foo AS (SELECT 1) SELECT * FROM foo
494 []
495 A more complete example (designed for mariadb, (ab)using the fact that
497 mysqloids materialise subselects in FROM into an unindexed temp table
498 to circumvent the restriction that you can't select from the table
499 you're currently updating:
500 # expr
502 { -update => {
503 _ => [
504 'tree_table', -join => {
505 as => 'tree',
506 on => { 'tree.id' => 'tree_with_path.id' },
507 to => { -select => {
508 from => 'tree_with_path',
509 select => '*',
510 with_recursive => [
511 [ 'tree_with_path', 'id', 'parent_id', 'path' ],
512 { -select => {
513 _ => [
514 'id', 'parent_id', { -as => [
515 { -cast => { -as => [ 'id', 'char', 255 ] } },
516 'path',
517 ] } ],
518 from => 'tree_table',
519 union_all => { -select => {
520 _ => [
521 't.id', 't.parent_id', { -as => [
522 { -concat => [ 'r.path', \"'/'", 't.id' ] },
523 'path',
524 ] },
525 ],
526 from => [
527 'tree_table', -as => 't', -join => {
528 as => 'r',
529 on => { 't.parent_id' => 'r.id' },
530 to => 'tree_with_path',
531 },
532 ],
533 } },
534 where => { parent_id => undef },
535 } },
536 ],
537 } },
538 },
539 ],
540 set => { path => { -ident => [ 'tree', 'path' ] } },
541 } }
542 # query
544 UPDATE
545 tree_table JOIN
546 (
547 WITH RECURSIVE
548 tree_with_path(id, parent_id, path) AS (
549 (
550 SELECT id, parent_id, CAST(id AS char(255)) AS path
551 FROM tree_table WHERE parent_id IS NULL
552 ) UNION ALL (
553 SELECT t.id, t.parent_id, CONCAT(r.path, '/', t.id) AS path
554 FROM
555 tree_table AS t JOIN tree_with_path AS r ON
556 t.parent_id = r.id
557 )
558 )
559 SELECT * FROM tree_with_path
560 ) AS tree
561 ON tree.id = tree_with_path.id
562 SET path = tree.path
563 []
564perl v5.34.0 2022-01S-Q2L1::Abstract::Plugin::ExtraClauses(3)