[Zope-DB] Per-user connections

[Yves Bastide]

Dieter Maurer wrote:
> Yves Bastide writes:
>  > For reasons best left in the dark, I'd like to have per-user database 
>  > connections.  Since this is in an application where (most of) the SQL is 
>  > already done in a module, I see two ways of doing:
>  > 
>  > * create a Connection object, stored in the Sessin, when the user is 
>  > authentified; to use ZSQL methods, subclass the DA to take the 
>  > Connection parameter in __call__
> This is very difficult, at least with the ZODB based sessions
> of the Zope core.
> 
> Synchronization is also difficult with pure RAM based sessions,
> such as ZSession.

Can you elaborate?  Problems with storing objects in the session object, 
with lifetime?

> 
>  > * don't count on using ZSQL methods; use a class derived from the one I 
>  > use outside of Zope, and from Shared.DC.ZRDB.TM's TM to know when to 
>  > commit/rollback.
> Why?

Er, why what? :)
My application uses biopython's BioSQL to access the database, so all 
the requests are (will) be written there.  Now, there's only the matter 
of binding the connection to an user.

> 
>  > Can people familiar with Zope's machinery comment on these approaches 
>  > (or give me a better one)?
> Do not like any of them.
> 
>   My favorite approach would be to switch users for each request.
> 
>   This requires the database to support user switches for a given
>   connection.
> 
>   As I read in the SQLRelay documentation, Oracle does support it
>   and SQLRelay supports this feature for Oracle.

Yes, with proxy roles.  Hmm, may work (must see for triggers and such), 
but I need a solution useable for postgresql too

> 
>   With this possibility, you can use the following approach:
> 
>           create a DA variant that switches users in its "register"
>           method
> 
> 	  Use an instance of this DA whenever you need
> 	  user specific database operations.
> 
> 	  You can use all your Z SQL Methods (once pointed
> 	  to the required variant for the DA)

OK, understood.  Now the generalisation would be a DA with a pool of 
connections keyed by the user id

> 
> 
> Dieter
> 

yves