Mercurial > lada > lada-server

comparison db_schema/audit.sql @ 1307:c2df9c848e9d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
comparison
equal deleted inserted replaced
1306:902bc2190fec 1307:c2df9c848e9d
79 BEGIN 79 BEGIN
80 IF TG_WHEN <> 'AFTER' THEN 80 IF TG_WHEN <> 'AFTER' THEN
81 RAISE EXCEPTION 'land.if_modified_func() may only run as an AFTER trigger'; 81 RAISE EXCEPTION 'land.if_modified_func() may only run as an AFTER trigger';
82 END IF; 82 END IF;
83 83
84 -- Do nothing on insert and delete. 84 -- Do nothing on delete.
85 IF (TG_OP = 'INSERT' OR TG_OP = 'DELETE') THEN 85 IF (TG_OP = 'DELETE') THEN
86 RETURN NULL; 86 RETURN NULL;
87 END IF; 87 END IF;
88 88
89 audit_row = ROW( 89 audit_row = ROW(
90 nextval('land.audit_trail_id_seq'), -- id 90 nextval('land.audit_trail_id_seq'), -- id
91 TG_TABLE_NAME::varchar, -- table_name 91 TG_TABLE_NAME::varchar, -- table_name
92 current_timestamp, -- tstamp 92 current_timestamp, -- tstamp
93 substring(TG_OP,1,1), -- action 93 substring(TG_OP,1,1), -- action
94 OLD.id, -- object_id 94 NEW.id, -- object_id
95 NULL, NULL -- row_data, changed_fields 95 NULL, NULL -- row_data, changed_fields
96 ); 96 );
97 97
98 IF TG_ARGV[1] IS NOT NULL THEN 98 IF TG_ARGV[1] IS NOT NULL THEN
99 excluded_cols = TG_ARGV[1]::text[]; 99 excluded_cols = TG_ARGV[1]::text[];
104 audit_row.changed_fields = row_to_json(NEW)::JSONB - audit_row.row_data - excluded_cols; 104 audit_row.changed_fields = row_to_json(NEW)::JSONB - audit_row.row_data - excluded_cols;
105 IF audit_row.changed_fields = '{}'::jsonb THEN 105 IF audit_row.changed_fields = '{}'::jsonb THEN
106 -- All changed fields are ignored. Skip this update. 106 -- All changed fields are ignored. Skip this update.
107 RETURN NULL; 107 RETURN NULL;
108 END IF; 108 END IF;
109 ELSIF (TG_OP = 'INSERT' AND TG_LEVEL = 'ROW') THEN
110 audit_row.row_data = row_to_json(NEW)::JSONB - excluded_cols;
111 audit_row.changed_fields = jsonb_strip_nulls(row_to_json(NEW)::JSONB - excluded_cols);
109 ELSE 112 ELSE
110 RAISE EXCEPTION '[land.if_modified_func] - Trigger func added as trigger for unhandled case: %, %',TG_OP, TG_LEVEL; 113 RAISE EXCEPTION '[land.if_modified_func] - Trigger func added as trigger for unhandled case: %, %',TG_OP, TG_LEVEL;
111 RETURN NULL; 114 RETURN NULL;
112 END IF; 115 END IF;
113 INSERT INTO land.audit_trail VALUES (audit_row.*); 116 INSERT INTO land.audit_trail VALUES (audit_row.*);
184 -- View for probe audit trail 187 -- View for probe audit trail
185 CREATE OR REPLACE VIEW audit_trail_probe AS 188 CREATE OR REPLACE VIEW audit_trail_probe AS
186 SELECT 189 SELECT
187 id, 190 id,
188 table_name, 191 table_name,
192 action,
189 object_id, 193 object_id,
190 tstamp, 194 tstamp,
191 cast(row_data ->> 'messungs_id' AS integer) AS messungs_id, 195 cast(row_data ->> 'messungs_id' AS integer) AS messungs_id,
192 coalesce(cast(row_data ->> 'probe_id' AS integer), 196 coalesce(cast(row_data ->> 'probe_id' AS integer),
193 (SELECT probe_id FROM messung WHERE id = cast( 197 (SELECT probe_id FROM messung WHERE id = cast(
208 audit_trail.changed_fields, 212 audit_trail.changed_fields,
209 cast(row_data ->> 'messungs_id' AS int) AS messungs_id 213 cast(row_data ->> 'messungs_id' AS int) AS messungs_id
210 FROM audit_trail; 214 FROM audit_trail;
211 215
212 216
213 SELECT audit_table('probe', true, false, '{tree_modified, letzte_aenderung}'::text[]); 217 SELECT audit_table('probe', true, false, '{id, tree_modified, letzte_aenderung}'::text[]);
214 SELECT audit_table('messung', true, false, '{tree_modified, letzte_aenderung, status}'::text[]); 218 SELECT audit_table('messung', true, false, '{id,, tree_modified, letzte_aenderung, status}'::text[]);
215 SELECT audit_table('messwert', true, false, '{tree_modified, letzte_aenderung}'::text[]); 219 SELECT audit_table('messwert', true, false, '{id, tree_modified, letzte_aenderung}'::text[]);
216 SELECT audit_table('kommentar_p', true, false, '{tree_modified, letzte_aenderung}'::text[]); 220 SELECT audit_table('kommentar_p', true, false, '{id, tree_modified, letzte_aenderung}'::text[]);
217 SELECT audit_table('kommentar_m', true, false, '{tree_modified, letzte_aenderung}'::text[]); 221 SELECT audit_table('kommentar_m', true, false, '{id, tree_modified, letzte_aenderung}'::text[]);
218 SELECT audit_table('zusatz_wert', true, false, '{tree_modified, letzte_aenderung}'::text[]); 222 SELECT audit_table('zusatz_wert', true, false, '{id, tree_modified, letzte_aenderung}'::text[]);
219 SELECT audit_table('ortszuordnung', true, false, '{tree_modified, letzte_aenderung}'::text[]); 223 SELECT audit_table('ortszuordnung', true, false, '{id, tree_modified, letzte_aenderung}'::text[]);
220 224
221 SET search_path TO public; 225 SET search_path TO public;
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)