1SQL::Abstract::Plugin::UEsxetrraCColnatursiebsu(t3e)d PeSrQlL:D:oAcbusmternatcatt:i:oPnlugin::ExtraClauses(3)
2
3
4

NAME

6       SQL::Abstract::ExtraClauses - new/experimental additions to
7       SQL::Abstract
8

SYNOPSIS

10         my $sqla = SQL::Abstract->new;
11         SQL::Abstract::ExtraClauses->apply_to($sqla);
12

WARNING

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

METHODS

18   apply_to
19       Applies the plugin to an SQL::Abstract object.
20
21   register_extensions
22       Registers the extensions described below
23
24   cb
25       For plugin authors, creates a callback to call a method on the plugin.
26
27   register
28       For plugin authors, registers callbacks more easily.
29
30   sqla
31       Available only during plugin callback executions, contains the
32       currently active SQL::Abstract object.
33

NODE TYPES

35   alias
36       Represents a table alias. Expands name and column names with ident as
37       default.
38
39         # expr
40         { -alias => [ 't', 'x', 'y', 'z' ] }
41
42         # aqt
43         { -alias => [
44             { -ident => [ 't' ] }, { -ident => [ 'x' ] },
45             { -ident => [ 'y' ] }, { -ident => [ 'z' ] },
46         ] }
47
48         # query
49         t(x, y, z)
50         []
51
52   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
56         # expr
57         { foo => { -as => 'bar' } }
58
59         # aqt
60         { -as => [ { -ident => [ 'foo' ] }, { -ident => [ 'bar' ] } ] }
61
62         # query
63         foo AS bar
64         []
65
66         # expr
67         { -as => [ { -select => { _ => 'blah' } }, 't', 'blah' ] }
68
69         # aqt
70         { -as => [
71             { -select =>
72                 { select => { -op => [ ',', { -ident => [ 'blah' ] } ] } }
73             },
74             { -alias => [ { -ident => [ 't' ] }, { -ident => [ 'blah' ] } ] },
75         ] }
76
77         # query
78         (SELECT blah) AS t(blah)
79         []
80
81   cast
82         # expr
83         { -cast => [ { -ident => 'birthday' }, 'date' ] }
84
85         # aqt
86         { -func => [
87             'cast', {
88               -as => [ { -ident => [ 'birthday' ] }, { -ident => [ 'date' ] } ]
89             },
90         ] }
91
92         # query
93         CAST(birthday AS date)
94         []
95
96   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
101       Given a hashref, the 'as' key is if presented expanded to wrap the
102       'to'.
103
104       If present the 'using' key is expanded as a list of idents.
105
106       Known keys are: 'from' (the left hand side), 'type' ('left', 'right',
107       or nothing), 'to' (the right hand side), 'on' and 'using'.
108
109         # expr
110         { -join => {
111             from => 'lft',
112             on => { 'lft.bloo' => { '>' => 'rgt.blee' } },
113             to => 'rgt',
114             type => 'left',
115         } }
116
117         # 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
128         # query
129         lft LEFT JOIN rgt ON lft.bloo > rgt.blee
130         []
131
132   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
136         # expr
137         { -from_list => [
138             't1', -as => 'table_one', -join =>
139             [ 't2', 'on', { 'table_one.x' => 't2.x' } ],
140         ] }
141
142         # 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
156         # query
157         t1 AS table_one JOIN t2 ON table_one.x = t2.x
158         []
159
160       Or with using:
161
162         # expr
163         { -from_list =>
164             [ 't1', -as => 'table_one', -join => [ 't2', 'using', [ 'x' ] ] ]
165         }
166
167         # 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
179         # query
180         t1 AS table_one JOIN t2 USING ( x )
181         []
182
183       With oddities:
184
185         # expr
186         { -from_list => [
187             'x', -join =>
188             [ [ 'y', -join => [ 'z', 'type', 'left' ] ], 'type', 'left' ],
189         ] }
190
191         # 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
202         # query
203         x LEFT JOIN ( y LEFT JOIN z )
204         []
205
206   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
210         # expr
211         { -union => [
212             { -select => { _ => { -value => 1 } } },
213             { -select => { _ => { -value => 2 } } },
214         ] }
215
216         # aqt
217         { -union => { queries => [
218               { -select =>
219                   { select => { -op => [ ',', { -bind => [ undef, 1 ] } ] } }
220               },
221               { -select =>
222                   { select => { -op => [ ',', { -bind => [ undef, 2 ] } ] } }
223               },
224         ] } }
225
226         # query
227         (SELECT ?) UNION (SELECT ?)
228         [ 1, 2 ]
229
230         # expr
231         { -union_all => [
232             { -select => { _ => { -value => 1 } } },
233             { -select => { _ => { -value => 2 } } },
234             { -select => { _ => { -value => 1 } } },
235         ] }
236
237         # 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
253         # query
254         (SELECT ?) UNION ALL (SELECT ?) UNION ALL (SELECT ?)
255         [ 1, 2, 1 ]
256

