Hi,
this is part of a long running debug story. I can't exactly recall on
which
channels I've already asked questions related to this issue. The real
case
is about deleting a "project" in a database with all its related records
before I applied ON-DELETE-triggers.
However I recently compiled a minimal example of the issue and I'd like
to
understand what actually happens and get a workaround or a fix.
The problem occurs with a resultset of all records of 2nd-level
relationship.
A has many B.
B has many C.
Delete all C of all B of A(42).
For other actions than delete(), i.e. select a whole column, it works as
I had expected:
SELECT c.x FROM b me JOIN c c ON c.b = me.id WHERE ( me.a = ? ): '1'
For delete() it starts a transaction, fetches all PKs and enumerates
them in a new statement. This made an actual app explode when the
query size increased over the maximum allowed length because of all
the IDs listed.
BEGIN WORK
SELECT c.b, c.x FROM b me JOIN c c ON c.b = me.id WHERE ( me.a = ? )
GROUP BY c.b, c.x: '1'
DELETE FROM c WHERE ( ( ( b = ? AND x = ? ) OR ( b = ? AND x = ? ) OR (
b = ? AND x = ? ) ) ): '1', '1001', '1', '1002', '1', '1003'
COMMIT
See my attached delete.pl for an test case with commented example calls.
Start it with
$ DBIC_TRACE=1 perl delete.pl
Why does delete() behave differently?
How to efficently delete a 2nd-level relationship resultset?
Kind regards
Daniel
CREATE TABLE a (
id INTEGER PRIMARY KEY NOT NULL
):
CREATE TABLE b (
id INTEGER PRIMARY KEY NOT NULL,
a INT NOT NULL,
FOREIGN KEY (a) REFERENCES a(id) ON DELETE CASCADE ON UPDATE CASCADE
):
CREATE INDEX b_idx_a ON b (a):
CREATE TABLE c (
b INT NOT NULL,
x INT NOT NULL,
PRIMARY KEY (b, x),
FOREIGN KEY (b) REFERENCES b(id) ON DELETE CASCADE ON UPDATE CASCADE
):
CREATE INDEX c_idx_b ON c (b):
INSERT INTO a DEFAULT VALUES:
INSERT INTO b ( a) VALUES ( ? ): '1'
INSERT INTO c ( b, x) VALUES ( ?, ? ): '1', '1001'
INSERT INTO c ( b, x) VALUES ( ?, ? ): '1', '1002'
INSERT INTO c ( b, x) VALUES ( ?, ? ): '1', '1003'
SELECT COUNT( * ) FROM b me JOIN c c ON c.b = me.id WHERE ( me.a = ? ): '1'
SELECT c.x FROM b me JOIN c c ON c.b = me.id WHERE ( me.a = ? ): '1'
BEGIN WORK
SELECT c.b, c.x FROM b me JOIN c c ON c.b = me.id WHERE ( me.a = ? ) GROUP BY
c.b, c.x: '1'
DELETE FROM c WHERE ( ( ( b = ? AND x = ? ) OR ( b = ? AND x = ? ) OR ( b = ?
AND x = ? ) ) ): '1', '1001', '1', '1002', '1', '1003'
COMMIT
DELETE FROM c WHERE ( b = ? ): '1'
use strict;
use warnings;
package MySchema::Result::A;
use parent 'DBIx::Class::Core';
__PACKAGE__->table('a');
__PACKAGE__->add_columns( id => { data_type => 'INT', is_auto_increment => 1 }
);
__PACKAGE__->set_primary_key('id');
__PACKAGE__->has_many( b => 'MySchema::Result::B' );
package MySchema::Result::B;
use parent 'DBIx::Class::Core';
__PACKAGE__->table('b');
__PACKAGE__->add_columns(
id => { data_type => 'INT', is_auto_increment => 1 },
a => { data_type => 'INT' },
);
__PACKAGE__->set_primary_key('id');
__PACKAGE__->belongs_to( a => 'MySchema::Result::A' );
__PACKAGE__->has_many( c => 'MySchema::Result::C' );
package MySchema::Result::C;
use parent 'DBIx::Class::Core';
__PACKAGE__->table('c');
__PACKAGE__->add_columns(
b => { data_type => 'INT' },
x => { data_type => 'INT' },
);
__PACKAGE__->set_primary_key( 'b', 'x' );
__PACKAGE__->belongs_to( b => 'MySchema::Result::B' );
package MySchema;
use parent 'DBIx::Class::Schema';
__PACKAGE__->load_classes( { 'MySchema::Result' => [qw< A B C >] } );
package main;
my $schema = MySchema->connect('dbi:SQLite::memory:');
$schema->deploy();
my $a = $schema->resultset('A')->create( {} );
my $b = $a->create_related( b => {} );
$b->create_related( c => { x => 1000 + $_ } ) for 1 .. 3;
my $c = $a->search_related('b')->search_related('c');
$c->count(); # uses 1 level JOIN on table 'b'
$c->get_column('x')->all; # uses 2 level JOIN on table 'c'
$c->delete(); # fetches and enumerates PKs
$b->search_related('c')->delete(); # 1st level relationship works as expected
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk