How to set Sqlite to be case insensitive when unicode string comparing?


Official SQLite docs state:

SQLite only understands upper/lower case for ASCII characters by default. The LIKE operator is case sensitive by default for unicode characters that are beyond the ASCII range.

I want to select records from local database by unicode string matching. But if I use 'LIKE' in the where clause, I found that sqlite is not work UPPER/LOWER with unicode. Can anyone tell me how to use string comparing case-insensitive?

Thank you very much!

Igor Kuznetsov
Found sqlite3.c hack/patch

Found sqlite3.c hack/patch (for russian symbols):

- # define GlogUpperToLower(A)   if( !((A)&~0x7f) ){ A = sqlite3UpperToLower[A]; }
+ # define GlogUpperToLower(A)   if( !((A)&~0x7f) ){ A = sqlite3UpperToLower[A]; } if ( A >= 0x0410 && A <= 0x042f) { A += 0x20; }

No pretty, but work!!!

But stiil wants to enable ICU for all platforms.

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Pietro Francesc...
Hi Igor,if you want, you can

Hi Igor,

if you want, you can reach out to me to see if we can build a business case around this requirements to prioritize it.

Best regards

Pietro

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Igor Kuznetsov
Hi Pietro!  How to organize

Hi Pietro!  How to organize our cooperation?

Here in private messages? 

I am the main technical mobile developer of the Resto.ru program.

https://itunes.apple.com/en/app/resto.ru/id562888926

https://play.google.com/store/apps/details?id=com.resto.resto 

In the current time of the iOS and Android platform, in the future in Window Phone 8 plans.

We use Rhodes 3.5 on Mac Mini and on Linux Debian. 

We use in the application a local database for storage and information search about restaurants.

Names of restaurants are stored in the localized Russian utf8 values.


Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Pietro Francesc...
Hi Igor,two options, on one

Hi Igor,

two options, on one side, as motorola we provaid payd support for the platform. So this can be one route.

Other route, is to issue a ticket on github on the Rhodes project.

What I can suggest is to test this feature on the v4.0 (beta is already available here on launchpad), and if is not enable there, open an issue ticket on github.

Ciao

Pietro

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Igor Kuznetsov
Hi Pietro!To report about ICU

Hi Pietro!

To report about ICU problem, it is necessary to know as correctly to enable this feature.

You can show a correct way in code?

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Pietro Francesc...
Hi Igor,I think that SQLite

Hi Igor,

I think that SQLite can have LIKE working with unicode text, there're the ICU extensions: and these are included in the source code in Rhodes, so probably it already works, but I've not tested it.

If it does not work out of the box, may be it's necessary to add the "collate nocase" SQLite option in the SQL code used to create the tables in RHOM...

~Pietro

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Igor Kuznetsov
I think that SQLite can have

I think that SQLite can have LIKE working with unicode text, there're the ICU extensions: and these are included in the source code in Rhodes, so probably it already works, but I've not tested it.

Hi Pietro!

How enable (and build) ICU functions in Rhodes?

my sql (simple example):

SELECT places.title FROM places WHERE UPPER(places.title, 'ru_ru') LIKE '%ДЕД%';

my log (rhosimulator):

I 06/21/2013 21:42:37:368 0b51f000 APP| exception when running query: could not prepare statement: 1; Message: wrong number of arguments to function UPPER()

I 06/21/2013 21:42:37:369 0b51f000 APP| App error: could not prepare statement: 1; Message: wrong number of arguments to function UPPER()

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Pietro Francesc...
Hi Igor,if you look in the

Hi Igor,

if you look in the Rhodes sources, ICU it's already included in the SQLite library:

https://raw.github.com/rhomobile/rhodes/3-5-stable/platform/shared/sqlite/sqlite3.c

I think that the error you got it's due to your usage of the UPPER function, in SQLite it only has one parameter:

http://www.tutorialspoint.com/sqlite/sqlite_useful_functions.htm

Best regards

Pietro

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Igor Kuznetsov
Pietro Francesco Maggi wrote

Pietro Francesco Maggi wrote:

Hi Igor,

if you look in the Rhodes sources, ICU it's already included in the SQLite library:

https://raw.github.com/rhomobile/rhodes/3-5-stable/platform/shared/sqlite/sqlite3.c

I think that the error you got it's due to your usage of the UPPER function, in SQLite it only has one parameter:

http://www.tutorialspoint.com/sqlite/sqlite_useful_functions.htm

Best regards

Pietro

Hi Pietro!

Sorry, i used old ICU readme http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/icu/README.txt for UPPER function.

