<p>我经常使用的最简单的模式是,对于每个关系,实际上都有单独的注释表。乍一看,这似乎很可怕,但与使用任何其他方法相比,它不会产生任何额外的代码—表是自动创建的,并且使用<code>Post.Comment</code>、<code>Project.Comment</code>等模式引用模型。注释的定义在一个地方维护。从引用的角度来看,这种方法是最简单、最有效的,也是对DBA最友好的方法,因为不同种类的注释保存在各自的表中,这些表可以单独调整大小。在</p>
<p>要使用的另一个模式是一个注释表,但有不同的关联表。此模式提供了一个用例,您可能希望一个注释一次链接到多个对象(比如同时链接一篇文章和一个项目)。这种模式仍然相当有效。在</p>
<p>第三,多态关联表。此模式使用固定数量的表来表示集合和相关类,而不牺牲引用完整性。这种模式试图最接近Django风格的“generic foreign key”,同时仍然保持引用的完整性,尽管它不像前两种方法那么简单。在</p>
<p>模仿ROR/Django使用的模式也是可能的,其中没有使用真正的外键,并且使用应用程序逻辑匹配行。在</p>
<p>前三种模式在SQLAlchemy发行版的examples/generic_associations/下以现代形式进行了说明。在</p>
<p>ROR/Django模式,因为它经常被问到,所以我还将添加到SQLAlchemy示例中,尽管我不太喜欢它。我使用的方法与Django的方法不完全相同,因为他们似乎使用“contenttypes”表来跟踪类型,这对我来说似乎有点多余,但是整数列(integer column)的总体思想是指向基于discriminator列的任意数量的表。这里是:</p>
<pre><code>from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy import create_engine, Integer, Column, \
String, and_
from sqlalchemy.orm import Session, relationship, foreign, remote, backref
from sqlalchemy import event
class Base(object):
"""Base class which provides automated table name
and surrogate primary key column.
"""
@declared_attr
def __tablename__(cls):
return cls.__name__.lower()
id = Column(Integer, primary_key=True)
Base = declarative_base(cls=Base)
class Address(Base):
"""The Address class.
This represents all address records in a
single table.
"""
street = Column(String)
city = Column(String)
zip = Column(String)
discriminator = Column(String)
"""Refers to the type of parent."""
parent_id = Column(Integer)
"""Refers to the primary key of the parent.
This could refer to any table.
"""
@property
def parent(self):
"""Provides in-Python access to the "parent" by choosing
the appropriate relationship.
"""
return getattr(self, "parent_%s" % self.discriminator)
def __repr__(self):
return "%s(street=%r, city=%r, zip=%r)" % \
(self.__class__.__name__, self.street,
self.city, self.zip)
class HasAddresses(object):
"""HasAddresses mixin, creates a relationship to
the address_association table for each parent.
"""
@event.listens_for(HasAddresses, "mapper_configured", propagate=True)
def setup_listener(mapper, class_):
name = class_.__name__
discriminator = name.lower()
class_.addresses = relationship(Address,
primaryjoin=and_(
class_.id == foreign(remote(Address.parent_id)),
Address.discriminator == discriminator
),
backref=backref(
"parent_%s" % discriminator,
primaryjoin=remote(class_.id) == foreign(Address.parent_id)
)
)
@event.listens_for(class_.addresses, "append")
def append_address(target, value, initiator):
value.discriminator = discriminator
class Customer(HasAddresses, Base):
name = Column(String)
class Supplier(HasAddresses, Base):
company_name = Column(String)
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
session = Session(engine)
session.add_all([
Customer(
name='customer 1',
addresses=[
Address(
street='123 anywhere street',
city="New York",
zip="10110"),
Address(
street='40 main street',
city="San Francisco",
zip="95732")
]
),
Supplier(
company_name="Ace Hammers",
addresses=[
Address(
street='2569 west elm',
city="Detroit",
zip="56785")
]
),
])
session.commit()
for customer in session.query(Customer):
for address in customer.addresses:
print(address)
print(address.parent)
</code></pre>