import sys
sys.path.insert(0, './lib/')

try:
#    import sqlalchemy.mods.threadlocal
    pass
except:
    pass
from sqlalchemy import *
import time

metadata = create_engine('sqlite://', echo=True)

t1s = Table( 't1s', metadata, 
    Column( 'id', Integer, primary_key=True),
    Column('data', String(100))
    ) 

t2s = Table( 't2s', metadata, 
    Column( 'id', Integer, primary_key=True),
    Column( 't1id', Integer, ForeignKey("t1s.id"), nullable=True ))

t3s = Table( 't3s', metadata, 
    Column( 'id', Integer, primary_key=True),
    Column( 't2id', Integer, ForeignKey("t2s.id"), nullable=True ))

t4s = Table( 't4s', metadata, 
    Column( 'id', Integer, primary_key=True), 
    Column( 't3id', Integer, ForeignKey("t3s.id"), nullable=True ))
    
[t.create() for t in [t1s,t2s,t3s,t4s]]

class T1( object ): pass
class T2( object ): pass
class T3( object ): pass
class T4( object ): pass 

mapper( T1, t1s )
mapper( T2, t2s )       
mapper( T3, t3s )       
mapper( T4, t4s )       

cascade = "all, delete-orphan"
use_backref = True

if use_backref:
    class_mapper(T1).add_property( 't2s', relation(T2, backref=backref("t1", cascade=cascade), cascade=cascade))
    class_mapper(T2).add_property ( 't3s', relation(T3, backref=backref("t2",cascade=cascade), cascade=cascade) )
    class_mapper(T3).add_property( 't4s', relation(T4, backref=backref("t3", cascade=cascade), cascade=cascade) )
else:
    T1.mapper.add_property( 't2s', relation(T2, cascade=cascade))
    T2.mapper.add_property ( 't3s', relation(T3, cascade=cascade) )
    T3.mapper.add_property( 't4s', relation(T4, cascade=cascade) )

now = time.time()
print "start"
sess = create_session()        
o1 = T1()
sess.save(o1) 
for i2 in range(10):
    o2 = T2()
    o1.t2s.append( o2 )
    
    for i3 in range( 10 ):
        o3 = T3()
        o2.t3s.append( o3 )
        
        for i4 in range( 10 ):
            o3.t4s.append ( T4() )
            print i2, i3, i4

print len([s for s in sess])            
print "flushing"
sess.flush()
total = time.time() - now
print "done,total time", total