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 -- These three functions are for capturing, getting, and clearing user and workstation information
26 -- Set the User AND workstation in one call. Tis faster. And less calls.
27 -- First argument is user, second is workstation
28 CREATE OR REPLACE FUNCTION auditor.set_audit_info(INT, INT) RETURNS VOID AS $$
29 $_SHARED{"eg_audit_user"} = $_[0];
30 $_SHARED{"eg_audit_ws"} = $_[1];
33 -- Get the User AND workstation in one call. Less calls, useful for joins ;)
34 CREATE OR REPLACE FUNCTION auditor.get_audit_info() RETURNS TABLE (eg_user INT, eg_ws INT) AS $$
35 return [{eg_user => $_SHARED{"eg_audit_user"}, eg_ws => $_SHARED{"eg_audit_ws"}}];
38 -- Clear the audit info, for whatever reason
39 CREATE OR REPLACE FUNCTION auditor.clear_audit_info() RETURNS VOID AS $$
40 delete($_SHARED{"eg_audit_user"});
41 delete($_SHARED{"eg_audit_ws"});
44 CREATE FUNCTION auditor.create_auditor_seq ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
47 CREATE SEQUENCE auditor.$$ || sch || $$_$$ || tbl || $$_pkey_seq;
51 $creator$ LANGUAGE 'plpgsql';
53 CREATE FUNCTION auditor.create_auditor_history ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
56 CREATE TABLE auditor.$$ || sch || $$_$$ || tbl || $$_history (
57 audit_id BIGINT PRIMARY KEY,
58 audit_time TIMESTAMP WITH TIME ZONE NOT NULL,
59 audit_action TEXT NOT NULL,
62 LIKE $$ || sch || $$.$$ || tbl || $$
67 $creator$ LANGUAGE 'plpgsql';
69 CREATE OR REPLACE FUNCTION auditor.create_auditor_func ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
73 SELECT INTO column_list array_agg(a.attname)
74 FROM pg_catalog.pg_attribute a
75 JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
76 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
77 WHERE relkind = 'r' AND n.nspname = sch AND c.relname = tbl AND a.attnum > 0 AND NOT a.attisdropped;
80 CREATE OR REPLACE FUNCTION auditor.audit_$$ || sch || $$_$$ || tbl || $$_func ()
81 RETURNS TRIGGER AS $func$
83 INSERT INTO auditor.$$ || sch || $$_$$ || tbl || $$_history ( audit_id, audit_time, audit_action, audit_user, audit_ws, $$
84 || array_to_string(column_list, ', ') || $$ )
85 SELECT nextval('auditor.$$ || sch || $$_$$ || tbl || $$_pkey_seq'),
90 OLD.$$ || array_to_string(column_list, ', OLD.') || $$
91 FROM auditor.get_audit_info();
94 $func$ LANGUAGE 'plpgsql';
98 $creator$ LANGUAGE 'plpgsql';
100 CREATE FUNCTION auditor.create_auditor_update_trigger ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
103 CREATE TRIGGER audit_$$ || sch || $$_$$ || tbl || $$_update_trigger
104 AFTER UPDATE OR DELETE ON $$ || sch || $$.$$ || tbl || $$ FOR EACH ROW
105 EXECUTE PROCEDURE auditor.audit_$$ || sch || $$_$$ || tbl || $$_func ();
109 $creator$ LANGUAGE 'plpgsql';
111 CREATE OR REPLACE FUNCTION auditor.create_auditor_lifecycle ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
115 SELECT INTO column_list array_agg(a.attname)
116 FROM pg_catalog.pg_attribute a
117 JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
118 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
119 WHERE relkind = 'r' AND n.nspname = sch AND c.relname = tbl AND a.attnum > 0 AND NOT a.attisdropped;
122 CREATE VIEW auditor.$$ || sch || $$_$$ || tbl || $$_lifecycle AS
123 SELECT -1 AS audit_id,
128 $$ || array_to_string(column_list, ', ') || $$
129 FROM $$ || sch || $$.$$ || tbl || $$
131 SELECT audit_id, audit_time, audit_action, audit_user, audit_ws,
132 $$ || array_to_string(column_list, ', ') || $$
133 FROM auditor.$$ || sch || $$_$$ || tbl || $$_history;
137 $creator$ LANGUAGE 'plpgsql';
142 CREATE FUNCTION auditor.create_auditor ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
144 PERFORM auditor.create_auditor_seq(sch, tbl);
145 PERFORM auditor.create_auditor_history(sch, tbl);
146 PERFORM auditor.create_auditor_func(sch, tbl);
147 PERFORM auditor.create_auditor_update_trigger(sch, tbl);
148 PERFORM auditor.create_auditor_lifecycle(sch, tbl);
151 $creator$ LANGUAGE 'plpgsql';
153 -- Corrects all column discrepencies between audit table and core table:
154 -- Adds missing columns
155 -- Removes leftover columns
157 -- Also, ensures all core auditor columns exist.
158 CREATE OR REPLACE FUNCTION auditor.fix_columns() RETURNS VOID AS $BODY$
160 current_table TEXT = ''; -- Storage for post-loop main table name
161 current_audit_table TEXT = ''; -- Storage for post-loop audit table name
162 query TEXT = ''; -- Storage for built query
163 cr RECORD; -- column record object
164 alter_t BOOL = false; -- Has the alter table command been appended yet
165 auditor_cores TEXT[] = ARRAY[]::TEXT[]; -- Core auditor function list (filled inside of loop)
166 core_column TEXT; -- The current core column we are adding
169 WITH audit_tables AS ( -- Basic grab of auditor tables. Anything in the auditor namespace, basically. With oids.
170 SELECT c.oid AS audit_oid, c.relname AS audit_table
171 FROM pg_catalog.pg_class c
172 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
173 WHERE relkind='r' AND nspname = 'auditor'
175 table_set AS ( -- Union of auditor tables with their "main" tables. With oids.
176 SELECT a.audit_oid, a.audit_table, c.oid AS main_oid, n.nspname as main_namespace, c.relname as main_table
177 FROM pg_catalog.pg_class c
178 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
179 JOIN audit_tables a ON a.audit_table = n.nspname || '_' || c.relname || '_history'
182 column_lists AS ( -- All columns associated with the auditor or main table, grouped by the main table's oid.
183 SELECT DISTINCT ON (main_oid, attname) t.main_oid, a.attname
185 JOIN pg_catalog.pg_attribute a ON a.attrelid IN (t.main_oid, t.audit_oid)
186 WHERE attnum > 0 AND NOT attisdropped
188 column_defs AS ( -- The motherload, every audit table and main table plus column names and defs.
192 a.attname AS main_column, -- These two will be null for columns that have since been deleted, or for auditor core columns
193 pg_catalog.format_type(a.atttypid, a.atttypmod) AS main_column_def,
194 b.attname AS audit_column, -- These two will be null for columns that have since been added
195 pg_catalog.format_type(b.atttypid, b.atttypmod) AS audit_column_def
197 JOIN column_lists c USING (main_oid)
198 LEFT JOIN pg_catalog.pg_attribute a ON a.attname = c.attname AND a.attrelid = t.main_oid AND a.attnum > 0 AND NOT a.attisdropped
199 LEFT JOIN pg_catalog.pg_attribute b ON b.attname = c.attname AND b.attrelid = t.audit_oid AND b.attnum > 0 AND NOT b.attisdropped
201 -- Nice sorted output from the above
202 SELECT * FROM column_defs WHERE main_column_def IS DISTINCT FROM audit_column_def ORDER BY main_namespace, main_table, main_column, audit_column
204 IF current_table <> (cr.main_namespace || '.' || cr.main_table) THEN -- New table?
205 FOR core_column IN SELECT DISTINCT unnest(auditor_cores) LOOP -- Update missing core auditor columns
206 IF NOT alter_t THEN -- Add ALTER TABLE if we haven't already
207 query:=query || $$ALTER TABLE auditor.$$ || current_audit_table;
210 query:=query || $$,$$;
212 -- Bit of a sneaky bit here. Create audit_id as a bigserial so it gets automatic values and doesn't complain about nulls when becoming a PRIMARY KEY.
213 query:=query || $$ ADD COLUMN $$ || CASE WHEN core_column = 'audit_id bigint' THEN $$audit_id bigserial PRIMARY KEY$$ ELSE core_column END;
215 IF alter_t THEN -- Open alter table = needs a semicolon
216 query:=query || $$; $$;
218 IF 'audit_id bigint' = ANY(auditor_cores) THEN -- We added a primary key...
219 -- Fun! Drop the default on audit_id, drop the auto-created sequence, create a new one, and set the current value
220 -- For added fun, we have to execute in chunks due to the parser checking setval/currval arguments at parse time.
222 EXECUTE $$ALTER TABLE auditor.$$ || current_audit_table || $$ ALTER COLUMN audit_id DROP DEFAULT; $$ ||
223 $$CREATE SEQUENCE auditor.$$ || current_audit_table || $$_pkey_seq;$$;
224 EXECUTE $$SELECT setval('auditor.$$ || current_audit_table || $$_pkey_seq', currval('auditor.$$ || current_audit_table || $$_audit_id_seq')); $$ ||
225 $$DROP SEQUENCE auditor.$$ || current_audit_table || $$_audit_id_seq;$$;
229 -- New table means we reset the list of needed auditor core columns
230 auditor_cores = ARRAY['audit_id bigint', 'audit_time timestamp with time zone', 'audit_action text', 'audit_user integer', 'audit_ws integer'];
231 -- And store some values for use later, because we can't rely on cr in all places.
232 current_table:=cr.main_namespace || '.' || cr.main_table;
233 current_audit_table:=cr.audit_table;
235 IF cr.main_column IS NULL AND cr.audit_column LIKE 'audit_%' THEN -- Core auditor column?
236 -- Remove core from list of cores
237 SELECT INTO auditor_cores array_agg(core) FROM unnest(auditor_cores) AS core WHERE core != (cr.audit_column || ' ' || cr.audit_column_def);
238 ELSIF cr.main_column IS NULL THEN -- Main column doesn't exist, and it isn't an auditor column. Needs dropping from the auditor.
240 query:=query || $$ALTER TABLE auditor.$$ || current_audit_table;
243 query:=query || $$,$$;
245 query:=query || $$ DROP COLUMN $$ || cr.audit_column;
246 ELSIF cr.audit_column IS NULL AND cr.main_column IS NOT NULL THEN -- New column auditor doesn't have. Add it.
248 query:=query || $$ALTER TABLE auditor.$$ || current_audit_table;
251 query:=query || $$,$$;
253 query:=query || $$ ADD COLUMN $$ || cr.main_column || $$ $$ || cr.main_column_def;
254 ELSIF cr.main_column IS NOT NULL AND cr.audit_column IS NOT NULL THEN -- Both sides have this column, but types differ. Fix that.
256 query:=query || $$ALTER TABLE auditor.$$ || current_audit_table;
259 query:=query || $$,$$;
261 query:=query || $$ ALTER COLUMN $$ || cr.audit_column || $$ TYPE $$ || cr.main_column_def;
264 FOR core_column IN SELECT DISTINCT unnest(auditor_cores) LOOP -- Repeat this outside of the loop to catch the last table
266 query:=query || $$ALTER TABLE auditor.$$ || current_audit_table;
269 query:=query || $$,$$;
271 -- Bit of a sneaky bit here. Create audit_id as a bigserial so it gets automatic values and doesn't complain about nulls when becoming a PRIMARY KEY.
272 query:=query || $$ ADD COLUMN $$ || CASE WHEN core_column = 'audit_id bigint' THEN $$audit_id bigserial PRIMARY KEY$$ ELSE core_column END;
274 IF alter_t THEN -- Open alter table = needs a semicolon
275 query:=query || $$;$$;
276 IF 'audit_id bigint' = ANY(auditor_cores) THEN -- We added a primary key...
277 -- Fun! Drop the default on audit_id, drop the auto-created sequence, create a new one, and set the current value
278 -- For added fun, we have to execute in chunks due to the parser checking setval/currval arguments at parse time.
280 EXECUTE $$ALTER TABLE auditor.$$ || current_audit_table || $$ ALTER COLUMN audit_id DROP DEFAULT; $$ ||
281 $$CREATE SEQUENCE auditor.$$ || current_audit_table || $$_pkey_seq;$$;
282 EXECUTE $$SELECT setval('auditor.$$ || current_audit_table || $$_pkey_seq', currval('auditor.$$ || current_audit_table || $$_audit_id_seq')); $$ ||
283 $$DROP SEQUENCE auditor.$$ || current_audit_table || $$_audit_id_seq;$$;
289 $BODY$ LANGUAGE plpgsql;
291 -- Update it all routine
292 CREATE OR REPLACE FUNCTION auditor.update_auditors() RETURNS boolean AS $BODY$
298 -- Drop Lifecycle view(s) before potential column changes
301 FROM pg_catalog.pg_class c
302 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
303 WHERE relkind = 'v' AND n.nspname = 'auditor' LOOP
304 EXECUTE $$ DROP VIEW auditor.$$ || auditor_name || $$;$$;
306 -- Fix all column discrepencies
307 PERFORM auditor.fix_columns();
308 -- Re-create trigger functions and lifecycle views
309 FOR table_schema, table_name IN
310 WITH audit_tables AS (
311 SELECT c.oid AS audit_oid, c.relname AS audit_table
312 FROM pg_catalog.pg_class c
313 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
314 WHERE relkind='r' AND nspname = 'auditor'
317 SELECT a.audit_oid, a.audit_table, c.oid AS main_oid, n.nspname as main_namespace, c.relname as main_table
318 FROM pg_catalog.pg_class c
319 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
320 JOIN audit_tables a ON a.audit_table = n.nspname || '_' || c.relname || '_history'
323 SELECT main_namespace, main_table FROM table_set LOOP
325 PERFORM auditor.create_auditor_func(table_schema, table_name);
326 PERFORM auditor.create_auditor_lifecycle(table_schema, table_name);
330 $BODY$ LANGUAGE plpgsql;