# GLOBALS
#======================================================================
-our $VERSION = '1.78';
+our $VERSION = '1.85';
# This would confuse some packagers
$VERSION = eval $VERSION if $VERSION =~ /_/; # numify for warning-free dev releases
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;
}
$sql = join " ", $self->_sqlcase('insert into'), $table, $sql;
if ($options->{returning}) {
- my ($s, @b) = $self->_insert_returning ($options);
+ my ($s, @b) = $self->_insert_returning($options);
$sql .= $s;
push @bind, @b;
}
return wantarray ? ($sql, @bind) : $sql;
}
-sub _insert_returning {
+# So that subclasses can override INSERT ... RETURNING separately from
+# UPDATE and DELETE (e.g. DBIx::Class::SQLMaker::Oracle does this)
+sub _insert_returning { shift->_returning(@_) }
+
+sub _returning {
my ($self, $options) = @_;
my $f = $options->{returning};
$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);
+ my (@values, @all_bind);
+ foreach my $value (@$data) {
+ my ($values, @bind) = $self->_insert_value(undef, $value);
+ push @values, $values;
+ push @all_bind, @bind;
+ }
+ my $sql = $self->_sqlcase('values')." ( ".join(", ", @values)." )";
+ return ($sql, @all_bind);
}
sub _insert_ARRAYREFREF { # literal SQL with bind
my (@values, @all_bind);
foreach my $column (sort keys %$data) {
- my $v = $data->{$column};
+ my ($values, @bind) = $self->_insert_value($column, $data->{$column});
+ push @values, $values;
+ push @all_bind, @bind;
+ }
+ my $sql = $self->_sqlcase('values')." ( ".join(", ", @values)." )";
+ return ($sql, @all_bind);
+}
- $self->_SWITCH_refkind($v, {
+sub _insert_value {
+ my ($self, $column, $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;
- }
- },
+ my (@values, @all_bind);
+ $self->_SWITCH_refkind($v, {
- ARRAYREFREF => sub { # literal SQL with bind
- my ($sql, @bind) = @${$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;
- },
+ }
+ },
- # 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);
- },
+ ARRAYREFREF => sub { # literal SQL with bind
+ my ($sql, @bind) = @${$v};
+ $self->_assert_bindval_matches_bindtype(@bind);
+ push @values, $sql;
+ push @all_bind, @bind;
+ },
- SCALARREF => sub { # literal SQL without bind
- push @values, $$v;
- },
+ # 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);
+ },
- SCALAR_or_UNDEF => sub {
- 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, $self->_bindtype($column, $v);
+ },
- }
+ });
- my $sql = $self->_sqlcase('values')." ( ".join(", ", @values)." )";
+ my $sql = join(", ", @values);
return ($sql, @all_bind);
}
sub update {
- my $self = shift;
- my $table = $self->_table(shift);
- my $data = shift || return;
- my $where = shift;
+ my $self = shift;
+ my $table = $self->_table(shift);
+ my $data = shift || return;
+ my $where = shift;
+ my $options = shift;
# first build the 'SET' part of the sql statement
- my (@set, @all_bind);
puke "Unsupported data type specified to \$sql->update"
unless ref $data eq 'HASH';
+ my ($sql, @all_bind) = $self->_update_set_values($data);
+ $sql = $self->_sqlcase('update ') . $table . $self->_sqlcase(' set ')
+ . $sql;
+
+ if ($where) {
+ my($where_sql, @where_bind) = $self->where($where);
+ $sql .= $where_sql;
+ push @all_bind, @where_bind;
+ }
+
+ if ($options->{returning}) {
+ my ($returning_sql, @returning_bind) = $self->_update_returning($options);
+ $sql .= $returning_sql;
+ push @all_bind, @returning_bind;
+ }
+
+ return wantarray ? ($sql, @all_bind) : $sql;
+}
+
+sub _update_set_values {
+ my ($self, $data) = @_;
+
+ my (@set, @all_bind);
for my $k (sort keys %$data) {
my $v = $data->{$k};
my $r = ref $v;
if (@rest or not $op =~ /^\-(.+)/);
local $self->{_nested_func_lhs} = $k;
- my ($sql, @bind) = $self->_where_unary_op ($1, $arg);
+ my ($sql, @bind) = $self->_where_unary_op($1, $arg);
push @set, "$label = $sql";
push @all_bind, @bind;
}
# generate sql
- my $sql = $self->_sqlcase('update') . " $table " . $self->_sqlcase('set ')
- . join ', ', @set;
+ my $sql = join ', ', @set;
- if ($where) {
- my($where_sql, @where_bind) = $self->where($where);
- $sql .= $where_sql;
- push @all_bind, @where_bind;
- }
-
- return wantarray ? ($sql, @all_bind) : $sql;
+ return ($sql, @all_bind);
}
+# So that subclasses can override UPDATE ... RETURNING separately from
+# INSERT and DELETE
+sub _update_returning { shift->_returning(@_) }
sub delete {
- my $self = shift;
- my $table = $self->_table(shift);
- my $where = shift;
-
+ my $self = shift;
+ my $table = $self->_table(shift);
+ my $where = shift;
+ my $options = shift;
my($where_sql, @bind) = $self->where($where);
- my $sql = $self->_sqlcase('delete from') . " $table" . $where_sql;
+ my $sql = $self->_sqlcase('delete from ') . $table . $where_sql;
+
+ if ($options->{returning}) {
+ my ($returning_sql, @returning_bind) = $self->_delete_returning($options);
+ $sql .= $returning_sql;
+ push @bind, @returning_bind;
+ }
return wantarray ? ($sql, @bind) : $sql;
}
+# So that subclasses can override DELETE ... RETURNING separately from
+# INSERT and UPDATE
+sub _delete_returning { shift->_returning(@_) }
+
+
#======================================================================
# WHERE: entry point
# order by?
if ($order) {
- $sql .= $self->_order_by($order);
+ my ($order_sql, @order_bind) = $self->_order_by($order);
+ $sql .= $order_sql;
+ push @bind, @order_bind;
}
return wantarray ? ($sql, @bind) : $sql;
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;
+ }
}
my (@sql_clauses, @all_bind);
# need to use while() so can shift() for pairs
- while (my $el = shift @clauses) {
+ while (@clauses) {
+ my $el = shift @clauses;
+
+ $el = undef if (defined $el and ! length $el);
# switch according to kind of $el and get corresponding ($sql, @bind)
my ($sql, @bind) = $self->_SWITCH_refkind($el, {
SCALARREF => sub { ($$el); },
- SCALAR => sub {# top-level arrayref with scalars, recurse in pairs
- $self->_recurse_where({$el => shift(@clauses)})},
+ SCALAR => sub {
+ # top-level arrayref with scalars, recurse in pairs
+ $self->_recurse_where({$el => shift(@clauses)})
+ },
- UNDEF => sub {puke "not supported : UNDEF in arrayref" },
+ UNDEF => sub {puke "Supplying an empty left hand side argument is not supported in array-pairs" },
});
if ($sql) {
$op =~ s/^not_/NOT /i;
$self->_debug("Unary OP(-$op) within hashref, recursing...");
- my ($s, @b) = $self->_where_unary_op ($op, $v);
+ my ($s, @b) = $self->_where_unary_op($op, $v);
# top level vs nested
# we assume that handled unary ops will take care of their ()s
$s = "($s)" unless (
List::Util::first {$op =~ $_->{regex}} @{$self->{unary_ops}}
or
- defined($self->{_nested_func_lhs}) && ($self->{_nested_func_lhs} eq $k)
+ ( defined $self->{_nested_func_lhs} and $self->{_nested_func_lhs} eq $k )
);
($s, @b);
}
else {
+ if (! length $k) {
+ if (is_literal_value ($v) ) {
+ belch 'Hash-pairs consisting of an empty string with a literal are deprecated, and will be removed in 2.0: use -and => [ $literal ] instead';
+ }
+ else {
+ puke "Supplying an empty left hand side argument is not supported in hash-pairs";
+ }
+ }
+
my $method = $self->_METHOD_FOR_refkind("_where_hashpair", $v);
$self->$method($k, $v);
}
sub _where_unary_op {
my ($self, $op, $rhs) = @_;
- if (my $op_entry = List::Util::first {$op =~ $_->{regex}} @{$self->{unary_ops}}) {
+ # top level special ops are illegal in general
+ # this includes the -ident/-value ops (dual purpose unary and special)
+ puke "Illegal use of top-level '-$op'"
+ if ! defined $self->{_nested_func_lhs} and List::Util::first { $op =~ $_->{regex} } @{$self->{special_ops}};
+
+ if (my $op_entry = List::Util::first { $op =~ $_->{regex} } @{$self->{unary_ops}}) {
my $handler = $op_entry->{handler};
if (not ref $handler) {
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 ... ]";
}
- return $self->$handler ($op, $rhs);
+ return $self->$handler($op, $rhs);
}
elsif (ref $handler eq 'CODE') {
return $handler->($self, $op, $rhs);
$self->_assert_pass_injection_guard($op);
- my ($sql, @bind) = $self->_SWITCH_refkind ($rhs, {
+ my ($sql, @bind) = $self->_SWITCH_refkind($rhs, {
SCALAR => sub {
- puke "Illegal use of top-level '$op'"
- unless $self->{_nested_func_lhs};
+ puke "Illegal use of top-level '-$op'"
+ unless defined $self->{_nested_func_lhs};
return (
$self->_convert('?'),
);
},
FALLBACK => sub {
- $self->_recurse_where ($rhs)
+ $self->_recurse_where($rhs)
},
});
- $sql = sprintf ('%s %s',
+ $sql = sprintf('%s %s',
$self->_sqlcase($op),
$sql,
);
},
HASHREF => sub {
- return ( $op =~ /^or/i )
- ? $self->_where_ARRAYREF( [ map { $_ => $v->{$_} } ( sort keys %$v ) ], $op )
+ return ($op =~ /^or/i)
+ ? $self->_where_ARRAYREF([ map { $_ => $v->{$_} } (sort keys %$v) ], $op)
: $self->_where_HASHREF($v);
},
},
FALLBACK => sub {
- $self->_recurse_where ($v);
+ $self->_recurse_where($v);
},
});
},
FALLBACK => sub {
- $self->_recurse_where ($v);
+ $self->_recurse_where($v);
},
});
# special-case NULL
if (! defined $rhs) {
- return $lhs
+ return defined $lhs
? $self->_convert($self->_quote($lhs)) . ' IS NULL'
: undef
;
}
my @bind =
- $self->_bindtype (
- ($lhs || $self->{_nested_func_lhs}),
+ $self->_bindtype(
+ (defined $lhs ? $lhs : $self->{_nested_func_lhs}),
$rhs,
)
;
sub _where_hashpair_ARRAYREF {
my ($self, $k, $v) = @_;
- if( @$v ) {
+ if (@$v) {
my @v = @$v; # need copy because of shift below
$self->_debug("ARRAY($k) means distribute over elements");
my ($self, $k, $v, $logic) = @_;
$logic ||= 'and';
- local $self->{_nested_func_lhs} = $self->{_nested_func_lhs};
+ local $self->{_nested_func_lhs} = defined $self->{_nested_func_lhs}
+ ? $self->{_nested_func_lhs}
+ : $k
+ ;
my ($all_sql, @all_bind);
my ($sql, @bind);
# CASE: col-value logic modifiers
- if ( $orig_op =~ /^ \- (and|or) $/xi ) {
+ if ($orig_op =~ /^ \- (and|or) $/xi) {
($sql, @bind) = $self->_where_hashpair_HASHREF($k, $val, $1);
}
# CASE: special operators like -in or -between
- elsif ( my $special_op = List::Util::first {$op =~ $_->{regex}} @{$self->{special_ops}} ) {
+ 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);
+ ($sql, @bind) = $self->$handler($k, $op, $val);
}
elsif (ref $handler eq 'CODE') {
($sql, @bind) = $handler->($self, $k, $op, $val);
},
FALLBACK => sub { # CASE: col => {op/func => $stuff}
+ ($sql, @bind) = $self->_where_unary_op($op, $val);
- # retain for proper column type bind
- $self->{_nested_func_lhs} ||= $k;
-
- ($sql, @bind) = $self->_where_unary_op ($op, $val);
-
- $sql = join (' ',
+ $sql = join(' ',
$self->_convert($self->_quote($k)),
$self->{_nested_func_lhs} eq $k ? $sql : "($sql)", # top level vs nested
);
my @vals = @$vals; #always work on a copy
- if(@vals) {
+ if (@vals) {
$self->_debug(sprintf '%s means multiple elements: [ %s ]',
$vals,
- join (', ', map { defined $_ ? "'$_'" : 'NULL' } @vals ),
+ join(', ', map { defined $_ ? "'$_'" : 'NULL' } @vals ),
);
# see if the first element is an -and/-or op
my $logic;
- if (defined $vals[0] && $vals[0] =~ /^ - ( AND|OR ) $/ix) {
+ if (defined $vals[0] && $vals[0] =~ /^ - (AND|OR) $/ix) {
$logic = uc $1;
shift @vals;
}
and
(!$logic or $logic eq 'OR')
and
- ( $op =~ $self->{inequality_op} or $op =~ $self->{not_like_op} )
+ ($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' "
$self->_sqlcase($self->{cmp}),
$self->_convert('?');
my @bind = $self->_bindtype($k, $v);
- return ( $sql, @bind);
+ return ($sql, @bind);
}
},
HASHREF => sub {
my ($func, $arg, @rest) = %$val;
- puke ("Only simple { -func => arg } functions accepted as sub-arguments to BETWEEN")
+ puke "Only simple { -func => arg } functions accepted as sub-arguments to BETWEEN"
if (@rest or $func !~ /^ \- (.+)/x);
- local $self->{_nested_func_lhs} = $k;
- $self->_where_unary_op ($1 => $arg);
+ $self->_where_unary_op($1 => $arg);
},
FALLBACK => sub {
puke $invalid_args,
},
HASHREF => sub {
my ($func, $arg, @rest) = %$val;
- puke ("Only simple { -func => arg } functions accepted as sub-arguments to IN")
+ puke "Only simple { -func => arg } functions accepted as sub-arguments to IN"
if (@rest or $func !~ /^ \- (.+)/x);
- local $self->{_nested_func_lhs} = $k;
- $self->_where_unary_op ($1 => $arg);
+ $self->_where_unary_op($1 => $arg);
},
UNDEF => sub {
puke(
}
return (
- sprintf ('%s %s ( %s )',
+ sprintf('%s %s ( %s )',
$label,
$op,
- join (', ', @all_sql)
+ join(', ', @all_sql)
),
$self->_bindtype($k, @all_bind),
);
},
SCALARREF => sub { # literal SQL
- my $sql = $self->_open_outer_paren ($$vals);
+ 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);
+ $sql = $self->_open_outer_paren($sql);
return ("$label $op ( $sql )", @bind);
},
# adding them back in the corresponding method
sub _open_outer_paren {
my ($self, $sql) = @_;
- $sql = $1 while $sql =~ /^ \s* \( (.*) \) \s* $/xs;
- return $sql;
+
+ while (my ($inner) = $sql =~ /^ \s* \( (.*) \) \s* $/xs) {
+
+ # there are closing parens inside, need the heavy duty machinery
+ # to reevaluate the extraction starting from $sql (full reevaluation)
+ if ($inner =~ /\)/) {
+ require Text::Balanced;
+
+ my (undef, $remainder) = do {
+ # idiotic design - writes to $@ but *DOES NOT* throw exceptions
+ local $@;
+ Text::Balanced::extract_bracketed($sql, '()', qr/\s*/);
+ };
+
+ # the entire expression needs to be a balanced bracketed thing
+ # (after an extract no remainder sans trailing space)
+ last if defined $remainder and $remainder =~ /\S/;
+ }
+
+ $sql = $inner;
+ }
+
+ $sql;
}
my ($self, $arg) = @_;
my (@sql, @bind);
- for my $c ($self->_order_by_chunks ($arg) ) {
- $self->_SWITCH_refkind ($c, {
+ 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',
+ ? sprintf('%s %s',
$self->_sqlcase(' order by'),
- join (', ', @sql)
+ join(', ', @sql)
)
: ''
;
return $self->_SWITCH_refkind($arg, {
ARRAYREF => sub {
- map { $self->_order_by_chunks ($_ ) } @$arg;
+ map { $self->_order_by_chunks($_ ) } @$arg;
},
ARRAYREFREF => sub {
return () unless $key;
- if ( @rest 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)";
}
my $direction = $1;
my @ret;
- for my $c ($self->_order_by_chunks ($val)) {
+ for my $c ($self->_order_by_chunks($val)) {
my ($sql, @bind);
- $self->_SWITCH_refkind ($c, {
+ $self->_SWITCH_refkind($c, {
SCALAR => sub {
$sql = $c;
},
return '' unless defined $_[1];
return ${$_[1]} if ref($_[1]) eq 'SCALAR';
- unless ($_[0]->{quote_char}) {
- $_[0]->_assert_pass_injection_guard($_[1]);
- return $_[1];
- }
+ $_[0]->{quote_char} or
+ ($_[0]->_assert_pass_injection_guard($_[1]), return $_[1]);
my $qref = ref $_[0]->{quote_char};
- my ($l, $r);
- if (!$qref) {
- ($l, $r) = ( $_[0]->{quote_char}, $_[0]->{quote_char} );
- }
- elsif ($qref eq 'ARRAY') {
- ($l, $r) = @{$_[0]->{quote_char}};
- }
- else {
- puke "Unsupported quote_char format: $_[0]->{quote_char}";
- }
+ my ($l, $r) =
+ !$qref ? ($_[0]->{quote_char}, $_[0]->{quote_char})
+ : ($qref eq 'ARRAY') ? @{$_[0]->{quote_char}}
+ : 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 '*' ? $_ : do { (my $n = $_) =~ s/(\Q$esc\E|\Q$r\E)/$esc$1/g; $l . $n . $r } }
+ return join($_[0]->{name_sep}||'', map
+ +( $_ 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 ($) {
+sub _convert {
#my ($self, $arg) = @_;
if ($_[0]->{convert}) {
return $_[0]->_sqlcase($_[0]->{convert}) .'(' . $_[1] . ')';
}
# And bindtype
-sub _bindtype (@) {
+sub _bindtype {
#my ($self, $col, @vals) = @_;
# called often - tighten code
return $_[0]->{bindtype} eq 'columns'
unless ref $data eq 'HASH';
my @all_bind;
- foreach my $k ( sort keys %$data ) {
+ foreach my $k (sort keys %$data) {
my $v = $data->{$k};
$self->_SWITCH_refkind($v, {
ARRAYREF => sub {
my $sql = SQL::Abstract->new;
- my($stmt, @bind) = $sql->select($source, \@fields, \%where, \@order);
+ my($stmt, @bind) = $sql->select($source, \@fields, \%where, $order);
my($stmt, @bind) = $sql->insert($table, \%fieldvals || \@values);
$sth->execute(@bind);
# Just generate the WHERE clause
- my($stmt, @bind) = $sql->where(\%where, \@order);
+ my($stmt, @bind) = $sql->where(\%where, $order);
# Return values in the same order, for hashed queries
# See PERFORMANCE section for more details
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
=head1 METHODS
-The methods are simple. There's one for each major SQL operation,
+The methods are simple. There's one for every major SQL operation,
and a constructor you use first. The arguments are specified in a
-similar order to each method (table, then fields, then a where
+similar order for each method (table, then fields, then a where
clause) to try and simplify things.
=head2 new(option => 'value')
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.
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
+The parameter 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
+this parameter defaults to the B<closing (right)> L</quote_char>. Occurrences
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
=back
-=head2 update($table, \%fieldvals, \%where)
+=head2 update($table, \%fieldvals, \%where, \%options)
This takes a table, hashref of field/value pairs, and an optional
hashref L<WHERE clause|/WHERE CLAUSES>. It returns an SQL UPDATE function and a list
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 update SQL. Currently supported options
+are:
+
+=over 4
+
+=item returning
+
+See the C<returning> option to
+L<insert|/insert($table, \@values || \%fieldvals, \%options)>.
+
+=back
+
=head2 select($source, $fields, $where, $order)
This returns a SQL SELECT statement and associated list of bind values, as
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
-(literal table name, not quoted), or a ref to an arrayref
-(list of literal table names, joined by commas, not quoted).
+(literal SQL, not quoted).
=item $fields
=back
-=head2 delete($table, \%where)
+=head2 delete($table, \%where, \%options)
This takes a table name and optional hashref L<WHERE clause|/WHERE CLAUSES>.
It returns an SQL DELETE statement and list of bind values.
-=head2 where(\%where, \@order)
+The optional C<\%options> hash reference may contain additional
+options to generate the delete SQL. Currently supported options
+are:
+
+=over 4
+
+=item returning
+
+See the C<returning> option to
+L<insert|/insert($table, \@values || \%fieldvals, \%options)>.
+
+=back
+
+=head2 where(\%where, $order)
This is used to generate just the WHERE clause. For example,
if you have an arbitrary data structure and know what the
=back
-On failure returns C<undef>, on sucess returns a B<scalar> reference
+On failure returns C<undef>, on success returns a B<scalar> reference
to the original supplied argument.
=over
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>
+|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
=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
+On failure returns C<undef>, on success returns an B<array> reference
containing the unpacked version of the supplied literal SQL and bind values.
=head1 WHERE CLAUSES
@bind = ('2', '5', 'nwiger');
If you want to include literal SQL (with or without bind values), just use a
-scalar reference or array reference as the value:
+scalar reference or reference to an arrayref as the value:
my %where = (
date_entered => { '>' => \["to_date(?, 'MM/DD/YYYY')", "11/26/2008"] },
Which would generate:
- $stmt = "WHERE date_entered > "to_date(?, 'MM/DD/YYYY') AND date_expires < now()";
+ $stmt = "WHERE date_entered > to_date(?, 'MM/DD/YYYY') AND date_expires < now()";
@bind = ('11/26/2008');
Because, in Perl you I<can't> do this:
- priority => { '!=', 2, '!=', 1 }
+ priority => { '!=' => 2, '!=' => 1 }
As the second C<!=> key will obliterate the first. The solution
is to use the special C<-modifier> form inside an arrayref:
That would yield:
- WHERE ( user = ? AND (
- ( workhrs > ? AND geo = ? )
- OR ( workhrs < ? OR geo = ? )
- ) )
+ $stmt = "WHERE ( user = ?
+ AND ( ( workhrs > ? AND geo = ? )
+ OR ( workhrs < ? OR geo = ? ) ) )";
+ @bind = ('nwiger', '20', 'ASIA', '50', 'EURO');
=head3 Algebraic inconsistency, for historical reasons
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 ? ) )
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
=head1 ORDER BY CLAUSES
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:
-
- Given | Will Generate
- ----------------------------------------------------------
- |
- \'colA DESC' | ORDER BY colA DESC
- |
- 'colA' | ORDER BY colA
- |
- [qw/colA colB/] | ORDER BY colA, colB
- |
- {-asc => 'colA'} | ORDER BY colA ASC
- |
- {-desc => 'colB'} | ORDER BY colB DESC
- |
- ['colA', {-asc => 'colB'}] | ORDER BY colA, colB ASC
- |
- { -asc => [qw/colA colB/] } | ORDER BY colA ASC, colB ASC
- |
- [ |
- { -asc => 'colA' }, | ORDER BY colA ASC, colB DESC,
- { -desc => [qw/colB/], | colC ASC, colD ASC
- { -asc => [qw/colC colD/],|
- ] |
- ===========================================================
+column name), a hashref of C<< { -desc => 'col' } >> or C<< { -asc => 'col' }
+>>, a scalarref, an arrayref-ref, or an arrayref of any of the previous
+forms. Examples:
+
+ Given | Will Generate
+ ---------------------------------------------------------------
+ |
+ 'colA' | ORDER BY colA
+ |
+ [qw/colA colB/] | ORDER BY colA, colB
+ |
+ {-asc => 'colA'} | ORDER BY colA ASC
+ |
+ {-desc => 'colB'} | ORDER BY colB DESC
+ |
+ ['colA', {-asc => 'colB'}] | ORDER BY colA, colB ASC
+ |
+ { -asc => [qw/colA colB/] } | ORDER BY colA ASC, colB ASC
+ |
+ \'colA DESC' | ORDER BY colA DESC
+ |
+ \[ 'FUNC(colA, ?)', $x ] | ORDER BY FUNC(colA, ?)
+ | /* ...with $x bound to ? */
+ |
+ [ | ORDER BY
+ { -asc => 'colA' }, | colA ASC,
+ { -desc => [qw/colB/] }, | colB DESC,
+ { -asc => [qw/colC colD/] },| colC ASC, colD ASC,
+ \'colE DESC', | colE DESC,
+ \[ 'FUNC(colF, ?)', $x ], | FUNC(colF, ?)
+ ] | /* ...with $x bound to ? */
+ ===============================================================
the expected return is C<< ($sql, @bind) >>.
When supplied with a method name, it is simply called on the
-L<SQL::Abstract/> object as:
+L<SQL::Abstract> object as:
- $self->$method_name ($field, $op, $arg)
+ $self->$method_name($field, $op, $arg)
Where:
- $op is the part that matched the handler regex
$field is the LHS of the operator
+ $op is the part that matched the handler regex
$arg is the RHS
When supplied with a coderef, it is called as:
the expected return is C<< $sql >>.
When supplied with a method name, it is simply called on the
-L<SQL::Abstract/> object as:
+L<SQL::Abstract> object as:
- $self->$method_name ($op, $arg)
+ $self->$method_name($op, $arg)
Where:
use these three modules together to write complex database query
apps in under 50 lines.
-=head1 REPO
+=head1 HOW TO CONTRIBUTE
+
+Contributions are always welcome, in all usable forms (we especially
+welcome documentation improvements). The delivery methods include git-
+or unified-diff formatted patches, GitHub pull requests, or plain bug
+reports either via RT or the Mailing list. Contributors are generally
+granted full access to the official repository after their first several
+patches pass successful review.
+
+This project is maintained in a git repository. The code and related tools are
+accessible at the following locations:
=over
-=item * gitweb: L<http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/SQL-Abstract.git>
+=item * Official repo: L<git://git.shadowcat.co.uk/dbsrgits/SQL-Abstract.git>
+
+=item * Official gitweb: L<http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/SQL-Abstract.git>
-=item * git: L<git://git.shadowcat.co.uk/dbsrgits/SQL-Abstract.git>
+=item * GitHub mirror: L<https://github.com/dbsrgits/sql-abstract>
+
+=item * Authorized committers: L<ssh://dbsrgits@git.shadowcat.co.uk/SQL-Abstract.git>
=back
=item *
-support for literal SQL through the C<< \ [$sql, bind] >> syntax.
+support for literal SQL through the C<< \ [ $sql, @bind ] >> syntax.
=item *
the Artistic License)
=cut
-
projects / dbsrgits/SQL-Abstract.git / blobdiff