We (in @solidlines ) are facing the issue of “orphan metadata” (metadata resources that should be linked to another “main” resources, but there are not) in dhis2 instances that are running for a long time.
Do you have another examples to share ?
I would like to compile the different issues that are arising and an approach for cleaning them from the system using SQL.
1. Program Rule Action (PRA) without Program Rule
For instance, a Program Rule Action (PRA) must be linked to a Program Rule (actually, you cannot edit a PRA using the UI). But there are cases were this link is broken.
SELECT * FROM programruleaction WHERE programruleid IS NULL
Or using the API REST:
/api/programRuleActions?filter=programRule:null
In order to remove them, you can go to the db and execute:
DELETE FROM programruleaction WHERE programruleid IS NULL;
2. Option without OptionSet
Another case is Options without a link to a OptionSet. You can know them executing:
SELECT * FROM optionvalue WHERE optionsetid IS NULL;
Or using the API REST:
/api/options?filter=optionSet:null
Sometimes, the option is used in other tables, so prior to remove it, you need to clean the others executing these SQL in order:
SELECT * FROM programruleaction WHERE optionid IN (SELECT optionid FROM optionvalue WHERE optionsetid IS NULL);
UPDATE programruleaction SET optionid = NULL WHERE optionid IN (SELECT optionid FROM optionvalue WHERE optionsetid IS NULL);
After that, and in order to remove them, you can go to the db and execute:
DELETE FROM optionvalue WHERE optionsetid IS NULL;
3. Program Stage Section without Program Stage
Another case is Program Stage Sections without a link to a Program Stage. You can know them executing:
SELECT * FROM programstagesection WHERE programstageid IS NULL;
Or using the API REST:
/api/programStageSections?filter=programStage:null
Sometimes, the program stage section is used in other tables, so prior to remove it, you need to clean the others executing these SQL in order:
UPDATE programruleaction SET programstagesectionid = NULL WHERE programstagesectionid IN (SELECT programstagesectionid FROM programstagesection WHERE programstageid IS NULL);
DELETE FROM programstagesection_programindicators WHERE programstagesectionid IN (SELECT programstagesectionid FROM programstagesection WHERE programstageid IS NULL);
DELETE FROM programstagesection_dataelements WHERE programstagesectionid IN (SELECT programstagesectionid FROM programstagesection WHERE programstageid IS NULL);
After that, and in order to remove them, you can go to the db and execute:
DELETE FROM programstagesection WHERE programstageid IS NULL;
4. Program Stage without Program
Another case is Program Stage without a link to a Program. You can know them executing:
SELECT * from programstage WHERE programid IS NULL;
Or using the API REST:
/api/programStages?filter=program:null
Sometimes, the program stage is used in other tables, so prior to remove it, you need to clean the others, executing these SQL in order:
SELECT * FROM programstagedataelement WHERE programstageid IN (SELECT programstageid from programstage WHERE programid IS NULL);
DELETE FROM programstagedataelement WHERE programstageid IN (SELECT programstageid from programstage WHERE programid IS NULL);
After that, and in order to remove them, you can go to the db and execute:
DELETE FROM programstage WHERE programid IS NULL;