[Zope-dev] Re: SQLAlchemy integration experiment

[Martijn Faassen]

Brian Sutherland wrote:
> On Mon, Jun 16, 2008 at 08:40:24PM +0200, Martijn Faassen wrote:
[snip]
>> from z3c.sa_integration import Session
>>
>> ...
>> def somewhere_in_a_view(self):
>>     session = Session()
>>     return session.query(Test).all()
> 
> For some reason this raises a warning bell in my head. I keep on
> thinking: this is zope, the session is a classic case for a utility, we
> should be getting it in views by an interface.

Well, it's also SQLAlchemy, where people get a session this way. That 
said, integrating the two frameworks produces lots of questions about 
which framework takes precedence. I've chosen to try to make the 
SQLAlchemy examples work in the way I interpret them they should work, 
and try to use SQLAlchemy infrastructure as much as possible, but this 
is certainly not the only choice.

> That may be because I've already become too fixed in the way I think
> about things.
> 
> However, Laurence's point about database re-connection is relevant here.
> If the Session were looked up by an interface, someone could implement
> seamless database re-connection (lots of complex code) without polluting
> zope.sqlalchemy.

And what is seamless database re-connection and why is this a job for 
Zope and not for SQLAlchemy?

>> 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
> 
> This looks very nice. I'm not sure IDatabase.engine is necessary, it
> seems to be only used internally to Database. 

Yes, but it is policy someone may want to change. In this case I 
implemented the policy to look up the engine by name, but it might be 
the default policy would be to simply look up IEngine locally or something.

> Also, any view code  should really be getting the connection directly from the session.

I don't understand that what you mean by 'connection'. Oh, you mean 
which engine is used? That's really just some testing code, I imagine it 
could directly manipulate the session in realer code.

[snip]
> As Laurence has suggested, the session persists, so
> IDatabase.configuration will only be called once per thread. Not sure if
> creating one new engine per thread is bad.

I don't know either. I've assumed that one wouldn't want to recreate the 
engine for each thread, but perhaps that's fine and we don't break any, 
say, connection pooling mechanisms that way? I do not know.

Regards,

Martijn