On Nov 3, 2013, at 5:41 AM, Peter <madp...@gmail.com> wrote:
OK I tried to scan through that post a bit to get a handle on what schema it was proposing, but it wasn’t entirely clear. The schema you’ve presented here gives me a better idea. These kinds of schemas are very common, but I would note that they are troublesome, both in terms of scalability as well as querying ability, due to being overly generic. Like if you have three types of Entity, you can’t easily query for Entity objects of just one particular type as you have all types of entities jumbled into the same set of tables. If an entity of type Q has attributes A, B and C, you can’t easily produce a query that’s equivalent to “SELECT * FROM Q where A=1 AND B=2 AND C=3”, because you don’t have “A, B, C” columns - you have to produce joins out to the Component tables in order to figure these things out, producing complex and inefficient queries. If your system has many different types of objects, some of which there’s only a handful and others of which there are millions, they all get stuffed into the same set of tables, turning those tables into bottlenecks for all object access.
so the next part here, is I didn’t see the term “tablename” in that blog post at all, but I didn’t read it fully. The practice of joining out from a single foreign key out to the primary key of table A, B, or C based on a string tablename in another column is sometimes called a “generic foreign key”. In relational algebra and SQL, there is no such thing - it’s an artificial concept created in application space (and is very popular in the Rails world) that works against the spirit and common practice of relational databases. I wrote an old post about it here: http://techspot.zzzeek.org/2007/05/29/polymorphic-associations-with-sqlalchemy/ and very recently I’ve updated the examples in SQLAlchemy itself to include modern versions of each. In SQLAlchemy 0.9 the examples include a pure “generic foreign key” where the “tablename” (more specifically a “discriminator”) is stored and used as a means of selecting the related table (this is in examples/generic_associations). The key here is not so much having a string that says “this object is of type X”, SQLAlchemy does have a system called “table inheritance” which uses this, it’s the foreign key part. When a column can point to any number of other columns elsewhere, it works against standard relational techniques and also makes proper data constraints more difficult to produce. In the case of your specific model, I’ve made the observation that a Component seems to have data fields on it, which, though I’m not sure, seem to be specific to a specific Entity? In which case we can safely make a row in Component a foreign key out to Entity directly. I’ve adapted this into a joined inheritance scheme attached which is one way of achieving the basic idea here, though the specifics might not match what you’re looking for. Using the separate ComponentsDirectory entity in order to get at “discriminator” values, rather than having a simple column on ComponentBase, makes the model much more complicated, but I’ve tried to work that out here as well just to illustrate that also. |
from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import event, inspect from sqlalchemy.orm import exc as orm_exc
Base = declarative_base()
class Entity(Base):
__tablename__ = 'entities'
id = Column(Integer, primary_key=True)
name = Column(String)
class ComponentsDirectory(Base):
__tablename__ = 'components_dir'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False, unique=True)
discriminator = Column(String, nullable=False, unique=True)
class ComponentBase(Base):
__tablename__ = 'component_base'
id = Column(Integer, primary_key=True)
entity_id = Column(Integer, ForeignKey('entities.id'))
entity = relationship('Entity', backref=backref('components'))
component_id = Column(Integer, ForeignKey('components_dir.id'), nullable=False)
component_type = relationship('ComponentsDirectory',
backref=backref('entities_assoc', cascade_backrefs=False)
)
# use a subquery so that we can SELECT the "discriminator" when we
# SELECT from component_base. Alternatively, we can just
# have a string or integer column here that we populate directly
# from each "polymorphic_identity".
component_type_discriminator = column_property(
select([
ComponentsDirectory.discriminator
]).where(component_id == ComponentsDirectory.id)
)
__mapper_args__ = {
'polymorphic_on': component_type_discriminator
}
@event.listens_for(Session, "before_attach")
def before_attach(session, instance):
"""lookup ComponentsDirectory objects as ComponentBase objects are added to
a Session and generate if needed.
There's other ways to do this as well, like in the __init__() of ComponentBase,
etc., you just need some way to look up ComponentsDirectory objects
(maybe you have them cached somewhere.)
"""
if isinstance(instance, ComponentBase):
discriminator = inspect(instance).mapper.polymorphic_identity
try:
components_directory = session.query(ComponentsDirectory).\
filter_by(
discriminator=discriminator).\
one()
except orm_exc.NoResultFound:
# note: this action can create a race condition if more than
# one thread or process attempt to create the same ComponentsDirectory
# at the same time.
components_directory = ComponentsDirectory(name=discriminator,
discriminator=discriminator)
session.add(components_directory)
session.flush()
# set the related field. cascade_backrefs=False above
# will prevent an endless loop here.
instance.component_type = components_directory
class ComponentA(ComponentBase):
__tablename__ = 'component_a'
id = Column(Integer, ForeignKey('component_base.id'), primary_key=True)
data_field1 = Column(Integer)
__mapper_args__ = {
'polymorphic_identity': "component_a"
}
class ComponentB(ComponentBase):
__tablename__ = 'component_b'
id = Column(Integer, ForeignKey('component_base.id'), primary_key=True)
data_field1 = Column(Integer)
data_field2 = Column(String)
__mapper_args__ = {
'polymorphic_identity': "component_b"
}
class ComponentC(ComponentBase):
__tablename__ = 'component_c'
id = Column(Integer, ForeignKey('component_base.id'), primary_key=True)
data_field1 = Column(Integer)
data_field2 = Column(String)
data_field3 = Column(Boolean)
__mapper_args__ = {
'polymorphic_identity': "component_c"
}
if __name__ == '__main__':
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([
Entity(name='e1', components=[
ComponentA(data_field1=1),
ComponentC(data_field1=2, data_field2='e1_cc_df2', data_field3=False),
]),
Entity(name='e2', components=[
ComponentB(data_field1=1, data_field2='e2_cb_df2'),
ComponentC(data_field1=2, data_field2='e2_cc_df2', data_field3=True),
])
])
s.commit()
for e in s.query(Entity):
print(e.components)
signature.asc
Description: Message signed with OpenPGP using GPGMail
