Index: ossp-pkg/as/as-db/bres/bres-history.sql
RCS File: /v/ossp/cvs/ossp-pkg/as/as-db/bres/bres-history.sql,v
rcsdiff -q -kk '-r1.10' '-r1.11' -u '/v/ossp/cvs/ossp-pkg/as/as-db/bres/bres-history.sql,v' 2>/dev/null
--- bres-history.sql	2003/06/08 14:50:02	1.10
+++ bres-history.sql	2003/06/08 15:07:47	1.11
@@ -1,11 +1,42 @@
--- TODO: delete entries from as_hs_ut for expiring history: does it still not correctly
--- and especially should only allow the oldest entry to be deleted or implicitly
--- delete also all older entries.
-
--- ##
--- ##  Create Database Schema
--- ##
+--
+--  OSSP as -- Accounting System
+--  Copyright (c) 2002-2003 Cable & Wireless Deutschland <http://www.cw.com/de/>
+--  Copyright (c) 2002-2003 Ralf S. Engelschall <rse@engelschall.com>
+--
+--  This file is part of OSSP as, an accounting system which can be
+--  found at http://www.ossp.org/pkg/tool/as/
+--
+--  This program is free software; you can redistribute it and/or
+--  modify it under the terms of the GNU General Public License
+--  as published by the Free Software Foundation; either version
+--  2.0 of the License, or (at your option) any later version.
+--
+--  This program is distributed in the hope that it will be useful,
+--  but WITHOUT ANY WARRANTY; without even the implied warranty of
+--  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+--  General Public License for more details.
+--
+--  You should have received a copy of the GNU General Public License
+--  along with this program; if not, write to the Free Software
+--  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
+--  USA, or contact The OSSP Project <ossp@ossp.org>.
+--
+--  bres-history.sql -- History Tracking Basic Research
+--
+
+--  TODO:
+--  - delete entries from as_hs_ut for expiring history: does it still not correctly
+--    and especially should only allow the oldest entry to be deleted or implicitly
+--    delete also all older entries.
+
+--  ##  _____________________________________________________________________
+--  ##
+--  ##  Database Schema
+--  ##  _____________________________________________________________________
+--  ##
 
+--  cascadingly drop existing schema
+--  (just ignore errors on console)
 DROP SEQUENCE as_hs_st_seq CASCADE;
 DROP TABLE as_hs_st CASCADE;
 DROP TABLE as_hs_ut CASCADE;
