dhis2-devs team mailing list archive

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

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

 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@xxxxxxxxx> wrote:
> 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
>>
>


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