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 ---------------------------------
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
sqlobject-discuss mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
