2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2007-2008 Equinox Software, Inc.
4 * Mike Rylander <miker@esilibrary.com>
6 * This program is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU General Public License
8 * as published by the Free Software Foundation; either version 2
9 * of the License, or (at your option) any later version.
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
18 DROP SCHEMA IF EXISTS auditor CASCADE;
22 CREATE SCHEMA auditor;
24 CREATE FUNCTION auditor.create_auditor_seq ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
27 CREATE SEQUENCE auditor.$$ || sch || $$_$$ || tbl || $$_pkey_seq;
31 $creator$ LANGUAGE 'plpgsql';
33 CREATE FUNCTION auditor.create_auditor_history ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
36 CREATE TABLE auditor.$$ || sch || $$_$$ || tbl || $$_history (
37 audit_id BIGINT PRIMARY KEY,
38 audit_time TIMESTAMP WITH TIME ZONE NOT NULL,
39 audit_action TEXT NOT NULL,
40 LIKE $$ || sch || $$.$$ || tbl || $$
45 $creator$ LANGUAGE 'plpgsql';
47 CREATE FUNCTION auditor.create_auditor_func ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
50 CREATE FUNCTION auditor.audit_$$ || sch || $$_$$ || tbl || $$_func ()
51 RETURNS TRIGGER AS $func$
53 INSERT INTO auditor.$$ || sch || $$_$$ || tbl || $$_history
54 SELECT nextval('auditor.$$ || sch || $$_$$ || tbl || $$_pkey_seq'),
60 $func$ LANGUAGE 'plpgsql';
64 $creator$ LANGUAGE 'plpgsql';
66 CREATE FUNCTION auditor.create_auditor_update_trigger ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
69 CREATE TRIGGER audit_$$ || sch || $$_$$ || tbl || $$_update_trigger
70 AFTER UPDATE OR DELETE ON $$ || sch || $$.$$ || tbl || $$ FOR EACH ROW
71 EXECUTE PROCEDURE auditor.audit_$$ || sch || $$_$$ || tbl || $$_func ();
75 $creator$ LANGUAGE 'plpgsql';
77 CREATE FUNCTION auditor.create_auditor_lifecycle ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
80 CREATE VIEW auditor.$$ || sch || $$_$$ || tbl || $$_lifecycle AS
81 SELECT -1, now() as audit_time, '-' as audit_action, *
82 FROM $$ || sch || $$.$$ || tbl || $$
85 FROM auditor.$$ || sch || $$_$$ || tbl || $$_history;
89 $creator$ LANGUAGE 'plpgsql';
94 CREATE FUNCTION auditor.create_auditor ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
96 PERFORM auditor.create_auditor_seq(sch, tbl);
97 PERFORM auditor.create_auditor_history(sch, tbl);
98 PERFORM auditor.create_auditor_func(sch, tbl);
99 PERFORM auditor.create_auditor_update_trigger(sch, tbl);
100 PERFORM auditor.create_auditor_lifecycle(sch, tbl);
103 $creator$ LANGUAGE 'plpgsql';