On Monday 27 August 2007 19:57:21 Michael Bayer wrote: > On Aug 24, 2007, at 8:39 AM, praddy wrote: > > ########################################################## > > from sqlalchemy import * > > meta = BoundMetaData('sqlite://', echo=False) > > > > # Parents table. > > parents = Table('parents', meta, > > Column("id", Integer, primary_key=True), > > Column("data", String(50), nullable=False) > > ) > > > > # Children_1 Table. > > children_1 = Table('children_1', meta, > > Column("id", Integer, primary_key=True), > > Column("data", String(50), nullable=False) > > ) > > > > # Children_2 Table. > > children_2 = Table('children_2', meta, > > Column("id", Integer, primary_key=True), > > Column("data", String(50)) > > ) > > > > # Association Table. > > # This is a generic table which can relate anything to parent. > > assoc = Table('assoc', meta, > > # parents.c.id > > Column("parent_id", Integer, ForeignKey(parents.c.id)), > > # associate's id either children_1.c.id or children_2.c.id or any > > other child. > > Column("assoc_id", Integer), > > # Which can be either 'child_1' or 'child_2' for now (can be used for > > extending children > > # type, decides which table to look in. > > Column("assoc_type", String(20)) > > ) > > ####################################################### > > > > I am a novice with respect to sqlalchemy & may be RDBMS as well. > > How would you like to work on this scenario to achieve backwards > > cascading (may not be the right word) which means when one deletes > > one specific child from children_1 table (for example), there should > > not be any > > association entry, which associates that child to the parent, in the > > association table as well? > > To allow proper foreign key support, you probably want to place the > foreign key to the association table on each of children_1 and > children_2, and remove the "assoc_id" column from the "assoc" table. > this is the key to the blog article about polymorphic associations. > its probably easiest for you to work with the example mentioned in > the blog and modify. (http://techspot.zzzeek.org/files/ > poly_assoc_2.py ). at least, if you study the example it will lead > to more insight on how to approach this. If you really want to keep > your foreign keys as they are, the ActiveRecord example (http:// > techspot.zzzeek.org/files/poly_assoc_1.py ) illustrates that pattern > with SA. > > next, you're going to want to specify "cascade='all, delete-orphan'" > on both relations which reference the association table: > > mapper(Parent, parents, properties={ > 'associations':relation(Association, cascade="all, delete-orphan") > }) > > class Association(object): > def _child(self): > if self.assoc_type='child1': > return self.child_1 > else: > return self.child_2 > child=property(_child) > > mapper(Association, assoc, properties={ > 'child_1':relation(Child1, backref=backref("assoc_1", > cascade="all, delete-orphan")), > 'child_2':relation(Child2, backref=backref("assoc_2", > cascade="all, delete-orphan")), > }) > > mapper(Child1, children_1) > mapper(Child2, children_2) > > > im out of town this week so I dont have the resources to work out the > full solution but this is the general idea. > > Mike,
I have got the solution this way. - Pradeep Jindal --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
poly_assoc_sep_table.py
Description: application/python