dhis2-devs team mailing list archive

[Edward Ari Bichetero <ebichete@xxxxxxxxx>]

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