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.1' '-r1.2' -u '/v/ossp/cvs/ossp-pkg/as/as-db/bres/bres-history.sql,v' 2>/dev/null --- bres-history.sql 2003/05/29 10:11:15 1.1 +++ bres-history.sql 2003/06/05 10:58:37 1.2 @@ -5,9 +5,9 @@ DROP TABLE ac CASCADE; CREATE TABLE ac( - ac_oid INTEGER, - ac_name TEXT, - ac_hid INTEGER + ac_oid INTEGER, + ac_name TEXT, + ac_hid INTEGER ); DROP SEQUENCE st_seq CASCADE; @@ -16,28 +16,28 @@ 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 + 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 + 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 + ut_oid INTEGER, -- oid + ut_beg INTEGER, -- user time begin + ut_end INTEGER, -- user time end + ut_hid INTEGER, -- user time history id (system time when this entry was made) + ut_hid_max INTEGER -- user time history id (system time until which this entry is valid) ); -- ## @@ -61,7 +61,7 @@ hid := $2; -- delete all old entries corresponding to OID - DELETE FROM ut_no WHERE ut_oid = oid AND ut_shid = hid; + DELETE FROM ut_no WHERE ut_oid = oid AND ut_hid_max = hid; -- initialize iteration variables pos_prev := -1; @@ -99,10 +99,10 @@ 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; + 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_shid) + INSERT INTO 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; @@ -174,7 +174,7 @@ 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; + arg_st_max := $5; IF arg_st_max IS NULL THEN arg_st_max := 0; END IF; -- perform the query operation IF arg_st_max = 1 THEN @@ -188,7 +188,7 @@ GROUP BY st_oid ) AS st WHERE - ut_oid = st_oid AND ut_shid = st_hid + ut_oid = st_oid AND ut_hid_max = st_hid AND (ut_beg <= arg_ut_end AND arg_ut_beg < ut_end) LOOP RETURN NEXT rec; @@ -196,14 +196,14 @@ ELSE -- variant 2: take all system times for each user time and oid FOR rec IN - SELECT ut_oid, ut_hid + SELECT DISTINCT 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 + ut_oid = st_oid AND ut_hid_max = st_hid AND (ut_beg <= arg_ut_end AND arg_ut_beg < ut_end) LOOP RETURN NEXT rec; @@ -235,40 +235,16 @@ -- ## 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); +-- # ^ +-- # | +-- # st hid +-- # 2007 #6 | [==20:bar3=[ | +-- # 2006 #5 | [==20:bar2===========[ +-- # 2005 #4 [=============20:bar1===========[ +-- # 2004 #3 [=============10:foo============[ +-- # 2002 #2 | [==10:bar==[ | +-- # 2001 #1 [==10:foo==[ | | +-- # ut--> 100 200 300 9999 INSERT INTO st (st_oid,st_beg) VALUES (10, 2001); INSERT INTO ut VALUES (10, 100, 200, currval('st_seq')); @@ -294,3 +270,21 @@ INSERT INTO ut VALUES (20, 200, 300, currval('st_seq')); INSERT INTO ac VALUES (20, 'bar3', currval('st_seq')); +-- ## +-- ## Test Usage +-- ## + +select * from ac; +select * from ut; +select * from ut_no; +select * from st; + +-- whole test data (complete history, i.e. data at all hids), PLAIN +SELECT * FROM ac; + +-- whole history data +SELECT * FROM as_hs(); + +-- whole test data (complete history, i.e. data at all hids), WITH HISTORY +SELECT * FROM ac, as_hs() WHERE ac_hid = hs_hid; +