dhis2-devs team mailing list archive

[Calle Hedberg <calle.hedberg@xxxxxxxxx>]

Hi,

When extracting all "FOREGIN KEY" and "UNIQUE" constraints from a new 2.28
database created from scratch (i.e. create empty database and start up
DHIS2 2.28 pointing to it), you get
- 918 "FOREIGN KEY" constraints
- 420 "UNIQUE" constraints

When doing the same on the 2.28 Play SL demo right now, I got:
- 1,215 "FOREIGN KEY" constraints (the extra ~300 are probably mostly
duplicates)
and more interestingly only 338 "UNIQUE" constraints. So 82 unique
constraints that are supposedly standard for 2.28 are actually missing from
the SL demo.

It's to be noted that all "FOREIGN KEY" constraints are created during
DHIS2 startup, if they don't exist already. That's the reason for all the
duplicates - older versions (up to 2.22 or 2.23, I think) used intuitive
names for the constraints like "fk_categories_categoryoptions_categoryid"
listed above. After that the same constraints were inserted using
autogenerated strings as names, like "fkf453b3bdd24076b3" - and it seems
like these string names have been re-autogenerated again in later versions,
causing the duplication.

"UNIQUE" constraints are, on the other hand, NOT created during startup
(except for a few) if not existing - so here we are faced with the
potentially more serious situation that many of them end up missing,
potentially breaking functionality (example: yesterday it was impossible to
create new users
in one of our instances because missing "UNIQUE" constraints had resulted
in duplicated meta-data.

It is critical that somebody from the core team outlines the consequences
of e.g. missing UNIQUE constraints, and in particular whether any
ready-to-use tools are available for rectifying these data model
inconsistencies.

Regards
Calle

On Wed, 22 Aug 2018 at 18:16, Ant Snyman <ant@xxxxxxxx> wrote:

> Dear Dev,
>
> We have noticed on numerous DHIS2 databases that there sometimes are
> multiple Foreign Key constraints on the same table for the same column. On
> Sierra Leone some examples:
>
>
> categories_categoryoptions FOREIGN KEY
> fk_categories_categoryoptions_categoryid categoryid
> categories_categoryoptions FOREIGN KEY fkf453b3bdd24076b3 categoryid
> categorycombos_categories FOREIGN KEY fk731150b827f29ff categorycomboid
> categorycombos_categories FOREIGN KEY
> fk_categorycombos_categories_categorycomboid categorycomboid
> indicatorgroupmembers FOREIGN KEY fkf01275498be51834 indicatorgroupid
> indicatorgroupmembers FOREIGN KEY
> fk_indicatorgroupmembers_indicatorgroupid indicatorgroupid
>
> organisationunitattributevalues FOREIGN KEY fk601dda02b8e4fd1
> attributevalueid
> organisationunitattributevalues FOREIGN KEY fkcoo6svgtx8pre5fabnjuyhgpf
> attributevalueid
> organisationunitattributevalues FOREIGN KEY fk601dda0fb4caaad
> organisationunitid
> organisationunitattributevalues FOREIGN KEY fk7utgogpv8n5r4yxm41lhd70i0
> organisationunitid
> orgunitgroupmembers FOREIGN KEY fk55fa7022b8b57b9d orgunitgroupid
> orgunitgroupmembers FOREIGN KEY fk_orgunitgroupmembers_orgunitgroupid
> orgunitgroupid
> programstageinstancecomments FOREIGN KEY fk5323f083fc199ca1
> trackedentitycommentid
> programstageinstancecomments FOREIGN KEY fkki1hrag24gcjuq78oafhuk1wh
> trackedentitycommentid
> programstageinstancecomments FOREIGN KEY
> fk_programstageinstancecomments_trackedentitycommentid
> trackedentitycommentid
> reporttable_organisationunits FOREIGN KEY fk4b7becf027251155 reporttableid
> reporttable_organisationunits FOREIGN KEY fk4b7becf03aab0439 reporttableid
> reporttable_organisationunits FOREIGN KEY fk4b7becf0cc2046c1 reporttableid
> Are these "duplicates" a matter of concern and should this be maintained
> regularly by removing them? Do these "duplicates" add to the database
> overheads/performance degradation?
>
> Best Regards
>
> *Ant Snyman*
>
> *Cell: 0824910449*
>
> *Landline: 028 2713242*
>
>
> Health Information Systems Program - SA
>
> *This message and any attachments are subject to a disclaimer published at
> http://www.hisp.org/policies.html#comms_disclaimer
> <http://www.hisp.org/policies.html#comms_disclaimer>.  Please read the
> disclaimer before opening any attachment or taking any other action in
> terms of this electronic transmission.  If you cannot access the
> disclaimer, kindly send an email to disclaimer@xxxxxxxx
> <disclaimer@xxxxxxxx> and a copy will be provided to you. By replying to
> this e-mail or opening any attachment you agree to be bound by the
> provisions of the disclaimer.*
> _______________________________________________
> 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
>


-- 

*******************************************

Calle Hedberg

46D Alma Road, 7700 Rosebank, SOUTH AFRICA

Tel/fax (home): +27-21-685-6472

Cell: +27-82-853-5352

Iridium SatPhone: +8816-315-19119

Email: calle.hedberg@xxxxxxxxx

Skype: calle_hedberg

*******************************************