[Zope] Oracle or PostGreSQL connection pool

[Oleg Broytmann]

Hi!

   Is there any way to implement the following thing?

   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?

   Cuurently the site is in the process of development, and we are
considering different techiniques. Zope is one of them; another possible
solution will be CGIs + small server (on WWW side) that will handle
long-time pool of connections from WWW to Oracle (CGIs will not be
connected to Oracle directly, but will connect to the server, use a
connection from the pool, etc.)

On Mon, 17 Jan 2000, Jim Fulton wrote:
> Here's a longer explanation.  Zope maintains a pool
> of ZODB database connections.  Currently this pool size is
> set to 7 (for non-version connections).  (There ought to be
> a way to adjust this without hacking Python code, 
> and there will be in 2.2.)  Practically speaking, 
> the ZODB connection pool size is the lesser of the 
> number of threads and 7. What does this have to do with
> Oracle or Postgres connections?  
> 
> When you define a database connection object in Zope, 
> there could be a copy of the connection object for each 
> ZODB connection, depending on how heavily the connection 
> object is used. Each copy of the connection object will 
> have it's own connection to the underlying database.  
> This means that there will be an RDBMS connection pool 
> for each Zope database connection object. The size of 
> this connection pool is the lesser of the number of
> zope threads and the size of the ZODB connection pool. 
> RDBMS connections will be added and removed from the connection 
> pool based on need.
> 
> Consider the following example. Suppose we have a Zope process using
> ZServer with the default thread/pool configuration and a single
> ZOracleDA database connection object.  If the site is handling alot of 
> requests that require access to Oracle, then there will probably be
> 4 oracle connections, since the default number of Zope threads is 4.
> If the nuber of requests requiring Oracle support drops off, then 
> will the number of Oracle connections.  In fact, the number of Oracle
> connections could drop to zero. If the number of requests pick up, then
> so will the number of Oracle connections.  This all happens automatically
> as a by-product of the standard Zope management of ZODB database objects.
> 
> One caveate is that to get the benefit of connection pooling, the 
> database adapter needs to be fully threaded. This is the case for Oracle.
> I'm not sure what the statis is for the Postgres DA.
> 
> We plan to put together a more extensive explanation of this in the form
> of a white paper or how to. I hope this explanation helps in the
> mean time.
> 
> 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.

Oleg.
---- 
    Oleg Broytmann      Foundation for Effective Policies      phd@phd.russ.ru
           Programmers don't die, they just GOSUB without RETURN.