*** /dev/null Sat Nov 23 08:18:19 2024
--- - Sat Nov 23 08:18:19 2024
***************
*** 0 ****
--- 1,296 ----
+
+ -- ##
+ -- ## Create Database Schema
+ -- ##
+
+ DROP TABLE ac CASCADE;
+ CREATE TABLE ac(
+ ac_oid INTEGER,
+ ac_name TEXT,
+ ac_hid INTEGER
+ );
+
+ DROP SEQUENCE st_seq CASCADE;
+ CREATE SEQUENCE st_seq
+ INCREMENT 1 MINVALUE 1 START 1;
+
+ DROP TABLE st CASCADE;
+ CREATE TABLE st (
+ st_oid INTEGER,
+ st_hid INTEGER DEFAULT nextval('st_seq') NOT NULL,
+ -- discrete monton increasing value assigned to system time
+ st_beg INTEGER
+ -- system time when system change occurred
+ );
+
+ DROP TABLE ut CASCADE;
+ CREATE TABLE ut (
+ ut_oid INTEGER,
+ ut_beg INTEGER,
+ ut_end INTEGER,
+ ut_hid INTEGER
+ );
+
+ DROP TABLE ut_no CASCADE;
+ CREATE TABLE ut_no (
+ ut_oid INTEGER,
+ ut_beg INTEGER,
+ ut_end INTEGER,
+ ut_hid INTEGER,
+ ut_shid INTEGER
+ );
+
+ -- ##
+ -- ## Update ut_no table for ut change on particular OID
+ -- ##
+
+ -- Update function
+ DROP FUNCTION ut_update(INTEGER, INTEGER) CASCADE;
+ CREATE FUNCTION ut_update(INTEGER, INTEGER)
+ RETURNS VOID LANGUAGE 'plpgsql' STABLE AS '
+ DECLARE
+ oid INTEGER;
+ hid INTEGER;
+ pos_prev INTEGER;
+ pos_this INTEGER;
+ pos_next INTEGER;
+ alt_this INTEGER;
+ alt_prev INTEGER;
+ BEGIN
+ oid := $1;
+ hid := $2;
+
+ -- delete all old entries corresponding to OID
+ DELETE FROM ut_no WHERE ut_oid = oid AND ut_shid = hid;
+
+ -- initialize iteration variables
+ pos_prev := -1;
+ pos_this := -1;
+ pos_next := NULL;
+ alt_this := NULL;
+ alt_prev := NULL;
+
+ LOOP
+ -- determine next position from existing begin/end positions
+ pos_next := (
+ SELECT min(v) FROM (
+ (SELECT min(ut_beg) FROM ut WHERE ut_beg > pos_this AND ut_oid = oid AND ut_hid <= hid)
+ UNION (SELECT min(ut_end) FROM ut WHERE ut_end > pos_this AND ut_oid = oid AND ut_hid <= hid)
+ ) AS result (v)
+ );
+ IF pos_next IS NULL THEN
+ pos_next := 9999;
+ END IF;
+
+ -- determine current altidude
+ alt_this := (
+ SELECT max(ut_hid) FROM ut
+ WHERE ut_beg <= pos_this AND pos_this < ut_end AND ut_oid = oid AND ut_hid <= hid
+ );
+ IF alt_this IS NULL THEN
+ alt_this := -1;
+ END IF;
+
+ -- RAISE NOTICE ''pos_this = %, pos_next = %, alt_this = %'', pos_this, pos_next, alt_this;
+
+ IF alt_this = -1 THEN
+ -- RAISE NOTICE ''hit ground - pruning entry'';
+ ELSE
+ IF alt_this = alt_prev THEN
+ -- RAISE NOTICE ''keeping altitude - consolidating entry'';
+ UPDATE ut_no SET ut_end = pos_next
+ WHERE ut_hid = alt_this AND ut_beg = pos_prev AND ut_end = pos_this AND ut_oid = oid AND ut_shid = hid;
+ ELSE
+ -- RAISE NOTICE ''changing altitude - new entry'';
+ INSERT INTO ut_no (ut_hid, ut_oid, ut_beg, ut_end, ut_shid)
+ VALUES (alt_this, oid, pos_this, pos_next, hid);
+ pos_prev := pos_this;
+ END IF;
+ END IF;
+
+ -- stop if maximum is reached
+ IF pos_next >= 9999 THEN
+ EXIT;
+ END IF;
+
+ -- move one step
+ pos_this := pos_next;
+ alt_prev := alt_this;
+ END LOOP;
+ RETURN;
+ END;
+ ';
+
+ -- 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;
+ END IF;
+ RETURN NULL;
+ END;
+ ';
+
+ -- Global Trigger
+ -- DROP TRIGGER ut_trigger ON ut CASCADE;
+ CREATE TRIGGER ut_trigger
+ AFTER INSERT OR UPDATE OR DELETE
+ ON ut FOR EACH ROW
+ EXECUTE PROCEDURE ut_trigger ();
+
+ -- ##
+ -- ## 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).
+
+ DROP TYPE as_hs_t CASCADE;
+ CREATE TYPE as_hs_t AS (
+ hs_oid INTEGER,
+ hs_hid INTEGER
+ );
+ CREATE OR REPLACE FUNCTION as_hs(INTEGER, INTEGER, INTEGER, INTEGER, INTEGER)
+ RETURNS SETOF as_hs_t LANGUAGE 'plpgsql' STABLE AS '
+ DECLARE
+ arg_ut_beg INTEGER; -- user time begin (including)
+ arg_ut_end INTEGER; -- user time end (including)
+ arg_st_beg INTEGER; -- system time begin (including)
+ arg_st_end INTEGER; -- system time end (including)
+ arg_st_max INTEGER; -- whether to take only maximum system time into account
+ rec RECORD;
+ BEGIN
+ -- pre-process input arguments by providing reasonable defaults
+ arg_ut_beg := $1; IF arg_ut_beg IS NULL THEN arg_ut_beg := 0; END IF;
+ arg_ut_end := $2; IF arg_ut_end IS NULL THEN arg_ut_end := 9999; END IF;
+ arg_st_beg := $3; IF arg_st_beg IS NULL THEN arg_st_beg := 0; END IF;
+ arg_st_end := $4; IF arg_st_end IS NULL THEN arg_st_end := 9999; END IF;
+ arg_st_max := $5; IF arg_st_max IS NULL THEN arg_st_max := 1; END IF;
+
+ -- perform the query operation
+ IF arg_st_max = 1 THEN
+ -- variant 1: take only maximum system time for each user time and oid
+ FOR rec IN
+ SELECT ut_oid, ut_hid
+ FROM ut_no, (
+ SELECT st_oid, max(st_hid) AS st_hid
+ FROM st
+ WHERE (arg_st_beg <= st_beg AND st_beg <= arg_st_end)
+ GROUP BY st_oid
+ ) AS st
+ WHERE
+ ut_oid = st_oid AND ut_shid = st_hid
+ AND (ut_beg <= arg_ut_end AND arg_ut_beg < ut_end)
+ LOOP
+ RETURN NEXT rec;
+ END LOOP;
+ ELSE
+ -- variant 2: take all system times for each user time and oid
+ FOR rec IN
+ SELECT ut_oid, ut_hid
+ FROM ut_no, (
+ SELECT st_oid, st_hid
+ FROM st
+ WHERE (arg_st_beg <= st_beg AND st_beg <= arg_st_end)
+ ) AS st
+ WHERE
+ ut_oid = st_oid AND ut_shid = st_hid
+ AND (ut_beg <= arg_ut_end AND arg_ut_beg < ut_end)
+ LOOP
+ RETURN NEXT rec;
+ END LOOP;
+ END IF;
+ RETURN;
+ END;
+ ';
+
+ -- convinience overloaded function: without "st_max"
+ CREATE OR REPLACE FUNCTION as_hs(INTEGER, INTEGER, INTEGER, INTEGER)
+ RETURNS SETOF as_hs_t LANGUAGE 'sql' STABLE AS '
+ SELECT * FROM as_hs($1, $2, $3, $4, NULL);
+ ';
+
+ -- convinience overloaded function: without "st" at all
+ CREATE OR REPLACE FUNCTION as_hs(INTEGER, INTEGER)
+ RETURNS SETOF as_hs_t LANGUAGE 'sql' STABLE AS '
+ SELECT * FROM as_hs($1, $2, NULL, NULL, NULL);
+ ';
+
+ -- convinience overloaded function: without "ut" and "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);
+ ';
+
+ -- ##
+ -- ## Test Data
+ -- ##
+
+ -- INSERT INTO ac VALUES (11, 'foo', 1);
+ -- INSERT INTO ac VALUES (10, 'foo2', 3);
+ -- INSERT INTO ac VALUES (10, 'foo3', 5);
+ -- INSERT INTO ac VALUES (10, 'foo', 7);
+ -- INSERT INTO ac VALUES (10, 'foo', 9);
+ --
+ -- INSERT INTO ac VALUES (20, 'bar', 2);
+ -- INSERT INTO ac VALUES (20, 'bar2', 4);
+ -- INSERT INTO ac VALUES (20, 'bar3', 6);
+ -- INSERT INTO ac VALUES (20, 'bar', 8);
+ -- INSERT INTO ac VALUES (20, 'bar', 10);
+ --
+ -- INSERT INTO st (st_oid,st_beg) VALUES (10, 100);
+ -- INSERT INTO st (st_oid,st_beg) VALUES (20, 110);
+ -- INSERT INTO st (st_oid,st_beg) VALUES (10, 120);
+ -- INSERT INTO st (st_oid,st_beg) VALUES (20, 130);
+ -- INSERT INTO st (st_oid,st_beg) VALUES (10, 140);
+ -- INSERT INTO st (st_oid,st_beg) VALUES (20, 150);
+ -- INSERT INTO st (st_oid,st_beg) VALUES (10, 160);
+ -- INSERT INTO st (st_oid,st_beg) VALUES (20, 170);
+ --
+ -- INSERT INTO ut VALUES (10, 100, 9999, 1);
+ -- INSERT INTO ut VALUES (10, 102, 9999, 3);
+ -- INSERT INTO ut VALUES (10, 104, 9999, 5);
+ -- INSERT INTO ut VALUES (10, 106, 9999, 7);
+ -- INSERT INTO ut VALUES (10, 100, 200, 9);
+ --
+ -- INSERT INTO ut VALUES (20, 101, 9999, 2);
+ -- INSERT INTO ut VALUES (20, 103, 9999, 4);
+ -- INSERT INTO ut VALUES (20, 105, 9999, 6);
+ -- INSERT INTO ut VALUES (20, 107, 9999, 8);
+ -- INSERT INTO ut VALUES (20, 100, 200, 10);
+ -- INSERT INTO ut VALUES (20, 300, 400, 10);
+ -- INSERT INTO ut VALUES (20, 500, 600, 10);
+
+ INSERT INTO st (st_oid,st_beg) VALUES (10, 2001);
+ INSERT INTO ut VALUES (10, 100, 200, currval('st_seq'));
+ INSERT INTO ac VALUES (10, 'foo', currval('st_seq'));
+
+ INSERT INTO st (st_oid,st_beg) VALUES (10, 2002);
+ INSERT INTO ut VALUES (10, 200, 300, currval('st_seq'));
+ INSERT INTO ac VALUES (10, 'bar', currval('st_seq'));
+
+ INSERT INTO st (st_oid,st_beg) VALUES (10, 2004);
+ INSERT INTO ut VALUES (10, 100, 9999, currval('st_seq'));
+ INSERT INTO ac VALUES (10, 'foo', currval('st_seq'));
+
+ INSERT INTO st (st_oid,st_beg) VALUES (20, 2005);
+ INSERT INTO ut VALUES (20, 100, 9999, currval('st_seq'));
+ INSERT INTO ac VALUES (20, 'bar1', currval('st_seq'));
+
+ INSERT INTO st (st_oid,st_beg) VALUES (20, 2006);
+ INSERT INTO ut VALUES (20, 200, 9999, currval('st_seq'));
+ INSERT INTO ac VALUES (20, 'bar2', currval('st_seq'));
+
+ INSERT INTO st (st_oid,st_beg) VALUES (20, 2007);
+ INSERT INTO ut VALUES (20, 200, 300, currval('st_seq'));
+ INSERT INTO ac VALUES (20, 'bar3', currval('st_seq'));
+
|
