use Carp;
use strict;
use warnings;
-use List::Util qw/first/;
+use List::Util qw/first/;
+use Scalar::Util qw/blessed/;
#======================================================================
# GLOBALS
#======================================================================
-our $VERSION = '1.49_01';
+our $VERSION = '1.49_04';
+$VERSION = eval $VERSION; # numify for warning-free dev releases
+
our $AUTOLOAD;
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;
$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, $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, $v;
+ push @all_bind, $self->_bindtype($column, $v);
},
});
}
-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
}
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";
# dispatch on appropriate method according to refkind of $where
my $method = $self->_METHOD_FOR_refkind("_where", $where);
- $self->$method($where, $logic);
+
+
+ my ($sql, @bind) = $self->$method($where, $logic);
+
+ # DBIx::Class directly calls _recurse_where in scalar context, so
+ # we must implement it, even if not in the official API
+ return wantarray ? ($sql, @bind) : $sql;
}
# 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
UNDEF => sub {puke "not supported : UNDEF in arrayref" },
});
- push @sql_clauses, $sql;
- push @all_bind, @bind;
+ if ($sql) {
+ push @sql_clauses, $sql;
+ push @all_bind, @bind;
+ }
}
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
if ($special_op) {
($sql, @bind) = $special_op->{handler}->($self, $k, $op, $val);
}
+ else {
+ $self->_SWITCH_refkind($val, {
- # CASE: col => {op => \@vals}
- elsif (ref $val eq 'ARRAY') {
- ($sql, @bind) = $self->_where_field_op_ARRAYREF($k, $op, $val);
- }
-
- # CASE: col => {op => undef} : sql "IS (NOT)? NULL"
- elsif (! defined($val)) {
- my $is = ($op =~ $self->{equality_op}) ? 'is' :
- ($op =~ $self->{inequality_op}) ? 'is not' :
- puke "unexpected operator '$op' with undef operand";
- $sql = $self->_quote($k) . $self->_sqlcase(" $is null");
- }
+ ARRAYREF => sub { # CASE: col => {op => \@vals}
+ ($sql, @bind) = $self->_where_field_op_ARRAYREF($k, $op, $val);
+ },
- # CASE: col => {op => $scalar}
- else {
- $sql = join ' ', $self->_convert($self->_quote($k)),
- $self->_sqlcase($op),
- $self->_convert('?');
- @bind = $self->_bindtype($k, $val);
+ 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]} (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 =~ $self->{equality_op}) ? 'is' :
+ ($op =~ $self->{inequality_op}) ? 'is not' :
+ puke "unexpected operator '$op' with undef operand";
+ $sql = $self->_quote($k) . $self->_sqlcase(" $is null");
+ },
+
+ FALLBACK => sub { # CASE: col => {op => $scalar}
+ $sql = join ' ', $self->_convert($self->_quote($k)),
+ $self->_sqlcase($op),
+ $self->_convert('?');
+ @bind = $self->_bindtype($k, $val);
+ },
+ });
}
push @all_sql, $sql;
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");
# 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);
}
ARRAYREF => sub {
map {$self->_SWITCH_refkind($_, {
SCALAR => sub {$self->_quote($_)},
+ UNDEF => sub {},
SCALARREF => sub {$$_}, # literal SQL, no quoting
HASHREF => sub {$self->_order_by_hash($_)}
}) } @$arg;
},
SCALAR => sub {$self->_quote($arg)},
+ UNDEF => sub {},
SCALARREF => sub {$$arg}, # literal SQL, no quoting
HASHREF => sub {$self->_order_by_hash($arg)},
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) = @_;
my ($self, $data) = @_;
my $suffix = '';
my $ref;
+ my $n_steps = 0;
- # $suffix = 'REF' x (length of ref chain, i. e. \\[] is REFREFREF)
while (1) {
- $suffix .= 'REF';
- $ref = ref $data;
- last if $ref ne 'REF';
+ # blessed objects are treated like scalars
+ $ref = (blessed $data) ? '' : ref $data;
+ $n_steps += 1 if $ref;
+ last if $ref ne 'REF';
$data = $$data;
}
- return $ref ? $ref.$suffix :
- defined $data ? 'SCALAR' :
- 'UNDEF';
+ my $base = $ref || (defined $data ? 'SCALAR' : 'UNDEF');
+
+ return $base . ('REF' x $n_steps);
}
+
+
sub _try_refkind {
my ($self, $data) = @_;
my @try = ($self->_refkind($data));
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 {
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 = ?";
# 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 {
);
You can then iterate through this manually, using DBI's C<bind_param()>.
-
+
$sth->prepare($stmt);
my $i = 1;
for (@bind) {
sub called C<bind_fields()> or something and reuse it repeatedly. You still
get a layer of abstraction over manual SQL specification.
+Note that if you set L</bindtype> to C<columns>, the C<\[$sql, @bind]>
+construct (see L</Literal SQL with placeholders and bind values (subqueries)>)
+will expect the bind values in this format.
+
=item quote_char
This is the character that a table or column name will be quoted
of field names, will be joined by commas and quoted), or a
plain scalar (literal SQL, not quoted).
Please observe that this API is not as flexible as for
-the first argument <$table>, for backwards compatibility reasons.
+the first argument C<$table>, for backwards compatibility reasons.
=item $where
$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
-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.
You can also use the hashref format to compare a list of fields using the
-and => [
user => 'nwiger',
-nest => [
- -and => [workhrs => {'>', 20}, geo => 'ASIA' ],
- -and => [workhrs => {'<', 50}, geo => 'EURO' ]
+ ["-and", workhrs => {'>', 20}, geo => 'ASIA' ],
+ ["-and", workhrs => {'<', 50}, geo => 'EURO' ]
],
],
);
This would create:
- $stmt = "WHERE ( date_column = date \'2008-09-30\' - ?::integer )"
+ $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 C</where>. That means that if you set L</bindtype> to C<columns>, you must
+provide the bind values in the C<< [ column_meta => value ] >> format, where
+C<column_meta> is an opaque scalar value; most commonly the column name, but
+you can use any scalar scalar value (including references and blessed
+references), L<SQL::Abstract> will simply pass it through intact. So eg. 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 :
=head1 SPECIAL OPERATORS
-[to be written]
+ my $sqlmaker = SQL::Abstract->new(special_ops => [
+ {regex => qr/.../,
+ handler => sub {
+ my ($self, $field, $op, $arg) = @_;
+ ...
+ },
+ },
+ ]);
+
+A "special operator" is a SQL syntactic clause that can be
+applied to a field, instead of a usual binary operator.
+For example :
+ WHERE field IN (?, ?, ?)
+ WHERE field BETWEEN ? AND ?
+ WHERE MATCH(field) AGAINST (?, ?)
-=head1 TABLES AND JOINS
+Special operators IN and BETWEEN are fairly standard and therefore
+are builtin within C<SQL::Abstract>. For other operators,
+like the MATCH .. AGAINST example above which is
+specific to MySQL, you can write your own operator handlers :
+supply a C<special_ops> argument to the C<new> method.
+That argument takes an arrayref of operator definitions;
+each operator definition is a hashref with two entries
-[to be written]
+=over
+
+=item regex
+
+the regular expression to match the operator
+
+=item handler
+
+coderef that will be called when meeting that operator
+in the input tree. The coderef will be called with
+arguments C<< ($self, $field, $op, $arg) >>, and
+should return a C<< ($sql, @bind) >> structure.
+
+=back
+
+For example, here is an implementation
+of the MATCH .. AGAINST syntax for MySQL
+
+ my $sqlmaker = SQL::Abstract->new(special_ops => [
+
+ # special op for MySql MATCH (field) AGAINST(word1, word2, ...)
+ {regex => qr/^match$/i,
+ handler => sub {
+ my ($self, $field, $op, $arg) = @_;
+ $arg = [$arg] if not ref $arg;
+ my $label = $self->_quote($field);
+ my ($placeholder) = $self->_convert('?');
+ my $placeholders = join ", ", (($placeholder) x @$arg);
+ my $sql = $self->_sqlcase('match') . " ($label) "
+ . $self->_sqlcase('against') . " ($placeholders) ";
+ my @bind = $self->_bindtype($field, @$arg);
+ return ($sql, @bind);
+ }
+ },
+
+ ]);
=head1 PERFORMANCE
on some dark areas of C<SQL::Abstract> v1.*
B<might behave differently> in v1.50.
-=head1 Public changes
+The main changes are :
=over
=item *
+support for the { operator => \"..." } construct (to embed literal SQL)
+
+=item *
+
+support for the { operator => \["...", @bind] } construct (to embed literal SQL with bind values)
+
+=item *
+
added -nest1, -nest2 or -nest_1, -nest_2, ...
=item *
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)
+ Norbert Buchmuller (support for literal SQL in hashpair, misc. fixes & tests)
Thanks!