Forum OpenACS Development: Re: OACS 6 and beyond (database abstraction layer)

FWIW. I have been using a database abstraction layer for a couple of years now (written in XOTcl). It is not perfect by any means but it does enable you to write database neutral code.
  • How do I define the data model of my application?
    DB_Class Blog_Item -lmap mk_attribute {
        {String body}
        {Timestamptz entry_date}
        {Boolean allow_comments_p -isNullable no -default 'f'}
        {Integer cnt_comments -isNullable no -default 0}
        {Timestamptz last_comment -isNullable yes}
    } -lmap mk_like {
    } -lmap mk_index {
        {Index entry_date}
  • How do I store a new data object?
    set bi [Blog_Item new -mixin ::db::Object]
    ${bi} set id $id
    ${bi} set title $subject
    ${bi} set body $body
    ${bi} set shared_p $shared_p
    ${bi} set allow_comments_p $allow_comments_p
    ${bi} do self-insert
  • How do I retrieve data?
    set blogdata [db::Set new -type ::Blog_Item -order "entry_date desc"]
    ${blogdata} load
    foreach blogItem [${blogdata} set result] {
        # do something with the data, for example:
        ns_write [$blogItem set title]
  • What else does it do?
    • Partitioning -- See
    • Scoping -- You write your code once but you can apply it to a different scope every time. That means, you no longer need to have the same database table for forum messages in your installation, i.e. each class/community/subsite/user can have their own tables which of course improves performance.
    • TCL-level triggers, onInsertSync, onDeleteSync, onUpdateSync -- For writing aggregators, for example, maintaining forum summaries is a form of aggregation. Aggregators in this abstraction layer are maintained/updated upon insert/update so that you no longer have to do a "select * from forum_messages order by creation_date limit 10"
    • You can join XOTcl defined data classes with database tables which have not been defined as data classes -- For example, you can join against the OpenACS Users table eventhough it has not been defined as an XOTcl class (see above). This is done easily by replacing "-type" with "-from"
There are much more that can be said here (how easy caching would be, etc). Eventhough it has served me well, there is a downside. The downside is that you would still need to invest time bringing it up to the needs of the corporate users in the community as well as writing the Oracle Translation/Interface Driver.

Being an infrastructure package, though, means that any investment that would go into it (either the package itself or related efforts to bring developers/packages up to speed) would only need to be done once.

The reason I have been silent about this is because I'm already pursuing full persistence (i.e. eliminate the need for foreign keys which they now exist in the definition of the data model classes and replace them with conceptual classes and relationships between them -- the persistence mechanism decides how to organize attributes/data into tables and it does it in the same way an expert developer would).

Posted by Nick Carroll on

You have been doing some interesting work there! When do you think you will be finished with this? Is it something that will be added to the openacs repository? If so, then it would be great if we can coordinate this with the planned 6.0 release.


Posted by Don Baccus on
How does this database neutral persistence level deal with the difference between CONNECT BY tree queries and datamodels vs. tree_sortkey datamodels and their associated queries?

The two aren't comparable.

Now 10g's relaxation of restrictions on CONNECT BY queries actually helps a bit (because you can join tables against CONNECT BY subqueries) ... I can actually visualize being able to build similar queries for both schemes via subqueries and joins against them ... but they wouldn't scale well. Scaling tree-building queries using the two models requires different strategies.

We've talked about tcl-based table declarations of the sort Neophytos talks about in his xotcl stuff.

The problem is that we need to clean up the somewhat conflicting attribute systems in the content repository and that for base objects.

Lots of datamodel clean-up.


This grunt-level work would need doing for both databases, and of course would need to be EXTREMELY well-tested in both environments. With realistic data representative of real sites.

The need to provide an upgrade path is a (NECESSARY) millstone around the neck of those that would like to clean-up the datamodel as a first step towards making a much cleaner and easier development environment for programmers.

The need to provide upgrade paths for two databases is a double-weight millstone. Worse, actually, because as is pointed out, I've been the only person committed to keeping Oracle supported.

In short, the higher-level API path (exposing as much as possible through Tcl API as we've been increasingly doing the past couple years making it easy to write db-independent code, or an Xotcl approach such as Neophytos favors) helps the easy problem.

Unfortunately, if that were the only problem, Oracle wouldn't be a big deal.

But fundamental clean-up of the datamodel combined with upgrade scripts is a much harder problem, and eye-candy help such as higher level APIs for the easy part of the problem isn't all that beneficial.


On CONNECT BY: Rails implements nested sets and trees on top of either of the three databases -- Oracle, PG, MySQL. Maybe there are a few lessons that could be learnt from it.

There does seem to be some performance impact in doing it the way Rails does, but having Tcl inside of AOLServer may just as well neutralize it, don't you think?

Well, we support trees on top of two databases, that's not the issue. The issue is simply that this particular need is one which drives db query differences.

If you're suggesting that Rails does the nested tree support in the Ruby layer, I'd humbly suggest that this is likely to be terribly inefficient if sizable trees are involved.

I should also point out that the forums package's oracle version uses tree_sortkeys rather than CONNECT BY queries.

And that PG in the semi-near future should support SQL's recursive queries that would allow an approach similar to CONNECT BY.

Hi Guys,

I am not an active participant nor a frequent user of openACS. But after going thorught this long forum postings, I have a question.

Can someone tellme what exactly is the reasons of dropping the oracle support? It would be good if we know exactly the work/effort required then we can collective divide the work and start supporting openACS with oracle.

1. Is it because of lack of Oracle users in the community who will test oracle code and give green signal to it?

2. Since Don is the only person doing this and he is not interested in doing and no one is there to take that responsiblity. So we are dropping oracle support?

3. Or is it because of licensing issues of oracle?

What exactly are the reasons of dropping oracle support? We know that upto now openACS was suppporting both databases and most of the websites are running on openACS having backend both oracle and postgres. Can someone (Malte, Don or Dave) please actually list down the things that is required to support oracle with openACS 6

I see most of them had shown anger/disrespct to other users but again its not directly from the heart but its more about the frustration of not having oracle support.

Why dont we actually know the things that is required in order to continuing support oracle?

I will be waiting for the answers then..


Hi Andre,

1. Yes Oracle maybe dropped in the future if no one will step up in testing it.

2. Currently its only Don who does the testing, its difficult and not a sustainable model as Don is not really doing Oracle development

3. Its not a licensing issue, its a manpower issue.

I hope a member for the OCT can confirm my answers.

Well, since this discussion has started, people have stepped forward offering server support and the like. So perhaps some of the resource issues will be resolved. We shall see.