Omnipotent 'between'

[dbsrgits/SQL-Abstract.git] / lib / SQL / Abstract.pm

diff --git a/lib/SQL/Abstract.pm b/lib/SQL/Abstract.pm
index 3ad246b..7740f79 100644 (file)
--- a/lib/SQL/Abstract.pm
+++ b/lib/SQL/Abstract.pm
@@ -15,7 +15,7 @@ use Scalar::Util qw/blessed/;
 # GLOBALS
 #======================================================================
 
-our $VERSION  = '1.56';
+our $VERSION  = '1.58';
 
 # This would confuse some packagers
 #$VERSION      = eval $VERSION; # numify for warning-free dev releases
@@ -708,11 +708,14 @@ sub _where_field_op_ARRAYREF {
   my @vals = @$vals;  #always work on a copy
 
   if(@vals) {
-    $self->_debug("ARRAY($vals) means multiple elements: [ @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 ($vals[0] =~ /^ - ( AND|OR ) $/ix) {
+    if (defined $vals[0] && $vals[0] =~ /^ - ( AND|OR ) $/ix) {
       $logic = uc $1;
       shift @vals;
     }
@@ -815,38 +818,51 @@ sub _where_UNDEF {
 sub _where_field_BETWEEN {
   my ($self, $k, $op, $vals) = @_;
 
-  (ref $vals eq 'ARRAY' && @$vals == 2) or 
-  (ref $vals eq 'REF' && (@$$vals == 1 || @$$vals == 2 || @$$vals == 3))
-    or puke "special op 'between' requires an arrayref of two values (or a scalarref or arrayrefref for literal SQL)";
-
-  my ($clause, @bind, $label, $and, $placeholder);
+  my ($label, $and, $placeholder);
   $label       = $self->_convert($self->_quote($k));
   $and         = ' ' . $self->_sqlcase('and') . ' ';
   $placeholder = $self->_convert('?');
   $op               = $self->_sqlcase($op);
 
-  if (ref $vals eq 'REF') {
-    ($clause, @bind) = @$$vals;
-  }
-  else {
-    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), ());
-         },
-      });
-      push @all_sql, $sql;
-      push @all_bind, @bind;
-    }
+  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";
+    },
+  });
 
-    $clause = (join $and, @all_sql);
-    @bind = $self->_bindtype($k, @all_bind);
-  }
   my $sql = "( $label $op $clause )";
   return ($sql, @bind)
 }
@@ -892,8 +908,6 @@ sub _where_field_IN {
 }
 
 
-
-
 #======================================================================
 # ORDER BY
 #======================================================================
@@ -1977,8 +1991,23 @@ example to test the column C<is_user> being true and the column
 
 Would give you:
 
-    WHERE is_user AND NOT is_enabledmv 
+    WHERE is_user AND NOT is_enabled
+
+If a more complex combination is required, testing more conditions,
+then you should use the and/or operators:-
+
+    my %where  = (
+        -and           => [
+            -bool      => 'one',
+            -bool      => 'two',
+            -bool      => 'three',
+            -not_bool  => 'four',
+        ],
+    );
+
+Would give you:
 
+    WHERE one AND two AND three AND NOT four
 
 
 =head2 Nested conditions, -and/-or prefixes
@@ -2101,8 +2130,10 @@ with this:
 
 TMTOWTDI.
 
-Conditions on boolean columns can be expressed in the 
-same way, passing a reference to an empty string :
+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"> :
 
     my %where = (
         priority  => { '<', 2 },