Hi Bob,
Hmm..auditing the audit table. Now that would be a pretty good idea.
Taking this off the list until you have a look at the procedure, which
I actually whipped up this morning after our chat yesterday and a
realization that the other one was not really sufficient.
First, a few comments about the datavalue_audit table of DHIS
The dataaudit table has a constraint..
CONSTRAINT fk_datavalueaudit_datavalue FOREIGN KEY (dataelementid,
periodid, sourceid, categoryoptioncomboid)
REFERENCES datavalue (dataelementid, periodid, sourceid,
categoryoptioncomboid)
which seems to indicate that a datavalue audit row must reference some
row in the data value table. I see a couple of problems with this,
especially over time. What happens if there is a delete? Mustn't this
cascade to the datavalue_audit table as well?
Second problem...If DEs, periods, sources, or
categoryoptioncombos are deleted, there is no longer a reference which
could easily be reconstructed (without historical backups).
My approach (slightly more developed than the last SQL snippet) is to
audit updates and deletes on the data value table, as well as any
deletes to the orgunit, user, or data element table. We are not using
McDonalds (categoryoptioncomboid) so I have not bothered to untangle
this part of the data model, although I suppose by auditing all of the
category tables, it could be done. Furthermore, I initially populate
the DE, orgunit, and userinfo table in the audit schema. I want to
have a sort of portable DB, to allow the people responsible for the
audit report to deal with the data without getting involved with the
full DHIS
Anyway, here is my first pass at a better procedure for datavalue.
Let me know what you think.
Regards,
Jason
CREATE SCHEMA audit;
CREATE USER audit;
GRANT ALL ON SCHEMA audit to audit;
-- Table: audit.datavalue
-- DROP TABLE audit.datavalue;
CREATE TABLE audit.datavalue
(
dataelementid integer,
periodid integer,
sourceid integer,
categoryoptioncomboid integer,
"value" character varying(255),
storedby character varying(31),
lastupdated timestamp with time zone DEFAULT now(),
"comment" character varying(360),
id integer NOT NULL DEFAULT nextval('audit.datavalue_audit_id_seq'::regclass),
CONSTRAINT pk_data_value_audit PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE audit.datavalue OWNER TO audit;
-- Table: dataelement
-- DROP TABLE audit.dataelement;
CREATE SEQUENCE audit.dataelement_audit_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE audit.dataelement_audit_id_seq OWNER TO audit;
CREATE TABLE audit.dataelement
(
id integer NOT NULL DEFAULT
nextval('audit.dataelement_audit_id_seq'::regclass),
dataelementid integer NOT NULL,
uuid character varying(40),
"name" character varying(230) NOT NULL,
alternativename character varying(230),
shortname character varying(25) NOT NULL,
code character varying(100),
description text,
active boolean,
valuetype character varying(16) NOT NULL,
numbertype character varying(16),
domaintype character varying(16),
aggregationtype character varying(16) NOT NULL,
categorycomboid integer,
sortorder integer,
url character varying(255),
lastupdated timestamp without time zone DEFAULT now(),
zeroissignificant boolean,
CONSTRAINT pk_dataelement_audit PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE audit.dataelement OWNER TO audit;
-- Table: organisationunit_audit
CREATE SEQUENCE audit.organisationunit_audit_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE audit.organisationunit_audit_id_seq OWNER TO audit;
-- DROP TABLE audit.organisationunit;
CREATE TABLE audit.organisationunit
(
id integer NOT NULL DEFAULT
nextval('audit.organisationunit_audit_id_seq'::regclass),
organisationunitid integer NOT NULL,
uuid character varying(40),
"name" character varying(230) NOT NULL,
parentid integer,
shortname character varying(50) NOT NULL,
code character varying(25),
openingdate date,
closeddate date,
active boolean,
"comment" character varying(360),
geocode character varying(255),
featuretype character varying(255),
coordinates text,
url character varying(255),
lastupdated timestamp without time zone DEFAULT now(),
contactperson character varying(255),
address character varying(255),
email character varying(150),
phonenumber character varying(150),
sortorder integer,
CONSTRAINT pk_organisationunit_audit PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE organisationunit OWNER TO audit;
CREATE SEQUENCE audit.users_audit_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE audit.users_audit_id_seq OWNER TO audit;
-- DROP TABLE audit.users;
CREATE TABLE audit.userinfo
(
id integer NOT NULL DEFAULT nextval('audit.users_audit_id_seq'::regclass),
userinfoid integer NOT NULL,
surname character varying(160) NOT NULL,
firstname character varying(160) NOT NULL,
email character varying(160),
phonenumber character varying(80),
CONSTRAINT pk_user_info PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE audit.userinfo OWNER TO audit;
--Trigger procedure for orgunit table
CREATE OR REPLACE FUNCTION audit_delete_orgunit() RETURNS trigger AS
$orgunit_audit$
DECLARE
changed boolean;
BEGIN
INSERT INTO audit.organisationunit(
organisationunitid, uuid, "name", parentid, shortname, code,
openingdate, closeddate, active, "comment", geocode, featuretype,
coordinates, url, contactperson, address, email,
phonenumber, sortorder)
VALUES (
old.organisationunitid, old.uuid, old."name",
old.parentid, old.shortname, old.code,
old.openingdate, old.closeddate, old.active,
old."comment", old.geocode, old.featuretype,
old.coordinates, old.url, old.contactperson, old.address,
old.email,
old.phonenumber, old.sortorder);
RETURN old;
END;
$orgunit_audit$
LANGUAGE plpgsql;
CREATE TRIGGER orgunit_audit AFTER DELETE ON organisationunit FOR EACH
ROW EXECUTE PROCEDURE audit_delete_orgunit();
--data element audit
CREATE OR REPLACE FUNCTION audit_dataelement() RETURNS trigger AS
$dataelement_audit$
DECLARE
changed boolean;
BEGIN
INSERT INTO audit.dataelement(
dataelementid, uuid, "name", alternativename, shortname, code,
description, active, valuetype, numbertype, domaintype,
aggregationtype,
categorycomboid, sortorder, url, zeroissignificant)
VALUES ( old.dataelementid, old.uuid, old."name",
old.alternativename, old.shortname, old.code,
old.description, old.active, old.valuetype,
old.numbertype, old.domaintype, old.aggregationtype,
old.categorycomboid, old.sortorder, old.url,
old.zeroissignificant);
RETURN old;
END;
$dataelement_audit$
LANGUAGE plpgsql;
CREATE TRIGGER dataelement_audit AFTER DELETE ON dataelement FOR EACH
ROW EXECUTE PROCEDURE audit_dataelement();
--users audit
CREATE OR REPLACE FUNCTION audit_users() RETURNS trigger AS $users_audit$
DECLARE
changed boolean;
BEGIN
INSERT INTO audit.userinfo(
userinfoid, surname, firstname, email, phonenumber)
VALUES ( old.userinfoid, old.surname, old.firstname, old.email,
old.phonenumber) ;
RETURN old;
END;
$users_audit$
LANGUAGE plpgsql;
CREATE TRIGGER users_audit AFTER DELETE ON userinfo FOR EACH ROW
EXECUTE PROCEDURE audit_users();
--data value audit
CREATE OR REPLACE FUNCTION audit_routine_data() RETURNS trigger AS
$datavalue_audit$
DECLARE
changed boolean;
BEGIN
INSERT INTO audit.datavalue(dataelementid, periodid,sourceid,
categoryoptioncomboid, value, storedby, comment)
VALUES (old.dataelementid, old.periodid,old.sourceid,
old.categoryoptioncomboid,old.value, old.storedby, old.comment);
RETURN old;
END;
$datavalue_audit$
LANGUAGE plpgsql;
CREATE TRIGGER datavalue_audit AFTER UPDATE OR DELETE ON datavalue FOR
EACH ROW EXECUTE PROCEDURE audit_routine_data();
--lastly give the DHIS user INSERT only privileges to this schema
GRANT INSERT ON TABLE audit.datavalue to dhis;
GRANT INSERT ON TABLE audit.dataelement to dhis;
GRANT INSERT ON TABLE audit.organisationunit to dhis;
GRANT INSERT ON TABLE audit.users to dhis;
--Initially populate the audit tables, so we can easily reconstruct
things in the form of a report
INSERT INTO audit.dataelement(
dataelementid, uuid, "name", alternativename, shortname, code,
description, active, valuetype, numbertype, domaintype,
aggregationtype,
categorycomboid, sortorder, url, zeroissignificant)
SELECT dataelementid, uuid, "name", alternativename, shortname, code,
description, active, valuetype, numbertype, domaintype,
aggregationtype,
categorycomboid, sortorder, url, zeroissignificant from
dataelement;
INSERT INTO audit.organisationunit(
organisationunitid, uuid, "name", parentid, shortname, code,
openingdate, closeddate, active, "comment", geocode, featuretype,
coordinates, url, contactperson, address, email,
phonenumber, sortorder)
SELECT organisationunitid, uuid, "name", parentid,
shortname, code,
openingdate, closeddate, active, "comment", geocode, featuretype,
coordinates, url, contactperson, address, email,
phonenumber, sortorder from organisationunit;
INSERT INTO audit.userinfo(
userinfoid, surname, firstname, email, phonenumber)
SELECT userinfoid, surname, firstname, email, phonenumber from userinfo;
···
On 2/15/11, Bob Jolliffe <bobjolliffe@gmail.com> wrote:
On 2 February 2011 21:36, Jason Pickering <jason.p.pickering@gmail.com> > wrote:
I have a question regarding the datavalue_audit table. Is it actually
used for anything? Based on a requirement here, we need to implement a
data audit procedure whereby updates or deletes to values are
recorded. It would seem that changes to data values are not recorded
in the datavalue_audit table/object. I would sort of think that this
was the purpose of the data audit table, but it did not seem to behave
as I thought it would.
A database procedure such as the one at the end of this mail does what
we want, but not sure what the implication of putting values in this
table actually is. Perhaps it better to put it in a separate schema
outside of this public schema used by DHIS2?
Thoughts?
Regards,
JPP
CREATE OR REPLACE FUNCTION update_routine_data() RETURNS trigger AS
$datavalue_audit$
BEGIN
INSERT INTO datavalue_audit(dataelementid, periodid,sourceid,
categoryoptioncomboid,value, storedby, lastupdated,comment)
VALUES (old.dataelementid, old.periodid,old.sourceid,
old.categoryoptioncomboid,old.value, old.storedby,
old.lastupdated,old.comment);
RETURN old;
END;
$datavalue_audit$
LANGUAGE plpgsql;
CREATE TRIGGER dv_audit AFTER UPDATE OR DELETE ON datavalue FOR EACH
ROW EXECUTE PROCEDURE update_routine_data();
This looks fine. Followed by
CREATE TRIGGER dv_auditaudit AFTER UPDATE OR DELETE ON audit FOR EACH
ROW EXECUTE PROCEDURE update_audit_routine_data();
I do see the value of moving this out of public schema. Maybe you
could document such an auditable implementation process.
--
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+260974901293
_______________________________________________
Mailing list: DHIS 2 developers in Launchpad
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : DHIS 2 developers in Launchpad
More help : ListHelp - Launchpad Help
--
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+260974901293