Forum OpenACS Q&A: Querying other DB's from OACS

Posted by Steve Manning on 09/01/03 12:07 AM

As part of a job I'm working on I may have to use access tables stored in another Postgres database. Is it possible to access another database using the OACS DB API? I may be able to replicate the required tables in the OACS DB but it would be easier if I could access them in situ as they change daily.

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 :)

TIA

Steve

2: Re: Querying other DB's from OACS (response to 1)

Posted by Jerry Asher on 09/01/03 02:48 AM

Short answer: Yes and no for the reasons you have suggested.

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.

3: Re: Querying other DB's from OACS (response to 1)

Posted by russell muetzelfeldt on 09/01/03 09:32 AM

If you define any other pools, you will also need to add an ns/server/yourserver/acs/database section to your config file with an AvailablePool entry for each ACS database pool (pool1, pool2, pool3). If no such entries exist the DB API will use all available pools (including the one going to your other 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...

4: multi-db support is done (response to 1)

Posted by Andrew Piskorski on 09/01/03 11:16 AM

I completed multi-database support for the db_* API on the Head back in April. Therefore, the info Jerry and Russel give above is accurate for OpenACS 4.6.x and earlier, but not for the OpenACS Head - what will be OpenACS 5.0.

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.

5: Re: Querying other DB's from OACS (response to 1)

Posted by Steve Manning on 09/01/03 11:26 AM

Thanks. So would I be opening a hermetically sealed can of the genus Lumbricus in trying to adopt the API to work with more than one DB?

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.

Steve

6: Re: multi-db support is done (response to 4)

Posted by Steve Manning on 09/01/03 11:51 AM

Thanks Andrew - I think we cross posted. Its really encouraging that everytime I ask about a feature its already been considered and is usually in the HEAD :)

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.

Thanks again.

Steve

7: Re: Querying other DB's from OACS (response to 5)

Posted by Andrew Piskorski on 09/01/03 11:58 AM

If you like the OpenACS db api better than ns_db in the first place, then you are also going to like it better for accessing your second database as well. The only question is whether you want to take the trouble to back port the mult-db support to 4.6.x or not. If you're going to have lots of code that needs to talk to your second database, I'd do it. If you just have a couple utility procedures, then it's probably not worth the trouble, just use ns_db for now and you can change those couple procedures when you upgrade to 5.0, if you feel like it.

Hm, when is OpenACS 5.0 scheduled for release anyway? I'm not sure.

8: Re: Querying other DB's from OACS (response to 7)

Posted by Steve Manning on 09/01/03 01:52 PM

>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

Steve