cascading deletes with relatedjoin

[qhar...@gmail.com]

The cascade functionality of SQLObject seems to not apply to related
joins. Having to manually cleanup pointers back to deleted records
seems silly. I've looked through archives for both TG and SQLObject and
seen a lot of people asking about this funcitonality, but no
explanation as to how to approximate this functionality. Thoughts?

-QH-

[Robin Haswell]

Seems obvious, but have you considered using a transactional DB, eg
MySQL with InnoDB?

-Rob

[BJörn Lindqvist]

Just alther the tables that sqlobject automagically creates. I.e. I
have a RelatedJoin between Person and Keyword. Using tg-admin sql sql
I can see that the intermediate table between Person and Keyword that
sqlobject would create looks like this:

CREATE TABLE keyword_person (
keyword_id INT NOT NULL,
person_id INT NOT NULL
);

Then I just alter it to add the foreign key contraints:

alter table keyword_person
add foreign key (keyword_id) references keyword(id) on delete cascade;

alter table keyword_person
add foreign key (person_id) references person(id) on delete cascade;

It's dirt simple.

--
mvh Björn

[qhar...@gmail.com]

>...have you considered using a transactional DB, eg MySQL with InnoDB?

Nope, I hadn't. About the only thing I know about transactional
databases is that they are generally considered a good thing. I'm
pretty new to working with DB programming, and all of my expereince has
been with ones that don't support transactions so using that feature is
somehwat outside of my ken. I'll look into it though, thanks for the
suggestion.

-QH-

[qhar...@gmail.com]

> Just alther the tables that sqlobject automagically creates...

<snip useful example stuff>

I had no idea tg-admin could let me look at that sort of stuff. Handy.
In other "framework" systems I've worked with, directly manipulating
the "automagic" elements had a tendency to break stuff so I've learned
to avoid it. So, if that's not the case with TG, you're right, it's
dirt simple. Thanks!

-QH-

[Robin Haswell]

This solution is a more fleshed out version of my solution. You need a
DB which supports ON DELETE CASCADE, which generally are transactional.
MySQL with InnoDB tables supports this, PostGres will almost certainly
support this, however I'm not so sure about SQLite.

-Rob

[Alberto Valverde]

On Apr 8, 2006, at 10:03 PM, Robin Haswell wrote:

>
> This solution is a more fleshed out version of my solution. You need a
> DB which supports ON DELETE CASCADE, which generally are
> transactional.
> MySQL with InnoDB tables supports this, PostGres will almost certainly
> support this, however I'm not so sure about SQLite.

Yep, Postgres surely does.

Alberto

[Max Ischenko]

One REALLY crude solution that I use is:

def destroySelfPatched(join):
tableName = join.kw['intermediateTable']
joinColumn = join.kw['joinColumn']
def wrapper(self):
cls = self.__class__
super(cls, self).destroySelf()
sql = "DELETE FROM %s WHERE %s = %d" % \
(tableName, joinColumn, self.id)
self._connection.query(sql)
return wrapper

And

class Foobar(SQLObject):
tags = RelatedJoin(...)
destroySelf = destroySelfPatched(tags)