lib/SQL/Abstract/Plugin/ExtraClauses.pm

   1 package SQL::Abstract::Plugin::ExtraClauses;
   2
   3 use Moo;
   4
   5 with 'SQL::Abstract::Role::Plugin';
   6
   7 sub register_extensions {
   8   my ($self, $sqla) = @_;
   9
  10   my @clauses = $sqla->clauses_of('select');
  11   my @before_setop;
  12   CLAUSE: foreach my $idx (0..$#clauses) {
  13     if ($clauses[$idx] eq 'order_by') {
  14       @before_setop = @clauses[0..$idx-1];
  15       splice(@clauses, $idx, 0, qw(setop group_by having));
  16       last CLAUSE;
  17     }
  18   }
  19
  20   die "Huh?" unless @before_setop;
  21   $sqla->clauses_of(select => @clauses);
  22
  23   $sqla->clauses_of(update => sub {
  24     my ($self, @clauses) = @_;
  25     splice(@clauses, 2, 0, 'from');
  26     @clauses;
  27   });
  28
  29   $sqla->clauses_of(delete => sub {
  30     my ($self, @clauses) = @_;
  31     splice(@clauses, 1, 0, 'using');
  32     @clauses;
  33   });
  34
  35   $self->register(
  36     (map +(
  37       "${_}er" => [
  38         do {
  39           my $x = $_;
  40           (map +($_ => "_${x}_${_}"), qw(join from_list alias))
  41         }
  42        ]
  43     ), qw(expand render)),
  44     binop_expander => [ as => '_expand_op_as' ],
  45     renderer => [ as => '_render_as' ],
  46     expander => [ cast => '_expand_cast' ],
  47     clause_expanders => [
  48       "select.from", '_expand_from_list',
  49       'select.group_by'
  50         => sub { $_[0]->expand_expr({ -list => $_[2] }, -ident) },
  51       'select.having'
  52         => sub { $_[0]->expand_expr($_[2]) },
  53       'update.from' => '_expand_from_list',
  54       "update.target", '_expand_update_clause_target',
  55       "update.update", '_expand_update_clause_target',
  56       'delete.using' => '_expand_from_list',
  57       'insert.rowvalues' => sub {
  58         +(from => $_[0]->expand_expr({ -values => $_[2] }));
  59       },
  60       'insert.select' => sub {
  61         +(from => $_[0]->expand_expr({ -select => $_[2] }));
  62       },
  63     ],
  64   );
  65
  66   # set ops
  67   $sqla->wrap_expander(select => sub {
  68     $self->cb('_expand_select', $_[0], \@before_setop);
  69   });
  70
  71   $self->register(
  72     clause_renderer => [
  73       'select.setop' => sub { $_[0]->render_aqt($_[2]) }
  74     ],
  75     expander => [
  76       map +($_ => '_expand_setop', "${_}_all" => '_expand_setop'), qw(union intersect except) ],
  77     renderer => [ map +($_ => '_render_setop'), qw(union intersect except) ],
  78   );
  79
  80   my $setop_expander = $self->cb('_expand_clause_setop');
  81
  82   $sqla->clause_expanders(
  83     map +($_ => $setop_expander),
  84       map "select.${_}",
  85         map +($_, "${_}_all", "${_}_distinct"),
  86           qw(union intersect except)
  87   );
  88
  89   foreach my $stmt (qw(select insert update delete)) {
  90     $sqla->clauses_of($stmt => 'with', $sqla->clauses_of($stmt));
  91     $self->register(
  92       clause_expanders => [
  93         "${stmt}.with" => '_expand_with',
  94         "${stmt}.with_recursive" => '_expand_with',
  95       ],
  96       clause_renderer => [ "${stmt}.with" => '_render_with' ],
  97     );
  98   }
  99
 100   return $sqla;
 101 }
 102
 103 sub _expand_select {
 104   my ($self, $orig, $before_setop, @args) = @_;
 105   my $exp = $self->sqla->$orig(@args);
 106   return $exp unless my $setop = (my $sel = $exp->{-select})->{setop};
 107   if (my @keys = grep $sel->{$_}, @$before_setop) {
 108     my %inner; @inner{@keys} = delete @{$sel}{@keys};
 109     unshift @{(values(%$setop))[0]{queries}},
 110       { -select => \%inner };
 111   }
 112   return $exp;
 113 }
 114
 115 sub _expand_from_list {
 116   my ($self, undef, $args) = @_;
 117   if (ref($args) eq 'HASH') {
 118     return $args if $args->{-from_list};
 119     return { -from_list => [ $self->expand_expr($args) ] };
 120   }
 121   my @list;
 122   my @args = ref($args) eq 'ARRAY' ? @$args : ($args);
 123   while (my $entry = shift @args) {
 124     if (!ref($entry) and $entry =~ /^-(.*)/) {
 125       if ($1 eq 'as') {
 126         $list[-1] = $self->expand_expr({ -as => [
 127           $list[-1], map +(ref($_) eq 'ARRAY' ? @$_ : $_), shift(@args)
 128         ]});
 129         next;
 130       }
 131       $entry = { $entry => shift @args };
 132     }
 133     my $aqt = $self->expand_expr($entry, -ident);
 134     if ($aqt->{-join} and not $aqt->{-join}{from}) {
 135       $aqt->{-join}{from} = pop @list;
 136     }
 137     push @list, $aqt;
 138   }
 139   return $list[0] if @list == 1;
 140   return { -from_list => \@list };
 141 }
 142
 143 sub _expand_join {
 144   my ($self, undef, $args) = @_;
 145   my %proto = (
 146     ref($args) eq 'HASH'
 147       ? %$args
 148       : (to => @$args)
 149   );
 150   if (my $as = delete $proto{as}) {
 151     $proto{to} = $self->expand_expr(
 152                    { -as => [ { -from_list => $proto{to} }, $as ] }
 153                  );
 154   }
 155   if (defined($proto{using}) and ref(my $using = $proto{using}) ne 'HASH') {
 156     $proto{using} = [
 157       map [ $self->expand_expr($_, -ident) ],
 158         ref($using) eq 'ARRAY' ? @$using: $using
 159     ];
 160   }
 161   my %ret = (
 162     type => delete $proto{type},
 163     to => $self->expand_expr({ -from_list => delete $proto{to} }, -ident)
 164   );
 165   %ret = (%ret,
 166     map +($_ => $self->expand_expr($proto{$_}, -ident)),
 167       sort keys %proto
 168   );
 169   return +{ -join => \%ret };
 170 }
 171
 172 sub _render_from_list {
 173   my ($self, undef, $list) = @_;
 174   return $self->join_query_parts(', ', @$list);
 175 }
 176
 177 sub _render_join {
 178   my ($self, undef, $args) = @_;
 179
 180   my @parts = (
 181     $args->{from},
 182     { -keyword => join '_', ($args->{type}||()), 'join' },
 183     (map +($_->{-ident} || $_->{-as}
 184       ? $_
 185       : ('(', $self->render_aqt($_, 1), ')')),
 186         map +(@{$_->{-from_list}||[]} == 1 ? $_->{-from_list}[0] : $_),
 187           $args->{to}
 188     ),
 189     ($args->{on} ? (
 190       { -keyword => 'on' },
 191       $args->{on},
 192     ) : ()),
 193     ($args->{using} ? (
 194       { -keyword => 'using' },
 195       '(', $args->{using}, ')',
 196     ) : ()),
 197   );
 198   return $self->join_query_parts(' ', @parts);
 199 }
 200
 201 sub _expand_op_as {
 202   my ($self, undef, $vv, $k) = @_;
 203   my @vv = (ref($vv) eq 'ARRAY' ? @$vv : $vv);
 204   my $ik = $self->expand_expr($k, -ident);
 205   return +{ -as => [ $ik, $self->expand_expr($vv[0], -ident) ] }
 206     if @vv == 1 and ref($vv[0]) eq 'HASH';
 207
 208   my @as = map $self->expand_expr($_, -ident), @vv;
 209   return { -as => [ $ik, $self->expand_expr({ -alias => \@as }) ] };
 210 }
 211
 212 sub _render_as {
 213   my ($self, undef, $args) = @_;
 214   my ($thing, $alias) = @$args;
 215   return $self->join_query_parts(
 216     ' ',
 217     $thing,
 218     { -keyword => 'as' },
 219     $alias,
 220   );
 221 }
 222
 223 sub _render_alias {
 224   my ($self, undef, $args) = @_;
 225   my ($as, @cols) = @$args;
 226   return (@cols
 227     ? $self->join_query_parts('',
 228          $as,
 229          '(',
 230          $self->join_query_parts(
 231            ', ',
 232            @cols
 233          ),
 234          ')',
 235       )
 236     : $self->render_aqt($as)
 237   );
 238 }
 239
 240 sub _expand_update_clause_target {
 241   my ($self, undef, $target) = @_;
 242   +(target => $self->_expand_from_list(undef, $target));
 243 }
 244
 245 sub _expand_cast {
 246   my ($self, undef, $thing) = @_;
 247   return { -func => [ cast => $thing ] } if ref($thing) eq 'HASH';
 248   my ($cast, $to) = @{$thing};
 249   +{ -func => [ cast => { -as => [
 250     $self->expand_expr($cast),
 251     $self->expand_expr($to, -ident),
 252   ] } ] };
 253 }
 254
 255 sub _expand_alias {
 256   my ($self, undef, $args) = @_;
 257   if (ref($args) eq 'HASH' and my $alias = $args->{-alias}) {
 258     $args = $alias;
 259   }
 260   my @parts = map $self->expand_expr($_, -ident),
 261                 ref($args) eq 'ARRAY' ? @{$args} : $args;
 262   return $parts[0] if @parts == 1;
 263   return { -alias => \@parts };
 264 }
 265
 266 sub _expand_with {
 267   my ($self, $name, $with) = @_;
 268   my (undef, $type) = split '_', $name;
 269   if (ref($with) eq 'HASH') {
 270     return +{
 271       %$with,
 272       queries => [
 273         map +[
 274           $self->expand_expr({ -alias => $_->[0] }, -ident),
 275           $self->expand_expr($_->[1]),
 276         ], @{$with->{queries}}
 277       ]
 278     }
 279   }
 280   my @with = @$with;
 281   my @exp;
 282   while (my ($alias, $query) = splice @with, 0, 2) {
 283     push @exp, [
 284       $self->expand_expr({ -alias => $alias }, -ident),
 285       $self->expand_expr($query)
 286     ];
 287   }
 288   return +(with => { ($type ? (type => $type) : ()), queries => \@exp });
 289 }
 290
 291 sub _render_with {
 292   my ($self, undef, $with) = @_;
 293   my $q_part = $self->join_query_parts(', ',
 294     map {
 295       my ($alias, $query) = @$_;
 296       $self->join_query_parts(' ',
 297           $alias,
 298           { -keyword => 'as' },
 299           $query,
 300       )
 301     } @{$with->{queries}}
 302   );
 303   return $self->join_query_parts(' ',
 304     { -keyword => join '_', 'with', ($with->{type}||'') },
 305     $q_part,
 306   );
 307 }
 308
 309 sub _expand_setop {
 310   my ($self, $setop, $args) = @_;
 311   my $is_all = $setop =~ s/_all$//;
 312   +{ "-${setop}" => {
 313        ($is_all ? (type => 'all') : ()),
 314        (ref($args) eq 'ARRAY'
 315           ? (queries => [ map $self->expand_expr($_), @$args ])
 316           : (
 317               %$args,
 318               queries => [ map $self->expand_expr($_), @{$args->{queries}} ]
 319             )
 320        ),
 321   } };
 322 }
 323
 324 sub _render_setop {
 325   my ($self, $setop, $args) = @_;
 326   $self->join_query_parts(
 327     { -keyword => ' '.join('_', $setop, ($args->{type}||())).' ' },
 328     @{$args->{queries}}
 329   );
 330 }
 331
 332 sub _expand_clause_setop {
 333   my ($self, $setop, $args) = @_;
 334   my ($op, $type) = split '_', $setop;
 335   +(setop => $self->expand_expr({
 336     "-${op}" => {
 337       ($type ? (type => $type) : ()),
 338       queries => (ref($args) eq 'ARRAY' ? $args : [ $args ])
 339     }
 340   }));
 341 }
 342
 343 1;
 344
 345 __END__
 346
 347 =head1 NAME
 348
 349 SQL::Abstract::ExtraClauses - new/experimental additions to L<SQL::Abstract>
 350
 351 =head1 SYNOPSIS
 352
 353   my $sqla = SQL::Abstract->new;
 354   SQL::Abstract::ExtraClauses->apply_to($sqla);
 355
 356 =head1 WARNING
 357
 358 This module is basically a nursery for things that seem like a good idea
 359 to live in until we figure out if we were right about that.
 360
 361 =head1 METHODS
 362
 363 =head2 apply_to
 364
 365 Applies the plugin to an L<SQL::Abstract> object.
 366
 367 =head2 register_extensions
 368
 369 Registers the extensions described below
 370
 371 =head2 cb
 372
 373 For plugin authors, creates a callback to call a method on the plugin.
 374
 375 =head2 register
 376
 377 For plugin authors, registers callbacks more easily.
 378
 379 =head2 sqla
 380
 381 Available only during plugin callback executions, contains the currently
 382 active L<SQL::Abstract> object.
 383
 384 =head1 NODE TYPES
 385
 386 =head2 alias
 387
 388 Represents a table alias. Expands name and column names with ident as default.
 389
 390   # expr
 391   { -alias => [ 't', 'x', 'y', 'z' ] }
 392
 393   # aqt
 394   { -alias => [
 395       { -ident => [ 't' ] }, { -ident => [ 'x' ] },
 396       { -ident => [ 'y' ] }, { -ident => [ 'z' ] },
 397   ] }
 398
 399   # query
 400   t(x, y, z)
 401   []
 402
 403 =head2 as
 404
 405 Represents an sql AS. LHS is expanded with ident as default, RHS is treated
 406 as a list of arguments for the alias node.
 407
 408   # expr
 409   { foo => { -as => 'bar' } }
 410
 411   # aqt
 412   { -as => [ { -ident => [ 'foo' ] }, { -ident => [ 'bar' ] } ] }
 413
 414   # query
 415   foo AS bar
 416   []
 417
 418   # expr
 419   { -as => [ { -select => { _ => 'blah' } }, 't', 'blah' ] }
 420
 421   # aqt
 422   { -as => [
 423       { -select =>
 424           { select => { -op => [ ',', { -ident => [ 'blah' ] } ] } }
 425       },
 426       { -alias => [ { -ident => [ 't' ] }, { -ident => [ 'blah' ] } ] },
 427   ] }
 428
 429   # query
 430   (SELECT blah) AS t(blah)
 431   []
 432
 433 =head2 cast
 434
 435   # expr
 436   { -cast => [ { -ident => 'birthday' }, 'date' ] }
 437
 438   # aqt
 439   { -func => [
 440       'cast', {
 441         -as => [ { -ident => [ 'birthday' ] }, { -ident => [ 'date' ] } ]
 442       },
 443   ] }
 444
 445   # query
 446   CAST(birthday AS date)
 447   []
 448
 449 =head2 join
 450
 451 If given an arrayref, pretends it was given a hashref with the first
 452 element of the arrayref as the value for 'to' and the remaining pairs copied.
 453
 454 Given a hashref, the 'as' key is if presented expanded to wrap the 'to'.
 455
 456 If present the 'using' key is expanded as a list of idents.
 457
 458 Known keys are: 'from' (the left hand side), 'type' ('left', 'right', or
 459 nothing), 'to' (the right hand side), 'on' and 'using'.
 460
 461   # expr
 462   { -join => {
 463       from => 'lft',
 464       on => { 'lft.bloo' => { '>' => 'rgt.blee' } },
 465       to => 'rgt',
 466       type => 'left',
 467   } }
 468
 469   # aqt
 470   { -join => {
 471       from => { -ident => [ 'lft' ] },
 472       on => { -op => [
 473           '>', { -ident => [ 'lft', 'bloo' ] },
 474           { -ident => [ 'rgt', 'blee' ] },
 475       ] },
 476       to => { -ident => [ 'rgt' ] },
 477       type => 'left',
 478   } }
 479
 480   # query
 481   lft LEFT JOIN rgt ON lft.bloo > rgt.blee
 482   []
 483
 484 =head2 from_list
 485
 486 List of components of the FROM clause; -foo type elements indicate a pair
 487 with the next element; this is easiest if I show you:
 488
 489   # expr
 490   { -from_list => [
 491       't1', -as => 'table_one', -join =>
 492       [ 't2', 'on', { 'table_one.x' => 't2.x' } ],
 493   ] }
 494
 495   # aqt
 496   { -join => {
 497       from =>
 498         {
 499           -as => [ { -ident => [ 't1' ] }, { -ident => [ 'table_one' ] } ]
 500         },
 501       on => { -op => [
 502           '=', { -ident => [ 'table_one', 'x' ] },
 503           { -ident => [ 't2', 'x' ] },
 504       ] },
 505       to => { -ident => [ 't2' ] },
 506       type => undef,
 507   } }
 508
 509   # query
 510   t1 AS table_one JOIN t2 ON table_one.x = t2.x
 511   []
 512
 513 Or with using:
 514
 515   # expr
 516   { -from_list =>
 517       [ 't1', -as => 'table_one', -join => [ 't2', 'using', [ 'x' ] ] ]
 518   }
 519
 520   # aqt
 521   { -join => {
 522       from =>
 523         {
 524           -as => [ { -ident => [ 't1' ] }, { -ident => [ 'table_one' ] } ]
 525         },
 526       to => { -ident => [ 't2' ] },
 527       type => undef,
 528       using =>
 529         { -op => [ 'or', { -op => [ 'or', { -ident => [ 'x' ] } ] } ] },
 530   } }
 531
 532   # query
 533   t1 AS table_one JOIN t2 USING ( x )
 534   []
 535
 536 With oddities:
 537
 538   # expr
 539   { -from_list => [
 540       'x', -join =>
 541       [ [ 'y', -join => [ 'z', 'type', 'left' ] ], 'type', 'left' ],
 542   ] }
 543
 544   # aqt
 545   { -join => {
 546       from => { -ident => [ 'x' ] },
 547       to => { -join => {
 548           from => { -ident => [ 'y' ] },
 549           to => { -ident => [ 'z' ] },
 550           type => 'left',
 551       } },
 552       type => 'left',
 553   } }
 554
 555   # query
 556   x LEFT JOIN ( y LEFT JOIN z )
 557   []
 558
 559 =head2 setops
 560
 561 Expanders are provided for union, union_all, intersect, intersect_all,
 562 except and except_all, and each takes an arrayref of queries:
 563
 564   # expr
 565   { -union => [
 566       { -select => { _ => { -value => 1 } } },
 567       { -select => { _ => { -value => 2 } } },
 568   ] }
 569
 570   # aqt
 571   { -union => { queries => [
 572         { -select =>
 573             { select => { -op => [ ',', { -bind => [ undef, 1 ] } ] } }
 574         },
 575         { -select =>
 576             { select => { -op => [ ',', { -bind => [ undef, 2 ] } ] } }
 577         },
 578   ] } }
 579
 580   # query
 581   (SELECT ?) UNION (SELECT ?)
 582   [ 1, 2 ]
 583
 584   # expr
 585   { -union_all => [
 586       { -select => { _ => { -value => 1 } } },
 587       { -select => { _ => { -value => 2 } } },
 588       { -select => { _ => { -value => 1 } } },
 589   ] }
 590
 591   # aqt
 592   { -union => {
 593       queries => [
 594         { -select =>
 595             { select => { -op => [ ',', { -bind => [ undef, 1 ] } ] } }
 596         },
 597         { -select =>
 598             { select => { -op => [ ',', { -bind => [ undef, 2 ] } ] } }
 599         },
 600         { -select =>
 601             { select => { -op => [ ',', { -bind => [ undef, 1 ] } ] } }
 602         },
 603       ],
 604       type => 'all',
 605   } }
 606
 607   # query
 608   (SELECT ?) UNION ALL (SELECT ?) UNION ALL (SELECT ?)
 609   [ 1, 2, 1 ]
 610
 611 =head1 STATEMENT EXTENSIONS
 612
 613 =head2 group by clause for select
 614
 615 Expanded as a list with an ident default:
 616
 617   # expr
 618   { -select => { group_by => [ 'foo', 'bar' ] } }
 619
 620   # aqt
 621   { -select => { group_by =>
 622         {
 623           -op => [ ',', { -ident => [ 'foo' ] }, { -ident => [ 'bar' ] } ]
 624         }
 625   } }
 626
 627   # query
 628   GROUP BY foo, bar
 629   []
 630
 631 =head2 having clause for select
 632
 633 Basic expr, just like where, given having is pretty much post-group-by
 634 where clause:
 635
 636   # expr
 637   { -select =>
 638       { having => { '>' => [ { -count => { -ident => 'foo' } }, 3 ] } }
 639   }
 640
 641   # aqt
 642   { -select => { having => { -op => [
 643           '>', { -func => [ 'count', { -ident => [ 'foo' ] } ] },
 644           { -bind => [ undef, 3 ] },
 645   ] } } }
 646
 647   # query
 648   HAVING COUNT(foo) > ?
 649   [ 3 ]
 650
 651 =head2 setop clauses
 652
 653 If a select query contains a clause matching any of the setop node types,
 654 clauses that appear before the setop would in the resulting query are
 655 gathered together and moved into an inner select node:
 656
 657   # expr
 658   { -select => {
 659       _ => '*',
 660       from => 'foo',
 661       order_by => 'baz',
 662       union =>
 663         {
 664           -select => { _ => '*', from => 'bar', where => { thing => 1 } }
 665         },
 666       where => { thing => 1 },
 667   } }
 668
 669   # aqt
 670   { -select => {
 671       order_by => { -op => [ ',', { -ident => [ 'baz' ] } ] },
 672       setop => { -union => { queries => [
 673             { -select => {
 674                 from => { -ident => [ 'foo' ] },
 675                 select => { -op => [ ',', { -ident => [ '*' ] } ] },
 676                 where => { -op => [
 677                     '=', { -ident => [ 'thing' ] },
 678                     { -bind => [ 'thing', 1 ] },
 679                 ] },
 680             } },     ] },
 681             { -select => {
 682                 from => { -ident => [ 'bar' ] },
 683                 select => { -op => [ ',', { -ident => [ '*' ] } ] },
 684                 where => { -op => [
 685                     '=', { -ident => [ 'thing' ] },
 686                     { -bind => [ 'thing', 1 ] },
 687             } },
 688       ] } },
 689   } }
 690
 691   # query
 692   (SELECT * FROM foo WHERE thing = ?) UNION (
 693     SELECT * FROM bar WHERE thing = ?
 694   )
 695   ORDER BY baz
 696   [ 1, 1 ]
 697
 698 =head2 update from clause
 699
 700 Some databases allow an additional FROM clause to reference other tables
 701 for the data to update; this clause is expanded as a normal from list, check
 702 your database for what is and isn't allowed in practice.
 703
 704   # expr
 705   { -update => {
 706       _ => 'employees',
 707       from => 'accounts',
 708       set => { sales_count => { sales_count => { '+' => \1 } } },
 709       where => {
 710         'accounts.name' => { '=' => \"'Acme Corporation'" },
 711         'employees.id' => { -ident => 'accounts.sales_person' },
 712       },
 713   } }
 714
 715   # aqt
 716   { -update => {
 717       from => { -ident => [ 'accounts' ] },
 718       set => { -op => [
 719           ',', { -op => [
 720               '=', { -ident => [ 'sales_count' ] }, { -op => [
 721                   '+', { -ident => [ 'sales_count' ] },
 722                   { -literal => [ 1 ] },
 723               ] },
 724           ] },
 725       ] },
 726       target => { -ident => [ 'employees' ] },
 727       where => { -op => [
 728           'and', { -op => [
 729               '=', { -ident => [ 'accounts', 'name' ] },
 730               { -literal => [ "'Acme Corporation'" ] },
 731           ] }, { -op => [
 732               '=', { -ident => [ 'employees', 'id' ] },
 733               { -ident => [ 'accounts', 'sales_person' ] },
 734           ] },
 735       ] },
 736   } }
 737
 738   # query
 739   UPDATE employees SET sales_count = sales_count + 1 FROM accounts
 740   WHERE (
 741     accounts.name = 'Acme Corporation'
 742     AND employees.id = accounts.sales_person
 743   )
 744   []
 745
 746 =head2 delete using clause
 747
 748 Some databases allow an additional USING clause to reference other tables
 749 for the data to update; this clause is expanded as a normal from list, check
 750 your database for what is and isn't allowed in practice.
 751
 752   # expr
 753   { -delete => {
 754       from => 'x',
 755       using => 'y',
 756       where => { 'x.id' => { -ident => 'y.x_id' } },
 757   } }
 758
 759   # aqt
 760   { -delete => {
 761       target => { -op => [ ',', { -ident => [ 'x' ] } ] },
 762       using => { -ident => [ 'y' ] },
 763       where => { -op => [
 764           '=', { -ident => [ 'x', 'id' ] },
 765           { -ident => [ 'y', 'x_id' ] },
 766       ] },
 767   } }
 768
 769   # query
 770   DELETE FROM x USING y WHERE x.id = y.x_id
 771   []
 772
 773 =head2 insert rowvalues and select clauses
 774
 775 rowvalues and select are shorthand for
 776
 777   { from => { -select ... } }
 778
 779 and
 780
 781   { from => { -values ... } }
 782
 783 respectively:
 784
 785   # expr
 786   { -insert =>
 787       { into => 'numbers', rowvalues => [ [ 1, 2 ], [ 3, 4 ], [ 5, 6 ] ] }
 788   }
 789
 790   # aqt
 791   { -insert => {
 792       from => { -values => [
 793           { -row =>
 794               [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ]
 795           },
 796           { -row =>
 797               [ { -bind => [ undef, 3 ] }, { -bind => [ undef, 4 ] } ]
 798           },
 799           { -row =>
 800               [ { -bind => [ undef, 5 ] }, { -bind => [ undef, 6 ] } ]
 801           },
 802       ] },
 803       target => { -ident => [ 'numbers' ] },
 804   } }
 805
 806   # query
 807   INSERT INTO numbers VALUES (?, ?), (?, ?), (?, ?)
 808   [ 1, 2, 3, 4, 5, 6 ]
 809
 810   # expr
 811   { -insert =>
 812       { into => 'numbers', select => { _ => '*', from => 'old_numbers' } }
 813   }
 814
 815   # aqt
 816   { -insert => {
 817       from => { -select => {
 818           from => { -ident => [ 'old_numbers' ] },
 819           select => { -op => [ ',', { -ident => [ '*' ] } ] },
 820       } },
 821       target => { -ident => [ 'numbers' ] },
 822   } }
 823
 824   # query
 825   INSERT INTO numbers SELECT * FROM old_numbers
 826   []
 827
 828 =head2 with and with_recursive clauses
 829
 830 These clauses are available on select/insert/update/delete queries; check
 831 your database for applicability (e.g. mysql supports all four but mariadb
 832 only select).
 833
 834 The value should be an arrayref of name/query pairs:
 835
 836   # expr
 837   { -select => {
 838       from => 'foo',
 839       select => '*',
 840       with => [ 'foo', { -select => { select => \1 } } ],
 841   } }
 842
 843   # aqt
 844   { -select => {
 845       from => { -ident => [ 'foo' ] },
 846       select => { -op => [ ',', { -ident => [ '*' ] } ] },
 847       with => { queries => [ [
 848             { -ident => [ 'foo' ] }, { -select =>
 849                 { select => { -op => [ ',', { -literal => [ 1 ] } ] } }
 850             },
 851       ] ] },
 852   } }
 853
 854   # query
 855   WITH foo AS (SELECT 1) SELECT * FROM foo
 856   []
 857
 858 A more complete example (designed for mariadb, (ab)using the fact that
 859 mysqloids materialise subselects in FROM into an unindexed temp table to
 860 circumvent the restriction that you can't select from the table you're
 861 currently updating:
 862
 863   # expr
 864   { -update => {
 865       _ => [
 866         'tree_table', -join => {
 867           as => 'tree',
 868           on => { 'tree.id' => 'tree_with_path.id' },
 869           to => { -select => {
 870               from => 'tree_with_path',
 871               select => '*',
 872               with_recursive => [
 873                 [ 'tree_with_path', 'id', 'parent_id', 'path' ],
 874                 { -select => {
 875                     _ => [
 876                       'id', 'parent_id', { -as => [
 877                           { -cast => { -as => [ 'id', 'char', 255 ] } },
 878                           'path',
 879                       ] } ],
 880                     from => 'tree_table',
 881                     union_all => { -select => {
 882                         _ => [
 883                           't.id', 't.parent_id', { -as => [
 884                               { -concat => [ 'r.path', \"'/'", 't.id' ] },
 885                               'path',
 886                           ] },
 887                         ],
 888                         from => [
 889                           'tree_table', -as => 't', -join => {
 890                             as => 'r',
 891                             on => { 't.parent_id' => 'r.id' },
 892                             to => 'tree_with_path',
 893                           },
 894                         ],
 895                     } },
 896                     where => { parent_id => undef },
 897                 } },
 898               ],
 899           } },
 900         },
 901       ],
 902       set => { path => { -ident => [ 'tree', 'path' ] } },
 903   } }
 904
 905   # query
 906   UPDATE
 907     tree_table JOIN
 908     (
 909       WITH RECURSIVE
 910         tree_with_path(id, parent_id, path) AS (
 911           (
 912             SELECT id, parent_id, CAST(id AS char(255)) AS path
 913             FROM tree_table WHERE parent_id IS NULL
 914           ) UNION ALL (
 915             SELECT t.id, t.parent_id, CONCAT(r.path, '/', t.id) AS path
 916             FROM
 917               tree_table AS t JOIN tree_with_path AS r ON
 918               t.parent_id = r.id
 919           )
 920         )
 921       SELECT * FROM tree_with_path
 922     ) AS tree
 923     ON tree.id = tree_with_path.id
 924   SET path = tree.path
 925   []
 926
 927 =cut