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

annotate db_schema/audit.sql @ 1298:ba47994c1665

Add audit-trail table/trigger/views to database.

author	Raimund Renkert <raimund.renkert@intevation.de>
date	Fri, 24 Feb 2017 14:27:10 +0100
parents
children	c2df9c848e9d

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 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	54 RETURNS jsonb AS
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	55 $BODY$
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	56 SELECT COALESCE(
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	57 (
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	58 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	59 FROM jsonb_each(a)
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	60 WHERE key <> b
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	61 )
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	62 , '{}')::jsonb;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	63 $BODY$
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	64 LANGUAGE sql IMMUTABLE STRICT;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	65 COMMENT ON FUNCTION jsonb_delete_left(jsonb, text) IS 'delete key in second argument from first argument';
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	66 DROP OPERATOR IF EXISTS - (jsonb, text);
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	67 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	68 COMMENT ON OPERATOR - (jsonb, text) IS 'delete key from left operand';
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	69
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 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	72 DECLARE
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	73 audit_row land.audit_trail;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	74 include_values boolean;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	75 log_diffs boolean;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	76 h_old jsonb;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	77 h_new jsonb;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	78 excluded_cols text[] = ARRAY[]::text[];
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	79 BEGIN
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	80 IF TG_WHEN <> 'AFTER' THEN
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	81 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	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 -- Do nothing on insert and delete.
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	85 IF (TG_OP = 'INSERT' OR TG_OP = 'DELETE') THEN
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	86 RETURN NULL;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	87 END IF;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	88
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	89 audit_row = ROW(
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	90 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	91 TG_TABLE_NAME::varchar, -- table_name
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	92 current_timestamp, -- tstamp
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	93 substring(TG_OP,1,1), -- action
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	94 OLD.id, -- object_id
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	95 NULL, NULL -- row_data, changed_fields
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	96 );
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	97
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	98 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	99 excluded_cols = TG_ARGV[1]::text[];
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	100 END IF;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	101
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	102 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	103 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	104 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	105 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	106 -- 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	107 RETURN NULL;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	108 END IF;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	109 ELSE
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	110 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	111 RETURN NULL;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	112 END IF;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	113 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	114 RETURN NULL;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	115 END;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	116 $body$
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	117 LANGUAGE plpgsql
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	118 SECURITY DEFINER
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	119 SET search_path = land, public;
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 CREATE OR REPLACE FUNCTION audit_table(target_table regclass, audit_rows boolean, audit_query_text boolean, ignored_cols text[]) RETURNS void AS $body$
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	122 DECLARE
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	123 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	124 _q_txt text;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	125 _ignored_cols_snip text = '';
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	126 BEGIN
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	127 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	128 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	129
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	130 IF audit_rows THEN
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	131 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	132 _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	133 END IF;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	134 _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	135 quote_ident(target_table::TEXT) \|\|
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	136 ' 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	137 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	138 RAISE NOTICE '%',_q_txt;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	139 EXECUTE _q_txt;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	140 stm_targets = 'TRUNCATE';
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	141 ELSE
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	142 END IF;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	143
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	144 _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	145 target_table \|\|
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	146 ' 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	147 quote_literal(audit_query_text) \|\| ');';
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	148 RAISE NOTICE '%',_q_txt;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	149 EXECUTE _q_txt;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	150
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	151 END;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	152 $body$
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	153 language 'plpgsql';
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	154
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	155 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	156 Add auditing support to a table.
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	157
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	158 Arguments:
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	159 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	160 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	161 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	162 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	163 $body$;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	164
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	165 -- 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	166 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	167 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	168 $body$ LANGUAGE SQL;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	169
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	170 -- 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	171 -- 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	172 --
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	173 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	174 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	175 $body$ LANGUAGE 'sql';
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	176
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	177 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	178 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	179 $body$;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	180
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	181 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	182 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	183
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	184 -- View for probe audit trail
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	185 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	186 SELECT
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	187 id,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	188 table_name,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	189 object_id,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	190 tstamp,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	191 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	192 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	193 (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	194 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	195 row_data,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	196 changed_fields
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	197 FROM audit_trail;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	198
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	199
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	200 -- View for messung audit trail
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	201 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	202 SELECT audit_trail.id,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	203 audit_trail.table_name,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	204 audit_trail.tstamp,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	205 audit_trail.action,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	206 audit_trail.object_id,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	207 audit_trail.row_data,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	208 audit_trail.changed_fields,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	209 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	210 FROM audit_trail;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	211
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 SELECT audit_table('probe', true, false, '{tree_modified, letzte_aenderung}'::text[]);
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	214 SELECT audit_table('messung', true, false, '{tree_modified, letzte_aenderung, status}'::text[]);
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	215 SELECT audit_table('messwert', true, false, '{tree_modified, letzte_aenderung}'::text[]);
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	216 SELECT audit_table('kommentar_p', true, false, '{tree_modified, letzte_aenderung}'::text[]);
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	217 SELECT audit_table('kommentar_m', true, false, '{tree_modified, letzte_aenderung}'::text[]);
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	218 SELECT audit_table('zusatz_wert', true, false, '{tree_modified, letzte_aenderung}'::text[]);
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	219 SELECT audit_table('ortszuordnung', true, false, '{tree_modified, letzte_aenderung}'::text[]);
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	220
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	221 SET search_path TO public;

Mercurial > lada > lada-server

annotate db_schema/audit.sql @ 1298:ba47994c1665