DBIx::Class::Schema::Loader and on_delete/cascade

petdance has asked for the wisdom of the Perl Monks concerning the following question:

I'm using 0.07017 of DBIx::Class::Schema::Loader, and I'm confused by its output for two tables with a FK relationship.

Here are excerpts of two related tables, List and Notification. Notification is a subtable of List, with a proper FK in the (Postgres) database, and it's defined as ON DELETE CASCADE in the DB. These snippets were from the files generated by ::Loader.

# from TT/Schema/List.pm

__PACKAGE__->has_many(
  "notification",
  "TT::Schema::Notification",
  { "foreign.listid" => "self.listid", "foreign.ltype" => "self.ltype"
+ },
  { cascade_copy => 0, cascade_delete => 0 },
);

# from TT/Schema/Notification.pm

__PACKAGE__->belongs_to(
  "list",
  "TT::Schema::List",
  { listid => "listid", ltype => "ltype" },
  { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE"
+ },
);
[download]

The DBIx::Class docs say

By default, DBIx::Class cascades deletes across has_many, has_one and might_have relationships. You can disable this behaviour on a per-relationship basis by supplying cascade_delete => 0 in the relationship attributes. The cascaded operations are performed after the requested delete, so if your database has a constraint on the relationship, it will have deleted/updated the related records or raised an exception before DBIx::Class gets to perform the cascaded operation.

which I take to mean that cascade_delete should be false if the database is going to take care of the deleting for us. In this case, it will, because there's a foreign key from notification up to list with ON DELETE CASCADE. But then why is on_delete=>'CASCADE'?

It seems to me that the cascade_delete in the List.pm should be set to 1, to match the on_delete => 'CASCADE' in the Notification.pm. Or, that they should be 0 and undef, respectively. But I would think they should match.

Am I misunderstanding something? Or is DBIx::Class::Schema::Loader not building my schema correctly?

xoxo,
Andy

Comment on DBIx::Class::Schema::Loader and on_delete/cascade_delete Download Code

Replies are listed 'Best First'.
Re: DBIx::Class::Schema::Loader and on_delete/cascade_delete by tilly (Archbishop) on Feb 15, 2012 at 18:51 UTC
DBIx::Class::Schema::Loader is building your schema in a sensible manner. If you delete a List, the database will correctly delete all of the associated Notifications. If you delete a Notification, cpan::DBIx::Class will correctly not delete the associated List. There is no need to delete Notifications in Perl given than the database already did it, and there is likewise no need to tell the database to not delete Lists, because it won't try to do that. Note that it is more efficient to have `on_delete => 'CASCADE'` in the database rather than having to do a round trip through Perl to cascade deletes. However some users don't like it there because it makes it too easy to accidentally wipe all of your data when doing routine maintenance. I understand that making this optional is a feature request for a future version of DBIx::Class::Schema:::Loader.	[reply] [d/l]
Re^2: DBIx::Class::Schema::Loader and on_delete/cascade_delete by petdance (Parson) on Feb 15, 2012 at 19:03 UTC
Thanks, Ben. What confuses me is why they are set differently. The way I understand this is that `cascade_delete => 0` tells DBIC that it's not necessary for DBIC to delete the notifications manually, because Postgres will have done it automatically. That makes sense. But then why is there the `on_delete => "CASCADE"` at the notification level? What does DBIC do with that bit of knowledge? xoxo, Andy	[reply]

Back to Seekers of Perl Wisdom