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

annotate db_schema/audit.sql @ 1307:c2df9c848e9d

Record inserts in audit table.

author	Raimund Renkert <raimund.renkert@intevation.de>
date	Wed, 08 Mar 2017 09:43:46 +0100
parents	ba47994c1665
children	1a3e86f4d768

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
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	124 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	125 DECLARE
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	126 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	127 _q_txt text;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	128 _ignored_cols_snip text = '';
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	129 BEGIN
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	130 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	131 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	132
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	133 IF audit_rows THEN
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	134 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	135 _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	136 END IF;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	137 _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	138 quote_ident(target_table::TEXT) \|\|
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	139 ' 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	140 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	141 RAISE NOTICE '%',_q_txt;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	142 EXECUTE _q_txt;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	143 stm_targets = 'TRUNCATE';
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	144 ELSE
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	145 END IF;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	146
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	147 _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	148 target_table \|\|
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	149 ' 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	150 quote_literal(audit_query_text) \|\| ');';
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	151 RAISE NOTICE '%',_q_txt;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	152 EXECUTE _q_txt;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	153
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	154 END;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	155 $body$
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	156 language 'plpgsql';
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 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	159 Add auditing support to a table.
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 Arguments:
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	162 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	163 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	164 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	165 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	166 $body$;
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 -- 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	169 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	170 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	171 $body$ LANGUAGE SQL;
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 -- 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	174 -- 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	175 --
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	176 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	177 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	178 $body$ LANGUAGE 'sql';
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	179
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	180 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	181 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	182 $body$;
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 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	185 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	186
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	187 -- View for probe audit trail
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	188 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	189 SELECT
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	190 id,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	191 table_name,
1307 c2df9c848e9d Record inserts in audit table. Raimund Renkert <raimund.renkert@intevation.de> parents: 1298 diff changeset	192 action,
1298 ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	193 object_id,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	194 tstamp,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	195 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	196 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	197 (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	198 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	199 row_data,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	200 changed_fields
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	201 FROM audit_trail;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	202
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 -- View for messung audit trail
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	205 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	206 SELECT audit_trail.id,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	207 audit_trail.table_name,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	208 audit_trail.tstamp,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	209 audit_trail.action,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	210 audit_trail.object_id,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	211 audit_trail.row_data,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	212 audit_trail.changed_fields,
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	213 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	214 FROM audit_trail;
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	215
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	216
1307 c2df9c848e9d Record inserts in audit table. Raimund Renkert <raimund.renkert@intevation.de> parents: 1298 diff changeset	217 SELECT audit_table('probe', true, false, '{id, tree_modified, letzte_aenderung}'::text[]);
c2df9c848e9d Record inserts in audit table. Raimund Renkert <raimund.renkert@intevation.de> parents: 1298 diff changeset	218 SELECT audit_table('messung', true, false, '{id,, tree_modified, letzte_aenderung, status}'::text[]);
c2df9c848e9d Record inserts in audit table. Raimund Renkert <raimund.renkert@intevation.de> parents: 1298 diff changeset	219 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	220 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	221 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	222 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	223 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	224
ba47994c1665 Add audit-trail table/trigger/views to database. Raimund Renkert <raimund.renkert@intevation.de> parents: diff changeset	225 SET search_path TO public;

Mercurial > lada > lada-server

annotate db_schema/audit.sql @ 1307:c2df9c848e9d