[Zope3-Users] Re: Other SQLAlchemy thoughts, Zope transaction manager, etc...

[Florent Guillaume]

Jeff Shell wrote:
> On 4/6/06, Martin Aspeli <optilude-hi6Y0CQ0nG0 at public.gmane.org> wrote:
>> I, for one, am really excited by this [sqlalchemy / zalchemy]. I really hope
>> that Zope 3 will have a somewhat consistent story for RDBMS access that (a) uses
>> technology not invented in Zope alone (b) is scalable and robust and (c) is
>> documented with patterns that people can easily follow. It's been too long. :)
> 
> I agree. I'm independently testing some methods of SQLAlchemy / Zope
> integration. There's a lot about SQLAlchemy to like.

Indeed, it's quite well modularized, and easy to plug into.

>>From what I can gather, SQLAlchemy does a lot of things similarly to
> the ZODB when it comes to data mapping: its objectstore/unit of work
> system tracks clean and dirty objects (and deletes, etc), I guess
> similar to the _v_changed property in the ZODB. When used with the
> objectstore session's begin/commit/rollback functions, only the items
> changed after the begin are committed. It uses thread locals heavily
> to store some of the core work-tracking items, so a call to
> 'objectstore.get_session()' is often enough. objectstore.begin() is
> just a wrapper like:
> 
> def begin():
>     get_session().begin()
> 
> with the same for commit, is_dirty, and so on.
> 
> Confusingly, there are a couple of notions of 'sessions', and a couple
> of notions of 'commit'. On the 'engine' objects themselves (them what
> work with the dbapi connections and database vendor specifics) have
> both 'commit()' and 'do_commit()' (same for begin and rollback).
> 'engine.commit()' calls 'engine.session.commit()' which decrements a
> counter that has been incremented each time a begin() call was made.
> When the counter reaches 0 (free of nesting), it calls back to
> engine.do_commit(), which is what calls commmit() on the dbapi
> connection and sends the command to MySQL.
> 
> sqlalchemy's mapping layer is built on top of this core layer, and is
> the get_session() stuff I mentioned above. It also supports nesting
> (with an option to commit each time commit is called, like a save
> point, or just when the nesting is back to 0). It has a begin() option
> which sets a starting point. Any changes made prior to begin() are
> lost. begin() returns a transaction object with commit / rollback.
> 
> What I've been toying with is how to work with the SQLAlchemy's
> concept of a transaction manager (which does not do two-phase commit)

Which is unfortunate and actually a showstopper for me. However it's 
fixable. SQLAlchemy's author said he didn't do two-phase commit for now 
because the python DBI layer didn't support it. It'll need to be extended or 
worker around.

> along with Zope's. Pulling ideas from different sources, I've played
> with the idea of:
> 
> * Subclassing from an engine (like mysqlengine) and replacing its do_*
> methods which are what actually call commit(), rollback() on the
> RDBMS, and using a DataManager to call those actions directly.
> * Still letting the objectstore do its commits which are where the
> 'sql is sent over the wire' in the main commit() phase of the Data
> Manager. [first phase - send data; second phase - send 'commit'
> command]
> * Using just one proxy engine (using a subclass of sqlalchemy's proxy)
> and a simple utility / zcml directive that connects the engine only
> once. The subclassed version allows for a factory to be used instead
> of a DBAPI uri (mysql://...), so that I could use my custom MySQL
> Engine. SQLAlchemy's threading and pool management should work fine
> with Zope 3.
> 
> And... well, it _kindof_ works. But I'm buggered by how to tap into
> Zope's transaction.begin(), which actually looks like it's going away
> soon (it's marked as deprecated). I'm guessing I can use the
> beforeTraverseEvent and manually join, like 'zalchemy' does. But I
> don't like that option. I'd rather use transaction.begin() itself (I
> looked at Synchronizers, but couldn't really figure out how to make
> those work since a synchronizer is bound to just one thread). There
> are no other hooks that I can see where I can register my
> engine/mapping/whatever in SQLAlchemy to automatically join a
> transaction at the beginning (mostly so that it's just one concept,
> whether I'm using Zope's transaction stuff in a web request or in a
> command line tool).
> 
> Alternately, I was looking at the
> zope.app.publication.zopepublication.ZopePublication. That's where the
> traversal events are fired off, and also where the main transaction
> begin/commit/abort work happens. But no event is fired off in
> beforeTraversal (which is called only once, at the beginning of
> publication, and shouldn't be confused with BeforeTraverseEvent which
> is called for names traversed along the way), and subclassing and
> registering new publications and factories for a new Publication just
> to add (perhaps) one line of code felt grossly disproportionate when
> all I'm interested in is "a new transaction was just started, let this
> third party transaction system start up too".

IMO the proper way to do any of this stuff would be to write a Connection 
and DB implementation, and write a mount point configuration for it. The 
Resource Manager would then be automatically registered on traversal.

I had started work in that direction but I've been sidetracked and won't be 
able to come back to that for at least a few months.

Florent

> Documentation on the 'transaction' package is scant. I can't tell if
> it's primarily an abstraction of a useful tool from the ZODB, or if it
> maybe wants to be a Transaction Manager for Python. If it's the
> latter, it could stand to have more documentation. Trying to figure
> out where to plug in (if possible) in this situation is tricky.
> 
> And I imagine I'm probably overthinking everything at this point
> anyways. Maybe it'd be better to just ask for some kind of two-phase
> commit support in SQLAlchemy's unit of work system where the object
> graph writing and the actual 'commit' command for the storage are more
> obviously separated and usable for API's like Zope's.
> 
>> Martin
> 
> 
> --
> Jeff Shell


-- 
Florent Guillaume, Nuxeo (Paris, France)   Director of R&D
+33 1 40 33 71 59   http://nuxeo.com   fg at nuxeo.com