TIP:    52
Title:    Create abstract layer over RDBMS system catalog
Version: $Revision: 1.00$
Authors: Alfred Werner <alfred hat thunderstick dawt com>
State:    Proposed
Type:    Project
OpenACS-Version: 5.03
Created:    Friday, 27 February  2004
Discussions To:    http://openacs.org/forums/message-post?forum_id=115570

Abstract

The OpenACS kernel provides a mechanism for storing extended attributes of system objects. Specifically, acs_attributes* and acs_datatypes are intended to map into the physical storage layer of the underlying RDBMS. Currently there is no mechanism for interrogating the underlying system catalog to run audits to compare coverage of the OpenACS catalog against the underlying RDBMS catalog. Providing this abstraction will make the 'data soup' effort much easier and consistent.

Proposal

I would like to create three specific VIEWS and add them to the OpenACS kernel. acs_tables, acs_views, and acs_constraints. I don't think indexes are necessary from a 'logical design' perspective. Omitting it would be in keeping with current practice. A large portion of the work already exists thanks to the schema-browser package.

Not in Scope
There are additional items in both the Oracle and Postgres catalogs - including data type definitions and other items that probably should be considered in the future where equivalencies exist.

Malte - the specific application I am looking at is to allow me the ability to query the rdbms namespace to let me store uploaded csv files (or fixed-width and xml eventually) as tables in the underlying database. I have also been looking at the datasoup discussions and feel that there is no clean way to map from the OpenACS internal objects and datatypes into the underlying tables. One could construct database unique queries to do this, but I think a set of views would make things a lot easier and reusable. Currently the kernel tables let you view everything in the system EXCEPT the actual RDBMS catalog.

Since you can't really ADD a row even if the view ends up as updateable and expect the tables to appear, I don't really see any harm in creating these.

The purpose is NOT for monitoring, but rather to complete the internal representation all the way from CR->object->table/column (for example).

Approved.

Approved

One of the things I would like to see come out of this
is something we could use for making upgrade scripts a
little safer (like a clean way to check if a constraint
or index exists already).