a noob delete/cascade problem

[Dustin Suchter]

This is only slightly modified code from the tutorial on CPAN. I
probably mis-copied something and hence my problem, but I can't seem
to figure out what.

I have a a controller called Campaigns.pm with the delete method
below (comments and empty lines removed):

124 sub delete : Local {
126     my ($self, $c, $id) = @_;
129     $c->model('AdBlueDB::Campaign')->search({id =>
$id})->delete_all;
132     $c->flash->{status_msg} = 'Campaign deleted';
135     $c->response->redirect($c->uri_for('/campaigns/list'));
136 }

My DB model Campaign.pm has the necessary has_many relationship to
populate a relationship table in my db:

23 __PACKAGE__->has_many(campaign_client =>
'AdBlueDB::CampaignClient', 'campaign_id');

My SQL is as follows:

84 CREATE TABLE `campaign_clients` (
85   `campaign_id`     int(32) unsigned              NOT NULL
                    COMMENT '',
86   `client_id`       int(32) unsigned              NOT NULL
                    COMMENT '',
87   FOREIGN KEY (`campaign_id`) REFERENCES campaigns(id),
88   FOREIGN KEY (`client_id`) REFERENCES clients(id)
89 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
COMMENT='' AUTO_INCREMENT=0;

I can create new campaigns and the primary "campaign" table gets all
the right stuff along with the "campaign_client" table (it also gets
the right relationship data and stores it properly).

I'm wondering why I get the error:

DBIx::Class::ResultSet::delete_all(): DBI Exception: DBD::mysql::st
execute failed: Cannot delete or update a parent row: a foreign key
constraint fails [for Statement "DELETE FROM campaigns WHERE ( id =
? )" with ParamValues: 0='47'] at... AdBlue/Controller/Campaigns.pm
line 129

when I use my delete method?

>From what I can tell the DBIx::Class::Relationship documentation
indicates that the dependent child tables will be deleted
automatically unless I specifically turn off cascade_delete, so why
am I getting an error that I essentially translate as "I can't
delete this row because it has child dependencies"?

http://cpan.uwinnipeg.ca/htdocs/DBIx-Class/DBIx/Class/Relationship.html#has_many

"If you delete an object in a class with a has_many relationship,
all the related objects will be deleted as well. To turn this
behaviour off, pass cascade_delete => 0 in the $attr hashref.
However, any database-level cascade or restrict will take precedence
over a DBIx-Class-based cascading delete."


I know this has got to be something simple, so I guess I haven't
wrapped my head around the DB relationships yet. The only somewhat
significant difference I can find between my SQL and the CPAN
example is that I use Foreign Keys and I don't see that in the
example. Dunno if that matters though...

thanks!
-d

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Dustin Suchter]

Well, I found a solution to my problem. I'm not sure it is the most
elegant, but here goes:

I figured that just putting the proper cascade logic into my actual
DB schema would fix things, and it does. I changed my table
definition to:

