X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FSQL%2FAbstract.pm;h=f8518deeeeb2be216383e8624102ecf18d7bb96c;hb=2af21cb852b6c6c629ce0eda1116fd366cea666b;hp=4b72804ce0ed29e8044dec67a089662ed213c74d;hpb=4efe8199d3482d0b0ec4e326e62c02fa55705fcc;p=dbsrgits%2FSQL-Abstract.git

diff --git a/lib/SQL/Abstract.pm b/lib/SQL/Abstract.pm
index 4b72804..f8518de 100644
--- a/lib/SQL/Abstract.pm
+++ b/lib/SQL/Abstract.pm
@@ -39,26 +39,9 @@ our $AUTOLOAD;
 my @BUILTIN_SPECIAL_OPS = (
   {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
-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/^ 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' },
-);
-
 #======================================================================
 # DEBUGGING AND ERROR REPORTING
 #======================================================================
@@ -172,13 +155,13 @@ sub new {
   $opt{sqlfalse} ||= '0=1';
 
   # special operators
-  $opt{user_special_ops} = [ @{$opt{special_ops} ||= []} ];
+  $opt{special_ops} ||= [];
+
   # regexes are applied in order, thus push after user-defines
   push @{$opt{special_ops}}, @BUILTIN_SPECIAL_OPS;
 
   # unary operators
   $opt{unary_ops} ||= [];
-  push @{$opt{unary_ops}}, @BUILTIN_UNARY_OPS;
 
   # rudimentary sanity-check for user supplied bits treated as functions/operators
   # If a purported  function matches this regular expression, an exception is thrown.
@@ -194,6 +177,13 @@ sub new {
     ^ \s* go \s
   /xmi;
 
+  $opt{node_types} = +{
+    map +("-$_" => '_render_'.$_),
+      qw(op func value bind ident literal)
+  };
+
+  $opt{expand_unary} = {};
+
   return bless \%opt, $class;
 }
 
@@ -242,12 +232,12 @@ sub _returning {
 
   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 $self->_sqlcase(' returning ') . $fieldlist;
+  my ($sql, @bind) = $self->_render_expr(
+    $self->_expand_maybe_list_expr($f, undef, -ident)
+  );
+  return wantarray
+    ? $self->_sqlcase(' returning ') . $sql
+    : ($self->_sqlcase(' returning ').$sql, @bind);
 }
 
 sub _insert_HASHREF { # explicit list of fields and then values
@@ -313,50 +303,33 @@ sub _insert_values {
 sub _insert_value {
   my ($self, $column, $v) = @_;
 
-  my (@values, @all_bind);
-  $self->_SWITCH_refkind($v, {
-
-    ARRAYREF => sub {
-      if ($self->{array_datatypes}) { # if array datatype are activated
-        push @values, '?';
-        push @all_bind, $self->_bindtype($column, $v);
-      }
-      else {                  # else literal SQL with bind
-        my ($sql, @bind) = @$v;
-        $self->_assert_bindval_matches_bindtype(@bind);
-        push @values, $sql;
-        push @all_bind, @bind;
-      }
-    },
-
-    ARRAYREFREF => sub {        # literal SQL with bind
-      my ($sql, @bind) = @${$v};
-      $self->_assert_bindval_matches_bindtype(@bind);
-      push @values, $sql;
-      push @all_bind, @bind;
-    },
-
-    # 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, $self->_bindtype($column, $v);
-    },
-
-    SCALARREF => sub {          # literal SQL without bind
-      push @values, $$v;
-    },
-
-    SCALAR_or_UNDEF => sub {
-      push @values, '?';
-      push @all_bind, $self->_bindtype($column, $v);
-    },
+  return $self->_render_expr(
+    $self->_expand_insert_value($column, $v)
+  );
+}
 
-  });
+sub _expand_insert_value {
+  my ($self, $column, $v) = @_;
 
-  my $sql = join(", ", @values);
-  return ($sql, @all_bind);
+  if (ref($v) eq 'ARRAY') {
+    if ($self->{array_datatypes}) {
+      return +{ -bind => [ $column, $v ] };
+    }
+    my ($sql, @bind) = @$v;
+    $self->_assert_bindval_matches_bindtype(@bind);
+    return +{ -literal => $v };
+  }
+  if (ref($v) eq 'HASH') {
+    if (grep !/^-/, keys %$v) {
+      belch "HASH ref as bind value in insert is not supported";
+      return +{ -bind => [ $column, $v ] };
+    }
+  }
+  if (!defined($v)) {
+    return +{ -bind => [ $column, undef ] };
+  }
+  local our $Cur_Col_Meta = $column;
+  return $self->_expand_expr($v);
 }
 
 
@@ -399,59 +372,33 @@ sub update {
 sub _update_set_values {
   my ($self, $data) = @_;
 
-  my (@set, @all_bind);
-  for my $k (sort keys %$data) {
-    my $v = $data->{$k};
-    my $r = ref $v;
-    my $label = $self->_quote($k);
-
-    $self->_SWITCH_refkind($v, {
-      ARRAYREF => sub {
-        if ($self->{array_datatypes}) { # array datatype
-          push @set, "$label = ?";
-          push @all_bind, $self->_bindtype($k, $v);
-        }
-        else {                          # literal SQL with bind
-          my ($sql, @bind) = @$v;
-          $self->_assert_bindval_matches_bindtype(@bind);
-          push @set, "$label = $sql";
-          push @all_bind, @bind;
-        }
-      },
-      ARRAYREFREF => sub { # literal SQL with bind
-        my ($sql, @bind) = @${$v};
-        $self->_assert_bindval_matches_bindtype(@bind);
-        push @set, "$label = $sql";
-        push @all_bind, @bind;
-      },
-      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 our $Cur_Col_Meta = $k;
-        my ($sql, @bind) = $self->_render_expr(
-          $self->_expand_expr_hashpair($op, $arg)
-        );
-
-        push @set, "$label = $sql";
-        push @all_bind, @bind;
-      },
-      SCALAR_or_UNDEF => sub {
-        push @set, "$label = ?";
-        push @all_bind, $self->_bindtype($k, $v);
-      },
-    });
-  }
-
-  # generate sql
-  my $sql = join ', ', @set;
+  return $self->_render_expr(
+    $self->_expand_update_set_values($data),
+  );
+}
 
-  return ($sql, @all_bind);
+sub _expand_update_set_values {
+  my ($self, $data) = @_;
+  $self->_expand_maybe_list_expr( [
+    map {
+      my ($k, $set) = @$_;
+      $set = { -bind => $_ } unless defined $set;
+      +{ -op => [ '=', { -ident => $k }, $set ] };
+    }
+    map {
+      my $k = $_;
+      my $v = $data->{$k};
+      (ref($v) eq 'ARRAY'
+        ? ($self->{array_datatypes}
+            ? [ $k, +{ -bind => [ $k, $v ] } ]
+            : [ $k, +{ -literal => $v } ])
+        : do {
+            local our $Cur_Col_Meta = $k;
+            [ $k, $self->_expand_expr($v) ]
+          }
+      );
+    } sort keys %$data
+  ] );
 }
 
 # So that subclasses can override UPDATE ... RETURNING separately from
@@ -486,8 +433,10 @@ sub select {
 
 sub _select_fields {
   my ($self, $fields) = @_;
-  return ref $fields eq 'ARRAY' ? join ', ', map { $self->_quote($_) } @$fields
-                                : $fields;
+  return $fields unless ref($fields);
+  return $self->_render_expr(
+    $self->_expand_maybe_list_expr($fields, undef, '-ident')
+  );
 }
 
 #======================================================================
@@ -529,6 +478,8 @@ sub _delete_returning { shift->_returning(@_) }
 sub where {
   my ($self, $where, $order) = @_;
 
+  local $self->{convert_where} = $self->{convert};
+
   # where ?
   my ($sql, @bind) = defined($where)
    ? $self->_recurse_where($where)
@@ -546,7 +497,8 @@ sub where {
 }
 
 sub _expand_expr {
-  my ($self, $expr, $logic) = @_;
+  my ($self, $expr, $logic, $default_scalar_to) = @_;
+  local our $Default_Scalar_To = $default_scalar_to if $default_scalar_to;
   return undef unless defined($expr);
   if (ref($expr) eq 'HASH') {
     if (keys %$expr > 1) {
@@ -590,6 +542,9 @@ sub _expand_expr {
     return +{ -literal => $literal };
   }
   if (!ref($expr) or Scalar::Util::blessed($expr)) {
+    if (my $d = $Default_Scalar_To) {
+      return +{ $d => $expr };
+    }
     if (my $m = our $Cur_Col_Meta) {
       return +{ -bind => [ $m, $expr ] };
     }
@@ -646,14 +601,18 @@ sub _expand_expr_hashpair {
     
       # 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 List::Util::first { $op =~ $_->{regex} } @{$self->{special_ops}};
+      if (my $us = List::Util::first { $op =~ $_->{regex} } @{$self->{unary_ops}}) {
+        return { -op => [ $op, $v ] };
+      }
     }
     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') {
+    if (my $custom = $self->{expand_unary}{$k}) {
+      return $self->$custom($v);
+    }
+    if ($self->{node_types}{$k}) {
       return { $k => $v };
     }
     if (
@@ -679,11 +638,12 @@ sub _expand_expr_hashpair {
     return $self->_expand_expr_hashpair($k => { $self->{cmp} => undef });
   }
   if (!ref($v) or Scalar::Util::blessed($v)) {
+    my $d = our $Default_Scalar_To;
     return +{
       -op => [
         $self->{cmp},
         { -ident => $k },
-        { -bind => [ $k, $v ] }
+        ($d ? { $d => $v } : { -bind => [ $k, $v ] })
       ]
     };
   }
@@ -750,8 +710,8 @@ sub _expand_expr_hashpair {
       ] };
     }
     if ($vk eq 'ident') {
-      if (! defined $vv or ref $vv) {
-        puke "-$vk requires a single plain scalar argument (a quotable identifier)";
+      if (! defined $vv or (ref($vv) and ref($vv) eq 'ARRAY')) {
+        puke "-$vk requires a single plain scalar argument (a quotable identifier) or an arrayref of identifier parts";
       }
       return +{ -op => [
         $self->{cmp},
@@ -787,9 +747,16 @@ sub _expand_expr_hashpair {
         ] };
       }
     }
-    if (my $us = List::Util::first { $vk =~ $_->{regex} } @{$self->{user_special_ops}}) {
+    if (my $us = List::Util::first { $vk =~ $_->{regex} } @{$self->{special_ops}}) {
       return { -op => [ $vk, { -ident => $k }, $vv ] };
     }
+    if (my $us = List::Util::first { $vk =~ $_->{regex} } @{$self->{unary_ops}}) {
+      return { -op => [
+        $self->{cmp},
+        { -ident => $k },
+        { -op => [ $vk, $vv ] }
+      ] };
+    }
     if (ref($vv) eq 'ARRAY') {
       my ($logic, @values) = (
         (defined($vv->[0]) and $vv->[0] =~ /^-(and|or)$/i)
@@ -884,10 +851,8 @@ 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);
+  if (my $meth = $self->{node_types}{$k}) {
+    return $self->$meth($v);
   }
   die "notreached: $k";
 }
@@ -919,60 +884,22 @@ sub _recurse_where {
   }
 }
 
-sub _where_op_IDENT {
-  my $self = shift;
-  my ($op, $rhs) = splice @_, -2;
-  if (! defined $rhs or length ref $rhs) {
-    puke "-$op requires a single plain scalar argument (a quotable identifier)";
-  }
+sub _render_ident {
+  my ($self, $ident) = @_;
 
-  # in case we are called as a top level special op (no '=')
-  my $has_lhs = my $lhs = shift;
-
-  $_ = $self->_convert($self->_quote($_)) for ($lhs, $rhs);
-
-  return $has_lhs
-    ? "$lhs = $rhs"
-    : $rhs
-  ;
+  return $self->_convert($self->_quote($ident));
 }
 
-sub _where_op_VALUE {
-  my $self = shift;
-  my ($op, $rhs) = splice @_, -2;
-
-  # in case we are called as a top level special op (no '=')
-  my $lhs = shift;
-
-  # special-case NULL
-  if (! defined $rhs) {
-    return defined $lhs
-      ? $self->_where_hashpair_HASHREF($lhs, { -is => undef })
-      : undef
-    ;
-  }
+sub _render_value {
+  my ($self, $value) = @_;
 
-  my @bind =
-    $self->_bindtype(
-      (defined $lhs ? $lhs : $self->{_nested_func_lhs}),
-      $rhs,
-    )
-  ;
-
-  return $lhs
-    ? (
-      $self->_convert($self->_quote($lhs)) . ' = ' . $self->_convert('?'),
-      @bind
-    )
-    : (
-      $self->_convert('?'),
-      @bind,
-    )
-  ;
+  return ($self->_convert('?'), $self->_bindtype(undef, $value));
 }
 
-
-my %unop_postfix = map +($_ => 1), 'is null', 'is not null';
+my %unop_postfix = map +($_ => 1),
+  'is null', 'is not null',
+  'asc', 'desc',
+;
 
 my %special = (
   (map +($_ => do {
@@ -1020,20 +947,22 @@ my %special = (
   }), 'in', 'not in'),
 );
 
-sub _where_op_OP {
-  my ($self, undef, $v) = @_;
+sub _render_op {
+  my ($self, $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}}) {
+  if (my $us = List::Util::first { $op =~ $_->{regex} } @{$self->{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]);
   }
+  if (my $us = List::Util::first { $op =~ $_->{regex} } @{$self->{unary_ops}}) {
+    return $self->${\($us->{handler})}($op, $args[0]);
+  }
   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]);
@@ -1047,7 +976,7 @@ sub _where_op_OP {
   } else {
      my @parts = map [ $self->_render_expr($_) ], @args;
      my ($final_sql) = map +($op =~ /^(and|or)$/ ? "(${_})" : $_), join(
-       ' '.$self->_sqlcase($final_op).' ',
+       ($final_op eq ',' ? '' : ' ').$self->_sqlcase($final_op).' ',
        map $_->[0], @parts
      );
      return (
@@ -1058,8 +987,8 @@ sub _where_op_OP {
   die "unhandled";
 }
 
-sub _where_op_FUNC {
-  my ($self, undef, $rest) = @_;
+sub _render_func {
+  my ($self, $rest) = @_;
   my ($func, @args) = @$rest;
   my @arg_sql;
   my @bind = map {
@@ -1070,433 +999,17 @@ sub _where_op_FUNC {
   return ($self->_sqlcase($func).'('.join(', ', @arg_sql).')', @bind);
 }
 
-sub _where_op_BIND {
-  my ($self, undef, $bind) = @_;
+sub _render_bind {
+  my ($self,  $bind) = @_;
   return ($self->_convert('?'), $self->_bindtype(@$bind));
 }
 
-sub _where_op_LITERAL {
-  my ($self, undef, $literal) = @_;
+sub _render_literal {
+  my ($self, $literal) = @_;
   $self->_assert_bindval_matches_bindtype(@{$literal}[1..$#$literal]);
   return @$literal;
 }
 
-sub _where_hashpair_ARRAYREF {
-  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 {
-    $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} = defined $self->{_nested_func_lhs}
-    ? $self->{_nested_func_lhs}
-    : $k
-  ;
-
-  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;
-
-    # FIXME - we need to phase out dash-less ops
-    $op =~ s/^-//;        # remove possible initial dash
-    $op =~ s/^\s+|\s+$//g;# remove leading/trailing space
-    $op =~ s/\s+/ /g;     # compress whitespace
-
-    $self->_assert_pass_injection_guard($op);
-
-    # fixup is_not
-    $op =~ s/^is_not/IS NOT/i;
-
-    # so that -not_foo works correctly
-    $op =~ s/^not_/NOT /i;
-
-    # another retarded special case: foo => { $op => { -value => undef } }
-    if (ref $val eq 'HASH' and keys %$val == 1 and exists $val->{-value} and ! defined $val->{-value} ) {
-      $val = undef;
-    }
-
-    my ($sql, @bind);
-
-    # CASE: col-value logic modifiers
-    if ($orig_op =~ /^ \- (and|or) $/xi) {
-      ($sql, @bind) = $self->_where_hashpair_HASHREF($k, $val, $1);
-    }
-    # 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";
-      }
-      elsif (not ref $handler) {
-        ($sql, @bind) = $self->$handler($k, $op, $val);
-      }
-      elsif (ref $handler eq 'CODE') {
-        ($sql, @bind) = $handler->($self, $k, $op, $val);
-      }
-      else {
-        puke "Illegal handler for special operator $orig_op - expecting a method name or a coderef";
-      }
-    }
-    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 =~ /^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 '$orig_op' with undef operand";
-
-          $sql = $self->_quote($k) . $self->_sqlcase(" $is null");
-        },
-
-        FALLBACK => sub {       # CASE: col => {op/func => $stuff}
-          ($sql, @bind) = $self->_where_unary_op($op, $val);
-
-          $sql = join(' ',
-            $self->_convert($self->_quote($k)),
-            $self->{_nested_func_lhs} eq $k ? $sql : "($sql)",  # top level vs nested
-          );
-        },
-      });
-    }
-
-    ($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_IS {
-  my ($self, $k, $op, $v) = @_;
-
-  my ($s) = $self->_SWITCH_refkind($v, {
-    UNDEF => sub {
-      join ' ',
-        $self->_convert($self->_quote($k)),
-        map { $self->_sqlcase($_)} ($op, 'null')
-    },
-    FALLBACK => sub {
-      puke "$op can only take undef as argument";
-    },
-  });
-
-  $s;
-}
-
-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;
-    }
-
-    # a long standing API wart - an attempt to change this behavior during
-    # the 1.50 series failed *spectacularly*. Warn instead and leave the
-    # behavior as is
-    if (
-      @vals > 1
-        and
-      (!$logic or $logic eq 'OR')
-        and
-      ($op =~ $self->{inequality_op} or $op =~ $self->{not_like_op})
-    ) {
-      my $o = uc($op);
-      belch "A multi-element arrayref as an argument to the inequality op '$o' "
-          . 'is technically equivalent to an always-true 1=1 (you probably wanted '
-          . "to say ...{ \$inequality_op => [ -and => \@values ] }... instead)"
-      ;
-    }
-
-    # distribute $op over each remaining member of @vals, append logic if exists
-    return $self->_recurse_where([map { {$k => {$op, $_}} } @vals], $logic);
-
-  }
-  else {
-    # try to DWIM on equality operators
-    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')";
-  }
-}
-
-
-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");
-  return ($self->_where_hashpair_HASHREF($k, { $self->{cmp} => $v }));
-}
-
-
-sub _where_hashpair_UNDEF {
-  my ($self, $k, $v) = @_;
-  $self->_debug("UNDEF($k) means IS NULL");
-  return $self->_where_hashpair_HASHREF($k, { -is => undef });
-}
-
-#======================================================================
-# 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 $invalid_args = "Operator '$op' requires either an arrayref with two defined values or expressions, or a single literal scalarref/arrayref-ref";
-
-  my ($clause, @bind) = $self->_SWITCH_refkind($vals, {
-    ARRAYREFREF => sub {
-      my ($s, @b) = @$$vals;
-      $self->_assert_bindval_matches_bindtype(@b);
-      ($s, @b);
-    },
-    SCALARREF => sub {
-      return $$vals;
-    },
-    ARRAYREF => sub {
-      puke $invalid_args if @$vals != 2;
-
-      my (@all_sql, @all_bind);
-      foreach my $val (@$vals) {
-        my ($sql, @bind) = $self->_SWITCH_refkind($val, {
-           SCALAR => sub {
-             return ($placeholder, $self->_bindtype($k, $val) );
-           },
-           SCALARREF => sub {
-             return $$val;
-           },
-           ARRAYREFREF => sub {
-             my ($sql, @bind) = @$$val;
-             $self->_assert_bindval_matches_bindtype(@bind);
-             return ($sql, @bind);
-           },
-           HASHREF => sub {
-             my ($func, $arg, @rest) = %$val;
-             puke "Only simple { -func => arg } functions accepted as sub-arguments to BETWEEN"
-               if (@rest or $func !~ /^ \- (.+)/x);
-             $self->_where_unary_op($1 => $arg);
-           },
-           FALLBACK => sub {
-             puke $invalid_args,
-           },
-        });
-        push @all_sql, $sql;
-        push @all_bind, @bind;
-      }
-
-      return (
-        (join $and, @all_sql),
-        @all_bind
-      );
-    },
-    FALLBACK => sub {
-      puke $invalid_args,
-    },
-  });
-
-  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 (@all_sql, @all_bind);
-
-        for my $val (@$vals) {
-          my ($sql, @bind) = $self->_SWITCH_refkind($val, {
-            SCALAR => sub {
-              return ($placeholder, $val);
-            },
-            SCALARREF => sub {
-              return $$val;
-            },
-            ARRAYREFREF => sub {
-              my ($sql, @bind) = @$$val;
-              $self->_assert_bindval_matches_bindtype(@bind);
-              return ($sql, @bind);
-            },
-            HASHREF => sub {
-              my ($func, $arg, @rest) = %$val;
-              puke "Only simple { -func => arg } functions accepted as sub-arguments to IN"
-                if (@rest or $func !~ /^ \- (.+)/x);
-              $self->_where_unary_op($1 => $arg);
-            },
-            UNDEF => sub {
-              puke(
-                'SQL::Abstract before v1.75 used to generate incorrect SQL when the '
-              . "-$op 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)'
-              );
-            },
-          });
-          push @all_sql, $sql;
-          push @all_bind, @bind;
-        }
-
-        return (
-          sprintf('%s %s ( %s )',
-            $label,
-            $op,
-            join(', ', @all_sql)
-          ),
-          $self->_bindtype($k, @all_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);
-    },
-
-    UNDEF => sub {
-      puke "Argument passed to the '$op' operator can not be undefined";
-    },
-
-    FALLBACK => sub {
-      puke "special op $op 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
@@ -1532,85 +1045,63 @@ sub _open_outer_paren {
 # ORDER BY
 #======================================================================
 
-sub _order_by {
+sub _expand_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 },
-    });
-  }
+  return unless defined($arg) and not (ref($arg) eq 'ARRAY' and !@$arg);
 
-  my $sql = @sql
-    ? sprintf('%s %s',
-        $self->_sqlcase(' order by'),
-        join(', ', @sql)
-      )
-    : ''
-  ;
+  my $expander = sub {
+    my ($self, $dir, $expr) = @_;
+    my @to_expand = ref($expr) eq 'ARRAY' ? @$expr : $expr;
+    foreach my $arg (@to_expand) {
+      if (
+        ref($arg) eq 'HASH'
+        and keys %$arg > 1
+        and grep /^-(asc|desc)$/, keys %$arg
+      ) {
+        puke "ordering direction hash passed to order by must have exactly one key (-asc or -desc)";
+      }
+    }
+    my @exp = map +(defined($dir) ? { -op => [ $dir => $_ ] } : $_),
+                map $self->_expand_expr($_, undef, -ident), @to_expand;
+    return (@exp > 1 ? { -op => [ ',', @exp ] } : $exp[0]);
+  };
+
+  local @{$self->{expand_unary}}{qw(-asc -desc)} = (
+    sub { shift->$expander(asc => @_) },
+    sub { shift->$expander(desc => @_) },
+  );
 
-  return wantarray ? ($sql, @bind) : $sql;
+  return $self->$expander(undef, $arg);
 }
 
-sub _order_by_chunks {
+sub _order_by {
   my ($self, $arg) = @_;
 
-  return $self->_SWITCH_refkind($arg, {
+  return '' unless defined(my $expanded = $self->_expand_order_by($arg));
 
-    ARRAYREF => sub {
-      map { $self->_order_by_chunks($_ ) } @$arg;
-    },
+  my ($sql, @bind) = $self->_render_expr($expanded);
 
-    ARRAYREFREF => sub {
-      my ($s, @b) = @$$arg;
-      $self->_assert_bindval_matches_bindtype(@b);
-      [ $s, @b ];
-    },
+  my $final_sql = $self->_sqlcase(' order by ').$sql;
 
-    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, @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 $direction = $1;
-
-      my @ret;
-      for my $c ($self->_order_by_chunks($val)) {
-        my ($sql, @bind);
+  return wantarray ? ($final_sql, @bind) : $final_sql;
+}
 
-        $self->_SWITCH_refkind($c, {
-          SCALAR => sub {
-            $sql = $c;
-          },
-          ARRAYREF => sub {
-            ($sql, @bind) = @$c;
-          },
-        });
+sub _order_by_chunks {
+  my ($self, $arg) = @_;
 
-        $sql = $sql . ' ' . $self->_sqlcase($direction);
+  return () unless defined(my $expanded = $self->_expand_order_by($arg));
 
-        push @ret, [ $sql, @bind];
+  for ($expanded) {
+    if (ref() eq 'HASH' and my $op = $_->{-op}) {
+      if ($op->[0] eq ',') {
+        return map [ $self->_render_expr($_) ], @{$op}[1..$#$op];
       }
-
-      return @ret;
-    },
-  });
+    }
+    return [ $self->_render_expr($_) ];
+  }
 }
 
-
 #======================================================================
 # DATASOURCE (FOR NOW, JUST PLAIN TABLE OR LIST OF TABLES)
 #======================================================================
@@ -1618,11 +1109,9 @@ sub _order_by_chunks {
 sub _table  {
   my $self = shift;
   my $from = shift;
-  $self->_SWITCH_refkind($from, {
-    ARRAYREF     => sub {join ', ', map { $self->_quote($_) } @$from;},
-    SCALAR       => sub {$self->_quote($from)},
-    SCALARREF    => sub {$$from},
-  });
+  ($self->_render_expr(
+    $self->_expand_maybe_list_expr($from, undef, -ident)
+  ))[0];
 }
 
 
@@ -1630,15 +1119,37 @@ sub _table  {
 # UTILITY FUNCTIONS
 #======================================================================
 
+sub _expand_maybe_list_expr {
+  my ($self, $expr, $logic, $default) = @_;
+  my $e = do {
+    if (ref($expr) eq 'ARRAY') {
+      return { -op => [
+        ',', map $self->_expand_expr($_, $logic, $default), @$expr
+      ] } if @$expr > 1;
+      $expr->[0]
+    } else {
+      $expr
+    }
+  };
+  return $self->_expand_expr($e, $logic, $default);
+}
+
 # highly optimized, as it's called way too often
 sub _quote {
   # my ($self, $label) = @_;
 
   return '' unless defined $_[1];
   return ${$_[1]} if ref($_[1]) eq 'SCALAR';
+  puke 'Identifier cannot be hashref' if ref($_[1]) eq 'HASH';
 
-  $_[0]->{quote_char} or
-    ($_[0]->_assert_pass_injection_guard($_[1]), return $_[1]);
+  unless ($_[0]->{quote_char}) {
+    if (ref($_[1]) eq 'ARRAY') {
+      return join($_[0]->{name_sep}||'.', @{$_[1]});
+    } else {
+      $_[0]->_assert_pass_injection_guard($_[1]);
+      return $_[1];
+    }
+  }
 
   my $qref = ref $_[0]->{quote_char};
   my ($l, $r) =
@@ -1649,9 +1160,21 @@ sub _quote {
   my $esc = $_[0]->{escape_char} || $r;
 
   # parts containing * are naturally unquoted
-  return join($_[0]->{name_sep}||'', map
-    +( $_ eq '*' ? $_ : do { (my $n = $_) =~ s/(\Q$esc\E|\Q$r\E)/$esc$1/g; $l . $n . $r } ),
-    ( $_[0]->{name_sep} ? split (/\Q$_[0]->{name_sep}\E/, $_[1] ) : $_[1] )
+  return join(
+    $_[0]->{name_sep}||'',
+    map +(
+      $_ eq '*'
+        ? $_
+        : do { (my $n = $_) =~ s/(\Q$esc\E|\Q$r\E)/$esc$1/g; $l . $n . $r }
+    ),
+    (ref($_[1]) eq 'ARRAY'
+      ? @{$_[1]}
+      : (
+          $_[0]->{name_sep}
+            ? split (/\Q$_[0]->{name_sep}\E/, $_[1] )
+            : $_[1]
+        )
+    )
   );
 }
 
@@ -1659,8 +1182,8 @@ sub _quote {
 # Conversion, if applicable
 sub _convert {
   #my ($self, $arg) = @_;
-  if ($_[0]->{convert}) {
-    return $_[0]->_sqlcase($_[0]->{convert}) .'(' . $_[1] . ')';
+  if ($_[0]->{convert_where}) {
+    return $_[0]->_sqlcase($_[0]->{convert_where}) .'(' . $_[1] . ')';
   }
   return $_[1];
 }