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