dhis2-devs team mailing list archive

[Lars Helge Øverland <larshelge@xxxxxxxxx>]

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