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

Reply via email to