a noob delete/cascade problem

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-> = '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: Catalystlists.rawmode.org
Listinfo: ht
tp://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-
archive.com/catalystlists.rawmode.org/
Dev site: http://dev.catalyst.per
l.org/

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-> = '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: Catalystlists.rawmode.org
Listinfo: ht
tp://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-
archive.com/catalystlists.rawmode.org/
Dev site: http://dev.catalyst.per
l.org/

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.vo
x.com/                    http://www.shadowcat.co.u
k/ 

_______________________________________________
List: Catalystlists.rawmode.org
Listinfo: ht
tp://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-
archive.com/catalystlists.rawmode.org/
Dev site: http://dev.catalyst.per
l.org/