head	1.2;
access;
symbols;
locks; strict;
comment	@# @;


1.2
date	2003.05.06.15.16.34;	author rse;	state Exp;
branches;
next	1.1;

1.1
date	2003.05.05.18.21.05;	author rse;	state Exp;
branches;
next	;


desc
@@


1.2
log
@flush pending changes from today's schema-full-work-off
@
text
@
Database
--------

The AS database is based on the PostgreSQL RDBMS. It consists of a
PostgreSQL database named "as" and owned by user "as".

Implementation
--------------

The implementation consists mainly of two parts:

o as_db.sql: the PL/pgSQL based database schema definition plus
  accompaning stored procedures for the RDBMS side.

o as_db.pm: the Perl based database schema abstraction layer
  module which maps the RDBMS/SQL layer into a Perl OO API.

o as_db.pl: the Perl based database creating/destruction 
  command-line utility.

Database Schema Components
--------------------------

o system tables:  
  - as_config
  - as_oid
  - as_epoch
  - as_log

o object tables:
  - as_holiday
  - as_locality
  - as_user
  - as_group
  - as_account
  - as_event

o relationship tables:
  - as_rel_struct
  - as_rel_access
  - as_rel_effective

Schema Intrinsics
-----------------

o Object Representation and Tables
  
  Each object is stored as a row in the object table corresponding to
  the object class. The attributes of an object are directly represented
  by table columns/fields.

o Object Identification and OID

  Each object in AS has a system wide unique and uniform object
  identification (OID) number. This has two reasons: 1. the primary
  key in the table always and uniformly can be formed out of the OID
  (plus Epoch, see below) and 2. the object relationships (see below)
  can easily and uniformly be represented by foreign keys consisting of
  OIDs, independent of the actual object class.
  
o Object History and Epoch

  While attributes of an object are changed over time, a history of the
  object is created. This history is tracked by global system Epoch
  numbers. Each row in an object table has two columns: epoch start and
  epoch end. Together with the OID column (see above) this allows exact
  selection of an object from a certain epoch in the object tables,
  i.e., the key <oid,epoch_start> always gives exactly one row). Each
  time an object in any object table is changed, instead of changing
  columns in place, a new epoch is created (by incrementing the epoch
  number), the current row is duplicated and the old row closed by
  setting its epoch end to the new epoch start.

o Object Relationships

  <<see orm.txt>>

Relationships
-------------

o as_rel_struct:

  as_locality       has-parent         as_locality
  as_holiday        is-valid-for       as_locality
  as_user           is-located-in      as_locality
  as_account        has-parent         as_account
  as_account        diverts-to         as_account
  as_user           is-member-of       as_group
  as_group          is-member-of       as_group
  as_user           is-proxy-for       as_user
  as_user           reports-to         as_user

@


1.1
log
@first cut for internal DB documentation
@
text
@d79 15
@