From 18f7d1f02401e11df3a3e68a0eca1894c3cb2302 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 28 May 2007 23:41:44 +0000 Subject: [PATCH] added "polymorphic assocaition" example, illustrates how to emulate Rails' polymorphic associaiton functionality --- examples/poly_assoc/poly_assoc.py | 144 +++++++++++++++++++++++++ examples/poly_assoc/poly_assoc_fk.py | 156 +++++++++++++++++++++++++++ 2 files changed, 300 insertions(+) create mode 100644 examples/poly_assoc/poly_assoc.py create mode 100644 examples/poly_assoc/poly_assoc_fk.py diff --git a/examples/poly_assoc/poly_assoc.py b/examples/poly_assoc/poly_assoc.py new file mode 100644 index 0000000000..53ef4d985d --- /dev/null +++ b/examples/poly_assoc/poly_assoc.py @@ -0,0 +1,144 @@ +""" +"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. The comments suggest +implementing triggers if you really want constraints. + +For a modification of this method which is normalized, see the other script in this directory, +poly_assoc_fks.py. The interface is the same, the efficiency is more or less the same, +but full foreign key constraints are used. That example also better separates +the associated target object from those which associate with it. + +""" + +from sqlalchemy import * + +metadata = BoundMetaData('sqlite://', echo=False) + +####### +# 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 + member = property(lambda self: 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 = (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, relation( + Address, + primaryjoin=primaryjoin, uselist=uselist, foreign_keys=foreign_keys, + backref=backref('_backref_%s' % table.name, primaryjoin=primaryjoin, 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 = create_session() +sess.save(u1) +sess.save(o1) +sess.flush() + +sess.clear() + +# query objects, get their addresses + +bob = sess.query(User).get_by(name='bob') +assert [s.street for s in bob.addresses] == ['123 anywhere street', '345 orchard ave'] + +order = sess.query(Order).get_by(description='order 1') +assert order.address.street == '444 park ave.' + +# query from Address to members + +for address in sess.query(Address).list(): + print "Street", address.street, "Member", address.member + + + diff --git a/examples/poly_assoc/poly_assoc_fk.py b/examples/poly_assoc/poly_assoc_fk.py new file mode 100644 index 0000000000..2806a4088a --- /dev/null +++ b/examples/poly_assoc/poly_assoc_fk.py @@ -0,0 +1,156 @@ +""" +"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. + +""" + +from sqlalchemy import * +from sqlalchemy.ext.associationproxy import association_proxy + +metadata = BoundMetaData('sqlite://', echo=False) + +####### +# 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): + member = property(lambda self: 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. if + you really wanted to make a "generic" version of this function, it's straightforward. + + """ + mapper = class_mapper(cls) + table = mapper.local_table + mapper.add_property('address_rel', relation(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':relation(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('address_id', None, 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('address_id', None, 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 = create_session() +sess.save(u1) +sess.save(o1) +sess.flush() + +sess.clear() + +# query objects, get their addresses + +bob = sess.query(User).get_by(name='bob') +assert [s.street for s in bob.addresses] == ['123 anywhere street', '345 orchard ave'] + +order = sess.query(Order).get_by(description='order 1') +assert order.address.street == '444 park ave.' + +# query from Address to members + +for address in sess.query(Address).list(): + print "Street", address.street, "Member", address.member -- 2.47.2