X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FSQL%2FAbstract.pm;h=a07d89c31f086a25f393fc7c8308320bb943d060;hb=0a7639af83c66757087775fef8a0b557915c30d7;hp=a479468fc0b878a10dc5c4d74cb5f7c90ca98a51;hpb=ce3272fe22bf1c076b67f6e9c1c810b7802fff26;p=dbsrgits%2FSQL-Abstract.git diff --git a/lib/SQL/Abstract.pm b/lib/SQL/Abstract.pm index a479468..a07d89c 100644 --- a/lib/SQL/Abstract.pm +++ b/lib/SQL/Abstract.pm @@ -37,11 +37,11 @@ our $AUTOLOAD; # special operators (-in, -between). May be extended/overridden by user. # See section WHERE: BUILTIN SPECIAL OPERATORS below for implementation my @BUILTIN_SPECIAL_OPS = ( - {regex => qr/^ (?: not \s )? between $/ix, handler => '_where_field_BETWEEN'}, - {regex => qr/^ (?: not \s )? in $/ix, handler => '_where_field_IN'}, - {regex => qr/^ ident $/ix, handler => '_where_op_IDENT'}, - {regex => qr/^ value $/ix, handler => '_where_op_VALUE'}, - {regex => qr/^ is (?: \s+ not )? $/ix, handler => '_where_field_IS'}, + {regex => qr/^ (?: not \s )? between $/ix, handler => sub { die "NOPE" }}, + {regex => qr/^ (?: not \s )? in $/ix, handler => sub { die "NOPE" }}, + {regex => qr/^ ident $/ix, handler => sub { die "NOPE" }}, + {regex => qr/^ value $/ix, handler => sub { die "NOPE" }}, + {regex => qr/^ is (?: \s+ not )? $/ix, handler => sub { die "NOPE" }}, ); # unaryish operators - key maps to handler @@ -53,6 +53,10 @@ my @BUILTIN_UNARY_OPS = ( { regex => qr/^ (?: not \s )? bool $/xi, handler => '_where_op_BOOL' }, { regex => qr/^ ident $/xi, handler => '_where_op_IDENT' }, { regex => qr/^ value $/xi, handler => '_where_op_VALUE' }, + { regex => qr/^ op $/xi, handler => '_where_op_OP' }, + { regex => qr/^ bind $/xi, handler => '_where_op_BIND' }, + { regex => qr/^ literal $/xi, handler => '_where_op_LITERAL' }, + { regex => qr/^ func $/xi, handler => '_where_op_FUNC' }, ); #====================================================================== @@ -168,7 +172,7 @@ sub new { $opt{sqlfalse} ||= '0=1'; # special operators - $opt{special_ops} ||= []; + $opt{user_special_ops} = [ @{$opt{special_ops} ||= []} ]; # regexes are applied in order, thus push after user-defines push @{$opt{special_ops}}, @BUILTIN_SPECIAL_OPS; @@ -193,6 +197,8 @@ sub new { return bless \%opt, $class; } +sub sqltrue { +{ -literal => [ $_[0]->{sqltrue} ] } } +sub sqlfalse { +{ -literal => [ $_[0]->{sqlfalse} ] } } sub _assert_pass_injection_guard { if ($_[1] =~ $_[0]->{injection_guard}) { @@ -522,7 +528,9 @@ sub where { my ($self, $where, $order) = @_; # where ? - my ($sql, @bind) = $self->_recurse_where($where); + my ($sql, @bind) = defined($where) + ? $self->_recurse_where($where) + : (undef); $sql = (defined $sql and length $sql) ? $self->_sqlcase(' where ') . "( $sql )" : ''; # order by? @@ -537,49 +545,366 @@ sub where { sub _expand_expr { my ($self, $expr, $logic) = @_; + return undef unless defined($expr); if (ref($expr) eq 'HASH') { if (keys %$expr > 1) { $logic ||= 'and'; - return +{ "-${logic}" => [ + return +{ -op => [ + $logic, map $self->_expand_expr_hashpair($_ => $expr->{$_}, $logic), sort keys %$expr ] }; } + return unless %$expr; return $self->_expand_expr_hashpair(%$expr, $logic); } - return $expr; + if (ref($expr) eq 'ARRAY') { + my $logic = lc($logic || $self->{logic}); + $logic eq 'and' or $logic eq 'or' or puke "unknown logic: $logic"; + + my @expr = @$expr; + + my @res; + + while (my ($el) = splice @expr, 0, 1) { + puke "Supplying an empty left hand side argument is not supported in array-pairs" + unless defined($el) and length($el); + my $elref = ref($el); + if (!$elref) { + push(@res, $self->_expand_expr({ $el, shift(@expr) })); + } elsif ($elref eq 'ARRAY') { + push(@res, $self->_expand_expr($el)) if @$el; + } elsif (my $l = is_literal_value($el)) { + push @res, { -literal => $l }; + } elsif ($elref eq 'HASH') { + push @res, $self->_expand_expr($el); + } else { + die "notreached"; + } + } + return { -op => [ $logic, @res ] }; + } + if (my $literal = is_literal_value($expr)) { + return +{ -literal => $literal }; + } + if (!ref($expr) or Scalar::Util::blessed($expr)) { + if (my $m = our $Cur_Col_Meta) { + return +{ -bind => [ $m, $expr ] }; + } + return +{ -value => $expr }; + } + die "notreached"; } sub _expand_expr_hashpair { my ($self, $k, $v, $logic) = @_; - if (!ref($v)) { - if ($k !~ /^-/) { - return +{ $k => { $self->{cmp} => $v } }; + unless (defined($k) and length($k)) { + if (defined($k) and my $literal = is_literal_value($v)) { + belch 'Hash-pairs consisting of an empty string with a literal are deprecated, and will be removed in 2.0: use -and => [ $literal ] instead'; + return { -literal => $literal }; } + puke "Supplying an empty left hand side argument is not supported"; } - if ($k eq '-nest') { - return $self->_expand_expr($v); + if ($k =~ /^-/) { + $self->_assert_pass_injection_guard($k =~ /^-(.*)$/s); + if ($k =~ s/ [_\s]? \d+ $//x ) { + belch 'Use of [and|or|nest]_N modifiers is deprecated and will be removed in SQLA v2.0. ' + . "You probably wanted ...-and => [ $k => COND1, $k => COND2 ... ]"; + } + if ($k eq '-nest') { + return $self->_expand_expr($v); + } + if ($k eq '-bool') { + if (ref($v)) { + return $self->_expand_expr($v); + } + puke "-bool => undef not supported" unless defined($v); + return { -ident => $v }; + } + if ($k eq '-not') { + return { -op => [ 'not', $self->_expand_expr($v) ] }; + } + if (my ($rest) = $k =~/^-not[_ ](.*)$/) { + return +{ -op => [ + 'not', + $self->_expand_expr_hashpair("-${rest}", $v, $logic) + ] }; + } + if (my ($logic) = $k =~ /^-(and|or)$/i) { + if (ref($v) eq 'HASH') { + return $self->_expand_expr($v, $logic); + } + if (ref($v) eq 'ARRAY') { + return $self->_expand_expr($v, $logic); + } + } + { + my $op = $k; + $op =~ s/^-// if length($op) > 1; + + # top level special ops are illegal in general + puke "Illegal use of top-level '-$op'" + if !(defined $self->{_nested_func_lhs}) + and List::Util::first { $op =~ $_->{regex} } @{$self->{special_ops}} + and not List::Util::first { $op =~ $_->{regex} } @{$self->{unary_ops}}; + } + if ($k eq '-value' and my $m = our $Cur_Col_Meta) { + return +{ -bind => [ $m, $v ] }; + } + if ($k eq '-op' or $k eq '-ident' or $k eq '-value' or $k eq '-bind' or $k eq '-literal' or $k eq '-func') { + return { $k => $v }; + } + if ( + ref($v) eq 'HASH' + and keys %$v == 1 + and (keys %$v)[0] =~ /^-/ + ) { + my ($func) = $k =~ /^-(.*)$/; + return +{ -func => [ $func, $self->_expand_expr($v) ] }; + } + if (!ref($v) or is_literal_value($v)) { + return +{ -op => [ $k =~ /^-(.*)$/, $self->_expand_expr($v) ] }; + } + } + if ( + !defined($v) + or ( + ref($v) eq 'HASH' + and exists $v->{-value} + and not defined $v->{-value} + ) + ) { + return $self->_expand_expr_hashpair($k => { $self->{cmp} => undef }); + } + if (!ref($v) or Scalar::Util::blessed($v)) { + return +{ + -op => [ + $self->{cmp}, + { -ident => $k }, + { -bind => [ $k, $v ] } + ] + }; + } + if (ref($v) eq 'HASH') { + if (keys %$v > 1) { + return { -op => [ + 'and', + map $self->_expand_expr_hashpair($k => { $_ => $v->{$_} }), + sort keys %$v + ] }; + } + my ($vk, $vv) = %$v; + $vk =~ s/^-//; + $vk = lc($vk); + $self->_assert_pass_injection_guard($vk); + if ($vk =~ s/ [_\s]? \d+ $//x ) { + belch 'Use of [and|or|nest]_N modifiers is deprecated and will be removed in SQLA v2.0. ' + . "You probably wanted ...-and => [ -$vk => COND1, -$vk => COND2 ... ]"; + } + if ($vk =~ /^(?:not[ _])?between$/) { + local our $Cur_Col_Meta = $k; + my @rhs = map $self->_expand_expr($_), + ref($vv) eq 'ARRAY' ? @$vv : $vv; + unless ( + (@rhs == 1 and ref($rhs[0]) eq 'HASH' and $rhs[0]->{-literal}) + or + (@rhs == 2 and defined($rhs[0]) and defined($rhs[1])) + ) { + puke "Operator '${\uc($vk)}' requires either an arrayref with two defined values or expressions, or a single literal scalarref/arrayref-ref"; + } + return +{ -op => [ + join(' ', split '_', $vk), + { -ident => $k }, + @rhs + ] } + } + if ($vk =~ /^(?:not[ _])?in$/) { + if (my $literal = is_literal_value($vv)) { + my ($sql, @bind) = @$literal; + my $opened_sql = $self->_open_outer_paren($sql); + return +{ -op => [ + $vk, { -ident => $k }, + [ { -literal => [ $opened_sql, @bind ] } ] + ] }; + } + my $undef_err = + 'SQL::Abstract before v1.75 used to generate incorrect SQL when the ' + . "-${\uc($vk)} operator was given an undef-containing list: !!!AUDIT YOUR CODE " + . 'AND DATA!!! (the upcoming Data::Query-based version of SQL::Abstract ' + . 'will emit the logically correct SQL instead of raising this exception)' + ; + puke("Argument passed to the '${\uc($vk)}' operator can not be undefined") + if !defined($vv); + my @rhs = map $self->_expand_expr($_), + map { ref($_) ? $_ : { -bind => [ $k, $_ ] } } + map { defined($_) ? $_: puke($undef_err) } + (ref($vv) eq 'ARRAY' ? @$vv : $vv); + return $self->${\($vk =~ /^not/ ? 'sqltrue' : 'sqlfalse')} unless @rhs; + + return +{ -op => [ + join(' ', split '_', $vk), + { -ident => $k }, + \@rhs + ] }; + } + if ($vk eq 'ident') { + if (! defined $vv or ref $vv) { + puke "-$vk requires a single plain scalar argument (a quotable identifier)"; + } + return +{ -op => [ + $self->{cmp}, + { -ident => $k }, + { -ident => $vv } + ] }; + } + if ($vk eq 'value') { + return $self->_expand_expr_hashpair($k, undef) unless defined($vv); + return +{ -op => [ + $self->{cmp}, + { -ident => $k }, + { -bind => [ $k, $vv ] } + ] }; + } + if ($vk =~ /^is(?:[ _]not)?$/) { + puke "$vk can only take undef as argument" + if defined($vv) + and not ( + ref($vv) eq 'HASH' + and exists($vv->{-value}) + and !defined($vv->{-value}) + ); + $vk =~ s/_/ /g; + return +{ -op => [ $vk.' null', { -ident => $k } ] }; + } + if ($vk =~ /^(and|or)$/) { + if (ref($vv) eq 'HASH') { + return +{ -op => [ + $vk, + map $self->_expand_expr_hashpair($k, { $_ => $vv->{$_} }), + sort keys %$vv + ] }; + } + } + if (my $us = List::Util::first { $vk =~ $_->{regex} } @{$self->{user_special_ops}}) { + return { -op => [ $vk, { -ident => $k }, $vv ] }; + } + if (ref($vv) eq 'ARRAY') { + my ($logic, @values) = ( + (defined($vv->[0]) and $vv->[0] =~ /^-(and|or)$/i) + ? @$vv + : (-or => @$vv) + ); + if ( + $vk =~ $self->{inequality_op} + or join(' ', split '_', $vk) =~ $self->{not_like_op} + ) { + if (lc($logic) eq '-or' and @values > 1) { + my $op = uc join ' ', split '_', $vk; + belch "A multi-element arrayref as an argument to the inequality op '$op' " + . 'is technically equivalent to an always-true 1=1 (you probably wanted ' + . "to say ...{ \$inequality_op => [ -and => \@values ] }... instead)" + ; + } + } + unless (@values) { + # try to DWIM on equality operators + my $op = join ' ', split '_', $vk; + return + $op =~ $self->{equality_op} ? $self->sqlfalse + : $op =~ $self->{like_op} ? belch("Supplying an empty arrayref to '@{[ uc $op]}' is deprecated") && $self->sqlfalse + : $op =~ $self->{inequality_op} ? $self->sqltrue + : $op =~ $self->{not_like_op} ? belch("Supplying an empty arrayref to '@{[ uc $op]}' is deprecated") && $self->sqltrue + : puke "operator '$op' applied on an empty array (field '$k')"; + } + return +{ -op => [ + $logic =~ /^-(.*)$/, + map $self->_expand_expr_hashpair($k => { $vk => $_ }), + @values + ] }; + } + if ( + !defined($vv) + or ( + ref($vv) eq 'HASH' + and exists $vv->{-value} + and not defined $vv->{-value} + ) + ) { + my $op = join ' ', split '_', $vk; + my $is = + $op =~ /^not$/i ? 'is not' # legacy + : $op =~ $self->{equality_op} ? 'is' + : $op =~ $self->{like_op} ? belch("Supplying an undefined argument to '@{[ uc $op]}' is deprecated") && 'is' + : $op =~ $self->{inequality_op} ? 'is not' + : $op =~ $self->{not_like_op} ? belch("Supplying an undefined argument to '@{[ uc $op]}' is deprecated") && 'is not' + : puke "unexpected operator '$op' with undef operand"; + return +{ -op => [ $is.' null', { -ident => $k } ] }; + } + local our $Cur_Col_Meta = $k; + return +{ -op => [ + $vk, + { -ident => $k }, + $self->_expand_expr($vv) + ] }; + } + if (ref($v) eq 'ARRAY') { + return $self->sqlfalse unless @$v; + $self->_debug("ARRAY($k) means distribute over elements"); + my $this_logic = ( + $v->[0] =~ /^-((?:and|or))$/i + ? ($v = [ @{$v}[1..$#$v] ], $1) + : ($self->{logic} || 'or') + ); + return +{ -op => [ + $this_logic, + map $self->_expand_expr({ $k => $_ }, $this_logic), @$v + ] }; } - if ($k !~ /^-/ and my $literal = is_literal_value($v)) { + if (my $literal = is_literal_value($v)) { unless (length $k) { belch 'Hash-pairs consisting of an empty string with a literal are deprecated, and will be removed in 2.0: use -and => [ $literal ] instead'; return \$literal; } my ($sql, @bind) = @$literal; - return \[ $self->_quote($k).' '.$sql, @bind ]; + if ($self->{bindtype} eq 'columns') { + for (@bind) { + if (!defined $_ || ref($_) ne 'ARRAY' || @$_ != 2) { + puke "bindtype 'columns' selected, you need to pass: [column_name => bind_value]" + } + } + } + return +{ -literal => [ $self->_quote($k).' '.$sql, @bind ] }; + } + die "notreached"; +} + +sub _render_expr { + my ($self, $expr) = @_; + my ($k, $v, @rest) = %$expr; + die "No" if @rest; + my %op = map +("-$_" => '_where_op_'.uc($_)), + qw(op func value bind ident literal); + if (my $meth = $op{$k}) { + return $self->$meth(undef, $v); } - return { $k => $v }; + die "notreached: $k"; } sub _recurse_where { my ($self, $where, $logic) = @_; +#print STDERR Data::Dumper::Concise::Dumper([ $where, $logic ]); + my $where_exp = $self->_expand_expr($where, $logic); +#print STDERR Data::Dumper::Concise::Dumper([ EXP => $where_exp ]); + # dispatch on appropriate method according to refkind of $where - my $method = $self->_METHOD_FOR_refkind("_where", $where_exp); +# my $method = $self->_METHOD_FOR_refkind("_where", $where_exp); - my ($sql, @bind) = $self->$method($where_exp, $logic); +# my ($sql, @bind) = $self->$method($where_exp, $logic); + + my ($sql, @bind) = defined($where_exp) ? $self->_render_expr($where_exp) : (undef); # DBIx::Class used to call _recurse_where in scalar context # something else might too... @@ -718,10 +1043,13 @@ sub _where_HASHREF { sub _where_unary_op { my ($self, $op, $rhs) = @_; + $op =~ s/^-// if length($op) > 1; + # top level special ops are illegal in general - # this includes the -ident/-value ops (dual purpose unary and special) puke "Illegal use of top-level '-$op'" - if ! defined $self->{_nested_func_lhs} and List::Util::first { $op =~ $_->{regex} } @{$self->{special_ops}}; + if !(defined $self->{_nested_func_lhs}) + and List::Util::first { $op =~ $_->{regex} } @{$self->{special_ops}} + and not List::Util::first { $op =~ $_->{regex} } @{$self->{unary_ops}}; if (my $op_entry = List::Util::first { $op =~ $_->{regex} } @{$self->{unary_ops}}) { my $handler = $op_entry->{handler}; @@ -768,46 +1096,6 @@ sub _where_unary_op { return ($sql, @bind); } -sub _where_op_ANDOR { - my ($self, $op, $v) = @_; - - $self->_SWITCH_refkind($v, { - ARRAYREF => sub { - return $self->_where_ARRAYREF($v, $op); - }, - - HASHREF => sub { - return ($op =~ /^or/i) - ? $self->_where_ARRAYREF([ map { $_ => $v->{$_} } (sort keys %$v) ], $op) - : $self->_where_HASHREF($v); - }, - - SCALARREF => sub { - puke "-$op => \\\$scalar makes little sense, use " . - ($op =~ /^or/i - ? '[ \$scalar, \%rest_of_conditions ] instead' - : '-and => [ \$scalar, \%rest_of_conditions ] instead' - ); - }, - - ARRAYREFREF => sub { - puke "-$op => \\[...] makes little sense, use " . - ($op =~ /^or/i - ? '[ \[...], \%rest_of_conditions ] instead' - : '-and => [ \[...], \%rest_of_conditions ] instead' - ); - }, - - SCALAR => sub { # permissively interpreted as SQL - puke "-$op => \$value makes little sense, use -bool => \$value instead"; - }, - - UNDEF => sub { - puke "-$op => undef not supported"; - }, - }); -} - sub _where_op_NEST { my ($self, $op, $v) = @_; @@ -861,11 +1149,11 @@ sub _where_op_IDENT { } # in case we are called as a top level special op (no '=') - my $lhs = shift; + my $has_lhs = my $lhs = shift; $_ = $self->_convert($self->_quote($_)) for ($lhs, $rhs); - return $lhs + return $has_lhs ? "$lhs = $rhs" : $rhs ; @@ -905,6 +1193,118 @@ sub _where_op_VALUE { ; } + +my %unop_postfix = map +($_ => 1), 'is null', 'is not null'; + +my %special = ( + (map +($_ => do { + my $op = $_; + sub { + my ($self, $args) = @_; + my ($left, $low, $high) = @$args; + my ($rhsql, @rhbind) = do { + if (@$args == 2) { + puke "Single arg to between must be a literal" + unless $low->{-literal}; + @{$low->{-literal}} + } else { + local $self->{_nested_func_lhs} = $left->{-ident} + if ref($left) eq 'HASH' and $left->{-ident}; + my ($l, $h) = map [ $self->_where_unary_op(%$_) ], $low, $high; + (join(' ', $l->[0], $self->_sqlcase('and'), $h->[0]), + @{$l}[1..$#$l], @{$h}[1..$#$h]) + } + }; + my ($lhsql, @lhbind) = $self->_recurse_where($left); + return ( + join(' ', '(', $lhsql, $self->_sqlcase($op), $rhsql, ')'), + @lhbind, @rhbind + ); + } + }), 'between', 'not between'), + (map +($_ => do { + my $op = $_; + sub { + my ($self, $args) = @_; + my ($lhs, $rhs) = @$args; + my @in_bind; + my @in_sql = map { + my ($sql, @bind) = $self->_render_expr($_); + push @in_bind, @bind; + $sql; + } @$rhs; + my ($lhsql, @lbind) = $self->_render_expr($lhs); + return ( + $lhsql.' '.$self->_sqlcase($op).' ( ' + .join(', ', @in_sql) + .' )', + @lbind, @in_bind + ); + } + }), 'in', 'not in'), +); + +sub _where_op_OP { + my ($self, undef, $v) = @_; + my ($op, @args) = @$v; + $op =~ s/^-// if length($op) > 1; + $op = lc($op); + local $self->{_nested_func_lhs}; + if (my $h = $special{$op}) { + return $self->$h(\@args); + } + if (my $us = List::Util::first { $op =~ $_->{regex} } @{$self->{user_special_ops}}) { + puke "Special op '${op}' requires first value to be identifier" + unless my ($k) = map $_->{-ident}, grep ref($_) eq 'HASH', $args[0]; + return $self->${\($us->{handler})}($k, $op, $args[1]); + } + my $final_op = $op =~ /^(?:is|not)_/ ? join(' ', split '_', $op) : $op; + if (@args == 1 and $op !~ /^(and|or)$/) { + my ($expr_sql, @bind) = $self->_render_expr($args[0]); + my $op_sql = $self->_sqlcase($final_op); + my $final_sql = ( + $unop_postfix{lc($final_op)} + ? "${expr_sql} ${op_sql}" + : "${op_sql} ${expr_sql}" + ); + return (($op eq 'not' ? '('.$final_sql.')' : $final_sql), @bind); + } else { + my @parts = map [ $self->_render_expr($_) ], @args; + my ($final_sql) = map +($op =~ /^(and|or)$/ ? "(${_})" : $_), join( + ' '.$self->_sqlcase($final_op).' ', + map $_->[0], @parts + ); + return ( + $final_sql, + map @{$_}[1..$#$_], @parts + ); + } + die "unhandled"; +} + +sub _where_op_FUNC { + my ($self, undef, $rest) = @_; + my ($func, @args) = @$rest; + my @arg_sql; + my @bind = map { + my @x = @$_; + push @arg_sql, shift @x; + @x + } map [ $self->_render_expr($_) ], @args; + return ($self->_sqlcase($func).'('.join(', ', @arg_sql).')', @bind); +} + +sub _where_op_BIND { + my ($self, undef, $bind) = @_; + return ($self->_convert('?'), $self->_bindtype(@$bind)); +} + +sub _where_op_LITERAL { + my ($self, undef, $literal) = @_; + $self->_assert_bindval_matches_bindtype(@{$literal}[1..$#$literal]); + return @$literal; +} + sub _where_hashpair_ARRAYREF { my ($self, $k, $v) = @_;