[Catalyst] Re: a noob delete/cascade problem

[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
>