[Zope-dev] zope.sqlalchemy, integration ideas

[Martijn Faassen]

Hi there,

Today I had a discussion with Jasper Spaans about how to go about 
improving megrok.rdb, Grok's relational database integration which aims 
to integrate Grok with SQLAlchemy. We started developing megrok.rdb at 
the Grokkerdam sprint a few weeks ago. We reread the discussion 
surrounding zope.sqlalchemy for ideas on how to go about integration and 
configuration. I think these discussions reach wider than just Grok's 
concerns. Note that I'm not proposing we fold any of these ideas into 
zope.sqlalchemy itself, which should remain as free of policy as 
possible; it could become (yet another) extension.

Let me run our current thinking by the list.

What would be nice in Zope applications (and we think would be good for 
Grok) would be per-instance database configuration. That is, we want to 
be able to install multiple instances of the same application and then 
configure each of them with a different database URN and it should all 
work, each talking to their own database.

Michael Bayer's suggestion involves the use of scoped sessions. He 
proposed the following code:

Session = scoped_session()

# start of request
engine = get_appropriate_engine()
Session(bind=engine)
try:
     # do request
finally:
     Session.remove()

Let's go through the steps. First it makes a scoped session object, it 
then configures it with the right engine at the start of the request (it 
can do this on a per-class level), and then at the end of the request it 
removes the Session again, which results in the actual session being closed.

Our get_appropriate_engine() would probably look the engine up as a 
local utility, as Laurence suggested. There is a bit of question about 
engine configuration, though.

If we want to support the use case of looking up the engine URL in a 
persistent datastore (for instance one URL per location), we have a 
question of ordering. We cannot do it too early; at the start of the 
transaction there isn't a ZODB yet to talk to so we can't look up a 
local utility. We can try doing it just in time:

_Session = scoped_session()

def Session(*args, **kw):
     engine = get_appropriate_engine()
     _Session.bind(bind=engine)
     return _Session(*args, **kw)

Here get_appropriate_engine() could do a component.getUtility() and look 
up the engine for us, possibly in an application-local way. There's 
still the question of how this engine got configured in the first place. 
How does it know the database URL? How does the engine get created after 
the database URL is known (this might be quite late in the game; it 
could be stored in the ZODB). It then starts to look more and more 
attractive to do something similar like collective.lead's IDatabase 
utility, which can be stored persistently in the ZODB and has a 
getEngine() method which actually gets the engine (creating it if 
necessary).

If we use sqlalchemy.ext.declarative, we also need to make the 
declarative extension of SQLALchemy load up the tables at the right 
point in time.

We would also like a way to hook into matters and register some of our 
own tables and mappers manually. We figured perhaps the utility could 
fire an event that you can then write a handler for. This way there's 
less need to subclass the utility just to change some configuration 
(this is what collective.lead currently requires you to do). If a 
persistent local utility is in play, it shouldn't fire the configuration 
event during its own creation, as that would mean it'd only be fired 
once ever. We want to fire it just after engine creation.

I guess the database utility can remain quite simple. Its main tasks 
would be:

* allow access to the engine (creating it the first time)

* fire the event for additional configuration when the engine is first 
created

* maintain or somehow obtain the database URL. This could be retrieved 
from the ZODB if it's a local utility, or it could be hardcoded into a 
global utility, or it could be retrieved from some config file by a 
global utility.

We could have an expanded variety which also configures things using the 
SQLAlchemy declarative extension.

We still have the question of the 'remove()' bit in Michael's code. We 
looked at ScopedSession's remove() method, and it looks like it removes 
the session from the thread-local storage, and it actually closes the 
session.

Closing the session should be taken care of: zope.sqlalchemy's 
integration with Zope's transaction machinery will close the session. 
What about the registry cleanup that remove() appears to do? Is this 
currently being done by zope.sqlalchemy? Should it be?

Anyway, a whole lot of abstract talk. I still hope to get some feedback 
on this.

Regards,

Martijn