Forum OpenACS Q&A: Querying other DB's from OACS
I thinking I would have to set up an new pool connection to the other DB then provide a switch to allow the new DB pool to be selected in the DB API commands or a new command to switch the DB for all following commands until its switched back. I've had a cursory glance at the DB API source but haven't delved into it yet. Just thought I'd ask in case someones done this already or I'm missing something obvious :)
There are several layers involved.
The OACS DB Layer (db_foreach, ...) sits on top of the AOLserver database driver layer.
You're right in that you set up a different pool to speak to each database, configuring each pool in your config.tcl.
In your config.tcl, I believe all you need to do is to set ns_section ns/db/pool/remotepool datasource to point to the right host and database, maybe something like: ashcroft.justice.gov::tia.
You will need to check on your other pg instance to make sure it can accept connections via tcp. IIRC, the default ootb is that it will not accept tcp based connections on linux.
But you're also right in that the public interface to the OACS db layer (db_... ) doesn't make it easy to switch pools.
So you either need to add that, use the older and funnier ns_db layer, or create another instance of your application that just points to the remote database.
One way of being able to use the DB API in your queries against this other data would be to have a scheduled proc. accessing the external data with the ns_db API and copying changed data to a set of local tables. This way you could use knowledge of how the data changes to only copy across what is required at each update, and run the synchroniser more frequently than would be possible if you were doing a full dump & copy...
Steve, if you need or want multi-db support in your current 4.6.x OpenACS, it shouldn't be too hard to backport the code from the Head, it's fairly self-contained. There changes however, were in three different places: The acs-tcl package, the acs-bootstrap-installer package, and the etc/config.tcl file.
If so I'll look at either accessing the DB the old AOLServer ns_db way or replicate the tables in the OACS DB. I had thought of using a scheduled proc but if I'm going down this route it may be worth looking at a replication journal and applying that. Don't no if the Postgres replication stuff has moved on much - last time I looked it was still a bit virgin.
The project isn't due just yet - I will continue with a copy of the tables in the OACS DB and hopefully 5 will be released before I need to go live - otherwise I'll backport the changes to whatever version I use.
Hm, when is OpenACS 5.0 scheduled for release anyway? I'm not sure.
Late September according to this thread http://openacs.org/forums/message-view?message_id=114675