ossp-pkg/as/as-db/bres/bres-history.sql
--
-- OSSP as -- Accounting System
-- Copyright (c) 2002-2003 Cable & Wireless Deutschland <http://www.cw.com/de/>
-- Copyright (c) 2002-2003 Ralf S. Engelschall <rse@engelschall.com>
--
-- This file is part of OSSP as, an accounting system which can be
-- found at http://www.ossp.org/pkg/tool/as/
--
-- This program is free software; you can redistribute it and/or
-- modify it under the terms of the GNU General Public License
-- as published by the Free Software Foundation; either version
-- 2.0 of the License, or (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
-- General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
-- USA, or contact The OSSP Project <ossp@ossp.org>.
--
-- bres-history.sql -- History Tracking Basic Research
--
-- TODO:
-- - delete entries from as_hs_ut for expiring history: does it still not correctly
-- and especially should only allow the oldest entry to be deleted or implicitly
-- delete also all older entries.
-- ## _____________________________________________________________________
-- ##
-- ## Database Schema
-- ## _____________________________________________________________________
-- ##
-- cascadingly drop existing schema
-- (just ignore errors on console)
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 as_hs_st_seq
INCREMENT 1 MINVALUE 1 START 1;
-- System Time Table
CREATE TABLE as_hs_st (
st_oid BIGINT
NOT NULL,
-- REFERENCES as_oid (id_oid) FIXME
-- MATCH FULL DEFERRABLE, FIXME
-- object identifier
-- [1] (rw)
st_hid BIGINT
UNIQUE NOT NULL
DEFAULT nextval('as_hs_st_seq'),
-- history id associated with system time (discrete monoton increasing)
-- [742] (rw)
st_beg TIMESTAMP (4) WITH TIME ZONE
NOT NULL
DEFAULT 'now'
-- system time of point in history (not monoton increasing, could backstep)
-- [2002-01-01 00:00:00.0000] (rw)
);
-- User Time Table
CREATE TABLE as_hs_ut (
ut_oid BIGINT
NOT NULL,
-- REFERENCES as_oid (id_oid) FIXME
-- MATCH FULL DEFERRABLE, FIXME
-- object identifier
-- [1] (rw)
ut_beg TIMESTAMP (4) WITH TIME ZONE
NOT NULL
DEFAULT 'now',
-- user time begin (inclusive)
-- [2002-01-01 00:00:00.0000] (rw)
ut_end TIMESTAMP (4) WITH TIME ZONE
NOT NULL
DEFAULT 'infinity',
-- user time end (exclusive)
-- [2002-01-01 00:00:00.0000] (rw)
ut_hid BIGINT
NOT NULL
REFERENCES as_hs_st (st_hid)
MATCH FULL DEFERRABLE
-- user time history identifier
-- [42] (rw)
);
-- User Time Table (non-overlapping)
CREATE TABLE as_hs_ut_no (
ut_oid BIGINT
NOT NULL,
-- REFERENCES as_oid (id_oid) FIXME
-- MATCH FULL DEFERRABLE, FIXME
-- object identifier
-- [1] (rw)
ut_beg TIMESTAMP (4) WITH TIME ZONE
NOT NULL
DEFAULT 'now',
-- user time begin (inclusive)
-- [2002-01-01 00:00:00.0000] (rw)
ut_end TIMESTAMP (4) WITH TIME ZONE
NOT NULL
DEFAULT 'infinity',
-- user time end (exclusive)
-- [2002-01-01 00:00:00.0000] (rw)
ut_hid BIGINT
NOT NULL
REFERENCES as_hs_st (st_hid)
MATCH FULL DEFERRABLE,
-- user time history identifier
-- [42] (rw)
ut_hid_max BIGINT
NOT NULL
-- REFERENCES as_hs_st (st_hid) FIXME
-- MATCH FULL DEFERRABLE FIXME
-- user time history identifier (maximum value until which this entry is valid)
-- [42] (rw)
);
-- Account Table
CREATE TABLE as_account(
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 as_hs_st (st_hid)
MATCH FULL DEFERRABLE
-- account history identifier
-- [42] (rw)
);
-- ## _____________________________________________________________________
-- ##
-- ## Automated Update of as_hs_ut_no Table
-- ## _____________________________________________________________________
-- ##
-- as_hs_ut update function
DROP FUNCTION as_hs_ut_update(BIGINT, BIGINT) CASCADE;
CREATE FUNCTION as_hs_ut_update(BIGINT, BIGINT)
RETURNS VOID LANGUAGE 'plpgsql' STABLE AS '
DECLARE
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;
-- delete all old entries corresponding to OID
DELETE FROM as_hs_ut_no WHERE ut_oid = oid AND ut_hid_max = hid;
-- initialize iteration variables
pos_prev := ''-infinity'';
pos_this := ''-infinity'';
pos_next := NULL;
alt_this := NULL;
alt_prev := NULL;
LOOP
-- determine next position from existing begin/end positions
pos_next := (
SELECT min(v) FROM (
(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
pos_next := ''infinity'';
END IF;
-- determine current altidude
alt_this := (
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
alt_this := -1;
END IF;
-- RAISE NOTICE ''pos_this = %, pos_next = %, alt_this = %'', pos_this, pos_next, alt_this;
IF alt_this = -1 THEN
-- RAISE NOTICE ''hit ground - pruning entry'';
ELSE
IF alt_this = alt_prev THEN
-- RAISE NOTICE ''keeping altitude - consolidating entry'';
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 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;
END IF;
-- stop if maximum is reached
IF pos_next >= ''infinity'' THEN
EXIT;
END IF;
-- move one step
pos_this := pos_next;
alt_prev := alt_this;
END LOOP;
RETURN;
END;
';
-- as_hs_ut trigger stub function
DROP FUNCTION ut_trigger() CASCADE;
CREATE FUNCTION ut_trigger()
RETURNS TRIGGER LANGUAGE 'plpgsql' STABLE AS '
BEGIN
IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
PERFORM as_hs_ut_update(NEW.ut_oid, NEW.ut_hid);
ELSIF TG_OP = ''DELETE'' THEN
PERFORM as_hs_ut_update(OLD.ut_oid, OLD.ut_hid);
END IF;
RETURN NULL;
END;
';
-- as_hs_ut trigger
--DROP TRIGGER ut_trigger ON as_hs_ut CASCADE;
CREATE TRIGGER ut_trigger
AFTER INSERT OR UPDATE OR DELETE
ON as_hs_ut FOR EACH ROW
EXECUTE PROCEDURE ut_trigger ();
-- ## _____________________________________________________________________
-- ##
-- ## Convinience History Tracking Constructor/Destructor Functions
-- ## hid := as_hs_make([oid[, ut_beg, ut_end[, st_beg]]]);
-- ## as_hs_delete(oid, st_hid);
-- ## _____________________________________________________________________
-- ##
-- hid := as_hs_make(oid, ut_beg, ut_end, st_beg)
CREATE OR REPLACE FUNCTION as_hs_make
(BIGINT, TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE)
RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
DECLARE
var_oid BIGINT;
var_ut_beg TIMESTAMP (4) WITH TIME ZONE;
var_ut_end TIMESTAMP (4) WITH TIME ZONE;
var_st_beg TIMESTAMP (4) WITH TIME ZONE;
var_hid BIGINT;
BEGIN
var_oid := $1;
var_ut_beg := $2; IF var_ut_beg IS NULL THEN var_ut_beg := now(); END IF;
var_ut_end := $3; IF var_ut_end IS NULL THEN var_ut_end := ''infinity''; END IF;
var_st_beg := $4; IF var_st_beg IS NULL THEN var_st_beg := now(); END IF;
var_hid := NULL;
IF var_oid IS NOT NULL THEN
-- create new system time entry
INSERT INTO as_hs_st (st_oid, st_beg)
VALUES (var_oid, var_st_beg);
var_hid := currval(''as_hs_st_seq'');
-- create new user time entry
INSERT INTO as_hs_ut (ut_oid, ut_beg, ut_end, ut_hid)
VALUES (var_oid, var_ut_beg, var_ut_end, var_hid);
ELSE
-- return just the current/maximum history id
var_hid := currval(''as_hs_st_seq'');
END IF;
RETURN var_hid;
END;
';
-- convenience function overloading
CREATE OR REPLACE FUNCTION as_hs_make
(BIGINT, TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE)
RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
BEGIN
RETURN as_hs_make($1, $2, $3, NULL);
END;
';
CREATE OR REPLACE FUNCTION as_hs_make
(BIGINT, TIMESTAMP (4) WITH TIME ZONE)
RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
BEGIN
RETURN as_hs_make($1, $2, NULL, NULL);
END;
';
CREATE OR REPLACE FUNCTION as_hs_make(BIGINT)
RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
BEGIN
RETURN as_hs_make($1, NULL, NULL, NULL);
END;
';
CREATE OR REPLACE FUNCTION as_hs_make()
RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
BEGIN
RETURN as_hs_make(NULL, NULL, NULL, NULL);
END;
';
-- as_hs_delete(oid, st_hid);
CREATE OR REPLACE FUNCTION as_hs_delete (BIGINT, BIGINT)
RETURNS VOID LANGUAGE 'plpgsql' STABLE AS '
DECLARE
var_oid BIGINT;
var_hid BIGINT;
BEGIN
var_oid := $1;
var_hid := $2;
DELETE FROM as_hs_ut WHERE ut_oid = var_oid AND ut_hid = var_hid;
DELETE FROM as_hs_st WHERE st_oid = var_oid AND st_hid = var_hid;
RETURN;
END;
';
-- ## _____________________________________________________________________
-- ##
-- ## Parametrized History Table View
-- ## <oid, hid-max>* := as_hs(ut_beg[, ut_end[, st_beg[, st_end[, st_max]]]])
-- ## _____________________________________________________________________
-- ##
-- This finds (optionally only maximum) oid/hid combinations between
-- st_beg and st_end (both inclusive) system time, for which the
-- user time touches the time range between ut_beg and ut_end (both
-- inclusive).
-- parametrized view return type
DROP TYPE as_hs_t CASCADE;
CREATE TYPE as_hs_t AS (
hs_oid BIGINT,
hs_hid BIGINT
);
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 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 := ''-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
-- variant 1: take only maximum system time for each user time and oid
FOR rec IN
SELECT ut_oid, ut_hid
FROM as_hs_ut_no, (
SELECT st_oid, max(st_hid) AS st_hid
FROM as_hs_st
WHERE (arg_st_beg <= st_beg AND st_beg <= arg_st_end)
GROUP BY st_oid
) 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)
LOOP
RETURN NEXT rec;
END LOOP;
ELSE
-- variant 2: take all system times for each user time and oid
FOR rec IN
SELECT DISTINCT ut_oid, ut_hid
FROM as_hs_ut_no, (
SELECT st_oid, st_hid
FROM as_hs_st
WHERE (arg_st_beg <= st_beg AND st_beg <= arg_st_end)
) 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)
LOOP
RETURN NEXT rec;
END LOOP;
END IF;
RETURN;
END;
';
-- convenience overloading functions
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);
';
CREATE OR REPLACE FUNCTION as_hs
(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, NULL, NULL);
';
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);
';
CREATE OR REPLACE FUNCTION as_hs
(TIMESTAMP (4) WITH TIME ZONE)
RETURNS SETOF as_hs_t LANGUAGE 'sql' STABLE AS '
SELECT * FROM as_hs($1, NULL, NULL, NULL, NULL);
';
CREATE OR REPLACE FUNCTION as_hs ()
RETURNS SETOF as_hs_t LANGUAGE 'sql' STABLE AS '
SELECT * FROM as_hs(NULL, NULL, NULL, NULL, NULL);
';
-- ## _____________________________________________________________________
-- ##
-- ## Account Table Convinience Manipulation Triggers
-- ## _____________________________________________________________________
-- ##
-- as_account trigger function
CREATE OR REPLACE FUNCTION as_account_trigger()
RETURNS TRIGGER LANGUAGE 'plpgsql' STABLE AS '
DECLARE
hid INTEGER;
BEGIN
IF TG_WHEN = ''BEFORE'' AND TG_OP = ''INSERT'' THEN
IF new.ac_hid IS NULL THEN
-- auto-generate history id if not explicitly set by user
new.ac_hid := as_hs_make(new.ac_oid);
END IF;
RETURN new;
ELSIF TG_WHEN = ''BEFORE'' AND TG_OP = ''UPDATE'' THEN
-- clone old entry to allow old entry to be overwritten as it would be new entry
INSERT INTO as_account VALUES (old.ac_oid, old.ac_name, old.ac_hid);
-- give new entry a new history id if not explicity set by user
IF (new.ac_hid = old.ac_hid) THEN
new.ac_hid := as_hs_make(new.ac_oid);
END IF;
RETURN new;
ELSIF TG_WHEN = ''AFTER'' AND TG_OP = ''DELETE'' THEN
-- delete corresponding history entries
PERFORM as_hs_delete(old.ac_oid, old.ac_hid);
RETURN old;
END IF;
END;
';
-- as_account triggers
CREATE TRIGGER as_account_trigger_before
BEFORE INSERT OR UPDATE ON as_account FOR EACH ROW
EXECUTE PROCEDURE as_account_trigger();
CREATE TRIGGER as_account_trigger_after
AFTER DELETE ON as_account FOR EACH ROW
EXECUTE PROCEDURE as_account_trigger();
-- ## _____________________________________________________________________
-- ##
-- ## Parametrized Account Table View
-- ## <as_account>* := as_account([ut_beg[, ut_end[, st_beg[, st_end[, st_max]]]]])
-- ## _____________________________________________________________________
-- ##
CREATE OR REPLACE FUNCTION as_account ()
RETURNS SETOF as_account LANGUAGE 'sql' STABLE AS '
SELECT ac.* FROM as_account ac, as_hs() WHERE ac_hid = hs_hid;
';
CREATE OR REPLACE FUNCTION as_account
(TIMESTAMP (4) WITH TIME ZONE)
RETURNS SETOF as_account LANGUAGE 'sql' STABLE AS '
SELECT ac.* FROM as_account ac, as_hs($1) WHERE ac_hid = hs_hid;
';
CREATE OR REPLACE FUNCTION as_account
(TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE)
RETURNS SETOF as_account LANGUAGE 'sql' STABLE AS '
SELECT ac.* FROM as_account ac, as_hs($1, $2, $3) WHERE ac_hid = hs_hid;
';
CREATE OR REPLACE FUNCTION as_account
(TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE,
TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE)
RETURNS SETOF as_account LANGUAGE 'sql' STABLE AS '
SELECT ac.* FROM as_account ac, as_hs($1, $2, $3, $4) WHERE ac_hid = hs_hid;
';
CREATE OR REPLACE FUNCTION as_account
(TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE,
TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE, INTEGER)
RETURNS SETOF as_account LANGUAGE 'sql' STABLE AS '
SELECT ac.* FROM as_account ac, as_hs($1, $2, $3, $4, $5) WHERE ac_hid = hs_hid;
';
-- ## _____________________________________________________________________
-- ##
-- ## Test Data
-- ## _____________________________________________________________________
-- ##
-- whole test data as view
-- # ^ ^
-- # | |
-- # 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==[ | |
-- # 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
-- # 2nd (systime hid=2) create record for oid=10, name="bar", 200 <= usertime < 300
-- # 3rd (systime hid=3) create record for oid=10, name="foo", 100 <= usertime < oo
-- # 4th (systime hid=4) create record for oid=20, name="bar1", 100 <= usertime < oo
-- # 5th (systime hid=5) create record for oid=20, name="bar2", 200 <= usertime < oo
-- # 6th (systime hid=6) create record for oid=20, name="bar3", 200 <= usertime < 300
-- whole test data in prosa
-- # 1st create a new record "foo" containing payload valid between usertime 100 upto not including 200
-- # 2nd rename "foo" to "bar" and move usertime frame to 200 upto n. i. 300
-- # 3rd rename "bar" back to "foo", extend usertime begin back to original 100 and extend usertime end to infinity
-- # 4th create a new record "bar1" containing payload valid between usertime 100 and infinity
-- # 5th give "bar1" a new name "bar2" beginning with usertime 200
-- # 6th another new name "bar3" valid for usertime frame 200 upto n. i. 300
-- whole test data injected
INSERT INTO as_account VALUES (10, 'foo', as_hs_make(10, '0100-01-01', '0200-01-01', '2001-01-01'));
INSERT INTO as_account VALUES (10, 'bar', as_hs_make(10, '0200-01-01', '0300-01-01', '2002-01-01'));
INSERT INTO as_account VALUES (10, 'foo', as_hs_make(10, '0100-01-01', 'infinity', '2004-01-01'));
INSERT INTO as_account VALUES (20, 'bar1', as_hs_make(20, '0100-01-01', 'infinity', '2005-01-01'));
INSERT INTO as_account VALUES (20, 'bar2', as_hs_make(20, '0200-01-01', 'infinity', '2006-01-01'));
INSERT INTO as_account VALUES (20, 'bar3', as_hs_make(20, '0200-01-01', '0300-01-01', '2007-01-01'));
-- ## _____________________________________________________________________
-- ##
-- ## Test Data Dumping
-- ## _____________________________________________________________________
-- ##
select * from as_account;
-- # 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 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
-- # 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 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
-- # 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 as_hs_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 as_account;
-- # 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)
-- for any usertime and any systime, list any valid oid for every point in systime (all oids with full history)
SELECT * FROM as_hs('-infinity','infinity','-infinity','infinity',0);
-- # hs_oid | hs_hid
-- # --------+--------
-- # 10 | 1
-- # 10 | 2
-- # 10 | 3
-- # 20 | 4
-- # 20 | 5
-- # 20 | 6
-- # (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('-infinity','infinity','-infinity','infinity',1); -- same as SELECT FROM as_hs();
-- # hs_oid | hs_hid
-- # --------+--------
-- # 10 | 3
-- # 20 | 4
-- # 20 | 6
-- # 20 | 5
-- # (4 rows)
-- same as previous but join with payload to retrieve more details (details from oids with hightes altitude)
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
-- # 20 | bar1 | 4 | 20 | 4
-- # 20 | bar2 | 5 | 20 | 5
-- # 20 | bar3 | 6 | 20 | 6
-- # (4 rows)
-- same as previous but we are only interested in oids valid for usertime point 222
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
-- # 20 | bar3 | 6 | 20 | 6
-- # (2 rows)
-- same as previous but we look back in systime 2006
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
-- # 20 | bar2 | 5 | 20 | 5
-- # (2 rows)
