X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FSQL%2FAbstract.pm;h=a56970805cbef9e29a854408fc3a9b5979ce0434;hb=d92d804b3d06c8110b12512b62333c6aec506993;hp=248ec3a8759f75b4c95e22817342cb7d22094f03;hpb=b3be7bd08ab2e97184d5700fee0930f651b0c9a2;p=scpubgit%2FQ-Branch.git
diff --git a/lib/SQL/Abstract.pm b/lib/SQL/Abstract.pm
index 248ec3a..a569708 100644
--- a/lib/SQL/Abstract.pm
+++ b/lib/SQL/Abstract.pm
@@ -15,9 +15,10 @@ use Scalar::Util qw/blessed/;
# GLOBALS
#======================================================================
-our $VERSION = '1.49_01';
-$VERSION = eval $VERSION; # numify for warning-free dev releases
+our $VERSION = '1.53';
+# This would confuse some packagers
+#$VERSION = eval $VERSION; # numify for warning-free dev releases
our $AUTOLOAD;
@@ -62,7 +63,7 @@ sub new {
delete $opt{case} if $opt{case} && $opt{case} ne 'lower';
# default logic for interpreting arrayrefs
- $opt{logic} = uc $opt{logic} || 'OR';
+ $opt{logic} = $opt{logic} ? uc $opt{logic} : 'OR';
# how to return bind vars
# LDNOTE: changed nwiger code : why this 'delete' ??
@@ -110,18 +111,7 @@ sub _insert_HASHREF { # explicit list of fields and then values
my @fields = sort keys %$data;
- my ($sql, @bind);
- { # get values (need temporary override of bindtype to avoid an error)
- local $self->{bindtype} = 'normal';
- ($sql, @bind) = $self->_insert_ARRAYREF([@{$data}{@fields}]);
- }
-
- # if necessary, transform values according to 'bindtype'
- if ($self->{bindtype} eq 'columns') {
- for my $i (0 .. $#fields) {
- ($bind[$i]) = $self->_bindtype($fields[$i], $bind[$i]);
- }
- }
+ my ($sql, @bind) = $self->_insert_values($data);
# assemble SQL
$_ = $self->_quote($_) foreach @fields;
@@ -137,18 +127,48 @@ sub _insert_ARRAYREF { # just generate values(?,?) part (no list of fields)
$self->{bindtype} ne 'columns'
or belch "can't do 'columns' bindtype when called with arrayref";
+ # fold the list of values into a hash of column name - value pairs
+ # (where the column names are artificially generated, and their
+ # lexicographical ordering keep the ordering of the original list)
+ my $i = "a"; # incremented values will be in lexicographical order
+ my $data_in_hash = { map { ($i++ => $_) } @$data };
+
+ return $self->_insert_values($data_in_hash);
+}
+
+sub _insert_ARRAYREFREF { # literal SQL with bind
+ my ($self, $data) = @_;
+
+ my ($sql, @bind) = @${$data};
+ $self->_assert_bindval_matches_bindtype(@bind);
+
+ return ($sql, @bind);
+}
+
+
+sub _insert_SCALARREF { # literal SQL without bind
+ my ($self, $data) = @_;
+
+ return ($$data);
+}
+
+sub _insert_values {
+ my ($self, $data) = @_;
+
my (@values, @all_bind);
- for my $v (@$data) {
+ foreach my $column (sort keys %$data) {
+ my $v = $data->{$column};
$self->_SWITCH_refkind($v, {
ARRAYREF => sub {
if ($self->{array_datatypes}) { # if array datatype are activated
push @values, '?';
- push @all_bind, $v;
+ 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;
}
@@ -156,11 +176,18 @@ sub _insert_ARRAYREF { # just generate values(?,?) part (no list of fields)
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;
@@ -168,7 +195,7 @@ sub _insert_ARRAYREF { # just generate values(?,?) part (no list of fields)
SCALAR_or_UNDEF => sub {
push @values, '?';
- push @all_bind, $v;
+ push @all_bind, $self->_bindtype($column, $v);
},
});
@@ -180,19 +207,6 @@ sub _insert_ARRAYREF { # just generate values(?,?) part (no list of fields)
}
-sub _insert_ARRAYREFREF { # literal SQL with bind
- my ($self, $data) = @_;
- return @${$data};
-}
-
-
-sub _insert_SCALARREF { # literal SQL without bind
- my ($self, $data) = @_;
-
- return ($$data);
-}
-
-
#======================================================================
# UPDATE methods
@@ -223,14 +237,16 @@ sub update {
}
else { # literal SQL with bind
my ($sql, @bind) = @$v;
+ $self->_assert_bindval_matches_bindtype(@bind);
push @set, "$label = $sql";
- push @all_bind, $self->_bindtype($k, @bind);
+ 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, $self->_bindtype($k, @bind);
+ push @all_bind, @bind;
},
SCALARREF => sub { # literal SQL without bind
push @set, "$label = $$v";
@@ -351,17 +367,7 @@ sub _where_ARRAYREF {
my @clauses = @$where;
- # if the array starts with [-and|or => ...], recurse with that logic
- my $first = $clauses[0] || '';
- if ($first =~ /^-(and|or)/i) {
- $logic = $1;
- shift @clauses;
- return $self->_where_ARRAYREF(\@clauses, $logic);
- }
-
- #otherwise..
my (@sql_clauses, @all_bind);
-
# need to use while() so can shift() for pairs
while (my $el = shift @clauses) {
@@ -371,6 +377,8 @@ sub _where_ARRAYREF {
# skip empty elements, otherwise get invalid trailing AND stuff
ARRAYREF => sub {$self->_recurse_where($el) if @$el},
+ ARRAYREFREF => sub { @{${$el}} if @{${$el}}},
+
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
@@ -395,7 +403,16 @@ sub _where_ARRAYREF {
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 ($sql, @bind);
+}
#======================================================================
# WHERE: top-level HASHREF
@@ -405,7 +422,6 @@ sub _where_HASHREF {
my ($self, $where) = @_;
my (@sql_clauses, @all_bind);
- # LDNOTE : don't really know why we need to sort keys
for my $k (sort keys %$where) {
my $v = $where->{$k};
@@ -425,49 +441,56 @@ sub _where_HASHREF {
sub _where_op_in_hash {
- my ($self, $op, $v) = @_;
+ my ($self, $op_str, $v) = @_;
+
+ $op_str =~ /^ (AND|OR|PAREN|NEST) ( \_? \d* ) $/xi
+ or puke "unknown operator: -$op_str";
+
+ my $op = uc($1); # uppercase, remove trailing digits
+ if ($2) {
+ belch 'Use of op_N modifiers is deprecated and will be removed in SQLA v2.0. '
+ . "You probably wanted ...-and => [ $op_str => COND1, $op_str => COND2 ... ]";
+ }
+
+ if ($op eq 'NEST') {
+ belch 'The -nest modifier is deprecated in favor of -paren and will be removed in SQLA v2.0';
+ $op = 'PAREN';
+ }
- $op =~ /^(AND|OR|NEST)[_\d]*/i
- or puke "unknown operator: -$op";
- $op = uc($1); # uppercase, remove trailing digits
$self->_debug("OP(-$op) within hashref, recursing...");
$self->_SWITCH_refkind($v, {
ARRAYREF => sub {
- # LDNOTE : should deprecate {-or => [...]} and {-and => [...]}
- # because they are misleading; the only proper way would be
- # -nest => [-or => ...], -nest => [-and ...]
- return $self->_where_ARRAYREF($v, $op eq 'NEST' ? '' : $op);
+ return $self->_where_ARRAYREF($v, $op eq 'PAREN' ? '' : $op);
},
HASHREF => sub {
if ($op eq 'OR') {
- belch "-or => {...} should be -nest => [...]";
- return $self->_where_ARRAYREF([%$v], 'OR');
+ return $self->_where_ARRAYREF([ map { $_ => $v->{$_} } (sort keys %$v) ], 'OR');
}
- else { # NEST | AND
+ else { # PAREN | AND
return $self->_where_HASHREF($v);
}
},
SCALARREF => sub { # literal SQL
- $op eq 'NEST'
- or puke "-$op => \\\$scalar not supported, use -nest => ...";
+ $op eq 'PAREN'
+ or puke "-$op => \\\$scalar not supported, use -paren => ...";
return ($$v);
},
ARRAYREFREF => sub { # literal SQL
- $op eq 'NEST'
- or puke "-$op => \\[..] not supported, use -nest => ...";
+ $op eq 'PAREN'
+ or puke "-$op => \\[..] not supported, use -paren => ...";
return @{${$v}};
},
SCALAR => sub { # permissively interpreted as SQL
- $op eq 'NEST'
- or puke "-$op => 'scalar' not supported, use -nest => \\'scalar'";
- belch "literal SQL should be -nest => \\'scalar' "
- . "instead of -nest => 'scalar' ";
+ $op eq 'PAREN'
+ or puke "-$op => 'scalar' not supported, use -paren => \\'scalar'";
+ belch "literal SQL should be -paren => \\'scalar' "
+ . "instead of -paren => 'scalar' ";
return ($v);
},
@@ -486,13 +509,21 @@ sub _where_hashpair_ARRAYREF {
$self->_debug("ARRAY($k) means distribute over elements");
# put apart first element if it is an operator (-and, -or)
- my $op = $v[0] =~ /^-/ ? shift @v : undef;
- $self->_debug("OP($op) reinjected into the distributed array") if $op;
-
+ my $op = (
+ (defined $v[0] && $v[0] =~ /^ - (?: AND|OR ) $/ix)
+ ? shift @v
+ : ''
+ );
my @distributed = map { {$k => $_} } @v;
- unshift @distributed, $op if $op;
- return $self->_recurse_where(\@distributed);
+ 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?
@@ -502,9 +533,10 @@ sub _where_hashpair_ARRAYREF {
}
sub _where_hashpair_HASHREF {
- my ($self, $k, $v) = @_;
+ my ($self, $k, $v, $logic) = @_;
+ $logic ||= 'and';
- my (@all_sql, @all_bind);
+ my ($all_sql, @all_bind);
for my $op (sort keys %$v) {
my $val = $v->{$op};
@@ -530,20 +562,25 @@ sub _where_hashpair_HASHREF {
($sql, @bind) = $self->_where_field_op_ARRAYREF($k, $op, $val);
},
- SCALARREF => sub { # CASE: col => {op => \$scalar}
+ SCALARREF => sub { # CASE: col => {op => \$scalar} (literal SQL without bind)
$sql = join ' ', $self->_convert($self->_quote($k)),
$self->_sqlcase($op),
$$val;
},
- ARRAYREFREF => sub { # CASE: col => {op => \[$sql, @bind]}
+ 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;
},
+ HASHREF => sub {
+ ($sql, @bind) = $self->_where_hashpair_HASHREF($k, $val, $op);
+ },
+
UNDEF => sub { # CASE: col => {op => undef} : sql "IS (NOT)? NULL"
my $is = ($op =~ $self->{equality_op}) ? 'is' :
($op =~ $self->{inequality_op}) ? 'is not' :
@@ -560,11 +597,10 @@ sub _where_hashpair_HASHREF {
});
}
- push @all_sql, $sql;
+ ($all_sql) = (defined $all_sql and $all_sql) ? $self->_join_sql_clauses($logic, [$all_sql, $sql], []) : $sql;
push @all_bind, @bind;
}
-
- return $self->_join_sql_clauses('and', \@all_sql, \@all_bind);
+ return ($all_sql, @all_bind);
}
@@ -575,17 +611,24 @@ sub _where_field_op_ARRAYREF {
if(@$vals) {
$self->_debug("ARRAY($vals) means multiple elements: [ @$vals ]");
+ # see if the first element is an -and/-or op
+ my $logic;
+ if ($vals->[0] =~ /^ - ( AND|OR ) $/ix) {
+ $logic = uc $1;
+ shift @$vals;
+ }
+ # distribute $op over each remaining member of @$vals, append logic if exists
+ return $self->_recurse_where([map { {$k => {$op, $_}} } @$vals], $logic);
- # LDNOTE : change the distribution logic when
+ # 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.
- my $logic = ($op =~ $self->{inequality_op}) ? 'AND' : 'OR';
-
- # distribute $op over each member of @$vals
- return $self->_recurse_where([map { {$k => {$op, $_}} } @$vals], $logic);
+ # 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 {
@@ -607,15 +650,17 @@ sub _where_hashpair_SCALARREF {
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;
- @bind = $self->_bindtype($k, @bind);
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");
@@ -671,16 +716,39 @@ sub _where_UNDEF {
sub _where_field_BETWEEN {
my ($self, $k, $op, $vals) = @_;
- ref $vals eq 'ARRAY' && @$vals == 2
- or puke "special op 'between' requires an arrayref of two values";
+ (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 ($label) = $self->_convert($self->_quote($k));
- my ($placeholder) = $self->_convert('?');
- my $and = $self->_sqlcase('and');
+ my ($clause, @bind, $label, $and, $placeholder);
+ $label = $self->_convert($self->_quote($k));
+ $and = ' ' . $self->_sqlcase('and') . ' ';
+ $placeholder = $self->_convert('?');
$op = $self->_sqlcase($op);
- my $sql = "( $label $op $placeholder $and $placeholder )";
- my @bind = $self->_bindtype($k, @$vals);
+ 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;
+ }
+
+ $clause = (join $and, @all_sql);
+ @bind = $self->_bindtype($k, @all_bind);
+ }
+ my $sql = "( $label $op $clause )";
return ($sql, @bind)
}
@@ -691,25 +759,37 @@ 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;
+ $self->_assert_bindval_matches_bindtype(@bind);
+ return ("$label $op ( $sql )", @bind);
+ },
+
+ FALLBACK => sub {
+ puke "special op 'in' requires an arrayref (or arrayref-ref)";
+ },
+ });
+
+ return ($sql, @bind);
}
@@ -762,7 +842,8 @@ sub _order_by_hash {
my ($order) = ($key =~ /^-(desc|asc)/i)
or puke "invalid key in _order_by hash : $key";
- return $self->_quote($val) ." ". $self->_sqlcase($order);
+ $val = ref $val eq 'ARRAY' ? $val : [$val];
+ return join ', ', map { $self->_quote($_) . ' ' . $self->_sqlcase($order) } @$val;
}
@@ -852,6 +933,20 @@ sub _bindtype (@) {
return $self->{bindtype} eq 'columns' ? map {[$col, $_]} @vals : @vals;
}
+# Dies if any element of @bind is not in [colname => value] format
+# if bindtype is 'columns'.
+sub _assert_bindval_matches_bindtype {
+ my ($self, @bind) = @_;
+
+ 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]"
+ }
+ }
+ }
+}
+
sub _join_sql_clauses {
my ($self, $logic, $clauses_aref, $bind_aref) = @_;
@@ -946,7 +1041,35 @@ sub values {
my $data = shift || return;
puke "Argument to ", __PACKAGE__, "->values must be a \\%hash"
unless ref $data eq 'HASH';
- return map { $self->_bindtype($_, $data->{$_}) } sort keys %$data;
+
+ my @all_bind;
+ foreach my $k ( sort keys %$data ) {
+ my $v = $data->{$k};
+ $self->_SWITCH_refkind($v, {
+ ARRAYREF => sub {
+ if ($self->{array_datatypes}) { # array datatype
+ push @all_bind, $self->_bindtype($k, $v);
+ }
+ else { # literal SQL with bind
+ my ($sql, @bind) = @$v;
+ $self->_assert_bindval_matches_bindtype(@bind);
+ push @all_bind, @bind;
+ }
+ },
+ ARRAYREFREF => sub { # literal SQL with bind
+ my ($sql, @bind) = @${$v};
+ $self->_assert_bindval_matches_bindtype(@bind);
+ push @all_bind, @bind;
+ },
+ SCALARREF => sub { # literal SQL without bind
+ },
+ SCALAR_or_UNDEF => sub {
+ push @all_bind, $self->_bindtype($k, $v);
+ },
+ });
+ }
+
+ return @all_bind;
}
sub generate {
@@ -962,13 +1085,13 @@ sub generate {
my $r = ref $v;
my $label = $self->_quote($k);
if ($r eq 'ARRAY') {
- # SQL included for values
- my @bind = @$v;
- my $sql = shift @bind;
+ # literal SQL with bind
+ my ($sql, @bind) = @$v;
+ $self->_assert_bindval_matches_bindtype(@bind);
push @sqlq, "$label = $sql";
- push @sqlv, $self->_bindtype($k, @bind);
+ push @sqlv, @bind;
} elsif ($r eq 'SCALAR') {
- # embedded literal SQL
+ # literal SQL without bind
push @sqlq, "$label = $$v";
} else {
push @sqlq, "$label = ?";
@@ -980,11 +1103,12 @@ sub generate {
# unlike insert(), assume these are ONLY the column names, i.e. for SQL
for my $v (@$_) {
my $r = ref $v;
- if ($r eq 'ARRAY') {
- my @val = @$v;
- push @sqlq, shift @val;
- push @sqlv, @val;
- } elsif ($r eq 'SCALAR') {
+ if ($r eq 'ARRAY') { # literal SQL with bind
+ my ($sql, @bind) = @$v;
+ $self->_assert_bindval_matches_bindtype(@bind);
+ push @sqlq, $sql;
+ push @sqlv, @bind;
+ } elsif ($r eq 'SCALAR') { # literal SQL without bind
# embedded literal SQL
push @sqlq, $$v;
} else {
@@ -1237,12 +1361,15 @@ the huge section on L"WHERE CLAUSES"> at the bottom.
=item sqltrue, sqlfalse
Expressions for inserting boolean values within SQL statements.
-By default these are C<1=1> and C<1=0>.
+By default these are C<1=1> and C<1=0>. They are used
+by the special operators C<-in> and C<-not_in> for generating
+correct SQL even when the argument is an empty array (see below).
=item logic
This determines the default logical operator for multiple WHERE
-statements in arrays. By default it is "or", meaning that a WHERE
+statements in arrays or hashes. If absent, the default logic is "or"
+for arrays, and "and" for hashes. This means that a WHERE
array of the form:
@where = (
@@ -1250,7 +1377,7 @@ array of the form:
event_date => {'<=', '4/24/03'},
);
-Will generate SQL like this:
+will generate SQL like this:
WHERE event_date >= '2/13/99' OR event_date <= '4/24/03'
@@ -1264,10 +1391,10 @@ Which will change the above C to:
WHERE event_date >= '2/13/99' AND event_date <= '4/24/03'
The logic can also be changed locally by inserting
-an extra first element in the array :
+a modifier in front of an arrayref :
- @where = (-and => event_date => {'>=', '2/13/99'},
- event_date => {'<=', '4/24/03'} );
+ @where = (-and => [event_date => {'>=', '2/13/99'},
+ event_date => {'<=', '4/24/03'} ]);
See the L"WHERE CLAUSES"> section for explanations.
@@ -1337,6 +1464,10 @@ are or are not included. You could wrap that above C loop in a simple
sub called C or something and reuse it repeatedly. You still
get a layer of abstraction over manual SQL specification.
+Note that if you set L to C, the C<\[$sql, @bind]>
+construct (see L)
+will expect the bind values in this format.
+
=item quote_char
This is the character that a table or column name will be quoted
@@ -1548,10 +1679,10 @@ This simple code will create the following:
$stmt = "WHERE user = ? AND ( status = ? OR status = ? OR status = ? )";
@bind = ('nwiger', 'assigned', 'in-progress', 'pending');
-An empty arrayref will be considered a logical false and
-will generate 0=1.
+A field associated to an empty arrayref will be considered a
+logical false and will generate 0=1.
-=head2 Key-value pairs
+=head2 Specific comparison operators
If you want to specify a different type of operator for your comparison,
you can use a hashref for a given column:
@@ -1568,19 +1699,9 @@ Which would generate:
To test against multiple values, just enclose the values in an arrayref:
- status => { '!=', ['assigned', 'in-progress', 'pending'] };
-
-Which would give you:
-
- "WHERE status != ? AND status != ? AND status != ?"
-
-Notice that since the operator was recognized as being a 'negative'
-operator, the arrayref was interpreted with 'AND' logic (because
-of Morgan's laws). By contrast, the reverse
-
status => { '=', ['assigned', 'in-progress', 'pending'] };
-would generate :
+Which would give you:
"WHERE status = ? OR status = ? OR status = ?"
@@ -1615,6 +1736,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
@@ -1655,29 +1789,6 @@ Here is a quick list of equivalencies, since there is some overlap:
status => [ -or => {'=', 'assigned'}, {'=', 'in-progress'}]
status => [ {'=', 'assigned'}, {'=', 'in-progress'} ]
-In addition to C<-and> and C<-or>, 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' ],
- );
-
-If you need several nested subexpressions, you can number
-the C<-nest> branches :
-
- my %where = (
- user => 'nwiger',
- -nest1 => ...,
- -nest2 => ...,
- ...
- );
=head2 Special operators : IN, BETWEEN, etc.
@@ -1698,6 +1809,12 @@ Which would generate:
The reverse operator C<-not_in> generates SQL C and is used in
the same way.
+If the argument to C<-in> is an empty array, 'sqlfalse' is generated
+(by default : C<1=0>). Similarly, C<< -not_in => [] >> generates
+'sqltrue' (by default : C<1=1>).
+
+
+
Another pair of operators is C<-between> and C<-not_between>,
used with an arrayref of two values:
@@ -1715,7 +1832,7 @@ Would give you:
These are the two builtin "special operators"; but the
list can be expanded : see section L"SPECIAL OPERATORS"> below.
-=head2 Nested conditions
+=head2 Nested conditions, -and/-or prefixes
So far, we've seen how multiple conditions are joined with a top-level
C. We can change this by putting the different conditions we want in
@@ -1738,15 +1855,32 @@ This data structure would create the following:
OR ( user = ? AND status = ? ) )";
@bind = ('nwiger', 'pending', 'dispatched', 'robot', 'unassigned');
-This can be combined with the C<-nest> operator to properly group
-SQL statements:
+
+There is also a special C<-paren>
+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',
+ -paren => [ 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 :
my @where = (
-and => [
user => 'nwiger',
- -nest => [
- ["-and", workhrs => {'>', 20}, geo => 'ASIA' ],
- ["-and", workhrs => {'<', 50}, geo => 'EURO' ]
+ -paren => [
+ -and => [workhrs => {'>', 20}, geo => 'ASIA' ],
+ -and => [workhrs => {'<', 50}, geo => 'EURO' ]
],
],
);
@@ -1757,6 +1891,37 @@ That would yield:
( ( workhrs > ? AND geo = ? )
OR ( workhrs < ? AND geo = ? ) ) )
+
+=head2 Algebraic inconsistency, for historical reasons
+
+C: when connecting several conditions, the C<-and->|C<-or>
+operator goes C of the nested structure; whereas when connecting
+several constraints on one column, the C<-and> operator goes
+C the arrayref. Here is an example combining both features :
+
+ my @where = (
+ -and => [a => 1, b => 2],
+ -or => [c => 3, d => 4],
+ e => [-and => {-like => 'foo%'}, {-like => '%bar'} ]
+ )
+
+yielding
+
+ 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'}]}
+ # yields : WHERE ( ( col LIKE ? AND col LIKE ? ) )
+
+ [-and => {col => {-like => 'foo%'}, {col => {-like => '%bar'}}]]
+ # yields : WHERE ( ( col LIKE ? OR col LIKE ? ) )
+
+
=head2 Literal SQL
Finally, sometimes only literal SQL will do. If you want to include
@@ -1817,6 +1982,17 @@ This would create:
$stmt = "WHERE ( date_column = date '2008-09-30' - ?::integer )"
@bind = ('10');
+Note that you must pass the bind values in the same format as they are returned
+by L. That means that if you set L to C, you must
+provide the bind values in the C<< [ column_meta => value ] >> format, where
+C is an opaque scalar value; most commonly the column name, but
+you can use any scalar value (including references and blessed references),
+L will simply pass it through intact. So if C is set
+to C the above example will look like:
+
+ my %where = (
+ date_column => \[q/= date '2008-09-30' - ?::integer/, [ dummy => 10 ]/]
+ )
Literal SQL is especially useful for nesting parenthesized clauses in the
main SQL query. Here is a first example :
@@ -1855,7 +2031,7 @@ hash, like an EXISTS subquery :
= $sql->select("t1", "*", {c1 => 1, c2 => \"> t0.c0"});
my %where = (
foo => 1234,
- -nest => \["EXISTS ($sub_stmt)" => @sub_bind],
+ -paren => \["EXISTS ($sub_stmt)" => @sub_bind],
);
which yields
@@ -1878,7 +2054,7 @@ 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/]
+ -paren => \["MATCH (col1, col2) AGAINST (?)" => qw/apples/]
);
Finally, here is an example where a subquery is used
@@ -1889,7 +2065,7 @@ for expressing unary negation:
$sub_stmt =~ s/^ where //i; # don't want "WHERE" in the subclause
my %where = (
lname => {like => '%son%'},
- -nest => \["NOT ($sub_stmt)" => @sub_bind],
+ -paren => \["NOT ($sub_stmt)" => @sub_bind],
);
This yields
@@ -1923,19 +2099,29 @@ 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:
- Given | Will Generate
+ Given | Will Generate
----------------------------------------------------------
- \'colA DESC' | ORDER BY colA DESC
- 'colA' | ORDER BY colA
- [qw/colA colB/] | ORDER BY colA, colB
- {-asc => 'colA'} | ORDER BY colA ASC
- {-desc => 'colB'} | ORDER BY colB DESC
- [ |
- {-asc => 'colA'}, | ORDER BY colA ASC, colB DESC
- {-desc => 'colB'} |
- ] |
- [colA => {-asc => 'colB'}] | ORDER BY colA, colB ASC
- ==========================================================
+ |
+ \'colA DESC' | ORDER BY colA DESC
+ |
+ 'colA' | ORDER BY colA
+ |
+ [qw/colA colB/] | ORDER BY colA, colB
+ |
+ {-asc => 'colA'} | ORDER BY colA ASC
+ |
+ {-desc => 'colB'} | ORDER BY colB DESC
+ |
+ ['colA', {-asc => 'colB'}] | ORDER BY colA, colB ASC
+ |
+ { -asc => [qw/colA colB] } | ORDER BY colA ASC, colB ASC
+ |
+ [ |
+ { -asc => 'colA' }, | ORDER BY colA ASC, colB DESC,
+ { -desc => [qw/colB/], | colC ASC, colD ASC
+ { -asc => [qw/colC colD/],|
+ ] |
+ ===========================================================
@@ -2085,7 +2271,7 @@ support for the { operator => \"..." } construct (to embed literal SQL)
=item *
-added -nest1, -nest2 or -nest_1, -nest_2, ...
+support for the { operator => \["...", @bind] } construct (to embed literal SQL with bind values)
=item *
@@ -2098,21 +2284,12 @@ defensive programming : check arguments
=item *
fixed bug with global logic, which was previously implemented
-through global variables yielding side-effects. Prior versons would
+through global variables yielding side-effects. Prior versions would
interpret C<< [ {cond1, cond2}, [cond3, cond4] ] >>
as C<< "(cond1 AND cond2) OR (cond3 AND cond4)" >>.
Now this is interpreted
as C<< "(cond1 AND cond2) OR (cond3 OR cond4)" >>.
-=item *
-
-C<-and> / C<-or> operators are no longer accepted
-in the middle of an arrayref : they are
-only admitted if in first position.
-
-=item *
-
-changed logic for distributing an op over arrayrefs
=item *
@@ -2146,7 +2323,9 @@ so I have no idea who they are! But the people I do know are:
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, multiple -paren, 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)
Thanks!
@@ -2165,6 +2344,8 @@ While not an official support venue, C makes heavy use of
C, and as such list members there are very familiar with
how to create queries.
+=head1 LICENSE
+
This module is free software; you may copy this under the terms of
the GNU General Public License, or the Artistic License, copies of
which should have accompanied your Perl kit.