from sqlalchemy import *
from sqlalchemy.orm import *

engine = create_engine("sqlite://", echo=True)

meta = MetaData(engine)
Session = sessionmaker(bind=engine)
session = Session()

# Tables
address_table = Table('addresses', meta,
    Column('id', Integer, primary_key=True),
    Column('street', Unicode(100)),
    Column('zip', Unicode(10)),
    Column('city', Unicode(80)),
    Column('country', Unicode(2)),
)

company_table = Table('companies', meta,
    Column('id', Integer, primary_key=True),
    Column('name', Unicode(40), nullable=False, unique=True),
    Column('phone', Unicode(10)),
    Column('type', Unicode(20)),
    Column('id_address', None, ForeignKey('addresses.id'))
)

client_table = Table('clients', meta,
    Column('id', None, ForeignKey('companies.id'), primary_key=True),
)

# Classes
class Address(object):
    pass

class Company(object):
    def __repr__(self):
        return "<%s: id=%s, name=%s>" % (self.__class__.__name__, self.id, self.name)

class Client(Company):
    pass

# Mappers
address_mapper = mapper(Address, address_table)
company_mapper = mapper(Company, company_table, polymorphic_on=company_table.c.type, polymorphic_identity='company',
    properties = {
        'address':relation(Address,
                      backref=backref("company", uselist=False),
                      cascade="all, delete-orphan",
                      lazy=False,
                  ),
    },
    order_by=company_table.c.name,
)
client_mapper = mapper(Client, client_table, inherits=Company, polymorphic_identity='client')

meta.drop_all()
meta.create_all()

address1 = Address()
address1.city = "Paris"

address2 = Address()
address2.city = "Roma"

client1 = Client()
client1.name = "Client 1"
client1.address = address1

client2 = Client()
client2.name = "Client 2"
client2.address = address2

session.save(client1)
session.save(client2)
session.flush()
session.clear()

pattern = "%Paris%"
#pattern = "%Roma%"

OR = [Client.c[col].like(pattern) for col in ["name", "phone"]]
OR += [Address.c[col].like(pattern) for col in ["street", "city", "zip", "country"]]

print session.query(Client).all(), "#" * 20
#print session.query(Client).join("address").filter(or_(*OR)).all(), "#" * 20
print session.query(Client).options(lazyload("address")).join("address").filter(or_(*OR)).all(), "#" * 20