STATEMENT EXTENSIONS

258   group by clause for select
259       Expanded as a list with an ident default:
260
261         # expr
262         { -select => { group_by => [ 'foo', 'bar' ] } }
263
264         # aqt
265         { -select => { group_by =>
266               {
267                 -op => [ ',', { -ident => [ 'foo' ] }, { -ident => [ 'bar' ] } ]
268               }
269         } }
270
271         # query
272         GROUP BY foo, bar
273         []
274
275   having clause for select
276       Basic expr, just like where, given having is pretty much post-group-by
277       where clause:
278
279         # expr
280         { -select =>
281             { having => { '>' => [ { -count => { -ident => 'foo' } }, 3 ] } }
282         }
283
284         # aqt
285         { -select => { having => { -op => [
286                 '>', { -func => [ 'count', { -ident => [ 'foo' ] } ] },
287                 { -bind => [ undef, 3 ] },
288         ] } } }
289
290         # query
291         HAVING COUNT(foo) > ?
292         [ 3 ]
293
294   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
299         # 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
311         # 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
333         # query
334         (SELECT * FROM foo WHERE thing = ?) UNION (
335           SELECT * FROM bar WHERE thing = ?
336         )
337         ORDER BY baz
338         [ 1, 1 ]
339
340   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
345         # 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
356         # 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
379         # 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
387   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
392         # expr
393         { -delete => {
394             from => 'x',
395             using => 'y',
396             where => { 'x.id' => { -ident => 'y.x_id' } },
397         } }
398
399         # 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
409         # query
410         DELETE FROM x USING y WHERE x.id = y.x_id
411         []
412
413   insert rowvalues and select clauses
414       rowvalues and select are shorthand for
415
416         { from => { -select ... } }
417
418       and
419
420         { from => { -values ... } }
421
422       respectively:
423
424         # expr
425         { -insert =>
426             { into => 'numbers', rowvalues => [ [ 1, 2 ], [ 3, 4 ], [ 5, 6 ] ] }
427         }
428
429         # 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
445         # query
446         INSERT INTO numbers VALUES (?, ?), (?, ?), (?, ?)
447         [ 1, 2, 3, 4, 5, 6 ]
448
449         # expr
450         { -insert =>
451             { into => 'numbers', select => { _ => '*', from => 'old_numbers' } }
452         }
453
454         # aqt
455         { -insert => {
456             from => { -select => {
457                 from => { -ident => [ 'old_numbers' ] },
458                 select => { -op => [ ',', { -ident => [ '*' ] } ] },
459             } },
460             target => { -ident => [ 'numbers' ] },
461         } }
462
463         # query
464         INSERT INTO numbers SELECT * FROM old_numbers
465         []
466
467   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
472       The value should be an arrayref of name/query pairs:
473
474         # expr
475         { -select => {
476             from => 'foo',
477             select => '*',
478             with => [ 'foo', { -select => { select => \1 } } ],
479         } }
480
481         # 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
492         # query
493         WITH foo AS (SELECT 1) SELECT * FROM foo
494         []
495
496       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
501         # 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
543         # 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         []
564
565
566
567perl v5.32.1                      2021-01S-Q2L7::Abstract::Plugin::ExtraClauses(3)
Impressum