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