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

[Jeff Shell]

On 4/6/06, Martin Aspeli <optilude at gmx.net> 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.

>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)
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".

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