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.9' '-r1.10' -u '/v/ossp/cvs/ossp-pkg/as/as-db/bres/bres-history.sql,v' 2>/dev/null
--- bres-history.sql	2003/06/06 13:40:46	1.9
+++ bres-history.sql	2003/06/08 14:50:02	1.10
@@ -1,3 +1,6 @@
+-- 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
@@ -85,8 +88,8 @@
                    -- [42] (rw)
     ut_hid_max     BIGINT
                    NOT NULL
-                   REFERENCES as_hs_st (st_hid)
-                   MATCH FULL DEFERRABLE
+                   -- REFERENCES as_hs_st (st_hid)
+                   -- MATCH FULL DEFERRABLE
                    -- user time history identifier (maximum value until which this entry is valid)
                    -- [42] (rw)
 );
@@ -104,7 +107,7 @@
                    -- name of account
                    -- [foo] (rw)
     ac_hid         BIGINT
-                   NOT NULL 
+                   NOT NULL
                    REFERENCES as_hs_st (st_hid)
                    MATCH FULL DEFERRABLE
                    -- account history identifier
@@ -252,6 +255,21 @@
 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)
 CREATE OR REPLACE FUNCTION as_hs_make
 (BIGINT, TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE) 
@@ -394,6 +412,81 @@
 ';
 
 -- ##
+-- ##  Account Table Convinience Manipulation Triggers
+-- ##
+
+--  as_account trigger function
+CREATE OR REPLACE FUNCTION as_account_trigger() 
+RETURNS TRIGGER LANGUAGE 'plpgsql' STABLE AS '
+DECLARE
+    hid INTEGER;
+BEGIN
+    IF TG_WHEN = ''BEFORE'' AND TG_OP = ''INSERT'' THEN
+        IF new.ac_hid IS NULL THEN
+            --  auto-generate history id if not explicitly set by user
+            new.ac_hid := as_hs_make(new.ac_oid);
+        END IF;
+        RETURN new;
+    END IF;
+    IF TG_WHEN = ''BEFORE'' AND TG_OP = ''UPDATE'' THEN
+        --  clone old entry to allow old entry to be overwritten as it would be new entry
+        INSERT INTO as_account VALUES (old.ac_oid, old.ac_name, old.ac_hid);
+        --  give new entry a new history id if not explicity set by user
+        IF (new.ac_hid = old.ac_hid) THEN
+            new.ac_hid := as_hs_make(new.ac_oid);
+        END IF;
+        RETURN new;
+    END IF;
+    IF TG_WHEN = ''AFTER'' AND TG_OP = ''DELETE'' THEN
+        --  delete corresponding history entries
+        PERFORM as_hs_delete(old.ac_oid, old.ac_hid); 
+        RETURN old;
+    END IF;
+END;
+';
+
+-- as_account triggers
+CREATE TRIGGER as_account_trigger_before
+    BEFORE INSERT OR UPDATE ON as_account FOR EACH ROW
+    EXECUTE PROCEDURE as_account_trigger();
+CREATE TRIGGER as_account_trigger_after
+    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]]]]])
+-- ##
+
+CREATE OR REPLACE FUNCTION as_account () 
+RETURNS SETOF as_account LANGUAGE 'sql' STABLE AS '
+    SELECT ac.* FROM as_account ac, as_hs() WHERE ac_hid = hs_hid;
+';
+CREATE OR REPLACE FUNCTION as_account
+(TIMESTAMP (4) WITH TIME ZONE) 
+RETURNS SETOF as_account LANGUAGE 'sql' STABLE AS '
+    SELECT ac.* FROM as_account ac, as_hs($1) WHERE ac_hid = hs_hid;
+';
+CREATE OR REPLACE FUNCTION as_account
+(TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE)
+RETURNS SETOF as_account LANGUAGE 'sql' STABLE AS '
+    SELECT ac.* FROM as_account ac, as_hs($1, $2, $3) WHERE ac_hid = hs_hid;
+';
+CREATE OR REPLACE FUNCTION as_account
+(TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE,
+ TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE)
+RETURNS SETOF as_account LANGUAGE 'sql' STABLE AS '
+    SELECT ac.* FROM as_account ac, as_hs($1, $2, $3, $4) WHERE ac_hid = hs_hid;
+';
+CREATE OR REPLACE FUNCTION as_account
+(TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE,
+ TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE, INTEGER)
+RETURNS SETOF as_account LANGUAGE 'sql' STABLE AS '
+    SELECT ac.* FROM as_account ac, as_hs($1, $2, $3, $4, $5) WHERE ac_hid = hs_hid;
+';
+
+
+-- ##
 -- ##  Test Data
 -- ##