monitoring orgunit changes

orgunits effectively have coded parameters, for example Type and Ownership.

A problem is that if these are changed (eg. change type to ‘Health Centre’), there is no simple way to know whether this orgunit has been updated or not. This is important if you want to relay changes from the dhis server to another system. And presumably affects our FRED implementation.

Attached workaround uses an sql trigger to force an orgunit timestamp when any of its group memberships change. This is obviously postgres specific and would need to be translated for the likes of mysql.

This works like a charm, but is it the desired behaviour? Depends really on how we choose to interpret lastUpdated. If it is, should we consider dhis taking care of this itself.

If this is the wrong approach can anyone suggest another?

Bob

trigger.sql (1.02 KB)

Hi Bob,

you are right. The technical explanation is that we update the timestamp in the service layer whenever the org unit (applies to all kind of objects) is explicitly updated - not when associations are updated on the other end.

I guess the problem is that some use-cases require different behavior here - for example we use the timestamps to control when the org unit tree must be re-downloaded to the client/browser after changes and here we do not want to force an update when only group associations are changed.

So maybe we need to introduce more timestamp properties to handle this ideally. Not sure.

Lars

···

On Mon, Oct 14, 2013 at 6:41 PM, Bob Jolliffe bobjolliffe@gmail.com wrote:

orgunits effectively have coded parameters, for example Type and Ownership.

A problem is that if these are changed (eg. change type to ‘Health Centre’), there is no simple way to know whether this orgunit has been updated or not. This is important if you want to relay changes from the dhis server to another system. And presumably affects our FRED implementation.

Attached workaround uses an sql trigger to force an orgunit timestamp when any of its group memberships change. This is obviously postgres specific and would need to be translated for the likes of mysql.

This works like a charm, but is it the desired behaviour? Depends really on how we choose to interpret lastUpdated. If it is, should we consider dhis taking care of this itself.

If this is the wrong approach can anyone suggest another?

Bob


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,

you are right. The technical explanation is that we update the timestamp
in the service layer whenever the org unit (applies to all kind of objects)
is explicitly updated - not when associations are updated on the other end.

Yes that is correct. We are not really linking the object model fully
through the hibernate layer (which would imply cascading and the like). So
the timestamp on the orgunit table relates only to those properties of the
orgunit which are stored in the table. So what we call the orgunit
"object" in terms of persistence is a little fuzzy.

I guess the problem is that some use-cases require different behavior here
- for example we use the timestamps to control when the org unit tree must
be re-downloaded to the client/browser after changes and here we do not
want to force an update when only group associations are changed.

Ah I was worried this might be the case. (I thought we used a version
object?).

Short term, since I have no option currently except getting down and dirty
in the sql to workaround, I guess I should instead create a new table for
updated orgunit ids, but whose update stamp takes into account groups,
attributes and what have you. And leave the orgUnit lastUpdated untouched.

There might be a secondary benefit of having a separate table for logging
orgunit updates via trigger .. I could possibly also record the deletions
which is currently another missing functionality in the picture. Hopefully
will get time to look more at this tomorrow.

Cheers
Bob

···

On 15 October 2013 14:34, Lars Helge Øverland <larshelge@gmail.com> wrote:

So maybe we need to introduce more timestamp properties to handle this
ideally. Not sure.

Lars

On Mon, Oct 14, 2013 at 6:41 PM, Bob Jolliffe <bobjolliffe@gmail.com>wrote:

orgunits effectively have coded parameters, for example Type and
Ownership.

