from sqlalchemy import *
from sqlalchemy.orm import *

engine = create_engine("sqlite://", echo=True)
meta = MetaData(engine)
Session = sessionmaker(bind=engine)
session = Session()

# Tables
company_table = Table('companies', meta,
    Column('id', Integer, primary_key=True),
    Column('name', Unicode(40), nullable=False, unique=True),
    Column('email', Unicode(100)),
    Column('phone', Unicode(20)),
    Column('type', Unicode(20)),
)

supplier_table = Table('suppliers', meta,
    Column('id', None, ForeignKey('companies.id'), primary_key=True),
)

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

site_table = Table('sites', meta,
    Column('id', None, ForeignKey('companies.id'), primary_key=True),
    Column('type', Unicode(20)),
)

site_client_table = Table('site_client', meta,
    Column('id', None, ForeignKey('sites.id'), primary_key=True),
    Column('id_client', None, ForeignKey('clients.id')),
)

site_supplier_table = Table('site_supplier', meta,
    Column('id', None, ForeignKey('sites.id'), primary_key=True),
    Column('id_supplier', None, ForeignKey('suppliers.id')),
)

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

class Client(Company):
    pass

class Supplier(Company):
    pass

class Site(Company):
    pass

class SiteClient(Site):
    pass

class SiteSupplier(Site):
    pass

# Mappers
company_mapper = mapper(Company, company_table, polymorphic_on=company_table.c.type, polymorphic_identity='company',
    properties = {
        'sites':relation(Site, backref='company')
    }
)

client_mapper = mapper(Client, client_table, inherits=Company, polymorphic_identity='client')
supplier_mapper = mapper(Supplier, supplier_table, inherits=Company, polymorphic_identity='supplier')

#site_mapper = mapper(Site, site_table, inherits=Company, polymorphic_identity='site')
site_mapper = mapper(Site, site_table, polymorphic_on=site_table.c.type, polymorphic_identity='site')

site_client_mapper = mapper(SiteClient, site_client_table, inherits=Site, polymorphic_identity='client')
site_supplier_mapper = mapper(SiteSupplier, site_supplier_table, inherits=Site, polymorphic_identity='supplier')

meta.drop_all()
meta.create_all()

client = Client()
client.name = "Mr Client"

supplier = Supplier()
supplier.name = "Mr Supplier"

site1 = SiteClient()
site1.name = "Mr Site (client)"

site2 = SiteSupplier()
site2.name = "Mr Site (supplier)"

client.sites.append(site1)
supplier.sites.append(site2)

session.save(client)
session.save(supplier)
session.flush()
session.clear()

print session.query(Company).all(), "#" * 20
print session.query(Site).all(), "#" * 20