On Apr 6, 2008, at 11:19 PM, Michael Robellard wrote:
> > Michael, > > Thanks for your reply. I have been trying what you suggested as well > as several variants > and I keep getting stuck with something along the lines of: > > FlushError: New instance [EMAIL PROTECTED] with identity key > (<class 'models. > dbmodel.Node'>, (2L,), None) conflicts with persistent instance > [EMAIL PROTECTED] > 510 > > when I try to commit the new object. OK well the pattern you're attempting to do here is that you'd have a Node with ID #1, and then multiple VersionNodes with ID (1, 1), (1, 2). But the way joined table inheritance works is, a row from the base table corresponds to exactly one instance. In joined table inhertiance, the relation from parent to child table is necessarily one-to-one. So using joined inheritance in this manner for this particular schema is not appropriate. Since you want to have multiple VersionNode objects which all reference the same row in the "nodes" table, that is a many-to-one relation. So on the "mapping" side, you want to use composition to create this setup instead of inheritance. But through the usage of various tricks we can make it look very similar to the single VersionNode/Individual interface you're looking for. I'm not sure if the datamodel you've proposed is actually what you want (such as, what is an Individual? is it by design that a single Node can change its sex by having multiple Individual records of different sexes ?), but anyway below is a version that composes VersionNode and Node together, and uses a with_polymorphic selectable so that the nodes_table can provide the "discriminator" field to the VersionNode/Individual hierarchy (though im suspecting that you might want Individual to subclass Node, and have VersionNode be on its own...that would be much easier to set up than what I have below): from sqlalchemy import * from sqlalchemy.orm import * db = create_engine('sqlite:///:memory:', echo=True) metadata = MetaData() Session = sessionmaker(bind=db, autoflush=True, transactional=True) nodes_table = Table('nodes', metadata, Column('id', Integer, Sequence('node_id_seq'),primary_key=True), Column('type', String(50), nullable=False), ) versionnodes_table = Table('versionnodes', metadata, Column('id', Integer,ForeignKey('nodes.id'), primary_key=True), Column('vernum', Integer, primary_key=True,default=1), ) individual_table = Table('individual', metadata, Column('id', Integer(), primary_key=True), Column('vernum', Integer, primary_key=True), Column('sex', String(1)), ForeignKeyConstraint(['id','vernum'], ['versionnodes.id','versionnodes.vernum'], ondelete="CASCADE") ) node_relation_table = Table('noderelations', metadata, Column('id1', Integer,ForeignKey('nodes.id'), primary_key=True), Column('id2', Integer,ForeignKey('nodes.id'), primary_key=True) ) class Node(object): pass class VersionNode(object): def __init__(self, node=None): self.node = node or Node() self.node.type = "versionnode" def childnodes(self): return self.node.childnodes childnodes = property(childnodes) def IncrementVersion(self): session = Session() maxversion = session .execute (select ([func .max (versionnodes_table .c.vernum)],versionnodes_table.c.id==self.id)).scalar() self.vernum = maxversion + 1 def __repr__(self): return "%s(id=%r, version=%r)" % (self.__class__.__name__, self.id, self.vernum) class Individual(VersionNode): def __init__(self, node=None): self.node = node or Node() self.node.type = "individual" def create_new_version(self): v = Individual(self.node) v.sex = self.sex v.id = self.id v.IncrementVersion() return v def __repr__(self): return "%s(id=%r, version=%r, sex=%r)" % (self.__class__.__name__, self.id, self.vernum, self.sex) mapper(Node, nodes_table, properties={'childnodes':relation(Node, secondary=node_relation_table, primaryjoin=nodes_table.c.id==node_relation_table.c.id1, secondaryjoin=nodes_table.c.id==node_relation_table.c.id2, backref='parentnodes'), } ) vn_select = select([versionnodes_table, nodes_table .c.type]).select_from(versionnodes_table.join(nodes_table)).alias() ind_select = select([versionnodes_table, individual_table, nodes_table .c .type ]).select_from (versionnodes_table.join(individual_table).join(nodes_table)).alias() mapper(VersionNode, versionnodes_table, polymorphic_identity='versionnode', properties={ 'node':relation(Node, backref='versions') }, with_polymorphic=([VersionNode], vn_select), polymorphic_on=vn_select.c.type) mapper(Individual, individual_table, with_polymorphic=([Individual], ind_select), polymorphic_on=ind_select.c.type, inherits=VersionNode, polymorphic_identity='individual') def CreateTables(): metadata.create_all(bind=db) def DropTables(): metadata.drop_all(bind=db) CreateTables() ind = Individual() ind.sex = 'M' ind2 = Individual() ind2.sex = 'F' ind.node.childnodes.append(ind2.node) session = Session() session.save(ind) session.commit() newind = ind.create_new_version() session.save(newind) session.commit() session.clear() print session.query(VersionNode).all() print "------------------------" ind = session.query(Individual).filter(Individual.vernum==2).one() print [node.versions for node in ind.childnodes] --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---