[Zope-DB] Per-user connections
Fri, 06 Dec 2002 10:54:35 +0100
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,
> > * 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.
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
> 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"
> 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