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

annotate db_schema/audit.sql @ 1315:84bb7e2aecb1

Do not redefine existing operator. The removed SQL failed on PostgreSQL 9.5 because the operator is provided and needed by the system. Further, it does not seem to be used here.

author	Tom Gottfried <tom@intevation.de>
date	Fri, 17 Mar 2017 13:25:57 +0100
parents	1a3e86f4d768
children	d3113d594013

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
c2df9c848e9d Record inserts in audit table. Raimund Renkert <raimund.renkert@intevation.de> parents: 1298 diff changeset	92 audit_row.row_data = row_to_json(NEW)::JSONB - excluded_cols;
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[]);
1314 1a3e86f4d768 Fix broken array and improve readability of function definition. Tom Gottfried <tom@intevation.de> parents: 1307 diff changeset	206 SELECT audit_table('messung', true, false,
1a3e86f4d768 Fix broken array and improve readability of function definition. Tom Gottfried <tom@intevation.de> parents: 1307 diff changeset	207 '{id, tree_modified, letzte_aenderung, status}'::text[]);
1307 c2df9c848e9d Record inserts in audit table. Raimund Renkert <raimund.renkert@intevation.de> parents: 1298 diff changeset	208 SELECT audit_table('messwert', true, false, '{id, tree_modified, letzte_aenderung}'::text[]);
c2df9c848e9d Record inserts in audit table. Raimund Renkert <raimund.renkert@intevation.de> parents: 1298 diff changeset	209 SELECT audit_table('kommentar_p', true, false, '{id, tree_modified, letzte_aenderung}'::text[]);
c2df9c848e9d Record inserts in audit table. Raimund Renkert <raimund.renkert@intevation.de> parents: 1298 diff changeset	210 SELECT audit_table('kommentar_m', true, false, '{id, tree_modified, letzte_aenderung}'::text[]);
c2df9c848e9d Record inserts in audit table. Raimund Renkert <raimund.renkert@intevation.de> parents: 1298 diff changeset	211 SELECT audit_table('zusatz_wert', true, false, '{id, tree_modified, letzte_aenderung}'::text[]);
c2df9c848e9d Record inserts in audit table. Raimund Renkert <raimund.renkert@intevation.de> parents: 1298 diff changeset	212 SELECT audit_table('ortszuordnung', true, false, '{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	213
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	214 SET search_path TO public;

Mercurial > lada > lada-server

annotate db_schema/audit.sql @ 1315:84bb7e2aecb1