package SQL::Abstract; # see doc at end of file
-# LDNOTE : this code is heavy refactoring from original SQLA.
-# Several design decisions will need discussion during
-# the test / diffusion / acceptance phase; those are marked with flag
-# 'LDNOTE' (note by laurent.dami AT free.fr)
-
use strict;
use warnings;
use Carp ();
use List::Util ();
use Scalar::Util ();
+use Exporter 'import';
+our @EXPORT_OK = qw(is_plain_value is_literal_value);
+
+BEGIN {
+ if ($] < 5.009_005) {
+ require MRO::Compat;
+ }
+ else {
+ require mro;
+ }
+
+ *SQL::Abstract::_ENV_::DETECT_AUTOGENERATED_STRINGIFICATION = $ENV{SQLA_ISVALUE_IGNORE_AUTOGENERATED_STRINGIFICATION}
+ ? sub () { 0 }
+ : sub () { 1 }
+ ;
+}
+
#======================================================================
# GLOBALS
#======================================================================
-our $VERSION = '1.72';
+our $VERSION = '1.78';
# This would confuse some packagers
$VERSION = eval $VERSION if $VERSION =~ /_/; # numify for warning-free dev releases
{regex => qr/^ (?: not \s )? in $/ix, handler => '_where_field_IN'},
{regex => qr/^ ident $/ix, handler => '_where_op_IDENT'},
{regex => qr/^ value $/ix, handler => '_where_op_VALUE'},
+ {regex => qr/^ is (?: \s+ not )? $/ix, handler => '_where_field_IS'},
);
# unaryish operators - key maps to handler
{ regex => qr/^ nest (?: [_\s]? \d+ )? $/xi, handler => '_where_op_NEST' },
{ regex => qr/^ (?: not \s )? bool $/xi, handler => '_where_op_BOOL' },
{ regex => qr/^ ident $/xi, handler => '_where_op_IDENT' },
- { regex => qr/^ value $/ix, handler => '_where_op_VALUE' },
+ { regex => qr/^ value $/xi, handler => '_where_op_VALUE' },
);
#======================================================================
Carp::croak "[$func] Fatal: ", @_;
}
+sub is_literal_value ($) {
+ ref $_[0] eq 'SCALAR' ? [ ${$_[0]} ]
+ : ( ref $_[0] eq 'REF' and ref ${$_[0]} eq 'ARRAY' ) ? [ @${ $_[0] } ]
+ : (
+ ref $_[0] eq 'HASH' and keys %{$_[0]} == 1
+ and
+ defined $_[0]->{-ident} and ! length ref $_[0]->{-ident}
+ ) ? [ $_[0]->{-ident} ]
+ : undef;
+}
+
+# FIXME XSify - this can be done so much more efficiently
+sub is_plain_value ($) {
+ no strict 'refs';
+ ! length ref $_[0] ? \($_[0])
+ : (
+ ref $_[0] eq 'HASH' and keys %{$_[0]} == 1
+ and
+ exists $_[0]->{-value}
+ ) ? \($_[0]->{-value})
+ : (
+ # reuse @_ for even moar speedz
+ defined ( $_[1] = Scalar::Util::blessed $_[0] )
+ and
+ # deliberately not using Devel::OverloadInfo - the checks we are
+ # intersted in are much more limited than the fullblown thing, and
+ # this is a very hot piece of code
+ (
+ # simply using ->can('(""') can leave behind stub methods that
+ # break actually using the overload later (see L<perldiag/Stub
+ # found while resolving method "%s" overloading "%s" in package
+ # "%s"> and the source of overload::mycan())
+ #
+ # either has stringification which DBI SHOULD prefer out of the box
+ grep { *{ (qq[${_}::(""]) }{CODE} } @{ $_[2] = mro::get_linear_isa( $_[1] ) }
+ or
+ # has nummification or boolification, AND fallback is *not* disabled
+ (
+ SQL::Abstract::_ENV_::DETECT_AUTOGENERATED_STRINGIFICATION
+ and
+ (
+ grep { *{"${_}::(0+"}{CODE} } @{$_[2]}
+ or
+ grep { *{"${_}::(bool"}{CODE} } @{$_[2]}
+ )
+ and
+ (
+ # no fallback specified at all
+ ! ( ($_[3]) = grep { *{"${_}::()"}{CODE} } @{$_[2]} )
+ or
+ # fallback explicitly undef
+ ! defined ${"$_[3]::()"}
+ or
+ # explicitly true
+ !! ${"$_[3]::()"}
+ )
+ )
+ )
+ ) ? \($_[0])
+ : undef;
+}
+
+
#======================================================================
# NEW
$opt{logic} = $opt{logic} ? uc $opt{logic} : 'OR';
# how to return bind vars
- # LDNOTE: changed nwiger code : why this 'delete' ??
- # $opt{bindtype} ||= delete($opt{bind_type}) || 'normal';
$opt{bindtype} ||= 'normal';
# default comparison is "=", but can be overridden
$opt{cmp} ||= '=';
- # 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;
+ # try to recognize which are the 'equality' and 'inequality' ops
+ # (temporary quickfix (in 2007), should go through a more seasoned API)
+ $opt{equality_op} = qr/^( \Q$opt{cmp}\E | \= )$/ix;
+ $opt{inequality_op} = qr/^( != | <> )$/ix;
+
+ $opt{like_op} = qr/^ (is\s+)? r?like $/xi;
+ $opt{not_like_op} = qr/^ (is\s+)? not \s+ r?like $/xi;
# SQL booleans
$opt{sqltrue} ||= '1=1';
$opt{unary_ops} ||= [];
push @{$opt{unary_ops}}, @BUILTIN_UNARY_OPS;
- # rudimentary saniy-check for user supplied bits treated as functions/operators
+ # rudimentary sanity-check for user supplied bits treated as functions/operators
# If a purported function matches this regular expression, an exception is thrown.
# Literal SQL is *NOT* subject to this check, only functions (and column names
# when quoting is not in effect)
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;
+ # DBIx::Class used to call _recurse_where in scalar context
+ # something else might too...
+ if (wantarray) {
+ return ($sql, @bind);
+ }
+ else {
+ belch "Calling _recurse_where in scalar context is deprecated and will go away before 2.0";
+ return $sql;
+ }
}
},
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
- # the global logic to 'AND'. So [ {cond1, cond2}, [cond3, 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); },
sub _where_op_IDENT {
my $self = shift;
my ($op, $rhs) = splice @_, -2;
- if (ref $rhs) {
- puke "-$op takes a single scalar argument (a quotable identifier)";
+ if (! defined $rhs or length ref $rhs) {
+ puke "-$op requires a single plain scalar argument (a quotable identifier)";
}
# in case we are called as a top level special op (no '=')
# in case we are called as a top level special op (no '=')
my $lhs = shift;
+ # special-case NULL
+ if (! defined $rhs) {
+ return $lhs
+ ? $self->_convert($self->_quote($lhs)) . ' IS NULL'
+ : undef
+ ;
+ }
+
my @bind =
$self->_bindtype (
($lhs || $self->{_nested_func_lhs}),
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");
return ($self->{sqlfalse});
}
$self->_assert_pass_injection_guard($op);
+ # fixup is_not
+ $op =~ s/^is_not/IS NOT/i;
+
# so that -not_foo works correctly
$op =~ s/^not_/NOT /i;
+ # another retarded special case: foo => { $op => { -value => undef } }
+ if (ref $val eq 'HASH' and keys %$val == 1 and exists $val->{-value} and ! defined $val->{-value} ) {
+ $val = undef;
+ }
+
my ($sql, @bind);
# CASE: col-value logic modifiers
},
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 '$orig_op' with undef operand";
+ my $is =
+ $op =~ /^not$/i ? 'is not' # legacy
+ : $op =~ $self->{equality_op} ? 'is'
+ : $op =~ $self->{like_op} ? belch("Supplying an undefined argument to '@{[ uc $op]}' is deprecated") && 'is'
+ : $op =~ $self->{inequality_op} ? 'is not'
+ : $op =~ $self->{not_like_op} ? belch("Supplying an undefined argument to '@{[ uc $op]}' is deprecated") && 'is not'
+ : puke "unexpected operator '$orig_op' with undef operand";
+
$sql = $self->_quote($k) . $self->_sqlcase(" $is null");
},
return ($all_sql, @all_bind);
}
+sub _where_field_IS {
+ my ($self, $k, $op, $v) = @_;
+
+ my ($s) = $self->_SWITCH_refkind($v, {
+ UNDEF => sub {
+ join ' ',
+ $self->_convert($self->_quote($k)),
+ map { $self->_sqlcase($_)} ($op, 'null')
+ },
+ FALLBACK => sub {
+ puke "$op can only take undef as argument";
+ },
+ });
+ $s;
+}
sub _where_field_op_ARRAYREF {
my ($self, $k, $op, $vals) = @_;
shift @vals;
}
+ # a long standing API wart - an attempt to change this behavior during
+ # the 1.50 series failed *spectacularly*. Warn instead and leave the
+ # behavior as is
+ if (
+ @vals > 1
+ and
+ (!$logic or $logic eq 'OR')
+ and
+ ( $op =~ $self->{inequality_op} or $op =~ $self->{not_like_op} )
+ ) {
+ my $o = uc($op);
+ belch "A multi-element arrayref as an argument to the inequality op '$o' "
+ . 'is technically equivalent to an always-true 1=1 (you probably wanted '
+ . "to say ...{ \$inequality_op => [ -and => \@values ] }... instead)"
+ ;
+ }
+
# 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 :
- # 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.
- # 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 {
# 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};
-
- # otherwise
- puke "operator '$op' applied on an empty array (field '$k')";
+ return
+ $op =~ $self->{equality_op} ? $self->{sqlfalse}
+ : $op =~ $self->{like_op} ? belch("Supplying an empty arrayref to '@{[ uc $op]}' is deprecated") && $self->{sqlfalse}
+ : $op =~ $self->{inequality_op} ? $self->{sqltrue}
+ : $op =~ $self->{not_like_op} ? belch("Supplying an empty arrayref to '@{[ uc $op]}' is deprecated") && $self->{sqltrue}
+ : puke "operator '$op' applied on an empty array (field '$k')";
}
}
$placeholder = $self->_convert('?');
$op = $self->_sqlcase($op);
+ my $invalid_args = "Operator '$op' requires either an arrayref with two defined values or expressions, or a single literal scalarref/arrayref-ref";
+
my ($clause, @bind) = $self->_SWITCH_refkind($vals, {
ARRAYREFREF => sub {
my ($s, @b) = @$$vals;
return $$vals;
},
ARRAYREF => sub {
- puke "special op 'between' accepts an arrayref with exactly two values"
- if @$vals != 2;
+ puke $invalid_args if @$vals != 2;
my (@all_sql, @all_bind);
foreach my $val (@$vals) {
if (@rest or $func !~ /^ \- (.+)/x);
local $self->{_nested_func_lhs} = $k;
$self->_where_unary_op ($1 => $arg);
- }
+ },
+ FALLBACK => sub {
+ puke $invalid_args,
+ },
});
push @all_sql, $sql;
push @all_bind, @bind;
);
},
FALLBACK => sub {
- puke "special op 'between' accepts an arrayref with two values, or a single literal scalarref/arrayref-ref";
+ puke $invalid_args,
},
});
$self->_where_unary_op ($1 => $arg);
},
UNDEF => sub {
- return $self->_sqlcase('null');
+ puke(
+ 'SQL::Abstract before v1.75 used to generate incorrect SQL when the '
+ . "-$op operator was given an undef-containing list: !!!AUDIT YOUR CODE "
+ . 'AND DATA!!! (the upcoming Data::Query-based version of SQL::Abstract '
+ . 'will emit the logically correct SQL instead of raising this exception)'
+ );
},
});
push @all_sql, $sql;
return ("$label $op ( $sql )", @bind);
},
+ UNDEF => sub {
+ puke "Argument passed to the '$op' operator can not be undefined";
+ },
+
FALLBACK => sub {
- puke "special op 'in' requires an arrayref (or scalarref/arrayref-ref)";
+ puke "special op $op requires an arrayref (or scalarref/arrayref-ref)";
},
});
else {
puke "Unsupported quote_char format: $_[0]->{quote_char}";
}
+ my $esc = $_[0]->{escape_char} || $r;
# parts containing * are naturally unquoted
return join( $_[0]->{name_sep}||'', map
- { $_ eq '*' ? $_ : $l . $_ . $r }
+ { $_ eq '*' ? $_ : do { (my $n = $_) =~ s/(\Q$esc\E|\Q$r\E)/$esc$1/g; $l . $n . $r } }
( $_[0]->{name_sep} ? split (/\Q$_[0]->{name_sep}\E/, $_[1] ) : $_[1] )
);
}
# Conversion, if applicable
sub _convert ($) {
#my ($self, $arg) = @_;
-
-# 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
-# scalar function.
-# return @_ unless $self->{convert};
-# my $conv = $self->_sqlcase($self->{convert});
-# my @ret = map { $conv.'('.$_.')' } @_;
-# return wantarray ? @ret : $ret[0];
if ($_[0]->{convert}) {
return $_[0]->_sqlcase($_[0]->{convert}) .'(' . $_[1] . ')';
}
# And bindtype
sub _bindtype (@) {
#my ($self, $col, @vals) = @_;
-
- #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;
-
# called often - tighten code
return $_[0]->{bindtype} eq 'columns'
? map {[$_[1], $_]} @_[2 .. $#_]
my $sql = SQL::Abstract->new;
- my($stmt, @bind) = $sql->select($table, \@fields, \%where, \@order);
+ my($stmt, @bind) = $sql->select($source, \@fields, \%where, \@order);
my($stmt, @bind) = $sql->insert($table, \%fieldvals || \@values);
my %data = (
name => 'Bill',
- date_entered => \["to_date(?,'MM/DD/YYYY')", "03/02/2003"],
+ date_entered => \[ "to_date(?,'MM/DD/YYYY')", "03/02/2003" ],
);
The first value in the array is the actual SQL. Any other values are
Easy, eh?
-=head1 FUNCTIONS
+=head1 METHODS
-The functions are simple. There's one for each major SQL operation,
+The methods 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 method (table, then fields, then a where
clause) to try and simplify things.
-
-
-
=head2 new(option => 'value')
The C<new()> function takes a list of options and values, and returns
WHERE name like 'nwiger' AND email like 'nate@wiger.org'
-You can also override the comparsion on an individual basis - see
+You can also override the comparison on an individual basis - see
the huge section on L</"WHERE CLAUSES"> at the bottom.
=item sqltrue, sqlfalse
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]>
+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.
Quoting is useful if you have tables or columns names that are reserved
words in your database's SQL dialect.
+=item escape_char
+
+This is the character that will be used to escape L</quote_char>s appearing
+in an identifier before it has been quoted.
+
+The paramter default in case of a single L</quote_char> character is the quote
+character itself.
+
+When opening-closing-style quoting is used (L</quote_char> is an arrayref)
+this parameter defaults to the B<closing (right)> L</quote_char>. Occurences
+of the B<opening (left)> L</quote_char> within the identifier are currently left
+untouched. The default for opening-closing-style quotes may change in future
+versions, thus you are B<strongly encouraged> to specify the escape character
+explicitly.
+
=item name_sep
This is the character that separates a table and column name. It is
The argument can be either an arrayref (interpreted as a list
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.
+Please observe that this API is not as flexible as that of
+the first argument C<$source>, for backwards compatibility reasons.
=item $where
You get the idea. Strings get their case twiddled, but everything
else remains verbatim.
+=head1 EXPORTABLE FUNCTIONS
+
+=head2 is_plain_value
+
+Determines if the supplied argument is a plain value as understood by this
+module:
+
+=over
+
+=item * The value is C<undef>
+
+=item * The value is a non-reference
+
+=item * The value is an object with stringification overloading
+
+=item * The value is of the form C<< { -value => $anything } >>
+
+=back
+
+On failure returns C<undef>, on sucess returns a B<scalar> reference
+to the original supplied argument.
+
+=over
+
+=item * Note
+
+The stringification overloading detection is rather advanced: it takes
+into consideration not only the presence of a C<""> overload, but if that
+fails also checks for enabled
+L<autogenerated versions of C<"">|overload/Magic Autogeneration>, based
+on either C<0+> or C<bool>.
+
+Unfortunately testing in the field indicates that this
+detection B<< may tickle a latent bug in perl versions before 5.018 >>,
+but only when very large numbers of stringifying objects are involved.
+At the time of writing ( Sep 2014 ) there is no clear explanation of
+the direct cause, nor is there a manageably small test case that reliably
+reproduces the problem.
+
+If you encounter any of the following exceptions in B<random places within
+your application stack> - this module may be to blame:
+
+ Operation "ne": no method found,
+ left argument in overloaded package <something>,
+ right argument in overloaded package <something>
+
+or perhaps even
+
+ Stub found while resolving method "???" overloading """" in package <something>
+
+If you fall victim to the above - please attempt to reduce the problem
+to something that could be sent to the L<SQL::Abstract developers
+|DBIx::Class/GETTING HELP/SUPPORT>
+(either publicly or privately). As a workaround in the meantime you can
+set C<$ENV{SQLA_ISVALUE_IGNORE_AUTOGENERATED_STRINGIFICATION}> to a true
+value, which will most likely eliminate your problem (at the expense of
+not being able to properly detect exotic forms of stringification).
+
+This notice and environment variable will be removed in a future version,
+as soon as the underlying problem is found and a reliable workaround is
+devised.
+
+=back
+
+=head2 is_literal_value
+
+Determines if the supplied argument is a literal value as understood by this
+module:
+
+=over
+
+=item * C<\$sql_string>
+
+=item * C<\[ $sql_string, @bind_values ]>
+
+=item * C<< { -ident => $plain_defined_string } >>
+
+=back
+
+On failure returns C<undef>, on sucess returns an B<array> reference
+containing the unpacked version of the supplied literal SQL and bind values.
+
=head1 WHERE CLAUSES
=head2 Introduction
)";
@bind = ('2000');
-
+Finally, if the argument to C<-in> is not a reference, it will be
+treated as a single-element array.
Another pair of operators is C<-between> and C<-not_between>,
used with an arrayref of two values:
my %where = (
-and => [
-bool => 'one',
- -bool => 'two',
- -bool => 'three',
- -not_bool => 'four',
+ -not_bool => { two=> { -rlike => 'bar' } },
+ -not_bool => { three => [ { '=', 2 }, { '>', 5 } ] },
],
);
Would give you:
- WHERE one AND two AND three AND NOT four
+ WHERE
+ one
+ AND
+ (NOT two RLIKE ?)
+ AND
+ (NOT ( three = ? OR three > ? ))
=head2 Nested conditions, -and/-or prefixes
array => [1, 2, 3]
);
-the result would porbably be not what you wanted:
+the result would probably not be what you wanted:
$stmt = 'WHERE array = ? OR array = ? OR array = ?';
@bind = (1, 2, 3);
in Postgres you can use something like this:
my %where = (
- date_column => \[q/= date '2008-09-30' - ?::integer/, 10/]
+ date_column => \[ "= date '2008-09-30' - ?::integer", 10 ]
)
This would create:
@bind = ('10');
Note that you must pass the bind values in the same format as they are returned
-by L</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 value (including references and blessed references),
-L<SQL::Abstract> will simply pass it through intact. So if C<bindtype> is set
-to C<columns> the above example will look like:
+by L<where|/where(\%where, \@order)>. This 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 value
+(including references and blessed references), L<SQL::Abstract> will simply
+pass it through intact. So if C<bindtype> is set to C<columns> the above
+example will look like:
my %where = (
- date_column => \[q/= date '2008-09-30' - ?::integer/, [ dummy => 10 ]/]
+ date_column => \[ "= date '2008-09-30' - ?::integer", [ {} => 10 ] ]
)
Literal SQL is especially useful for nesting parenthesized clauses in the
#!/usr/bin/perl
+ use warnings;
+ use strict;
+
use CGI::FormBuilder;
use SQL::Abstract;
=item *
-support for literal SQL through the C<< \ [$sql, bind] >> syntax.
+support for literal SQL through the C<< \ [ $sql, @bind ] >> syntax.
=item *
projects / dbsrgits/SQL-Abstract.git / blobdiff