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 {
  
      ::content::Object
      ::content::Title
      ::sharing::Flag_with_Start_Date
  
  } -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 http://www.oracle.com/technology/products/oracle9i/datasheets/partitioning.html
  • 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).

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.

WITH LOTS OF UPGRADE SCRIPTS.

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.

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 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.