dhis2-devs team mailing list archive

[Edward Ari Bichetero <ebichete@xxxxxxxxx>]

Looks like the quoting solution was implemented in rev.13039, so marking
as fixed.

- Edward -


** Changed in: dhis2
     Assignee: (unassigned) => Lars Helge Øverland (larshelge)

** Changed in: dhis2
       Status: New => Fix Released

-- 
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:
  Fix Released

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