Thread: [SQLObject] Guidance on ManyToMany

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Hi all,

I am trying to get my head around using a ManyToMany relation between tables 
and hope to get some guidance here since the available docs are a bit sparse 
on this (no offence - I know how difficult it is to keep docs up to 
date :-) )

Here is what I have:

class Member(SQLObject):
    name = StringCol(alternateID=True)
    roles = ManyToMany('Role')

class Role(SQLObject):
    name = StringCol(alternateID=True)
    members = ManyToMany('Member')

I am testing with a SQLite backend for what it's worth.

Q1:
Is there a way, by adding additional params to the ManyToMany(...) 
declarations for example, to restrict the same role to be added multiple 
times to the same member and vice versa?

Q2:
Is there a way to restrict/cascade deletes of a role or member if that role or 
member has a link to the other?

Q3:
When deleting a role or member using the .destroySelf() method, the link 
record in the intermediate table is not deleted. Is there a standard way to 
do this? I suppose an answer to Q2 will also address this.

I am adding a sample python file, and the output from an iPython session, 
below to illustrate the questions if that would help.

Thanks,
  Tom

------------- cut : ManyToMany.py -----------------------------------
import os
from sqlobject import *

myDir = os.path.realpath(os.path.dirname(__file__))
dbURI = 'sqlite://%s/ManyToMany.sqlite' % myDir
sqlhub.processConnection = connectionForURI(dbURI)

class Member(SQLObject):
    name = StringCol(alternateID=True)
    roles = ManyToMany('Role')

class Role(SQLObject):
    name = StringCol(alternateID=True)
    members = ManyToMany('Member')

def initDB():
    tables = (Member, Role)
    for t in tables:
        t.createTable(ifNotExists=True)

def addSamples():
    members = ('John', 'Paul', 'George', 'Ringo')
    roles = ('drums', 'lead', 'rythm', 'bass', 'vocals')

    for m in members:
        Member(name=m)
    for r in roles:
        Role(name=r)

    Member.byName('John').roles.add(Role.byName('rythm'))
    Member.byName('Paul').roles.add(Role.byName('bass'))
    Member.byName('George').roles.add(Role.byName('lead'))
    Member.byName('Ringo').roles.add(Role.byName('drums'))
------------- cut : ManyToMany.py -----------------------------------

------------- cut : iPython session ---------------------------------
In [1]: import ManyToMany as mm

In [2]: mm.initDB()

In [3]: mm.addSamples()

In [4]: vocals = mm.Role.byName('vocals')

In [5]: john = mm.Member.byName('John')

In [6]: john.roles.add(vocals)

-----> Q1: Added the 'vocals' role to john, and here it is:

In [7]: list(john.roles)
Out[7]: [<Role 3 name='rythm'>, <Role 5 name='vocals'>]

In [8]: john.roles.add(vocals)

-----> Q1: ... but we can add it a second time and now have the same role 
twice. Some way to specify a unique index on the intermediate table could 
avoid this and would raise a trapable exception.

In [9]: list(john.roles)
Out[9]: [<Role 3 name='rythm'>, <Role 5 name='vocals'>, <Role 5 
name='vocals'>]

------> Q1: Here is the corresponding rows from the intermediate table for 
the 'vocals' role

In [10]: Q = "SELECT * from member_role where role_id=5"

In [11]: mm.Role._connection.queryAll(Q)
Out[11]: [(1, 5), (1, 5)]

------> Q3: Destroy the 'vocals' role

In [12]: vocals.destroySelf()

------> Q3: It's gone from john's roles

In [13]: list(john.roles)
Out[13]: [<Role 3 name='rythm'>]

------> Q3: ... but both records are still in the intermediate table

In [14]: mm.Role._connection.queryAll(Q)
Out[14]: [(1, 5), (1, 5)]

------> Here we add a new role 'foo'. It may just be an SQLite 
related 'feature', but this new role will get the same id as the just 
deleted 'vocals' role had.

In [15]: foo = mm.Role(name='foo')

In [16]: foo
Out[16]: <Role 5 name='foo'>

------> ... and due to this, john will automatically get 2 'foo' roles added 
to his list of roles - not good :-(

In [17]: list(john.roles)
Out[17]: [<Role 3 name='rythm'>, <Role 5 name='foo'>, <Role 5 name='foo'>]

------------- cut : iPython session ---------------------------------

Thread: [SQLObject] Guidance on ManyToMany

SQLObject is a Python ORM.

sqlobject-discuss