Commits

Mike Bayer committed aea7b1c

- removed the ancient "polymorphic association"
examples and replaced with an updated set of
examples that use declarative mixins,
"generic_associations". Each presents an alternative
table layout.

  • Participants
  • Parent commits ef318d8

Comments (0)

Files changed (9)

   - Fixed the psycopg2_version parsing in the 
     psycopg2 dialect.
 
+- examples
+  - removed the ancient "polymorphic association"
+    examples and replaced with an updated set of
+    examples that use declarative mixins, 
+    "generic_associations".   Each presents an alternative
+    table layout.
+
 0.7.0b4
 =======
 - general

File examples/generic_associations/__init__.py

+"""
+Illustrates various methods of associating multiple types of 
+parents with a particular child object.
+
+The examples all use the declarative extension along with 
+declarative mixins.   Each one presents the identical use
+case at the end - two clases, ``Customer`` and ``Supplier``, both
+subclassing the ``HasAddresses`` mixin, which ensures that the
+parent class is provided with an ``addresses`` collection
+which contains ``Address`` objects.
+
+The ``discriminator_on_association.py`` script in particular is a modernized
+version of the "polymorphic associations" example present in older versions of
+SQLAlchemy.
+
+
+"""

File examples/generic_associations/discriminator_on_association.py

+"""discriminator_on_related.py
+
+The HasAddresses mixin will provide a relationship
+to the fixed Address table based on a fixed association table.
+
+The association table will also contain a "discriminator"
+which determines what type of parent object associates to the
+Address row.
+
+This is a "polymorphic association".   Even though a "discriminator"
+that refers to a particular table is present, the extra association
+table is used so that traditional foreign key constraints may be used.
+
+This configuration has the advantage that a fixed set of tables
+are used, with no extra-table-per-parent needed.   The individual 
+Address record can also locate its parent with no need to scan 
+amongst many tables.
+
+"""
+from sqlalchemy.ext.declarative import declarative_base, declared_attr
+from sqlalchemy import create_engine, Integer, Column, \
+                    String, ForeignKey, Table
+from sqlalchemy.orm import Session, relationship, backref
+from sqlalchemy.ext.associationproxy import association_proxy
+
+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 AddressAssociation(Base):
+    """Associates a collection of Address objects
+    with a particular parent.
+    
+    """
+    __tablename__ = "address_association"
+
+    @classmethod
+    def creator(cls, discriminator):
+        """Provide a 'creator' function to use with 
+        the association proxy."""
+
+        return lambda addresses:AddressAssociation(
+                                addresses=addresses, 
+                                discriminator=discriminator)
+
+    discriminator = Column(String)
+    """Refers to the type of parent."""
+
+    @property
+    def parent(self):
+        """Return the parent object."""
+        return getattr(self, "%s_parent" % self.discriminator)
+
+class Address(Base):
+    """The Address class.   
+    
+    This represents all address records in a 
+    single table.
+    
+    """
+    association_id = Column(Integer, 
+                        ForeignKey("address_association.id")
+                    )
+    street = Column(String)
+    city = Column(String)
+    zip = Column(String)
+    association = relationship(
+                    "AddressAssociation", 
+                    backref="addresses")
+
+    parent = association_proxy("association", "parent")
+
+    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.
+    
+    """
+    @declared_attr
+    def address_association_id(cls):
+        return Column(Integer, 
+                                ForeignKey("address_association.id"))
+
+    @declared_attr
+    def address_association(cls):
+        discriminator = cls.__name__.lower()
+        cls.addresses= association_proxy(
+                    "address_association", "addresses",
+                    creator=AddressAssociation.creator(discriminator)
+                )
+        return relationship("AddressAssociation", 
+                    backref=backref("%s_parent" % discriminator, 
+                                        uselist=False))
+
+
+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

File examples/generic_associations/table_per_association.py

+"""table_per_association.py
+
+The HasAddresses mixin will provide a new "address_association" table for
+each parent class.   The "address" table will be shared
+for all parents.
+
+This configuration has the advantage that all Address
+rows are in one table, so that the definition of "Address"
+can be maintained in one place.   The association table 
+contains the foreign key to Address so that Address
+has no dependency on the system.
+
+
+"""
+from sqlalchemy.ext.declarative import declarative_base, declared_attr
+from sqlalchemy import create_engine, Integer, Column, \
+                    String, ForeignKey, Table
+from sqlalchemy.orm import Session, relationship
+
+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)
+
+    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 new address_association
+    table for each parent.
+    
+    """
+    @declared_attr
+    def addresses(cls):
+        address_association = Table(
+            "%s_addresses" % cls.__tablename__,
+            cls.metadata,
+            Column("address_id", ForeignKey("address.id"), 
+                                primary_key=True),
+            Column("%s_id" % cls.__tablename__, 
+                                ForeignKey("%s.id" % cls.__tablename__), 
+                                primary_key=True),
+        )
+        return relationship(Address, secondary=address_association)
+
+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
+        # no parent here

