gnumed-devel
[Top][All Lists]
Advanced

[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




reply via email to

[Prev in Thread] Current Thread [Next in Thread]