Hello list,
I sent a previous message a week ago asking for help on
many-to-many relationships from a table to itself:
> Hello list,
>
> I'm having trouble trying to define a many-to-many
> relationship from a table
> to itself (of course using an intermediate table).
[... more deleted...]
I've prepared a test case to demonstrate my problem.
Here is the code:
----------------------------------------------------------
from sqlalchemy import *
import testbase
import string
import sqlalchemy.attributes as attr
class Place(object):
'''represents a place'''
def __init__(self, name=None):
self.name = name
class PlaceThingy(object):
'''represents a thingy attached to a Place'''
def __init__(self, name=None):
self.name = name
class Transition(object):
'''represents a transition'''
def __init__(self, name=None):
self.name = name
self.inputs = []
self.outputs = []
def __repr__(self):
return object.__repr__(self)+ " " + repr(self.inputs) + " " +
repr(self.outputs)
class M2MTest(testbase.AssertMixin):
def setUpAll(self):
db = testbase.db
global place
place = Table('place', db,
Column('place_id', Integer, Sequence('pid_seq', optional=True),
primary_key=True),
Column('name', String(30), nullable=False),
)
global transition
transition = Table('transition', db,
Column('transition_id', Integer, Sequence('tid_seq',
optional=True), primary_key=True),
Column('name', String(30), nullable=False),
)
global place_thingy
place_thingy = Table('place_thingy', db,
Column('thingy_id', Integer, Sequence('thid_seq', optional=True),
primary_key=True),
Column('place_id', Integer, ForeignKey('place.place_id'),
nullable=False),
Column('name', String(30), nullable=False)
)
# association table #1
global place_input
place_input = Table('place_input', db,
Column('place_id', Integer, ForeignKey('place.place_id')),
Column('transition_id', Integer,
ForeignKey('transition.transition_id')),
)
# association table #2
global place_output
place_output = Table('place_output', db,
Column('place_id', Integer, ForeignKey('place.place_id')),
Column('transition_id', Integer,
ForeignKey('transition.transition_id')),
)
# association table #3
global place_place
place_place = Table('place_place', db,
Column('pl1_id', Integer, ForeignKey('place.place_id')),
Column('pl2_id', Integer, ForeignKey('place.place_id')),
)
place.create()
transition.create()
place_input.create()
place_output.create()
place_thingy.create()
place_place.create()
def tearDownAll(self):
place_input.drop()
place_output.drop()
place_thingy.drop()
place_place.drop()
place.drop()
transition.drop()
def setUp(self):
objectstore.clear()
clear_mappers()
def tearDown(self):
place_input.delete().execute()
place_output.delete().execute()
place_place.delete().execute()
transition.delete().execute()
place.delete().execute()
def testmanytomany_ok(self):
"""ok test taken from the suite."""
Place.mapper = mapper(Place, place)
Transition.mapper = mapper(Transition, transition)
Place.mapper.add_property('inputs', relation(
Transition.mapper, place_output, lazy=False,
selectalias='input_alias',
))
def testselftable_1(self):
"""tests a many-to-many relationship from a table to itself."""
Place.mapper = mapper(Place, place)
Transition.mapper = mapper(Transition, transition)
Place.mapper.add_property('selfplaces', relation(
Place.mapper, place_place, lazy=False,
selectalias='selfplaces_alias',
))
def testselftable_2(self):
"""tests a many-to-many relationship from a table to itself."""
Place.mapper = mapper(Place, place)
Transition.mapper = mapper(Transition, transition)
Place.mapper.add_property('selfplaces', relation(
Place.mapper, place_place, lazy=False,
selectalias='selfplaces_alias',
foreignkey=place_place.c.pl1_id
))
def testselftable_3(self):
"""tests a many-to-many relationship from a table to itself."""
Place.mapper = mapper(Place, place)
Transition.mapper = mapper(Transition, transition)
Place.mapper.add_property('selfplaces', relation(
Place.mapper, place_place, lazy=False,
selectalias='selfplaces_alias',
primaryjoin=place.c.place_id==place_place.c.pl1_id,
foreignkey=place_place.c.pl1_id
))
def testselftable_4(self):
"""tests a many-to-many relationship from a table to itself."""
Place.mapper = mapper(Place, place)
Transition.mapper = mapper(Transition, transition)
Place.mapper.add_property('selfplaces', relation(
Place.mapper, place_place, lazy=False,
selectalias='selfplaces_alias',
primaryjoin=place.c.place_id==place_place.c.pl1_id,
secondaryjoin=place_place.c.pl2_id==place.c.place_id,
foreignkey=place_place.c.pl1_id
))
if __name__ == "__main__":
testbase.main()
----------------------------------------------------------
I don't know SQLAlchemy good enough to assert that it's a bug; perhaps
I'm doing the relations the wrong way. If anyone can throw some light
on the subject I'll be very pleased.
And a great thanks to Michael Bayer for this fine piece of software :)
Raul Garcia.
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting language
that extends applications into web and mobile media. Attend the live webcast
and join the prime developer group breaking into this new coding territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