File examples/generic_associations/table_per_related.py

+"""table_per_related.py
+
+The HasAddresses mixin will provide a new "address" table for
+each parent class, as well as a distinct "Address" subclass.
+
+This configuration has the advantage that each type of parent
+maintains its "Address" rows separately, so that collection
+size for one type of parent will have no impact on other types
+of parent.   Navigation between parent and "Address" is simple,
+direct, and bidirectional.
+
+"""
+from sqlalchemy.ext.declarative import declarative_base, declared_attr
+from sqlalchemy import create_engine, Integer, Column, String, ForeignKey
+from sqlalchemy.orm import Session, relationship
+
+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(object):
+    """Define columns that will be present in each 
+    'Address' table.
+    
+    This is a declarative mixin, so additional mapped
+    attributes beyond simple columns specified here
+    should be set up using @declared_attr.
+    
+    """
+    street = Column(String)
+    city = Column(String)
+    zip = Column(String)
+
+    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 new Address class
+    for each parent.
+    
+    """
+    @declared_attr
+    def addresses(cls):
+        cls.Address = type(
+            "%sAddress" % cls.__name__,
+            (Address, Base,),
+            dict(
+                __tablename__ = "%s_address" % 
+                            cls.__tablename__,
+                parent_id = Column(Integer, 
+                    ForeignKey("%s.id" % cls.__tablename__)),
+                parent = relationship(cls)
+            )
+        )
+        return relationship(cls.Address)
+
+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=[
+            Customer.Address(
+                    street='123 anywhere street',
+                    city="New York",
+                    zip="10110"),
+            Customer.Address(
+                    street='40 main street',
+                    city="San Francisco",
+                    zip="95732")
+        ]
+    ),
+    Supplier(
+        company_name="Ace Hammers",
+        addresses=[
+            Supplier.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

File examples/poly_assoc/__init__.py

-"""
-Illustrates polymorphic associations, a method of associating a particular child object with many different types of parent object.
-
-This example is based off the original blog post at `<http://techspot.zzzeek.org/?p=13>`_ and illustrates three techniques:
-
-* ``poly_assoc.py`` - imitates the non-foreign-key schema used by Ruby on Rails' Active Record.
-* ``poly_assoc_fk.py`` - Adds a polymorphic association table so that referential integrity can be maintained.
-* ``poly_assoc_generic.py`` - further automates the approach of ``poly_assoc_fk.py`` to also generate the association table definitions automatically.
-
-"""

File examples/poly_assoc/poly_assoc.py

-"""
-"polymorphic" associations, ala ActiveRecord.
-
-In this example, we are specifically targeting this ActiveRecord
-functionality:
-
-http://wiki.rubyonrails.org/rails/pages/UnderstandingPolymorphicAssociations
-
-The term "polymorphic" here means "object X can be referenced by objects A, B,
-and C, along a common line of association".
-
-In this example we illustrate the relationship in both directions. A little
-bit of property magic is used to smooth the edges.
-
-AR creates this relationship in such a way that disallows any foreign key
-constraint from existing on the association. For a different way of doing
-this, see poly_assoc_fks.py. The interface is the same, the efficiency is more
-or less the same, but foreign key constraints may be used. That example also
-better separates the associated target object from those which associate with
-it.
-
-"""
-
-from sqlalchemy import MetaData, Table, Column, Integer, String, and_
-from sqlalchemy.orm import mapper, relationship, sessionmaker, \
-    class_mapper, backref
-
-metadata = MetaData('sqlite://')
-
-#######
-# addresses table, class, 'addressable interface'.
-
-addresses = Table("addresses", metadata,
-    Column('id', Integer, primary_key=True),
-    Column('addressable_id', Integer),
-    Column('addressable_type', String(50)),
-    Column('street', String(100)),
-    Column('city', String(50)),
-    Column('country', String(50))
-    )
-
-class Address(object):
-    def __init__(self, type):
-        self.addressable_type = type
-    @property
-    def member(self):
-        return getattr(self, '_backref_%s' % self.addressable_type)
-
-def addressable(cls, name, uselist=True):
-    """addressable 'interface'.
-
-    if you really wanted to make a "generic" version of this function, it's
-    straightforward.
-    """
-
-    # create_address function, imitaes the rails example.
-    # we could probably use property tricks as well to set
-    # the Address object's "addressabletype" attribute.
-    def create_address(self):
-        a = Address(table.name)
-        if uselist:
-            getattr(self, name).append(a)
-        else:
-            setattr(self, name, a)
-        return a
-
-    mapper = class_mapper(cls)
-    table = mapper.local_table
-    cls.create_address = create_address
-    # no constraints.  therefore define constraints in an ad-hoc fashion.
-    primaryjoin = and_(
-            list(table.primary_key)[0] == addresses.c.addressable_id,
-            addresses.c.addressable_type == table.name
-     )
-    foreign_keys = [addresses.c.addressable_id]
-    mapper.add_property(name, relationship(
-            Address,
-            primaryjoin=primaryjoin, 
-            uselist=uselist, 
-            foreign_keys=foreign_keys,
-            backref=backref('_backref_%s' % table.name, 
-                            primaryjoin=list(table.primary_key)[0] ==\
-                                        addresses.c.addressable_id, 
-                            foreign_keys=foreign_keys)
-        )
-    )
-
-mapper(Address, addresses)
-
-######
-# sample # 1, users
-
-users = Table("users", metadata,
-    Column('id', Integer, primary_key=True),
-    Column('name', String(50), nullable=False)
-    )
-
-class User(object):
-    pass
-
-mapper(User, users)
-addressable(User, 'addresses', uselist=True)
-
-######
-# sample # 2, orders
-
-orders = Table("orders", metadata,
-    Column('id', Integer, primary_key=True),
-    Column('description', String(50), nullable=False))
-
-class Order(object):
-    pass
-
-mapper(Order, orders)
-addressable(Order, 'address', uselist=False)
-
-
-######
-# use it !
-metadata.create_all()
-
-u1 = User()
-u1.name = 'bob'
-
-o1 = Order()
-o1.description = 'order 1'
-
-a1 = u1.create_address()
-a1.street = '123 anywhere street'
-a2 = u1.create_address()
-a2.street = '345 orchard ave'
-
-a3 = o1.create_address()
-a3.street = '444 park ave.'
-
-sess = sessionmaker()()
-sess.add(u1)
-sess.add(o1)
-
-sess.commit()
-
-# query objects, get their addresses
-
-bob = sess.query(User).filter_by(name='bob').one()
-assert [s.street for s in bob.addresses] == ['123 anywhere street', '345 orchard ave']
-
-order = sess.query(Order).filter_by(description='order 1').one()
-assert order.address.street == '444 park ave.'
-
-# query from Address to members
-
-for address in sess.query(Address).all():
-    print "Street", address.street, "Member", address.member
-
-
-

File examples/poly_assoc/poly_assoc_fk.py

-"""
-"polymorphic" associations, ala SQLAlchemy.
-
-See "poly_assoc.py" for an imitation of this functionality as implemented in
-ActiveRecord.
-
-Here, we build off the previous example, adding an association table that
-allows the relationship to be expressed as a many-to-one from the "model"
-object to its "association", so that each model table bears the foreign key
-constraint. This allows the same functionality via traditional normalized form
-with full constraints. It also isolates the target associated object from its
-method of being associated, allowing greater flexibility in its usage.
-
-As in the previous example, a little bit of property magic is used to smooth
-the edges.
-
-For a more genericized version of this example, see poly_assoc_generic.py.
-"""
-
-from sqlalchemy import MetaData, Table, Column, Integer, String, \
-    ForeignKey
-from sqlalchemy.orm import mapper, relationship, sessionmaker, \
-    class_mapper
-
-metadata = MetaData('sqlite://')
-
-#######
-# addresses table, class, 'addressable interface'.
-
-addresses = Table("addresses", metadata,
-    Column('id', Integer, primary_key=True),
-    Column('assoc_id', None, ForeignKey('address_associations.assoc_id')),
-    Column('street', String(100)),
-    Column('city', String(50)),
-    Column('country', String(50))
-    )
-
-## association table
-address_associations = Table("address_associations", metadata,
-    Column('assoc_id', Integer, primary_key=True),
-    Column('type', String(50), nullable=False)
-)
-
-class Address(object):
-
-    @property
-    def member(self):
-        return getattr(self.association, '_backref_%s'
-                       % self.association.type)
-
-class AddressAssoc(object):
-    def __init__(self, name):
-        self.type = name
-
-def addressable(cls, name, uselist=True):
-    """addressable 'interface'.
-
-    we create this function here to imitate the style used in poly_assoc.py.
-
-    """
-    mapper = class_mapper(cls)
-    table = mapper.local_table
-    mapper.add_property('address_rel', 
-                        relationship(AddressAssoc, 
-                                backref='_backref_%s' % table.name)
-                        )
-
-    if uselist:
-        # list based property decorator
-        def get(self):
-            if self.address_rel is None:
-                self.address_rel = AddressAssoc(table.name)
-            return self.address_rel.addresses
-        setattr(cls, name, property(get))
-    else:
-        # scalar based property decorator
-        def get(self):
-            return self.address_rel.addresses[0]
-        def set(self, value):
-            if self.address_rel is None:
-                self.address_rel = AddressAssoc(table.name)
-            self.address_rel.addresses = [value]
-        setattr(cls, name, property(get, set))
-
-mapper(Address, addresses)
-
-mapper(AddressAssoc, address_associations, properties={
-    'addresses':relationship(Address, backref='association'),
-})
-
-######
-# sample # 1, users
-
-users = Table("users", metadata,
-    Column('id', Integer, primary_key=True),
-    Column('name', String(50), nullable=False),
-    # this column ties the users table into the address association
-    Column('assoc_id', Integer, ForeignKey('address_associations.assoc_id'))
-    )
-
-class User(object):
-    pass
-
-mapper(User, users)
-addressable(User, 'addresses', uselist=True)
-
-######
-# sample # 2, orders
-
-orders = Table("orders", metadata,
-    Column('id', Integer, primary_key=True),
-    Column('description', String(50), nullable=False),
-    # this column ties the orders table into the address association
-    Column('assoc_id', Integer, ForeignKey('address_associations.assoc_id'))
-    )
-
-class Order(object):
-    pass
-
-mapper(Order, orders)
-addressable(Order, 'address', uselist=False)
-
-######
-# use it !
-metadata.create_all()
-
-u1 = User()
-u1.name = 'bob'
-
-o1 = Order()
-o1.description = 'order 1'
-
-# note we can just create an Address object freely.
-# if you want a create_address() function, just stick it on the class.
-a1 = Address()
-u1.addresses.append(a1)
-a1.street = '123 anywhere street'
-
-a2 = Address()
-u1.addresses.append(a2)
-a2.street = '345 orchard ave'
-
-o1.address = Address()
-o1.address.street = '444 park ave.'
-
-sess = sessionmaker()()
-sess.add(u1)
-sess.add(o1)
-
-sess.commit()
-
-# query objects, get their addresses
-
-bob = sess.query(User).filter_by(name='bob').one()
-assert [s.street for s in bob.addresses] == \
-            ['123 anywhere street', '345 orchard ave']
-
-order = sess.query(Order).filter_by(description='order 1').one()
-assert order.address.street == '444 park ave.'
-
-# query from Address to members
-
-for address in sess.query(Address).all():
-    print "Street", address.street, "Member", address.member

File examples/poly_assoc/poly_assoc_generic.py

-"""
-"polymorphic" associations, ala SQLAlchemy.
-
-This example generalizes the function in poly_assoc_pk.py into a function
-"association" which creates a new polymorphic association "interface".
-"""
-
-from sqlalchemy import MetaData, Table, Column, Integer, String, \
-    ForeignKey
-from sqlalchemy.orm import mapper, relationship, sessionmaker, \
-    class_mapper
-
-metadata = MetaData('sqlite://')
-
-def association(cls, table):
-    """create an association 'interface'."""
-
-    interface_name = table.name
-    attr_name = "%s_rel" % interface_name
-
-    metadata = table.metadata
-    association_table = Table("%s_associations" % interface_name, metadata,
-        Column('assoc_id', Integer, primary_key=True),
-        Column('type', String(50), nullable=False)
-    )
-
-    class GenericAssoc(object):
-        def __init__(self, name):
-            self.type = name
-
-    def interface(cls, name, uselist=True):
-
-        mapper = class_mapper(cls)
-        table = mapper.local_table
-        mapper.add_property(attr_name, 
-                            relationship(GenericAssoc, 
-                                    backref='_backref_%s' % table.name)
-                            )
-
-        if uselist:
-            # list based property decorator
-            def get(self):
-                if getattr(self, attr_name) is None:
-                    setattr(self, attr_name, GenericAssoc(table.name))
-                return getattr(self, attr_name).targets
-            setattr(cls, name, property(get))
-        else:
-            # scalar based property decorator
-            def get(self):
-                return getattr(self, attr_name).targets[0]
-            def set(self, value):
-                if getattr(self, attr_name) is None:
-                    setattr(self, attr_name, GenericAssoc(table.name))
-                getattr(self, attr_name).targets = [value]
-            setattr(cls, name, property(get, set))
-
-    @property
-    def member(self):
-        return getattr(self.association, 
-                    '_backref_%s' % self.association.type)
-
-    setattr(cls, 'member', member)
-
-    mapper(GenericAssoc, association_table, properties={
-        'targets':relationship(cls, backref='association'),
-    })
-
-    return interface
-
-
-#######
-# addresses table
-
-addresses = Table("addresses", metadata,
-    Column('id', Integer, primary_key=True),
-    Column('assoc_id', Integer, ForeignKey('addresses_associations.assoc_id')),
-    Column('street', String(100)),
-    Column('city', String(50)),
-    Column('country', String(50))
-    )
-
-class Address(object):
-    pass
-
-# create "addressable" association
-addressable = association(Address, addresses)
-
-mapper(Address, addresses)
-
-
-######
-# sample # 1, users
-
-users = Table("users", metadata,
-    Column('id', Integer, primary_key=True),
-    Column('name', String(50), nullable=False),
-    Column('assoc_id', Integer, ForeignKey('addresses_associations.assoc_id'))
-    )
-
-class User(object):
-    pass
-
-mapper(User, users)
-
-# use the association
-addressable(User, 'addresses', uselist=True)
-
-######
-# sample # 2, orders
-
-orders = Table("orders", metadata,
-    Column('id', Integer, primary_key=True),
-    Column('description', String(50), nullable=False),
-    Column('assoc_id', Integer, ForeignKey('addresses_associations.assoc_id'))
-    )
-
-class Order(object):
-    pass
-
-mapper(Order, orders)
-addressable(Order, 'address', uselist=False)
-
-######
-# use it !
-metadata.create_all()
-
-u1 = User()
-u1.name = 'bob'
-
-o1 = Order()
-o1.description = 'order 1'
-
-a1 = Address()
-u1.addresses.append(a1)
-a1.street = '123 anywhere street'
-
-a2 = Address()
-u1.addresses.append(a2)
-a2.street = '345 orchard ave'
-
-o1.address = Address()
-o1.address.street = '444 park ave.'
-
-sess = sessionmaker()()
-sess.add(u1)
-sess.add(o1)
-sess.commit()
-
-# query objects, get their addresses
-
-bob = sess.query(User).filter_by(name='bob').one()
-assert [s.street for s in bob.addresses] == \
-            ['123 anywhere street', '345 orchard ave']
-
-order = sess.query(Order).filter_by(description='order 1').one()
-assert order.address.street == '444 park ave.'
-
-# query from Address to members
-
-for address in sess.query(Address).all():
-    print "Street", address.street, "Member", address.member