annotate db_schema/audit.sql @ 1344:4c82db65c32e 2.6.x

changed version to 2.6-SNAPSHOT after releas2.6.2 for new 2.6.x branch
author Marco Lechner, Bundesamt fuer Strahlenschutz, SW 2.1 <mlechner@bfs.de>
date Fri, 07 Apr 2017 11:13:38 +0200
parents d3113d594013
children
rev   line source
1298
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
1 -- Based on https://github.com/xdimedrolx/audit-trigger/
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
2 --
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
3 -- which is licensed by "The PostgreSQL License", effectively equivalent to the BSD
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
4 -- license.
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
5
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
6 SET search_path TO land;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
7 CREATE TABLE audit_trail(
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
8 id bigserial primary key,
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
9 table_name varchar(50) not null,
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
10 tstamp TIMESTAMP WITHOUT TIME ZONE NOT NULL,
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
11 action varchar(1) NOT NULL CHECK (action IN ('I','D','U', 'T')),
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
12 object_id integer not null,
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
13 row_data JSONB,
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
14 changed_fields JSONB
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
15 );
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
16
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
17 CREATE OR REPLACE FUNCTION jsonb_delete_left(a jsonb, b jsonb)
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
18 RETURNS jsonb AS
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
19 $BODY$
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
20 SELECT COALESCE(
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
21 (
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
22 SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}')
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
23 FROM jsonb_each(a)
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
24 WHERE NOT ('{' || to_json(key) || ':' || value || '}')::jsonb <@ b
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
25 )
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
26 , '{}')::jsonb;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
27 $BODY$
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
28 LANGUAGE sql IMMUTABLE STRICT;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
29 COMMENT ON FUNCTION jsonb_delete_left(jsonb, jsonb) IS 'delete matching pairs in second argument from first argument';
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
30 DROP OPERATOR IF EXISTS - (jsonb, jsonb);
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
31 CREATE OPERATOR - ( PROCEDURE = jsonb_delete_left, LEFTARG = jsonb, RIGHTARG = jsonb);
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
32 COMMENT ON OPERATOR - (jsonb, jsonb) IS 'delete matching pairs from left operand';
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
33
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
34
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
35 CREATE OR REPLACE FUNCTION jsonb_delete_left(a jsonb, b text[])
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
36 RETURNS jsonb AS
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
37 $BODY$
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
38 SELECT COALESCE(
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
39 (
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
40 SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}')
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
41 FROM jsonb_each(a)
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
42 WHERE key <> ALL(b)
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
43 )
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
44 , '{}')::jsonb;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
45 $BODY$
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
46 LANGUAGE sql IMMUTABLE STRICT;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
47 COMMENT ON FUNCTION jsonb_delete_left(jsonb, text[]) IS 'delete keys in second argument from first argument';
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
48 DROP OPERATOR IF EXISTS - (jsonb, text[]);
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
49 CREATE OPERATOR - ( PROCEDURE = jsonb_delete_left, LEFTARG = jsonb, RIGHTARG = text[]);
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
50 COMMENT ON OPERATOR - (jsonb, text[]) IS 'delete keys from left operand';
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
51
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
52
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
53 CREATE OR REPLACE FUNCTION if_modified_func() RETURNS TRIGGER AS $body$
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
54 DECLARE
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
55 audit_row land.audit_trail;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
56 include_values boolean;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
57 log_diffs boolean;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
58 h_old jsonb;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
59 h_new jsonb;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
60 excluded_cols text[] = ARRAY[]::text[];
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
61 BEGIN
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
62 IF TG_WHEN <> 'AFTER' THEN
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
63 RAISE EXCEPTION 'land.if_modified_func() may only run as an AFTER trigger';
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
64 END IF;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
65
1307
c2df9c848e9d Record inserts in audit table.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1298
diff changeset
66 -- Do nothing on delete.
c2df9c848e9d Record inserts in audit table.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1298
diff changeset
67 IF (TG_OP = 'DELETE') THEN
1298
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
68 RETURN NULL;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
69 END IF;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
70
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
71 audit_row = ROW(
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
72 nextval('land.audit_trail_id_seq'), -- id
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
73 TG_TABLE_NAME::varchar, -- table_name
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
74 current_timestamp, -- tstamp
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
75 substring(TG_OP,1,1), -- action
1307
c2df9c848e9d Record inserts in audit table.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1298
diff changeset
76 NEW.id, -- object_id
1298
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
77 NULL, NULL -- row_data, changed_fields
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
78 );
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
79
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
80 IF TG_ARGV[1] IS NOT NULL THEN
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
81 excluded_cols = TG_ARGV[1]::text[];
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
82 END IF;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
83
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
84 IF (TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW') THEN
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
85 audit_row.row_data = row_to_json(OLD)::JSONB;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
86 audit_row.changed_fields = row_to_json(NEW)::JSONB - audit_row.row_data - excluded_cols;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
87 IF audit_row.changed_fields = '{}'::jsonb THEN
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
88 -- All changed fields are ignored. Skip this update.
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
89 RETURN NULL;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
90 END IF;
1307
c2df9c848e9d Record inserts in audit table.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1298
diff changeset
91 ELSIF (TG_OP = 'INSERT' AND TG_LEVEL = 'ROW') THEN
1325
d3113d594013 Exclude probe/messung foreign keys in audit trail.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1315
diff changeset
92 audit_row.row_data = row_to_json(NEW)::JSONB;
1307
c2df9c848e9d Record inserts in audit table.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1298
diff changeset
93 audit_row.changed_fields = jsonb_strip_nulls(row_to_json(NEW)::JSONB - excluded_cols);
1298
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
94 ELSE
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
95 RAISE EXCEPTION '[land.if_modified_func] - Trigger func added as trigger for unhandled case: %, %',TG_OP, TG_LEVEL;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
96 RETURN NULL;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
97 END IF;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
98 INSERT INTO land.audit_trail VALUES (audit_row.*);
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
99 RETURN NULL;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
100 END;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
101 $body$
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
102 LANGUAGE plpgsql
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
103 SECURITY DEFINER
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
104 SET search_path = land, public;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
105
1314
1a3e86f4d768 Fix broken array and improve readability of function definition.
Tom Gottfried <tom@intevation.de>
parents: 1307
diff changeset
106 CREATE OR REPLACE FUNCTION audit_table(
1a3e86f4d768 Fix broken array and improve readability of function definition.
Tom Gottfried <tom@intevation.de>
parents: 1307
diff changeset
107 target_table regclass,
1a3e86f4d768 Fix broken array and improve readability of function definition.
Tom Gottfried <tom@intevation.de>
parents: 1307
diff changeset
108 audit_rows boolean,
1a3e86f4d768 Fix broken array and improve readability of function definition.
Tom Gottfried <tom@intevation.de>
parents: 1307
diff changeset
109 audit_query_text boolean,
1a3e86f4d768 Fix broken array and improve readability of function definition.
Tom Gottfried <tom@intevation.de>
parents: 1307
diff changeset
110 ignored_cols text[]
1a3e86f4d768 Fix broken array and improve readability of function definition.
Tom Gottfried <tom@intevation.de>
parents: 1307
diff changeset
111 ) RETURNS void AS
1a3e86f4d768 Fix broken array and improve readability of function definition.
Tom Gottfried <tom@intevation.de>
parents: 1307
diff changeset
112 $body$
1298
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
113 DECLARE
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
114 stm_targets text = 'INSERT OR UPDATE OR DELETE OR TRUNCATE';
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
115 _q_txt text;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
116 _ignored_cols_snip text = '';
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
117 BEGIN
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
118 EXECUTE 'DROP TRIGGER IF EXISTS audit_trigger_row ON ' || quote_ident(target_table::TEXT);
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
119 EXECUTE 'DROP TRIGGER IF EXISTS audit_trigger_stm ON ' || quote_ident(target_table::TEXT);
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
120
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
121 IF audit_rows THEN
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
122 IF array_length(ignored_cols,1) > 0 THEN
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
123 _ignored_cols_snip = ', ' || quote_literal(ignored_cols);
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
124 END IF;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
125 _q_txt = 'CREATE TRIGGER audit_trigger_row AFTER INSERT OR UPDATE OR DELETE ON ' ||
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
126 quote_ident(target_table::TEXT) ||
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
127 ' FOR EACH ROW EXECUTE PROCEDURE land.if_modified_func(' ||
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
128 quote_literal(audit_query_text) || _ignored_cols_snip || ');';
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
129 RAISE NOTICE '%',_q_txt;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
130 EXECUTE _q_txt;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
131 stm_targets = 'TRUNCATE';
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
132 ELSE
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
133 END IF;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
134
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
135 _q_txt = 'CREATE TRIGGER audit_trigger_stm AFTER ' || stm_targets || ' ON ' ||
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
136 target_table ||
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
137 ' FOR EACH STATEMENT EXECUTE PROCEDURE land.if_modified_func('||
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
138 quote_literal(audit_query_text) || ');';
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
139 RAISE NOTICE '%',_q_txt;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
140 EXECUTE _q_txt;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
141
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
142 END;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
143 $body$
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
144 language 'plpgsql';
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
145
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
146 COMMENT ON FUNCTION audit_table(regclass, boolean, boolean, text[]) IS $body$
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
147 Add auditing support to a table.
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
148
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
149 Arguments:
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
150 target_table: Table name, schema qualified if not on search_path
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
151 audit_rows: Record each row change, or only audit at a statement level
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
152 audit_query_text: Record the text of the client query that triggered the audit event?
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
153 ignored_cols: Columns to exclude from update diffs, ignore updates that change only ignored cols.
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
154 $body$;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
155
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
156 -- Pg doesn't allow variadic calls with 0 params, so provide a wrapper
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
157 CREATE OR REPLACE FUNCTION audit_table(target_table regclass, audit_rows boolean, audit_query_text boolean) RETURNS void AS $body$
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
158 SELECT audit_table($1, $2, $3, ARRAY[]::text[]);
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
159 $body$ LANGUAGE SQL;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
160
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
161 -- And provide a convenience call wrapper for the simplest case
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
162 -- of row-level logging with no excluded cols and query logging enabled.
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
163 --
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
164 CREATE OR REPLACE FUNCTION audit_table(target_table regclass) RETURNS void AS $body$
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
165 SELECT audit_table($1, BOOLEAN 't', BOOLEAN 't');
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
166 $body$ LANGUAGE 'sql';
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
167
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
168 COMMENT ON FUNCTION audit_table(regclass) IS $body$
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
169 Add auditing support to the given table. Row-level changes will be logged with full client query text. No cols are ignored.
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
170 $body$;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
171
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
172 CREATE INDEX probe_id_ndx ON audit_trail(cast("row_data"->>'probe_id' AS int));
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
173 CREATE INDEX messung_id_ndx ON audit_trail(cast("row_data"->>'messung_id' AS int));
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
174
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
175 -- View for probe audit trail
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
176 CREATE OR REPLACE VIEW audit_trail_probe AS
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
177 SELECT
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
178 id,
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
179 table_name,
1307
c2df9c848e9d Record inserts in audit table.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1298
diff changeset
180 action,
1298
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
181 object_id,
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
182 tstamp,
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
183 cast(row_data ->> 'messungs_id' AS integer) AS messungs_id,
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
184 coalesce(cast(row_data ->> 'probe_id' AS integer),
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
185 (SELECT probe_id FROM messung WHERE id = cast(
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
186 row_data ->> 'messungs_id' AS integer))) AS probe_id,
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
187 row_data,
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
188 changed_fields
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
189 FROM audit_trail;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
190
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
191
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
192 -- View for messung audit trail
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
193 CREATE OR REPLACE VIEW audit_trail_messung AS
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
194 SELECT audit_trail.id,
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
195 audit_trail.table_name,
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
196 audit_trail.tstamp,
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
197 audit_trail.action,
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
198 audit_trail.object_id,
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
199 audit_trail.row_data,
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
200 audit_trail.changed_fields,
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
201 cast(row_data ->> 'messungs_id' AS int) AS messungs_id
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
202 FROM audit_trail;
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
203
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
204
1307
c2df9c848e9d Record inserts in audit table.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1298
diff changeset
205 SELECT audit_table('probe', true, false, '{id, tree_modified, letzte_aenderung}'::text[]);
1325
d3113d594013 Exclude probe/messung foreign keys in audit trail.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1315
diff changeset
206 SELECT audit_table('messung', true, false, '{id, probe_id, tree_modified, letzte_aenderung, status}'::text[]);
d3113d594013 Exclude probe/messung foreign keys in audit trail.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1315
diff changeset
207 SELECT audit_table('messwert', true, false, '{id, messungs_id, tree_modified, letzte_aenderung}'::text[]);
d3113d594013 Exclude probe/messung foreign keys in audit trail.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1315
diff changeset
208 SELECT audit_table('kommentar_p', true, false, '{id, probe_id, tree_modified, letzte_aenderung}'::text[]);
d3113d594013 Exclude probe/messung foreign keys in audit trail.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1315
diff changeset
209 SELECT audit_table('kommentar_m', true, false, '{id, messungs_id, tree_modified, letzte_aenderung}'::text[]);
d3113d594013 Exclude probe/messung foreign keys in audit trail.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1315
diff changeset
210 SELECT audit_table('zusatz_wert', true, false, '{id, probe_id, tree_modified, letzte_aenderung}'::text[]);
d3113d594013 Exclude probe/messung foreign keys in audit trail.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1315
diff changeset
211 SELECT audit_table('ortszuordnung', true, false, '{id, probe_id, tree_modified, letzte_aenderung}'::text[]);
1298
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
212
ba47994c1665 Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff changeset
213 SET search_path TO public;
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)