--- 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
-- ##
|
