dhis2-devs team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

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@xxxxxxxxx> wrote:
> On 2 February 2011 21:36, Jason Pickering <jason.p.pickering@xxxxxxxxx>
> 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@xxxxxxxxx
>> tel:+260974901293
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~dhis2-devs
>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~dhis2-devs
>> More help   : https://help.launchpad.net/ListHelp
>>
>


-- 
Jason P. Pickering
email: jason.p.pickering@xxxxxxxxx
tel:+260974901293