from twisted.internet import reactor, threads

from sqlalchemy import *

meta = MetaData()

def init(uri, strategy):
    engine = create_engine('sqlite://%s' % uri, strategy=strategy)
    meta.bind = engine
    reactor.suggestThreadPoolSize(1)

def _transact(meta):
    """
    return an high level function bind to a particular sqlalchemy engine

    this function is suitable to use as a decorator

    a decorated function returns a Deferred while its body
    is running in a separate thread
    """
    def _transaction(f):
        # _transaction *is* the decorator
        def _wrap(*args, **kwargs):
            def _1():
                conn = meta.engine.connect()
                session = create_session(bind=conn)
                trans = conn.begin()
                try:
                    r = f(session, *args, **kwargs)
                    session.flush()
                except:
                    trans.rollback()
                    conn.close()
                    raise
                else:
                    trans.commit()
                    conn.close()
                    return r
            return threads.deferToThread(_1)
        _wrap.__name__ = f.__name__
        return _wrap
    return _transaction

users = Table('users', meta,
    Column('email', String(50), unique=True, nullable=False),
    Column('username', String(50), primary_key=True, nullable=False),
    Column('password', String(32), nullable=False),
    Column('role', String(30), nullable=False, default=u'user'),
    Column('enabled', Boolean, nullable=False, default=False)
)

class User(object):
    def __init__(self, email, username, password):
        self.email = email
        self.username = username
        self.password = password

    def __str__(self):
        return "%s(%s)" % (self.__class__.__name__, self.username)

mapper(User, users)

transact = _transact(meta)

@transact
def doSomething(session):
    return session.query(User).select()

def presult(results):
    for user in results:
        print user

@transact
def setup(session):
    meta.create_all()
    user = User('dialtone@gmail.com', 'dialtone', 'test')
    user2 = User('aaron@adroll.com', 'aaron', 'test2')
    session.save(user)
    session.save(user2)

init('/:memory:', 'threadlocal')
setup().addCallback(lambda _: doSomething()
      ).addCallback(presult
      ).addCallback(lambda _: reactor.stop())
reactor.run()