[Zope-dev] Re: straighting out the SQLAlchemy integration mess

[Laurence Rowe]

The transaction manager here:

http://svn.plone.org/svn/collective/collective.lead/branches/elro-tpc/collective/lead/tx.py

Has support for TPC and savepoints. It is tested and works. The only 
issue with sqlite is that the default in the branch is to use two-phase 
commit and sqlite does not support this. Apart from that the module is 
completely self contained.

The public interfaces to it is the two functions at the bottom, 
join_transaction and dirty_session. The idea of the dirty_session stuff 
is to prevent unnecessary commits when data is only read. Session writes 
  trigger the 'dirtying' automatically with a SessionExtension:

http://svn.plone.org/svn/collective/collective.lead/branches/elro-tpc/collective/lead/database.py

The downside of this approach is that the when you interact with the 
connection directly to modify data you must call dirty_session yourself. 
If this is a problem for you then you may set up the transaction to be 
in the dirty state initially.

Thread based session management is used (scoped_session). There is 
fairly extensive testing of this and the transaction support here:

http://svn.plone.org/svn/collective/collective.lead/branches/elro-tpc/collective/lead/tests.py

I think it would be worth factoring out the threaded session support as 
well as the transactions.

@Christian:

I'm not quite sure what you mean by:

- Database triggers as events

I don't see how you could reasonably get a database trigger on the db 
server to instantiate a zope event on the db client. Perhaps the 
database could be persuaded to make a request to a URL on a trigger, but 
this seems a bit slow and maybe not a great solution.

- Making SA 0.4 work with security proxies

I don't know anything about security proxies in zope 3, but surely this 
is just a matter of what the base class is / the container 
implementation? I don't see why an SQL Alchemy mapped object would be 
any different to any other object.

@Martin:

Should one phase commit be set as the default to make it easier to work 
with sqlite (and mssql)? Probably yes.

Should the default be for sessions to start out `active` or `dirty`? If 
they start out `dirty` then existing 1.0 code should work as before.

It'll probably be next week before I manage to get this tidied up and 
merged back to trunk.

@everyone:

If we can all agree to use the same basic session and transaction 
management then we should probably push for it to be included as a 
sqlalchemy extension module.


Laurence

Martin Aspeli wrote:
> Martijn Faassen wrote:
>> Hi there,
>>
>> [I originally picked this up on a thread on zope3-users, but this 
>> deserves its own thread here]
>>
>> There are at least three approaches to SQLAlchemy integration with Zope:
>>
>> * z3c.zalchemy (Christian Theune)
>>
>> * z3c.sqlalchemy (Andreas Jung)
>>
>> * collective.lead (Laurence Rowe)
>>
>> All of these are in various states of brokenness. z3c.zalchemy doesn't 
>> work with SQLAlchemy trunk. collective.lead works with it, but only if 
>> you check out a particular branch, and not with sqlite. Quite possibly 
>> z3c.sqlalchemy has a release that actually works. One out of three is 
>> not bad... :)
>>
>> Then there's also mentions about WSGI-based integration, and I think 
>> in Plone, Alchemist probably also does its own integration...
>>
>> There must be a reason for this proliferation of approaches. What is 
>> it?   We all get along, don't we? I know that the various packages are 
>> taking code and approaches from each other too.
>>
>> Can't we work together more and at least come up with *one* package 
>> that works? Perhaps factor out some low-level commonality than then 
>> all share? Criticize one of the other packages until you're satisfied, 
>> and then retire your own package perhaps? I know the various packages 
>> add on their own approaches to configuration and might offer higher 
>> level container approaches. Those could be in different packages, 
>> sharing a foundation.
>>
>> In the end, I hope we will end up with just *one* integration layer, 
>> that is released, that works with Zope 2 and Zope 3 and a recent 
>> release of SQLAlchemy, that is documented, and that people know about. 
>> We can then offer packages on top of this that offer extra features.
> 
> I'm all for one integration layer, even though I spurred one of the 
> above (collective.lead). I would ask that we be very conservative, 
> though. collective.lead wants to provide:
> 
>  - automatic transaction integration with ZODB transactions
>  - an easy way to register your database connections
>  - an easy way to look those up as utilities and get an ORM session
> 
> Everything else (e.g. Zope ORM integration, configuration abstractions, 
> etc) should be in some other package.
> 
> collective.lead has a reasonable degree of traction in the Plone 
> universe, at least, but should work just fine with plain Zope 3. We have 
> other things that work on top it (mercury, rope, probably others).
> 
> The branch that works with trunk also has some carefully worked out TPC 
> support, which is difficult to get right, though I'm disappointed that 
> we seem to have broken sqlite integration. Hopefully it's an easy fix, 
> though.
> 
> I'm CC'ing Laurence to ask:
> 
>  - can we get a release soon?
>  - can we fix the sqlite integration?
> 
> Cheers,
> Martin
>