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.6' '-r1.7' -u '/v/ossp/cvs/ossp-pkg/as/as-db/bres/bres-history.sql,v' 2>/dev/null --- bres-history.sql 2003/06/06 09:46:27 1.6 +++ bres-history.sql 2003/06/06 10:09:17 1.7 @@ -3,18 +3,18 @@ -- ## Create Database Schema -- ## -DROP SEQUENCE st_seq CASCADE; -DROP TABLE st CASCADE; -DROP TABLE ut CASCADE; -DROP TABLE ut_no CASCADE; -DROP TABLE ac CASCADE; +DROP SEQUENCE as_hs_st_seq CASCADE; +DROP TABLE as_hs_st CASCADE; +DROP TABLE as_hs_ut CASCADE; +DROP TABLE as_hs_ut_no CASCADE; +DROP TABLE as_account CASCADE; -- System Time Sequence -CREATE SEQUENCE st_seq +CREATE SEQUENCE as_hs_st_seq INCREMENT 1 MINVALUE 1 START 1; -- System Time Table -CREATE TABLE st ( +CREATE TABLE as_hs_st ( st_oid BIGINT NOT NULL, -- REFERENCES as_oid (id_oid) @@ -23,7 +23,7 @@ -- [1] (rw) st_hid BIGINT UNIQUE NOT NULL - DEFAULT nextval('st_seq'), + DEFAULT nextval('as_hs_st_seq'), -- history id associated with system time (discrete monoton increasing) -- [742] (rw) st_beg TIMESTAMP (4) WITH TIME ZONE @@ -33,7 +33,7 @@ ); -- User Time Table -CREATE TABLE ut ( +CREATE TABLE as_hs_ut ( ut_oid BIGINT NOT NULL, -- REFERENCES as_oid (id_oid) @@ -50,14 +50,14 @@ -- [2002-01-01 00:00:00.0000] (rw) ut_hid BIGINT NOT NULL - REFERENCES st (st_hid) + REFERENCES as_hs_st (st_hid) MATCH FULL DEFERRABLE -- user time history identifier -- [42] (rw) ); -- User Time Table (non-overlapping) -CREATE TABLE ut_no ( +CREATE TABLE as_hs_ut_no ( ut_oid BIGINT NOT NULL, -- REFERENCES as_oid (id_oid) @@ -74,20 +74,20 @@ -- [2002-01-01 00:00:00.0000] (rw) ut_hid BIGINT NOT NULL - REFERENCES st (st_hid) + REFERENCES as_hs_st (st_hid) MATCH FULL DEFERRABLE, -- user time history identifier -- [42] (rw) ut_hid_max BIGINT NOT NULL - REFERENCES st (st_hid) + REFERENCES as_hs_st (st_hid) MATCH FULL DEFERRABLE -- user time history identifier (maximum value until which this entry is valid) -- [42] (rw) ); -- Account Table -CREATE TABLE ac( +CREATE TABLE as_account( ac_oid BIGINT NOT NULL, -- REFERENCES as_oid (id_oid) @@ -100,14 +100,14 @@ -- [foo] (rw) ac_hid BIGINT NOT NULL - REFERENCES st (st_hid) + REFERENCES as_hs_st (st_hid) MATCH FULL DEFERRABLE -- account history identifier -- [42] (rw) ); -- ## --- ## Update ut_no table for ut change on particular OID +-- ## Update as_hs_ut_no table for as_hs_ut change on particular OID -- ## -- Update function @@ -127,7 +127,7 @@ hid := $2; -- delete all old entries corresponding to OID - DELETE FROM ut_no WHERE ut_oid = oid AND ut_hid_max = hid; + DELETE FROM as_hs_ut_no WHERE ut_oid = oid AND ut_hid_max = hid; -- initialize iteration variables pos_prev := ''-infinity''; @@ -140,8 +140,8 @@ -- 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) + (SELECT min(ut_beg) FROM as_hs_ut WHERE ut_beg > pos_this AND ut_oid = oid AND ut_hid <= hid) + UNION (SELECT min(ut_end) FROM as_hs_ut WHERE ut_end > pos_this AND ut_oid = oid AND ut_hid <= hid) ) AS result (v) ); IF pos_next IS NULL THEN @@ -150,7 +150,7 @@ -- determine current altidude alt_this := ( - SELECT max(ut_hid) FROM ut + SELECT max(ut_hid) FROM as_hs_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 @@ -164,11 +164,11 @@ ELSE IF alt_this = alt_prev THEN -- RAISE NOTICE ''keeping altitude - consolidating entry''; - UPDATE ut_no SET ut_end = pos_next + UPDATE as_hs_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_hid_max = hid; ELSE -- RAISE NOTICE ''changing altitude - new entry''; - INSERT INTO ut_no (ut_hid, ut_oid, ut_beg, ut_end, ut_hid_max) + INSERT INTO as_hs_ut_no (ut_hid, ut_oid, ut_beg, ut_end, ut_hid_max) VALUES (alt_this, oid, pos_this, pos_next, hid); pos_prev := pos_this; END IF; @@ -204,10 +204,10 @@ '; -- Global Trigger --- DROP TRIGGER ut_trigger ON ut CASCADE; +-- DROP TRIGGER ut_trigger ON as_hs_ut CASCADE; CREATE TRIGGER ut_trigger AFTER INSERT OR UPDATE OR DELETE - ON ut FOR EACH ROW + ON as_hs_ut FOR EACH ROW EXECUTE PROCEDURE ut_trigger (); -- ## @@ -249,12 +249,12 @@ -- variant 1: take only maximum system time for each user time and oid FOR rec IN SELECT ut_oid, ut_hid - FROM ut_no, ( + FROM as_hs_ut_no, ( SELECT st_oid, max(st_hid) AS st_hid - FROM st + FROM as_hs_st WHERE (arg_st_beg <= st_beg AND st_beg <= arg_st_end) GROUP BY st_oid - ) AS st + ) AS as_hs_st WHERE ut_oid = st_oid AND ut_hid_max = st_hid AND (ut_beg <= arg_ut_end AND arg_ut_beg < ut_end) @@ -265,11 +265,11 @@ -- variant 2: take all system times for each user time and oid FOR rec IN SELECT DISTINCT ut_oid, ut_hid - FROM ut_no, ( + FROM as_hs_ut_no, ( SELECT st_oid, st_hid - FROM st + FROM as_hs_st WHERE (arg_st_beg <= st_beg AND st_beg <= arg_st_end) - ) AS st + ) AS as_hs_st WHERE ut_oid = st_oid AND ut_hid_max = st_hid AND (ut_beg <= arg_ut_end AND arg_ut_beg < ut_end) @@ -289,14 +289,14 @@ SELECT * FROM as_hs($1, $2, $3, $4, NULL); '; --- convinience overloaded function: without "st" at all +-- 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" and "st" at all +-- 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); @@ -309,14 +309,14 @@ -- whole test data as view -- # ^ ^ -- # | | --- # st hid | | | | +-- # as_hs_st hid | | | | -- # 2007-01-01 #6 | [==20:bar3=[ | -- # 2006-01-01 #5 | [==20:bar2===========[ -- # 2005-01-01 #4 [=============20:bar1===========[ -- # 2004-01-01 #3 [=============10:foo============[ -- # 2002-01-01 #2 | [==10:bar==[ | -- # 2001-01-01 #1 [==10:foo==[ | | --- # ut--> 0100-01-01 0200-01-01 0300-01-01 infinity +-- # as_hs_ut--> 0100-01-01 0200-01-01 0300-01-01 infinity -- whole test data as raw data -- # 1st (systime hid=1) create record for oid=10, name="foo", 100 <= usertime < 200 @@ -335,35 +335,35 @@ -- # 6th another new name "bar3" valid for usertime frame 200 upto n. i. 300 -- whole test data injected -INSERT INTO st (st_oid,st_beg) VALUES (10, '2001-01-01'); -INSERT INTO ut VALUES (10, '0100-01-01', '0200-01-01', currval('st_seq')); -INSERT INTO ac VALUES (10, 'foo', currval('st_seq')); - -INSERT INTO st (st_oid,st_beg) VALUES (10, '2002-01-01'); -INSERT INTO ut VALUES (10, '0200-01-01', '0300-01-01', currval('st_seq')); -INSERT INTO ac VALUES (10, 'bar', currval('st_seq')); - -INSERT INTO st (st_oid,st_beg) VALUES (10, '2004-01-01'); -INSERT INTO ut VALUES (10, '0100-01-01', 'infinity', currval('st_seq')); -INSERT INTO ac VALUES (10, 'foo', currval('st_seq')); - -INSERT INTO st (st_oid,st_beg) VALUES (20, '2005-01-01'); -INSERT INTO ut VALUES (20, '0100-01-01', 'infinity', currval('st_seq')); -INSERT INTO ac VALUES (20, 'bar1', currval('st_seq')); - -INSERT INTO st (st_oid,st_beg) VALUES (20, '2006-01-01'); -INSERT INTO ut VALUES (20, '0200-01-01', 'infinity', currval('st_seq')); -INSERT INTO ac VALUES (20, 'bar2', currval('st_seq')); - -INSERT INTO st (st_oid,st_beg) VALUES (20, '2007-01-01'); -INSERT INTO ut VALUES (20, '0200-01-01', '0300-01-01', currval('st_seq')); -INSERT INTO ac VALUES (20, 'bar3', currval('st_seq')); +INSERT INTO as_hs_st (st_oid,st_beg) VALUES (10, '2001-01-01'); +INSERT INTO as_hs_ut VALUES (10, '0100-01-01', '0200-01-01', currval('as_hs_st_seq')); +INSERT INTO as_account VALUES (10, 'foo', currval('as_hs_st_seq')); + +INSERT INTO as_hs_st (st_oid,st_beg) VALUES (10, '2002-01-01'); +INSERT INTO as_hs_ut VALUES (10, '0200-01-01', '0300-01-01', currval('as_hs_st_seq')); +INSERT INTO as_account VALUES (10, 'bar', currval('as_hs_st_seq')); + +INSERT INTO as_hs_st (st_oid,st_beg) VALUES (10, '2004-01-01'); +INSERT INTO as_hs_ut VALUES (10, '0100-01-01', 'infinity', currval('as_hs_st_seq')); +INSERT INTO as_account VALUES (10, 'foo', currval('as_hs_st_seq')); + +INSERT INTO as_hs_st (st_oid,st_beg) VALUES (20, '2005-01-01'); +INSERT INTO as_hs_ut VALUES (20, '0100-01-01', 'infinity', currval('as_hs_st_seq')); +INSERT INTO as_account VALUES (20, 'bar1', currval('as_hs_st_seq')); + +INSERT INTO as_hs_st (st_oid,st_beg) VALUES (20, '2006-01-01'); +INSERT INTO as_hs_ut VALUES (20, '0200-01-01', 'infinity', currval('as_hs_st_seq')); +INSERT INTO as_account VALUES (20, 'bar2', currval('as_hs_st_seq')); + +INSERT INTO as_hs_st (st_oid,st_beg) VALUES (20, '2007-01-01'); +INSERT INTO as_hs_ut VALUES (20, '0200-01-01', '0300-01-01', currval('as_hs_st_seq')); +INSERT INTO as_account VALUES (20, 'bar3', currval('as_hs_st_seq')); -- ## -- ## Data Dump -- ## -select * from ac; +select * from as_account; -- # ac_oid | ac_name | ac_hid -- # --------+---------+-------- -- # 10 | foo | 1 @@ -374,7 +374,7 @@ -- # 20 | bar3 | 6 -- # (6 rows) -select * from ut; +select * from as_hs_ut; -- # ut_oid | ut_beg | ut_end | ut_hid -- # --------+---------------------+---------------------+-------- -- # 10 | 0100-01-01 00:00:00 | 0200-01-01 00:00:00 | 1 @@ -385,7 +385,7 @@ -- # 20 | 0200-01-01 00:00:00 | 0300-01-01 00:00:00 | 6 -- # (6 rows) -select * from ut_no; +select * from as_hs_ut_no; -- # ut_oid | ut_beg | ut_end | ut_hid | ut_hid_max -- # --------+---------------------+---------------------+--------+------------ -- # 10 | 0100-01-01 00:00:00 | 0200-01-01 00:00:00 | 1 | 1 @@ -400,7 +400,7 @@ -- # 20 | 0300-01-01 00:00:00 | infinity | 5 | 6 -- # (10 rows) -select * from st; +select * from as_hs_st; -- # st_oid | st_hid | st_beg -- # --------+--------+------------------------ -- # 10 | 1 | 2001-01-01 00:00:00+01 @@ -416,7 +416,7 @@ -- ## -- whole test data retrieved -SELECT * FROM ac; +SELECT * FROM as_account; -- # ac_oid | ac_name | ac_hid -- # --------+---------+-------- -- # 10 | foo | 1 @@ -450,7 +450,7 @@ -- # (4 rows) -- same as previous but join with payload to retrieve more details (details from oids with hightes altitude) -SELECT * FROM ac, as_hs() WHERE ac_hid = hs_hid; +SELECT * FROM as_account, as_hs() WHERE ac_hid = hs_hid; -- # ac_oid | ac_name | ac_hid | hs_oid | hs_hid -- # --------+---------+--------+--------+-------- -- # 10 | foo | 3 | 10 | 3 @@ -460,7 +460,7 @@ -- # (4 rows) -- same as previous but we are only interested in oids valid for usertime point 222 -SELECT * FROM ac, as_hs('0222-01-01','0222-01-01','-infinity','infinity',1) WHERE ac_hid = hs_hid; +SELECT * FROM as_account, as_hs('0222-01-01','0222-01-01','-infinity','infinity',1) WHERE ac_hid = hs_hid; -- # ac_oid | ac_name | ac_hid | hs_oid | hs_hid -- # --------+---------+--------+--------+-------- -- # 10 | foo | 3 | 10 | 3 @@ -468,7 +468,7 @@ -- # (2 rows) -- same as previous but we look back in systime 2006 -SELECT * FROM ac, as_hs('0222-01-01','0222-01-01','-infinity','2006-01-01',1) WHERE ac_hid = hs_hid; +SELECT * FROM as_account, as_hs('0222-01-01','0222-01-01','-infinity','2006-01-01',1) WHERE ac_hid = hs_hid; -- # ac_oid | ac_name | ac_hid | hs_oid | hs_hid -- # --------+---------+--------+--------+-------- -- # 10 | foo | 3 | 10 | 3