Forum OpenACS Q&A: Just the database API

Posted by Alfred Werner on 03/11/03 11:04 PM

Two questions actually ...

Has anyone ever documented the various database API's available? By various I mean

AOLserver API (which is the most raw)
ACS TCL API rev1
ACS TCL API rev2 (I think around 3.0)
OpenACS TCL API

I just ask because there have been a few times where I have installed an entire ACS instance just to be able to use the database API which is sort of overkill, because then you have a gazillion tables and stored procs etc that you can't avoid because of all the mutual dependencies.

So having said that, what is the minimum install of OpenACS that allows you to use a nicer abstracted interface to the database (db_foreach for example) without the whole kit and kaboodle of an OpenACS install?

I suppose a parallel question exists for form handling, for page contracts and many other 'core' features, but I think focusing the answer on the database layer will probably answer this for a larger set of subsystems.

2: Re: Just the database API (response to 1)

Posted by Don Baccus on 03/11/03 11:52 PM

What happens if you just take the database procs from acs-tcl/tcl and source them? There may be pieces you need in acs-bootstrap-installer/tcl, too. You only want to load the non-specific tcl file(s) and either the *-postgresql.tcl or *-oracle.tcl files, not both!

The template system can be ripped out standalone, that includes the form builder. I know at least one person did this to make it available for OpenACS 3.x. You might have to rip out some hooks but if so they'd be very minimal.

If you wanted to bundle these up as standalone, tested tarballs we could probably host them here. They certainly are useful for AOLserver users who don't want to develop an OpenACS site per se.

3: Re: Just the database API (response to 2)

Posted by Alfred Werner on 03/12/03 12:21 AM

Tks Don - I'll give it a shot. If it looks like it would be a good package, generally useful - I'll be in touch :)

Sort of leads to another issue that always tickles (pun intended) the back of my brain - what layer should features be included in? To whit - OpenACS is an open-source project built on AOLserver - which is open-source project built on/in/around TCL which is an open-source project (which is written largely in C in which case you should use SWIG and make the modules available in ALL scripting languages, but I digress)..

To my thinking any feature should be pushed as far DOWN the stack as possible (or practical) to allow it to have the widest reach/appeal and 'bazaar' qualities that will lead to its continual improvement.

So should the 'improved' database API make its way into AOLserver as an optional package someday? Is the distinction that the AOLserver modules are all written in C and OpenACS is a layer of TCL native code on top? That's what appears to be the case to my eye.

4: Re: Just the database API (response to 1)

Posted by Andrew Piskorski on 03/12/03 02:47 AM

