OpenACS 4.0

OpenACS 4.0 Guidelines

Simple Issues

We faced most of these issues at some point during the process of developing OpenACS 3.x. It's now time to standardize these and do everything correctly in 4.x!

Sequences

Use Dan Wickstrom's sequence view hack. The idea is to structure a view around a sequence so that the foo_seq.nextval construct can work.

create sequence user_id_seq;
create view user_id_sequence as select nextval('user_id_seq') as nextval;

Empty Strings

Be stricter, not Oracle'ish. Default all varchars to empty string not null.

create table foo (
       some_text varchar(100) default '' not null
);

Sysdate

No using sysdate in the data model. This is pretty important given that we don't really want to have the basic data-model depend on a PL/SQL procedure. In general, avoid user-defined functions in the data model. Otherwise, the sysdate() function works fine.

Case/Decode, NVL/Coalesce

These issues are solved, but let's just remind everyone how they should be solved

In Oracle, a decode statement looks like this:

select sum(decode(available_p,'t', 1,0)) as n_available from foo;

In PG, this looks like this:

select sum(case when available_p='t' then 1 else 0 end) as n_available from foo;

In Oracle, an NVL statement looks like:

select user_id, nvl(screen_name, '(no screen name)') from users;

In PG, it looks like:

select user_id, coalesce(screen_name, '(no screen name)') from users;

Note that coalesce is more powerful than NVL in that you can list any number of arguments, and the first non-null one will be returned. With NVL, you have to nest a number of NVL statements to achieve the same effect.

Outer joins

conversion examples by Don.

Dispatching New Queries

This is a pretty involved topic which will be handled in a separate document. We note one important thing: we can easily identify which page we are in using [ns_conn url] and the Tcl procedure we are in using ns_info. This means that the unique tagging scheme used in ACS 4.0 will work perfectly fine to replace simple SQL queries using a different source.

Data Model Changes

We need to standardize ways in which a data model is modified from Oracle to Postgres (7.1 or later):

Char(1) becomes boolean
date becomes timestamp
lztext gone, we use text
varchar(4000) becomes text
varchar < 1000 remains varchar
clob becomes text
blobs are moved to filesystem level persistent object stuff (content repository).
move to Postgres object extensions (declare indexes and triggers on all child tables). This may still be somewhat controversial. We will discuss this on the bboards, right here.
comment on is supported, which means all the commenting can be kept precisely as is.

PL/pgSQL

function names when packages are involved: package__function (two underbars)
16 parameters enough? We will see.
default params: function overloading for common cases.

Things for PG/GB to do

up the identifier length to 64.
up the # of PL/pgSQL parameters to 32.

Questions for aD

what's with the disabled constraints, followed by index creation, followed by constraint reenablement?
what is the security_inherit_p attribute, and why no explicit attribute created for it? Answer: that is used by the AFS-like permission scheme, where a security context is inherited or not.

Random Issues

Email done within Oracle?? No way - a new notifications module
acs_object_context_index may not be necessary? Create views on top of pg_inherits?
Adding a content type that is a persistent object in the FS, that other systems rely on.

Immediate Next Steps

Who's available to do work?
Putting together a full doc of all stuff discussed by Don and Ben done!
Pushing PG and GB on a couple of issues this cannot happen for 7.1. Maybe 7.2
Putting a CVS solution/repository begun on openacs.org
DB layer - fetching queries from somewhere else initial design in progress
DB bind variables work in progress
APM
Porting Kernel
a Tcl API for storing large files

dhogaza@pacifier.com / ben@mit.edu