84 CREATE TABLE `campaign_clients` (
85   `campaign_id` int(32) unsigned NOT NULL COMMENT '',
86   `client_id` int(32) unsigned NOT NULL COMMENT '',
87   FOREIGN KEY (`campaign_id`) REFERENCES campaigns(id) ON DELETE
CASCADE ON UPDATE CASCADE,
88   FOREIGN KEY (`client_id`) REFERENCES clients(id) ON DELETE
CASCADE ON UPDATE CASCADE
89 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
COMMENT='' AUTO_INCREMENT=0;

Obviously I could also have just ALTERed the tables (in case you
don't want to trash all your data just to fix this silly little
problem):

ALTER TABLE `campaign_clients` ADD CONSTRAINT `campaign_clients`
FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE
CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `campaign_clients` FOREIGN
KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE ON
UPDATE CASCADE;

I think a better solution (TIMTOWTDI) is to for DBIx to somehow
automatically detect the constraint and transact a delete that
removes the child rows first and then the parent rows, in order to
make sure everything gets deleted. I think that one is a pipe dream
so I'll happily use my solution above for now.

-d

Dustin Suchter wrote:

> This is only slightly modified code from the tutorial on CPAN. I
> probably mis-copied something and hence my problem, but I can't seem
> to figure out what.
>
> I have a a controller called Campaigns.pm with the delete method
> below (comments and empty lines removed):
>
> 124 sub delete : Local {
> 126     my ($self, $c, $id) = @_;
> 129     $c->model('AdBlueDB::Campaign')->search({id =>
> $id})->delete_all;
> 132     $c->flash->{status_msg} = 'Campaign deleted';
> 135     $c->response->redirect($c->uri_for('/campaigns/list'));
> 136 }
>
> My DB model Campaign.pm has the necessary has_many relationship to
> populate a relationship table in my db:
>
> 23 __PACKAGE__->has_many(campaign_client =>
> 'AdBlueDB::CampaignClient', 'campaign_id');
>
> My SQL is as follows:
>
> 84 CREATE TABLE `campaign_clients` (
> 85   `campaign_id`     int(32) unsigned              NOT NULL
>                     COMMENT '',
> 86   `client_id`       int(32) unsigned              NOT NULL
>                     COMMENT '',
> 87   FOREIGN KEY (`campaign_id`) REFERENCES campaigns(id),
> 88   FOREIGN KEY (`client_id`) REFERENCES clients(id)
> 89 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
> COMMENT='' AUTO_INCREMENT=0;
>
> I can create new campaigns and the primary "campaign" table gets all
> the right stuff along with the "campaign_client" table (it also gets
> the right relationship data and stores it properly).
>
> I'm wondering why I get the error:
>
> DBIx::Class::ResultSet::delete_all(): DBI Exception: DBD::mysql::st
> execute failed: Cannot delete or update a parent row: a foreign key
> constraint fails [for Statement "DELETE FROM campaigns WHERE ( id =
> ? )" with ParamValues: 0='47'] at... AdBlue/Controller/Campaigns.pm
> line 129
>
> when I use my delete method?
>
> From what I can tell the DBIx::Class::Relationship documentation
> indicates that the dependent child tables will be deleted
> automatically unless I specifically turn off cascade_delete, so why
> am I getting an error that I essentially translate as "I can't
> delete this row because it has child dependencies"?
>
> http://cpan.uwinnipeg.ca/htdocs/DBIx-Class/DBIx/Class/Relationship.html#has_many
>
> "If you delete an object in a class with a has_many relationship,
> all the related objects will be deleted as well. To turn this
> behaviour off, pass cascade_delete => 0 in the $attr hashref.
> However, any database-level cascade or restrict will take precedence
> over a DBIx-Class-based cascading delete."
>
>
> I know this has got to be something simple, so I guess I haven't
> wrapped my head around the DB relationships yet. The only somewhat
> significant difference I can find between my SQL and the CPAN
> example is that I use Foreign Keys and I don't see that in the
> example. Dunno if that matters though...
>
> thanks!
> -d
>

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Marcello Romani]

Dustin Suchter ha scritto:

> Well, I found a solution to my problem. I'm not sure it is the most
> elegant, but here goes:
>
> I figured that just putting the proper cascade logic into my actual
> DB schema would fix things, and it does. I changed my table
> definition to:
>
> 84 CREATE TABLE `campaign_clients` (
> 85   `campaign_id` int(32) unsigned NOT NULL COMMENT '',
> 86   `client_id` int(32) unsigned NOT NULL COMMENT '',
> 87   FOREIGN KEY (`campaign_id`) REFERENCES campaigns(id) ON DELETE
> CASCADE ON UPDATE CASCADE,
> 88   FOREIGN KEY (`client_id`) REFERENCES clients(id) ON DELETE
> CASCADE ON UPDATE CASCADE
> 89 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
> COMMENT='' AUTO_INCREMENT=0;
>
> Obviously I could also have just ALTERed the tables (in case you
> don't want to trash all your data just to fix this silly little
> problem):
>
> ALTER TABLE `campaign_clients` ADD CONSTRAINT `campaign_clients`
> FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE
> CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `campaign_clients` FOREIGN
> KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE ON
> UPDATE CASCADE;
>
> I think a better solution (TIMTOWTDI) is to for DBIx to somehow
> automatically detect the constraint and transact a delete that
> removes the child rows first and then the parent rows, in order to
> make sure everything gets deleted. I think that one is a pipe dream
> so I'll happily use my solution above for now.
>
> -d
>

[snip]

You can transact dbic calls via txn_do(), passing it a subref that does
the actual work.
i.e.:
$schema->txn_do( sub { $item->delete(); } );

Just my 2 cents.

HTH

--
Marcello Romani
Responsabile IT
Ottotecnica s.r.l.
http://www.ottotecnica.com

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Matt S Trout-2]

On Tue, Sep 11, 2007 at 10:20:50PM -0700, Dustin Suchter wrote:

> Well, I found a solution to my problem. I'm not sure it is the most
> elegant, but here goes:
>
> I figured that just putting the proper cascade logic into my actual
> DB schema would fix things, and it does. I changed my table
> definition to:
>
> I think a better solution (TIMTOWTDI) is to for DBIx to somehow
> automatically detect the constraint and transact a delete that
> removes the child rows first and then the parent rows, in order to
> make sure everything gets deleted. I think that one is a pipe dream
> so I'll happily use my solution above for now.

Couple of notes:

(1) it's DBIx::Class, DBIx:: is the namespace for -all- DBI extensions
(2) it has its own mailing list

As for automatically detecting the constraint, if you use $schema->deploy to
let DBIC generate your CREATE TABLE statements for you it puts in the
constraint cascades automatically so you'll never see a problem.

We intentionally don't override cascade/fk settings on your database to avoid
buggy code causing mass dataloss (I've seen this many times before now with
ORMs that try to be helpful the way you're requesting) - DBIC -will- try the
delete -after- deleting the main record for DBs like SQLite without FKs, but
in the case where your database has constraints DBIx::Class prefers to respect
them.

Either do the cascade clearly in your own code or make sure your DB schema's
correct - at least that way when a cascade wipes out a load of data it's
easy for the maintenance programmer to see why and how it's happened :)

--
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director    Want a managed development or deployment platform?
 Shadowcat Systems Ltd.  Contact mst (at) shadowcatsystems.co.uk for a quote
http://chainsawblues.vox.com/                    http://www.shadowcat.co.uk/ 

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/