[Catalyst] Re: a noob delete/cascade problem

[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