[Sqlalchemy-users] Autonomous transactions

[Qvx 3000]

I would like to somehow simulate support for autonomous transactions. Here is what I need:

I have a JOB nad LOG tables. I use them to track the progress of long running jobs. Data has to be written to them and commited immediately. The work that those jobs are performing is part of a single transaction. In other words if job fails no business data should be commited but job log must be constantly commited.

I'm usually doing this via stored procedures directly inside Oracle: I have JOB utility package whose procedures are AUTONOMOUS_TRANSACTIONs and all is fine. The main transaction which is calling those functions is not affected by JOB package commits.

I was thinking how to accomplish this in SQLAlchemy with arbitrary database backends.

I am prepared to open separate connection to database to do just that but I am having problems figuring out how to do it.

I would very much like to use AutoConnectEngine.

## jobutil.py
from sqlalchemy import *
from sqlalchemy.ext.activemapper import *
from sqlalchemy.ext.proxy import AutoConnectEngine
__engine__ = AutoConnectEngine('sqlite', {...}, echo=True)

class Job(ActiveMapper):
    class mapping:
        __table__ = 'tool_jobs'
        id = column(Integer, primary_key = True)
        ...
    # following methods must be autonomous transactions
    @staticmethod
    def create_job(...) # return new job
    def start_job(...)
    def finish_job(...)
    def fail_job(...)
    def update_job(...) # enables tracking of job progress

## business.py
from sqlalchemy import *
from sqlalchemy.ext.activemapper import *
from sqlalchemy.ext.proxy import AutoConnectEngine
__engine__ = AutoConnectEngine('sqlite', {...}, echo=True)

class BusinessObject(ActiveMapper):
    class mapping:
        __table__ = 'business_table'
        id = column(Integer, primary_key = True)
        ...
# example usage
def do_job():
    job = Job.create_job('ORDER PROCESSING', ...) # commit
    job.start_job(now, expected_steps=m, ...) # commit
    begin() # still new with SA, don't know how to do it using objectstore
    try:
        for x in y:
            ... # manipulate other objects (ins/upd/del)
            job.update_job(step=i,...) # commit
        job.finish_job(now, ...) # commit
        commit() # commit business changes (maybe using objectstore or something)
    except:
        job.fail_job(now, status_message=e, ...) # commit
        rollback() # rollback business changes (maybe using objectstore or something)

Thanks,
Tvrtko