[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Gnumed-devel] attempt at audit trailing
From: |
Karsten Hilbert |
Subject: |
[Gnumed-devel] attempt at audit trailing |
Date: |
Mon, 12 May 2003 21:57:16 +0200 |
User-agent: |
Mutt/1.3.22.1i |
Dear all,
please comment on the following scheme of things regarding the
audit trail:
Consider the tables:
--------------------------------------------
create table clin_narrative (
pk_narr serial primary key,
narrative text
);
create table clin_item (
pk_item serial primary key,
id_encounter integer not null references clin_encounter(id),
id_episode integer not null references clin_episode(id)
) inherits (clin_narrative);
create table allergy (
id serial primary key,
substance varchar(128) not null,
substance_code varchar(256) default null,
generics varchar(256) default null,
allergene varchar(256) default null,
atc_code varchar(32) default null,
id_type integer not null references _enum_allergy_type(id),
reaction text default '',
generic_specific boolean default false,
definate boolean default false
) inherits (clin_item, audit_mark);
create table audit_mark (
pk_audit serial primary key,
row_version integer default 0,
modify_when timestamp with time zone check
(modify_when=CURRENT_TIMESTAMP),
modify_by name check (modify_by=CURRENT_USER)
);
create table audit_log (
pk_audit serial primary key,
orig_version integer not null default 0,
orig_when timestamp with time zone not null,
orig_by name not null,
orig_tableoid oid not null,
modify_action varchar(6) check (modify_action in ('UPDATE', 'DELETE')),
modify_when timestamp with time zone check
(modify_when=CURRENT_TIMESTAMP),
modify_by name check (modify_by=CURRENT_USER),
modify_why text not null
);
--------------------------------------------
Audit_mark marks tables for audit trigger generation. Audit_log
is the ancestor for tables that the audit trail is logged into.
Given this schema server/bootstrap/gmAuditSchemaGenerator.py
generates the following audit schema:
--------------------------------------------
DROP TABLE "log_allergy";
CREATE TABLE "log_allergy" () INHERITS (audit_log);
DROP FUNCTION f_log_allergy(text);
CREATE FUNCTION f_log_allergy(text) RETURNS OPAQUE AS '
DECLARE
reason alias for $1;
BEGIN
-- explicitely increment row version counter
NEW.row_version := OLD.row_version + 1;
INSERT INTO log_allergy (
-- auditing metadata
orig_version, orig_when, orig_by, orig_tableoid, modify_action,
modify_why,
-- table content
pk_narr, narrative, pk_item, id_encounter, id_episode,
pk_audit, row_version, modify_when, modify_by, id, substance, substance_code,
generics, allergene, atc_code, id_type, reaction, generic_specific, definate
) VALUES (
-- auditing metadata
OLD.row_version, OLD.modify_when, OLD.modify_by, TG_RELID,
TG_OP, reason,
-- table content
OLD.pk_narr, OLD.narrative, OLD.pk_item, OLD.id_encounter,
OLD.id_episode, OLD.pk_audit, OLD.row_version, OLD.modify_when, OLD.modify_by,
OLD.id, OLD.substance, OLD.substance_code, OLD.generics, OLD.allergene,
OLD.atc_code, OLD.id_type, OLD.reaction, OLD.generic_specific, OLD.definate
);
return NEW;
END' LANGUAGE 'plpgsql';
DROP TRIGGER tr_log_allergy ON f_log_allergy;
CREATE TRIGGER tr_log_allergy
BEFORE UPDATE OR DELETE
ON allergy
FOR EACH ROW EXECUTE PROCEDURE f_log_allergy;
--------------------------------------------
Note:
- selects are not audited
- inserts are audited virtue of the fields in audit_mark only,
no explicit audit record is created in the audit tables
Questions:
- did I miss something or screw up somewhere ?
- do we want to automatically create the attribute list in
"create table ... () inherits ...;" ?
- is it possible/useful to pass a "reason" to the trigger
function ?
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
[Prev in Thread] |
Current Thread |
[Next in Thread] |
- [Gnumed-devel] attempt at audit trailing,
Karsten Hilbert <=