From 42da201c90efc4b5087e464c31f591cc1a51c422 Mon Sep 17 00:00:00 2001 From: miker Date: Fri, 20 Nov 2009 19:15:10 +0000 Subject: [PATCH] Patch from Joe Atzberger to break up the auditor table creation function into multiple bits. This is the start of a process that will make the auditor functionality more stable and predictable across upgrades, where columns are dropped and created on audited tables. git-svn-id: svn://svn.open-ils.org/ILS/trunk@14991 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/900.audit-functions.sql | 111 ++++++++++++------ .../upgrade/0087.audit_functions_atomized.sql | 95 +++++++++++++++ 3 files changed, 168 insertions(+), 40 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0087.audit_functions_atomized.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 27d1205e0e..af4bf8764d 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 ('0086'); -- Scott McKellar +INSERT INTO config.upgrade_log (version) VALUES ('0087'); -- atz CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/900.audit-functions.sql b/Open-ILS/src/sql/Pg/900.audit-functions.sql index df4acd7c03..60a019bb1d 100644 --- a/Open-ILS/src/sql/Pg/900.audit-functions.sql +++ b/Open-ILS/src/sql/Pg/900.audit-functions.sql @@ -21,53 +21,86 @@ BEGIN; CREATE SCHEMA auditor; - -CREATE FUNCTION auditor.create_auditor ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +CREATE FUNCTION auditor.create_auditor_seq ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ BEGIN - EXECUTE $$ - CREATE SEQUENCE auditor.$$ || sch || $$_$$ || tbl || $$_pkey_seq; - $$; + EXECUTE $$ + CREATE SEQUENCE auditor.$$ || sch || $$_$$ || tbl || $$_pkey_seq; + $$; + RETURN TRUE; +END; +$creator$ LANGUAGE 'plpgsql'; - EXECUTE $$ - CREATE TABLE auditor.$$ || sch || $$_$$ || tbl || $$_history ( - audit_id BIGINT PRIMARY KEY, - audit_time TIMESTAMP WITH TIME ZONE NOT NULL, - audit_action TEXT NOT NULL, - LIKE $$ || sch || $$.$$ || tbl || $$ - ); - $$; +CREATE FUNCTION auditor.create_auditor_history ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + EXECUTE $$ + CREATE TABLE auditor.$$ || 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'; - EXECUTE $$ - CREATE FUNCTION auditor.audit_$$ || sch || $$_$$ || tbl || $$_func () - RETURNS TRIGGER AS $func$ - BEGIN - INSERT INTO auditor.$$ || sch || $$_$$ || tbl || $$_history - SELECT nextval('auditor.$$ || sch || $$_$$ || tbl || $$_pkey_seq'), - now(), - SUBSTR(TG_OP,1,1), - OLD.*; - RETURN NULL; - END; - $func$ LANGUAGE 'plpgsql'; - $$; +CREATE FUNCTION auditor.create_auditor_func ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + EXECUTE $$ + CREATE FUNCTION auditor.audit_$$ || sch || $$_$$ || tbl || $$_func () + RETURNS TRIGGER AS $func$ + BEGIN + INSERT INTO auditor.$$ || sch || $$_$$ || tbl || $$_history + SELECT nextval('auditor.$$ || sch || $$_$$ || tbl || $$_pkey_seq'), + now(), + SUBSTR(TG_OP,1,1), + OLD.*; + RETURN NULL; + END; + $func$ LANGUAGE 'plpgsql'; + $$; + RETURN TRUE; +END; +$creator$ LANGUAGE 'plpgsql'; - EXECUTE $$ - CREATE TRIGGER audit_$$ || sch || $$_$$ || tbl || $$_update_trigger - AFTER UPDATE OR DELETE ON $$ || sch || $$.$$ || tbl || $$ FOR EACH ROW - EXECUTE PROCEDURE auditor.audit_$$ || sch || $$_$$ || tbl || $$_func (); - $$; +CREATE FUNCTION auditor.create_auditor_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 auditor.audit_$$ || sch || $$_$$ || tbl || $$_func (); + $$; + RETURN TRUE; +END; +$creator$ LANGUAGE 'plpgsql'; - EXECUTE $$ - CREATE VIEW auditor.$$ || sch || $$_$$ || tbl || $$_lifecycle AS - SELECT -1, now() as audit_time, '-' as audit_action, * - FROM $$ || sch || $$.$$ || tbl || $$ - UNION ALL - SELECT * - FROM auditor.$$ || sch || $$_$$ || tbl || $$_history; - $$; +CREATE FUNCTION auditor.create_auditor_lifecycle ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + EXECUTE $$ + CREATE VIEW auditor.$$ || sch || $$_$$ || tbl || $$_lifecycle AS + SELECT -1, now() as audit_time, '-' as audit_action, * + FROM $$ || sch || $$.$$ || tbl || $$ + UNION ALL + SELECT * + FROM auditor.$$ || sch || $$_$$ || tbl || $$_history; + $$; RETURN TRUE; END; $creator$ LANGUAGE 'plpgsql'; + +-- The main event + +CREATE FUNCTION auditor.create_auditor ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + PERFORM auditor.create_auditor_seq(sch, tbl); + PERFORM auditor.create_auditor_history(sch, tbl); + PERFORM auditor.create_auditor_func(sch, tbl); + PERFORM auditor.create_auditor_update_trigger(sch, tbl); + PERFORM auditor.create_auditor_lifecycle(sch, tbl); + RETURN TRUE; +END; +$creator$ LANGUAGE 'plpgsql'; + COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/0087.audit_functions_atomized.sql b/Open-ILS/src/sql/Pg/upgrade/0087.audit_functions_atomized.sql new file mode 100644 index 0000000000..0dd38ba37a --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0087.audit_functions_atomized.sql @@ -0,0 +1,95 @@ +/* + * Copyright (C) 2009 Equinox Software, Inc. + * Joe Atzberger + * + * Released under GNU General Public License version 2 + */ + +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0087'); -- atz + +CREATE FUNCTION auditor.create_auditor_seq ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + EXECUTE $$ + CREATE SEQUENCE auditor.$$ || sch || $$_$$ || tbl || $$_pkey_seq; + $$; + RETURN TRUE; +END; +$creator$ LANGUAGE 'plpgsql'; + +CREATE FUNCTION auditor.create_auditor_history ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + EXECUTE $$ + CREATE TABLE auditor.$$ || 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 FUNCTION auditor.create_auditor_func ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + EXECUTE $$ + CREATE FUNCTION auditor.audit_$$ || sch || $$_$$ || tbl || $$_func () + RETURNS TRIGGER AS $func$ + BEGIN + INSERT INTO auditor.$$ || sch || $$_$$ || tbl || $$_history + SELECT nextval('auditor.$$ || sch || $$_$$ || tbl || $$_pkey_seq'), + now(), + SUBSTR(TG_OP,1,1), + OLD.*; + RETURN NULL; + END; + $func$ LANGUAGE 'plpgsql'; + $$; + RETURN TRUE; +END; +$creator$ LANGUAGE 'plpgsql'; + +CREATE FUNCTION auditor.create_auditor_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 auditor.audit_$$ || sch || $$_$$ || tbl || $$_func (); + $$; + RETURN TRUE; +END; +$creator$ LANGUAGE 'plpgsql'; + +CREATE FUNCTION auditor.create_auditor_lifecycle ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + EXECUTE $$ + CREATE VIEW auditor.$$ || sch || $$_$$ || tbl || $$_lifecycle AS + SELECT -1, now() as audit_time, '-' as audit_action, * + FROM $$ || sch || $$.$$ || tbl || $$ + UNION ALL + SELECT * + FROM auditor.$$ || sch || $$_$$ || tbl || $$_history; + $$; + RETURN TRUE; +END; +$creator$ LANGUAGE 'plpgsql'; + +DROP FUNCTION IF EXISTS auditor.create_auditor; -- Besides this line and the 0087 INSERT, the rest of this file is 900.audit-functions.sql + +-- The main event + +CREATE FUNCTION auditor.create_auditor ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + PERFORM auditor.create_auditor_seq(sch, tbl); + PERFORM auditor.create_auditor_history(sch, tbl); + PERFORM auditor.create_auditor_func(sch, tbl); + PERFORM auditor.create_auditor_update_trigger(sch, tbl); + PERFORM auditor.create_auditor_lifecycle(sch, tbl); + RETURN TRUE; +END; +$creator$ LANGUAGE 'plpgsql'; + +COMMIT; + -- 2.43.2