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.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
