# GLOBALS
#======================================================================
-our $VERSION = '1.50';
+our $VERSION = '1.51';
# This would confuse some packagers
#$VERSION = eval $VERSION; # numify for warning-free dev releases
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' ??
}
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};
@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' :
});
}
- 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);
}
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)
}
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;
}
=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
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:
The reverse operator C<-not_in> generates SQL C<NOT IN> 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:
{-desc => 'colB'} |
] |
[colA => {-asc => 'colB'}] | ORDER BY colA, colB ASC
+ { -asc => [qw/colA colB] } | ORDER BY colA ASC, colB ASC
+ { -asc => [qw/colA colB] },|
+ -desc => [qw/colC colD] } | ORDER BY colA ASC, colB ASC, colC DESC, colD DESC
==========================================================
=item *
-added official support for -nest1, -nest2 or -nest_1, -nest_2, ...
-(undocumented in previous versions)
-
-=item *
-
optional support for L<array datatypes|/"Inserting and Updating Arrays">
=item *