Data audit table..is it being used?

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();

···

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

Hi Jason,

We have the service for this already, however the code that calls the service maybe be reverted.
I will find out and fix it ASAP.

Best regards,
Quang

···

On Thu, Feb 3, 2011 at 4:36 AM, 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();

Jason P. Pickering

email: jason.p.pickering@gmail.com

tel:+260974901293


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

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();

:slight_smile:

I do see the value of moving this out of public schema. Maybe you
could document such an auditable implementation process.

···

On 2 February 2011 21:36, Jason Pickering <jason.p.pickering@gmail.com> wrote:

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

_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp

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();

:slight_smile:

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

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

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

···

On 16 February 2011 04:48, Jason Pickering <jason.p.pickering@gmail.com> wrote:

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, &quot;name&quot;, parentid, shortname, code,
       openingdate, closeddate, active, &quot;comment&quot;, geocode, featuretype,
       coordinates, url,  contactperson, address, email,
       phonenumber, sortorder\)
VALUES \(
       old\.organisationunitid, old\.uuid, old\.&quot;name&quot;,

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, &quot;name&quot;, 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();

:slight_smile:

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: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp

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

LOL. Yeah, reminds me of a Frasier episode. :slight_smile:

That code was a bit too green for public consumption, but oh well.

Well, the reason I had to do this was because I had seen the
datavalue_audit table, and assumed it work. It doesn't. Preferring SQL
to Java, this is what I came up with. Yes, in terms of the expense of
this, it is a bit unknown at this point. It could potentially be too
much to each and every change in the data value table. We may have to
back off this if it appears to be overwhelming. However, I am also
working under the assumption that there are many more inserts than
updates and deletes.

One unintended side effect it seems is that the tables (even though
they are in a separate schema) may be picked up by DHIS2 during
startup. Not a problem to rename them.

I will commit this stuff to the /resources/sql directory in case there
is more interest from others.

Regards,
Jason

···

On 2/16/11, Bob Jolliffe <bobjolliffe@gmail.com> wrote:

On 16 February 2011 04:48, Jason Pickering <jason.p.pickering@gmail.com> > 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 :slight_smile:

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, &quot;name&quot;, parentid, shortname, code,
       openingdate, closeddate, active, &quot;comment&quot;, 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, &quot;name&quot;, 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();

:slight_smile:

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

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