From c5a63b7dbf31a99de0fe6f270a3ac4ce7243396a Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 29 May 2007 23:56:10 +0000 Subject: [PATCH] some updates --- examples/poly_assoc/poly_assoc.py | 10 +- examples/poly_assoc/poly_assoc_fk.py | 10 +- examples/poly_assoc/poly_assoc_generic.py | 152 ++++++++++++++++++++++ 3 files changed, 161 insertions(+), 11 deletions(-) create mode 100644 examples/poly_assoc/poly_assoc_generic.py diff --git a/examples/poly_assoc/poly_assoc.py b/examples/poly_assoc/poly_assoc.py index 1a2eb2a75f..faeabd9561 100644 --- a/examples/poly_assoc/poly_assoc.py +++ b/examples/poly_assoc/poly_assoc.py @@ -12,19 +12,17 @@ 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, +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 full foreign key constraints are used. That example also better separates +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 = BoundMetaData('sqlite://', echo=True) +metadata = BoundMetaData('sqlite://', echo=False) ####### # addresses table, class, 'addressable interface'. diff --git a/examples/poly_assoc/poly_assoc_fk.py b/examples/poly_assoc/poly_assoc_fk.py index 2806a4088a..53cb5c81f8 100644 --- a/examples/poly_assoc/poly_assoc_fk.py +++ b/examples/poly_assoc/poly_assoc_fk.py @@ -15,10 +15,11 @@ 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 * -from sqlalchemy.ext.associationproxy import association_proxy metadata = BoundMetaData('sqlite://', echo=False) @@ -49,8 +50,7 @@ class AddressAssoc(object): 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. + we create this function here to imitate the style used in poly_assoc.py. """ mapper = class_mapper(cls) @@ -87,7 +87,7 @@ 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')) + Column('assoc_id', None, ForeignKey('address_associations.assoc_id')) ) class User(object): @@ -103,7 +103,7 @@ 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')) + Column('assoc_id', None, ForeignKey('address_associations.assoc_id')) ) class Order(object): diff --git a/examples/poly_assoc/poly_assoc_generic.py b/examples/poly_assoc/poly_assoc_generic.py new file mode 100644 index 0000000000..b0418cd5c0 --- /dev/null +++ b/examples/poly_assoc/poly_assoc_generic.py @@ -0,0 +1,152 @@ +""" +"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 = BoundMetaData('sqlite://', echo=False) + +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, relation(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)) + + setattr(cls, 'member', property(lambda self: getattr(self.association, '_backref_%s' % self.association.type))) + + mapper(GenericAssoc, association_table, properties={ + 'targets':relation(cls, backref='association'), + }) + + return interface + + +####### +# addresses table + +addresses = Table("addresses", metadata, + Column('id', Integer, primary_key=True), + Column('assoc_id', None, 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', None, 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', None, 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 = 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