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

annotate db_schema/audit.sql @ 1314:1a3e86f4d768

Fix broken array and improve readability of function definition.

author	Tom Gottfried <tom@intevation.de>
date	Fri, 17 Mar 2017 13:00:31 +0100
parents	c2df9c848e9d
children	84bb7e2aecb1

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
1307 c2df9c848e9d Record inserts in audit table. Raimund Renkert <raimund.renkert@intevation.de> parents: 1298 diff changeset	84 -- Do nothing on delete.
c2df9c848e9d Record inserts in audit table. Raimund Renkert <raimund.renkert@intevation.de> parents: 1298 diff changeset	85 IF (TG_OP = 'DELETE') THEN
1298 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
1307 c2df9c848e9d Record inserts in audit table. Raimund Renkert <raimund.renkert@intevation.de> parents: 1298 diff changeset	94 NEW.id, -- object_id
1298 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;
1307 c2df9c848e9d Record inserts in audit table. Raimund Renkert <raimund.renkert@intevation.de> parents: 1298 diff changeset	109 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	110 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	111 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	112 ELSE
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	113 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	114 RETURN NULL;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	115 END IF;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	116 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	117 RETURN NULL;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	118 END;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	119 $body$
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	120 LANGUAGE plpgsql
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	121 SECURITY DEFINER
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	122 SET search_path = land, public;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	123
1314 1a3e86f4d768 Fix broken array and improve readability of function definition. Tom Gottfried <tom@intevation.de> parents: 1307 diff changeset	124 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	125 target_table regclass,
1a3e86f4d768 Fix broken array and improve readability of function definition. Tom Gottfried <tom@intevation.de> parents: 1307 diff changeset	126 audit_rows boolean,
1a3e86f4d768 Fix broken array and improve readability of function definition. Tom Gottfried <tom@intevation.de> parents: 1307 diff changeset	127 audit_query_text boolean,
1a3e86f4d768 Fix broken array and improve readability of function definition. Tom Gottfried <tom@intevation.de> parents: 1307 diff changeset	128 ignored_cols text[]
1a3e86f4d768 Fix broken array and improve readability of function definition. Tom Gottfried <tom@intevation.de> parents: 1307 diff changeset	129 ) RETURNS void AS
1a3e86f4d768 Fix broken array and improve readability of function definition. Tom Gottfried <tom@intevation.de> parents: 1307 diff changeset	130 $body$
1298 ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	131 DECLARE
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	132 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	133 _q_txt text;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	134 _ignored_cols_snip text = '';
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	135 BEGIN
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	136 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	137 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	138
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	139 IF audit_rows THEN
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	140 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	141 _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	142 END IF;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	143 _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	144 quote_ident(target_table::TEXT) \|\|
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	145 ' 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	146 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	147 RAISE NOTICE '%',_q_txt;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	148 EXECUTE _q_txt;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	149 stm_targets = 'TRUNCATE';
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	150 ELSE
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	151 END IF;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	152
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	153 _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	154 target_table \|\|
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	155 ' 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	156 quote_literal(audit_query_text) \|\| ');';
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	157 RAISE NOTICE '%',_q_txt;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	158 EXECUTE _q_txt;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	159
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	160 END;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	161 $body$
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	162 language 'plpgsql';
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 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	165 Add auditing support to a table.
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	166
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	167 Arguments:
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	168 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	169 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	170 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	171 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	172 $body$;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	173
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	174 -- 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	175 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	176 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	177 $body$ LANGUAGE SQL;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	178
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	179 -- 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	180 -- 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	181 --
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	182 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	183 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	184 $body$ LANGUAGE 'sql';
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	185
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	186 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	187 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	188 $body$;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	189
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	190 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	191 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	192
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	193 -- View for probe audit trail
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	194 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	195 SELECT
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	196 id,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	197 table_name,
1307 c2df9c848e9d Record inserts in audit table. Raimund Renkert <raimund.renkert@intevation.de> parents: 1298 diff changeset	198 action,
1298 ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	199 object_id,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	200 tstamp,
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 integer) AS messungs_id,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	202 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	203 (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	204 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	205 row_data,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	206 changed_fields
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	207 FROM audit_trail;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	208
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	209
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	210 -- View for messung audit trail
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	211 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	212 SELECT audit_trail.id,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	213 audit_trail.table_name,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	214 audit_trail.tstamp,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	215 audit_trail.action,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	216 audit_trail.object_id,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	217 audit_trail.row_data,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	218 audit_trail.changed_fields,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	219 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	220 FROM audit_trail;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	221
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	222
1307 c2df9c848e9d Record inserts in audit table. Raimund Renkert <raimund.renkert@intevation.de> parents: 1298 diff changeset	223 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	224 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	225 '{id, tree_modified, letzte_aenderung, status}'::text[]);
1307 c2df9c848e9d Record inserts in audit table. Raimund Renkert <raimund.renkert@intevation.de> parents: 1298 diff changeset	226 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	227 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	228 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	229 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	230 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	231
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	232 SET search_path TO public;

Mercurial > lada > lada-server

annotate db_schema/audit.sql @ 1314:1a3e86f4d768