[Zope] Managing a pool of database connections

[Tino Wildenhain]

Hi David,

David Mitchell schrieb:
> Hello list,
> 
> I have a bunch of Postgres databases, containing archived data.  The 
> number of databases is growing over time, and already numbers several 
> thousand.
> 
> I want to give my Zope users the ability to do the following:
> - specify a database to connect to
> - select from a list of "lookup" values for certain fields in that database
> - search the database for data based on the entered "lookup" values
> - display the results
> 
> The only part I'm having trouble with is the first step.  I don't want 
> to pre-define thousands of database connections, and have to continually 
> add new connections - most of the databases will only be accessed 
> rarely, so creating all those database definitions would constitute a 
> significant time and resource drain.
> 
> What I'd like to do is:
> - user enters a database name
> - a new Zope database connection gets created programmatically, pointing 
> to the requested database (how do I do this???)
> - using this database connection, we look for data in several tables and 
> present the results as "lookup" values
> - user searches for data
> - results displayed
> - user disconnects, times out or logs out
> - database connection gets closed
> 
> Is it possible to create and destroy database connections in Zope like 
> this, and if so, what's the best way to go about it?

I think I would patch the hell out of ZPsycopgDA
to be able to connect to "template1" and
retrieve a list of currently defined databases

SELECT d.datname as "Name",
        u.usename as "Owner",
        pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding"
FROM pg_catalog.pg_database d
   LEFT JOIN pg_catalog.pg_user u ON d.datdba = u.usesysid
ORDER BY 1;

(borrowed from psql - you may have to check actual version of
Postgresql you use)

Next step would be creating the pool of connections,
handled by your special branch of the Database Adaptor.
You may also subclass ZSQLMethods to be able to
specify the current DB you want to run the query
(instead of modifying the persistent value for
the connection since you have to deal with concurrent
access)

Btw. if you run a recent postgresql version (7.3+)
you might be better off just using Schemas.

Regards
Tino Wildenhain