routines, triggering a connection before the set-cycle is finished
- Fix multi-value literal populate not working with simplified bind
specifications
+ - Massively improve the implied resultset condition parsing - now all
+ applicable conditions within a resultset should be properly picked
+ up by create() and populate()
+ - Ensure definitive condition extractor handles bizarre corner cases
+ without bombing out (RT#93244)
0.08270 2014-01-30 21:54 (PST)
* Fixes
sub _stack_cond {
my ($self, $left, $right) = @_;
- # collapse single element top-level conditions
- # (single pass only, unlikely to need recursion)
- for ($left, $right) {
- if (ref $_ eq 'ARRAY') {
- if (@$_ == 0) {
- $_ = undef;
- }
- elsif (@$_ == 1) {
- $_ = $_->[0];
- }
- }
- elsif (ref $_ eq 'HASH') {
- my ($first, $more) = keys %$_;
+ (
+ (ref $_ eq 'ARRAY' and !@$_)
+ or
+ (ref $_ eq 'HASH' and ! keys %$_)
+ ) and $_ = undef for ($left, $right);
- # empty hash
- if (! defined $first) {
- $_ = undef;
- }
- # one element hash
- elsif (! defined $more) {
- if ($first eq '-and' and ref $_->{'-and'} eq 'HASH') {
- $_ = $_->{'-and'};
- }
- elsif ($first eq '-or' and ref $_->{'-or'} eq 'ARRAY') {
- $_ = $_->{'-or'};
- }
- }
- }
+ # either on of the two undef or both undef
+ if ( ( (defined $left) xor (defined $right) ) or ! defined $left ) {
+ return defined $left ? $left : $right;
}
- # merge hashes with weeding out of duplicates (simple cases only)
- if (ref $left eq 'HASH' and ref $right eq 'HASH') {
+ my $cond = $self->result_source->schema->storage->_collapse_cond({ -and => [$left, $right] });
- # shallow copy to destroy
- $right = { %$right };
- for (grep { exists $right->{$_} } keys %$left) {
- # the use of eq_deeply here is justified - the rhs of an
- # expression can contain a lot of twisted weird stuff
- delete $right->{$_} if Data::Compare::Compare( $left->{$_}, $right->{$_} );
- }
+ for my $c (grep { ref $cond->{$_} eq 'ARRAY' and ($cond->{$_}[0]||'') eq '-and' } keys %$cond) {
- $right = undef unless keys %$right;
- }
+ my @vals = sort @{$cond->{$c}}[ 1..$#{$cond->{$c}} ];
+ my @fin = shift @vals;
+ for my $v (@vals) {
+ push @fin, $v unless Data::Compare::Compare( $fin[-1], $v );
+ }
- if (defined $left xor defined $right) {
- return defined $left ? $left : $right;
- }
- elsif (! defined $left) {
- return undef;
- }
- else {
- return { -and => [ $left, $right ] };
+ $cond->{$c} = (@fin == 1) ? $fin[0] : [-and => @fin ];
}
+
+ $cond;
}
=head2 search_literal
);
}
else {
- # precedence must be given to passed values over values inherited from
- # the cond, so the order here is important.
- my $collapsed_cond = $self->_collapse_cond($self->{cond});
- my %implied = %{$self->_remove_alias($collapsed_cond, $alias)};
+ if ($self->{cond}) {
+ my $implied = $self->_remove_alias(
+ $self->result_source->schema->storage->_collapse_cond($self->{cond}),
+ $alias,
+ );
- while ( my($col, $value) = each %implied ) {
- my $vref = ref $value;
- if (
- $vref eq 'HASH'
- and
- keys(%$value) == 1
- and
- (keys %$value)[0] eq '='
- ) {
- $new_data{$col} = $value->{'='};
- }
- elsif( !$vref or $vref eq 'SCALAR' or blessed($value) ) {
- $new_data{$col} = $value;
+ for my $c (keys %$implied) {
+ my $v = $implied->{$c};
+ if (
+ ! ref $v
+ or
+ overload::Method($v, '""')
+ ) {
+ $new_data{$c} = $v;
+ }
+ elsif (
+ ref $v eq 'HASH' and keys %$v == 1 and exists $v->{'='} and (
+ ref $v->{'='} eq 'SCALAR'
+ or
+ ( ref $v->{'='} eq 'REF' and ref ${$v->{'='}} eq 'ARRAY' )
+ )
+ ) {
+ $new_data{$c} = $v->{'='};
+ }
}
}
}
+ # precedence must be given to passed values over values inherited from
+ # the cond, so the order here is important.
%new_data = (
%new_data,
%{ $self->_remove_alias($data, $alias) },
return 0;
}
-# _collapse_cond
-#
-# Recursively collapse the condition.
-
-sub _collapse_cond {
- my ($self, $cond, $collapsed) = @_;
-
- $collapsed ||= {};
-
- if (ref $cond eq 'ARRAY') {
- foreach my $subcond (@$cond) {
- next unless ref $subcond; # -or
- $collapsed = $self->_collapse_cond($subcond, $collapsed);
- }
- }
- elsif (ref $cond eq 'HASH') {
- if (keys %$cond and (keys %$cond)[0] eq '-and') {
- foreach my $subcond (@{$cond->{-and}}) {
- $collapsed = $self->_collapse_cond($subcond, $collapsed);
- }
- }
- else {
- foreach my $col (keys %$cond) {
- my $value = $cond->{$col};
- $collapsed->{$col} = $value;
- }
- }
- }
-
- return $collapsed;
-}
-
# _remove_alias
#
# Remove the specified alias from the specified query hash. A copy is made so
# for all sources
sub _resolve_column_info {
my ($self, $ident, $colnames) = @_;
+
+ return {} if $colnames and ! @$colnames;
+
my $alias2src = $self->_resolve_ident_sources($ident);
my (%seen_cols, @auto_colnames);
my ($self, $ident, $order_by, $where) = @_;
my @cols = (
- (map { $_->[0] } $self->_extract_order_criteria($order_by)),
- $where ? @{$self->_extract_fixed_condition_columns($where)} :(),
+ ( map { $_->[0] } $self->_extract_order_criteria($order_by) ),
+ ( $where ? @{ $self->_extract_fixed_condition_columns($where) || [] } : () ),
) or return undef;
my $colinfo = $self->_resolve_column_info($ident, \@cols);
my $unqualified_idset = $main_rsrc->_identifying_column_set({
( $where ? %{
$self->_resolve_column_info(
- $main_rsrc, $self->_extract_fixed_condition_columns($where)
+ $main_rsrc, $self->_extract_fixed_condition_columns($where)||[]
)
} : () ),
%$order_portion_ci
die 'How did we get here...';
}
+# Attempts to flatten a passed in SQLA condition as much as possible towards
+# a plain hashref, *without* altering its semantics. Required by
+# create/populate being able to extract definitive conditions from preexisting
+# resultset {where} stacks
+#
+# FIXME - while relatively robust, this is still imperfect, one of the first
+# things to tackle with DQ
+sub _collapse_cond {
+ my ($self, $where, $where_is_anded_array) = @_;
+
+ if (! $where) {
+ return;
+ }
+ elsif ($where_is_anded_array or ref $where eq 'HASH') {
+
+ my @pairs;
+
+ my @pieces = $where_is_anded_array ? @$where : $where;
+ while (@pieces) {
+ my $chunk = shift @pieces;
+
+ if (ref $chunk eq 'HASH') {
+ push @pairs, map { [ $_ => $chunk->{$_} ] } sort keys %$chunk;
+ }
+ elsif (ref $chunk eq 'ARRAY') {
+ push @pairs, [ -or => $chunk ]
+ if @$chunk;
+ }
+ elsif ( ! ref $chunk) {
+ push @pairs, [ $chunk, shift @pieces ];
+ }
+ else {
+ push @pairs, [ '', $chunk ];
+ }
+ }
+
+ return unless @pairs;
+
+ my @conds = $self->_collapse_cond_unroll_pairs(\@pairs)
+ or return;
+
+ # Consolidate various @conds back into something more compact
+ my $fin;
+
+ for my $c (@conds) {
+ if (ref $c ne 'HASH') {
+ push @{$fin->{-and}}, $c;
+ }
+ else {
+ for my $col (sort keys %$c) {
+ if (exists $fin->{$col}) {
+ my ($l, $r) = ($fin->{$col}, $c->{$col});
+
+ (ref $_ ne 'ARRAY' or !@$_) and $_ = [ -and => $_ ] for ($l, $r);
+
+ if (@$l and @$r and $l->[0] eq $r->[0] and $l->[0] eq '-and') {
+ $fin->{$col} = [ -and => map { @$_[1..$#$_] } ($l, $r) ];
+ }
+ else {
+ $fin->{$col} = [ -and => $fin->{$col}, $c->{$col} ];
+ }
+ }
+ else {
+ $fin->{$col} = $c->{$col};
+ }
+ }
+ }
+ }
+
+ if ( ref $fin->{-and} eq 'ARRAY' and @{$fin->{-and}} == 1 ) {
+ my $piece = (delete $fin->{-and})->[0];
+ if (ref $piece eq 'ARRAY') {
+ $fin->{-or} = $fin->{-or} ? [ $piece, $fin->{-or} ] : $piece;
+ }
+ elsif (! exists $fin->{''}) {
+ $fin->{''} = $piece;
+ }
+ }
+
+ return $fin;
+ }
+ elsif (ref $where eq 'ARRAY') {
+ my @w = @$where;
+
+ while ( @w and (
+ (ref $w[0] eq 'ARRAY' and ! @{$w[0]} )
+ or
+ (ref $w[0] eq 'HASH' and ! keys %{$w[0]})
+ )) { shift @w };
+
+ return unless @w;
+
+ if ( @w == 1 ) {
+ return ( ref $w[0] )
+ ? $self->_collapse_cond($w[0])
+ : { $w[0] => undef }
+ ;
+ }
+ elsif ( ref $w[0] ) {
+ return \@w;
+ }
+ elsif ( @w == 2 ) {
+ if ( ( $w[0]||'' ) =~ /^\-and$/i ) {
+ return (ref $w[1] eq 'HASH' or ref $w[1] eq 'ARRAY')
+ ? $self->_collapse_cond($w[1], (ref $w[1] eq 'ARRAY') )
+ : $self->throw_exception("Unsupported top-level op/arg pair: [ $w[0] => $w[1] ]")
+ ;
+ }
+ else {
+ return $self->_collapse_cond({ @w });
+ }
+ }
+ }
+ else {
+ # not a hash not an array
+ return { '' => $where };
+ }
+
+ # catchall, some of the things above fall through
+ return $where;
+}
+
+sub _collapse_cond_unroll_pairs {
+ my ($self, $pairs) = @_;
+
+ my @conds;
+
+ while (@$pairs) {
+ my ($lhs, $rhs) = @{ shift @$pairs };
+
+ if ($lhs eq '') {
+ push @conds, $self->_collapse_cond($rhs);
+ }
+ elsif ( $lhs =~ /^\-and$/i ) {
+ push @conds, $self->_collapse_cond($rhs, (ref $rhs eq 'ARRAY'));
+ }
+ elsif ( $lhs =~ /^\-or$/i ) {
+ push @conds, $self->_collapse_cond(
+ (ref $rhs eq 'HASH') ? [ map { $_ => $rhs->{$_} } sort keys %$rhs ] : $rhs
+ );
+ }
+ else {
+ if (ref $rhs eq 'HASH' and ! keys %$rhs) {
+ # FIXME - SQLA seems to be doing... nothing...?
+ }
+ elsif (ref $rhs eq 'HASH' and keys %$rhs == 1 and exists $rhs->{'='}) {
+ for my $p ($self->_collapse_cond_unroll_pairs([ [ $lhs => $rhs->{'='} ] ])) {
+
+ # extra sanity check
+ if (keys %$p > 1) {
+ require Data::Dumper::Concise;
+ local $Data::Dumper::Deepcopy = 1;
+ $self->throw_exception(
+ "Internal error: unexpected collapse unroll:"
+ . Data::Dumper::Concise::Dumper { in => { $lhs => $rhs }, out => $p }
+ );
+ }
+
+ my ($l, $r) = %$p;
+
+ push @conds, ( ! ref $r or overload::Method($r, '""' ) )
+ ? { $l => $r }
+ : { $l => { '=' => $r } }
+ ;
+ }
+ }
+ elsif (ref $rhs eq 'ARRAY') {
+ # some of these conditionals encounter multi-values - roll them out using
+ # an unshift, which will cause extra looping in the while{} above
+ if (! @$rhs ) {
+ push @conds, { $lhs => [] };
+ }
+ elsif ( ($rhs->[0]||'') =~ /^\-(?:and|or)$/i ) {
+ $self->throw_exception("Value modifier not followed by any values: $lhs => [ $rhs->[0] ] ")
+ if @$rhs == 1;
+
+ if( $rhs->[0] =~ /^\-and$/i ) {
+ unshift @$pairs, map { [ $lhs => $_ ] } @{$rhs}[1..$#$rhs];
+ }
+ # if not an AND then it's an OR
+ elsif(@$rhs == 2) {
+ unshift @$pairs, [ $lhs => $rhs->[1] ];
+ }
+ else {
+ push @conds, { $lhs => $rhs };
+ }
+ }
+ elsif (@$rhs == 1) {
+ unshift @$pairs, [ $lhs => $rhs->[0] ];
+ }
+ else {
+ push @conds, { $lhs => $rhs };
+ }
+ }
+ else {
+ push @conds, { $lhs => $rhs };
+ }
+ }
+ }
+
+ return @conds;
+}
+
+
# returns an arrayref of column names which *definitely* have some
-# sort of non-nullable equality requested in the given condition
+# sort of non-nullable *single* equality requested in the given condition
# specification. This is used to figure out if a resultset is
# constrained to a column which is part of a unique constraint,
# which in turn allows us to better predict how ordering will behave
# this is a rudimentary, incomplete, and error-prone extractor
# however this is OK - it is conservative, and if we can not find
# something that is in fact there - the stack will recover gracefully
-# Also - DQ and the mst it rode in on will save us all RSN!!!
sub _extract_fixed_condition_columns {
- my ($self, $where) = @_;
-
- return unless ref $where eq 'HASH';
-
- my @cols;
- for my $lhs (keys %$where) {
- if ($lhs =~ /^\-and$/i) {
- push @cols, ref $where->{$lhs} eq 'ARRAY'
- ? ( map { @{ $self->_extract_fixed_condition_columns($_) } } @{$where->{$lhs}} )
- : @{ $self->_extract_fixed_condition_columns($where->{$lhs}) }
- ;
- }
- elsif ($lhs !~ /^\-/) {
- my $val = $where->{$lhs};
-
- push @cols, $lhs if (defined $val and (
- ! ref $val
+ my $self = shift;
+ my $where_hash = $self->_collapse_cond(shift);
+
+ my $res;
+ for my $c (keys %$where_hash) {
+ if (defined (my $v = $where_hash->{$c}) ) {
+ if (
+ ! ref $v
or
- (ref $val eq 'HASH' and keys %$val == 1 and defined $val->{'='})
- ));
+ (ref $v eq 'HASH' and keys %$v == 1 and defined $v->{'='} and (
+ ! ref $v->{'='}
+ or
+ ref $v->{'='} eq 'SCALAR'
+ or
+ ( ref $v->{'='} eq 'REF' and ref ${$v->{'='}} eq 'ARRAY' )
+ or
+ overload::Method($v->{'='}, '""')
+ ))
+ ) {
+ $res->{$c} = 1;
+ }
+ elsif (ref $v eq 'ARRAY' and ($v->[0]||'') eq '-and') {
+ $res->{$_} = 1 for map { @{ $self->_extract_fixed_condition_columns({ $c => $_ }) } } @{$v}[1..$#$v];
+ }
}
}
- return \@cols;
+
+ return [ sort keys %$res ];
}
1;
my $art_rs = $schema->resultset('Artist');
my $cd_rs = $schema->resultset('CD');
-my $restricted_art_rs = $art_rs->search({rank => 42});
+my $restricted_art_rs = $art_rs->search({ -and => [ rank => 42, charfield => { '=', \['(SELECT MAX(artistid) FROM artist) + ?', 6] } ] });
ok( $schema, 'Got a Schema object');
ok( $art_rs, 'Got Good Artist Resultset');
]);
## Did it use the condition in the resultset?
+ $more_crap->discard_changes;
cmp_ok( $more_crap->rank, '==', 42, "Got Correct rank for result object");
+ cmp_ok( $more_crap->charfield, '==', $more_crap->id + 5, "Got Correct charfield for result object");
}
}
})->first;
## Did it use the condition in the resultset?
+ $more_crap->discard_changes;
cmp_ok( $more_crap->rank, '==', 42, "Got Correct rank for result object");
+ cmp_ok( $more_crap->charfield, '==', $more_crap->id + 5, "Got Correct charfield for result object");
}
}
is $cooler->name, 'Cooler', 'Correct Name';
is $lamer->name, 'Lamer', 'Correct Name';
- cmp_ok $cooler->rank, '==', 42, 'Correct Rank';
+ for ($cooler, $lamer) {
+ $_->discard_changes;
+ cmp_ok( $_->rank, '==', 42, "Got Correct rank for result object");
+ cmp_ok( $_->charfield, '==', $_->id + 5, "Got Correct charfield for result object");
+ }
ARRAY_CONTEXT_WITH_COND_FROM_RS: {
]);
## Did it use the condition in the resultset?
+ $mega_lamer->discard_changes;
cmp_ok( $mega_lamer->rank, '==', 42, "Got Correct rank for result object");
+ cmp_ok( $mega_lamer->charfield, '==', $mega_lamer->id + 5, "Got Correct charfield for result object");
}
VOID_CONTEXT_WITH_COND_FROM_RS: {
## Did it use the condition in the resultset?
cmp_ok( $mega_lamer->rank, '==', 42, "Got Correct rank for result object");
+ cmp_ok( $mega_lamer->charfield, '==', $mega_lamer->id + 5, "Got Correct charfield for result object");
}
}
SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
(SELECT COUNT( * )
FROM cd siblings
- WHERE siblings.artist = me.artist
+ WHERE me.artist != ?
+ AND siblings.artist = me.artist
AND siblings.cdid != me.cdid
AND siblings.cdid != ?
- AND me.artist != ?
),
tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at
FROM cd me
[
# subselect
- [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' }
- => 23414 ],
-
[ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
=> 2 ],
+ [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' }
+ => 23414 ],
+
# outher WHERE
[ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
=> 2 ],
SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
(SELECT COUNT( * )
FROM cd siblings
- WHERE siblings.artist = me.artist
+ WHERE me.artist != ?
+ AND siblings.artist = me.artist
AND siblings.cdid != me.cdid
AND siblings.cdid != ?
- AND me.artist != ?
),
(SELECT MIN( year ), MAX( year )
FROM cd siblings
- WHERE siblings.artist = me.artist
- AND me.artist != ?
+ WHERE me.artist != ?
+ AND siblings.artist = me.artist
),
tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at
FROM cd me
[
# first subselect
- [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' }
- => 23414 ],
-
[ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
=> 2 ],
+ [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' }
+ => 23414 ],
+
# second subselect
[ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
=> 2 ],
$search_sql,
'SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
FROM cd me
- WHERE ( me.artist = ? AND me.title = ? AND me.genreid = ? )
+ WHERE ( me.artist = ? AND me.genreid = ? AND me.title = ? )
',
'expected select issued',
);
{
is_same_sql_bind(
$art_rs->as_query,
- "(SELECT me.artistid, me.name, me.rank, me.charfield FROM artist me WHERE ( ( ( rank = ? ) AND ( name = ? ) ) ) )",
- [ $rank_resolved_bind, $name_resolved_bind ],
+ "(SELECT me.artistid, me.name, me.rank, me.charfield FROM artist me WHERE name = ? AND rank = ? )",
+ [ $name_resolved_bind, $rank_resolved_bind ],
);
}
{
is_same_sql_bind(
$rscol->as_query,
- "(SELECT me.charfield FROM artist me WHERE ( ( ( rank = ? ) AND ( name = ? ) ) ) )",
- [ $rank_resolved_bind, $name_resolved_bind ],
+ "(SELECT me.charfield FROM artist me WHERE name = ? AND rank = ? )",
+ [ $name_resolved_bind, $rank_resolved_bind ],
);
}
my $rs;
{
- local $TODO = 'bind args order needs fixing (semifor)';
-
# First, the simple cases...
$rs = $schema->resultset('Artist')->search(
{ artistid => 1 },
is ( $rs->count, 1, 'where/bind last' );
# and the complex case
- local $TODO = 'bind args order needs fixing (semifor)';
$rs = $schema->resultset('CustomSql')->search({}, { bind => [ 1999 ] })
->search({ 'artistid' => 1 }, {
where => \'title like ?',
AND fourkeys_to_twokeys.f_foo = me.foo
AND fourkeys_to_twokeys.f_goodbye = me.goodbye
AND fourkeys_to_twokeys.f_hello = me.hello
- WHERE fourkeys_to_twokeys.pilot_sequence != ? AND ( bar = ? OR bar = ? ) AND ( foo = ? OR foo = ? ) AND ( goodbye = ? OR goodbye = ? ) AND ( hello = ? OR hello = ? ) AND sensors != ?
+ WHERE ( bar = ? OR bar = ? ) AND ( foo = ? OR foo = ? ) AND fourkeys_to_twokeys.pilot_sequence != ? AND ( goodbye = ? OR goodbye = ? ) AND ( hello = ? OR hello = ? ) AND sensors != ?
)
)
',
[
+ ("'1'", "'2'") x 2,
"'666'",
- ("'1'", "'2'") x 4,
+ ("'1'", "'2'") x 2,
"'c'",
],
'Correct update-SQL with multicolumn in support',
--- /dev/null
+use strict;
+use warnings;
+use Test::More;
+use Test::Warn;
+
+use lib qw(t/lib);
+use DBICTest;
+
+use DBIC::SqlMakerTest;
+use Data::Dumper;
+
+my $schema = DBICTest->init_schema( no_deploy => 1);
+my $sm = $schema->storage->sql_maker;
+
+for my $t (
+ {
+ where => { artistid => 1, charfield => undef },
+ cc_result => { artistid => 1, charfield => undef },
+ sql => 'WHERE artistid = ? AND charfield IS NULL',
+ efcc_result => [qw( artistid )],
+ },
+ {
+ where => { -and => [ artistid => 1, charfield => undef, { rank => 13 } ] },
+ cc_result => { artistid => 1, charfield => undef, rank => 13 },
+ sql => 'WHERE artistid = ? AND charfield IS NULL AND rank = ?',
+ efcc_result => [qw( artistid rank )],
+ },
+ {
+ where => { -and => [ { artistid => 1, charfield => undef}, { rank => 13 } ] },
+ cc_result => { artistid => 1, charfield => undef, rank => 13 },
+ sql => 'WHERE artistid = ? AND charfield IS NULL AND rank = ?',
+ efcc_result => [qw( artistid rank )],
+ },
+ {
+ where => { -and => [ -or => { name => 'Caterwauler McCrae' }, 'rank' ] },
+ cc_result => { name => 'Caterwauler McCrae', rank => undef },
+ sql => 'WHERE name = ? AND rank IS NULL',
+ efcc_result => [qw( name )],
+ },
+ {
+ where => { -and => [ [ [ artist => {'=' => \'foo' } ] ], { name => \[ '= ?', 'bar' ] } ] },
+ cc_result => { artist => {'=' => \'foo' }, name => \[ '= ?', 'bar' ] },
+ sql => 'WHERE artist = foo AND name = ?',
+ efcc_result => [qw( artist )],
+ },
+ {
+ where => { -and => [ -or => { name => 'Caterwauler McCrae', artistid => 2 } ] },
+ cc_result => { -or => [ artistid => 2, name => 'Caterwauler McCrae' ] },
+ sql => 'WHERE artistid = ? OR name = ?',
+ efcc_result => [],
+ },
+ {
+ where => { -and => [ \'foo=bar', [ { artistid => { '=', 3 } } ], { name => 'Caterwauler McCrae'} ] },
+ cc_result => { '' => \'foo=bar', name => 'Caterwauler McCrae', artistid => 3 },
+ sql => 'WHERE foo=bar AND artistid = ? AND name = ?',
+ efcc_result => [qw( artistid name )],
+ },
+ {
+ where => { artistid => [ 1 ], rank => [ 13, 2, 3 ], charfield => [ undef ] },
+ cc_result => { artistid => 1, charfield => undef, rank => [13, 2, 3] },
+ sql => 'WHERE artistid = ? AND charfield IS NULL AND ( rank = ? OR rank = ? OR rank = ? )',
+ efcc_result => [qw( artistid )],
+ },
+ {
+ where => { artistid => { '=' => 1 }, rank => { '>' => 12 }, charfield => { '=' => undef } },
+ cc_result => { artistid => 1, charfield => undef, rank => { '>' => 12 } },
+ sql => 'WHERE artistid = ? AND charfield IS NULL AND rank > ?',
+ efcc_result => [qw( artistid )],
+ },
+ {
+ where => { artistid => { '=' => [ 1 ], }, charfield => { '=' => [-and => \'1', \['?',2] ] }, rank => { '=' => [ 1, 2 ] } },
+ cc_result => { artistid => 1, charfield => [-and => { '=' => \'1' }, { '=' => \['?',2] } ], rank => { '=' => [1, 2] } },
+ sql => 'WHERE artistid = ? AND charfield = 1 AND charfield = ? AND ( rank = ? OR rank = ? )',
+ efcc_result => [qw( artistid charfield )],
+ },
+ {
+ where => { -and => [ artistid => 1, artistid => 2 ], name => [ -and => { '!=', 1 }, 2 ], charfield => [ -or => { '=', 2 } ], rank => [-and => undef, { '=', undef }, { '!=', 2 } ] },
+ cc_result => { artistid => [ -and => 1, 2 ], name => [ -and => { '!=', 1 }, 2 ], charfield => 2, rank => [ -and => undef, undef, { '!=', 2 } ] },
+ sql => 'WHERE artistid = ? AND artistid = ? AND charfield = ? AND name != ? AND name = ? AND rank IS NULL AND rank IS NULL AND rank != ?',
+ efcc_result => [qw( artistid charfield name )],
+ },
+ {
+ where => { -and => [
+ [ '_macro.to' => { -like => '%correct%' }, '_wc_macros.to' => { -like => '%correct%' } ],
+ { -and => [ { 'group.is_active' => 1 }, { 'me.is_active' => 1 } ] }
+ ] },
+ cc_result => {
+ 'group.is_active' => 1,
+ 'me.is_active' => 1,
+ -or => [
+ '_macro.to' => { -like => '%correct%' },
+ '_wc_macros.to' => { -like => '%correct%' },
+ ],
+ },
+ sql => 'WHERE ( _macro.to LIKE ? OR _wc_macros.to LIKE ? ) AND group.is_active = ? AND me.is_active = ?',
+ efcc_result => [qw( group.is_active me.is_active )],
+ },
+ {
+ where => { artistid => [] },
+ cc_result => { artistid => [] },
+ efcc_result => [],
+ },
+ (map {
+ {
+ where => { -and => $_ },
+ cc_result => undef,
+ efcc_result => [],
+ sql => '',
+ },
+ {
+ where => { -or => $_ },
+ cc_result => undef,
+ efcc_result => [],
+ sql => '',
+ },
+ } (
+ # bare
+ [], {},
+ # singles
+ [ {} ], [ [] ],
+ # doubles
+ [ [], [] ], [ {}, {} ], [ [], {} ], [ {}, [] ],
+ # tripples
+ [ {}, [], {} ], [ [], {}, [] ]
+ )),
+
+ # FIXME legacy compat crap, possibly worth undef/dieing in SQLMaker
+ { where => { artistid => {} }, sql => '', cc_result => undef, efcc_result => [] },
+
+ # batshit insanity, just to be thorough
+ {
+ where => { -and => [ [ 'artistid' ], [ -and => [ artistid => { '!=', 69 }, artistid => undef, artistid => { '=' => 200 } ]], artistid => [], { -or => [] }, { -and => [] }, [ 'charfield' ], { name => [] }, 'rank' ] },
+ cc_result => { artistid => [ -and => undef, { '!=', 69 }, undef, 200, [] ], charfield => undef, name => [], rank => undef },
+ sql => 'WHERE artistid IS NULL AND artistid != ? AND artistid IS NULL AND artistid = ? AND 0=1 AND charfield IS NULL AND 0=1 AND rank IS NULL',
+ efcc_result => [qw( artistid )],
+ },
+) {
+
+ for my $w (
+ $t->{where},
+ [ -and => $t->{where} ],
+ ( keys %{$t->{where}} <= 1 ) ? [ %{$t->{where}} ] : ()
+ ) {
+ my $name = do { local ($Data::Dumper::Indent, $Data::Dumper::Terse, $Data::Dumper::Sortkeys) = (0, 1, 1); Dumper $w };
+
+ my @orig_sql_bind = $sm->where($w);
+
+ is_same_sql ( $orig_sql_bind[0], $t->{sql}, "Expected SQL from $name" )
+ if exists $t->{sql};
+
+ my $collapsed_cond = $schema->storage->_collapse_cond($w);
+
+ is_same_sql_bind(
+ \[ $sm->where($collapsed_cond) ],
+ \\@orig_sql_bind,
+ "Collapse did not alter final SQL based on $name",
+ );
+
+ is_deeply(
+ $collapsed_cond,
+ $t->{cc_result},
+ "Expected collapsed condition produced on $name",
+ );
+
+ is_deeply(
+ $schema->storage->_extract_fixed_condition_columns($w),
+ $t->{efcc_result},
+ "Expected fixed_condition produced on $name",
+ );
+ }
+}
+
+done_testing;
my $schema = DBICTest->init_schema;
my $native_limit_dialect = $schema->storage->sql_maker->{limit_dialect};
+my $where_string = 'me.title = ? AND source != ? AND source = ?';
+
my @where_bind = (
- [ {} => 'Study' ],
[ {} => 'kama sutra' ],
+ [ {} => 'Study' ],
[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
);
my @select_bind = (
LimitOffset => {
limit => [
- '(
+ "(
SELECT me.id, owner.id, owner.name, ? * ?, ?
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
LIMIT ?
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
limit_offset => [
- '(
+ "(
SELECT me.id, owner.id, owner.name, ? * ?, ?
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
LIMIT ?
OFFSET ?
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
ordered_limit => [
- '(
+ "(
SELECT me.id, owner.id, owner.name, ? * ?, ?
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY ? / ?, ?
LIMIT ?
- )',
+ )",
[
@select_bind,
@where_bind,
]
],
ordered_limit_offset => [
- '(
+ "(
SELECT me.id, owner.id, owner.name, ? * ?, ?
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY ? / ?, ?
LIMIT ?
OFFSET ?
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
limit_offset_prefetch => [
- '(
+ "(
SELECT me.name, books.id, books.source, books.owner, books.title, books.price
FROM (
SELECT me.name, me.id
) me
LEFT JOIN books books
ON books.owner = me.id
- )',
+ )",
[
[ { sqlt_datatype => 'integer' } => 3 ],
[ { sqlt_datatype => 'integer' } => 1 ],
LimitXY => {
ordered_limit_offset => [
- '(
+ "(
SELECT me.id, owner.id, owner.name, ? * ?, ?
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY ? / ?, ?
LIMIT ?, ?
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
limit_offset_prefetch => [
- '(
+ "(
SELECT me.name, books.id, books.source, books.owner, books.title, books.price
FROM (
SELECT me.name, me.id
) me
LEFT JOIN books books
ON books.owner = me.id
- )',
+ )",
[
[ { sqlt_datatype => 'integer' } => 1 ],
[ { sqlt_datatype => 'integer' } => 3 ],
SkipFirst => {
ordered_limit_offset => [
- '(
+ "(
SELECT SKIP ? FIRST ? me.id, owner.id, owner.name, ? * ?, ?
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY ? / ?, ?
- )',
+ )",
[
[ { sqlt_datatype => 'integer' } => 3 ],
[ { sqlt_datatype => 'integer' } => 4 ],
],
],
limit_offset_prefetch => [
- '(
+ "(
SELECT me.name, books.id, books.source, books.owner, books.title, books.price
FROM (
SELECT SKIP ? FIRST ? me.name, me.id
) me
LEFT JOIN books books
ON books.owner = me.id
- )',
+ )",
[
[ { sqlt_datatype => 'integer' } => 1 ],
[ { sqlt_datatype => 'integer' } => 3 ],
FirstSkip => {
ordered_limit_offset => [
- '(
+ "(
SELECT FIRST ? SKIP ? me.id, owner.id, owner.name, ? * ?, ?
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY ? / ?, ?
- )',
+ )",
[
[ { sqlt_datatype => 'integer' } => 4 ],
[ { sqlt_datatype => 'integer' } => 3 ],
],
],
limit_offset_prefetch => [
- '(
+ "(
SELECT me.name, books.id, books.source, books.owner, books.title, books.price
FROM (
SELECT FIRST ? SKIP ? me.name, me.id
) me
LEFT JOIN books books
ON books.owner = me.id
- )',
+ )",
[
[ { sqlt_datatype => 'integer' } => 3 ],
[ { sqlt_datatype => 'integer' } => 1 ],
},
RowNumberOver => do {
- my $unordered_sql = '(
+ my $unordered_sql = "(
SELECT me.id, owner__id, owner__name, bar, baz
FROM (
SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER() AS rno__row__index
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
) me
) me
WHERE rno__row__index >= ? AND rno__row__index <= ?
- )';
+ )";
- my $ordered_sql = '(
+ my $ordered_sql = "(
SELECT me.id, owner__id, owner__name, bar, baz
FROM (
SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER( ORDER BY ORDER__BY__001, ORDER__BY__002 ) AS rno__row__index
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
) me
) me
WHERE rno__row__index >= ? AND rno__row__index <= ?
- )';
+ )";
{
limit => [$unordered_sql,
],
],
limit_offset_prefetch => [
- '(
+ "(
SELECT me.name, books.id, books.source, books.owner, books.title, books.price
FROM (
SELECT me.name, me.id
) me
LEFT JOIN books books
ON books.owner = me.id
- )',
+ )",
[
[ { sqlt_datatype => 'integer' } => 2 ],
[ { sqlt_datatype => 'integer' } => 4 ],
RowNum => do {
my $limit_sql = sub {
- sprintf '(
+ sprintf "(
SELECT me.id, owner__id, owner__name, bar, baz
FROM (
SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
%s
) me
WHERE ROWNUM <= ?
- )', $_[0] || '';
+ )", $_[0] || '';
};
{
],
],
limit_offset => [
- '(
+ "(
SELECT me.id, owner__id, owner__name, bar, baz
FROM (
SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM AS rownum__index
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
) me
) me
WHERE rownum__index BETWEEN ? AND ?
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
ordered_limit_offset => [
- '(
+ "(
SELECT me.id, owner__id, owner__name, bar, baz
FROM (
SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM AS rownum__index
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY ? / ?, ?
WHERE ROWNUM <= ?
) me
WHERE rownum__index >= ?
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
limit_offset_prefetch => [
- '(
+ "(
SELECT me.name, books.id, books.source, books.owner, books.title, books.price
FROM (
SELECT me.name, me.id
) me
LEFT JOIN books books
ON books.owner = me.id
- )',
+ )",
[
[ { sqlt_datatype => 'integer' } => 2 ],
[ { sqlt_datatype => 'integer' } => 4 ],
FetchFirst => {
limit => [
- '(
+ "(
SELECT me.id, owner.id, owner.name, ? * ?, ?
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
FETCH FIRST 4 ROWS ONLY
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
limit_offset => [
- '(
+ "(
SELECT me.id, owner__id, owner__name, bar, baz
FROM (
SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY me.id
) me
ORDER BY me.id DESC
FETCH FIRST 4 ROWS ONLY
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
ordered_limit => [
- '(
+ "(
SELECT me.id, owner.id, owner.name, ? * ?, ?
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY ? / ?, ?
FETCH FIRST 4 ROWS ONLY
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
ordered_limit_offset => [
- '(
+ "(
SELECT me.id, owner__id, owner__name, bar, baz
FROM (
SELECT me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY ? / ?, ?
FETCH FIRST 4 ROWS ONLY
) me
ORDER BY ORDER__BY__001, ORDER__BY__002
- )',
+ )",
[
@select_bind,
@order_bind,
],
],
limit_offset_prefetch => [
- '(
+ "(
SELECT me.name, books.id, books.source, books.owner, books.title, books.price
FROM (
SELECT me.name, me.id
) me
LEFT JOIN books books
ON books.owner = me.id
- )',
+ )",
[],
],
},
Top => {
limit => [
- '(
+ "(
SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
limit_offset => [
- '(
+ "(
SELECT TOP 4 me.id, owner__id, owner__name, bar, baz
FROM (
SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY me.id
) me
ORDER BY me.id DESC
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
ordered_limit => [
- '(
+ "(
SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ?
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY ? / ?, ?
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
ordered_limit_offset => [
- '(
+ "(
SELECT me.id, owner__id, owner__name, bar, baz
FROM (
SELECT TOP 4 me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
ORDER BY ? / ?, ?
ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC
) me
ORDER BY ORDER__BY__001, ORDER__BY__002
- )',
+ )",
[
@select_bind,
@order_bind,
],
],
limit_offset_prefetch => [
- '(
+ "(
SELECT me.name, books.id, books.source, books.owner, books.title, books.price
FROM (
SELECT TOP 3 me.name, me.id
) me
LEFT JOIN books books
ON books.owner = me.id
- )',
+ )",
[],
],
},
GenericSubQ => {
ordered_limit => [
- '(
+ "(
SELECT me.id, owner__id, owner__name, bar, baz
FROM (
SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
) me
)
) < ?
ORDER BY me.price DESC, me.id ASC
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
ordered_limit_offset => [
- '(
+ "(
SELECT me.id, owner__id, owner__name, bar, baz
FROM (
SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price
FROM books me
JOIN owners owner
ON owner.id = me.owner
- WHERE source != ? AND me.title = ? AND source = ?
+ WHERE $where_string
GROUP BY (me.id / ?), owner.id
HAVING ?
) me
)
) BETWEEN ? AND ?
ORDER BY me.price DESC, me.id ASC
- )',
+ )",
[
@select_bind,
@where_bind,
],
],
limit_offset_prefetch => [
- '(
+ "(
SELECT me.name, books.id, books.source, books.owner, books.title, books.price
FROM (
SELECT me.name, me.id
LEFT JOIN books books
ON books.owner = me.id
ORDER BY me.name ASC, me.id DESC
- )',
+ )",
[
[ { sqlt_datatype => 'integer' } => 1 ],
[ { sqlt_datatype => 'integer' } => 3 ],
projects / dbsrgits/DBIx-Class.git / commitdiff