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

[j.kartnaller]

Jeff Shell wrote:
> 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.

Separation of definition and implementation is a natural approach for a Zope user.

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

That's why I use beforeTraverseEvent. It's the only option I found.
I also have no idea how the synchronizer can be used if there is no
hook to add it to a thread.

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

Right

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

I personally don't see this "big" thing. All I need is a simple possibility to 
use a relational database in an object oriented way.


Jürgen