Sqlite : On Delete Cascade seem to issue wrong SQL.

[Harish]

Hello,

I am using Sqlite DB, with Elixir and Sqlalchemy on top of it. For an
ondelete="cascade" on Many to one relationship, the SQL generated in
the background doesn't seem to be right.

I am aware that Sqlite just parses FK constraint statements, but does
not support it. In my model, I have a Movie and Director objects.
There is a many to one relationship from Movie to Director.

>>> d
<Director S Speilberg>
>>> d.movies
[<Movie "Titanic", (1999) by <Director S Speilberg>>, <Movie "Catch me
if you c
n!", (1998) by <Director S Speilberg>>]

>>> session.delete(d)
>>> session.flush()
2008-07-29 11:11:31,148 INFO sqlalchemy.engine.base.Engine.0x..d0
BEGIN
2008-07-29 11:11:31,180 INFO sqlalchemy.engine.base.Engine.0x..d0
UPDATE movie S
ET director_id=? WHERE movie.title = ? AND movie.year = ?
2008-07-29 11:11:31,226 INFO sqlalchemy.engine.base.Engine.0x..d0
[None, 'Catch
me if you can!', 1998]
2008-07-29 11:11:31,289 INFO sqlalchemy.engine.base.Engine.0x..d0
UPDATE movie S
ET director_id=? WHERE movie.title = ? AND movie.year = ?
2008-07-29 11:11:31,335 INFO sqlalchemy.engine.base.Engine.0x..d0
[None, 'Titani
c', 1999]
2008-07-29 11:11:31,367 INFO sqlalchemy.engine.base.Engine.0x..d0
DELETE FROM di
rector WHERE director.id = ?
2008-07-29 11:11:31,414 INFO sqlalchemy.engine.base.Engine.0x..d0 [1]
2008-07-29 11:11:31,428 INFO sqlalchemy.engine.base.Engine.0x..d0
COMMIT

After deleting the director, SQL is issued on all Child Movie objects
to UPDATE the Foreign Key (Director's ID) to None.

Shouldn't it be DELETE FROM MOVIE WHERE director_id=? [<director
id>] ?
Is there a way to get this kind of SQL to be issued?

[Jason R. Coombs]

I've found the ondelete="cascade" is only used to generate the rules
in the dialect... so if sqlite supports creating a FK constraint with
cascading deletes, including ondelete="cascade" in your metadata when
you create the database will add that rule to the database definition.

If, on the other hand, you want Elixir/SQLAlchemy to handle the
cascading deletes, you need to put this definition in the OneToMany
declaration in the parent class:

class Director(Entity):
movies = OneToMany('Movie', cascade='all, delete-orphan')

This has been my experience. I hope it helps you.

Regards,
Jason

[Harish K Vishwanath]

Hey Jason,

Thank you so much!

I was giving the cascade="all,delete,delete-orphan" on ManyToOne() side instead of OneToMany() side of the relationship.

By the time you replied, I had tortured myself to go into the code of Elixir and SqlAlchemy only to finally find out that I will need to manually alter the mapper properties on Parent Table.



In the above example, Director is the parent, Movie is the child table.

>>> for p in Director.mapper.iterate_properties:
...     p
...    
<sqlalchemy.orm.properties.ColumnProperty object at 0x02E77C50>
<sqlalchemy.orm.properties.ColumnProperty object at 0x02E7C290>
<sqlalchemy.orm.properties.PropertyLoader object at 0x02E77C30>
>>> p
<sqlalchemy.orm.properties.PropertyLoader object at 0x02E77C30>
>>> p.cascade.delete = True
>>> p.cascade.delete_orphan = True

It started working for me after that. Apparently, the suggestion you gave me does this entire painstaking thing in one shot.

Thanks again!

--
Regards,
Harish