# GLOBALS
#======================================================================
-our $VERSION = '1.79';
+our $VERSION = '1.81';
# This would confuse some packagers
$VERSION = eval $VERSION if $VERSION =~ /_/; # numify for warning-free dev releases
return wantarray ? ($sql, @bind) : $sql;
}
-sub _insert_returning {
+# Used by DBIx::Class::SQLMaker->insert
+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);
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_returning { shift->_returning(@_) }
# 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;
$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);
}
sub _where_unary_op {
my ($self, $op, $rhs) = @_;
+ # 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};
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('?'),
# 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}),
+ ( defined $lhs ? $lhs : $self->{_nested_func_lhs} ),
$rhs,
)
;
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);
},
FALLBACK => sub { # CASE: col => {op/func => $stuff}
-
- # retain for proper column type bind
- $self->{_nested_func_lhs} ||= $k;
-
($sql, @bind) = $self->_where_unary_op ($op, $val);
$sql = join (' ',
my ($func, $arg, @rest) = %$val;
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);
},
FALLBACK => sub {
my ($func, $arg, @rest) = %$val;
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);
},
UNDEF => sub {
# 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;
}
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 } }
+ +( $_ 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'
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
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)
=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
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)
+=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
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 ? ) )
@bind = ('10');
Note that you must pass the bind values in the same format as they are returned
-by L<where|/where(\%where, \@order)>. This means that if you set L</bindtype>
+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
=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:
+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 DESC' | ORDER BY colA DESC
+ ---------------------------------------------------------------
|
'colA' | ORDER BY colA
|
|
{ -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 ? */
+ |
[ |
{ -asc => 'colA' }, | ORDER BY colA ASC, colB DESC,
- { -desc => [qw/colB/], | colC ASC, colD ASC
- { -asc => [qw/colC colD/],|
+ { -desc => [qw/colB/], | colC ASC, colD ASC,
+ { -asc => [qw/colC colD/],| colE DESC, FUNC(colF, ?)
+ \'colE DESC', | /* ...with $x bound to ? */
+ \[ 'FUNC(colF, ?)', $x ], |
] |
- ===========================================================
+ ===============================================================
projects / dbsrgits/SQL-Abstract.git / blobdiff