dhis2-devs team mailing list archive

[Knut Staring <knutst@xxxxxxxxx>]

I agree this is tricky and there should be examples and correction in the
docs. Please go ahead and update the docs.
I mentioned Excel because many people have their data in that format, and
it's easy to either create csv in excel or create text strings with the
required xml code, which can then be pasted into a text editor. We should
probably aim to include examples of how to do this also.
On 17 Jan 2015 08:53, "Calle Hedberg" <calle.hedberg@xxxxxxxxx> wrote:

> Hi,
>
> FYI, Lars finally got back to me on those examples I sent him: (a)
> confirming that using Excel for editing XM does not work; (b) pointing
> out that the reason for my CSV file not working was incorrect field
> sequence.
>
> Point (b) highlights a fundamental (and frequent) problem with the
> standard interface, then: the system does not check if the format is
> correct - the import process just fails silently AND the standard
> success message is displayed to the user.
>
> The documentation is not clear - ref user manual chapter 20 for CSV
> meta-data import:
> "DHIS 2 supports import of meta-data in the CSV format. Columns which
> are not required can be omitted in the CSV file, but the order will be
> affected. If you would like to specify columns which appear late in
> the order but not specify columns which appear early in the order you
> can include empty columns ("") for them."
>
> "Not required can be omitted", "you CAN include empty columns" - my
> interpretation of this was that I could just drop columns I did not
> need, but reality is that a far better and easy-to-understand rule
> would be: ALL COLUMNS MUST BE INCLUDED, AND JUST USE BLANK VALUES IN
> ANY COLUMN NOT ACTUALLY REQUIRED.
>
> Even better/more logical would be for the core development team to
> provide a complete set of empty templates - XML, JSON, and CSV - as a
> download from the database (that way, the templates can be updated
> whenever there's a data model change). We've had that type of
> functionality in DHIS 1.x for as long as I can remember. Just add one
> extra menu option under import/export saying "Export all legal
> templates" and then allow the user to select XML, JSON, or CSV.
>
> Regards from Windhoek
> Calle
>
>
>
> On 16/01/2015, Knut Staring <knutst@xxxxxxxxx> wrote:
> > To be frank, it doesn't really matter what you use for I'd as long as it
> is
> > always smaller than the nextval of the hibernate sequence. But in order
> to
> > ensure that, it is best practice to use it.
> > On Jan 16, 2015 11:57 AM, "Bob Jolliffe" <bobjolliffe@xxxxxxxxx> wrote:
> >
> >> Agree Calle.  I too have had to make use of the database directly to
> deal
> >> with some things which are just more difficult or not implemented in the
> >> api.
> >>
> >> So the point is just that if you are making sql insert queries then you
> >> should make use of the nextval function as described by Jason above to
> >> populate the id field.
> >>
> >> On 16 January 2015 at 11:40, Calle Hedberg <calle.hedberg@xxxxxxxxx>
> >> wrote:
> >>
> >>> Hi,
> >>>
> >>> You guys are all correct when saying that it's best to use the
> >>> standard tools and/or the web api - the problem is that DHIS2
> >>> processing is rather opaque and/or the interface simply do not allow
> >>> updating things (try accessing the OrgUnitLevel table if it's empty).
> >>> Example: the other day I exported the OrganisationUnit data using the
> >>> standard meta-data export, made some updates directly in the xml file,
> >>> and imported it again. The system indicates that import is OK and that
> >>> x records were updated - but when I look at the database table itself
> >>> there's no change. I tried again, this time importing updates via CSV
> >>> - same thing, import summary indicates all OK and x records updated,
> >>> but no actual changes in the database. (Sent the files to Lars for a
> >>> check, but no response so I guess he's just too busy). That
> >>> documentation tend to be brief, very techie and outdated .....
> >>>
> >>> I spend a lot of time working on pulling in data from a variety of
> >>> formats and sources. Using the web api etc means (a) lots of
> >>> complicated formatting and many steps; (b) painstakingly having to
> >>> cross-verify that the result of every single step has actually done
> >>> what it's supposed to, because the summary feedback tend to be limited
> >>> or - as shown above - unreliable.
> >>>
> >>> I cannot get "preparing things in Excel" to work either - with the
> >>> example above I tried to open the exported OU xml file in Excel. It
> >>> opens, but with somewhat strange formatting because the metaexport.xml
> >>> file do not have a scheme (at least that excel understand). So excel
> >>> creates a schema - but when trying to export as xml again after
> >>> modifications, it don't work...  It's possible I'm doing something
> >>> wrong, of course - but export xml file, edit it in Excel, save xml
> >>> file, import it, that SHOULD be straightforward ...
> >>>
> >>> Anyway - the bottom line is that building up a database partially
> >>> using direct dbms access make the whole process a lot faster and less
> >>> painful.
> >>>
> >>> Regards from Windhoek
> >>> Calle
> >>>
> >>>
> >>>
> >>>
> >>> On 16/01/2015, Greg Rowles <greg.rowles@xxxxxxxxx> wrote:
> >>> > Thank you Everyone, that leaves our db managers with 3 options then!
> >>> >
> >>> > On Fri, Jan 16, 2015 at 11:29 AM, Knut Staring <knutst@xxxxxxxxx>
> >>> wrote:
> >>> >
> >>> >> Yeah, that was my key question - and I thing it could work ok that
> >>> >> way.
> >>> >> But I would think you could achieve most of what you want through
> >>> >> preparing
> >>> >> things in Excel and then using the Metadata import instead (so you
> >>> don't
> >>> >> have to worry about the internal database IDs)
> >>> >>
> >>> >> On Fri, Jan 16, 2015 at 9:25 AM, Greg Rowles <greg.rowles@xxxxxxxxx
> >
> >>> >> wrote:
> >>> >>
> >>> >>> Let me not forget to mention that this type of work would be
> >>> preparatory
> >>> >>> and not after roll out...
> >>> >>>
> >>> >>> On Fri, Jan 16, 2015 at 11:20 AM, Greg Rowles
> >>> >>> <greg.rowles@xxxxxxxxx>
> >>> >>> wrote:
> >>> >>>
> >>> >>>> Thanks Bob, Jason & Others
> >>> >>>>
> >>> >>>> I agree and understand but we have a DHIS2 implementors who prefer
> >>> >>>> to
> >>> >>>> work directly inside the DBMS who are still new to this complex
> >>> >>>> environment. If they need to insert records directly into tables
> >>> >>>> this
> >>> >>>> gives
> >>> >>>> them the opportunity to do the work they're used to (coming from
> >>> >>>> version
> >>> >>>> 1.4)...
> >>> >>>>
> >>> >>>> Regards,
> >>> >>>> Greg
> >>> >>>>
> >>> >>>>
> >>> >>>> On Fri, Jan 16, 2015 at 11:17 AM, Bob Jolliffe <
> >>> bobjolliffe@xxxxxxxxx>
> >>> >>>> wrote:
> >>> >>>>
> >>> >>>>> Jason is right.  You should only need the hibernate sequence
> >>> >>>>> number
> >>> if
> >>> >>>>> you are dealing with the database through sql. And if you are,
> >>> >>>>> then
> >>> >>>>> the
> >>> >>>>> only safe way to use it is to get the sequence during the
> >>> >>>>> execution
> >>> of
> >>> >>>>> the
> >>> >>>>> statement.
> >>> >>>>>
> >>> >>>>> Getting the next sequence number and storing it to some variable
> >>> with
> >>> >>>>> the aim of using it at some time in the future is inherently
> >>> fragile.
> >>> >>>>>
> >>> >>>>> On 16 January 2015 at 09:10, Jason Pickering <
> >>> >>>>> jason.p.pickering@xxxxxxxxx> wrote:
> >>> >>>>>
> >>> >>>>>> Hi Greg,
> >>> >>>>>> I would tend to agree with Lars that this should really not ever
> >>> >>>>>> be
> >>> >>>>>> required, but it would be good to know what the use case
> actually
> >>> is.
> >>> >>>>>>
> >>> >>>>>> The easiest way to do this would be to create an SQL view in
> >>> >>>>>> DHIS2
> >>> as
> >>> >>>>>>
> >>> >>>>>> SELECT currval('hibernate_sequence')
> >>> >>>>>>
> >>> >>>>>>
> >>> >>>>>> In a multi-user database environment, this value is of course
> >>> >>>>>> volatile, so you can never be sure what the actual current value
> >>> is.
> >>> >>>>>>
> >>> >>>>>> If you really need to inject SQL into your database, then using
> >>> >>>>>> something like 'nextval('hibernate_sequence'::regclass)'in your
> >>> SQL,
> >>> >>>>>> which will provide the next value of the sequence.
> >>> >>>>>>
> >>> >>>>>> Regards,
> >>> >>>>>> Jason
> >>> >>>>>>
> >>> >>>>>> On Fri, Jan 16, 2015 at 9:49 AM, Lars Helge Øverland <
> >>> >>>>>> larshelge@xxxxxxxxx> wrote:
> >>> >>>>>>
> >>> >>>>>>> Hi Greg,
> >>> >>>>>>>
> >>> >>>>>>> that would be easy to implement, but the idea is that it should
> >>> >>>>>>> never
> >>> >>>>>>> be necessary to expose database details and instead work with
> >>> >>>>>>> the
> >>> >>>>>>> API. Do
> >>> >>>>>>> you think you could elaborate a bit on the use-case?
> >>> >>>>>>>
> >>> >>>>>>> Lars
> >>> >>>>>>>
> >>> >>>>>>>
> >>> >>>>>>> On Fri, Jan 16, 2015 at 8:35 AM, Greg Rowles <
> >>> greg.rowles@xxxxxxxxx>
> >>> >>>>>>> wrote:
> >>> >>>>>>>
> >>> >>>>>>>> Hi Devs
> >>> >>>>>>>>
> >>> >>>>>>>> Is there an easy way to expose the last hibernate sequence
> >>> >>>>>>>> value
> >>> >>>>>>>> through the web API?
> >>> >>>>>>>>
> >>> >>>>>>>> Greg
> >>> >>>>>>>>
> >>> >>>>>>>> --
> >>> >>>>>>>> *Health Information Systems Program - South Africa*
> >>> >>>>>>>> *- - - - - - - **- - - - - - - **- - - - - - - **- - - - - - -
> >>> **-
> >>> >>>>>>>> - - - - *
> >>> >>>>>>>> Mobile  :    073 246 2992
> >>> >>>>>>>> Landline:   021 554 3130
> >>> >>>>>>>> Fax:          086 733 8432
> >>> >>>>>>>> Skype:      gregory_rowles
> >>> >>>>>>>>
> >>> >>>>>>>> _______________________________________________
> >>> >>>>>>>> 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
> >>> >>>>>>>>
> >>> >>>>>>>>
> >>> >>>>>>>
> >>> >>>>>>> _______________________________________________
> >>> >>>>>>> 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
> >>> >>>>>>>
> >>> >>>>>>>
> >>> >>>>>>
> >>> >>>>>>
> >>> >>>>>> --
> >>> >>>>>> Jason P. Pickering
> >>> >>>>>> email: jason.p.pickering@xxxxxxxxx
> >>> >>>>>> tel:+46764147049
> >>> >>>>>>
> >>> >>>>>> _______________________________________________
> >>> >>>>>> 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
> >>> >>>>>>
> >>> >>>>>>
> >>> >>>>>
> >>> >>>>> _______________________________________________
> >>> >>>>> 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
> >>> >>>>>
> >>> >>>>>
> >>> >>>>
> >>> >>>>
> >>> >>>> --
> >>> >>>> *Health Information Systems Program - South Africa*
> >>> >>>> *- - - - - - - **- - - - - - - **- - - - - - - **- - - - - - - **-
> >>> >>>> -
> >>> -
> >>> >>>> - - *
> >>> >>>> Mobile  :    073 246 2992
> >>> >>>> Landline:   021 554 3130
> >>> >>>> Fax:          086 733 8432
> >>> >>>> Skype:      gregory_rowles
> >>> >>>>
> >>> >>>
> >>> >>>
> >>> >>>
> >>> >>> --
> >>> >>> *Health Information Systems Program - South Africa*
> >>> >>> *- - - - - - - **- - - - - - - **- - - - - - - **- - - - - - - **-
> -
> >>> - -
> >>> >>> - *
> >>> >>> Mobile  :    073 246 2992
> >>> >>> Landline:   021 554 3130
> >>> >>> Fax:          086 733 8432
> >>> >>> Skype:      gregory_rowles
> >>> >>>
> >>> >>> _______________________________________________
> >>> >>> 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
> >>> >> Dept. of Informatics, University of Oslo
> >>> >> Liberia: +231 770 496 123 or +231 886 146 381
> >>> >> Norway: +4791880522
> >>> >> Skype: knutstar
> >>> >> http://dhis2.org
> >>> >>
> >>> >
> >>> >
> >>> >
> >>> > --
> >>> > *Health Information Systems Program - South Africa*
> >>> > *- - - - - - - **- - - - - - - **- - - - - - - **- - - - - - - **- -
> -
> >>> - -
> >>> > *
> >>> > Mobile  :    073 246 2992
> >>> > Landline:   021 554 3130
> >>> > Fax:          086 733 8432
> >>> > Skype:      gregory_rowles
> >>> >
> >>>
> >>>
> >>> --
> >>>
> >>>
> >>> *******************************************
> >>>
> >>> Calle Hedberg
> >>>
> >>> 46D Alma Road, 7700 Rosebank, SOUTH AFRICA
> >>>
> >>> Tel/fax (home): +27-21-685-6472
> >>>
> >>> Cell: +27-82-853-5352
> >>>
> >>> Iridium SatPhone: +8816-315-19274
> >>>
> >>> Email: calle.hedberg@xxxxxxxxx
> >>>
> >>> Skype: calle_hedberg
> >>>
> >>> *******************************************
> >>>
> >>> _______________________________________________
> >>> 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
> >>>
> >>
> >>
> >> _______________________________________________
> >> 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
> >>
> >>
> >
>
>
> --
>
>
> *******************************************
>
> Calle Hedberg
>
> 46D Alma Road, 7700 Rosebank, SOUTH AFRICA
>
> Tel/fax (home): +27-21-685-6472
>
> Cell: +27-82-853-5352
>
> Iridium SatPhone: +8816-315-19274
>
> Email: calle.hedberg@xxxxxxxxx
>
> Skype: calle_hedberg
>
> *******************************************
>