# the test / diffusion / acceptance phase; those are marked with flag
# 'LDNOTE' (note by laurent.dami AT free.fr)
-use Carp;
use strict;
-use warnings;
+use Carp ();
+use warnings FATAL => 'all';
use List::Util ();
use Scalar::Util ();
+use Data::Query::Constants qw(
+ DQ_IDENTIFIER DQ_OPERATOR DQ_VALUE DQ_LITERAL DQ_JOIN DQ_SELECT DQ_ORDER
+);
+use Data::Query::ExprHelpers qw(perl_scalar_value);
#======================================================================
# GLOBALS
#======================================================================
-our $VERSION = '1.63_01';
+our $VERSION = '1.72';
# This would confuse some packagers
-#$VERSION = eval $VERSION; # numify for warning-free dev releases
+$VERSION = eval $VERSION if $VERSION =~ /_/; # numify for warning-free dev releases
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 )?between$/i, handler => '_where_field_BETWEEN'},
- {regex => qr/^(not )?in$/i, handler => '_where_field_IN'},
+ {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'},
);
# unaryish operators - key maps to handler
my @BUILTIN_UNARY_OPS = (
# the digits are backcompat stuff
- { regex => qr/^and (?: \s? \d+ )? $/xi, handler => '_where_op_ANDOR' },
- { regex => qr/^or (?: \s? \d+ )? $/xi, handler => '_where_op_ANDOR' },
- { regex => qr/^nest (?: \s? \d+ )? $/xi, handler => '_where_op_NEST' },
- { regex => qr/^ (?: not \s )? bool $/xi, handler => '_where_op_BOOL' },
+ { regex => qr/^ and (?: [_\s]? \d+ )? $/xi, handler => '_where_op_ANDOR' },
+ { regex => qr/^ or (?: [_\s]? \d+ )? $/xi, handler => '_where_op_ANDOR' },
+ { regex => qr/^ nest (?: [_\s]? \d+ )? $/xi, handler => '_where_op_NEST' },
+ { regex => qr/^ (?: not \s )? bool $/xi, handler => '_where_op_BOOL' },
+ { regex => qr/^ ident $/xi, handler => '_where_op_IDENT' },
+ { regex => qr/^ value $/ix, handler => '_where_op_VALUE' },
);
#======================================================================
sub belch (@) {
my($func) = (caller(1))[3];
- carp "[$func] Warning: ", @_;
+ Carp::carp "[$func] Warning: ", @_;
}
sub puke (@) {
my($func) = (caller(1))[3];
- croak "[$func] Fatal: ", @_;
+ Carp::croak "[$func] Fatal: ", @_;
}
$opt{sqltrue} ||= '1=1';
$opt{sqlfalse} ||= '0=1';
- # special operators
+ # special operators
$opt{special_ops} ||= [];
+ # regexes are applied in order, thus push after user-defines
push @{$opt{special_ops}}, @BUILTIN_SPECIAL_OPS;
- # unary operators
+ # unary operators
$opt{unary_ops} ||= [];
push @{$opt{unary_ops}}, @BUILTIN_UNARY_OPS;
+ # rudimentary saniy-check for user supplied bits treated as functions/operators
+ # If a purported function matches this regular expression, an exception is thrown.
+ # Literal SQL is *NOT* subject to this check, only functions (and column names
+ # when quoting is not in effect)
+
+ # FIXME
+ # need to guard against ()'s in column names too, but this will break tons of
+ # hacks... ideas anyone?
+ $opt{injection_guard} ||= qr/
+ \;
+ |
+ ^ \s* go \s
+ /xmi;
+
+ $opt{name_sep} ||= '.';
+
+ $opt{renderer} ||= do {
+ require Data::Query::Renderer::SQL::Naive;
+ my ($always, $chars);
+ for ($opt{quote_char}) {
+ $chars = defined() ? (ref() ? $_ : [$_]) : ['',''];
+ $always = defined;
+ }
+ Data::Query::Renderer::SQL::Naive->new({
+ quote_chars => $chars, always_quote => $always,
+ });
+ };
+
return bless \%opt, $class;
}
+sub _render_dq {
+ my ($self, $dq) = @_;
+ my ($sql, @bind) = @{$self->{renderer}->render($dq)};
+ wantarray ?
+ ($self->{bindtype} eq 'normal'
+ ? ($sql, map $_->{value}, @bind)
+ : ($sql, map [ $_->{value_meta}, $_->{value} ], @bind)
+ )
+ : $sql;
+}
+
+sub _literal_to_dq {
+ my ($self, $literal) = @_;
+ my @bind;
+ ($literal, @bind) = @$literal if ref($literal) eq 'ARRAY';
+ +{
+ type => DQ_LITERAL,
+ subtype => 'SQL',
+ literal => $literal,
+ (@bind ? (values => [ $self->_bind_to_dq(@bind) ]) : ()),
+ };
+}
+
+sub _literal_with_prepend_to_dq {
+ my ($self, $prepend, $literal) = @_;
+ if (ref($literal)) {
+ $self->_literal_to_dq(
+ [ join(' ', $prepend, $literal->[0]), @{$literal}[1..$#$literal] ]
+ );
+ } else {
+ $self->_literal_to_dq(
+ join(' ', $prepend, $literal)
+ );
+ }
+}
+
+sub _bind_to_dq {
+ my ($self, @bind) = @_;
+ return unless @bind;
+ $self->{bindtype} eq 'normal'
+ ? map perl_scalar_value($_), @bind
+ : do {
+ $self->_assert_bindval_matches_bindtype(@bind);
+ map perl_scalar_value(reverse @$_), @bind
+ }
+}
+
+sub _value_to_dq {
+ my ($self, $value) = @_;
+ perl_scalar_value($value, our $Cur_Col_Meta);
+}
+
+sub _ident_to_dq {
+ my ($self, $ident) = @_;
+ +{
+ type => DQ_IDENTIFIER,
+ elements => [ split /\Q$self->{name_sep}/, $ident ],
+ };
+}
+
+sub _assert_pass_injection_guard {
+ if ($_[1] =~ $_[0]->{injection_guard}) {
+ my $class = ref $_[0];
+ puke "Possible SQL injection attempt '$_[1]'. If this is indeed a part of the "
+ . "desired SQL use literal SQL ( \'...' or \[ '...' ] ) or supply your own "
+ . "{injection_guard} attribute to ${class}->new()"
+ }
+}
#======================================================================
my ($sql, @bind) = $self->$method($data);
$sql = join " ", $self->_sqlcase('insert into'), $table, $sql;
- if (my $ret = $options->{returning}) {
- $sql .= $self->_insert_returning ($ret);
+ if ($options->{returning}) {
+ my ($s, @b) = $self->_insert_returning ($options);
+ $sql .= $s;
+ push @bind, @b;
}
return wantarray ? ($sql, @bind) : $sql;
}
sub _insert_returning {
- my ($self, $fields) = @_;
+ my ($self, $options) = @_;
- my $f = $self->_SWITCH_refkind($fields, {
- ARRAYREF => sub {join ', ', map { $self->_quote($_) } @$fields;},
- SCALAR => sub {$self->_quote($fields)},
- SCALARREF => sub {$$fields},
+ my $f = $options->{returning};
+
+ my $fieldlist = $self->_SWITCH_refkind($f, {
+ ARRAYREF => sub {join ', ', map { $self->_quote($_) } @$f;},
+ SCALAR => sub {$self->_quote($f)},
+ SCALARREF => sub {$$f},
});
- return join (' ', $self->_sqlcase(' returning'), $f);
+ return $self->_sqlcase(' returning ') . $fieldlist;
}
sub _insert_HASHREF { # explicit list of fields and then values
$self->_SWITCH_refkind($v, {
- ARRAYREF => sub {
+ ARRAYREF => sub {
if ($self->{array_datatypes}) { # if array datatype are activated
push @values, '?';
push @all_bind, $self->_bindtype($column, $v);
push @all_bind, @bind;
},
- # THINK : anything useful to do with a HASHREF ?
+ # THINK : anything useful to do with a HASHREF ?
HASHREF => sub { # (nothing, but old SQLA passed it through)
#TODO in SQLA >= 2.0 it will die instead
belch "HASH ref as bind value in insert is not supported";
my $label = $self->_quote($k);
$self->_SWITCH_refkind($v, {
- ARRAYREF => sub {
+ ARRAYREF => sub {
if ($self->{array_datatypes}) { # array datatype
push @set, "$label = ?";
push @all_bind, $self->_bindtype($k, $v);
},
SCALARREF => sub { # literal SQL without bind
push @set, "$label = $$v";
- },
+ },
+ HASHREF => sub {
+ my ($op, $arg, @rest) = %$v;
+
+ puke 'Operator calls in update must be in the form { -op => $arg }'
+ if (@rest or not $op =~ /^\-(.+)/);
+
+ local $self->{_nested_func_lhs} = $k;
+ my ($sql, @bind) = $self->_where_unary_op ($1, $arg);
+
+ push @set, "$label = $sql";
+ push @all_bind, @bind;
+ },
SCALAR_or_UNDEF => sub {
push @set, "$label = ?";
push @all_bind, $self->_bindtype($k, $v);
sub select {
my $self = shift;
- my $table = $self->_table(shift);
+ my $table = shift;
my $fields = shift || '*';
my $where = shift;
my $order = shift;
my($where_sql, @bind) = $self->where($where, $order);
- my $f = (ref $fields eq 'ARRAY') ? join ', ', map { $self->_quote($_) } @$fields
- : $fields;
- my $sql = join(' ', $self->_sqlcase('select'), $f,
- $self->_sqlcase('from'), $table)
- . $where_sql;
+ my $sql = $self->_render_dq({
+ type => DQ_SELECT,
+ select => [
+ map $self->_ident_to_dq($_),
+ ref($fields) eq 'ARRAY' ? @$fields : $fields
+ ],
+ from => $self->_table_to_dq($table),
+ });
+
+ $sql .= $where_sql;
- return wantarray ? ($sql, @bind) : $sql;
+ return wantarray ? ($sql, @bind) : $sql;
}
#======================================================================
my($where_sql, @bind) = $self->where($where);
my $sql = $self->_sqlcase('delete from') . " $table" . $where_sql;
- return wantarray ? ($sql, @bind) : $sql;
+ return wantarray ? ($sql, @bind) : $sql;
}
$sql .= $self->_order_by($order);
}
- return wantarray ? ($sql, @bind) : $sql;
+ return wantarray ? ($sql, @bind) : $sql;
}
sub _recurse_where {
my ($self, $where, $logic) = @_;
- # dispatch on appropriate method according to refkind of $where
- my $method = $self->_METHOD_FOR_refkind("_where", $where);
-
- my ($sql, @bind) = $self->$method($where, $logic);
-
- # DBIx::Class directly calls _recurse_where in scalar context, so
- # we must implement it, even if not in the official API
- return wantarray ? ($sql, @bind) : $sql;
+ return $self->_render_dq($self->_where_to_dq($where, $logic));
}
+sub _where_to_dq {
+ my ($self, $where, $logic) = @_;
+ if (ref($where) eq 'ARRAY') {
+ return $self->_where_to_dq_ARRAYREF($where, $logic);
+ } elsif (ref($where) eq 'HASH') {
+ return $self->_where_to_dq_HASHREF($where, $logic);
+ } elsif (
+ ref($where) eq 'SCALAR'
+ or (ref($where) eq 'REF' and ref($$where) eq 'ARRAY')
+ ) {
+ return $self->_literal_to_dq($$where);
+ } elsif (!ref($where) or Scalar::Util::blessed($where)) {
+ return $self->_value_to_dq($where);
+ }
+ die "Can't handle $where";
+}
-#======================================================================
-# WHERE: top-level ARRAYREF
-#======================================================================
-
-
-sub _where_ARRAYREF {
+sub _where_to_dq_ARRAYREF {
my ($self, $where, $logic) = @_;
- $logic = uc($logic || $self->{logic});
+ $logic = uc($logic || 'OR');
$logic eq 'AND' or $logic eq 'OR' or puke "unknown logic: $logic";
- my @clauses = @$where;
-
- my (@sql_clauses, @all_bind);
- # need to use while() so can shift() for pairs
- while (my $el = shift @clauses) {
-
- # switch according to kind of $el and get corresponding ($sql, @bind)
- my ($sql, @bind) = $self->_SWITCH_refkind($el, {
+ return unless @$where;
- # skip empty elements, otherwise get invalid trailing AND stuff
- ARRAYREF => sub {$self->_recurse_where($el) if @$el},
+ my ($first, @rest) = @$where;
- ARRAYREFREF => sub { @{${$el}} if @{${$el}}},
+ return $self->_where_to_dq($first) unless @rest;
- HASHREF => sub {$self->_recurse_where($el, 'and') if %$el},
- # LDNOTE : previous SQLA code for hashrefs was creating a dirty
- # side-effect: the first hashref within an array would change
- # the global logic to 'AND'. So [ {cond1, cond2}, [cond3, cond4] ]
- # was interpreted as "(cond1 AND cond2) OR (cond3 AND cond4)",
- # whereas it should be "(cond1 AND cond2) OR (cond3 OR cond4)".
-
- SCALARREF => sub { ($$el); },
-
- SCALAR => sub {# top-level arrayref with scalars, recurse in pairs
- $self->_recurse_where({$el => shift(@clauses)})},
-
- UNDEF => sub {puke "not supported : UNDEF in arrayref" },
- });
-
- if ($sql) {
- push @sql_clauses, $sql;
- push @all_bind, @bind;
+ my $first_dq = do {
+ if (!ref($first)) {
+ $self->_where_hashpair_to_dq($first => shift(@rest));
+ } else {
+ $self->_where_to_dq($first);
}
- }
-
- return $self->_join_sql_clauses($logic, \@sql_clauses, \@all_bind);
-}
-
-#======================================================================
-# WHERE: top-level ARRAYREFREF
-#======================================================================
+ };
-sub _where_ARRAYREFREF {
- my ($self, $where) = @_;
- my ($sql, @bind) = @{${$where}};
+ return $self->_where_to_dq_ARRAYREF(\@rest, $logic) unless $first_dq;
- return ($sql, @bind);
+ +{
+ type => DQ_OPERATOR,
+ operator => { 'SQL.Naive' => $logic },
+ args => [ $first_dq, $self->_where_to_dq_ARRAYREF(\@rest, $logic) ]
+ };
}
-#======================================================================
-# WHERE: top-level HASHREF
-#======================================================================
-
-sub _where_HASHREF {
- my ($self, $where) = @_;
- my (@sql_clauses, @all_bind);
-
- for my $k (sort keys %$where) {
- my $v = $where->{$k};
-
- # ($k => $v) is either a special unary op or a regular hashpair
- my ($sql, @bind) = do {
- if ($k =~ /^-./) {
- # put the operator in canonical form
- my $op = $k;
- $op =~ s/^-//; # remove initial dash
- $op =~ s/[_\t ]+/ /g; # underscores and whitespace become single spaces
- $op =~ s/^\s+|\s+$//g;# remove leading/trailing space
-
- $self->_debug("Unary OP(-$op) within hashref, recursing...");
-
- my $op_entry = List::Util::first {$op =~ $_->{regex}} @{$self->{unary_ops}};
- if (my $handler = $op_entry->{handler}) {
- if (not ref $handler) {
- if ($op =~ s/\s?\d+$//) {
- belch 'Use of [and|or|nest]_N modifiers is deprecated and will be removed in SQLA v2.0. '
- . "You probably wanted ...-and => [ -$op => COND1, -$op => COND2 ... ]";
- }
- $self->$handler ($op, $v);
- }
- elsif (ref $handler eq 'CODE') {
- $handler->($self, $op, $v);
- }
- else {
- puke "Illegal handler for operator $k - expecting a method name or a coderef";
- }
- }
- else {
- $self->debug("Generic unary OP: $k - recursing as function");
- my ($sql, @bind) = $self->_where_func_generic ($op, $v);
- $sql = "($sql)" unless $self->{_nested_func_lhs} eq $k; # top level vs nested
- ($sql, @bind);
- }
- }
- else {
- my $method = $self->_METHOD_FOR_refkind("_where_hashpair", $v);
- $self->$method($k, $v);
- }
- };
-
- push @sql_clauses, $sql;
- push @all_bind, @bind;
- }
+sub _where_to_dq_HASHREF {
+ my ($self, $where, $logic) = @_;
- return $self->_join_sql_clauses('and', \@sql_clauses, \@all_bind);
-}
+ $logic = uc($logic || 'AND');
-sub _where_func_generic {
- my ($self, $op, $rhs) = @_;
+ my @dq = map {
+ $self->_where_hashpair_to_dq($_ => $where->{$_})
+ } sort keys %$where;
- my ($sql, @bind) = $self->_SWITCH_refkind ($rhs, {
- SCALAR => sub {
- puke "Illegal use of top-level '$op'"
- unless $self->{_nested_func_lhs};
+ return $dq[0] unless @dq > 1;
- return (
- $self->_convert('?'),
- $self->_bindtype($self->{_nested_func_lhs}, $rhs)
- );
- },
- FALLBACK => sub {
- $self->_recurse_where ($rhs)
- },
- });
+ my $final = pop(@dq);
- $sql = sprintf ('%s %s',
- $self->_sqlcase($op),
- $sql,
- );
+ foreach my $dq (reverse @dq) {
+ $final = +{
+ type => DQ_OPERATOR,
+ operator => { 'SQL.Naive' => $logic },
+ args => [ $dq, $final ]
+ }
+ }
- return ($sql, @bind);
+ return $final;
}
-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 not supported, use -nest => ...";
- },
-
- ARRAYREFREF => sub {
- puke "-$op => \\[..] not supported, use -nest => ...";
- },
-
- SCALAR => sub { # permissively interpreted as SQL
- puke "-$op => 'scalar' not supported, use -nest => \\'scalar'";
- },
-
- UNDEF => sub {
- puke "-$op => undef not supported";
- },
- });
+sub _where_to_dq_SCALAR {
+ shift->_value_to_dq(@_);
}
-sub _where_op_NEST {
- my ($self, $op, $v) = @_;
-
- $self->_SWITCH_refkind($v, {
-
- SCALAR => sub { # permissively interpreted as SQL
- belch "literal SQL should be -nest => \\'scalar' "
- . "instead of -nest => 'scalar' ";
- return ($v);
- },
+sub _where_op_IDENT {
+ my $self = shift;
+ my ($op, $rhs) = splice @_, -2;
+ if (ref $rhs) {
+ puke "-$op takes a single scalar argument (a quotable identifier)";
+ }
- UNDEF => sub {
- puke "-$op => undef not supported";
- },
+ # in case we are called as a top level special op (no '=')
+ my $lhs = shift;
- FALLBACK => sub {
- $self->_recurse_where ($v);
- },
+ $_ = $self->_convert($self->_quote($_)) for ($lhs, $rhs);
- });
+ return $lhs
+ ? "$lhs = $rhs"
+ : $rhs
+ ;
}
+sub _where_op_VALUE {
+ my $self = shift;
+ my ($op, $rhs) = splice @_, -2;
-sub _where_op_BOOL {
- my ($self, $op, $v) = @_;
-
- my ( $prefix, $suffix ) = ( $op =~ /\bnot\b/i )
- ? ( '(NOT ', ')' )
- : ( '', '' );
-
- my ($sql, @bind) = do {
- $self->_SWITCH_refkind($v, {
- SCALAR => sub { # interpreted as SQL column
- $self->_convert($self->_quote($v));
- },
-
- UNDEF => sub {
- puke "-$op => undef not supported";
- },
+ # in case we are called as a top level special op (no '=')
+ my $lhs = shift;
- FALLBACK => sub {
- $self->_recurse_where ($v);
- },
- });
- };
+ my @bind =
+ $self->_bindtype (
+ ($lhs || $self->{_nested_func_lhs}),
+ $rhs,
+ )
+ ;
- return (
- join ('', $prefix, $sql, $suffix),
- @bind,
- );
+ return $lhs
+ ? (
+ $self->_convert($self->_quote($lhs)) . ' = ' . $self->_convert('?'),
+ @bind
+ )
+ : (
+ $self->_convert('?'),
+ @bind,
+ )
+ ;
}
-
-sub _where_hashpair_ARRAYREF {
+sub _where_hashpair_to_dq {
my ($self, $k, $v) = @_;
- if( @$v ) {
- my @v = @$v; # need copy because of shift below
- $self->_debug("ARRAY($k) means distribute over elements");
-
- # put apart first element if it is an operator (-and, -or)
- my $op = (
- (defined $v[0] && $v[0] =~ /^ - (?: AND|OR ) $/ix)
- ? shift @v
- : ''
- );
- my @distributed = map { {$k => $_} } @v;
-
- if ($op) {
- $self->_debug("OP($op) reinjected into the distributed array");
- unshift @distributed, $op;
- }
-
- my $logic = $op ? substr($op, 1) : '';
-
- return $self->_recurse_where(\@distributed, $logic);
- }
- else {
- # LDNOTE : not sure of this one. What does "distribute over nothing" mean?
- $self->_debug("empty ARRAY($k) means 0=1");
- return ($self->{sqlfalse});
- }
-}
-
-sub _where_hashpair_HASHREF {
- my ($self, $k, $v, $logic) = @_;
- $logic ||= 'and';
-
- local $self->{_nested_func_lhs} = $self->{_nested_func_lhs};
-
- my ($all_sql, @all_bind);
-
- for my $orig_op (sort keys %$v) {
- my $val = $v->{$orig_op};
-
- # put the operator in canonical form
- my $op = $orig_op;
- $op =~ s/^-//; # remove initial dash
- $op =~ s/[_\t ]+/ /g; # underscores and whitespace become single spaces
- $op =~ s/^\s+|\s+$//g;# remove leading/trailing space
-
- my ($sql, @bind);
-
- # CASE: col-value logic modifiers
- if ( $orig_op =~ /^ \- (and|or) $/xi ) {
- ($sql, @bind) = $self->_where_hashpair_HASHREF($k, $val, $1);
+ if ($k =~ /-(.*)/) {
+ my $op = uc($1);
+ if ($op eq 'AND' or $op eq 'OR') {
+ return $self->_where_to_dq($v, $op);
+ } elsif ($op eq 'NEST') {
+ return $self->_where_to_dq($v);
+ } elsif ($op eq 'NOT') {
+ return +{
+ type => DQ_OPERATOR,
+ operator => { 'SQL.Naive' => 'NOT' },
+ args => [ $self->_where_to_dq($v) ]
+ }
+ } elsif ($op eq 'BOOL') {
+ return ref($v) ? $self->_where_to_dq($v) : $self->_ident_to_dq($v);
+ } elsif ($op eq 'NOT_BOOL') {
+ return +{
+ type => DQ_OPERATOR,
+ operator => { 'SQL.Naive' => 'NOT' },
+ args => [ ref($v) ? $self->_where_to_dq($v) : $self->_ident_to_dq($v) ]
+ };
+ } else {
+ my @args = do {
+ if (ref($v) eq 'HASH' and keys(%$v) == 1 and (keys %$v)[0] =~ /-(.*)/) {
+ my ($inner) = values %$v;
+ +{
+ type => DQ_OPERATOR,
+ operator => { 'SQL.Naive' => uc($1) },
+ args => [
+ (map $self->_where_to_dq($_),
+ (ref($inner) eq 'ARRAY' ? @$inner : $inner))
+ ]
+ };
+ } else {
+ (map $self->_where_to_dq($_), (ref($v) eq 'ARRAY' ? @$v : $v))
+ }
+ };
+ return +{
+ type => DQ_OPERATOR,
+ operator => { 'SQL.Naive' => 'apply' },
+ args => [
+ $self->_ident_to_dq($op), @args
+ ],
+ };
}
- # CASE: special operators like -in or -between
- elsif ( my $special_op = List::Util::first {$op =~ $_->{regex}} @{$self->{special_ops}} ) {
- my $handler = $special_op->{handler};
- if (! $handler) {
- puke "No handler supplied for special operator $orig_op";
+ } else {
+ local our $Cur_Col_Meta = $k;
+ if (ref($v) eq 'ARRAY') {
+ if (!@$v) {
+ return $self->_literal_to_dq($self->{sqlfalse});
+ } elsif (defined($v->[0]) && $v->[0] =~ /-(and|or)/i) {
+ return $self->_where_to_dq_ARRAYREF([
+ map +{ $k => $_ }, @{$v}[1..$#$v]
+ ], uc($1));
}
- elsif (not ref $handler) {
- ($sql, @bind) = $self->$handler ($k, $op, $val);
+ return $self->_where_to_dq_ARRAYREF([
+ map +{ $k => $_ }, @$v
+ ]);
+ } elsif (ref($v) eq 'SCALAR' or (ref($v) eq 'REF' and ref($$v) eq 'ARRAY')) {
+ return $self->_literal_with_prepend_to_dq($k, $$v);
+ }
+ my ($op, $rhs) = do {
+ if (ref($v) eq 'HASH') {
+ if (keys %$v > 1) {
+ return $self->_where_to_dq_ARRAYREF([
+ map +{ $k => { $_ => $v->{$_} } }, keys %$v
+ ], 'AND');
+ }
+ (uc((keys %$v)[0]), (values %$v)[0]);
+ } else {
+ ('=', $v);
}
- elsif (ref $handler eq 'CODE') {
- ($sql, @bind) = $handler->($self, $k, $op, $val);
+ };
+ s/^-//, s/_/ /g for $op;
+ if ($op eq 'BETWEEN' or $op eq 'IN' or $op eq 'NOT IN' or $op eq 'NOT BETWEEN') {
+ if (ref($rhs) ne 'ARRAY') {
+ if ($op =~ /IN$/) {
+ # have to add parens if none present because -in => \"SELECT ..."
+ # got documented. mst hates everything.
+ if (ref($rhs) eq 'SCALAR') {
+ my $x = $$rhs;
+ $x = "($x)" unless $x =~ /^\s*\(/;
+ $rhs = \$x;
+ } else {
+ my ($x, @rest) = @{$$rhs};
+ $x = "($x)" unless $x =~ /^\s*\(/;
+ $rhs = \[ $x, @rest ];
+ }
+ }
+ return $self->_literal_with_prepend_to_dq("$k $op", $$rhs);
}
- else {
- puke "Illegal handler for special operator $orig_op - expecting a method name or a coderef";
+ return $self->_literal_to_dq($self->{sqlfalse}) unless @$rhs;
+ return +{
+ type => DQ_OPERATOR,
+ operator => { 'SQL.Naive' => $op },
+ args => [ $self->_ident_to_dq($k), map $self->_where_to_dq($_), @$rhs ]
}
+ } elsif ($op =~ s/^NOT (?!LIKE)//) {
+ return $self->_where_hashpair_to_dq(-not => { $k => { $op => $rhs } });
+ } elsif (!defined($rhs)) {
+ my $null_op = do {
+ if ($op eq '=' or $op eq 'LIKE') {
+ 'IS NULL'
+ } elsif ($op eq '!=') {
+ 'IS NOT NULL'
+ } else {
+ die "Can't do undef -> NULL transform for operator ${op}";
+ }
+ };
+ return +{
+ type => DQ_OPERATOR,
+ operator => { 'SQL.Naive' => $null_op },
+ args => [ $self->_ident_to_dq($k) ]
+ };
}
- else {
- $self->_SWITCH_refkind($val, {
-
- ARRAYREF => sub { # CASE: col => {op => \@vals}
- ($sql, @bind) = $self->_where_field_op_ARRAYREF($k, $op, $val);
- },
-
- ARRAYREFREF => sub { # CASE: col => {op => \[$sql, @bind]} (literal SQL with bind)
- my ($sub_sql, @sub_bind) = @$$val;
- $self->_assert_bindval_matches_bindtype(@sub_bind);
- $sql = join ' ', $self->_convert($self->_quote($k)),
- $self->_sqlcase($op),
- $sub_sql;
- @bind = @sub_bind;
- },
-
- UNDEF => sub { # CASE: col => {op => undef} : sql "IS (NOT)? NULL"
- my $is = ($op =~ $self->{equality_op}) ? 'is' :
- ($op =~ $self->{inequality_op}) ? 'is not' :
- puke "unexpected operator '$orig_op' with undef operand";
- $sql = $self->_quote($k) . $self->_sqlcase(" $is null");
- },
-
- FALLBACK => sub { # CASE: col => {op/func => $stuff}
-
- # retain for proper column type bind
- $self->{_nested_func_lhs} ||= $k;
-
- ($sql, @bind) = $self->_where_func_generic ($op, $val);
-
- $sql = join (' ',
- $self->_convert($self->_quote($k)),
- $self->{_nested_func_lhs} eq $k ? $sql : "($sql)", # top level vs nested
- );
- },
- });
+ if (ref($rhs) eq 'ARRAY') {
+ if (!@$rhs) {
+ return $self->_literal_to_dq(
+ $op eq '!=' ? $self->{sqltrue} : $self->{sqlfalse}
+ );
+ } elsif (defined($rhs->[0]) and $rhs->[0] =~ /-(and|or)/i) {
+ return $self->_where_to_dq_ARRAYREF([
+ map +{ $k => { $op => $_ } }, @{$rhs}[1..$#$rhs]
+ ], uc($1));
+ }
+ return $self->_where_to_dq_ARRAYREF([
+ map +{ $k => { $op => $_ } }, @$rhs
+ ]);
}
-
- ($all_sql) = (defined $all_sql and $all_sql) ? $self->_join_sql_clauses($logic, [$all_sql, $sql], []) : $sql;
- push @all_bind, @bind;
- }
- return ($all_sql, @all_bind);
-}
-
-
-
-sub _where_field_op_ARRAYREF {
- my ($self, $k, $op, $vals) = @_;
-
- my @vals = @$vals; #always work on a copy
-
- if(@vals) {
- $self->_debug(sprintf '%s means multiple elements: [ %s ]',
- $vals,
- join (', ', map { defined $_ ? "'$_'" : 'NULL' } @vals ),
- );
-
- # see if the first element is an -and/-or op
- my $logic;
- if (defined $vals[0] && $vals[0] =~ /^ - ( AND|OR ) $/ix) {
- $logic = uc $1;
- shift @vals;
+ return +{
+ type => DQ_OPERATOR,
+ operator => { 'SQL.Naive' => $op },
+ args => [ $self->_ident_to_dq($k), $self->_where_to_dq($rhs) ]
}
-
- # distribute $op over each remaining member of @vals, append logic if exists
- return $self->_recurse_where([map { {$k => {$op, $_}} } @vals], $logic);
-
- # LDNOTE : had planned to change the distribution logic when
- # $op =~ $self->{inequality_op}, because of Morgan laws :
- # with {field => {'!=' => [22, 33]}}, it would be ridiculous to generate
- # WHERE field != 22 OR field != 33 : the user probably means
- # WHERE field != 22 AND field != 33.
- # To do this, replace the above to roughly :
- # my $logic = ($op =~ $self->{inequality_op}) ? 'AND' : 'OR';
- # return $self->_recurse_where([map { {$k => {$op, $_}} } @vals], $logic);
-
- }
- else {
- # try to DWIM on equality operators
- # LDNOTE : not 100% sure this is the correct thing to do ...
- return ($self->{sqlfalse}) if $op =~ $self->{equality_op};
- return ($self->{sqltrue}) if $op =~ $self->{inequality_op};
-
- # otherwise
- puke "operator '$op' applied on an empty array (field '$k')";
}
}
-
-sub _where_hashpair_SCALARREF {
- my ($self, $k, $v) = @_;
- $self->_debug("SCALAR($k) means literal SQL: $$v");
- my $sql = $self->_quote($k) . " " . $$v;
- return ($sql);
-}
-
-# literal SQL with bind
-sub _where_hashpair_ARRAYREFREF {
- my ($self, $k, $v) = @_;
- $self->_debug("REF($k) means literal SQL: @${$v}");
- my ($sql, @bind) = @${$v};
- $self->_assert_bindval_matches_bindtype(@bind);
- $sql = $self->_quote($k) . " " . $sql;
- return ($sql, @bind );
-}
-
-# literal SQL without bind
-sub _where_hashpair_SCALAR {
- my ($self, $k, $v) = @_;
- $self->_debug("NOREF($k) means simple key=val: $k $self->{cmp} $v");
- my $sql = join ' ', $self->_convert($self->_quote($k)),
- $self->_sqlcase($self->{cmp}),
- $self->_convert('?');
- my @bind = $self->_bindtype($k, $v);
- return ( $sql, @bind);
-}
-
-
-sub _where_hashpair_UNDEF {
- my ($self, $k, $v) = @_;
- $self->_debug("UNDEF($k) means IS NULL");
- my $sql = $self->_quote($k) . $self->_sqlcase(' is null');
- return ($sql);
-}
-
-#======================================================================
-# WHERE: TOP-LEVEL OTHERS (SCALARREF, SCALAR, UNDEF)
-#======================================================================
-
-
-sub _where_SCALARREF {
- my ($self, $where) = @_;
-
- # literal sql
- $self->_debug("SCALAR(*top) means literal SQL: $$where");
- return ($$where);
-}
-
-
-sub _where_SCALAR {
- my ($self, $where) = @_;
-
- # literal sql
- $self->_debug("NOREF(*top) means literal SQL: $where");
- return ($where);
-}
-
-
-sub _where_UNDEF {
- my ($self) = @_;
- return ();
-}
-
-
-#======================================================================
-# WHERE: BUILTIN SPECIAL OPERATORS (-in, -between)
-#======================================================================
-
-
-sub _where_field_BETWEEN {
- my ($self, $k, $op, $vals) = @_;
-
- my ($label, $and, $placeholder);
- $label = $self->_convert($self->_quote($k));
- $and = ' ' . $self->_sqlcase('and') . ' ';
- $placeholder = $self->_convert('?');
- $op = $self->_sqlcase($op);
-
- my ($clause, @bind) = $self->_SWITCH_refkind($vals, {
- ARRAYREFREF => sub {
- return @$$vals;
- },
- SCALARREF => sub {
- return $$vals;
- },
- ARRAYREF => sub {
- puke "special op 'between' accepts an arrayref with exactly two values"
- if @$vals != 2;
-
- my (@all_sql, @all_bind);
- foreach my $val (@$vals) {
- my ($sql, @bind) = $self->_SWITCH_refkind($val, {
- SCALAR => sub {
- return ($placeholder, ($val));
- },
- SCALARREF => sub {
- return ($self->_convert($$val), ());
- },
- ARRAYREFREF => sub {
- my ($sql, @bind) = @$$val;
- return ($self->_convert($sql), @bind);
- },
- });
- push @all_sql, $sql;
- push @all_bind, @bind;
- }
-
- return (
- (join $and, @all_sql),
- $self->_bindtype($k, @all_bind),
- );
- },
- FALLBACK => sub {
- puke "special op 'between' accepts an arrayref with two values, or a single literal scalarref/arrayref-ref";
- },
- });
-
- my $sql = "( $label $op $clause )";
- return ($sql, @bind)
-}
-
-
-sub _where_field_IN {
- my ($self, $k, $op, $vals) = @_;
-
- # backwards compatibility : if scalar, force into an arrayref
- $vals = [$vals] if defined $vals && ! ref $vals;
-
- my ($label) = $self->_convert($self->_quote($k));
- my ($placeholder) = $self->_convert('?');
- $op = $self->_sqlcase($op);
-
- my ($sql, @bind) = $self->_SWITCH_refkind($vals, {
- ARRAYREF => sub { # list of choices
- if (@$vals) { # nonempty list
- my $placeholders = join ", ", (($placeholder) x @$vals);
- my $sql = "$label $op ( $placeholders )";
- my @bind = $self->_bindtype($k, @$vals);
-
- return ($sql, @bind);
- }
- else { # empty list : some databases won't understand "IN ()", so DWIM
- my $sql = ($op =~ /\bnot\b/i) ? $self->{sqltrue} : $self->{sqlfalse};
- return ($sql);
- }
- },
-
- SCALARREF => sub { # literal SQL
- my $sql = $self->_open_outer_paren ($$vals);
- return ("$label $op ( $sql )");
- },
- ARRAYREFREF => sub { # literal SQL with bind
- my ($sql, @bind) = @$$vals;
- $self->_assert_bindval_matches_bindtype(@bind);
- $sql = $self->_open_outer_paren ($sql);
- return ("$label $op ( $sql )", @bind);
- },
-
- FALLBACK => sub {
- puke "special op 'in' requires an arrayref (or scalarref/arrayref-ref)";
- },
- });
-
- return ($sql, @bind);
-}
-
-# Some databases (SQLite) treat col IN (1, 2) different from
-# col IN ( (1, 2) ). Use this to strip all outer parens while
-# adding them back in the corresponding method
-sub _open_outer_paren {
- my ($self, $sql) = @_;
- $sql = $1 while $sql =~ /^ \s* \( (.*) \) \s* $/xs;
- return $sql;
-}
-
-
#======================================================================
# ORDER BY
#======================================================================
sub _order_by {
my ($self, $arg) = @_;
-
- my (@sql, @bind);
- for my $c ($self->_order_by_chunks ($arg) ) {
- $self->_SWITCH_refkind ($c, {
- SCALAR => sub { push @sql, $c },
- ARRAYREF => sub { push @sql, shift @$c; push @bind, @$c },
- });
+ if (my $dq = $self->_order_by_to_dq($arg)) {
+ # SQLA generates ' ORDER BY foo'. The hilarity.
+ wantarray
+ ? do { my @r = $self->_render_dq($dq); $r[0] = ' '.$r[0]; @r }
+ : ' '.$self->_render_dq($dq);
+ } else {
+ '';
}
-
- my $sql = @sql
- ? sprintf ('%s %s',
- $self->_sqlcase(' order by'),
- join (', ', @sql)
- )
- : ''
- ;
-
- return wantarray ? ($sql, @bind) : $sql;
}
-sub _order_by_chunks {
- my ($self, $arg) = @_;
-
- return $self->_SWITCH_refkind($arg, {
-
- ARRAYREF => sub {
- map { $self->_order_by_chunks ($_ ) } @$arg;
- },
-
- ARRAYREFREF => sub { [ @$$arg ] },
-
- SCALAR => sub {$self->_quote($arg)},
-
- UNDEF => sub {return () },
-
- SCALARREF => sub {$$arg}, # literal SQL, no quoting
-
- HASHREF => sub {
- # get first pair in hash
- my ($key, $val) = each %$arg;
-
- return () unless $key;
-
- if ( (keys %$arg) > 1 or not $key =~ /^-(desc|asc)/i ) {
- puke "hash passed to _order_by must have exactly one key (-desc or -asc)";
- }
-
- my $direction = $1;
-
- my @ret;
- for my $c ($self->_order_by_chunks ($val)) {
- my ($sql, @bind);
-
- $self->_SWITCH_refkind ($c, {
- SCALAR => sub {
- $sql = $c;
- },
- ARRAYREF => sub {
- ($sql, @bind) = @$c;
- },
- });
+sub _order_by_to_dq {
+ my ($self, $arg, $dir) = @_;
- $sql = $sql . ' ' . $self->_sqlcase($direction);
+ return unless $arg;
- push @ret, [ $sql, @bind];
- }
+ my $dq = {
+ type => DQ_ORDER,
+ ($dir ? (direction => $dir) : ()),
+ };
- return @ret;
- },
- });
+ if (!ref($arg)) {
+ $dq->{by} = $self->_ident_to_dq($arg);
+ } elsif (ref($arg) eq 'ARRAY') {
+ return unless @$arg;
+ local our $Order_Inner unless our $Order_Recursing;
+ local $Order_Recursing = 1;
+ my ($outer, $inner);
+ foreach my $member (@$arg) {
+ local $Order_Inner;
+ my $next = $self->_order_by_to_dq($member, $dir);
+ $outer ||= $next;
+ $inner->{from} = $next if $inner;
+ $inner = $Order_Inner || $next;
+ }
+ $Order_Inner = $inner;
+ return $outer;
+ } elsif (ref($arg) eq 'REF' and ref($$arg) eq 'ARRAY') {
+ $dq->{by} = $self->_literal_to_dq($$arg);
+ } elsif (ref($arg) eq 'SCALAR') {
+ $dq->{by} = $self->_literal_to_dq($$arg);
+ } elsif (ref($arg) eq 'HASH') {
+ my ($key, $val, @rest) = %$arg;
+
+ return unless $key;
+
+ if (@rest or not $key =~ /^-(desc|asc)/i) {
+ puke "hash passed to _order_by must have exactly one key (-desc or -asc)";
+ }
+ my $dir = uc $1;
+ return $self->_order_by_to_dq($val, $dir);
+ } else {
+ die "Can't handle $arg in _order_by_to_dq";
+ }
+ return $dq;
}
-
#======================================================================
# DATASOURCE (FOR NOW, JUST PLAIN TABLE OR LIST OF TABLES)
#======================================================================
sub _table {
- my $self = shift;
- my $from = shift;
+ my ($self, $from) = @_;
+ $self->_render_dq($self->_table_to_dq($from));
+}
+
+sub _table_to_dq {
+ my ($self, $from) = @_;
$self->_SWITCH_refkind($from, {
- ARRAYREF => sub {join ', ', map { $self->_quote($_) } @$from;},
- SCALAR => sub {$self->_quote($from)},
- SCALARREF => sub {$$from},
- ARRAYREFREF => sub {join ', ', @$from;},
+ ARRAYREF => sub {
+ die "Empty FROM list" unless my @f = @$from;
+ my $dq = $self->_ident_to_dq(shift @f);
+ while (my $x = shift @f) {
+ $dq = {
+ type => DQ_JOIN,
+ join => [ $dq, $self->_ident_to_dq($x) ]
+ };
+ }
+ $dq;
+ },
+ SCALAR => sub { $self->_ident_to_dq($from) },
+ SCALARREF => sub {
+ +{
+ type => DQ_LITERAL,
+ subtype => 'SQL',
+ literal => $$from
+ }
+ },
});
}
# UTILITY FUNCTIONS
#======================================================================
+# highly optimized, as it's called way too often
sub _quote {
- my $self = shift;
- my $label = shift;
-
- $label or puke "can't quote an empty label";
-
- # left and right quote characters
- my ($ql, $qr, @other) = $self->_SWITCH_refkind($self->{quote_char}, {
- SCALAR => sub {($self->{quote_char}, $self->{quote_char})},
- ARRAYREF => sub {@{$self->{quote_char}}},
- UNDEF => sub {()},
- });
- not @other
- or puke "quote_char must be an arrayref of 2 values";
+ # my ($self, $label) = @_;
- # no quoting if no quoting chars
- $ql or return $label;
+ return '' unless defined $_[1];
+ return ${$_[1]} if ref($_[1]) eq 'SCALAR';
- # no quoting for literal SQL
- return $$label if ref($label) eq 'SCALAR';
-
- # separate table / column (if applicable)
- my $sep = $self->{name_sep} || '';
- my @to_quote = $sep ? split /\Q$sep\E/, $label : ($label);
+ unless ($_[0]->{quote_char}) {
+ $_[0]->_assert_pass_injection_guard($_[1]);
+ return $_[1];
+ }
- # do the quoting, except for "*" or for `table`.*
- my @quoted = map { $_ eq '*' ? $_: $ql.$_.$qr} @to_quote;
+ my $qref = ref $_[0]->{quote_char};
+ my ($l, $r);
+ if (!$qref) {
+ ($l, $r) = ( $_[0]->{quote_char}, $_[0]->{quote_char} );
+ }
+ elsif ($qref eq 'ARRAY') {
+ ($l, $r) = @{$_[0]->{quote_char}};
+ }
+ else {
+ puke "Unsupported quote_char format: $_[0]->{quote_char}";
+ }
- # reassemble and return.
- return join $sep, @quoted;
+ # parts containing * are naturally unquoted
+ return join( $_[0]->{name_sep}||'', map
+ { $_ eq '*' ? $_ : $l . $_ . $r }
+ ( $_[0]->{name_sep} ? split (/\Q$_[0]->{name_sep}\E/, $_[1] ) : $_[1] )
+ );
}
# Conversion, if applicable
sub _convert ($) {
- my ($self, $arg) = @_;
+ #my ($self, $arg) = @_;
# LDNOTE : modified the previous implementation below because
# it was not consistent : the first "return" is always an array,
# the second "return" is context-dependent. Anyway, _convert
-# seems always used with just a single argument, so make it a
+# seems always used with just a single argument, so make it a
# scalar function.
# return @_ unless $self->{convert};
# my $conv = $self->_sqlcase($self->{convert});
# my @ret = map { $conv.'('.$_.')' } @_;
# return wantarray ? @ret : $ret[0];
- if ($self->{convert}) {
- my $conv = $self->_sqlcase($self->{convert});
- $arg = $conv.'('.$arg.')';
+ if ($_[0]->{convert}) {
+ return $_[0]->_sqlcase($_[0]->{convert}) .'(' . $_[1] . ')';
}
- return $arg;
+ return $_[1];
}
# And bindtype
sub _bindtype (@) {
- my $self = shift;
- my($col, @vals) = @_;
+ #my ($self, $col, @vals) = @_;
- #LDNOTE : changed original implementation below because it did not make
+ #LDNOTE : changed original implementation below because it did not make
# sense when bindtype eq 'columns' and @vals > 1.
# return $self->{bindtype} eq 'columns' ? [ $col, @vals ] : @vals;
- return $self->{bindtype} eq 'columns' ? map {[$col, $_]} @vals : @vals;
+ # called often - tighten code
+ return $_[0]->{bindtype} eq 'columns'
+ ? map {[$_[1], $_]} @_[2 .. $#_]
+ : @_[2 .. $#_]
+ ;
}
# Dies if any element of @bind is not in [colname => value] format
# if bindtype is 'columns'.
sub _assert_bindval_matches_bindtype {
- my ($self, @bind) = @_;
-
+# my ($self, @bind) = @_;
+ my $self = shift;
if ($self->{bindtype} eq 'columns') {
- foreach my $val (@bind) {
- if (!defined $val || ref($val) ne 'ARRAY' || @$val != 2) {
- die "bindtype 'columns' selected, you need to pass: [column_name => bind_value]"
+ for (@_) {
+ if (!defined $_ || ref($_) ne 'ARRAY' || @$_ != 2) {
+ puke "bindtype 'columns' selected, you need to pass: [column_name => bind_value]"
}
}
}
# Fix SQL case, if so requested
sub _sqlcase {
- my $self = shift;
-
# LDNOTE: if $self->{case} is true, then it contains 'lower', so we
# don't touch the argument ... crooked logic, but let's not change it!
- return $self->{case} ? $_[0] : uc($_[0]);
+ return $_[0]->{case} ? $_[1] : uc($_[1]);
}
sub _refkind {
my ($self, $data) = @_;
- my $suffix = '';
- my $ref;
- my $n_steps = 0;
- while (1) {
- # blessed objects are treated like scalars
- $ref = (Scalar::Util::blessed $data) ? '' : ref $data;
- $n_steps += 1 if $ref;
- last if $ref ne 'REF';
- $data = $$data;
- }
+ return 'UNDEF' unless defined $data;
- my $base = $ref || (defined $data ? 'SCALAR' : 'UNDEF');
+ # blessed objects are treated like scalars
+ my $ref = (Scalar::Util::blessed $data) ? '' : ref $data;
- return $base . ('REF' x $n_steps);
-}
+ return 'SCALAR' unless $ref;
+ my $n_steps = 1;
+ while ($ref eq 'REF') {
+ $data = $$data;
+ $ref = (Scalar::Util::blessed $data) ? '' : ref $data;
+ $n_steps++ if $ref;
+ }
+ return ($ref||'SCALAR') . ('REF' x $n_steps);
+}
sub _try_refkind {
my ($self, $data) = @_;
my @try = ($self->_refkind($data));
push @try, 'SCALAR_or_UNDEF' if $try[0] eq 'SCALAR' || $try[0] eq 'UNDEF';
push @try, 'FALLBACK';
- return @try;
+ return \@try;
}
sub _METHOD_FOR_refkind {
my ($self, $meth_prefix, $data) = @_;
my $method;
- for ($self->_try_refkind($data)) {
+ for (@{$self->_try_refkind($data)}) {
$method = $self->can($meth_prefix."_".$_)
and last;
}
my ($self, $data, $dispatch_table) = @_;
my $coderef;
- for ($self->_try_refkind($data)) {
+ for (@{$self->_try_refkind($data)}) {
$coderef = $dispatch_table->{$_}
and last;
}
foreach my $k ( sort keys %$data ) {
my $v = $data->{$k};
$self->_SWITCH_refkind($v, {
- ARRAYREF => sub {
+ ARRAYREF => sub {
if ($self->{array_datatypes}) { # array datatype
push @all_bind, $self->_bindtype($k, $v);
}
} elsif ($r eq 'SCALAR') {
# literal SQL without bind
push @sqlq, "$label = $$v";
- } else {
+ } else {
push @sqlq, "$label = ?";
push @sqlv, $self->_bindtype($k, $v);
}
} elsif ($r eq 'SCALAR') { # literal SQL without bind
# embedded literal SQL
push @sqlq, $$v;
- } else {
+ } else {
push @sqlq, '?';
push @sqlv, $v;
}
If your database has array types (like for example Postgres),
activate the special option C<< array_datatypes => 1 >>
-when creating the C<SQL::Abstract> object.
+when creating the C<SQL::Abstract> object.
Then you may use an arrayref to insert and update database array types:
my $sql = SQL::Abstract->new(array_datatypes => 1);
my %data = (
planets => [qw/Mercury Venus Earth Mars/]
);
-
+
my($stmt, @bind) = $sql->insert('solar_system', \%data);
This results in:
my %data = (
name => 'Bill',
date_entered => \["to_date(?,'MM/DD/YYYY')", "03/02/2003"],
- );
+ );
The first value in the array is the actual SQL. Any other values are
optional and would be included in the bind values array. This gives
my($stmt, @bind) = $sql->insert('people', \%data);
- $stmt = "INSERT INTO people (name, date_entered)
+ $stmt = "INSERT INTO people (name, date_entered)
VALUES (?, to_date(?,'MM/DD/YYYY'))";
@bind = ('Bill', '03/02/2003');
The functions are simple. There's one for each major SQL operation,
and a constructor you use first. The arguments are specified in a
-similar order to each function (table, then fields, then a where
+similar order to each function (table, then fields, then a where
clause) to try and simplify things.
array of the form:
@where = (
- event_date => {'>=', '2/13/99'},
- event_date => {'<=', '4/24/03'},
+ event_date => {'>=', '2/13/99'},
+ event_date => {'<=', '4/24/03'},
);
will generate SQL like this:
The logic can also be changed locally by inserting
a modifier in front of an arrayref :
- @where = (-and => [event_date => {'>=', '2/13/99'},
+ @where = (-and => [event_date => {'>=', '2/13/99'},
event_date => {'<=', '4/24/03'} ]);
See the L</"WHERE CLAUSES"> section for explanations.
=item quote_char
This is the character that a table or column name will be quoted
-with. By default this is an empty string, but you could set it to
+with. By default this is an empty string, but you could set it to
the character C<`>, to generate SQL like this:
SELECT `a_field` FROM `a_table` WHERE `some_field` LIKE '%someval%'
SELECT [a_field] FROM [a_table] WHERE [some_field] LIKE '%someval%'
-Quoting is useful if you have tables or columns names that are reserved
+Quoting is useful if you have tables or columns names that are reserved
words in your database's SQL dialect.
=item name_sep
SELECT `table`.`one_field` FROM `table` WHERE `table`.`other_field` = 1
+=item injection_guard
+
+A regular expression C<qr/.../> that is applied to any C<-function> and unquoted
+column name specified in a query structure. This is a safety mechanism to avoid
+injection attacks when mishandling user input e.g.:
+
+ my %condition_as_column_value_pairs = get_values_from_user();
+ $sqla->select( ... , \%condition_as_column_value_pairs );
+
+If the expression matches an exception is thrown. Note that literal SQL
+supplied via C<\'...'> or C<\['...']> is B<not> checked in any way.
+
+Defaults to checking for C<;> and the C<GO> keyword (TransactSQL)
+
=item array_datatypes
-When this option is true, arrayrefs in INSERT or UPDATE are
-interpreted as array datatypes and are passed directly
+When this option is true, arrayrefs in INSERT or UPDATE are
+interpreted as array datatypes and are passed directly
to the DBI layer.
When this option is false, arrayrefs are interpreted
as literal SQL, just like refs to arrayrefs
=item special_ops
-Takes a reference to a list of "special operators"
+Takes a reference to a list of "special operators"
to extend the syntax understood by L<SQL::Abstract>.
See section L</"SPECIAL OPERATORS"> for details.
=item unary_ops
-Takes a reference to a list of "unary operators"
+Takes a reference to a list of "unary operators"
to extend the syntax understood by L<SQL::Abstract>.
See section L</"UNARY OPERATORS"> for details.
=head2 select($source, $fields, $where, $order)
-This returns a SQL SELECT statement and associated list of bind values, as
+This returns a SQL SELECT statement and associated list of bind values, as
specified by the arguments :
=over
=item $source
-Specification of the 'FROM' part of the statement.
+Specification of the 'FROM' part of the statement.
The argument can be either a plain scalar (interpreted as a table
name, will be quoted), or an arrayref (interpreted as a list
of table names, joined by commas, quoted), or a scalarref
=item $fields
-Specification of the list of fields to retrieve from
+Specification of the list of fields to retrieve from
the source.
The argument can be either an arrayref (interpreted as a list
-of field names, will be joined by commas and quoted), or a
+of field names, will be joined by commas and quoted), or a
plain scalar (literal SQL, not quoted).
Please observe that this API is not as flexible as for
the first argument C<$table>, for backwards compatibility reasons.
Optional argument to specify the WHERE part of the query.
The argument is most often a hashref, but can also be
-an arrayref or plain scalar --
+an arrayref or plain scalar --
see section L<WHERE clause|/"WHERE CLAUSES"> for details.
=item $order
Optional argument to specify the ORDER BY part of the query.
-The argument can be a scalar, a hashref or an arrayref
+The argument can be a scalar, a hashref or an arrayref
-- see section L<ORDER BY clause|/"ORDER BY CLAUSES">
for details.
You get the idea. Strings get their case twiddled, but everything
else remains verbatim.
-
-
-
=head1 WHERE CLAUSES
=head2 Introduction
);
This simple code will create the following:
-
+
$stmt = "WHERE user = ? AND ( status = ? OR status = ? OR status = ? )";
@bind = ('nwiger', 'assigned', 'in-progress', 'pending');
-A field associated to an empty arrayref will be considered a
+A field associated to an empty arrayref will be considered a
logical false and will generate 0=1.
+=head2 Tests for NULL values
+
+If the value part is C<undef> then this is converted to SQL <IS NULL>
+
+ my %where = (
+ user => 'nwiger',
+ status => undef,
+ );
+
+becomes:
+
+ $stmt = "WHERE user = ? AND status IS NULL";
+ @bind = ('nwiger');
+
+To test if a column IS NOT NULL:
+
+ my %where = (
+ user => 'nwiger',
+ status => { '!=', undef },
+ );
+
=head2 Specific comparison operators
If you want to specify a different type of operator for your comparison,
my %where => (
user => 'nwiger',
- priority => [ {'=', 2}, {'!=', 1} ]
+ priority => [ { '=', 2 }, { '>', 5 } ]
);
Which would generate:
- $stmt = "WHERE user = ? AND priority = ? OR priority != ?";
- @bind = ('nwiger', '2', '1');
+ $stmt = "WHERE ( priority = ? OR priority > ? ) AND user = ?";
+ @bind = ('2', '5', 'nwiger');
If you want to include literal SQL (with or without bind values), just use a
scalar reference or array reference as the value:
As the second C<!=> key will obliterate the first. The solution
is to use the special C<-modifier> form inside an arrayref:
- priority => [ -and => {'!=', 2},
+ priority => [ -and => {'!=', 2},
{'!=', 1} ]
$stmt = "WHERE status = ? AND reportid IN (?,?,?)";
@bind = ('completed', '567', '2335', '2');
-The reverse operator C<-not_in> generates SQL C<NOT IN> and is used in
+The reverse operator C<-not_in> generates SQL C<NOT IN> and is used in
the same way.
If the argument to C<-in> is an empty array, 'sqlfalse' is generated
start0 => { -between => [ 1, 2 ] },
start1 => { -between => \["? AND ?", 1, 2] },
start2 => { -between => \"lower(x) AND upper(y)" },
- start3 => { -between => [
+ start3 => { -between => [
\"lower(x)",
\["upper(?)", 'stuff' ],
] },
@bind = (1, 2, 1, 2, 'stuff');
-These are the two builtin "special operators"; but the
+These are the two builtin "special operators"; but the
list can be expanded : see section L</"SPECIAL OPERATORS"> below.
=head2 Unary operators: bool
If you wish to test against boolean columns or functions within your
database you can use the C<-bool> and C<-not_bool> operators. For
example to test the column C<is_user> being true and the column
-<is_enabled> being false you would use:-
+C<is_enabled> being false you would use:-
my %where = (
-bool => 'is_user',
@bind = ('nwiger', 'pending', 'dispatched', 'robot', 'unassigned');
-There is also a special C<-nest>
-operator which adds an additional set of parens, to create a subquery.
-For example, to get something like this:
-
- $stmt = "WHERE user = ? AND ( workhrs > ? OR geo = ? )";
- @bind = ('nwiger', '20', 'ASIA');
-
-You would do:
-
- my %where = (
- user => 'nwiger',
- -nest => [ workhrs => {'>', 20}, geo => 'ASIA' ],
- );
-
-
-Finally, clauses in hashrefs or arrayrefs can be
-prefixed with an C<-and> or C<-or> to change the logic
-inside :
+Clauses in hashrefs or arrayrefs can be prefixed with an C<-and> or C<-or>
+to change the logic inside :
my @where = (
-and => [
user => 'nwiger',
- -nest => [
- -and => [workhrs => {'>', 20}, geo => 'ASIA' ],
- -and => [workhrs => {'<', 50}, geo => 'EURO' ]
+ [
+ -and => [ workhrs => {'>', 20}, geo => 'ASIA' ],
+ -or => { workhrs => {'<', 50}, geo => 'EURO' },
],
],
);
That would yield:
- WHERE ( user = ? AND
- ( ( workhrs > ? AND geo = ? )
- OR ( workhrs < ? AND geo = ? ) ) )
-
+ WHERE ( user = ? AND (
+ ( workhrs > ? AND geo = ? )
+ OR ( workhrs < ? OR geo = ? )
+ ) )
-=head2 Algebraic inconsistency, for historical reasons
+=head3 Algebraic inconsistency, for historical reasons
C<Important note>: when connecting several conditions, the C<-and->|C<-or>
operator goes C<outside> of the nested structure; whereas when connecting
yielding
- WHERE ( ( ( a = ? AND b = ? )
- OR ( c = ? OR d = ? )
+ WHERE ( ( ( a = ? AND b = ? )
+ OR ( c = ? OR d = ? )
OR ( e LIKE ? AND e LIKE ? ) ) )
This difference in syntax is unfortunate but must be preserved for
historical reasons. So be careful : the two examples below would
seem algebraically equivalent, but they are not
- {col => [-and => {-like => 'foo%'}, {-like => '%bar'}]}
+ {col => [-and => {-like => 'foo%'}, {-like => '%bar'}]}
# yields : WHERE ( ( col LIKE ? AND col LIKE ? ) )
- [-and => {col => {-like => 'foo%'}, {col => {-like => '%bar'}}]]
+ [-and => {col => {-like => 'foo%'}, {col => {-like => '%bar'}}]]
# yields : WHERE ( ( col LIKE ? OR col LIKE ? ) )
-=head2 Literal SQL
+=head2 Literal SQL and value type operators
-Finally, sometimes only literal SQL will do. If you want to include
-literal SQL verbatim, you can specify it as a scalar reference, namely:
+The basic premise of SQL::Abstract is that in WHERE specifications the "left
+side" is a column name and the "right side" is a value (normally rendered as
+a placeholder). This holds true for both hashrefs and arrayref pairs as you
+see in the L</WHERE CLAUSES> examples above. Sometimes it is necessary to
+alter this behavior. There are several ways of doing so.
+
+=head3 -ident
+
+This is a virtual operator that signals the string to its right side is an
+identifier (a column name) and not a value. For example to compare two
+columns you would write:
- my $inn = 'is Not Null';
my %where = (
priority => { '<', 2 },
- requestor => \$inn
+ requestor => { -ident => 'submitter' },
);
-This would create:
+which creates:
- $stmt = "WHERE priority < ? AND requestor is Not Null";
+ $stmt = "WHERE priority < ? AND requestor = submitter";
@bind = ('2');
-Note that in this example, you only get one bind parameter back, since
-the verbatim SQL is passed as part of the statement.
+If you are maintaining legacy code you may see a different construct as
+described in L</Deprecated usage of Literal SQL>, please use C<-ident> in new
+code.
+
+=head3 -value
-Of course, just to prove a point, the above can also be accomplished
-with this:
+This is a virtual operator that signals that the construct to its right side
+is a value to be passed to DBI. This is for example necessary when you want
+to write a where clause against an array (for RDBMS that support such
+datatypes). For example:
my %where = (
- priority => { '<', 2 },
- requestor => { '!=', undef },
+ array => { -value => [1, 2, 3] }
);
+will result in:
-TMTOWTDI
+ $stmt = 'WHERE array = ?';
+ @bind = ([1, 2, 3]);
-Conditions on boolean columns can be expressed in the same way, passing
-a reference to an empty string, however using liternal SQL in this way
-is deprecated - the preferred method is to use the boolean operators -
-see L</"Unary operators: bool"> :
+Note that if you were to simply say:
my %where = (
- priority => { '<', 2 },
- is_ready => \"";
+ array => [1, 2, 3]
);
-which yields
+the result would porbably be not what you wanted:
- $stmt = "WHERE priority < ? AND is_ready";
- @bind = ('2');
+ $stmt = 'WHERE array = ? OR array = ? OR array = ?';
+ @bind = (1, 2, 3);
+
+=head3 Literal SQL
+
+Finally, sometimes only literal SQL will do. To include a random snippet
+of SQL verbatim, you specify it as a scalar reference. Consider this only
+as a last resort. Usually there is a better way. For example:
+ my %where = (
+ priority => { '<', 2 },
+ requestor => { -in => \'(SELECT name FROM hitmen)' },
+ );
-=head2 Literal SQL with placeholders and bind values (subqueries)
+Would create:
+
+ $stmt = "WHERE priority < ? AND requestor IN (SELECT name FROM hitmen)"
+ @bind = (2);
+
+Note that in this example, you only get one bind parameter back, since
+the verbatim SQL is passed as part of the statement.
+
+=head4 CAVEAT
+
+ Never use untrusted input as a literal SQL argument - this is a massive
+ security risk (there is no way to check literal snippets for SQL
+ injections and other nastyness). If you need to deal with untrusted input
+ use literal SQL with placeholders as described next.
+
+=head3 Literal SQL with placeholders and bind values (subqueries)
If the literal SQL to be inserted has placeholders and bind values,
use a reference to an arrayref (yes this is a double reference --
This yields :
- $stmt = "WHERE (foo = ? AND bar IN (SELECT c1 FROM t1
+ $stmt = "WHERE (foo = ? AND bar IN (SELECT c1 FROM t1
WHERE c2 < ? AND c3 LIKE ?))";
@bind = (1234, 100, "foo%");
-Other subquery operators, like for example C<"E<gt> ALL"> or C<"NOT IN">,
+Other subquery operators, like for example C<"E<gt> ALL"> or C<"NOT IN">,
are expressed in the same way. Of course the C<$sub_stmt> and
-its associated bind values can be generated through a former call
+its associated bind values can be generated through a former call
to C<select()> :
my ($sub_stmt, @sub_bind)
- = $sql->select("t1", "c1", {c2 => {"<" => 100},
+ = $sql->select("t1", "c1", {c2 => {"<" => 100},
c3 => {-like => "foo%"}});
my %where = (
foo => 1234,
);
In the examples above, the subquery was used as an operator on a column;
-but the same principle also applies for a clause within the main C<%where>
+but the same principle also applies for a clause within the main C<%where>
hash, like an EXISTS subquery :
- my ($sub_stmt, @sub_bind)
+ my ($sub_stmt, @sub_bind)
= $sql->select("t1", "*", {c1 => 1, c2 => \"> t0.c0"});
- my %where = (
+ my %where = ( -and => [
foo => 1234,
- -nest => \["EXISTS ($sub_stmt)" => @sub_bind],
- );
+ \["EXISTS ($sub_stmt)" => @sub_bind],
+ ]);
which yields
- $stmt = "WHERE (foo = ? AND EXISTS (SELECT * FROM t1
+ $stmt = "WHERE (foo = ? AND EXISTS (SELECT * FROM t1
WHERE c1 = ? AND c2 > t0.c0))";
@bind = (1234, 1);
-Observe that the condition on C<c2> in the subquery refers to
-column C<t0.c0> of the main query : this is I<not> a bind
-value, so we have to express it through a scalar ref.
+Observe that the condition on C<c2> in the subquery refers to
+column C<t0.c0> of the main query : this is I<not> a bind
+value, so we have to express it through a scalar ref.
Writing C<< c2 => {">" => "t0.c0"} >> would have generated
C<< c2 > ? >> with bind value C<"t0.c0"> ... not exactly
what we wanted here.
-Another use of the subquery technique is when some SQL clauses need
-parentheses, as it often occurs with some proprietary SQL extensions
-like for example fulltext expressions, geospatial expressions,
-NATIVE clauses, etc. Here is an example of a fulltext query in MySQL :
-
- my %where = (
- -nest => \["MATCH (col1, col2) AGAINST (?)" => qw/apples/]
- );
-
Finally, here is an example where a subquery is used
for expressing unary negation:
- my ($sub_stmt, @sub_bind)
+ my ($sub_stmt, @sub_bind)
= $sql->where({age => [{"<" => 10}, {">" => 20}]});
$sub_stmt =~ s/^ where //i; # don't want "WHERE" in the subclause
my %where = (
lname => {like => '%son%'},
- -nest => \["NOT ($sub_stmt)" => @sub_bind],
+ \["NOT ($sub_stmt)" => @sub_bind],
);
This yields
$stmt = "lname LIKE ? AND NOT ( age < ? OR age > ? )"
@bind = ('%son%', 10, 20)
+=head3 Deprecated usage of Literal SQL
+
+Below are some examples of archaic use of literal SQL. It is shown only as
+reference for those who deal with legacy code. Each example has a much
+better, cleaner and safer alternative that users should opt for in new code.
+
+=over
+
+=item *
+
+ my %where = ( requestor => \'IS NOT NULL' )
+
+ $stmt = "WHERE requestor IS NOT NULL"
+
+This used to be the way of generating NULL comparisons, before the handling
+of C<undef> got formalized. For new code please use the superior syntax as
+described in L</Tests for NULL values>.
+
+=item *
+ my %where = ( requestor => \'= submitter' )
+
+ $stmt = "WHERE requestor = submitter"
+
+This used to be the only way to compare columns. Use the superior L</-ident>
+method for all new code. For example an identifier declared in such a way
+will be properly quoted if L</quote_char> is properly set, while the legacy
+form will remain as supplied.
+
+=item *
+
+ my %where = ( is_ready => \"", completed => { '>', '2012-12-21' } )
+
+ $stmt = "WHERE completed > ? AND is_ready"
+ @bind = ('2012-12-21')
+
+Using an empty string literal used to be the only way to express a boolean.
+For all new code please use the much more readable
+L<-bool|/Unary operators: bool> operator.
+
+=back
=head2 Conclusion
dynamically-generating SQL and could just hardwire it into your
script.
-
-
-
=head1 ORDER BY CLAUSES
-Some functions take an order by clause. This can either be a scalar (just a
+Some functions take an order by clause. This can either be a scalar (just a
column name,) a hash of C<< { -desc => 'col' } >> or C<< { -asc => 'col' } >>,
or an array of either of the two previous forms. Examples:
|
['colA', {-asc => 'colB'}] | ORDER BY colA, colB ASC
|
- { -asc => [qw/colA colB] } | ORDER BY colA ASC, colB ASC
+ { -asc => [qw/colA colB/] } | ORDER BY colA ASC, colB ASC
|
[ |
{ -asc => 'colA' }, | ORDER BY colA ASC, colB DESC,
},
]);
-A "special operator" is a SQL syntactic clause that can be
+A "special operator" is a SQL syntactic clause that can be
applied to a field, instead of a usual binary operator.
-For example :
+For example :
WHERE field IN (?, ?, ?)
WHERE field BETWEEN ? AND ?
=back
-For example, here is an implementation
+For example, here is an implementation
of the MATCH .. AGAINST syntax for MySQL
my $sqlmaker = SQL::Abstract->new(special_ops => [
-
+
# special op for MySql MATCH (field) AGAINST(word1, word2, ...)
- {regex => qr/^match$/i,
+ {regex => qr/^match$/i,
handler => sub {
my ($self, $field, $op, $arg) = @_;
$arg = [$arg] if not ref $arg;
return ($sql, @bind);
}
},
-
+
]);
},
]);
-A "unary operator" is a SQL syntactic clause that can be
+A "unary operator" is a SQL syntactic clause that can be
applied to a field - the operator goes before the field
You can write your own operator handlers - supply a C<unary_ops>
around. On subsequent queries, simply use the C<values> function provided
by this module to return your values in the correct order.
+However this depends on the values having the same type - if, for
+example, the values of a where clause may either have values
+(resulting in sql of the form C<column = ?> with a single bind
+value), or alternatively the values might be C<undef> (resulting in
+sql of the form C<column IS NULL> with no bind value) then the
+caching technique suggested will not work.
=head1 FORMBUILDER
table, the actual query script can be extremely simplistic.
If you're B<REALLY> lazy (I am), check out C<HTML::QuickTable> for
-a fast interface to returning and formatting data. I frequently
+a fast interface to returning and formatting data. I frequently
use these three modules together to write complex database query
apps in under 50 lines.
+=head1 REPO
+
+=over
+
+=item * gitweb: L<http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/SQL-Abstract.git>
+
+=item * git: L<git://git.shadowcat.co.uk/dbsrgits/SQL-Abstract.git>
+
+=back
=head1 CHANGES
Version 1.50 was a major internal refactoring of C<SQL::Abstract>.
Great care has been taken to preserve the I<published> behavior
documented in previous versions in the 1.* family; however,
-some features that were previously undocumented, or behaved
+some features that were previously undocumented, or behaved
differently from the documentation, had to be changed in order
to clarify the semantics. Hence, client code that was relying
-on some dark areas of C<SQL::Abstract> v1.*
+on some dark areas of C<SQL::Abstract> v1.*
B<might behave differently> in v1.50.
The main changes are :
=over
-=item *
+=item *
support for literal SQL through the C<< \ [$sql, bind] >> syntax.
optional support for L<array datatypes|/"Inserting and Updating Arrays">
-=item *
+=item *
defensive programming : check arguments
fixed semantics of _bindtype on array args
-=item *
+=item *
dropped the C<_anoncopy> of the %where tree. No longer necessary,
we just avoid shifting arrays within that tree.
=back
-
-
=head1 ACKNOWLEDGEMENTS
There are a number of individuals that have really helped out with
this module. Unfortunately, most of them submitted bugs via CPAN
so I have no idea who they are! But the people I do know are:
- Ash Berlin (order_by hash term support)
+ Ash Berlin (order_by hash term support)
Matt Trout (DBIx::Class support)
Mark Stosberg (benchmarking)
Chas Owens (initial "IN" operator support)
Mike Fragassi (enhancements to "BETWEEN" and "LIKE")
Dan Kubb (support for "quote_char" and "name_sep")
Guillermo Roditi (patch to cleanup "IN" and "BETWEEN", fix and tests for _order_by)
- Laurent Dami (internal refactoring, multiple -nest, extensible list of special operators, literal SQL)
+ Laurent Dami (internal refactoring, extensible list of special operators, literal SQL)
Norbert Buchmuller (support for literal SQL in hashpair, misc. fixes & tests)
Peter Rabbitson (rewrite of SQLA::Test, misc. fixes & tests)
Oliver Charles (support for "RETURNING" after "INSERT")
projects / dbsrgits/SQL-Abstract.git / blobdiff