Forum OpenACS Development: Re: driver support for multi-db db_api

Collapse
4: Re: driver support for multi-db db_api (response to 1)
Posted by John Sequeira on 03/21/03 12:57 PM
Andy,

You might want to look at how nstcl solves the multiple db problem.  I'm not quite sure that its multi-db support will be directly compatible with yours - I've peeked at it but spent most of my time working on templating - but MC's code is extremely well organized, mixes OO and tcl idioms well, and might serve as a good basis for what you're trying to do.

http://michael.cleverly.com/nstcl/database%5fapi.html

I confess to suggesting this for self reasons:  this would save me from writing more glue code.

Collapse
5: Re: driver support for multi-db db_api (response to 4)
Posted by Andrew Piskorski on 03/26/03 05:43 AM
John, when I last looked at the nstcl implementation it's db pool handling was decidedly inferior (in terms of multi-db support I mean). I think it only supported using named AOLserver databae pools (rather than something like my -dbn switch), which doesn't mesh nicely with the rest of the (Open)ACS db_* API at all. But that was a long time ago, probably back in 2001 when I originally made this multi-db stuff work on ACS 4.2, so I'll take a look.

Also, I don't recall at all how nstcl handled the multiple database driver issue. (Which is a bigger issue! Making the -dbn switch type thing work is really somewhat trivial.) So good suggestion, I should definitely take a look at it for at least that reason.

Collapse
8: nstcl vs. OpenACS db_* api (response to 5)
Posted by Andrew Piskorski on 04/10/03 01:30 AM
John, I looked at nstcl a bit, but AFAIK, no ideas of immediate use to the OpenACS multi-db db_* api work there. I did the straightforward thing - switch statements.

One, with the issue of supporting multiple drivers, nstcl is just completely different. Not bad (as far as I saw), just very different.

Two, nstcl still only allows you to specify a pool name to access a different (non-default) database, there is nothing like my -dbn switch. nstcl offers only, "To specify an alternate pool, other than the default, preface the statement_name with the name of the pool, followed by a colon.", e.g. "db_dml oracle:old_customers $sql".

In the OpenACS db_* api, if you call a db_ function while you already have a db handle in use (e.g., inside a db_foreach loop), the api will automagically open a second handle (and a second transaction) from a different database pool, if you have any pools left. (See the use of db_nth_pool_name in db_with_handle.) Whether or not this is a good feature or not is a different question (and one that I'm not entirely sure about), but AFAIK the db_* api has always done it that way; it certainly has since before ACS 4.2, anyway.

For the default database, I'm not really sure whether nstcl supports automagic multiple handles at once like that or not - I strongly suspect it does not (see: "::nstcl::database::api_get_dbhandle"). But nstcl definitely doesn't support it for any other database. I don't know if Cleverly did that on purpose, or by accident. Since there are no notes or comments about it, and his nstcl package seems otherwise well documented, I suspect the latter.

Collapse
9: Re: nstcl vs. OpenACS db_* api (response to 8)
Posted by Michael A. Cleverly on 04/10/03 06:15 AM
nstcl does support "automagic multiple handles" at once.

The only reason OpenACS has multiple pools (all to the same database, traditionally being main, subquery, and log) is due to the restriction in AOLserver that you have to allocate as many handles as you want upfront.  Once you have allocated handle(s) you have to release them all before you can get more from the same pool.

I didn't feel any need to impose this restriction with nstcl.  Rather, you can define a "pool" to have multiple connections and freely use them all up.  In OpenACS if you have the standard 3 pools you can't nest more four-levels of db_foreach statements.

In nstcl, for any given pool (default or otherwise) you can have as many db_foreach statements nested as you have defined connections. With nstcl there really is no need to define multiple pools for any reason other than to connect to multiple databases, be they two different Oracle databases (production & development perhaps) or two completely different types of databases (Oracle & Postgres).

In this sense, a single nstcl "pool" encompases the main, subquery, and log pools of AOLserver.  As for -dbn syntax; if that's the route OpenACS takes I'll likely add support to nstcl for it.  Lately I've found myself using nstcl more for work utterly unrelated to (Open)ACS (and I regret I haven't had time to track and participate in OpenACS development), but since I'll be converting a site from ACS 4.2 to OpenACS shortly, it'll be time to catch up on things.

Collapse
10: Re: nstcl vs. OpenACS db_* api (response to 9)
Posted by Andrew Piskorski on 04/10/03 08:22 PM
Ah, interesting, thanks for filling us in, Michael!

Hm, so the current workings of nstcl depend on the fact that all the drivers nstcl currently supports do not share AOLserver's "each thread must allocate all handles from a single pool at one time" limitation? So if down the road nstcl decided to support db drivers based on the AOLserver C code, you'd want to add in something like the db_nth_pool_name type stuff that OpenACS has?

On eventually adding the -dbn switch to nstcl, cool! I've yet to receive much of any feedback on it good or bad, but presumably others will gradually start using it. :)

Also, for the OpenACS db_* API, the -dbn switch definitely was the right way to go, but I don't see any harm in also adding your nstcl style "use this pool and no other" syntax, if people ask for it.