dhis2-devs team mailing list archive

[Lars Helge Øverland <larshelge@xxxxxxxxx>]

Hi again,

since there were no complaints I have now removed the name columns from the
*groupset resource tables. You can use the uid columns instead. Should now
be no problems with character encoding / Chinese.

Lars



On Tue, Aug 6, 2013 at 5:42 PM, Lars Helge Øverland <larshelge@xxxxxxxxx>wrote:

> Hi,
>
> yes I agree. In fact we could have simply removed the name columns
> altogether. After we introduced the UIDs they are not necessary - at the
> moment we have two columns for each group set in those resouce tables, one
> with names and one with uids. One could arrive at the names with SQL joins
> anyway.
>
> I am reluctant to remove the name columns from 2.12 stable since it might
> break potential SQL-based reports. But we can remove them for 2.13. Are
> there any objections against this?
>
>
> Lars
>
>
>
> On Sat, Jul 27, 2013 at 11:03 AM, Jason Pickering <
> jason.p.pickering@xxxxxxxxx> wrote:
>
>> Hi Easy,
>> I see what you are getting at and it might work, but I am not certain
>> that the column names are being passed as escaped UNICODE. It seems as if
>> they are not, because you should get something as a column name, as opposed
>> to it being blank.
>>
>>  As a test, look at this code (or run in pgAdmin)
>>
>> CREATE TABLE test (免疫 text) ;
>> INSERT INTO test ("免疫") VALUES ('foo');
>> SELECT * FROM test;
>> SELECT "免疫" FROM test;
>> SELECT 免疫 FROM test;
>>
>>
>> #Now the Escaped unicode sequences for these characters
>> CREATE TABLE test2("\u514d\u75ab" text);
>> INSERT INTO test2 ("\u514d\u75ab") VALUES ('bar');
>> SELECT * FROM test2;
>> SELECT "\u514d\u75ab" FROM test2;
>> SELECT \u514d\u75ab FROM test2;  --This fails
>>
>> CREATE table test3( SELECT text);
>>
>> Everything works except the last row, which does not have double quotes.
>>
>> To the devs, it would seem that the use of the regex in this case may not
>> be a good idea because the non-Latin characters are being stripped out,
>> even though they would be allowed as a viable column name. Enclosing all of
>> the column names in double quotes would help obviously, and would ensure
>> that any string could be used as a column name (except reserved SQL words),
>> for instance,
>>
>>
>> CREATE table test3( SELECT text); --Does not work, as it is reserved
>> keyword in SQL
>> CREATE TABLE test3( "SELECT" text); --Works when enclosed in double
>> quotes.
>>
>> Regards,
>> Jason
>>
>>
>>
>>
>> On Sat, Jul 27, 2013 at 8:40 AM, easy <lin_xd@xxxxxxx> wrote:
>>
>>>
>>> package org.hisp.dhis.system.util;
>>> CodecUtils.databaseEncode:
>>> Pattern pattern = Pattern.compile( "[a-zA-Z0-9_]" );
>>> ==>Pattern pattern = Pattern.compile( "[a-zA-Z0-9_\u4e00-\u9fa5]" );
>>>
>>>
>>>
>>> --
>>> 此致
>>>
>>>
>>> 莫愁前路无知己，天下谁人不识君。
>>>
>>> At 2013-07-27 14:24:27,easy <lin_xd@xxxxxxx> wrote:
>>>
>>>
>>> It's true, but we want create DE groupeset name in local words. It's
>>>  easy for user. so should adjust the column name generating method,ok?..
>>>
>>>
>>>
>>> --
>>> 此致
>>>
>>>  easy
>>> 莫愁前路无知己，天下谁人不识君。
>>>
>>> At 2013-07-27 14:18:17,"Jason Pickering" <jason.p.pickering@xxxxxxxxx>
>>> wrote:
>>>
>>> Using Google Translate, the syntax error is related to the attempt to
>>> create a table with a blank column name, namely   VARCHAR (250) (notice
>>> the blank)
>>>
>>> I think the the current methods to generate column names attempt to
>>> remove all non-safe characters, and looks like they are all being stripped
>>> out resulting in a blank column name.
>>>
>>>
>>> Regards,
>>> Jason
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Sat, Jul 27, 2013 at 6:19 AM, easy <lin_xd@xxxxxxx> wrote:
>>>
>>>> that's right, for chinese DE groupset name,create table sql  will get
>>>> null.
>>>> CREATE
>>>> TABLE _dataelementgroupsetstructure ( dataelementid INTEGER NOT NULL,
>>>> dataelementname VARCHA
>>>> R (250), [*this chinese name *] VARCHAR (250), qr7oQXJ4LW9
>>>> CHARACTER(11),  VARCHAR (250), PVBSMpEIesq CHARACTER(11
>>>>
>>>> ), PRIMARY KEY ( dataelementid ) )
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> 此致
>>>>
>>>> easy
>>>> 莫愁前路无知己，天下谁人不识君。
>>>>
>>>> At 2013-07-26 20:36:59,"Lars Helge Øverland" <larshelge@xxxxxxxxx>
>>>> wrote:
>>>>
>>>> Hi easy,
>>>>
>>>> it seems the error message is in chinese (after org.postgresql.util.
>>>> PSQLException: error ), hard for me to understand in order to help,
>>>> any chance you can translate or provide in English?
>>>>
>>>> regards,
>>>>
>>>> Lars
>>>>
>>>>
>>>> On Fri, Jul 26, 2013 at 11:57 AM, easy <lin_xd@xxxxxxx> wrote:
>>>>
>>>>> * ERROR 2013-07-26 17:46:46,431 Error while executing action
>>>>> (ExceptionInterceptor.java [htt
>>>>> p-apr-8080-exec-2])
>>>>> org.springframework.jdbc.BadSqlGrammarException: StatementCallback;
>>>>> bad SQL grammar [CREATE
>>>>> TABLE _dataelementgroupsetstructure ( dataelementid INTEGER NOT NULL,
>>>>> dataelementname VARCHA
>>>>> R (250),  VARCHAR (250), qr7oQXJ4LW9 CHARACTER(11),  VARCHAR (250),
>>>>> PVBSMpEIesq CHARACTER(11
>>>>> ), PRIMARY KEY ( dataelementid ) )]; nested exception is
>>>>> org.postgresql.util.PSQLException:
>>>>> error: 语法错误 在 "(" 或附近的
>>>>>   位置：118
>>>>>         at
>>>>> org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(S
>>>>> QLErrorCodeSQLExceptionTranslator.java:237)
>>>>>         at
>>>>> org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate
>>>>> (AbstractFallbackSQLExceptionTranslator.java:72)
>>>>>         at
>>>>> org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
>>>>>         at
>>>>> org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:428)
>>>>>         at
>>>>> org.hisp.dhis.resourcetable.jdbc.JdbcResourceTableStore.createDataElementGroupSet
>>>>> Structure(JdbcResourceTableStore.java:163)
>>>>>         at
>>>>> org.hisp.dhis.resourcetable.DefaultResourceTableService.generateDataElementGroupS
>>>>> etTable(DefaultResourceTableService.java:276)
>>>>>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>>>>         at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
>>>>>         at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown
>>>>> Source)
>>>>>         at java.lang.reflect.Method.invoke(Unknown Source)
>>>>>         at
>>>>> org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.
>>>>> java:317)
>>>>>         at
>>>>> org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(Refl
>>>>> ectiveMethodInvocation.java:183)
>>>>>         at
>>>>> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMe
>>>>> thodInvocation.java:150)
>>>>>         at
>>>>> org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithI
>>>>> nvocation(TransactionInterceptor.java:96)
>>>>>         at
>>>>> org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithin
>>>>> Transaction(TransactionAspectSupport.java:260)
>>>>>
>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> 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
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>> _______________________________________________
>>>> 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
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>
>