--- 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)
+
|
