hibernate_sequence

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

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@gmail.com> 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);
Using setval function to set sequence value : setval « Sequence « PostgreSQL

It should also be possible to call the sequence directly over ODBC:
MS Access 2003: Create a pass-through query to retrieve the nextval from an Oracle sequence with VBA code

Knut

_______________________________________________
Mailing list: DHIS 2 developers in Launchpad
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : DHIS 2 developers in Launchpad
More help : ListHelp - Launchpad Help

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@gmail.com 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@gmail.com 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@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp


Knut Staring

Informatics, U. of Oslo

http://hisp.uio.no

+4791880522

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@gmail.com> 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@gmail.com> 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@gmail.com> 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);
>
> Using setval function to set sequence value : setval « Sequence « PostgreSQL
>
> It should also be possible to call the sequence directly over ODBC:
> MS Access 2003: Create a pass-through query to retrieve the nextval from an Oracle sequence with VBA code
>
> Knut
>
> _______________________________________________
> Mailing list: DHIS 2 developers in Launchpad
> Post to : dhis2-devs@lists.launchpad.net
> Unsubscribe : DHIS 2 developers in Launchpad
> More help : ListHelp - Launchpad Help
>

--
Knut Staring
Informatics, U. of Oslo
http://hisp.uio.no
+4791880522