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