[Zope-dev] Re: Per-user Oracle connections (fwd)

Jim Fulton jim@digicool.com
Mon, 31 Jan 2000 13:33:36 -0500


Oleg Broytmann wrote:
> 
> Hi!
> 
>    Nobody seems able to answer, trying to repost...
> 
> On Tue, 18 Jan 2000, Jim Fulton wrote:
> > >    I have an Oracle database, and by design every user that logs into the
> > > site will be connected to the DB with his/her login/password (that is,
> > > his/her Oracle login/password is the same as for WWW).
> > >
> > >    I am considering using Zope for the site, but the question is - how can
> > > I create one ZOracleDA connection for every user, and how can I use diferent
> > > connections for different users?
> >
> > You can't.  However, you could create a new kind of connection
> > object that did this for you.  This should be a reasonably
> > straightforward hack of ZOracleDA:
> >
> >   - Take a database name through the web.
> 
>    Not a database name - it's fixed - but login/password. Well, I can do
> it, but how can I pass this information to Connection object?

Through AUTHENTICATED_USER.  I assume that you'll represent users
using Zope user objects.  Alternatively, yuo'd have to provide your
own user authentication logic.

>    Well, recently I read the code for Postgres, MySQL and Oracle adapters,
> and a lot of code in lib/python/Shared/DC/ZRDB, so I thing I do understand
> what is Connection object, adapter and so on.`
> 
>    It seems I need to write a Connection object based on
> Shared.DC.ZRDB.THUNK.THUNKED_TM or Shared.DC.ZRDB.THUNK.TM,

You don't need a thunked TM for Oracle. You should be
able to use Shared.DC.ZRDB.TM.TM

> maintain a pool
> of actual database connections, and pass queries down to
> db_connection.query(). But how can I find the db_connection? It is an
> object from my pool of connection, but I need to know 2 things:
> 
>    1) login/password; I thing the pool will be just a dictionary indexed by
> (login, password) tuples. Does this information available in the query()
> method?
>    I can stuff login/password into a REQUEST object, but is the REQUEST
> object available in the query() ?

You need to select a connection based on a login (or login and password)
gotten from AUTHENTICATED_USER, which you can get from the request, 
which you can acquire:

  user=self.REQUEST['AUTHENTICATED_USER']
  login=user.getUserName()
  pw=user._getPassword()
  connection=self.getCachedConnection(login, pw)
  connection.query(...)

of course, you'll have to figure out getCachedConnection
yourself. You may also want to implement a custom user folder
of some sort.

>    2) whether the connection is actually running a query; if the query is
> in the process - I need to open new connection for the same user. How do I
> ask the connection object "Are you running a query"?

You need to keep a cache of connections by user in your connection
objects.  You don't need to worry about threading. ZODB will automatically
manage multiple compies of your connection object and multiple caches
based on usage.

Jim

--
Jim Fulton           mailto:jim@digicool.com   Python Powered!        
Technical Director   (888) 344-4332            http://www.python.org  
Digital Creations    http://www.digicool.com   http://www.zope.org    

Under US Code Title 47, Sec.227(b)(1)(C), Sec.227(a)(2)(B) This email
address may not be added to any commercial mail list with out my
permission.  Violation of my privacy with advertising or SPAM will
result in a suit for a MINIMUM of $500 damages/incident, $1500 for
repeats.