From: Johannes Plunien Date: Sun, 3 May 2009 08:52:07 +0000 (+0000) Subject: Methods update/delete on resultset use now new as_query method to updated/delete... X-Git-Tag: v0.08103~117^2~4 X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits%2FDBIx-Class.git;a=commitdiff_plain;h=0f6fc7050c1f6120a1bae77ec57def4e965ac332 Methods update/delete on resultset use now new as_query method to updated/delete properly on joined/prefetched resultset using a subquery. Therefore some tests have been added and some have been changed as well as the warnings around $rs->update/delete have been removed. Cheers! --- diff --git a/lib/DBIx/Class/ResultSet.pm b/lib/DBIx/Class/ResultSet.pm index 7f53ec3..4f8f35f 100644 --- a/lib/DBIx/Class/ResultSet.pm +++ b/lib/DBIx/Class/ResultSet.pm @@ -1332,49 +1332,8 @@ sub _cond_for_update_delete { # No-op. No condition, we're updating/deleting everything return $cond unless ref $full_cond; - if (ref $full_cond eq 'ARRAY') { - $cond = [ - map { - my %hash; - foreach my $key (keys %{$_}) { - $key =~ /([^.]+)$/; - $hash{$1} = $_->{$key}; - } - \%hash; - } @{$full_cond} - ]; - } - elsif (ref $full_cond eq 'HASH') { - if ((keys %{$full_cond})[0] eq '-and') { - $cond->{-and} = []; - - my @cond = @{$full_cond->{-and}}; - for (my $i = 0; $i < @cond; $i++) { - my $entry = $cond[$i]; - - my $hash; - if (ref $entry eq 'HASH') { - $hash = $self->_cond_for_update_delete($entry); - } - else { - $entry =~ /([^.]+)$/; - $hash->{$1} = $cond[++$i]; - } - - push @{$cond->{-and}}, $hash; - } - } - else { - foreach my $key (keys %{$full_cond}) { - $key =~ /([^.]+)$/; - $cond->{$1} = $full_cond->{$key}; - } - } - } - else { - $self->throw_exception( - "Can't update/delete on resultset with condition unless hash or array" - ); + foreach my $pk ($self->result_source->primary_columns) { + $cond->{$pk} = { IN => $self->get_column($pk)->as_query({ skip_parens => 1 }) }; } return $cond; @@ -1402,13 +1361,8 @@ sub update { $self->throw_exception("Values for update must be a hash") unless ref $values eq 'HASH'; - carp( 'WARNING! Currently $rs->update() does not generate proper SQL' - . ' on joined resultsets, and may affect rows well outside of the' - . ' contents of $rs. Use at your own risk' ) - if ( $self->{attrs}{seen_join} ); - my $cond = $self->_cond_for_update_delete; - + return $self->result_source->storage->update( $self->result_source, $values, $cond ); @@ -1456,10 +1410,6 @@ to run. See also L. delete may not generate correct SQL for a query with joins or a resultset chained from a related resultset. In this case it will generate a warning:- - WARNING! Currently $rs->delete() does not generate proper SQL on - joined resultsets, and may delete rows well outside of the contents - of $rs. Use at your own risk - In these cases you may find that delete_all is more appropriate, or you need to respecify your query in a way that can be expressed without a join. @@ -1469,10 +1419,7 @@ sub delete { my ($self) = @_; $self->throw_exception("Delete should not be passed any arguments") if $_[1]; - carp( 'WARNING! Currently $rs->delete() does not generate proper SQL' - . ' on joined resultsets, and may delete rows well outside of the' - . ' contents of $rs. Use at your own risk' ) - if ( $self->{attrs}{seen_join} ); + my $cond = $self->_cond_for_update_delete; $self->result_source->storage->delete($self->result_source, $cond); @@ -1863,7 +1810,7 @@ sub _remove_alias { =over 4 -=item Arguments: none +=item Arguments: \%opts =item Return Value: \[ $sql, @bind ] @@ -1875,6 +1822,14 @@ This is generally used as the RHS for a subquery. B: This feature is still experimental. +The query returned will be surrounded by parentheses, e.g: + + ( SELECT cdid FROM cd WHERE title LIKE '%Hits%' ) + +This behaviour can be changed by passing special options: + + $rs->get_column('cdid')->as_query({ skip_parens => 1 }); + =cut sub as_query { return shift->cursor->as_query(@_) } diff --git a/lib/DBIx/Class/ResultSetColumn.pm b/lib/DBIx/Class/ResultSetColumn.pm index 596df7c..646e084 100644 --- a/lib/DBIx/Class/ResultSetColumn.pm +++ b/lib/DBIx/Class/ResultSetColumn.pm @@ -58,7 +58,7 @@ sub new { =over 4 -=item Arguments: none +=item Arguments: See L =item Return Value: \[ $sql, @bind ] @@ -72,7 +72,7 @@ B: This feature is still experimental. =cut -sub as_query { return shift->_resultset->as_query } +sub as_query { return shift->_resultset->as_query(@_) } =head2 next diff --git a/lib/DBIx/Class/Storage/DBI/Cursor.pm b/lib/DBIx/Class/Storage/DBI/Cursor.pm index 60df379..454738c 100644 --- a/lib/DBIx/Class/Storage/DBI/Cursor.pm +++ b/lib/DBIx/Class/Storage/DBI/Cursor.pm @@ -53,7 +53,7 @@ sub new { =over 4 -=item Arguments: none +=item Arguments: See L =item Return Value: \[ $sql, @bind ] @@ -64,7 +64,12 @@ Returns the SQL statement and bind vars associated with the invocant. =cut sub as_query { - my $self = shift; + my ( $self, $opts ) = @_; + + $self->throw_exception( "as_query needs a hashref" ) + if defined $opts and ref $opts ne 'HASH'; + + $opts->{skip_parens} ||= 0; my $storage = $self->{storage}; my $sql_maker = $storage->sql_maker; @@ -72,7 +77,8 @@ sub as_query { my @args = $storage->_select_args(@{$self->{args}}); my ($sql, $bind) = $storage->_prep_for_execute(@args[0 .. 2], [@args[4 .. $#args]]); - return \[ "($sql)", @$bind ]; + $sql = "($sql)" unless $opts->{skip_parens}; + return \[ $sql, @$bind ]; } =head2 next diff --git a/t/53delete_chained.t b/t/53delete_chained.t index 4619548..6d06be9 100644 --- a/t/53delete_chained.t +++ b/t/53delete_chained.t @@ -4,7 +4,7 @@ use warnings; use lib qw(t/lib); use DBICTest; -plan tests => 9; +plan tests => 7; # This set of tests attempts to do a delete on a chained resultset, which # would lead to SQL DELETE with a JOIN, which is not supported by the @@ -25,9 +25,10 @@ cmp_ok($total_tracks, '>', 0, 'need track records'); my $artist_tracks = $artist->cds->search_related('tracks')->count; cmp_ok($artist_tracks, '<', $total_tracks, 'need more tracks than just related tracks'); - ok(!eval{$artist->cds->search_related('tracks')->delete}); - cmp_ok($schema->resultset('Track')->count, '==', $total_tracks, 'No tracks should be deleted'); - like ($w, qr/Currently \$rs->delete\(\) does not generate proper SQL/, 'Delete join warning'); + my $rs = $artist->cds->search_related('tracks'); + $total_tracks -= $rs->count; + ok($rs->delete); + cmp_ok($schema->resultset('Track')->count, '==', $total_tracks, '3 tracks should be deleted'); } # test that delete_related w/conditions deletes just the matched related records only @@ -39,7 +40,8 @@ cmp_ok($total_tracks, '>', 0, 'need track records'); my $artist2_tracks = $artist2->search_related('cds')->search_related('tracks')->count; cmp_ok($artist2_tracks, '<', $total_tracks, 'need more tracks than related tracks'); - ok(!eval{$artist2->search_related('cds')->search_related('tracks')->delete}); + my $rs = $artist2->search_related('cds')->search_related('tracks'); + $total_tracks -= $rs->count; + ok($rs->delete); cmp_ok($schema->resultset('Track')->count, '==', $total_tracks, 'No tracks should be deleted'); - like ($w, qr/Currently \$rs->delete\(\) does not generate proper SQL/, 'Delete join warning'); } diff --git a/t/76joins.t b/t/76joins.t index 84d8ba5..f5ab52d 100644 --- a/t/76joins.t +++ b/t/76joins.t @@ -17,7 +17,7 @@ BEGIN { eval "use DBD::SQLite"; plan $@ ? ( skip_all => 'needs DBD::SQLite for testing' ) - : ( tests => 18 ); + : ( tests => 33 ); } # figure out if we've got a version of sqlite that is older than 3.2.6, in @@ -179,28 +179,79 @@ cmp_ok( $rs->count, '==', 1, "Single record in resultset"); is($rs->first->name, 'We Are Goth', 'Correct record returned'); -# test for warnings on delete of joined resultset -$rs = $schema->resultset("CD")->search( - { 'artist.name' => 'Caterwauler McCrae' }, - { join => [qw/artist/]} -); -my $tst_delete_warning; -eval { - local $SIG{__WARN__} = sub { $tst_delete_warning = shift }; - $rs->delete(); -}; -ok( ($@ || $tst_delete_warning), 'fail/warning on attempt to delete a join-ed resultset'); - -# test for warnings on update of joined resultset -$rs = $schema->resultset("CD")->search( - { 'artist.name' => 'Random Boy Band' }, - { join => [qw/artist/]} -); -my $tst_update_warning; -eval { - local $SIG{__WARN__} = sub { $tst_update_warning = shift }; - $rs->update({ 'artist' => 1 }); -}; - -ok( ($@ || $tst_update_warning), 'fail/warning on attempt to update a join-ed resultset'); +{ + $schema->populate('Artist', [ + [ qw/artistid name/ ], + [ 4, 'Another Boy Band' ], + ]); + $schema->populate('CD', [ + [ qw/cdid artist title year/ ], + [ 6, 2, "Greatest Hits", 2001 ], + [ 7, 4, "Greatest Hits", 2005 ], + [ 8, 4, "BoyBandBlues", 2008 ], + ]); + $schema->populate('TwoKeys', [ + [ qw/artist cd/ ], + [ 2, 4 ], + [ 2, 6 ], + [ 4, 7 ], + [ 4, 8 ], + ]); + + sub cd_count { + return $schema->resultset("CD")->count; + } + sub tk_count { + return $schema->resultset("TwoKeys")->count; + } + + cmp_ok(cd_count(), '==', 8, '8 rows in table cd'); + cmp_ok(tk_count(), '==', 7, '7 rows in table twokeys'); + + sub artist1 { + return $schema->resultset("CD")->search( + { 'artist.name' => 'Caterwauler McCrae' }, + { join => [qw/artist/]} + ); + } + sub artist2 { + return $schema->resultset("CD")->search( + { 'artist.name' => 'Random Boy Band' }, + { join => [qw/artist/]} + ); + } + + cmp_ok( artist1()->count, '==', 3, '3 Caterwauler McCrae CDs' ); + ok( artist1()->delete, 'Successfully deleted 3 CDs' ); + cmp_ok( artist1()->count, '==', 0, '0 Caterwauler McCrae CDs' ); + cmp_ok( artist2()->count, '==', 2, '3 Random Boy Band CDs' ); + ok( artist2()->update( { 'artist' => 1 } ) ); + cmp_ok( artist2()->count, '==', 0, '0 Random Boy Band CDs' ); + cmp_ok( artist1()->count, '==', 2, '2 Caterwauler McCrae CDs' ); + + # test update on multi-column-pk + sub tk1 { + return $schema->resultset("TwoKeys")->search( + { + 'artist.name' => { like => '%Boy Band' }, + 'cd.title' => 'Greatest Hits', + }, + { join => [qw/artist cd/] } + ); + } + sub tk2 { + return $schema->resultset("TwoKeys")->search( + { 'artist.name' => 'Caterwauler McCrae' }, + { join => [qw/artist/]} + ); + } + cmp_ok( tk2()->count, '==', 2, 'TwoKeys count == 2' ); + cmp_ok( tk1()->count, '==', 2, 'TwoKeys count == 2' ); + ok( tk1()->update( { artist => 1 } ) ); + cmp_ok( tk1()->count, '==', 0, 'TwoKeys count == 0' ); + cmp_ok( tk2()->count, '==', 4, '2 Caterwauler McCrae CDs' ); + ok( tk2()->delete, 'Successfully deleted 4 CDs' ); + cmp_ok(cd_count(), '==', 5, '5 rows in table cd'); + cmp_ok(tk_count(), '==', 3, '3 rows in table twokeys'); +} diff --git a/t/resultset/as_query.t b/t/resultset/as_query.t index 7f4c738..5e9a7fe 100644 --- a/t/resultset/as_query.t +++ b/t/resultset/as_query.t @@ -7,7 +7,7 @@ use Data::Dumper; use Test::More; -plan ( tests => 4 ); +plan ( tests => 6 ); use lib qw(t/lib); use DBICTest; @@ -66,4 +66,20 @@ my $rscol = $art_rs->get_column( 'charfield' ); ); } +{ + my $rs = $schema->resultset("CD")->search( + { 'artist.name' => 'Caterwauler McCrae' }, + { join => [qw/artist/]} + ); + my $query = $rs->get_column('cdid')->as_query({ skip_parens => 1 }); + my ($sql, @bind) = @{$$query}; + is_same_sql_bind( + $sql, \@bind, + 'SELECT me.cdid FROM cd me JOIN artist artist ON artist.artistid = me.artist WHERE ( artist.name = ? )', + [['artist.name' => 'Caterwauler McCrae']] + ); + my $subsel_rs = $schema->resultset("CD")->search( { cdid => { IN => $query } } ); + cmp_ok($subsel_rs->count, '==', $rs->count, 'Subselect on PK got the same row count'); +} + __END__