Alfred, I have taken the ACS 4.2 database API, ad_page_contract, and various other useful Tcl things and made them work stand-alone in AOLserver. (For the purposes of this discussion, ACS 4.2 and OpenACS 4.6 should be pretty much the same.) Doing so wasn't all that hard, but it definitely wasn't trivial. For example, I remember having to make a few silly subtle little tweaks (ifdef'd for with ACS vs. without) which my testing showed were necessary, but which I never really did understand why.

I could send you an old tarball of that code if you want. However, it's old ACS 4.2 stuff, and somewhat hackish. I have not yet tried doing the same with recent OpenACS code, but based on my past experience I'm sure it could be made to work. (On snafu might be dealing with the query dispatcher, however.)

At any rate, I too would like to see the current OpenACS Tcl code be refactored for easy, drop-in use in non-OpenACS AOLserver environments, but so far I haven't done anything about it. It's rather far down on my personal list of priorities, currently. But definitely worth doing.

5: Re: Just the database API (response to 1)

Posted by Andrew Piskorski on 03/12/03 02:56 AM

Also, if by "around 3.0" you mean ACS 3.0, there was never any "ACS database Tcl API" per-se as far as I can recall. Just the ns_db API provided by AOLserver plus a bunch of useful ACS helper procs - aids to using the ns_db api, not wrappers or replacements.

I think the AOLserver people are still discussing what to do about hosting non C code modules and applications.

But regardless, the OpenACS db API should always live and be maintained in the OpenACS CVS, since OpenACS is almost certainly going to stay the most active user and developer of the code for a long, long time. What would be very nice is for it and other OpenACS Tcl utility code to be very easily packaged up and used in other Tcl environments. (Not necessarily just AOLserver either, although that's the first and most biggest step.) Which is a much different issue from whose CVS server the code lives in.

6: Re: Just the database API (response to 1)

Posted by Andrew Piskorski on 03/12/03 02:59 AM

"most biggest", blagh, the perils of computerized editing of text are now made plain...

7: Re: Just the database API (response to 6)

Posted by Alfred Werner on 03/12/03 03:58 AM

Haha - I know what you mean :)

I suppose memory plays tricks - there was a point - perhaps on the 4.0 boundary where the database kit in ACS got 'nicer' maybe it was that I finally got it because I wasn't in a rush to get projects out the door and read the docs more thoroughly. I do recall it getting easier to nest queries and deal with database handles (er, pools) at some point.

As far as helper routines - other than [set_variables_after_query] there wasn't too much to write home about .. haha.

I'd love to get a tarball of what you ripped out of 4.6 - I just built two virgin machines for a project I'm gonna be implementing for my own fun/edification (and a dotLRN site for a friend's school) so now's the time to experiment before I have too much other code in the way.
(Any idea how to get files > 2GB on a Linux 2.4 kernel?)

Small unmarked procs in a brown paper bag if you please ;) Leave it behind the statue in the town square at midnight.

8: Re: Just the database API (response to 1)

Posted by Vinod Kurup on 03/12/03 04:59 AM

Hi Alfred,

Actually, I too remember there being a db_* API in ACS 3.4.x and the docs mention it too (https://openacs.org/doc/openacs-4/db-api.html). Jonathan Ellis ported the ACS 4.2 db api to openacs 3.x and created a patch.

There might be some useful stuff in the (not so) new-file-storage area of the old site (http://sdm.openacs.org/new-file-storage). I think some of Andrew's work is there.

9: Re: Just the database API (response to 1)

Posted by Alfred Werner on 03/12/03 05:45 AM

Thanks Vinod, that answers a bunch of questions (like does playing rugby in your twenties ruin your short term memory in your thirties - apparently not too badly!).

The patch looks basically like a standalone solution to what I was asking about - I think that side by side with the 10-database-procs.tcl and database-init.tcl might answer entirely what I was looking for.

I think based on what Andrew said earlier that there might be a few niggling dependencies, but it doesn't seem as odious now that I see the code in question.

10: Re: Just the database API (response to 1)

Posted by Malte Sussdorff on 03/12/03 12:13 PM

Out of curiosity: Would it make sense to port the DB-API to C and include it as a module within AOLserver ?

Would there be a speed gain ?

Would it make sense, as changes will be harder to implement?

Is the DB-API a moving target at the moment ?

11: Re: Just the database API (response to 1)

Posted by Don Baccus on 03/12/03 04:14 PM

Writing it in C would just increase the maintenance headache and make it harder for people to customize things to please themselves. The API does very little except call the DB and create variables in the caller's space. I'm sure the DB overhead swamps all.

There's no real advantage to having it as a module, a set of Tcl procs that are sourced at start-up are just fine.

12: Re: Just the database API (response to 1)

Posted by Alfred Werner on 03/12/03 05:50 PM

I concur that a 'C' implementation would probably lead to a more difficult to maintain API - it is literally just convenience wrappers to make the accesses a little easier and the code a little tighter.

What I'm looking for is a Goldilocks release - when AOLserver is too raw, OpenACS is too cooked, but a nice database API and forms abstraction would be 'just right'...

I think with the leads I got from this thread I can start putting something together in that regard.

13: Re: Just the database API (response to 8)

Posted by Andrew Piskorski on 03/12/03 09:23 PM

Oh, the first release of what we now think of as the OpenACS 4.x db_* api was in fact in ACS 3.3, 3.4, or something like that. But definitely not in 3.0! At least up through ACS 3.2 did not have it. I think post ACS 3.2 was when some of the new 4.x style stuff started going in - db api, templating, maybe request processor, etc. Of course, that was a long time ago now, and I don't really remember for sure. :)

14: Re: Just the database API (response to 7)

Posted by Andrew Piskorski on 03/12/03 09:41 PM

I just emailed Alfred my old ACS 4.2 based code. If anyone else wants
it, let me know and I'll see about sticking it in file storage or
something.

15: Re: Just the database API (response to 11)

Posted by Andrew Piskorski on 03/12/03 09:49 PM

I think Jeff Davis was talking once on IRC about the possibility of re-writing the db_api to eliminate the use of ns_set in the ns_db API, in order to remove a lot of the string copying and Tcl string/object conversion overhead. However, I don't remember if he was just speculating wildly, or if he had a real-world case where the extra string processing overhead was significant and worth the trouble to avoid.

Personally I'd be surprised if the string overhead was significant enough to justify the hassle of using some other than the standard ns_db API. But, anyone curious should probably ask Jeff...

16: Re: Just the database API (response to 15)

Posted by Jeff Davis on 03/12/03 10:26 PM

It mattered but this was for a page that was constructed from about 2k rows (aggregation of company financials where each table element came from one or more rows). In most cases this would not make that much of a difference. I did some testing and it looked like by going directly to tcl variables from the db you might cut the time on a query like that by 25% or so. I makes sense that it would matter for the some things for the same reason that whenever you read things about expr it tells you to use { }'s.

It's not that common a use case though.

The other thing was that tcl's handling of floats and ints etc is problematic. This turned up when doing these financial calculations since you would have round numbers going into a formula and get the wrong result like this:

% set x 1
1
% set y 3
3
% expr $x / $y
0

I also thought about making it so that floats were returned with .0 tacked on to avoid this sort of problem.

17: Re: Just the database API (response to 1)

Posted by Don Baccus on 03/13/03 03:19 AM

Strings and lists have also been sped up significantly in 8.4.1, don't know how that

18: Re: Just the database API (response to 1)

Posted by James Thornton on 03/13/03 05:52 AM

I traced through the procs and pulled out the needed ones for the DB API -- I'll package them up and make them available as a tarball soon...

19: Re: Just the database API (response to 12)

Posted by Jamie Rasmussen on 03/13/03 01:23 PM

I recently learned about "MiniACS". http://miniacs.sourceforge.net/ It was developed by zjednoczenie.com for use on the sites that they build. More info at http://miniacs.zjednoczenie.com/snapshot.html

It needs a customized AOLserver that Tomasz Kosiak announced to the mailing list a few days ago - see the "AOLserver Improvement Proposal" thread. (Mostly vserver-related stuff.)
I haven't used it and Tomasz has told me that they don't have the resources to support it.

20: Re: Just the database API (response to 1)

Posted by Jonathan Ellis on 03/13/03 03:02 PM

interesting, but if it doesn't even provide user registration it's pretty useless. :0

I'm guessing that's what "does not enforce particular SQL data model like ACS does" means, anyway.

and I'm not really seeing the benefit of hardcoding the db user and other config parameters into nsd...

21: Re: Just the database API (response to 1)

Posted by Don Baccus on 03/13/03 07:49 PM

Sounds like they have taken much of the db_* api stuff and bundled it, though, might be worth taking a look at.

They do seem to miss the point that the datamodel is really the key to things.