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.
>
>
Michael,
Thanks for the reply. I think I already got the solution, I will post
it here
after the testing part of it. Further, I could not change any table in the
real scenario, I had to go with the existing tables.
Thanks again.
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
-~----------~----~----~----~------~----~------~--~---
