dhis2-devs team mailing list archive

[Lars Helge Øverland <larshelge@xxxxxxxxx>]

On Mon, Mar 9, 2015 at 10:10 PM, Calle Hedberg <calle.hedberg@xxxxxxxxx>
wrote:

> Lars,
>
> Thanks for the clarification - even if I then do not really understand why
> there's an integrity check for duplicate periods if duplicates are directly
> blocked...??
>

The integrity check is actually checking duplicate period type + start date
combinations (e.g. invalid periods, such as monthly periods with start date
equal to end date). We have seen this sneak in from 3rd party
systems/aggregation query builder and this will confuse our period
generator, hence the check.


>
> As a result of your info, though, I've now reviewed 20-30 different DHIS2
> instances and found that this constraint is missing from all instances
> created&populated using the DHIS14-DHIS2 conversion procedure, whereas the
> other "standard" instances do have the "period-periodtypeid_key". So it
> looks like the conversion procedure drops that constraint during the
> conversion process, without re-creating it after the conversion. I will
> investigate and get it fixed.
>

Okay thanks for the update.

regards,

Lars



>
>
Best regards
> Calle
>
> On 9 March 2015 at 17:52, Lars Helge Øverland <larshelge@xxxxxxxxx> wrote:
>
>> Hi Calle,
>>
>> we do have such a uniqueness constraint on period table. It is called
>> "period_periodtypeid_key". If it is not there then it means it somehow was
>> removed, then duplicates was inserted which prevents hibernate to put it
>> back when starting DHIS. You can try adding it manually with this SQL:
>>
>> ALTER TABLE period ADD CONSTRAINT period_periodtypeid_key
>> UNIQUE(periodtypeid, startdate, enddate);
>>
>> If that fails, please remove the duplicates and run it again.
>>
>> best regards,
>>
>> Lars
>>
>>
>>
>> On Sat, Feb 28, 2015 at 11:39 PM, Calle Hedberg <calle.hedberg@xxxxxxxxx>
>> wrote:
>>
>>> Hi
>>>
>>> I have over the last 2-3 weeks had multiple cases with duplicated PERIOD
>>> records in DHIS2 instances. In the latest case, it looks like capturing of
>>> daily data in some cases created duplicated period records - two records
>>> with identical startdate+enddate+periodtype, and sequential periodid. With
>>> most duplicate pairs, only the first periodid had a record in the datavalue
>>> table.
>>>
>>> I will try to track down the root cause of this duplicate generation,
>>> BUT what I don't understand is the lack of a periodtypeid+startdate+enddate
>>> constraint on the period table.
>>>
>>> Any periodtypeid+startdate+enddate combination would in reality be
>>> unique?
>>>
>>> I see there's an integrity check for it, but a constraint on the table
>>> is better, I would think.
>>>
>>> Besides - where can I view the code/sql used for those integrity checks?
>>>
>>> Regards
>>> calle
>>>
>>> *******************************************
>>>
>>> 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-19274
>>>
>>> Email: calle.hedberg@xxxxxxxxx
>>>
>>> Skype: calle_hedberg
>>>
>>> *******************************************
>>>
>>>
>>> _______________________________________________
>>> 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-19274
>
> Email: calle.hedberg@xxxxxxxxx
>
> Skype: calle_hedberg
>
> *******************************************
>
>