use Carp;
use strict;
use warnings;
-use List::Util qw/first/;
+use List::Util qw/first/;
+use Scalar::Util qw/blessed/;
#======================================================================
# GLOBALS
ARRAYREF => sub {
if ($self->{array_datatypes}) { # if array datatype are activated
push @values, '?';
+ push @all_bind, $v;
}
else { # else literal SQL with bind
my ($sql, @bind) = @$v;
},
# 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";
+ push @values, '?';
+ push @all_bind, $v;
+ },
SCALARREF => sub { # literal SQL without bind
push @values, $$v;
# dispatch on appropriate method according to refkind of $where
my $method = $self->_METHOD_FOR_refkind("_where", $where);
- $self->$method($where, $logic);
+
+
+ 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;
}
if ($special_op) {
($sql, @bind) = $special_op->{handler}->($self, $k, $op, $val);
}
+ else {
+ $self->_SWITCH_refkind($val, {
- # CASE: col => {op => \@vals}
- elsif (ref $val eq 'ARRAY') {
- ($sql, @bind) = $self->_where_field_op_ARRAYREF($k, $op, $val);
- }
-
- # CASE: col => {op => undef} : sql "IS (NOT)? NULL"
- elsif (! defined($val)) {
- my $is = ($op =~ $self->{equality_op}) ? 'is' :
- ($op =~ $self->{inequality_op}) ? 'is not' :
- puke "unexpected operator '$op' with undef operand";
- $sql = $self->_quote($k) . $self->_sqlcase(" $is null");
- }
+ ARRAYREF => sub { # CASE: col => {op => \@vals}
+ ($sql, @bind) = $self->_where_field_op_ARRAYREF($k, $op, $val);
+ },
- # CASE: col => {op => $scalar}
- else {
- $sql = join ' ', $self->_convert($self->_quote($k)),
- $self->_sqlcase($op),
- $self->_convert('?');
- @bind = $self->_bindtype($k, $val);
+ SCALARREF => sub { # CASE: col => {op => \$scalar}
+ $sql = join ' ', $self->_convert($self->_quote($k)),
+ $self->_sqlcase($op),
+ $$val;
+ },
+
+ ARRAYREFREF => sub { # CASE: col => {op => \[$sql, @bind]}
+ my ($sub_sql, @sub_bind) = @$$val;
+ $sql = join ' ', $self->_convert($self->_quote($k)),
+ $self->_sqlcase($op),
+ $sub_sql;
+ @bind = $self->_bindtype($k, @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 '$op' with undef operand";
+ $sql = $self->_quote($k) . $self->_sqlcase(" $is null");
+ },
+
+ FALLBACK => sub { # CASE: col => {op => $scalar}
+ $sql = join ' ', $self->_convert($self->_quote($k)),
+ $self->_sqlcase($op),
+ $self->_convert('?');
+ @bind = $self->_bindtype($k, $val);
+ },
+ });
}
push @all_sql, $sql;
# backwards compatibility : if scalar, force into an arrayref
$vals = [$vals] if defined $vals && ! ref $vals;
- ref $vals eq 'ARRAY'
- or puke "special op 'in' requires an arrayref";
-
my ($label) = $self->_convert($self->_quote($k));
my ($placeholder) = $self->_convert('?');
- my $and = $self->_sqlcase('and');
$op = $self->_sqlcase($op);
- if (@$vals) { # nonempty list
- my $placeholders = join ", ", (($placeholder) x @$vals);
- my $sql = "$label $op ( $placeholders )";
- my @bind = $self->_bindtype($k, @$vals);
+ 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);
- }
+ 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);
+ }
+ },
+
+ ARRAYREFREF => sub { # literal SQL with bind
+ my ($sql, @bind) = @$$vals;
+ return ("$label $op ( $sql )", @bind);
+ },
+
+ FALLBACK => sub {
+ puke "special op 'in' requires an arrayref (or arrayref-ref)";
+ },
+ });
+
+ return ($sql, @bind);
}
ARRAYREF => sub {
map {$self->_SWITCH_refkind($_, {
SCALAR => sub {$self->_quote($_)},
+ UNDEF => sub {},
SCALARREF => sub {$$_}, # literal SQL, no quoting
HASHREF => sub {$self->_order_by_hash($_)}
}) } @$arg;
my ($self, $data) = @_;
my $suffix = '';
my $ref;
+ my $n_steps = 0;
- # $suffix = 'REF' x (length of ref chain, i. e. \\[] is REFREFREF)
while (1) {
- $suffix .= 'REF';
- $ref = ref $data;
- last if $ref ne 'REF';
+ # blessed objects are treated like scalars
+ $ref = (blessed $data) ? '' : ref $data;
+ $n_steps += 1 if $ref;
+ last if $ref ne 'REF';
$data = $$data;
}
- return $ref ? $ref.$suffix :
- defined $data ? 'SCALAR' :
- 'UNDEF';
+ my $base = $ref || (defined $data ? 'SCALAR' : 'UNDEF');
+
+ return $base . ('REF' x $n_steps);
}
+
+
sub _try_refkind {
my ($self, $data) = @_;
my @try = ($self->_refkind($data));
$stmt = "WHERE user = ? AND priority = ? OR priority != ?";
@bind = ('nwiger', '2', '1');
+If you want to include literal SQL (with or without bind values), just use a
+scalar reference or array reference as the value:
+
+ my %where = (
+ date_entered => { '>' => \["to_date(?, 'MM/DD/YYYY')", "11/26/2008"] },
+ date_expires => { '<' => \"now()" }
+ );
+
+Which would generate:
+
+ $stmt = "WHERE date_entered > "to_date(?, 'MM/DD/YYYY') AND date_expires < now()";
+ @bind = ('11/26/2008');
+
=head2 Logic and nesting operators
=item *
+support for the { operator => \"..." } construct (to embed literal SQL)
+
+=item *
+
+support for the { operator => \["...", @bind] } construct (to embed literal SQL with bind values)
+
+=item *
+
added -nest1, -nest2 or -nest_1, -nest_2, ...
=item *
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)
+ Norbert Buchmuller (support for literal SQL in hashpair, misc. fixes & tests)
Thanks!