From 8bf8bc6cf6690e1df3a0ae64d662dc0682f5340e Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Tue, 1 Nov 2011 10:19:17 -0400 Subject: [PATCH] adding auditor table create scripts --- Open-ILS/src/sql/Pg/pines-900.audit-functions.sql | 73 ++++++++++++++++++++++ Open-ILS/src/sql/Pg/pines-901.audit-tables.sql | 28 +++++++++ .../src/sql/Pg/pines-pre-1.6.1-2.1-upgrade.sql | 5 ++ 3 files changed, 106 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/pines-900.audit-functions.sql create mode 100644 Open-ILS/src/sql/Pg/pines-901.audit-tables.sql diff --git a/Open-ILS/src/sql/Pg/pines-900.audit-functions.sql b/Open-ILS/src/sql/Pg/pines-900.audit-functions.sql new file mode 100644 index 0000000000..df4acd7c03 --- /dev/null +++ b/Open-ILS/src/sql/Pg/pines-900.audit-functions.sql @@ -0,0 +1,73 @@ +/* + * Copyright (C) 2004-2008 Georgia Public Library Service + * Copyright (C) 2007-2008 Equinox Software, Inc. + * Mike Rylander + * + * 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. + * + */ + +DROP SCHEMA auditor CASCADE; + +BEGIN; + +CREATE SCHEMA auditor; + + +CREATE FUNCTION auditor.create_auditor ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + EXECUTE $$ + CREATE SEQUENCE auditor.$$ || sch || $$_$$ || tbl || $$_pkey_seq; + $$; + + 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 || $$ + ); + $$; + + 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'; + $$; + + 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 (); + $$; + + 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'; + +COMMIT; + diff --git a/Open-ILS/src/sql/Pg/pines-901.audit-tables.sql b/Open-ILS/src/sql/Pg/pines-901.audit-tables.sql new file mode 100644 index 0000000000..987a18ee41 --- /dev/null +++ b/Open-ILS/src/sql/Pg/pines-901.audit-tables.sql @@ -0,0 +1,28 @@ +/* + * Copyright (C) 2004-2008 Georgia Public Library Service + * Copyright (C) 2007-2008 Equinox Software, Inc. + * Mike Rylander + * + * 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; + +SELECT auditor.create_auditor ( 'actor', 'usr' ); +SELECT auditor.create_auditor ( 'actor', 'usr_address' ); +SELECT auditor.create_auditor ( 'actor', 'org_unit' ); +SELECT auditor.create_auditor ( 'biblio', 'record_entry' ); +SELECT auditor.create_auditor ( 'asset', 'call_number' ); +SELECT auditor.create_auditor ( 'asset', 'copy' ); + +COMMIT; + diff --git a/Open-ILS/src/sql/Pg/pines-pre-1.6.1-2.1-upgrade.sql b/Open-ILS/src/sql/Pg/pines-pre-1.6.1-2.1-upgrade.sql index a2392aeb17..a2bc5f57a5 100644 --- a/Open-ILS/src/sql/Pg/pines-pre-1.6.1-2.1-upgrade.sql +++ b/Open-ILS/src/sql/Pg/pines-pre-1.6.1-2.1-upgrade.sql @@ -1,3 +1,8 @@ +-- Run this script from within the Open-ILS/src/sql/Pg/ directory + +-- Create auditor schema for 1.6.1: + + -- Drop reporter views that depend on tables being dropped and re-created by the upgrade scripts. -- These are restored by the post-upgrade PINES sql script. -- 2.11.0