Search is case-sensitive for non-ASCII characters on pysqlite

[Oleg Frantsuzov <franoleg@…>]

Steps to reproduce:

1. Set up Trac with pysqlite as database backend.
2. In a blank environment, create two tickets, one named "Проблема", another "Вторая проблема" ("Problem" and "Another problem" in Russian, respectively). Note that "П" is uppercase in the first ticket, and lowercase in the second.
3. Using Trac search box, search for "Проблема", then for "проблема".

Expected behavior:

• In either search, both tickets are expected to appear in the results.

Actual behavior:

• In either search, only one of the tickets appears in the results.

Additional info

A quote from the SQLite docs:

A bug: 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. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.
— ​SQL As Understood By SQLite, section The LIKE and GLOB operators

Below is the transcript of a Python session which reproduces the problem in vanilla pysqlite:

Python 2.7 (r27:82525, Jul  4 2010, 09:01:59) [MSC v.1500 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> cnn = sqlite3.Connection(':memory:')
>>> r1 = cnn.execute('select "IT" like "it";')
>>> list(r1)
[(1,)]
>>> r2 = cnn.execute('select "ФУ" like "фу";')
>>> list(r2)
[(0,)]

[Christian Boos]

Yes, another reference is the FAQ: ​http://www.sqlite.org/faq.html#q18. That one suggests that the ​ICU extension for SQLite could be used to achieve this goal.

You could try to compile that extension and then it should be possible to load it dynamically (#7902) via the trac.ini's [sqlite] extensions setting. If you can't get the dynamic loading to work, it should still be possible to build a SQLite version with the ​SQLITE_ENABLE_ICU flag and making sure PySqlite uses it.

Please let us know if this worked for you, thanks!

[Christian Boos]

(if someone is able to check the validity of the advice given above, let us know)

[bebugz@…]

Solution with extension compilation truly works. Leaving full recipe here for Ubuntu Server:

$ apt install libsqlite3-dev icu-devtools libicu-dev
$ wget https://www.sqlite.org/src/raw/ext/icu/icu.c?name=c2c7592574c08cd1270d909b8fb8797f6ea1f49e931e71dbcc25506b9b224580 -O icu.c
... change sqlite3_icu_init to sqlite3_sqliteicu_init
$ gcc -shared icu.c `icu-config --ldflags` -fPIC -o libSqliteIcu.so

In trac.ini:

[sqlite]
extensions = /full/path/libSqliteIcu