lada/lada-server: db_schema/audit.sql annotate

annotate db_schema/audit.sql @ 1335:b9eb9229df21

Accept values for 'umweltbereich' with length of 1 - 3 characters.

author	Raimund Renkert <raimund.renkert@intevation.de>
date	Thu, 06 Apr 2017 10:08:47 +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;

Mercurial > lada > lada-server

annotate db_schema/audit.sql @ 1335:b9eb9229df21