[Dbix-class] bug in delete_all: CascadeActions::delete removes parent before children

[Noel Burton-Krahn]

On Sun, Oct 26, 2008 at 1:19 AM, Peter Rabbitson <rabbit+list at rabbit.us> wrote:
>
> 1) You call delete_all
> 2) You traverse the entire relationship chain, and start calling delete
> on the resulting three backwards, depth first.
> 3) By the time you get to the main row that you wanted to delete_all in
> the first place, it runs a trigger which is supposed to run some
> statistics on the rows the ref_integrity was supposed to delete - epic
> fail: they are not there anymore.

I don't agree with #3:  I do my business logic (in your example, doing
statistics) in Perl, not database triggers.  That's one thing ORM is
good for.

> In any case I am not entirely sure what your problem is. If you have
> referential integrity enforced, and have proper ON DELETE xxx clauses
> set - how is it that (quoting your original mail): "The database will
> throw a referential integrity exception when the parent is deleted
> before the children". Smells like a misdesigned schema to me.

Think about it:  You can't delete a parent row (a Person) while there
are still child rows (an Address) that refer to it.  That's why the
current implementation of delete_all can't work with referential
integrity, because it will attempt to delete the parent before the
child  See the example I attached to a previous email.

My example blows up because it has referential integrity checking, but
no "ON DELETE CASCADE" triggers.  I'd prefer the ORM to handle
cascading deletes, which is what I was hoping for with delete_all.
Unfortunately, that can't work the way it's current;y implemented.
So, I wonder why keep the current implementation of delete_all at all?

>> It would also be nice to get the ambiguous select thing fixed because
>> that's bitten me a couple of times before too.  Where does the SQL
>> construction happen?
>
> Can you rephrase this second issue you are talking about, without
> referring the the first one (which is ENOPROBLEM).

If you construct a sufficiently complex select_related clause, DBIx
will construct ambiguous SQL.  Take a look at the SQL that DBIx
generates:  It does not uniquely qualify the selected columns.  Here
was the SQL that failed, which you'll see if you run my test in my
patch:

SELECT artist_undirected_maps.id1, artist_undirected_maps.id2
FROM artist_undirected_map me
LEFT JOIN artist mapped_artists ON (
mapped_artists.artistid = me.id1 ) OR ( mapped_artists.artistid =
me.id2 )
LEFT JOIN artist_undirected_map artist_undirected_maps ON (
artist_undirected_maps.id1 = mapped_artists.artistid ) OR (
artist_undirected_maps.id2 = mapped_artists.artistid )
WHERE ( ( id1 = ? ) OR ( id2 = ? ) )

The id1 and id2 in the "where" clause should have been uniquely
qualified like me.id1 and me.id2.

~Noel

> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>