dhis2-devs team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

Agree. The sequence should not be messed with, but I would reccommend
instead that you follow another workflow, but one which does not
invlove Access. Using Kettle, just import your table into postgres,
lets say into private.provinces

First start with the country.

 INSERT INTO organisationunit(organisationunitid, name, shortname,
parentid, active,openingdate)
VALUES(nextval('hibernate_sequence'::regclass),' Country
Foo','CtryFoo',NULL,'true','2001-01-01');

This will now be sure the root value has a proper organisationunitid.
Note that the parentid is set to NULL as it should be for a root
orgunit.


The table from private.properties might look like this initially.

name: Province 1
parentname: Country Foo
organisationunitid: NULL
parentid: NULL

After wards,  you would need to set the parentid to that of the
country, and then do something like

UPDATE private.province SET organisationunitid =
nextval('hibernate_sequence'::regclass);
UPDATE private.province SET parentid = a.organisationunitid from
organisationunit where name ~*('Country Foo');

The tuple above might look like after these statements

name Province 1
parentname Country Foo
organisationunitid 101
parentid 100

After that you can safely inject the province layer by creating a
series of INSERT statements by something like this..

SELECT
'INSERT INTO organisationunit (organisationunitid,name, parentid,
shortname, active, comment) VALUES (' a.organisationunitid::text ||
',E''' ||
a.name
|| ''',' ||
 a.parentid::text || ', E''' ||
 substring(regexp_replace(substring(COALESCE(a.name) from 0),'[
,()/-]','','g') from 0 for 25)
 || ''',''true'',''Some new facilities');'
  from private.province a

This outline of a procedure will ensure that this situation never
happens. Granted, for your use case (initial import using Access) the
method you outline may work, but seems risky.

Best regards,
Jasn


On Wed, Dec 7, 2011 at 1:36 PM, Knut Staring <knutst@xxxxxxxxx> wrote:
> Thanks, Jason, you are quite right that one really shouldn't fiddle with the
> value of the sequence in an operational database. Also, in general, it is
> preferable to do all imports into the DHIS2 database through the XML import
> functionality rather than direct SQL injection.
>
> The use case that triggered my email is relatively special: When setting up
> a new database, one usually starts with building the hierarchy based on
> various sources of provinces, districts and facilities (often GIS
> shapefiles). If this really is the only layer one is working with, using an
> external ID might work. However, I agree that it is possible to do this
> cleanly accessing the sequence, which is why I included the second link.
>
> It is also highly preferable if one can have a unique code (usually numeric)
> for each orgunit, enables DHIS2 to serve as a Master Facility List for
> synching with other applications.
>
> Knut
>
>
> On Wed, Dec 7, 2011 at 12:17 PM, Jason Pickering
> <jason.p.pickering@xxxxxxxxx> wrote:
>>
>> Hi Knut,
>>
>> I am really not sure how or why this would ever happen. Are you using
>> DXF to import hierarchies? If so, then this would seem to be a bug.
>>
>> If you are manually injecting SQL into the database, you must be very
>> careful NOT to use externally generated IDs. It is a recipe for
>> disaster.
>>
>> Follow this pattern instead..
>>
>> INSERT INTO organisationunit(organisationunitid, name, shortname,
>> parentid, active,openingdate)
>> VALUES(nextval('hibernate_sequence'::regclass),'ad Federal Muslim
>> Women Association in Nigeria Adamawa (FOMWAN
>>
>> A)','adFederalMuslimWomenAssociationinNigeriaAdamawaF',501,'true','2001-01-01');
>>
>> Notice instead of setting the organisationunitid, i use the nextval of
>> the hibernate_sequence. Of course the injection becomes a bit more
>> complex, as you have to be sure that you get all of the parentIDs
>> correct, but if you do it organisation layer by organisation layer,
>> then it is relatively easy.
>>
>> Why would you want to set the value to 1010? I guess this is an
>> example, but the issue is how do you actually know what the value
>> should be? The hibernate_sequence is used through out the application,
>> so wouldn't you need to determine the maximum value of all possible
>> identifiers in all database objects and then to set the sequence value
>> to one greater than the maximum?
>>
>> I am just not sure we should reccommend to anyone to start fiddling
>> with the sequence, as the consequences could be really messy to try
>> and clean up unless you are very careful.
>>
>> Regards,
>> Jason
>>
>>
>>
>> On Wed, Dec 7, 2011 at 1:00 PM, Knut Staring <knutst@xxxxxxxxx> wrote:
>> > When importing metadata (e.g. orgunit hierarchies), one sometimes ends
>> > up
>> > using externally generated IDs, that will then conflict with the
>> > Postgres
>> > hibernate_sequence. If this is not resolved, new orgunits added through
>> > DHIS2 will give errors when DHIS2 gets nextval from the sequence and
>> > tries
>> > an insert. The current value of the sequence can be set like this:
>> >
>> >  SELECT setval('hibernate_sequence', 1010);
>> >
>> > http://www.java2s.com/Code/PostgreSQL/Sequence/Usingsetvalfunctiontosetsequencevalue.htm
>> >
>> > It should also be possible to call the sequence directly over ODBC:
>> > http://www.techonthenet.com/access/queries/passthrough2.php
>> >
>> > Knut
>> >
>> > _______________________________________________
>> > 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
>> >
>
>
>
>
> --
> Knut Staring
> Informatics, U. of Oslo
> http://hisp.uio.no
> +4791880522
>