7.4.06. Because I Could (SQLAlchemy, Zope 3, Metaclass Fun, Etc)

I continue to be (generally) impressed by SQLAlchemy. After a couple of days worth of work, digging around the guts of Zope transactions and thread management along with SQLAlchemy’s guts of similar nature, I think I have a system in place that will work fine with Zope 3. Zope 3 uses a two phase commit transaction management system, whereas SQLAlchemy generally supports the basic ‘begin / commit / abort’ cycle. However, by taking advantage of SQLAlchemy’s nesting of transactions, I essentially have the following model in place:

• Zope transaction begin—SQLAlchemy engine begin (starts a fresh outer scope and calls ‘begin’ on the database connection itself)
• On Zope commit start (first phase of commit)—SQLAlchemy objectstore commit (sends changes managed in SQLAlchemy’s unit of work / object graph over the wire)
  • This is of interest because objectstore commit also calls engine.begin() and engine.commit(). SQLAlchemy is designed for ‘reentrance’, so that multiple calls to something like engine.begin() are OK – as long as they’re matched by an equal number of finishing calls like engine.commit(). When the nesting count is back to zero, engine.commit() saves.
  • In two phase commits, it’s allowable for an error to happen here. It’s probably not preferred, but it’s OK.
• On Zope commit finish (final phase of commit)—SQLAlchemy engine commit. This ‘commit’ matches to the ‘begin’ at the start of the Zope transaction, and sends the ‘COMMIT’ statement to the target database.

Beyond that, I’m not doing too much work to track changes in either environment (SQLALchemy, ZODB) in this current setup. ZODB’s persistence manages the loading and unloading of its objects, and SQLAlchemy manages its own too. It’s not that I’m trying to mix them – it’s just that Zope really really really wants to run with the ZODB, so even if it’s not used, it’s probably going to be there.

If this system ends up actually doing its job, I’ll share the code. I’m trying to write it separate from the application I’m developing.

Also: for kicks, I’ve toyed with how I might get ActiveRecord style sytax (which I like) using Python and SQLAlchemy. This mostly works as it stands right now:

    from ruts.storage.base import Base
    from ruts.storage import act
    import db.tables

    class Venue(Base):
        act.fromTable(db.tables.venues)

        act.hasMany('Event', 'events', order_by='start_date')

    class Presenter(Base):
        act.fromTable(db.tables.presenters)

        act.hasMany('Event', 'events', order_by='start_date')

    class Event(Base):
        act.fromTable(db.tables.events)

        act.hasOne(Presenter)
        act.hasOne(Venue)

    >>> v = Venue.get(1)
    >>> v.events
    [<exmple.model.Event object at 0x104a930>]
    >>> v.events[0].venue.title
    'Testy McVenueVille'
    >>> v.events.append(Event(title="Jackson"))    
    >>> v.commit()
    >>> v.events
    [<example.model.Event object at 0x104a930>, <example.model.Event object at 0x2b2c090>]
    >>> e2 = Event.get(2)
    >>> e = Event.get(1)
    >>> from datetime import timedelta
    >>> e2.start_date = e.start_date + timedelta(1)
    >>> e2.end_date = e.end_date + timedelta(1)
    >>> e.end_date
    datetime.datetime(2006, 4, 8, 23, 0)
    >>> e2.end_date
    datetime.datetime(2006, 4, 9, 23, 0)
    >>> v.events[1].end_date
    datetime.datetime(2006, 4, 9, 23, 0)
    >>> v.events[1].title
    'Jackson'