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

[Laurence Rowe]

Martijn Faassen wrote:
> Laurence Rowe wrote:
>> We need to differentiate between the interface for session 
>> configuration and session usage from an application.
> 
> Session configuration? I'm talking about engine configuration. A session 
> doesn't need to be configured, except with a session, I think, which the 
> ScopedSession machinery allows you to do. If you can make the engine be 
> the right one in the appropriate context it should only be a matter of 
> configuring the session in that context.
> 
>> 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"
>>
>> 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, 
>> 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)
> 
> I don't understand what the point of doing this is.
> 
> What is 'context' here? Why am I adapting context? My context is 
> normally thread-local and implicit. This has been the way to approach 
> context in Zope 3 for a long time now (though explicit context can still 
> be passed through getUtility it's rarely done).
> 
> And again, I think *engine* should be in context, not sessions. 
> ScopedSession, a standard SQLAlchemy mechanism, should be used for 
> session context-specific session access.
> 
> Why the introduction of ISessionContext and ISession interfaces, and an 
> adapter lookup that looks up a utility and then *still* I haven't seen 
> the code that actually configures the engine? My aim was to try to stick 
> to SQLAlchemy patterns for solving this problem where we had no reason 
> to diverge from them, and our use case, I take it, is what ScopedSession 
> was designed for.
> 
> [snip]
>> session.remove() is not important, sessions are closed by the 
>> zope.sqlalchemy datamanager and closed sessions are recyclable. 
> 
> That's good to seee confirmed. I thought it was that way reading the 
> code, but I wanted to make sure.
> 
>> 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.
> 
> Are you talking about a persistent local utility? Which one? Would this 
> mean that the session needs to be re-created each time the ZODB swaps 
> out the object with the volatile attribute? I thought the session was 
> intended to be recreated each request, does it make any sense to cache 
> them between requests?
> 
>> Table creation is another matter that I don't think too important. 
> 
> I think it's important to get it right for Grok. We're using the 
> declarative extension and still want to support hand-created tables as 
> well. There are various scenarios surrounding table creation, either not 
> doing it at all ever, or spelling them out by hand in Python, or by 
> inlining them into the classes as with the declarative extension.
> 
>> 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).
> 
> Automatic on adding an application is a good point to do it, as that way 
> we don't bother the person who creates the application too much (first 
> create the database.. then install the application, then go to this 
> screen and create the tables).
> 
> There's another feature to automatic table creation though: when I'm 
> developing and I don't care about the data yet, all I need to do now 
> when I change the schema is throw away the database and create it again. 
>  I found this very convenient while developing with collective.lead. We 
> need to have something that is at least as convenient for this use case 
> (common during initial development).
> 
>> An exception to this is sqlalchemy in memory databases, which must be 
>> created on first access.
> 
> Don't know what these are?

Those with a url like 'sqlite://:memory:'

>> Session configuration would be somewhat similar to collective.lead 
>> currently (registering one as a local utility).
> 
> Before we talk more about session configuration, please explain why 
> we're not talking about engine configuration. :)

Engine configuration is a subset of session configuration. You cannot 
have a single ScopedSession for a package if you want to have multiple 
instances of that application. We must work with unbound metadata if we 
have this goal. That implies that we must use bound sessions, a session 
associated with a particular engine (actually it could be more complex 
than this, you can associate particular classes/tables with particular 
engines within a session).

The simplest way to achieve this is to associate an engine with a single 
(scoped) session, and create the engine based on some configuration 
infomation used to instantiate the scoped session / thing that 
implements ISessionContext.

In collective.tin I got bored passing around IDatabase utility names 
everywhere I needed to access a session, and created an IDatabase 
adapter that looked up the IDatabase on the __parent__ object. This 
would then be resolved to an actual utility lookup when it reached the 
top level. On a second look, this is entirely unnecessary... It is 
possible to retrieve the session for a mapped object using:

   session = sqlalchemy.orm.session.Session.object_session(obj)

I'm not sure whether it would be a good idea to wrap this in a session 
property, or just register it as an adapter. The only other object that 
would need access to the session (either as a property or through 
adaption) would be the application instance root object. Something like:

@adapter(MyApp)
@provider(ISession)
def root_session(context):
     return context._sessioncontext()

And the simplest persistent session context might be:

class PersistentSessionContext(persistent):
   implements(ISessionContext)

   def __init__(self, url, twophase=False, engine_kw={}, session_kw={}):
     self.url = url
     self.twophase = twophase
     self.engine_kw = engine_kw
     self.session_kw = session_kw

   def __call__(self):
     session = getattr(self._v_session, None)
     if session is None:
       engine = getattr(self._v_engine, None)
       if engine is None:
         engine = self._v_engine = create_engine(
           self.url, **self.engine_kw)
       session = self._v_session = create_session(
         bind=engine, twophase=self.twophase, **self.session_kw)
     return session


A more complex scheme might maintain a dict of ScopedSessions keyed by 
application path, outside of the object cache. You could also ensure 
that only a single engine is created for a given set of arguments, but 
this seems overkill

Everything would then get a session consistently with a call to 
ISession(self) or ISession(self.context) in the case of views. No parent 
pointers involved.

We do still need to setup parent pointers though for grok.url and 
IAbsoluteURL to work. It looks fairly easy to add location information 
to the containers themselves:

from sqlalchemy.orm.collections import MappedCollection, collection_adapter

class LocatedCollection(MappedCollection):

   @property
   def __name__(self):
     return collection_adapter(self).attr.key

   @property
   def __parent__(self):
     return collection_adapter(self).owner_state.obj()

But locating mapped objects themselves is more complex, they could exist 
in more than one collection at once. Perhaps A LocationProxy could solve 
this.

Laurence