ICU included in the SQLite library. But not work!

Tested: Rhosimulator, iPhone Emulator, Android 4.1 device (Asus Padfone)

Questions:

Where define (or set) SQLITE_ENABLE_ICU ?

How use/load/set ICU in Rhodes? (i dont understand)

$ grep -r SQLITE_ENABLE_ICU /Library/Ruby/Gems/1.8/gems/rhodes-3.5.0/

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/shared/sqlite/sqlite3.c:#ifdef SQLITE_ENABLE_ICU

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/shared/sqlite/sqlite3.c:#ifdef SQLITE_ENABLE_ICU

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/shared/sqlite/sqlite3.c:#ifdef SQLITE_ENABLE_ICU

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/shared/sqlite/sqlite3.c:#ifdef SQLITE_ENABLE_ICU

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/shared/sqlite/sqlite3.c:#ifdef SQLITE_ENABLE_ICU

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/shared/sqlite/sqlite3.c:#ifdef SQLITE_ENABLE_ICU

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/shared/sqlite/sqlite3.c:#if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_ICU)

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/shared/sqlite/sqlite3.c:#ifdef SQLITE_ENABLE_ICU

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/shared/sqlite/sqlite3.c:#endif /* defined(SQLITE_ENABLE_ICU) */

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/symbian/sqlite3/src/sqlite3.c:#ifdef SQLITE_ENABLE_ICU

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/symbian/sqlite3/src/sqlite3.c:#ifdef SQLITE_ENABLE_ICU

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/symbian/sqlite3/src/sqlite3.c:#ifdef SQLITE_ENABLE_ICU

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/symbian/sqlite3/src/sqlite3.c:#if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_ICU)

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/symbian/sqlite3/src/sqlite3.c:#ifdef SQLITE_ENABLE_ICU

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/symbian/sqlite3/src/sqlite3.c:#endif /* defined(SQLITE_ENABLE_ICU) */

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/wp7/sqlite3/SqliteWrapper/ctime_cs.cs:#if SQLITE_ENABLE_ICU

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/wp7/sqlite3/SqliteWrapper/main_c.cs:#if SQLITE_ENABLE_ICU

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/wp7/sqlite3/SqliteWrapper/main_c.cs:#if  SQLITE_ENABLE_ICU

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/wp8/sqlite3/sqlite3.c:#ifdef SQLITE_ENABLE_ICU

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/wp8/sqlite3/sqlite3.c:#ifdef SQLITE_ENABLE_ICU

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/wp8/sqlite3/sqlite3.c:#ifdef SQLITE_ENABLE_ICU

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/wp8/sqlite3/sqlite3.c:#ifdef SQLITE_ENABLE_ICU

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/wp8/sqlite3/sqlite3.c:#ifdef SQLITE_ENABLE_ICU

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/wp8/sqlite3/sqlite3.c:#ifdef SQLITE_ENABLE_ICU

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/wp8/sqlite3/sqlite3.c:#if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_ICU)

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/wp8/sqlite3/sqlite3.c:#ifdef SQLITE_ENABLE_ICU

/Library/Ruby/Gems/1.8/gems/rhodes-3.5.0//platform/wp8/sqlite3/sqlite3.c:#endif /* defined(SQLITE_ENABLE_ICU) */

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Igor Kuznetsov
Pietro Francesco Maggi wrote

Pietro Francesco Maggi wrote:

if you look in the Rhodes sources, ICU it's already included in the SQLite library:

https://raw.github.com/rhomobile/rhodes/3-5-stable/platform/shared/sqlite/sqlite3.c

I add in platform/shared/sqlite/sqlite3.c

#define SQLITE_ENABLE_ICU 1

and comment

// #define SQLITE_OMIT_UTF16

and run

rake run:iphone

build failed with errors:

RET: /Library/Ruby/Gems/1.8/gems/rhodes-3.5.0/platform/iphone/RhoLib/../../shared/sqlite/sqlite3.c:127710:10: fatal error: 'unicode/ucol.h' file not found

RET: #include <unicode/ucol.h>

RET:          ^

RET: 1 warning and 1 error generated.

** BUILD FAILED **

missing ICU headers files:

unicode/ubrk.h

unicode/utf16.h

unicode/utypes.h

unicode/uregex.h

unicode/ustring.h

unicode/ucol.h

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Kutir Mobility
Hi Igor,Try GLOB operator to

Hi Igor,

Try GLOB operator to compare a value.

GLOB is also case sensitive, unlike LIKE.

Thanks

Visnupriya

Kutir Mobility

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Log in to post comments