X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=scpubgit%2FQ-Branch.git;a=blobdiff_plain;f=lib%2FSQL%2FAbstract%2FExtraClauses.pm;h=e1029bcd247bad056d5a0c4e1b88dc434f808b43;hp=9c2784539387247910d19bae5ebb0f6646d86758;hb=e376aa8053a377ca85e9a16205132b30436b2ce5;hpb=4979c509c66eb5c81887d3afe37de54f0988ad94 diff --git a/lib/SQL/Abstract/ExtraClauses.pm b/lib/SQL/Abstract/ExtraClauses.pm index 9c27845..e1029bc 100644 --- a/lib/SQL/Abstract/ExtraClauses.pm +++ b/lib/SQL/Abstract/ExtraClauses.pm @@ -5,7 +5,7 @@ use Moo; has sqla => ( is => 'ro', init_arg => undef, handles => [ qw( - expand_expr expand_maybe_list_expr render_aqt join_query_parts + expand_expr render_aqt join_query_parts ) ], ); @@ -79,7 +79,7 @@ sub register_extensions { clause_expanders => [ "select.from", '_expand_from_list', 'select.group_by' - => sub { $_[0]->expand_maybe_list_expr($_[2], -ident) }, + => sub { $_[0]->expand_expr({ -list => $_[2] }, -ident) }, 'select.having' => sub { $_[0]->expand_expr($_[2]) }, 'update.from' => '_expand_from_list', @@ -104,7 +104,8 @@ sub register_extensions { clause_renderer => [ 'select.setop' => sub { $_[0]->render_aqt($_[2]) } ], - expander => [ map +($_ => '_expand_setop'), qw(union intersect except) ], + expander => [ + map +($_ => '_expand_setop', "${_}_all" => '_expand_setop'), qw(union intersect except) ], renderer => [ map +($_ => '_render_setop'), qw(union intersect except) ], ); @@ -339,9 +340,16 @@ sub _render_with { sub _expand_setop { my ($self, $setop, $args) = @_; + my $is_all = $setop =~ s/_all$//; +{ "-${setop}" => { - %$args, - queries => [ map $self->expand_expr($_), @{$args->{queries}} ], + ($is_all ? (type => 'all') : ()), + (ref($args) eq 'ARRAY' + ? (queries => [ map $self->expand_expr($_), @$args ]) + : ( + %$args, + queries => [ map $self->expand_expr($_), @{$args->{queries}} ] + ) + ), } }; } @@ -377,6 +385,11 @@ SQL::Abstract::ExtraClauses - new/experimental additions to L my $sqla = SQL::Abstract->new; SQL::Abstract::ExtraClauses->apply_to($sqla); +=head1 WARNING + +This module is basically a nursery for things that seem like a good idea +to live in until we figure out if we were right about that. + =head1 METHODS =head2 apply_to @@ -575,4 +588,372 @@ With oddities: x LEFT JOIN ( y LEFT JOIN z ) [] +=head2 setops + +Expanders are provided for union, union_all, intersect, intersect_all, +except and except_all, and each takes an arrayref of queries: + + # expr + { -union => [ + { -select => { _ => { -value => 1 } } }, + { -select => { _ => { -value => 2 } } }, + ] } + + # aqt + { -union => { queries => [ + { -select => + { select => { -op => [ ',', { -bind => [ undef, 1 ] } ] } } + }, + { -select => + { select => { -op => [ ',', { -bind => [ undef, 2 ] } ] } } + }, + ] } } + + # query + (SELECT ?) UNION (SELECT ?) + [ 1, 2 ] + + # expr + { -union_all => [ + { -select => { _ => { -value => 1 } } }, + { -select => { _ => { -value => 2 } } }, + { -select => { _ => { -value => 1 } } }, + ] } + + # aqt + { -union => { + queries => [ + { -select => + { select => { -op => [ ',', { -bind => [ undef, 1 ] } ] } } + }, + { -select => + { select => { -op => [ ',', { -bind => [ undef, 2 ] } ] } } + }, + { -select => + { select => { -op => [ ',', { -bind => [ undef, 1 ] } ] } } + }, + ], + type => 'all', + } } + + # query + (SELECT ?) UNION ALL (SELECT ?) UNION ALL (SELECT ?) + [ 1, 2, 1 ] + +=head1 STATEMENT EXTENSIONS + +=head2 group by clause for select + +Expanded as a list with an ident default: + + # expr + { -select => { group_by => [ 'foo', 'bar' ] } } + + # aqt + { -select => { group_by => + { + -op => [ ',', { -ident => [ 'foo' ] }, { -ident => [ 'bar' ] } ] + } + } } + + # query + GROUP BY foo, bar + [] + +=head2 having clause for select + +Basic expr, just like where, given having is pretty much post-group-by +where clause: + + # expr + { -select => + { having => { '>' => [ { -count => { -ident => 'foo' } }, 3 ] } } + } + + # aqt + { -select => { having => { -op => [ + '>', { -func => [ 'count', { -ident => [ 'foo' ] } ] }, + { -bind => [ undef, 3 ] }, + ] } } } + + # query + HAVING COUNT(foo) > ? + [ 3 ] + +=head2 setop clauses + +If a select query contains a clause matching any of the setop node types, +clauses that appear before the setop would in the resulting query are +gathered together and moved into an inner select node: + + # expr + { -select => { + _ => '*', + from => 'foo', + order_by => 'baz', + union => + { + -select => { _ => '*', from => 'bar', where => { thing => 1 } } + }, + where => { thing => 1 }, + } } + + # aqt + { -select => { + order_by => { -op => [ ',', { -ident => [ 'baz' ] } ] }, + setop => { -union => { queries => [ + { -select => { + from => { -ident => [ 'foo' ] }, + select => { -op => [ ',', { -ident => [ '*' ] } ] }, + where => { -op => [ + '=', { -ident => [ 'thing' ] }, + { -bind => [ 'thing', 1 ] }, + ] }, + } }, ] }, + { -select => { + from => { -ident => [ 'bar' ] }, + select => { -op => [ ',', { -ident => [ '*' ] } ] }, + where => { -op => [ + '=', { -ident => [ 'thing' ] }, + { -bind => [ 'thing', 1 ] }, + } }, + ] } }, + } } + + # query + (SELECT * FROM foo WHERE thing = ?) UNION ( + SELECT * FROM bar WHERE thing = ? + ) + ORDER BY baz + [ 1, 1 ] + +=head2 update from clause + +Some databases allow an additional FROM clause to reference other tables +for the data to update; this clause is expanded as a normal from list, check +your database for what is and isn't allowed in practice. + + # expr + { -update => { + _ => 'employees', + from => 'accounts', + set => { sales_count => { sales_count => { '+' => \1 } } }, + where => { + 'accounts.name' => { '=' => \"'Acme Corporation'" }, + 'employees.id' => { -ident => 'accounts.sales_person' }, + }, + } } + + # aqt + { -update => { + from => { -ident => [ 'accounts' ] }, + set => { -op => [ + ',', { -op => [ + '=', { -ident => [ 'sales_count' ] }, { -op => [ + '+', { -ident => [ 'sales_count' ] }, + { -literal => [ 1 ] }, + ] }, + ] }, + ] }, + target => { -ident => [ 'employees' ] }, + where => { -op => [ + 'and', { -op => [ + '=', { -ident => [ 'accounts', 'name' ] }, + { -literal => [ "'Acme Corporation'" ] }, + ] }, { -op => [ + '=', { -ident => [ 'employees', 'id' ] }, + { -ident => [ 'accounts', 'sales_person' ] }, + ] }, + ] }, + } } + + # query + UPDATE employees SET sales_count = sales_count + 1 FROM accounts + WHERE ( + accounts.name = 'Acme Corporation' + AND employees.id = accounts.sales_person + ) + [] + +=head2 delete using clause + +Some databases allow an additional USING clause to reference other tables +for the data to update; this clause is expanded as a normal from list, check +your database for what is and isn't allowed in practice. + + # expr + { -delete => { + from => 'x', + using => 'y', + where => { 'x.id' => { -ident => 'y.x_id' } }, + } } + + # aqt + { -delete => { + target => { -op => [ ',', { -ident => [ 'x' ] } ] }, + using => { -ident => [ 'y' ] }, + where => { -op => [ + '=', { -ident => [ 'x', 'id' ] }, + { -ident => [ 'y', 'x_id' ] }, + ] }, + } } + + # query + DELETE FROM x USING y WHERE x.id = y.x_id + [] + +=head2 insert rowvalues and select clauses + +rowvalues and select are shorthand for + + { from => { -select ... } } + +and + + { from => { -values ... } } + +respectively: + + # expr + { -insert => + { into => 'numbers', rowvalues => [ [ 1, 2 ], [ 3, 4 ], [ 5, 6 ] ] } + } + + # aqt + { -insert => { + from => { -values => [ + { -row => + [ { -bind => [ undef, 1 ] }, { -bind => [ undef, 2 ] } ] + }, + { -row => + [ { -bind => [ undef, 3 ] }, { -bind => [ undef, 4 ] } ] + }, + { -row => + [ { -bind => [ undef, 5 ] }, { -bind => [ undef, 6 ] } ] + }, + ] }, + target => { -ident => [ 'numbers' ] }, + } } + + # query + INSERT INTO numbers VALUES (?, ?), (?, ?), (?, ?) + [ 1, 2, 3, 4, 5, 6 ] + + # expr + { -insert => + { into => 'numbers', select => { _ => '*', from => 'old_numbers' } } + } + + # aqt + { -insert => { + from => { -select => { + from => { -ident => [ 'old_numbers' ] }, + select => { -op => [ ',', { -ident => [ '*' ] } ] }, + } }, + target => { -ident => [ 'numbers' ] }, + } } + + # query + INSERT INTO numbers SELECT * FROM old_numbers + [] + +=head2 with and with_recursive clauses + +These clauses are available on select/insert/update/delete queries; check +your database for applicability (e.g. mysql supports all four but mariadb +only select). + +The value should be an arrayref of name/query pairs: + + # expr + { -select => { + from => 'foo', + select => '*', + with => [ 'foo', { -select => { select => \1 } } ], + } } + + # aqt + { -select => { + from => { -ident => [ 'foo' ] }, + select => { -op => [ ',', { -ident => [ '*' ] } ] }, + with => { queries => [ [ + { -ident => [ 'foo' ] }, { -select => + { select => { -op => [ ',', { -literal => [ 1 ] } ] } } + }, + ] ] }, + } } + + # query + WITH foo AS (SELECT 1) SELECT * FROM foo + [] + +A more complete example (designed for mariadb, (ab)using the fact that +mysqloids materialise subselects in FROM into an unindexed temp table to +circumvent the restriction that you can't select from the table you're +currently updating: + + # expr + { -update => { + _ => [ + 'tree_table', -join => { + as => 'tree', + on => { 'tree.id' => 'tree_with_path.id' }, + to => { -select => { + from => 'tree_with_path', + select => '*', + with_recursive => [ + [ 'tree_with_path', 'id', 'parent_id', 'path' ], + { -select => { + _ => [ + 'id', 'parent_id', { -as => [ + { -cast => { -as => [ 'id', 'char', 255 ] } }, + 'path', + ] } ], + from => 'tree_table', + union_all => { -select => { + _ => [ + 't.id', 't.parent_id', { -as => [ + { -concat => [ 'r.path', \"'/'", 't.id' ] }, + 'path', + ] }, + ], + from => [ + 'tree_table', -as => 't', -join => { + as => 'r', + on => { 't.parent_id' => 'r.id' }, + to => 'tree_with_path', + }, + ], + } }, + where => { parent_id => undef }, + } }, + ], + } }, + }, + ], + set => { path => { -ident => [ 'tree', 'path' ] } }, + } } + + # query + UPDATE + tree_table JOIN + ( + WITH RECURSIVE + tree_with_path(id, parent_id, path) AS ( + ( + SELECT id, parent_id, CAST(id AS char(255)) AS path + FROM tree_table WHERE parent_id IS NULL + ) UNION ALL ( + SELECT t.id, t.parent_id, CONCAT(r.path, '/', t.id) AS path + FROM + tree_table AS t JOIN tree_with_path AS r ON + t.parent_id = r.id + ) + ) + SELECT * FROM tree_with_path + ) AS tree + ON tree.id = tree_with_path.id + SET path = tree.path + [] + =cut