dhis2-devs team mailing list archive

[Lars Helge Øverland <larshelge@xxxxxxxxx>]

Hi Edward,

you got me there, you are right. If it was fixed on 2011-11-03 it means it
was included as far back as DHIS 2.6.

The problem now is that 1) data element UIDs are used in custom data entry
forms, indicator formulas and validation rule formulas. E.g. in places
which is not protected by relational constraints. 2) if you have any
third-party system interoperability set up based on UIDs then the exchange
must be updated.

To get valid UIDs would be as simple as blanking the uid values and
restarting DHIS, letting the system generate new UIDs. But that will give
you a lot of trouble later on. I there are only two options; manually
fixing the 150 data elements and grep through forms and expressions; or
quoting table column names.





On Mon, Jun 17, 2013 at 3:23 PM, Edward Ari Bichetero <ebichete@xxxxxxxxx>wrote:

> A quick look shows we have about 1000 orgunits and 150 dataelements with
> this problem (I've only checked orgunits, dataelements and datasets).
> The proper fix would be to wait until there was a new release (or WAR
> drop) with a "UIDTableAlteror.java" or "UIDUpgrader.java" that converted
> the UIDs properly. I don't know how long that will take or, given you
> schedule, when you guys will be able to get around to it ?
>
> If it will take a while, is there any guidance you can give on the
> procedure and consequences of replacing the non-compliant UIDs with new
> ones ?
>
> We can take this discussion to email if the particulars of our situation
> are polluting the bug report.
>
> --
> You received this bug notification because you are a member of DHIS 2
> developers, which is subscribed to DHIS.
> https://bugs.launchpad.net/bugs/1191698
>
> Title:
>   Analytics creation causes SQL syntax error
>
> Status in DHIS 2 - District Health Information Software:
>   New
>
> Bug description:
>   The analytics tables use DHIS2 UIDs as fieldnames in a number of
>   tables. However the rules for a table column's name are restricted by
>   the rules for any identifiers in the SQL language.  Namely:
>
>   - Identifiers  and key words must begin with a letter or an underscore.
> Subsequent characters in an identifier or key word can be letters,
> underscores, digits (0-9), or dollar signs ($)
>   - Quoted identifiers can contain any character, except the character
> with code zero. (To include a double quote, write two double quotes.) This
> allows constructing table or column names that would otherwise not be
> possible, such as ones containing spaces or ampersands
>
>   The SQL generated during the analytics creation process uses unquoted
>   identifiers and therefore any UID involved that starts with a digit
>   will cause a syntax error.
>
>   There are two possible solutions to this problem:
>
>   1) Add quotes to to all identifiers.
>   2) Prefix UID derived identifiers with a compatible fixed string like
> "UID" ("5AfiAeRLQkm" => "UID5AfiAeRLQkm")
>
>   The choice of route will depend on how difficult it is to change. If
>   all we require to quote identifiers is modifying an ORM setting then
>   route 1 is the obvious choice.
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/dhis2/+bug/1191698/+subscriptions
>
> _______________________________________________
> 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
>