dhis2-devs team mailing list archive

[Knut Staring <knutst@xxxxxxxxx>]

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