[Zope-dev] Re: zope.sqlalchemy

[Laurence Rowe]

Michael Bayer wrote:
> 
> On May 7, 2008, at 7:08 AM, Martijn Faassen wrote:
> 
>> Hi there (especially Christian),
>>
>> I think we can work with explicits saves. In many cases the user won't 
>> have to worry about it anyway as the container object will do it for 
>> them (besides making the relation), or this 'query container' we spoke 
>> of will do it for them (but just the 'save' bit).
>>
>> One point is that the scoped session approach itself doesn't work very 
>> well for using multiple databases in the same app. We could consider 
>> passing the session along in the containers during object graph 
>> wakling (or traversal) so an app can easily traverse into multiple 
>> databases. I'm not sure whether we can make the ORM do this for us 
>> though; does it initialize the mapping with a session?
>>
> 
> SQLAlchemy's Session does support multiple engine binds itself, which 
> most easily can be associated with particular mapped classes (i.e. 
> vertical partitioning),  so that a single session (or a scoped_session) 
> can read and write data to the appropriate tables transparently 
> (although things like joins across multiple databases will raise 
> errors).   Theres a horizontally-partitioning version of Session as well 
> which obviously has a lot more caveats.
> 
> Using multiple sessions, one per DB is a valid approach as well.    I'm 
> not sure if Grok has other things going on when mulitple DBs are in use 
> but SA's multi-bind capability is something to be aware of.

I'm thinking more about having the same classes mapped to different 
databases at different points in the application. Imagine a 
departmental address book app. Intstances of the departmental address 
book are created for each department, each with a different databases:

http://addressbook/sales -> postgres:///sales
http://addressbook/engineering -> postgres:///engineering

The way I imagine this working is to have a proxy engine object that 
looks up the real engine through a local utility. Each application would 
be a `site` and capable of local utility registrations. /sales would 
have Engine('postgres:///sales') registered and /engineering 
Engine('postgres:///engineering').

Only a single ScopedSession would be required. This would be bound to 
proxy that performs the utility lookup. So when in the /sales context 
the proxy would point to the sales engine and when in the /engineering 
context to the engineering engine.

The limitation of this approach is that it would not be possible to mix 
objects from /sales and objects from /engineering into the same 
transaction. So really we need a session per application instance. 
Perhaps this can be achieved through a custom scoping function:

def scopefunc():
     return thread.get_ident(), id(zope.component.getSiteManager())

Laurence