dhis2-devs team mailing list archive

[Bob Jolliffe <bobjolliffe@xxxxxxxxx>]

On 16 February 2011 04:48, Jason Pickering <jason.p.pickering@xxxxxxxxx> wrote:
> Hi Bob,
> Hmm..auditing the audit table. Now that would be a pretty good idea.
>
> Taking this off the list

I think you missed that.  You are on air  :-)

>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
>

Had a brief squizz at the sql below and I like the approach.  Not sure
if it would all be mysql compatible but that could be addressed.  And
of course to be generally useful you would have to audit the McDonalds
stuff as well but that is just more of the same.

The existing audit log in dhis was grafted primarily to meet the
demands of certification by Indian State IT body.  I am not sure how
many implementations are actually seriously using it.  Does anybody
know?  For access logging this would probably still be what you would
use.

Your stuff in the audit schema (I like the separate schema) will of
course be pretty expensive both in space and cycles.  But that should
be a quantifiable tradeoff.  For datavalue audit this is pretty much
unavoidable.

Cheers
Bob

>
> 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
>