Applied patch from Zbigniew Lukasiak (with slight modifications) to accept "col ...

[scpubgit/Q-Branch.git] / lib / SQL / Abstract.pm

diff --git a/lib/SQL/Abstract.pm b/lib/SQL/Abstract.pm
index 848a60f..fcee0f9 100644 (file)
--- a/lib/SQL/Abstract.pm
+++ b/lib/SQL/Abstract.pm
@@ -161,6 +161,12 @@ sub _insert_ARRAYREF { # just generate values(?,?) part (no list of fields)
       },
 
       # 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;
@@ -536,6 +542,14 @@ sub _where_hashpair_HASHREF {
                             $$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' :
@@ -683,25 +697,36 @@ sub _where_field_IN {
   # 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);
 }
 
 
@@ -1607,6 +1632,19 @@ Which would generate:
     $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
 
@@ -2077,6 +2115,10 @@ 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 *
@@ -2139,6 +2181,7 @@ so I have no idea who they are! But the people I do know are:
     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!