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.5' '-r1.6' -u '/v/ossp/cvs/ossp-pkg/as/as-db/bres/bres-history.sql,v' 2>/dev/null --- bres-history.sql 2003/06/05 12:45:55 1.5 +++ bres-history.sql 2003/06/06 09:46:27 1.6 @@ -3,42 +3,107 @@ -- ## 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; -CREATE TABLE ac( - ac_oid INTEGER, - ac_name TEXT, - ac_hid INTEGER -); -DROP SEQUENCE st_seq CASCADE; +-- System Time Sequence CREATE SEQUENCE st_seq INCREMENT 1 MINVALUE 1 START 1; -DROP TABLE st CASCADE; +-- System Time Table CREATE TABLE st ( - st_oid INTEGER, - -- redundant stored oid to avoid additional joins on query - 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 BIGINT + NOT NULL, + -- REFERENCES as_oid (id_oid) + -- MATCH FULL DEFERRABLE, + -- object identifier + -- [1] (rw) + st_hid BIGINT + UNIQUE NOT NULL + DEFAULT nextval('st_seq'), + -- history id associated with system time (discrete monoton increasing) + -- [742] (rw) + st_beg TIMESTAMP (4) WITH TIME ZONE + NOT NULL + -- system time of point in history (not monoton increasing, could backstep) + -- [2002-01-01 00:00:00.0000] (rw) ); -DROP TABLE ut CASCADE; +-- User Time Table CREATE TABLE ut ( - ut_oid INTEGER, -- oid - ut_beg INTEGER, -- user time begin (inclusive) - ut_end INTEGER, -- user time end (exclusive) - ut_hid INTEGER -- user time history id (system time when this entry was made) + ut_oid BIGINT + NOT NULL, + -- REFERENCES as_oid (id_oid) + -- MATCH FULL DEFERRABLE, + -- object identifier + -- [1] (rw) + ut_beg TIMESTAMP (4) WITH TIME ZONE + NOT NULL, + -- user time begin (inclusive) + -- [2002-01-01 00:00:00.0000] (rw) + ut_end TIMESTAMP (4) WITH TIME ZONE + NOT NULL, + -- user time end (exclusive) + -- [2002-01-01 00:00:00.0000] (rw) + ut_hid BIGINT + NOT NULL + REFERENCES st (st_hid) + MATCH FULL DEFERRABLE + -- user time history identifier + -- [42] (rw) ); -DROP TABLE ut_no CASCADE; +-- User Time Table (non-overlapping) CREATE TABLE ut_no ( - ut_oid INTEGER, -- oid - ut_beg INTEGER, -- user time begin (inclusive) - ut_end INTEGER, -- user time end (exclusive) - 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) + ut_oid BIGINT + NOT NULL, + -- REFERENCES as_oid (id_oid) + -- MATCH FULL DEFERRABLE, + -- object identifier + -- [1] (rw) + ut_beg TIMESTAMP (4) WITH TIME ZONE + NOT NULL, + -- user time begin (inclusive) + -- [2002-01-01 00:00:00.0000] (rw) + ut_end TIMESTAMP (4) WITH TIME ZONE + NOT NULL, + -- user time end (exclusive) + -- [2002-01-01 00:00:00.0000] (rw) + ut_hid BIGINT + NOT NULL + REFERENCES st (st_hid) + MATCH FULL DEFERRABLE, + -- user time history identifier + -- [42] (rw) + ut_hid_max BIGINT + NOT NULL + REFERENCES 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( + ac_oid BIGINT + NOT NULL, + -- REFERENCES as_oid (id_oid) + -- MATCH FULL DEFERRABLE, + -- object identifier + -- [1] (rw) + ac_name TEXT + NOT NULL, + -- name of account + -- [foo] (rw) + ac_hid BIGINT + NOT NULL + REFERENCES st (st_hid) + MATCH FULL DEFERRABLE + -- account history identifier + -- [42] (rw) ); -- ## @@ -46,17 +111,17 @@ -- ## -- Update function -DROP FUNCTION ut_update(INTEGER, INTEGER) CASCADE; -CREATE FUNCTION ut_update(INTEGER, INTEGER) +DROP FUNCTION ut_update(BIGINT, BIGINT) CASCADE; +CREATE FUNCTION ut_update(BIGINT, BIGINT) 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; + oid BIGINT; + hid BIGINT; + pos_prev TIMESTAMP (4) WITH TIME ZONE; + pos_this TIMESTAMP (4) WITH TIME ZONE; + pos_next TIMESTAMP (4) WITH TIME ZONE; + alt_this BIGINT; + alt_prev BIGINT; BEGIN oid := $1; hid := $2; @@ -65,8 +130,8 @@ DELETE FROM ut_no WHERE ut_oid = oid AND ut_hid_max = hid; -- initialize iteration variables - pos_prev := -1; - pos_this := -1; + pos_prev := ''-infinity''; + pos_this := ''-infinity''; pos_next := NULL; alt_this := NULL; alt_prev := NULL; @@ -80,7 +145,7 @@ ) AS result (v) ); IF pos_next IS NULL THEN - pos_next := 9999; + pos_next := ''infinity''; END IF; -- determine current altidude @@ -110,7 +175,7 @@ END IF; -- stop if maximum is reached - IF pos_next >= 9999 THEN + IF pos_next >= ''infinity'' THEN EXIT; END IF; @@ -157,25 +222,27 @@ DROP TYPE as_hs_t CASCADE; CREATE TYPE as_hs_t AS ( - hs_oid INTEGER, - hs_hid INTEGER + hs_oid BIGINT, + hs_hid BIGINT ); -CREATE OR REPLACE FUNCTION as_hs(INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) +CREATE OR REPLACE FUNCTION as_hs +(TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE, + TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE, 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 + arg_ut_beg TIMESTAMP (4) WITH TIME ZONE; -- user time begin (including) + arg_ut_end TIMESTAMP (4) WITH TIME ZONE; -- user time end (including) + arg_st_beg TIMESTAMP (4) WITH TIME ZONE; -- system time begin (including) + arg_st_end TIMESTAMP (4) WITH TIME ZONE; -- 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; + arg_ut_beg := $1; IF arg_ut_beg IS NULL THEN arg_ut_beg := ''-infinity''; END IF; + arg_ut_end := $2; IF arg_ut_end IS NULL THEN arg_ut_end := ''infinity''; END IF; + arg_st_beg := $3; IF arg_st_beg IS NULL THEN arg_st_beg := ''-infinity''; END IF; + arg_st_end := $4; IF arg_st_end IS NULL THEN arg_st_end := ''infinity''; 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 @@ -215,19 +282,22 @@ '; -- convinience overloaded function: without "st_max" -CREATE OR REPLACE FUNCTION as_hs(INTEGER, INTEGER, INTEGER, INTEGER) +CREATE OR REPLACE FUNCTION as_hs +(TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE, + 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, $3, $4, NULL); '; -- convinience overloaded function: without "st" at all -CREATE OR REPLACE FUNCTION as_hs(INTEGER, INTEGER) +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 -CREATE OR REPLACE FUNCTION as_hs() +CREATE OR REPLACE FUNCTION as_hs () RETURNS SETOF as_hs_t LANGUAGE 'sql' STABLE AS ' SELECT * FROM as_hs(NULL, NULL, NULL, NULL, NULL); '; @@ -237,16 +307,16 @@ -- ## -- whole test data as view --- # ^ --- # | --- # 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 +-- # ^ ^ +-- # | | +-- # 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 -- whole test data as raw data -- # 1st (systime hid=1) create record for oid=10, name="foo", 100 <= usertime < 200 @@ -265,38 +335,85 @@ -- # 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); -INSERT INTO ut VALUES (10, 100, 200, currval('st_seq')); +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); -INSERT INTO ut VALUES (10, 200, 300, 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); -INSERT INTO ut VALUES (10, 100, 9999, 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); -INSERT INTO ut VALUES (20, 100, 9999, 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); -INSERT INTO ut VALUES (20, 200, 9999, 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); -INSERT INTO ut VALUES (20, 200, 300, 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')); -- ## --- ## Test Usage +-- ## Data Dump -- ## select * from ac; +-- # ac_oid | ac_name | ac_hid +-- # --------+---------+-------- +-- # 10 | foo | 1 +-- # 10 | bar | 2 +-- # 10 | foo | 3 +-- # 20 | bar1 | 4 +-- # 20 | bar2 | 5 +-- # 20 | bar3 | 6 +-- # (6 rows) + select * from ut; +-- # ut_oid | ut_beg | ut_end | ut_hid +-- # --------+---------------------+---------------------+-------- +-- # 10 | 0100-01-01 00:00:00 | 0200-01-01 00:00:00 | 1 +-- # 10 | 0200-01-01 00:00:00 | 0300-01-01 00:00:00 | 2 +-- # 10 | 0100-01-01 00:00:00 | infinity | 3 +-- # 20 | 0100-01-01 00:00:00 | infinity | 4 +-- # 20 | 0200-01-01 00:00:00 | infinity | 5 +-- # 20 | 0200-01-01 00:00:00 | 0300-01-01 00:00:00 | 6 +-- # (6 rows) + select * from 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 +-- # 10 | 0100-01-01 00:00:00 | 0200-01-01 00:00:00 | 1 | 2 +-- # 10 | 0200-01-01 00:00:00 | 0300-01-01 00:00:00 | 2 | 2 +-- # 10 | 0100-01-01 00:00:00 | infinity | 3 | 3 +-- # 20 | 0100-01-01 00:00:00 | infinity | 4 | 4 +-- # 20 | 0100-01-01 00:00:00 | 0200-01-01 00:00:00 | 4 | 5 +-- # 20 | 0200-01-01 00:00:00 | infinity | 5 | 5 +-- # 20 | 0100-01-01 00:00:00 | 0200-01-01 00:00:00 | 4 | 6 +-- # 20 | 0200-01-01 00:00:00 | 0300-01-01 00:00:00 | 6 | 6 +-- # 20 | 0300-01-01 00:00:00 | infinity | 5 | 6 +-- # (10 rows) + select * from st; +-- # st_oid | st_hid | st_beg +-- # --------+--------+------------------------ +-- # 10 | 1 | 2001-01-01 00:00:00+01 +-- # 10 | 2 | 2002-01-01 00:00:00+01 +-- # 10 | 3 | 2004-01-01 00:00:00+01 +-- # 20 | 4 | 2005-01-01 00:00:00+01 +-- # 20 | 5 | 2006-01-01 00:00:00+01 +-- # 20 | 6 | 2007-01-01 00:00:00+01 +-- # (6 rows) + +-- ## +-- ## Test Usage +-- ## -- whole test data retrieved SELECT * FROM ac; @@ -311,7 +428,7 @@ -- # (6 rows) -- for any usertime and any systime, list any valid oid for every point in systime (all oids with full history) -SELECT * FROM as_hs(0,9999,0,9999,0); +SELECT * FROM as_hs('-infinity','infinity','-infinity','infinity',0); -- # hs_oid | hs_hid -- # --------+-------- -- # 10 | 1 @@ -323,7 +440,7 @@ -- # (6 rows) -- for any usertime and any systime, list max valid oid for every point in systime (oids with highest altitude) -SELECT * FROM as_hs(0,9999,0,9999,1); -- same as SELECT FROM as_hs(); +SELECT * FROM as_hs('-infinity','infinity','-infinity','infinity',1); -- same as SELECT FROM as_hs(); -- # hs_oid | hs_hid -- # --------+-------- -- # 10 | 3 @@ -342,9 +459,8 @@ -- # 20 | bar3 | 6 | 20 | 6 -- # (4 rows) - -- same as previous but we are only interested in oids valid for usertime point 222 -SELECT * FROM ac, as_hs(222,222,0,9999,1) WHERE ac_hid = hs_hid; +SELECT * FROM ac, 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 @@ -352,9 +468,10 @@ -- # (2 rows) -- same as previous but we look back in systime 2006 -SELECT * FROM ac, as_hs(222,222,0,2006,1) WHERE ac_hid = hs_hid; +SELECT * FROM ac, 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 -- # 20 | bar2 | 5 | 20 | 5 -- # (2 rows) +