From b68e1d439eef0f1eb960a33aaafc42a814bc086b Mon Sep 17 00:00:00 2001 From: scottmk Date: Tue, 26 Jan 2010 21:40:32 +0000 Subject: [PATCH] Add history tables, in the acq schema, for acq.purchase_order and acq.lineitem. See KCLS tickets 4304 and 2172. M Open-ILS/src/sql/Pg/002.schema.config.sql A Open-ILS/src/sql/Pg/upgrade/0140.schema.acq-audit-funcs.sql A Open-ILS/src/sql/Pg/201.acq.audit-functions.sql M Open-ILS/examples/fm_IDL.xml git-svn-id: svn://svn.open-ils.org/ILS/trunk@15390 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 64 +++++++++++ Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- .../src/sql/Pg/201.acq.audit-functions.sql | 107 ++++++++++++++++++ .../upgrade/0140.schema.acq-audit-funcs.sql | 92 +++++++++++++++ 4 files changed, 264 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/201.acq.audit-functions.sql create mode 100644 Open-ILS/src/sql/Pg/upgrade/0140.schema.acq-audit-funcs.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 222fbbb447..0a2324f135 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -4844,6 +4844,35 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + + + + + + + + + + + + + + + + + + + + + + @@ -4896,6 +4925,41 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 343cd9b178..d88ab0797e 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -51,7 +51,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0139'); -- Dan Wells via miker +INSERT INTO config.upgrade_log (version) VALUES ('0140'); -- Scott McKellar CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/201.acq.audit-functions.sql b/Open-ILS/src/sql/Pg/201.acq.audit-functions.sql new file mode 100644 index 0000000000..51f3507ea4 --- /dev/null +++ b/Open-ILS/src/sql/Pg/201.acq.audit-functions.sql @@ -0,0 +1,107 @@ +/* + * Copyright (C) 2004-2008 Georgia Public Library Service + * Copyright (C) 2007-2008 Equinox Software, Inc. + * Scott McKellar + * + * 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 + * 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. + * + */ + +BEGIN; + +CREATE OR REPLACE FUNCTION acq.create_acq_seq ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + EXECUTE $$ + CREATE SEQUENCE acq.$$ || sch || $$_$$ || tbl || $$_pkey_seq; + $$; + RETURN TRUE; +END; +$creator$ LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION acq.create_acq_history ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + EXECUTE $$ + CREATE TABLE acq.$$ || sch || $$_$$ || tbl || $$_history ( + audit_id BIGINT PRIMARY KEY, + audit_time TIMESTAMP WITH TIME ZONE NOT NULL, + audit_action TEXT NOT NULL, + LIKE $$ || sch || $$.$$ || tbl || $$ + ); + $$; + RETURN TRUE; +END; +$creator$ LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION acq.create_acq_func ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + EXECUTE $$ + CREATE OR REPLACE FUNCTION acq.audit_$$ || sch || $$_$$ || tbl || $$_func () + RETURNS TRIGGER AS $func$ + BEGIN + INSERT INTO acq.$$ || sch || $$_$$ || tbl || $$_history + SELECT nextval('acq.$$ || sch || $$_$$ || tbl || $$_pkey_seq'), + now(), + SUBSTR(TG_OP,1,1), + OLD.*; + RETURN NULL; + END; + $func$ LANGUAGE 'plpgsql'; + $$; + RETURN TRUE; +END; +$creator$ LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION acq.create_acq_update_trigger ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + EXECUTE $$ + CREATE TRIGGER audit_$$ || sch || $$_$$ || tbl || $$_update_trigger + AFTER UPDATE OR DELETE ON $$ || sch || $$.$$ || tbl || $$ FOR EACH ROW + EXECUTE PROCEDURE acq.audit_$$ || sch || $$_$$ || tbl || $$_func (); + $$; + RETURN TRUE; +END; +$creator$ LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION acq.create_acq_lifecycle ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + EXECUTE $$ + CREATE OR REPLACE VIEW acq.$$ || sch || $$_$$ || tbl || $$_lifecycle AS + SELECT -1, now() as audit_time, '-' as audit_action, * + FROM $$ || sch || $$.$$ || tbl || $$ + UNION ALL + SELECT * + FROM acq.$$ || sch || $$_$$ || tbl || $$_history; + $$; + RETURN TRUE; +END; +$creator$ LANGUAGE 'plpgsql'; + + +-- The main event + +CREATE OR REPLACE FUNCTION acq.create_acq_auditor ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + PERFORM acq.create_acq_seq(sch, tbl); + PERFORM acq.create_acq_history(sch, tbl); + PERFORM acq.create_acq_func(sch, tbl); + PERFORM acq.create_acq_update_trigger(sch, tbl); + PERFORM acq.create_acq_lifecycle(sch, tbl); + RETURN TRUE; +END; +$creator$ LANGUAGE 'plpgsql'; + +SELECT acq.create_acq_auditor ( 'acq', 'purchase_order' ); +CREATE INDEX acq_po_hist_id_idx ON acq.acq_purchase_order_history( id ); + +SELECT acq.create_acq_auditor ( 'acq', 'lineitem' ); +CREATE INDEX acq_lineitem_hist_id_idx ON acq.acq_lineitem_history( id ); + +COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/0140.schema.acq-audit-funcs.sql b/Open-ILS/src/sql/Pg/upgrade/0140.schema.acq-audit-funcs.sql new file mode 100644 index 0000000000..4c4bfdc328 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0140.schema.acq-audit-funcs.sql @@ -0,0 +1,92 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0140'); -- Scott McKellar + +CREATE OR REPLACE FUNCTION acq.create_acq_seq ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + EXECUTE $$ + CREATE SEQUENCE acq.$$ || sch || $$_$$ || tbl || $$_pkey_seq; + $$; + RETURN TRUE; +END; +$creator$ LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION acq.create_acq_history ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + EXECUTE $$ + CREATE TABLE acq.$$ || sch || $$_$$ || tbl || $$_history ( + audit_id BIGINT PRIMARY KEY, + audit_time TIMESTAMP WITH TIME ZONE NOT NULL, + audit_action TEXT NOT NULL, + LIKE $$ || sch || $$.$$ || tbl || $$ + ); + $$; + RETURN TRUE; +END; +$creator$ LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION acq.create_acq_func ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + EXECUTE $$ + CREATE OR REPLACE FUNCTION acq.audit_$$ || sch || $$_$$ || tbl || $$_func () + RETURNS TRIGGER AS $func$ + BEGIN + INSERT INTO acq.$$ || sch || $$_$$ || tbl || $$_history + SELECT nextval('acq.$$ || sch || $$_$$ || tbl || $$_pkey_seq'), + now(), + SUBSTR(TG_OP,1,1), + OLD.*; + RETURN NULL; + END; + $func$ LANGUAGE 'plpgsql'; + $$; + RETURN TRUE; +END; +$creator$ LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION acq.create_acq_update_trigger ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + EXECUTE $$ + CREATE TRIGGER audit_$$ || sch || $$_$$ || tbl || $$_update_trigger + AFTER UPDATE OR DELETE ON $$ || sch || $$.$$ || tbl || $$ FOR EACH ROW + EXECUTE PROCEDURE acq.audit_$$ || sch || $$_$$ || tbl || $$_func (); + $$; + RETURN TRUE; +END; +$creator$ LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION acq.create_acq_lifecycle ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + EXECUTE $$ + CREATE OR REPLACE VIEW acq.$$ || sch || $$_$$ || tbl || $$_lifecycle AS + SELECT -1, now() as audit_time, '-' as audit_action, * + FROM $$ || sch || $$.$$ || tbl || $$ + UNION ALL + SELECT * + FROM acq.$$ || sch || $$_$$ || tbl || $$_history; + $$; + RETURN TRUE; +END; +$creator$ LANGUAGE 'plpgsql'; + + +-- The main event + +CREATE OR REPLACE FUNCTION acq.create_acq_auditor ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + PERFORM acq.create_acq_seq(sch, tbl); + PERFORM acq.create_acq_history(sch, tbl); + PERFORM acq.create_acq_func(sch, tbl); + PERFORM acq.create_acq_update_trigger(sch, tbl); + PERFORM acq.create_acq_lifecycle(sch, tbl); + RETURN TRUE; +END; +$creator$ LANGUAGE 'plpgsql'; + +SELECT acq.create_acq_auditor ( 'acq', 'purchase_order' ); +CREATE INDEX acq_po_hist_id_idx ON acq.acq_purchase_order_history( id ); + +SELECT acq.create_acq_auditor ( 'acq', 'lineitem' ); +CREATE INDEX acq_lineitem_hist_id_idx ON acq.acq_lineitem_history( id ); + +COMMIT; -- 2.43.2