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