best practice for ORM CASCADE ondelete

[kris]

After checking the docs, I am not exactly sure how to configure (if possible) a type such that SA will handle 

the deletes if underlying database does not support CASCADE.  

1.  Is it now expected that all DBs support ondelete="CASCADE" and you are expected to use it?

2.  Should ORM relations with cascade="all, delete" also passive_deletes=True?

Thanks,

Kris

For example: 

I have some hierarchical types are self-referential and I would like to configure ondelete and onupdate.

the following Node class has a root FK so we can delete all elements in a single bang by deleting the root node.

node = Table('nodes', metadata=Metadata(), 

                      Column('node_id', Integer, primary_key=True),

                      Column('comment', Text),

                      Column('parent_id', Integer, ForeignKey('node.node_id'), ondelete='cascade'),

                      Column('root_id', Integer, ForeignKey('node.node_id'),   ondelete='cascade' )

mapper( Node, node,

                       properties = {

 

    'children' : relation(Node, lazy=True, cascade="all, delete-orphan",  passive_deletes=True,

                          backref = backref('parent', enable_typechecks=False, remote_side = [ node.c.node_id]),

                          primaryjoin = (node.c.node_id == node.c.parent_id)),

    'allnodes': relation(Taggable, lazy=True, 

                         cascade = "all, delete-orphan", passive_delete=True,

                         post_update=True,

                         primaryjoin = (node.c.node_id == taggable.c.root_id),

                         backref = backref('document', post_update=True, 

                                           , remote_side=[node.c.node_id]),

                         ),

[Michael Bayer]

On Jul 30, 2012, at 6:28 PM, kris wrote:

>
> After checking the docs, I am not exactly sure how to configure (if possible) a type such that SA will handle
> the deletes if underlying database does not support CASCADE.
>
> 1. Is it now expected that all DBs support ondelete="CASCADE" and you are expected to use it?

There's no expectation that the DB supports ondelete="CASCADE", while my life might have been easier for me to just do it that way, when I wrote SQLAlchemy I was deeply aware that lots of DBs don't do it (SQLite without FKs, MySQL without InnoDB) or only do it partially (oracle), so SQLAlchemy ORM can cascade deletes also, but in a much less efficient way. If cascade="all, delete" it will load in unloaded collections when the parent is deleted, then do a delete() on each child item. When that occurs, if that child item also has cascade="all, delete" on its own relationships, the process of "cascading the delete" continues. But you can see we're needing to load all objects in before we can delete them, and individually deleting each row.

> 2. Should ORM relations with cascade="all, delete" also passive_deletes=True?

only *if* you have a working "ON DELETE CASCADE" set up on your foreign keys. Otherwise when you delete a parent row, if the dependent rows aren't loaded into memory, they won't be deleted. Your database will either A. complain, if it enforces referential integrity or B. silently leave those rows in place.

[kris]

So 

3.  Is there a fool-proof way to configure your table and mapper such that you will get the most efficient delete strategy?

     ON DELETE CASCADE when available or SA when not? 

4.  Can I always add ondelete='cascade' and it will be ignored (or filtered) if the underying DB doesn't support it.

5.  Add some sort of configuration setting 

        passive_deletes=( not db_sucks)?

Thx,

Kris   

 

[Michael Bayer]

On Jul 30, 2012, at 7:23 PM, kris wrote:

>
> So
> 3. Is there a fool-proof way to configure your table and mapper such that you will get the most efficient delete strategy?
> ON DELETE CASCADE when available or SA when not?

unfortunately this is not something built in to SQLAlchemy at this time. So you'd need to make it some kind of configuration option in your application, that would need to be established before you create your mappings - then you'd pass that flag along to all your "passive_deletes" flags.

What we're not getting here is the ability to automatically determine the "passive_deletes" flag *after* the engine has been connected. It's probably not too hard to get the RelationshipProperty to respond to a change in the "passive_deletes" flag, it's just not something that's been tested (meaning, maybe you could say, MyObject.somerelationship.passive_deletes = True after the fact).

In any case I don't know that SQLAlchemy would want to determine this flag automatically in any case as we're basically talking about a per-table quality with MySQL (MyISAM vs. InnoDB) and a pragma setting with SQLite, but at least if passive_deletes accepted some kind of callable that is passed the dialect or something would make "automatic" behavior here possible. But it gets even more weird if you were using a Session connected to multiple databases, some supporting on delete cascade and others not.

>
> 4. Can I always add ondelete='cascade' and it will be ignored (or filtered) if the underying DB doesn't support it.

mmm in the case of SQLite and MySQL this should work fine. I think every other DB we support has support for ON DELETE CASCADE.