from sqlalchemy import *
metadata = BoundMetaData('sqlite://', echo=True)

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))
    )
    
users = Table("users", metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(50), nullable=False)
    )

orders = Table("orders", metadata,
    Column('id', Integer, primary_key=True),
    Column('description', String(50), nullable=False)
)

class User(object):
    pass

class Order(object):
    pass
    
        
def addressable(cls, name, uselist=True):
    """addressable 'interface'."""

    def create_address(self):
        a = Address(table.name)
        if uselist:
            getattr(self, name).append(a)
        else:
            setattr(self, name, a)
        return a

    cls.create_address = create_address

    mapper = class_mapper(cls)
    table = mapper.local_table

    # 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, relation(
            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
            )
        )
    )
    
class Address(object):
    def __init__(self, type):
        self.addressable_type = type
    member = property(lambda self: getattr(self, '_backref_%s' % self.addressable_type))

mapper(Address, addresses)

mapper(User, users)
addressable(User, 'addresses', uselist=True)

mapper(Order, orders)
addressable(Order, 'address', uselist=False)

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