dhis2-devs team mailing list archive

[Edward Ari Bichetero <ebichete@xxxxxxxxx>]

Public bug reported:

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.

** Affects: dhis2
     Importance: Undecided
         Status: New

-- 
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