# GLOBALS
#======================================================================
-our $VERSION = '1.65_02';
+our $VERSION = '1.67_01';
# This would confuse some packagers
$VERSION = eval $VERSION if $VERSION =~ /_/; # numify for warning-free dev releases
# skip empty elements, otherwise get invalid trailing AND stuff
ARRAYREF => sub {$self->_recurse_where($el) if @$el},
- ARRAYREFREF => sub { @{${$el}} if @{${$el}}},
+ ARRAYREFREF => sub {
+ my ($s, @b) = @$$el;
+ $self->_assert_bindval_matches_bindtype(@b);
+ ($s, @b);
+ },
HASHREF => sub {$self->_recurse_where($el, 'and') if %$el},
# LDNOTE : previous SQLA code for hashrefs was creating a dirty
sub _where_ARRAYREFREF {
my ($self, $where) = @_;
- my ($sql, @bind) = @{${$where}};
-
+ my ($sql, @bind) = @$$where;
+ $self->_assert_bindval_matches_bindtype(@bind);
return ($sql, @bind);
}
else {
$self->debug("Generic unary OP: $k - recursing as function");
my ($sql, @bind) = $self->_where_func_generic ($op, $v);
- $sql = "($sql)" unless $self->{_nested_func_lhs} eq $k; # top level vs nested
+ $sql = "($sql)" unless (defined($self->{_nested_func_lhs}) && ($self->{_nested_func_lhs} eq $k)); # top level vs nested
($sql, @bind);
}
}
sub _where_hashpair_ARRAYREFREF {
my ($self, $k, $v) = @_;
$self->_debug("REF($k) means literal SQL: @${$v}");
- my ($sql, @bind) = @${$v};
+ my ($sql, @bind) = @$$v;
$self->_assert_bindval_matches_bindtype(@bind);
$sql = $self->_quote($k) . " " . $sql;
return ($sql, @bind );
my ($clause, @bind) = $self->_SWITCH_refkind($vals, {
ARRAYREFREF => sub {
- return @$$vals;
+ my ($s, @b) = @$$vals;
+ $self->_assert_bindval_matches_bindtype(@b);
+ ($s, @b);
},
SCALARREF => sub {
return $$vals;
},
ARRAYREFREF => sub {
my ($sql, @bind) = @$$val;
+ $self->_assert_bindval_matches_bindtype(@bind);
return ($self->_convert($sql), @bind);
},
});
map { $self->_order_by_chunks ($_ ) } @$arg;
},
- ARRAYREFREF => sub { [ @$$arg ] },
+ ARRAYREFREF => sub {
+ my ($s, @b) = @$$arg;
+ $self->_assert_bindval_matches_bindtype(@b);
+ [ $s, @b ];
+ },
SCALAR => sub {$self->_quote($arg)},
HASHREF => sub {
# get first pair in hash
- my ($key, $val) = each %$arg;
+ my ($key, $val, @rest) = %$arg;
return () unless $key;
- if ( (keys %$arg) > 1 or not $key =~ /^-(desc|asc)/i ) {
+ if ( @rest or not $key =~ /^-(desc|asc)/i ) {
puke "hash passed to _order_by must have exactly one key (-desc or -asc)";
}
# Dies if any element of @bind is not in [colname => value] format
# if bindtype is 'columns'.
sub _assert_bindval_matches_bindtype {
- my ($self, @bind) = @_;
-
+# my ($self, @bind) = @_;
+ my $self = shift;
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]"
+ for (@_) {
+ if (!defined $_ || ref($_) ne 'ARRAY' || @$_ != 2) {
+ puke "bindtype 'columns' selected, you need to pass: [column_name => bind_value]"
}
}
}
$n_steps++ if $ref;
}
- return $ref . ('REF' x $n_steps);
+ return ($ref||'SCALAR') . ('REF' x $n_steps);
}
sub _try_refkind {
A field associated to an empty arrayref will be considered a
logical false and will generate 0=1.
+=head2 Tests for NULL values
+
+If the value part is C<undef> then this is converted to SQL <IS NULL>
+
+ my %where = (
+ user => 'nwiger',
+ status => undef,
+ );
+
+becomes:
+
+ $stmt = "WHERE user = ? AND status IS NULL";
+ @bind = ('nwiger');
+
=head2 Specific comparison operators
If you want to specify a different type of operator for your comparison,
If you wish to test against boolean columns or functions within your
database you can use the C<-bool> and C<-not_bool> operators. For
example to test the column C<is_user> being true and the column
-<is_enabled> being false you would use:-
+C<is_enabled> being false you would use:-
my %where = (
-bool => 'is_user',
$stmt = "WHERE priority < ? AND is_ready";
@bind = ('2');
+Literal SQL is also the only way to compare 2 columns to one another:
+
+ my %where = (
+ priority => { '<', 2 },
+ requestor => \'= submittor'
+ );
+
+which creates:
+
+ $stmt = "WHERE priority < ? AND requestor = submitter";
+ @bind = ('2');
=head2 Literal SQL with placeholders and bind values (subqueries)
around. On subsequent queries, simply use the C<values> function provided
by this module to return your values in the correct order.
+However this depends on the values having the same type - if, for
+example, the values of a where clause may either have values
+(resulting in sql of the form C<column = ?> with a single bind
+value), or alternatively the values might be C<undef> (resulting in
+sql of the form C<column IS NULL> with no bind value) then the
+caching technique suggested will not work.
=head1 FORMBUILDER