dhis2-devs team mailing list archive

[Lars Helge Øverland <larshelge@xxxxxxxxx>]

Hi Bob,

yes this is correct. We were sort of in a squeeze here. Many people had
reported problems with generating the resource tables resulting from using
non-ascii characters in org unit group set names, which subsequently are
used as column names in the _organisationunitgroupsetstructure table. Only
way to fix that is to quote the column names ( e.g. "Type" ). As a result,
the name becomes case sensitive (using *Type* in a create table SQL command
leads to *type* being the column name, while *"Type"* leads to *Type* being
used, oddly enough). So to fix the problem, we introduced a change which
has potential for breaking existing clients. Not easy to be us sometimes.

Lars



On Thu, Nov 21, 2013 at 11:02 PM, Bob Jolliffe <bobjolliffe@xxxxxxxxx>wrote:

> Just forwarding a query I had from Andrew to the list.  I have observed
> this change in the _organisationgroupsetstructure table which I think might
> cause some grief to those who have written many direct queries against
> resource tables.  Is it just us noticing this?  Was there some change
> recently in the way resource table columns are named (RW just upgraded
> recently to 2.13)?
>
> ---------- Forwarded message ----------
> From: Bob Jolliffe <bobjolliffe@xxxxxxxxx>
> Date: 21 November 2013 21:57
> Subject: Re: Fw: Cron <dhis@hmisdb> psql -c 'select
> materialize_facility_views()' hmis
> To: Muhire Andrew <muhireandrew@xxxxxxxxx>
> Cc: Randy Randy <rwilson@xxxxxxx>
>
>
> That is very odd.  It seems with the upgrade to 2.13 the case of the
> fieldname has been changed from "type" to "Type".  And presumably the same
> with ownership.  I guess this has the effect of breaking many queries.
>  Some examples:
>
> hmis=# select organisationunitname, type from
> _organisationunitgroupsetstructure limit 10;
> ERROR:  column "type" does not exist
> LINE 1: select organisationunitname, "type" from _organisationunitgr...
>
> BUT
>
> hmis=# select organisationunitname, "Type" from
> _organisationunitgroupsetstructure limit 10;
>   organisationunitname  |      Type
>  ------------------------+----------------
>  A La Source DISP       | Dispensary
>  ABEF/Karongi Clinic    | Medical Clinic
>  ADA CLIN               | Medical Clinic
>  Afak DISP              | Dispensary
>  Afya DISP              | Dispensary
>  AHA Clinic             | Dispensary
>  Amahoro Disp           | Dispensary
>  Amani DISP             | Dispensary
>  Amizero (Kacyiru) DISP | Dispensary
>  Amizero (Kamonyi) DISP | Dispensary
>
> That is a pain.  Probably you have to change the materialized views to use
> "Type", "Ownership" etc (the quotes are significant).  This also messes up
> my resourcemapper query :-(
>
> Bob
>
>
>
> On 21 November 2013 18:50, Muhire Andrew <muhireandrew@xxxxxxxxx> wrote:
>
>>
>>  Dear Bob, I don't know if i am right but this is a sign that the
>> datamart failed to finish aggregating these tables. Is it right Bob and
>> Randy? check on Materialized quiries below!
>>
>> ------------------------------
>>
>>
>>
>> * Muhire Andrew*
>> * Ministry of Health / HMIS*
>> *""A mind is a terrible thing to waste""*
>>
>> ***********************************
>>
>>  ** Cell:(+25)0788436150**
>> *** Twitter :andrewmuhire***
>> **** skype:muhire_andrew****
>> *muhireandrew@xxxxxxxxx <muhireandrew@xxxxxxxxx>*
>>
>>
>>   On Thursday, November 21, 2013 7:58 PM, Cron Daemon <
>> root@xxxxxxxxxxxxxxxxx> wrote:
>>  ERROR:  column _organisationunitgroupsetstructure.ownership does not
>> exist
>> LINE 5:  _organisationunitgroupsetstructure.ownership,
>>         ^
>> QUERY:
>> INSERT INTO _view_healthfacilityhierarchy
>> SELECT _organisationunitgroupsetstructure.organisationunitid,
>> _organisationunitgroupsetstructure.organisationunitname,
>> _organisationunitgroupsetstructure.ownership,
>> "O5".name AS sector, "O6".code AS fosaid,
>> _organisationunitgroupsetstructure.type,
>> _orgunitstructure.level,
>> "O4".name AS subdistrict,
>> "O3".name AS district,
>> "O2".name AS province,
>> "O6".coordinates AS gpscoordinates,
>> "O6".openingdate
>>   FROM _orgunitstructure
>>   INNER JOIN _organisationunitgroupsetstructure ON
>>   _organisationunitgroupsetstructure.organisationunitid =
>> _orgunitstructure.organisationunitid
>>   INNER JOIN organisationunit "O4" ON "O4".organisationunitid =
>> _orgunitstructure.idlevel4
>>   INNER JOIN organisationunit "O3" ON _orgunitstructure.idlevel3 =
>> "O3".organisationunitid
>>   INNER JOIN organisationunit "O2" ON "O2".organisationunitid =
>> _orgunitstructure.idlevel2
>> INNER JOIN organisationunit "O5" ON
>> _orgunitstructure.idlevel5 = "O5".organisationunitid
>>   INNER JOIN organisationunit "O6" ON
>> _orgunitstructure.organisationunitid = "O6".organisationunitid
>>   WHERE _orgunitstructure.level = 6;
>> CONTEXT:  PL/pgSQL function materialize_facility_views() line 29 at
>> EXECUTE statement
>>
>>
>>
>
>
> _______________________________________________
> 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
>
>