[Zope-dev] Re: zope.sqlalchemy, integration ideas

[Laurence Rowe]

Brian Sutherland wrote:
> On Fri, May 23, 2008 at 11:39:39PM +0100, Laurence Rowe wrote:
>> We need to differentiate between the interface for session configuration 
>> and session usage from an application.
>>
>> For session usage I think it is fairly simple. We should define an 
>> ISessionContext interface such that:
>>
>> class ISessionContext(Interface):
>>     def __call__():
>>         "return a session in this context"
> 
> +lots
> 
> (I was thinking about proposing an interface called ISessionMaker doing
> much the same thing)
> 
> I'm not sure what "in this context" means?

The context of this application instance. Say you have two instances of 
an application, one pointed at database A, another at database B. It is 
possible to involve both applications in a single request / transaction. 
You must be sure that you are working with the correct session.

> 
>> A future version of collective.lead could implement an ISessionContext. 
>> Client code however should have a cleaner interface, a plain ISession. This 
>> is accessed through a lookup on the context, translated into a simple 
>> adapter:
>>
>> def session_adapter(context):
>>     session_context = queryUtility(ISessionContext, context=context, 
> 
> Why call queryUtility with the context keyword?
> 
>> default=None)
>>     if session_context is not None:
>>         return session_context()
>>
>> This will allow application code to do something like:
>>
>> session = ISession(context)
>> ob = session.query(MyClass)
> 
> This really confuses me. What is the context? Does it have any meaning?
> Or is it just a shorter way to write:
> 
>     session = getUtility(ISessionContext)()
> 
> Does the value of context have an effect on what you get from the
> ISession adaptation?

Yes, as it translates directly to the getUtility lookup. It ensures that 
lookups in /appA go to a local utility defined in /appA and lookups in 
/appB go to a local utility defined in /appB.

> 
>> Of course it would be possible to register a ScopedSession globally as such 
>> a utility, but more usually a local utility should be registered.
> 
> Depends what you're doing. If you are running without a ZODB, you have
> mostly just global utilities.
> 
> It would be a pity if zope.sqlalchemy started to depend on the ZODB.

Wihout ZODB and zope.app.component it seems unlikely that you would be 
able to register a local utility, the idea of ISessionContext is so that 
you might be able to register a ScopedSession as a global utility too.

>> (I haven't though about the consequences of this in pre-traversal, before 
>> the site and local utilities are set up)
>>
>> session.remove() is not important, sessions are closed by the 
>> zope.sqlalchemy datamanager and closed sessions are recyclable. Presumably 
>> the session object would be referred to by a volatile attribute on the 
>> local utility and the session would be GC'd along with the local utility 
>> object itself.
>>
>> Table creation is another matter that I don't think too important. Implicit 
>> creation of tables seems wrong, instead tables should only be created 
>> explicitly, by the use clicking a button in the zope web interface (or 
>> automatically on adding an application). An exception to this is sqlalchemy 
>> in memory databases, which must be created on first access.
>>
>> Session configuration would be somewhat similar to collective.lead 
>> currently (registering one as a local utility).
>>
>> Laurence
>>
>> Martijn Faassen wrote:
>>> 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
>>>
>>> _______________________________________________
>>> Zope-Dev maillist  -  Zope-Dev at zope.org
>>> http://mail.zope.org/mailman/listinfo/zope-dev
>>> **  No cross posts or HTML encoding!  **
>>> (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce
>>> http://mail.zope.org/mailman/listinfo/zope )
>>>
>> _______________________________________________
>> Zope-Dev maillist  -  Zope-Dev at zope.org
>> http://mail.zope.org/mailman/listinfo/zope-dev
>> **  No cross posts or HTML encoding!  **
>> (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce
>> http://mail.zope.org/mailman/listinfo/zope )
>