--- bres-history.sql 2003/06/08 14:50:02 1.10
+++ bres-history.sql 2003/06/08 15:07:47 1.11
@@ -1,11 +1,42 @@
--- 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.
-
--- ##
--- ## Create Database Schema
--- ##
+--
+-- 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;
@@ -20,8 +51,8 @@
CREATE TABLE as_hs_st (
st_oid BIGINT
NOT NULL,
- -- REFERENCES as_oid (id_oid)
- -- MATCH FULL DEFERRABLE,
+ -- REFERENCES as_oid (id_oid) FIXME
+ -- MATCH FULL DEFERRABLE, FIXME
-- object identifier
-- [1] (rw)
st_hid BIGINT
@@ -40,8 +71,8 @@
CREATE TABLE as_hs_ut (
ut_oid BIGINT
NOT NULL,
- -- REFERENCES as_oid (id_oid)
- -- MATCH FULL DEFERRABLE,
+ -- REFERENCES as_oid (id_oid) FIXME
+ -- MATCH FULL DEFERRABLE, FIXME
-- object identifier
-- [1] (rw)
ut_beg TIMESTAMP (4) WITH TIME ZONE
@@ -66,8 +97,8 @@
CREATE TABLE as_hs_ut_no (
ut_oid BIGINT
NOT NULL,
- -- REFERENCES as_oid (id_oid)
- -- MATCH FULL DEFERRABLE,
+ -- REFERENCES as_oid (id_oid) FIXME
+ -- MATCH FULL DEFERRABLE, FIXME
-- object identifier
-- [1] (rw)
ut_beg TIMESTAMP (4) WITH TIME ZONE
@@ -88,8 +119,8 @@
-- [42] (rw)
ut_hid_max BIGINT
NOT NULL
- -- REFERENCES as_hs_st (st_hid)
- -- MATCH FULL DEFERRABLE
+ -- 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)
);
@@ -114,13 +145,15 @@
-- [42] (rw)
);
--- ##
--- ## Update as_hs_ut_no table for as_hs_ut change on particular OID
--- ##
-
--- Update function
-DROP FUNCTION ut_update(BIGINT, BIGINT) CASCADE;
-CREATE FUNCTION ut_update(BIGINT, BIGINT)
+-- ## _____________________________________________________________________
+-- ##
+-- ## 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;
@@ -195,35 +228,36 @@
END;
';
--- Trigger Stub Function
+-- 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 ut_update(NEW.ut_oid, NEW.ut_hid);
- ELSE
- IF TG_OP = ''DELETE'' THEN
- PERFORM ut_update(OLD.ut_oid, OLD.ut_hid);
- END IF;
+ ELSIF TG_OP = ''DELETE'' THEN
+ PERFORM ut_update(OLD.ut_oid, OLD.ut_hid);
END IF;
RETURN NULL;
END;
';
--- Global Trigger
--- DROP TRIGGER ut_trigger ON as_hs_ut CASCADE;
+-- 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 ();
--- ##
--- ## Make History Tracking
--- ## hid := as_hs_make([oid[, ut_beg, ut_end[, st_beg]]])
--- ##
+-- ## _____________________________________________________________________
+-- ##
+-- ## Convinience History Tracking Constructor/Destructor Functions
+-- ## hid := as_hs_make([oid[, ut_beg, ut_end[, st_beg]]]);
+-- ## as_hs_delete(oid, st_hid);
+-- ## _____________________________________________________________________
+-- ##
--- full featured implementation: as_hs_make(oid, ut_beg, ut_end, st_beg)
+-- 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 '
@@ -255,22 +289,7 @@
END;
';
--- full featured implementation: 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;
-';
-
--- overloading: as_hs_make(oid, ut_beg, ut_end)
+-- convinience 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 '
@@ -278,8 +297,6 @@
RETURN as_hs_make($1, $2, $3, NULL);
END;
';
-
--- overloading: as_hs_make(oid, ut_beg)
CREATE OR REPLACE FUNCTION as_hs_make
(BIGINT, TIMESTAMP (4) WITH TIME ZONE)
RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
@@ -287,16 +304,12 @@
RETURN as_hs_make($1, $2, NULL, NULL);
END;
';
-
--- overloading: as_hs_make(oid)
CREATE OR REPLACE FUNCTION as_hs_make(BIGINT)
RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
BEGIN
RETURN as_hs_make($1, NULL, NULL, NULL);
END;
';
-
--- overloading: as_hs_make()
CREATE OR REPLACE FUNCTION as_hs_make()
RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
BEGIN
@@ -304,16 +317,34 @@
END;
';
--- ##
--- ## Dynamic History Table
--- ## <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).
+-- 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,
@@ -375,45 +406,39 @@
END;
';
--- convinience overloaded function: without "st_max"
+-- convinience 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);
';
-
--- convinience overloaded function: without "st_end"
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);
';
-
--- 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_end" at all
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);
';
-
--- 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);
';
--- ##
--- ## Account Table Convinience Manipulation Triggers
--- ##
+-- ## _____________________________________________________________________
+-- ##
+-- ## Account Table Convinience Manipulation Triggers
+-- ## _____________________________________________________________________
+-- ##
-- as_account trigger function
CREATE OR REPLACE FUNCTION as_account_trigger()
@@ -445,7 +470,7 @@
END;
';
--- as_account triggers
+-- as_account triggers
CREATE TRIGGER as_account_trigger_before
BEFORE INSERT OR UPDATE ON as_account FOR EACH ROW
EXECUTE PROCEDURE as_account_trigger();
@@ -453,10 +478,12 @@
AFTER DELETE ON as_account FOR EACH ROW
EXECUTE PROCEDURE as_account_trigger();
--- ##
--- ## Account Table Dynamic View
--- ## <as_account>* := as_account([ut_beg[, ut_end[, st_beg[, st_end[, st_max]]]]])
--- ##
+-- ## _____________________________________________________________________
+-- ##
+-- ## 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 '
@@ -485,10 +512,11 @@
SELECT ac.* FROM as_account ac, as_hs($1, $2, $3, $4, $5) WHERE ac_hid = hs_hid;
';
-
--- ##
--- ## Test Data
--- ##
+-- ## _____________________________________________________________________
+-- ##
+-- ## Test Data
+-- ## _____________________________________________________________________
+-- ##
-- whole test data as view
-- # ^ ^
@@ -526,9 +554,11 @@
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'));
--- ##
--- ## Data Dump
--- ##
+-- ## _____________________________________________________________________
+-- ##
+-- ## Test Data Dumping
+-- ## _____________________________________________________________________
+-- ##
select * from as_account;
-- # ac_oid | ac_name | ac_hid
|
