Re: [SQLObject] RealatedJoin

[Oleg B. <ph...@ph...>]

On Tue, Aug 11, 2009 at 07:40:12AM +0200, Tom Coetser wrote:
> class Member(SQLObject):
>     name = StringCol(alternateID=True)
>     roles = SQLRelatedJoin('Role', intermediateTable='member_role',
>                                createRelatedTable=False)
> 
> class Role(SQLObject):
>     name = StringCol(alternateID=True)
>     members = SQLRelatedJoin('Member', intermediateTable='member_role',
>                                createRelatedTable=False)
> 
> class MemberRole(SQLObject):
>     member = ForeignKey('Member', notNull=True, cascade=False)
>     role = ForeignKey('Role', notNull=True, cascade=False)
> 
> When I call destroySelf() on a member though, I would like to **not** have 
> that member deleted if it still has any roles assigned to it. I tried setting 
> the cascade=False option in the intermediate table, but this does not help 
> because it looks like the base destroySelf() method automatically deletes any 
> RelatedJoins.

   First thing I can guess your are using a backend that doesn't support
CASCADE (SQLite?) With SQLite, you can CREATE a TRIGGER to prevent deletion
from the intermediate table, but you have to do it yourself.
   .destroySelf() actually doesn't rely on the backend support for CASCADE;
instead it uses it itself, but not on the RelatedJoin's intermediate table
even if the table is declared explicitly. The simplest way I see is to
override .destroySelf:

class Member(SQLObject):
    name = StringCol(alternateID=True)
    roles = SQLRelatedJoin('Role', intermediateTable='member_role',
                               createRelatedTable=False)

    def destroySelf(self):
        if list(self.roles): # or self.roles.count()
            raise RuntimeError('Cannot delete a member (%s) that has roles' %
self.id)
        super(Member, self).destroySelf()

class Role(SQLObject):
    name = StringCol(alternateID=True)
    members = SQLRelatedJoin('Member', intermediateTable='member_role',
                               createRelatedTable=False)

    def destroySelf(self):
        try:
            self.members[0]
        except IndexError:
            super(Role, self).destroySelf()
        else:
            raise RuntimeError('Cannot delete a role (%s) that has members' %
self.id)

   I showed three different ways to check for RelatedJoin. This of course only
works with .destroySelf - other means will happily delete rows.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.