dhis2-devs team mailing list archive

[Lars Helge Øverland <larshelge@xxxxxxxxx>]

Hi Edward,

thanks for reporting, we did actually think about this and a UID will
always start with a letter [a-z][A-Z], due to SQL column and XML attribute
constraints.


regards,

Lars



On Mon, Jun 17, 2013 at 10:29 AM, Edward Ari Bichetero
<ebichete@xxxxxxxxx>wrote:

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