from sqlalchemy import *
db = create_engine( 'sqlite:///:memory:')
meta = BoundMetaData( db)
#meta.engine.echo = 1

table_Dept = Table( 'Dept', meta,
    Column( 'director_id', Integer, ForeignKey( 'Director.id', ), ),
    Column( 'name', type= String, ),
    Column( 'id', primary_key= True, type= Integer, ),
)
table_Director = Table( 'Director', meta,
    Column( 'salary', type= Integer, ),
    Column( 'id', Integer, ForeignKey( 'Employee.id', ), primary_key= True, ),
)
table_Employee = Table( 'Employee', meta,
    Column( 'dept_id', Integer, ForeignKey( 'Dept.id', name= 'dept_id_fk', use_alter= True, ), ),
    Column( 'name', type= String, ),
    Column( 'id', primary_key= True, type= Integer, ),
    Column( 'atype', type= String, ),
)

meta.create_all()

class Base2str( object):
    def __str__( me):
        r = me.__class__.__name__ +'/id'+ str(me.id) +'('
        for k in me.props:
            v = getattr( me, k, '<notset>')
            if isinstance( v, Base2str):
                v = '>' + v.__class__.__name__ +'/id'+ str( v.id)
            r+= ' '+k+'='+str(v)
        return r+' )'

class Employee( Base2str):
    props = ['dept', 'manager', 'name']
    data = property( lambda me: me.name)
class Director( Employee):
    props = ['salary', 'name', 'dept', 'manager', 'extras', ]
    data = property( lambda me: me.name)
class Dept( Base2str):
    props = ['director', 'manager', 'name']
    data = property( lambda me: me.name)

mapper_Dept = mapper( Dept, table_Dept,
                inherit_condition= None,
                inherits= None,
                polymorphic_identity= 'Dept',
                polymorphic_on= None,
                select_table= None,
            )
mapper_Dept.add_property( 'director', relation( Director,
                foreignkey= table_Dept.c.director_id,
                lazy= False,
                post_update= False,
                primaryjoin= table_Dept.c.director_id == table_Director.c.id,
                remote_side= table_Director.c.id,
                uselist= False,
            ) )

pu_Employee = polymorphic_union( {
                'Director': join( table_Employee, table_Director, table_Director.c.id == table_Employee.c.id, ),
                'Employee': table_Employee.select( table_Employee.c.atype == 'Employee', ),
                }, None, 'pu_Employee', ) #tableinh
mapper_Employee = mapper( Employee, table_Employee,
            inherit_condition= None,
            inherits= None,
            polymorphic_identity= 'Employee',
            polymorphic_on= pu_Employee.c.atype,
            select_table= pu_Employee,
            )
mapper_Employee.add_property( 'dept', relation( Dept,
                foreignkey= table_Employee.c.dept_id,
                lazy= False,
                post_update= True,
                primaryjoin= table_Employee.c.dept_id == table_Dept.c.id,
                remote_side= table_Dept.c.id,
                uselist= False,
            ) )


mapper_Director = mapper( Director, table_Director,
                inherit_condition= table_Director.c.id == table_Employee.c.id,
                inherits= mapper_Employee,
                polymorphic_identity= 'Director',
                polymorphic_on= None,
                select_table= None,
            )


def populate():
    a = Employee()
    a.name = 'empo'

    dept = Dept()
    dept.name = 'cubics'

    a.dept = dept

    h = Director()
    h.name = 'heady'

    s = create_session()
    s.save( a)
    s.save(dept)
    s.save(h)
    s.flush()

    s.clear()


def query_all_tables():
    print '=== whole database:'
    for t in meta.tables.values():
        print t,':',[r for r in t.select().execute()]

def test():
    session = create_session()
    #query_all_tables()
    for kl in [Employee, Director, Dept][1:]:
        print '== query', kl.__name__
        q2 = session.query(kl).select()
        print  '\n'.join( ['  '+str(x) for x in q2])

populate()
test()