[Zope-dev] SQLAlchemy integration experiment

[Martijn Faassen]

Hi there,

In some earlier discussions a number of approaches to integrate 
SQLAlchemy into Zope were discussed. Following up on that, I've tried a 
particular approach that tries to use ScopedSessions with a custom scope 
that isn't just per thread, but also per site (application). The benefit 
of this approach is that it should allow the following to just work:

from z3c.sa_integration import Session

...
def somewhere_in_a_view(self):
     session = Session()
     return session.query(Test).all()

where 'Session' is a custom SA scoped session.

I've checked in my (documented) experiment in here:

svn://svn.zope.org/repos/main/Sandbox/faassen/rdbintegration/trunk

The interesting bits are here:

http://svn.zope.org/Sandbox/faassen/rdbintegration/trunk/src/rdbintegration/app.py

See the documentation in that file for more information.

The one bit I'm not happy about yet is the way engines need to be global 
utilities now. I'd like it to be possible for users to edit the database 
connection parameters through the web. I have a sketchy idea about how 
to solve this by registering engines as non-persistent local utilities, 
but it's still unclear how they'd get created at the right time. It 
might also be possible to simply implement the engine method on 
IDatabase to create the engine when called the first time, and then 
cache it in a volatile property. I think this is something Laurence Rowe 
has been talking about. I'm not sure whether it's a good idea to rely on 
the ZODB cache, but it might be possible to simply cache the engines in 
a global registry...

Comments? Suggestions?

Regards,

Martijn