A problem is that if these are changed (eg. change type to 'Health
Centre'), there is no simple way to know whether this orgunit has been
updated or not. This is important if you want to relay changes from the
dhis server to another system. And presumably affects our FRED
implementation.

Attached workaround uses an sql trigger to force an orgunit timestamp
when any of its group memberships change. This is obviously postgres
specific and would need to be translated for the likes of mysql.

This works like a charm, but is it the desired behaviour? Depends really
on how we choose to interpret lastUpdated. If it is, should we consider
dhis taking care of this itself.

If this is the wrong approach can anyone suggest another?

Bob

_______________________________________________
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

Short term, since I have no option currently except getting down and dirty
in the sql to workaround, I guess I should instead create a new table for
updated orgunit ids, but whose update stamp takes into account groups,
attributes and what have you. And leave the orgUnit lastUpdated untouched.

There might be a secondary benefit of having a separate table for logging
orgunit updates via trigger .. I could possibly also record the deletions
which is currently another missing functionality in the picture. Hopefully
will get time to look more at this tomorrow.

Okay. Could another temporary work-around be to just check for recent
updates of org unit groups through the web api?

Hi Bob,

I think what you are really looking for potentially is a full-blown audit table for orgunits. Perhaps this is overkill, but agree that a custom trigger is probably the way to go with this and allow you to fine time exactly what gets audited. Downside of course is, you would need some way of working with this table. In one implementation I work on, we audit the datavalue table. Values whose period age (meaning the difference in time between the time of data entry and the period for which data is entered for) are audited into a separate audit table, which we work with through custom reports. We only consider UPDATES and DELETES, with two separate triggers

datavalue_audit_delete AFTER DELETE ON datavalue FOR EACH ROW WHEN (get_period_age(old.periodid) >= ‘30 days’::interval) EXECUTE PROCEDURE audit _routine_data()

datavalue_audit_update AFTER UPDATE ON datavalue FOR EACH ROW WHEN (get_period_age(old.periodid) >= ‘30 days’::interval AND old.value::text IS DISTINCT FROM new.value::text) EXECUTE PROCEDURE audit_routine_data()

Note that we use different conditions when the trigger happens, so I guess in your case, you could fine tune the trigger to your specific conditions, rather than perhaps auditing absolutely every change. It seems that the code in your case is important. Of course, using immutable codes to begin with would have been much better, but I suppose that was not a possibility. I would then put a trigger which looks for a change in the code, and then audit those, or redo the timestamp if putting it into a separate audit table is not going to work for you. The audit_routine_data() function looks like this.

CREATE OR REPLACE FUNCTION public.audit_routine_data()

RETURNS trigger

LANGUAGE plpgsql

AS $function$

DECLARE

changed boolean;

BEGIN

IF TG_OP = ‘UPDATE’ then

INSERT INTO audit.datavalue_audit(dataelementid, periodid,sourceid,

categoryoptioncomboid, value, storedby,lastupdated, comment, value_new, changedby, comment_new, operation)

VALUES (old.dataelementid, old.periodid,old.sourceid,

old.categoryoptioncomboid,old.value, old.storedby,old.lastupdated, old.comment, new.value,new.storedby, new.comment, TG_OP);

RETURN old;

ELSIF TG_OP = ‘DELETE’ then

INSERT INTO audit.datavalue_audit(dataelementid, periodid,sourceid,

categoryoptioncomboid, value, storedby,lastupdated, comment, value_new, changedby, comment_new, operation)

VALUES (old.dataelementid, old.periodid,old.sourceid,

old.categoryoptioncomboid,old.value, old.storedby,old.lastupdated, old.comment, NULL,NULL, NULL, TG_OP);

RETURN old;

END IF;

END;

Again, slightly different operations depending on what is happening. I think you could use a similar approach for the orgunits and tweak it to fire when you wanted it to.

Regards,

Jason

···

On Tue, Oct 15, 2013 at 6:18 PM, Lars Helge Øverland larshelge@gmail.com wrote:


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

Okay. Could another temporary work-around be to just check for recent updates of org unit groups through the web api?

Short term, since I have no option currently except getting down and dirty in the sql to workaround, I guess I should instead create a new table for updated orgunit ids, but whose update stamp takes into account groups, attributes and what have you. And leave the orgUnit lastUpdated untouched.

There might be a secondary benefit of having a separate table for logging orgunit updates via trigger … I could possibly also record the deletions which is currently another missing functionality in the picture. Hopefully will get time to look more at this tomorrow.

I don’t think so. If there are 1000 orgunits in a group and one changes I don’t really have a way of knowing which one changed. So I would have to update all orgunuts in that group.

If it is dhis2dhis then just resynching the group would work. But most systems with coded properties, like ‘type’, ‘ownership’ don’t use the same groups idea.

What I need is some indication of when an orgunit object (including its attributes and group memberships have changed). This is a blind spot in our api currently.

···

On 15 October 2013 16:18, Lars Helge Øverland larshelge@gmail.com wrote:

Okay. Could another temporary work-around be to just check for recent updates of org unit groups through the web api?

Short term, since I have no option currently except getting down and dirty in the sql to workaround, I guess I should instead create a new table for updated orgunit ids, but whose update stamp takes into account groups, attributes and what have you. And leave the orgUnit lastUpdated untouched.

There might be a secondary benefit of having a separate table for logging orgunit updates via trigger … I could possibly also record the deletions which is currently another missing functionality in the picture. Hopefully will get time to look more at this tomorrow.

That's true.

···

On Tue, Oct 15, 2013 at 5:38 PM, Bob Jolliffe <bobjolliffe@gmail.com> wrote:

I don't think so. If there are 1000 orgunits in a group and one changes I
don't really have a way of knowing which one changed. So I would have to
update all orgunuts in that group.

If it is dhis2dhis then just resynching the group would work. But most
systems with coded properties, like 'type', 'ownership' don't use the same
groups idea.

What I need is some indication of when an orgunit object (including its
attributes and group memberships have changed). This is a blind spot in
our api currently.

Here’s a clarifying example. In a facility-centric view of orgunits, their coded properties are part of the orgunit rather than some disconnected group concept. So a simple FRED example would look like:

{

“name”: “Nyanza DH”,

“coordinates”: [

29.7493,

-2.3526

],

“active”: true,

“properties”: {

“Fosaid”: “49”,

“Dhis2UID”: “a7LjsllVnD4”,

“ownership”: “Public”,

“Type”: “DH”

},

}

Now I have this data in dhis2 (amongst the orgunit and related groups tables) but no way of knowing whether one of the last two properties has been updated.

Anyway I’ll solve it in sql and we can consider what the longer term solution should be. Maybe an additional uberLastUpdated column on the orgunit table :slight_smile:

Bob

···

On 15 October 2013 16:38, Bob Jolliffe bobjolliffe@gmail.com wrote:

I don’t think so. If there are 1000 orgunits in a group and one changes I don’t really have a way of knowing which one changed. So I would have to update all orgunuts in that group.

If it is dhis2dhis then just resynching the group would work. But most systems with coded properties, like ‘type’, ‘ownership’ don’t use the same groups idea.

What I need is some indication of when an orgunit object (including its attributes and group memberships have changed). This is a blind spot in our api currently.

On 15 October 2013 16:18, Lars Helge Øverland larshelge@gmail.com wrote:

Okay. Could another temporary work-around be to just check for recent updates of org unit groups through the web api?

Short term, since I have no option currently except getting down and dirty in the sql to workaround, I guess I should instead create a new table for updated orgunit ids, but whose update stamp takes into account groups, attributes and what have you. And leave the orgUnit lastUpdated untouched.

There might be a secondary benefit of having a separate table for logging orgunit updates via trigger … I could possibly also record the deletions which is currently another missing functionality in the picture. Hopefully will get time to look more at this tomorrow.