Forum OpenACS Q&A: Which database API should we use?

Collapse
1: Which database API should we use?
Posted by Daryl Biberdorf on 01/15/02 04:06 PM

I've been doing some work on the OACS 4 bootstrap installer, after being away from AOLserver in any form for a year. As I got into it, I ran into immediate problems simply figuring out which database API to use. There's the raw ns_db and ns_ora (the latter being Oracle specific) stuff, but then there's lots of db_ functions littered about in the code. The latter abstract the underlying AOLserver API, but I don't really know what's available.

What API should I use in general? Is there a canonical list of these functions, with arguments and return data? (Yes, I get pretty frustrated with the information at AOLserver.com, since the docs are hard to read and, most of the time, no information about what, if anything, the functions return is given.

Collapse
2: Response to Which database API should we use? (response to 1)
Posted by Andrew Grumet on 01/15/02 04:37 PM
Most of my experience is with Classic ACS4, but assuming this wasn't massively changed in OACS4, in general you should use the db_ calls (aka "database access api"). Truth/beauty debates aside, if you don't know what you're doing, the ns_ calls can lead to subtle and hard to track down bugs. The reason is that the database access api performs its own internal database handle accounting and most of ACS relies on it. The ns_ calls require that you allocate handles manually, so you should only use it if you can be sure that no other db_* calls can grab handles from the same pool.

The ArsDigita docs for the db api are here: http://developer.arsdigita.com/doc/db-api-detailed.html. Perhaps someone can post the OACS URL for this (if it exists). If you want to perform some operation that is not covered by the api, you can always use db_with_handle.

Collapse
3: Response to Which database API should we use? (response to 1)
Posted by Daryl Biberdorf on 01/15/02 04:51 PM

Thanks for the pointer. This kind of documentation is what I was looking for, even if the discussion of bind variables is a bit misleading. (One uses Oracle bind variables for performance and scalability, not a strange kind of security.) In any case, though, the list of functions is very helpful.

In the OACS 4 environment, database abstraction is pretty critical, since multiple databases are supported. I assumed the db_ calls were preferred, but I didn't relish the thought of digging through to figure them all out.

Collapse
4: Response to Which database API should we use? (response to 1)
Posted by Don Baccus on 01/15/02 06:09 PM
The bind variables do indeed add to security, and that was the motivation that led aD to take this approach (I'm not sure if they were aware of the scalability side-effects, to be honest).

In fact, the bind variable emulation we added to the Postgres driver also adds to security.  Considerably, actually.  Proper use of ad_page_contract does, too.

Collapse
5: Response to Which database API should we use? (response to 1)
Posted by Don Baccus on 01/15/02 06:11 PM
Oh ... in further answer to your question, *please* use the db_* API in any code that runs after bootstrap.

Don't use the inferior template::query API, either.  That's scheduled to disappear in the future.  In particular the parameter parser for all of the template and form stuff doesn't do any meaningful error checking.  We'll keep the non-db template stuff, of course, but the smarter db_* code is the right stuff to use.