@@ -20,8 +51,8 @@
 CREATE TABLE as_hs_st (
     st_oid         BIGINT
                    NOT NULL,
-                   -- REFERENCES as_oid (id_oid)
-                   -- MATCH FULL DEFERRABLE,
+                   -- REFERENCES as_oid (id_oid) FIXME
+                   -- MATCH FULL DEFERRABLE,     FIXME
                    -- object identifier
                    -- [1] (rw)
     st_hid         BIGINT
@@ -40,8 +71,8 @@
 CREATE TABLE as_hs_ut (
     ut_oid         BIGINT
                    NOT NULL,
-                   -- REFERENCES as_oid (id_oid)
-                   -- MATCH FULL DEFERRABLE,
+                   -- REFERENCES as_oid (id_oid) FIXME
+                   -- MATCH FULL DEFERRABLE,     FIXME
                    -- object identifier
                    -- [1] (rw)
     ut_beg         TIMESTAMP (4) WITH TIME ZONE
@@ -66,8 +97,8 @@
 CREATE TABLE as_hs_ut_no (
     ut_oid         BIGINT
                    NOT NULL,
-                   -- REFERENCES as_oid (id_oid)
-                   -- MATCH FULL DEFERRABLE,
+                   -- REFERENCES as_oid (id_oid) FIXME
+                   -- MATCH FULL DEFERRABLE,     FIXME
                    -- object identifier
                    -- [1] (rw)
     ut_beg         TIMESTAMP (4) WITH TIME ZONE
@@ -88,8 +119,8 @@
                    -- [42] (rw)
     ut_hid_max     BIGINT
                    NOT NULL
-                   -- REFERENCES as_hs_st (st_hid)
-                   -- MATCH FULL DEFERRABLE
+                   -- REFERENCES as_hs_st (st_hid) FIXME
+                   -- MATCH FULL DEFERRABLE        FIXME
                    -- user time history identifier (maximum value until which this entry is valid)
                    -- [42] (rw)
 );
@@ -114,13 +145,15 @@
                    -- [42] (rw)
 );
 
--- ##
--- ##  Update as_hs_ut_no table for as_hs_ut change on particular OID
--- ##
-
---  Update function
-DROP FUNCTION ut_update(BIGINT, BIGINT) CASCADE;
-CREATE FUNCTION ut_update(BIGINT, BIGINT) 
+--  ##  _____________________________________________________________________
+--  ##
+--  ##  Automated Update of as_hs_ut_no Table
+--  ##  _____________________________________________________________________
+--  ##
+
+--  as_hs_ut update function
+DROP FUNCTION as_hs_ut_update(BIGINT, BIGINT) CASCADE;
+CREATE FUNCTION as_hs_ut_update(BIGINT, BIGINT) 
 RETURNS VOID LANGUAGE 'plpgsql' STABLE AS '
 DECLARE
     oid BIGINT;
@@ -195,35 +228,36 @@
 END;
 ';
 
---  Trigger Stub Function
+--  as_hs_ut trigger stub function
 DROP FUNCTION ut_trigger() CASCADE;
 CREATE FUNCTION ut_trigger() 
 RETURNS TRIGGER LANGUAGE 'plpgsql' STABLE AS '
 BEGIN
     IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
         PERFORM ut_update(NEW.ut_oid, NEW.ut_hid);
-    ELSE
-        IF TG_OP = ''DELETE'' THEN
-            PERFORM ut_update(OLD.ut_oid, OLD.ut_hid);
-        END IF;
+    ELSIF TG_OP = ''DELETE'' THEN
+        PERFORM ut_update(OLD.ut_oid, OLD.ut_hid);
     END IF;
     RETURN NULL;
 END;
 ';
 
---  Global Trigger
--- DROP TRIGGER ut_trigger ON as_hs_ut CASCADE;
+--  as_hs_ut trigger
+--DROP TRIGGER ut_trigger ON as_hs_ut CASCADE;
 CREATE TRIGGER ut_trigger
     AFTER INSERT OR UPDATE OR DELETE
     ON as_hs_ut FOR EACH ROW
     EXECUTE PROCEDURE ut_trigger ();
 
--- ##
--- ##   Make History Tracking
--- ##   hid := as_hs_make([oid[, ut_beg, ut_end[, st_beg]]])
--- ##
+--  ##  _____________________________________________________________________
+--  ##
+--  ##  Convinience History Tracking Constructor/Destructor Functions
+--  ##  hid := as_hs_make([oid[, ut_beg, ut_end[, st_beg]]]);
+--  ##  as_hs_delete(oid, st_hid);
+--  ##  _____________________________________________________________________
+--  ##
 
--- full featured implementation: as_hs_make(oid, ut_beg, ut_end, st_beg)
+--  hid := as_hs_make(oid, ut_beg, ut_end, st_beg)
 CREATE OR REPLACE FUNCTION as_hs_make
 (BIGINT, TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE) 
 RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
@@ -255,22 +289,7 @@
 END;
 ';
 
--- full featured implementation: as_hs_delete(oid, st_hid)
-CREATE OR REPLACE FUNCTION as_hs_delete (BIGINT, BIGINT)
-RETURNS VOID LANGUAGE 'plpgsql' STABLE AS '
-DECLARE
-    var_oid BIGINT;
-    var_hid BIGINT;
-BEGIN
-    var_oid := $1;
-    var_hid := $2;
-    DELETE FROM as_hs_ut WHERE ut_oid = var_oid AND ut_hid = var_hid;
-    DELETE FROM as_hs_st WHERE st_oid = var_oid AND st_hid = var_hid;
-    RETURN;
-END;
-';
-
--- overloading: as_hs_make(oid, ut_beg, ut_end)
+--  convinience function overloading
 CREATE OR REPLACE FUNCTION as_hs_make
 (BIGINT, TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE) 
 RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
@@ -278,8 +297,6 @@
     RETURN as_hs_make($1, $2, $3, NULL);
 END;
 ';
-
--- overloading: as_hs_make(oid, ut_beg)
 CREATE OR REPLACE FUNCTION as_hs_make
 (BIGINT, TIMESTAMP (4) WITH TIME ZONE)
 RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
@@ -287,16 +304,12 @@
     RETURN as_hs_make($1, $2, NULL, NULL);
 END;
 ';
-
--- overloading: as_hs_make(oid)
 CREATE OR REPLACE FUNCTION as_hs_make(BIGINT) 
 RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
 BEGIN
     RETURN as_hs_make($1, NULL, NULL, NULL);
 END;
 ';
-
--- overloading: as_hs_make()
 CREATE OR REPLACE FUNCTION as_hs_make() 
 RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
 BEGIN
@@ -304,16 +317,34 @@
 END;
 ';
 
--- ##
--- ##   Dynamic History Table
--- ##   <oid, hid-max>* := as_hs(ut_beg[, ut_end[, st_beg[, st_end[, st_max]]]])
--- ##
-
--- This finds (optionally only maximum) oid/hid combinations between
--- st_beg and st_end (both inclusive) system time, for which the
--- user time touches the time range between ut_beg and ut_end (both
--- inclusive).
+--  as_hs_delete(oid, st_hid);
+CREATE OR REPLACE FUNCTION as_hs_delete (BIGINT, BIGINT)
+RETURNS VOID LANGUAGE 'plpgsql' STABLE AS '
+DECLARE
+    var_oid BIGINT;
+    var_hid BIGINT;
+BEGIN
+    var_oid := $1;
+    var_hid := $2;
+    DELETE FROM as_hs_ut WHERE ut_oid = var_oid AND ut_hid = var_hid;
+    DELETE FROM as_hs_st WHERE st_oid = var_oid AND st_hid = var_hid;
+    RETURN;
+END;
+';
+
+--  ##  _____________________________________________________________________
+--  ##
+--  ##  Parametrized History Table View
+--  ##  <oid, hid-max>* := as_hs(ut_beg[, ut_end[, st_beg[, st_end[, st_max]]]])
+--  ##  _____________________________________________________________________
+--  ##
+
+--  This finds (optionally only maximum) oid/hid combinations between
+--  st_beg and st_end (both inclusive) system time, for which the
+--  user time touches the time range between ut_beg and ut_end (both
+--  inclusive).
 
+--  parametrized view return type
 DROP TYPE as_hs_t CASCADE;
 CREATE TYPE as_hs_t AS (
     hs_oid BIGINT,
@@ -375,45 +406,39 @@
 END;
 ';
 
---  convinience overloaded function: without "st_max"
+--  convinience overloading functions
 CREATE OR REPLACE FUNCTION as_hs
 (TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE,
  TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE)
 RETURNS SETOF as_hs_t LANGUAGE 'sql' STABLE AS '
     SELECT * FROM as_hs($1, $2, $3, $4, NULL);
 ';
-
---  convinience overloaded function: without "st_end"
 CREATE OR REPLACE FUNCTION as_hs
 (TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE,
  TIMESTAMP (4) WITH TIME ZONE)
 RETURNS SETOF as_hs_t LANGUAGE 'sql' STABLE AS '
     SELECT * FROM as_hs($1, $2, $3, NULL, NULL);
 ';
-
---  convinience overloaded function: without "as_hs_st" at all
 CREATE OR REPLACE FUNCTION as_hs
 (TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE)
 RETURNS SETOF as_hs_t LANGUAGE 'sql' STABLE AS '
     SELECT * FROM as_hs($1, $2, NULL, NULL, NULL);
 ';
-
---  convinience overloaded function: without "ut_end" at all
 CREATE OR REPLACE FUNCTION as_hs
 (TIMESTAMP (4) WITH TIME ZONE)
 RETURNS SETOF as_hs_t LANGUAGE 'sql' STABLE AS '
     SELECT * FROM as_hs($1, NULL, NULL, NULL, NULL);
 ';
-
---  convinience overloaded function: without "as_hs_ut" and "as_hs_st" at all
 CREATE OR REPLACE FUNCTION as_hs ()
 RETURNS SETOF as_hs_t LANGUAGE 'sql' STABLE AS '
     SELECT * FROM as_hs(NULL, NULL, NULL, NULL, NULL);
 ';
 
--- ##
--- ##  Account Table Convinience Manipulation Triggers
--- ##
+--  ##  _____________________________________________________________________
+--  ##
+--  ##  Account Table Convinience Manipulation Triggers
+--  ##  _____________________________________________________________________
+--  ##
 
 --  as_account trigger function
 CREATE OR REPLACE FUNCTION as_account_trigger() 
@@ -445,7 +470,7 @@
 END;
 ';
 
--- as_account triggers
+--  as_account triggers
 CREATE TRIGGER as_account_trigger_before
     BEFORE INSERT OR UPDATE ON as_account FOR EACH ROW
     EXECUTE PROCEDURE as_account_trigger();
@@ -453,10 +478,12 @@
     AFTER DELETE ON as_account FOR EACH ROW
     EXECUTE PROCEDURE as_account_trigger();
 
--- ##
--- ##  Account Table Dynamic View
--- ##  <as_account>* := as_account([ut_beg[, ut_end[, st_beg[, st_end[, st_max]]]]])
--- ##
+--  ##  _____________________________________________________________________
+--  ##
+--  ##  Parametrized Account Table View
+--  ##  <as_account>* := as_account([ut_beg[, ut_end[, st_beg[, st_end[, st_max]]]]])
+--  ##  _____________________________________________________________________
+--  ##
 
 CREATE OR REPLACE FUNCTION as_account () 
 RETURNS SETOF as_account LANGUAGE 'sql' STABLE AS '
@@ -485,10 +512,11 @@
     SELECT ac.* FROM as_account ac, as_hs($1, $2, $3, $4, $5) WHERE ac_hid = hs_hid;
 ';
 
-
--- ##
--- ##  Test Data
--- ##
+--  ##  _____________________________________________________________________
+--  ##
+--  ##  Test Data
+--  ##  _____________________________________________________________________
+--  ##
 
 -- whole test data as view
 -- #  ^          ^
@@ -526,9 +554,11 @@
 INSERT INTO as_account VALUES (20, 'bar2', as_hs_make(20, '0200-01-01', 'infinity',   '2006-01-01'));
 INSERT INTO as_account VALUES (20, 'bar3', as_hs_make(20, '0200-01-01', '0300-01-01', '2007-01-01'));
 
--- ##
--- ##  Data Dump
--- ##
+--  ##  _____________________________________________________________________
+--  ##
+--  ##  Test Data Dumping
+--  ##  _____________________________________________________________________
+--  ##
 
 select * from as_account;
 -- #  ac_oid | ac_name | ac_hid