On 03/19/2014 07:55 PM, Aleksey Tulinov wrote:
On 03/18/2014 10:02 PM, Alex Loukissas wrote:
Alex,
I suppose I can declare the column as BINARY and use LOWER( ) in my
select
statements. Browsing through the code though, I do see uses of
u_foldCase
in certain places, which leads me to believe that what I want may be
already there. I'll try to unit test this.
Correct me if i'm wrong, but i think i reproduced bug you've described
in SQLite with ICU extension compiled in:
sqlite> CREATE TABLE test (x COLLATE NOCASE);
sqlite> INSERT INTO test VALUES ('s');
sqlite> INSERT INTO test VALUES ('S');
sqlite> INSERT INTO test VALUES ('ё'); -- Russian e with diacritic
sqlite> INSERT INTO test VALUES ('Ё'); -- Russian E with diacritic
sqlite> SELECT * FROM test WHERE x = 'ё';
ё
sqlite> SELECT * FROM test WHERE x = 's';
s
S
This might be a point of interest for SQLite ICU extension developers.
On the other hand, SQLite nunicode extension, which i developed, do that:
sqlite> SELECT * FROM test WHERE x = 'ё';
ё
sqlite> .load ./libnusqlite3.so
sqlite> SELECT * FROM test WHERE x = 'ё';
ё
Ё
One problem with this sort of thing (overriding built-in collation
sequences) is that you need to be careful never to accidentally use the
wrong version.
If you were to create an index using collation sequence "NOCASE" and
populate the db using a stock SQLite, then query using a nunicode
enhanced version, the results would be unpredictable. And running
"PRAGMA integrity_check" would report corruption. If you were to write
to the db using both a regular SQLite and a nunicode version the
database would become corrupt in the sense that no version would be able
to query it reliably.
For that reason, the SQLite ICU extension does not automatically
override the "NOCASE" collation sequence the same way it overrides the
built-in upper() and lower() functions. If we were to add case-folding
to ICU collations, we would have to use a different name.
It does look like nunicode would solve the OP's problem nicely though.
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
