use Carp;
use strict;
use warnings;
-use List::Util qw/first/;
-use Scalar::Util qw/blessed/;
+use List::Util ();
+use Scalar::Util ();
#======================================================================
# GLOBALS
#======================================================================
-our $VERSION = '1.51';
+our $VERSION = '1.64';
# This would confuse some packagers
#$VERSION = eval $VERSION; # numify for warning-free dev releases
# special operators (-in, -between). May be extended/overridden by user.
# See section WHERE: BUILTIN SPECIAL OPERATORS below for implementation
my @BUILTIN_SPECIAL_OPS = (
- {regex => qr/^(not )?between$/i, handler => \&_where_field_BETWEEN},
- {regex => qr/^(not )?in$/i, handler => \&_where_field_IN},
+ {regex => qr/^(not )?between$/i, handler => '_where_field_BETWEEN'},
+ {regex => qr/^(not )?in$/i, handler => '_where_field_IN'},
+);
+
+# 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' },
);
#======================================================================
# try to recognize which are the 'equality' and 'unequality' ops
# (temporary quickfix, should go through a more seasoned API)
- $opt{equality_op} = qr/^(\Q$opt{cmp}\E|is|(is\s+)?like)$/i;
- $opt{inequality_op} = qr/^(!=|<>|(is\s+)?not(\s+like)?)$/i;
+ $opt{equality_op} = qr/^(\Q$opt{cmp}\E|is|(is\s+)?like)$/i;
+ $opt{inequality_op} = qr/^(!=|<>|(is\s+)?not(\s+like)?)$/i;
# SQL booleans
$opt{sqltrue} ||= '1=1';
$opt{sqlfalse} ||= '0=1';
- # special operators
+ # special operators
$opt{special_ops} ||= [];
push @{$opt{special_ops}}, @BUILTIN_SPECIAL_OPS;
+ # unary operators
+ $opt{unary_ops} ||= [];
+ push @{$opt{unary_ops}}, @BUILTIN_UNARY_OPS;
+
return bless \%opt, $class;
}
#======================================================================
sub insert {
- my $self = shift;
- my $table = $self->_table(shift);
- my $data = shift || return;
+ my $self = shift;
+ my $table = $self->_table(shift);
+ my $data = shift || return;
+ my $options = shift;
my $method = $self->_METHOD_FOR_refkind("_insert", $data);
- my ($sql, @bind) = $self->$method($data);
+ my ($sql, @bind) = $self->$method($data);
$sql = join " ", $self->_sqlcase('insert into'), $table, $sql;
+
+ if (my $ret = $options->{returning}) {
+ $sql .= $self->_insert_returning ($ret);
+ }
+
return wantarray ? ($sql, @bind) : $sql;
}
+sub _insert_returning {
+ my ($self, $fields) = @_;
+
+ my $f = $self->_SWITCH_refkind($fields, {
+ ARRAYREF => sub {join ', ', map { $self->_quote($_) } @$fields;},
+ SCALAR => sub {$self->_quote($fields)},
+ SCALARREF => sub {$$fields},
+ });
+ return join (' ', $self->_sqlcase(' returning'), $f);
+}
+
sub _insert_HASHREF { # explicit list of fields and then values
my ($self, $data) = @_;
$self->_SWITCH_refkind($v, {
- ARRAYREF => sub {
+ ARRAYREF => sub {
if ($self->{array_datatypes}) { # if array datatype are activated
push @values, '?';
push @all_bind, $self->_bindtype($column, $v);
push @all_bind, @bind;
},
- # THINK : anything useful to do with a HASHREF ?
+ # 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";
my $label = $self->_quote($k);
$self->_SWITCH_refkind($v, {
- ARRAYREF => sub {
+ ARRAYREF => sub {
if ($self->{array_datatypes}) { # array datatype
push @set, "$label = ?";
push @all_bind, $self->_bindtype($k, $v);
my $f = (ref $fields eq 'ARRAY') ? join ', ', map { $self->_quote($_) } @$fields
: $fields;
- my $sql = join(' ', $self->_sqlcase('select'), $f,
+ my $sql = join(' ', $self->_sqlcase('select'), $f,
$self->_sqlcase('from'), $table)
. $where_sql;
- return wantarray ? ($sql, @bind) : $sql;
+ return wantarray ? ($sql, @bind) : $sql;
}
#======================================================================
my($where_sql, @bind) = $self->where($where);
my $sql = $self->_sqlcase('delete from') . " $table" . $where_sql;
- return wantarray ? ($sql, @bind) : $sql;
+ return wantarray ? ($sql, @bind) : $sql;
}
$sql .= $self->_order_by($order);
}
- return wantarray ? ($sql, @bind) : $sql;
+ return wantarray ? ($sql, @bind) : $sql;
}
# dispatch on appropriate method according to refkind of $where
my $method = $self->_METHOD_FOR_refkind("_where", $where);
+ my ($sql, @bind) = $self->$method($where, $logic);
- my ($sql, @bind) = $self->$method($where, $logic);
-
- # DBIx::Class directly calls _recurse_where in scalar context, so
+ # 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;
+ return wantarray ? ($sql, @bind) : $sql;
}
my (@sql_clauses, @all_bind);
# need to use while() so can shift() for pairs
- while (my $el = shift @clauses) {
+ while (my $el = shift @clauses) {
# switch according to kind of $el and get corresponding ($sql, @bind)
my ($sql, @bind) = $self->_SWITCH_refkind($el, {
# LDNOTE : previous SQLA code for hashrefs was creating a dirty
# side-effect: the first hashref within an array would change
# the global logic to 'AND'. So [ {cond1, cond2}, [cond3, cond4] ]
- # was interpreted as "(cond1 AND cond2) OR (cond3 AND cond4)",
+ # was interpreted as "(cond1 AND cond2) OR (cond3 AND cond4)",
# whereas it should be "(cond1 AND cond2) OR (cond3 OR cond4)".
SCALARREF => sub { ($$el); },
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) {
+ for my $k (sort keys %$where) {
my $v = $where->{$k};
- # ($k => $v) is either a special op or a regular hashpair
- my ($sql, @bind) = ($k =~ /^-(.+)/) ? $self->_where_op_in_hash($1, $v)
- : do {
- my $method = $self->_METHOD_FOR_refkind("_where_hashpair", $v);
- $self->$method($k, $v);
- };
+ # ($k => $v) is either a special unary op or a regular hashpair
+ my ($sql, @bind) = do {
+ if ($k =~ /^-./) {
+ # put the operator in canonical form
+ my $op = $k;
+ $op =~ s/^-//; # remove initial dash
+ $op =~ s/[_\t ]+/ /g; # underscores and whitespace become single spaces
+ $op =~ s/^\s+|\s+$//g;# remove leading/trailing space
+
+ $self->_debug("Unary OP(-$op) within hashref, recursing...");
+
+ my $op_entry = List::Util::first {$op =~ $_->{regex}} @{$self->{unary_ops}};
+ if (my $handler = $op_entry->{handler}) {
+ if (not ref $handler) {
+ if ($op =~ s/\s?\d+$//) {
+ belch 'Use of [and|or|nest]_N modifiers is deprecated and will be removed in SQLA v2.0. '
+ . "You probably wanted ...-and => [ -$op => COND1, -$op => COND2 ... ]";
+ }
+ $self->$handler ($op, $v);
+ }
+ elsif (ref $handler eq 'CODE') {
+ $handler->($self, $op, $v);
+ }
+ else {
+ puke "Illegal handler for operator $k - expecting a method name or a coderef";
+ }
+ }
+ 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, @bind);
+ }
+ }
+ else {
+ my $method = $self->_METHOD_FOR_refkind("_where_hashpair", $v);
+ $self->$method($k, $v);
+ }
+ };
push @sql_clauses, $sql;
push @all_bind, @bind;
return $self->_join_sql_clauses('and', \@sql_clauses, \@all_bind);
}
+sub _where_func_generic {
+ my ($self, $op, $rhs) = @_;
-sub _where_op_in_hash {
- my ($self, $op_str, $v) = @_;
+ my ($sql, @bind) = $self->_SWITCH_refkind ($rhs, {
+ SCALAR => sub {
+ puke "Illegal use of top-level '$op'"
+ unless $self->{_nested_func_lhs};
- $op_str =~ /^ (AND|OR|NEST) ( \_? \d* ) $/xi
- or puke "unknown operator: -$op_str";
+ return (
+ $self->_convert('?'),
+ $self->_bindtype($self->{_nested_func_lhs}, $rhs)
+ );
+ },
+ FALLBACK => sub {
+ $self->_recurse_where ($rhs)
+ },
+ });
- my $op = uc($1); # uppercase, remove trailing digits
- if ($2) {
- belch 'Use of [and|or|nest]_N modifiers is deprecated and will be removed in SQLA v2.0. '
- . "You probably wanted ...-and => [ $op_str => COND1, $op_str => COND2 ... ]";
- }
+ $sql = sprintf ('%s %s',
+ $self->_sqlcase($op),
+ $sql,
+ );
- $self->_debug("OP(-$op) within hashref, recursing...");
+ return ($sql, @bind);
+}
- $self->_SWITCH_refkind($v, {
+sub _where_op_ANDOR {
+ my ($self, $op, $v) = @_;
+ $self->_SWITCH_refkind($v, {
ARRAYREF => sub {
- return $self->_where_ARRAYREF($v, $op eq 'NEST' ? '' : $op);
+ return $self->_where_ARRAYREF($v, $op);
},
HASHREF => sub {
- if ($op eq 'OR') {
- return $self->_where_ARRAYREF([%$v], 'OR');
- }
- else { # NEST | AND
- return $self->_where_HASHREF($v);
- }
+ return ( $op =~ /^or/i )
+ ? $self->_where_ARRAYREF( [ map { $_ => $v->{$_} } ( sort keys %$v ) ], $op )
+ : $self->_where_HASHREF($v);
+ },
+
+ SCALARREF => sub {
+ puke "-$op => \\\$scalar not supported, use -nest => ...";
},
- SCALARREF => sub { # literal SQL
- $op eq 'NEST'
- or puke "-$op => \\\$scalar not supported, use -nest => ...";
- return ($$v);
+ ARRAYREFREF => sub {
+ puke "-$op => \\[..] not supported, use -nest => ...";
},
- ARRAYREFREF => sub { # literal SQL
- $op eq 'NEST'
- or puke "-$op => \\[..] not supported, use -nest => ...";
- return @{${$v}};
+ SCALAR => sub { # permissively interpreted as SQL
+ puke "-$op => 'scalar' not supported, use -nest => \\'scalar'";
},
+ UNDEF => sub {
+ puke "-$op => undef not supported";
+ },
+ });
+}
+
+sub _where_op_NEST {
+ my ($self, $op, $v) = @_;
+
+ $self->_SWITCH_refkind($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' ";
- return ($v);
+ return ($v);
},
UNDEF => sub {
puke "-$op => undef not supported";
},
+
+ FALLBACK => sub {
+ $self->_recurse_where ($v);
+ },
+
});
}
+sub _where_op_BOOL {
+ my ($self, $op, $v) = @_;
+
+ my ( $prefix, $suffix ) = ( $op =~ /\bnot\b/i )
+ ? ( '(NOT ', ')' )
+ : ( '', '' );
+
+ my ($sql, @bind) = do {
+ $self->_SWITCH_refkind($v, {
+ SCALAR => sub { # interpreted as SQL column
+ $self->_convert($self->_quote($v));
+ },
+
+ UNDEF => sub {
+ puke "-$op => undef not supported";
+ },
+
+ FALLBACK => sub {
+ $self->_recurse_where ($v);
+ },
+ });
+ };
+
+ return (
+ join ('', $prefix, $sql, $suffix),
+ @bind,
+ );
+}
+
+
sub _where_hashpair_ARRAYREF {
my ($self, $k, $v) = @_;
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?
$self->_debug("empty ARRAY($k) means 0=1");
my ($self, $k, $v, $logic) = @_;
$logic ||= 'and';
+ local $self->{_nested_func_lhs} = $self->{_nested_func_lhs};
+
my ($all_sql, @all_bind);
- for my $op (sort keys %$v) {
- my $val = $v->{$op};
+ for my $orig_op (sort keys %$v) {
+ my $val = $v->{$orig_op};
# put the operator in canonical form
- $op =~ s/^-//; # remove initial dash
- $op =~ tr/_/ /; # underscores become spaces
- $op =~ s/^\s+//; # no initial space
- $op =~ s/\s+$//; # no final space
- $op =~ s/\s+/ /; # multiple spaces become one
+ my $op = $orig_op;
+ $op =~ s/^-//; # remove initial dash
+ $op =~ s/[_\t ]+/ /g; # underscores and whitespace become single spaces
+ $op =~ s/^\s+|\s+$//g;# remove leading/trailing space
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
- my $special_op = first {$op =~ $_->{regex}} @{$self->{special_ops}};
- if ($special_op) {
- ($sql, @bind) = $special_op->{handler}->($self, $k, $op, $val);
+ 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, {
($sql, @bind) = $self->_where_field_op_ARRAYREF($k, $op, $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);
@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' :
- puke "unexpected operator '$op' with undef operand";
+ puke "unexpected operator '$orig_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);
+
+ FALLBACK => sub { # CASE: col => {op/func => $stuff}
+
+ # retain for proper column type bind
+ $self->{_nested_func_lhs} ||= $k;
+
+ ($sql, @bind) = $self->_where_func_generic ($op, $val);
+
+ $sql = join (' ',
+ $self->_convert($self->_quote($k)),
+ $self->{_nested_func_lhs} eq $k ? $sql : "($sql)", # top level vs nested
+ );
},
});
}
sub _where_field_op_ARRAYREF {
my ($self, $k, $op, $vals) = @_;
- if(@$vals) {
- $self->_debug("ARRAY($vals) means multiple elements: [ @$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 ($vals->[0] =~ /^ - ( AND|OR ) $/ix) {
+ if (defined $vals[0] && $vals[0] =~ /^ - ( AND|OR ) $/ix) {
$logic = uc $1;
- shift @$vals;
+ shift @vals;
}
- # distribute $op over each remaining member of @$vals, append logic if exists
- return $self->_recurse_where([map { {$k => {$op, $_}} } @$vals], $logic);
+ # distribute $op over each remaining member of @vals, append logic if exists
+ return $self->_recurse_where([map { {$k => {$op, $_}} } @vals], $logic);
- # LDNOTE : had planned to change the distribution logic when
- # $op =~ $self->{inequality_op}, because of Morgan laws :
+ # 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 OR field != 33 : the user probably means
# WHERE field != 22 AND field != 33.
# 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);
+ # return $self->_recurse_where([map { {$k => {$op, $_}} } @vals], $logic);
- }
+ }
else {
- # try to DWIM on equality operators
+ # try to DWIM on equality operators
# LDNOTE : not 100% sure this is the correct thing to do ...
return ($self->{sqlfalse}) if $op =~ $self->{equality_op};
return ($self->{sqltrue}) if $op =~ $self->{inequality_op};
sub _where_hashpair_SCALAR {
my ($self, $k, $v) = @_;
$self->_debug("NOREF($k) means simple key=val: $k $self->{cmp} $v");
- my $sql = join ' ', $self->_convert($self->_quote($k)),
- $self->_sqlcase($self->{cmp}),
+ my $sql = join ' ', $self->_convert($self->_quote($k)),
+ $self->_sqlcase($self->{cmp}),
$self->_convert('?');
my @bind = $self->_bindtype($k, $v);
return ( $sql, @bind);
sub _where_field_BETWEEN {
my ($self, $k, $op, $vals) = @_;
- (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 ($clause, @bind, $label, $and, $placeholder);
+ my ($label, $and, $placeholder);
$label = $self->_convert($self->_quote($k));
$and = ' ' . $self->_sqlcase('and') . ' ';
$placeholder = $self->_convert('?');
$op = $self->_sqlcase($op);
- 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;
- }
+ my ($clause, @bind) = $self->_SWITCH_refkind($vals, {
+ ARRAYREFREF => sub {
+ return @$$vals;
+ },
+ SCALARREF => sub {
+ return $$vals;
+ },
+ ARRAYREF => sub {
+ puke "special op 'between' accepts an arrayref with exactly two values"
+ if @$vals != 2;
+
+ 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), ());
+ },
+ ARRAYREFREF => sub {
+ my ($sql, @bind) = @$$val;
+ return ($self->_convert($sql), @bind);
+ },
+ });
+ push @all_sql, $sql;
+ push @all_bind, @bind;
+ }
+
+ return (
+ (join $and, @all_sql),
+ $self->_bindtype($k, @all_bind),
+ );
+ },
+ FALLBACK => sub {
+ puke "special op 'between' accepts an arrayref with two values, or a single literal scalarref/arrayref-ref";
+ },
+ });
- $clause = (join $and, @all_sql);
- @bind = $self->_bindtype($k, @all_bind);
- }
my $sql = "( $label $op $clause )";
return ($sql, @bind)
}
}
},
+ 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);
},
FALLBACK => sub {
- puke "special op 'in' requires an arrayref (or arrayref-ref)";
+ puke "special op 'in' 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
+sub _open_outer_paren {
+ my ($self, $sql) = @_;
+ $sql = $1 while $sql =~ /^ \s* \( (.*) \) \s* $/xs;
+ return $sql;
+}
#======================================================================
sub _order_by {
my ($self, $arg) = @_;
- # construct list of ordering instructions
- my @order = $self->_SWITCH_refkind($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 },
+ });
+ }
+
+ my $sql = @sql
+ ? sprintf ('%s %s',
+ $self->_sqlcase(' order by'),
+ join (', ', @sql)
+ )
+ : ''
+ ;
+
+ return wantarray ? ($sql, @bind) : $sql;
+}
+
+sub _order_by_chunks {
+ my ($self, $arg) = @_;
+
+ return $self->_SWITCH_refkind($arg, {
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;
+ map { $self->_order_by_chunks ($_ ) } @$arg;
},
+ ARRAYREFREF => sub { [ @$$arg ] },
+
SCALAR => sub {$self->_quote($arg)},
- UNDEF => sub {},
+
+ UNDEF => sub {return () },
+
SCALARREF => sub {$$arg}, # literal SQL, no quoting
- HASHREF => sub {$self->_order_by_hash($arg)},
- });
+ HASHREF => sub {
+ # get first pair in hash
+ my ($key, $val) = each %$arg;
- # build SQL
- my $order = join ', ', @order;
- return $order ? $self->_sqlcase(' order by')." $order" : '';
-}
+ return () unless $key;
+ if ( (keys %$arg) > 1 or not $key =~ /^-(desc|asc)/i ) {
+ puke "hash passed to _order_by must have exactly one key (-desc or -asc)";
+ }
-sub _order_by_hash {
- my ($self, $hash) = @_;
+ my $direction = $1;
- # get first pair in hash
- my ($key, $val) = each %$hash;
+ my @ret;
+ for my $c ($self->_order_by_chunks ($val)) {
+ my ($sql, @bind);
+
+ $self->_SWITCH_refkind ($c, {
+ SCALAR => sub {
+ $sql = $c;
+ },
+ ARRAYREF => sub {
+ ($sql, @bind) = @$c;
+ },
+ });
- # check if one pair was found and no other pair in hash
- $key && !(each %$hash)
- or puke "hash passed to _order_by must have exactly one key (-desc or -asc)";
+ $sql = $sql . ' ' . $self->_sqlcase($direction);
- my ($order) = ($key =~ /^-(desc|asc)/i)
- or puke "invalid key in _order_by hash : $key";
+ push @ret, [ $sql, @bind];
+ }
- $val = ref $val eq 'ARRAY' ? $val : [$val];
- return join ', ', map { $self->_quote($_) . ' ' . $self->_sqlcase($order) } @$val;
+ return @ret;
+ },
+ });
}
-
#======================================================================
# DATASOURCE (FOR NOW, JUST PLAIN TABLE OR LIST OF TABLES)
#======================================================================
# do the quoting, except for "*" or for `table`.*
my @quoted = map { $_ eq '*' ? $_: $ql.$_.$qr} @to_quote;
- # reassemble and return.
+ # reassemble and return.
return join $sep, @quoted;
}
# LDNOTE : modified the previous implementation below because
# it was not consistent : the first "return" is always an array,
# the second "return" is context-dependent. Anyway, _convert
-# seems always used with just a single argument, so make it a
+# seems always used with just a single argument, so make it a
# scalar function.
# return @_ unless $self->{convert};
# my $conv = $self->_sqlcase($self->{convert});
my $self = shift;
my($col, @vals) = @_;
- #LDNOTE : changed original implementation below because it did not make
+ #LDNOTE : changed original implementation below because it did not make
# sense when bindtype eq 'columns' and @vals > 1.
# return $self->{bindtype} eq 'columns' ? [ $col, @vals ] : @vals;
while (1) {
# blessed objects are treated like scalars
- $ref = (blessed $data) ? '' : ref $data;
+ $ref = (Scalar::Util::blessed $data) ? '' : ref $data;
$n_steps += 1 if $ref;
last if $ref ne 'REF';
$data = $$data;
sub _METHOD_FOR_refkind {
my ($self, $meth_prefix, $data) = @_;
- my $method = first {$_} map {$self->can($meth_prefix."_".$_)}
- $self->_try_refkind($data)
- or puke "cannot dispatch on '$meth_prefix' for ".$self->_refkind($data);
- return $method;
+
+ my $method;
+ for ($self->_try_refkind($data)) {
+ $method = $self->can($meth_prefix."_".$_)
+ and last;
+ }
+
+ return $method || puke "cannot dispatch on '$meth_prefix' for ".$self->_refkind($data);
}
sub _SWITCH_refkind {
my ($self, $data, $dispatch_table) = @_;
- my $coderef = first {$_} map {$dispatch_table->{$_}}
- $self->_try_refkind($data)
- or puke "no dispatch entry for ".$self->_refkind($data);
+ my $coderef;
+ for ($self->_try_refkind($data)) {
+ $coderef = $dispatch_table->{$_}
+ and last;
+ }
+
+ puke "no dispatch entry for ".$self->_refkind($data)
+ unless $coderef;
+
$coderef->();
}
foreach my $k ( sort keys %$data ) {
my $v = $data->{$k};
$self->_SWITCH_refkind($v, {
- ARRAYREF => sub {
+ ARRAYREF => sub {
if ($self->{array_datatypes}) { # array datatype
push @all_bind, $self->_bindtype($k, $v);
}
} elsif ($r eq 'SCALAR') {
# literal SQL without bind
push @sqlq, "$label = $$v";
- } else {
+ } else {
push @sqlq, "$label = ?";
push @sqlv, $self->_bindtype($k, $v);
}
} elsif ($r eq 'SCALAR') { # literal SQL without bind
# embedded literal SQL
push @sqlq, $$v;
- } else {
+ } else {
push @sqlq, '?';
push @sqlv, $v;
}
If your database has array types (like for example Postgres),
activate the special option C<< array_datatypes => 1 >>
-when creating the C<SQL::Abstract> object.
+when creating the C<SQL::Abstract> object.
Then you may use an arrayref to insert and update database array types:
my $sql = SQL::Abstract->new(array_datatypes => 1);
my %data = (
planets => [qw/Mercury Venus Earth Mars/]
);
-
+
my($stmt, @bind) = $sql->insert('solar_system', \%data);
This results in:
my %data = (
name => 'Bill',
date_entered => \["to_date(?,'MM/DD/YYYY')", "03/02/2003"],
- );
+ );
The first value in the array is the actual SQL. Any other values are
optional and would be included in the bind values array. This gives
my($stmt, @bind) = $sql->insert('people', \%data);
- $stmt = "INSERT INTO people (name, date_entered)
+ $stmt = "INSERT INTO people (name, date_entered)
VALUES (?, to_date(?,'MM/DD/YYYY'))";
@bind = ('Bill', '03/02/2003');
The functions are simple. There's one for each major SQL operation,
and a constructor you use first. The arguments are specified in a
-similar order to each function (table, then fields, then a where
+similar order to each function (table, then fields, then a where
clause) to try and simplify things.
array of the form:
@where = (
- event_date => {'>=', '2/13/99'},
- event_date => {'<=', '4/24/03'},
+ event_date => {'>=', '2/13/99'},
+ event_date => {'<=', '4/24/03'},
);
will generate SQL like this:
The logic can also be changed locally by inserting
a modifier in front of an arrayref :
- @where = (-and => [event_date => {'>=', '2/13/99'},
+ @where = (-and => [event_date => {'>=', '2/13/99'},
event_date => {'<=', '4/24/03'} ]);
See the L</"WHERE CLAUSES"> section for explanations.
=item quote_char
This is the character that a table or column name will be quoted
-with. By default this is an empty string, but you could set it to
+with. By default this is an empty string, but you could set it to
the character C<`>, to generate SQL like this:
SELECT `a_field` FROM `a_table` WHERE `some_field` LIKE '%someval%'
SELECT [a_field] FROM [a_table] WHERE [some_field] LIKE '%someval%'
-Quoting is useful if you have tables or columns names that are reserved
+Quoting is useful if you have tables or columns names that are reserved
words in your database's SQL dialect.
=item name_sep
=item array_datatypes
-When this option is true, arrayrefs in INSERT or UPDATE are
-interpreted as array datatypes and are passed directly
+When this option is true, arrayrefs in INSERT or UPDATE are
+interpreted as array datatypes and are passed directly
to the DBI layer.
When this option is false, arrayrefs are interpreted
as literal SQL, just like refs to arrayrefs
=item special_ops
-Takes a reference to a list of "special operators"
+Takes a reference to a list of "special operators"
to extend the syntax understood by L<SQL::Abstract>.
See section L</"SPECIAL OPERATORS"> for details.
+=item unary_ops
+
+Takes a reference to a list of "unary operators"
+to extend the syntax understood by L<SQL::Abstract>.
+See section L</"UNARY OPERATORS"> for details.
+
=back
-=head2 insert($table, \@values || \%fieldvals)
+=head2 insert($table, \@values || \%fieldvals, \%options)
This is the simplest function. You simply give it a table name
and either an arrayref of values or hashref of field/value pairs.
L</"Inserting and Updating SQL"> for information on how to insert
with those data types.
+The optional C<\%options> hash reference may contain additional
+options to generate the insert SQL. Currently supported options
+are:
+
+=over 4
+
+=item returning
+
+Takes either a scalar of raw SQL fields, or an array reference of
+field names, and adds on an SQL C<RETURNING> statement at the end.
+This allows you to return data generated by the insert statement
+(such as row IDs) without performing another C<SELECT> statement.
+Note, however, this is not part of the SQL standard and may not
+be supported by all database engines.
+
+=back
+
=head2 update($table, \%fieldvals, \%where)
This takes a table, hashref of field/value pairs, and an optional
=head2 select($source, $fields, $where, $order)
-This returns a SQL SELECT statement and associated list of bind values, as
+This returns a SQL SELECT statement and associated list of bind values, as
specified by the arguments :
=over
=item $source
-Specification of the 'FROM' part of the statement.
+Specification of the 'FROM' part of the statement.
The argument can be either a plain scalar (interpreted as a table
name, will be quoted), or an arrayref (interpreted as a list
of table names, joined by commas, quoted), or a scalarref
=item $fields
-Specification of the list of fields to retrieve from
+Specification of the list of fields to retrieve from
the source.
The argument can be either an arrayref (interpreted as a list
-of field names, will be joined by commas and quoted), or a
+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 C<$table>, for backwards compatibility reasons.
Optional argument to specify the WHERE part of the query.
The argument is most often a hashref, but can also be
-an arrayref or plain scalar --
+an arrayref or plain scalar --
see section L<WHERE clause|/"WHERE CLAUSES"> for details.
=item $order
Optional argument to specify the ORDER BY part of the query.
-The argument can be a scalar, a hashref or an arrayref
+The argument can be a scalar, a hashref or an arrayref
-- see section L<ORDER BY clause|/"ORDER BY CLAUSES">
for details.
);
This simple code will create the following:
-
+
$stmt = "WHERE user = ? AND ( status = ? OR status = ? OR status = ? )";
@bind = ('nwiger', 'assigned', 'in-progress', 'pending');
-A field associated to an empty arrayref will be considered a
+A field associated to an empty arrayref will be considered a
logical false and will generate 0=1.
=head2 Specific comparison operators
As the second C<!=> key will obliterate the first. The solution
is to use the special C<-modifier> form inside an arrayref:
- priority => [ -and => {'!=', 2},
+ priority => [ -and => {'!=', 2},
{'!=', 1} ]
$stmt = "WHERE status = ? AND reportid IN (?,?,?)";
@bind = ('completed', '567', '2335', '2');
-The reverse operator C<-not_in> generates SQL C<NOT IN> and is used in
+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>).
+In addition to the array you can supply a chunk of literal sql or
+literal sql with bind:
+
+ my %where = {
+ customer => { -in => \[
+ 'SELECT cust_id FROM cust WHERE balance > ?',
+ 2000,
+ ],
+ status => { -in => \'SELECT status_codes FROM states' },
+ };
+
+would generate:
+ $stmt = "WHERE (
+ customer IN ( SELECT cust_id FROM cust WHERE balance > ? )
+ AND status IN ( SELECT status_codes FROM states )
+ )";
+ @bind = ('2000');
-Another pair of operators is C<-between> and C<-not_between>,
+
+
+Another pair of operators is C<-between> and C<-not_between>,
used with an arrayref of two values:
my %where = (
WHERE user = ? AND completion_date NOT BETWEEN ( ? AND ? )
-These are the two builtin "special operators"; but the
+Just like with C<-in> all plausible combinations of literal SQL
+are possible:
+
+ my %where = {
+ start0 => { -between => [ 1, 2 ] },
+ start1 => { -between => \["? AND ?", 1, 2] },
+ start2 => { -between => \"lower(x) AND upper(y)" },
+ start3 => { -between => [
+ \"lower(x)",
+ \["upper(?)", 'stuff' ],
+ ] },
+ };
+
+Would give you:
+
+ $stmt = "WHERE (
+ ( start0 BETWEEN ? AND ? )
+ AND ( start1 BETWEEN ? AND ? )
+ AND ( start2 BETWEEN lower(x) AND upper(y) )
+ AND ( start3 BETWEEN lower(x) AND upper(?) )
+ )";
+ @bind = (1, 2, 1, 2, 'stuff');
+
+
+These are the two builtin "special operators"; but the
list can be expanded : see section L</"SPECIAL OPERATORS"> below.
+=head2 Unary operators: bool
+
+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:-
+
+ my %where = (
+ -bool => 'is_user',
+ -not_bool => 'is_enabled',
+ );
+
+Would give you:
+
+ WHERE is_user AND NOT is_enabled
+
+If a more complex combination is required, testing more conditions,
+then you should use the and/or operators:-
+
+ my %where = (
+ -and => [
+ -bool => 'one',
+ -bool => 'two',
+ -bool => 'three',
+ -not_bool => 'four',
+ ],
+ );
+
+Would give you:
+
+ WHERE one AND two AND three AND NOT four
+
+
=head2 Nested conditions, -and/-or prefixes
So far, we've seen how multiple conditions are joined with a top-level
That would yield:
- WHERE ( user = ? AND
+ WHERE ( user = ? AND
( ( workhrs > ? AND geo = ? )
OR ( workhrs < ? AND geo = ? ) ) )
yielding
- WHERE ( ( ( a = ? AND b = ? )
- OR ( c = ? OR d = ? )
+ 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'}]}
+ {col => [-and => {-like => 'foo%'}, {-like => '%bar'}]}
# yields : WHERE ( ( col LIKE ? AND col LIKE ? ) )
- [-and => {col => {-like => 'foo%'}, {col => {-like => '%bar'}}]]
+ [-and => {col => {-like => 'foo%'}, {col => {-like => '%bar'}}]]
# yields : WHERE ( ( col LIKE ? OR col LIKE ? ) )
);
-TMTOWTDI.
+TMTOWTDI
-Conditions on boolean columns can be expressed in the
-same way, passing a reference to an empty string :
+Conditions on boolean columns can be expressed in the same way, passing
+a reference to an empty string, however using liternal SQL in this way
+is deprecated - the preferred method is to use the boolean operators -
+see L</"Unary operators: bool"> :
my %where = (
priority => { '<', 2 },
This yields :
- $stmt = "WHERE (foo = ? AND bar IN (SELECT c1 FROM t1
+ $stmt = "WHERE (foo = ? AND bar IN (SELECT c1 FROM t1
WHERE c2 < ? AND c3 LIKE ?))";
@bind = (1234, 100, "foo%");
-Other subquery operators, like for example C<"E<gt> ALL"> or C<"NOT IN">,
+Other subquery operators, like for example C<"E<gt> ALL"> or C<"NOT IN">,
are expressed in the same way. Of course the C<$sub_stmt> and
-its associated bind values can be generated through a former call
+its associated bind values can be generated through a former call
to C<select()> :
my ($sub_stmt, @sub_bind)
- = $sql->select("t1", "c1", {c2 => {"<" => 100},
+ = $sql->select("t1", "c1", {c2 => {"<" => 100},
c3 => {-like => "foo%"}});
my %where = (
foo => 1234,
);
In the examples above, the subquery was used as an operator on a column;
-but the same principle also applies for a clause within the main C<%where>
+but the same principle also applies for a clause within the main C<%where>
hash, like an EXISTS subquery :
- my ($sub_stmt, @sub_bind)
+ my ($sub_stmt, @sub_bind)
= $sql->select("t1", "*", {c1 => 1, c2 => \"> t0.c0"});
my %where = (
foo => 1234,
which yields
- $stmt = "WHERE (foo = ? AND EXISTS (SELECT * FROM t1
+ $stmt = "WHERE (foo = ? AND EXISTS (SELECT * FROM t1
WHERE c1 = ? AND c2 > t0.c0))";
@bind = (1234, 1);
-Observe that the condition on C<c2> in the subquery refers to
-column C<t0.c0> of the main query : this is I<not> a bind
-value, so we have to express it through a scalar ref.
+Observe that the condition on C<c2> in the subquery refers to
+column C<t0.c0> of the main query : this is I<not> a bind
+value, so we have to express it through a scalar ref.
Writing C<< c2 => {">" => "t0.c0"} >> would have generated
C<< c2 > ? >> with bind value C<"t0.c0"> ... not exactly
what we wanted here.
Another use of the subquery technique is when some SQL clauses need
parentheses, as it often occurs with some proprietary SQL extensions
-like for example fulltext expressions, geospatial expressions,
+like for example fulltext expressions, geospatial expressions,
NATIVE clauses, etc. Here is an example of a fulltext query in MySQL :
my %where = (
Finally, here is an example where a subquery is used
for expressing unary negation:
- my ($sub_stmt, @sub_bind)
+ my ($sub_stmt, @sub_bind)
= $sql->where({age => [{"<" => 10}, {">" => 20}]});
$sub_stmt =~ s/^ where //i; # don't want "WHERE" in the subclause
my %where = (
=head1 ORDER BY CLAUSES
-Some functions take an order by clause. This can either be a scalar (just a
+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:
|
['colA', {-asc => 'colB'}] | ORDER BY colA, colB ASC
|
- { -asc => [qw/colA colB] } | ORDER BY colA ASC, colB ASC
+ { -asc => [qw/colA colB/] } | ORDER BY colA ASC, colB ASC
|
[ |
{ -asc => 'colA' }, | ORDER BY colA ASC, colB DESC,
=head1 SPECIAL OPERATORS
my $sqlmaker = SQL::Abstract->new(special_ops => [
- {regex => qr/.../,
+ {
+ regex => qr/.../,
handler => sub {
my ($self, $field, $op, $arg) = @_;
...
- },
+ },
+ },
+ {
+ regex => qr/.../,
+ handler => 'method_name',
},
]);
-A "special operator" is a SQL syntactic clause that can be
+A "special operator" is a SQL syntactic clause that can be
applied to a field, instead of a usual binary operator.
-For example :
+For example :
WHERE field IN (?, ?, ?)
WHERE field BETWEEN ? AND ?
WHERE MATCH(field) AGAINST (?, ?)
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
+are builtin within C<SQL::Abstract> (as the overridable methods
+C<_where_field_IN> and C<_where_field_BETWEEN>). 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:
=over
=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.
+Either a coderef or a plain scalar method name. In both cases
+the expected return is C<< ($sql, @bind) >>.
+
+When supplied with a method name, it is simply called on the
+L<SQL::Abstract/> object as:
+
+ $self->$method_name ($field, $op, $arg)
+
+ Where:
+
+ $op is the part that matched the handler regex
+ $field is the LHS of the operator
+ $arg is the RHS
+
+When supplied with a coderef, it is called as:
+
+ $coderef->($self, $field, $op, $arg)
+
=back
-For example, here is an implementation
+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,
+ {regex => qr/^match$/i,
handler => sub {
my ($self, $field, $op, $arg) = @_;
$arg = [$arg] if not ref $arg;
return ($sql, @bind);
}
},
-
+
]);
+=head1 UNARY OPERATORS
+
+ my $sqlmaker = SQL::Abstract->new(unary_ops => [
+ {
+ regex => qr/.../,
+ handler => sub {
+ my ($self, $op, $arg) = @_;
+ ...
+ },
+ },
+ {
+ regex => qr/.../,
+ handler => 'method_name',
+ },
+ ]);
+
+A "unary operator" is a SQL syntactic clause that can be
+applied to a field - the operator goes before the field
+
+You can write your own operator handlers - supply a C<unary_ops>
+argument to the C<new> method. That argument takes an arrayref of
+operator definitions; each operator definition is a hashref with two
+entries:
+
+=over
+
+=item regex
+
+the regular expression to match the operator
+
+=item handler
+
+Either a coderef or a plain scalar method name. In both cases
+the expected return is C<< $sql >>.
+
+When supplied with a method name, it is simply called on the
+L<SQL::Abstract/> object as:
+
+ $self->$method_name ($op, $arg)
+
+ Where:
+
+ $op is the part that matched the handler regex
+ $arg is the RHS or argument of the operator
+
+When supplied with a coderef, it is called as:
+
+ $coderef->($self, $op, $arg)
+
+
+=back
+
+
=head1 PERFORMANCE
Thanks to some benchmarking by Mark Stosberg, it turns out that
table, the actual query script can be extremely simplistic.
If you're B<REALLY> lazy (I am), check out C<HTML::QuickTable> for
-a fast interface to returning and formatting data. I frequently
+a fast interface to returning and formatting data. I frequently
use these three modules together to write complex database query
apps in under 50 lines.
Version 1.50 was a major internal refactoring of C<SQL::Abstract>.
Great care has been taken to preserve the I<published> behavior
documented in previous versions in the 1.* family; however,
-some features that were previously undocumented, or behaved
+some features that were previously undocumented, or behaved
differently from the documentation, had to be changed in order
to clarify the semantics. Hence, client code that was relying
-on some dark areas of C<SQL::Abstract> v1.*
+on some dark areas of C<SQL::Abstract> v1.*
B<might behave differently> in v1.50.
The main changes are :
=over
-=item *
+=item *
support for literal SQL through the C<< \ [$sql, bind] >> syntax.
optional support for L<array datatypes|/"Inserting and Updating Arrays">
-=item *
+=item *
defensive programming : check arguments
fixed semantics of _bindtype on array args
-=item *
+=item *
dropped the C<_anoncopy> of the %where tree. No longer necessary,
we just avoid shifting arrays within that tree.
this module. Unfortunately, most of them submitted bugs via CPAN
so I have no idea who they are! But the people I do know are:
- Ash Berlin (order_by hash term support)
+ Ash Berlin (order_by hash term support)
Matt Trout (DBIx::Class support)
Mark Stosberg (benchmarking)
Chas Owens (initial "IN" operator support)
Laurent Dami (internal refactoring, multiple -nest, 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)
+ Oliver Charles (support for "RETURNING" after "INSERT")
Thanks!
C<SQL::Abstract>, and as such list members there are very familiar with
how to create queries.
-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.
+=head1 LICENSE
+
+This module is free software; you may copy this under the same
+terms as perl itself (either the GNU General Public License or
+the Artistic License)
=cut