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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
