SQLite и UNICODE

[sebres]

Откровениями в понимании «почему так» можно делится в комментариях..

все просто: cause single byte encodings are sub sets of utf-8.

Правда бывают исключения, когда оно не совсем корректно работает — например, в строку utf-8 можно положить букву «Я» как %d0%af или %c3%83%c2%9f (не совсем легально %04%2f) или даже однобайтно %df (правдо только если систем-кодировка cp1251). Теперь при сравнении естественно что %d0%af != %df.
Так же, не совсем корректно функция будет сравнивать (сортировать) буквы некоторых других языков, например те, что справа-налево — потому, что при простом вычитании, где должно быть позитив — будет негатив.

[Krovosos]

Имел в виду тонкий момент, связанный с тем, что «хвостовые» байты в UTF-8 всегда больше 127, поэтому их не испортит преобразование, используемое в NOCASE.

Что касается вашего замечания, то тут затронут как раз вопрос нормализации, не так ли?

[sebres]

Совершенно верно по поводу п. 1;
Про п. 2 не совсем — хотя эта тема отдельной статьи и никак не комментариев.

[Fahrenheit]

Спасибо за статью. Думаю, в статью стоит добавить, что именно попадает в эти самые 5% ситуаций, когда mini-ICU даст косяки

[Krovosos]

Я вот не настолько компетентен в этом вопросе, тут требуется глубокое знание UNICODE. Могу привести комментарии, из которых можно точнее понять.

/*
** <sqlite3_unicode>
** The built-in collating sequence: NOCASE is extended to accomodate the
** unicode case folding mapping tables to normalize characters to their
** fold equivalents and test them for equality.
**
** Both UTF-8 and UTF-16 implementations are supported.
*/

/*
** <sqlite3_unicode>
** Implementation of the FOLD(), UPPER(), LOWER(), TITLE() SQL functions.
** This function case folds each character in the supplied string to its
** single character equivalent.
**
** The conversion to be made depends on the contents of (sqlite3_context *)context
** where a pointer to a specific case conversion function is stored.
*/

/*
** <sqlite3_unicode>
** Implementation of the UNACCENT() SQL function.
** This function decomposes each character in the supplied string
** to its components and strips any accents present in the string.
**
** This function may result to a longer output string compared
** to the original input string. Memory has been properly reallocated
** to accomodate for the extra memory length required.
*/

Короче, идея моя была в том, что кому нужно найдет и скачает этот sqlite3_unicode.c и посмотрит внутри него, чтобы найти ответ на вопрос «есть ли вот это, что мне нужно»… )

[sebres]

Для тех кто использует TCL (можно не только как скрипт, но и нативно) — все гораздо проще…

% package require sqlite3
## откроем БД :
% sqlite3 db test.sqlite
## стандартный NOCASE collation вернет здесь "none", потому что 
## действует ASCII only ...
% db onecolumn {select 'ok' where 'абвгд' == 'АБВГД' collate NOCASE union all select 'none'}
none
## регистрируем NOCASE collation, используя tcl функцию (utf-8 safe) 
% proc NOCASE_compare {a b} {
  string compare -nocase $a $b
}
% db collate NOCASE NOCASE_compare
## теперь снова пробуем, NOCASE collation вернет здесь уже "ок"
% db onecolumn {select 'ok' where 'абвгд' == 'АБВГД' collate NOCASE union all select 'none'}
ok

А можно и через дополнительные функции…

## регистрируем собственную функцию для "nocase like" :
% db function NCLIKE {string match -nocase}
## test it ...
% db onecolumn {select 'ok' where NCLIKE('Te?t - аб*', 'TEST - АБВГД') union all select 'none'}
ok
## test it in table ...
% set search {Te?t - аб*}
% db onecolum {select field1 from tab1 where NCLIKE($search, field1)}
TEST - АБВГД

[Stac]

А что насчет sqlite2? Как там дела?

[naum]

А зачем? sqlite2 свое отжил, абсолютно по всем фронтам. И не смотря на частую деградацию sqlite3 performance в более новых версиях, вспоминать про sqlite2 — ну точно не стоит.

[Stac]

Где-то отжил, а где-то он работает себе и работает.

[naum]

Ну, работать он может, ок. Но почему бы не переехать на sqlite3? Профит вы точно получите.

[Stac]

В том-то и дело, что нет. Поэтому и не переезжаю, никто не оплатит затраты.

Хотя все равно придется… когда буду посвободнее.

[naum]

Что же там у вас такого, что миграция sqlite2 -> sqlite3 займет столько времени / сил?

[sepich]

Нашел эту отличную статью поиском. Для таких же как я, хотел оставить коммент:
В c# System.Data.Sqlite уже включена поддержка unicode.
При складывании русского текста в utf8 в FTS — регистронезависимый поиск работает.
Не надо городить костыли с записью lower(text) в FTS.

[Nashev]

А у меня сегодня засунутая в базу строчка, написанная большими латинскими буквами, была строго больше ровно такой же, но передаваемой в запрос параметром или даже подстановкой. Причём и upper от них, и даже hex от них совпадали. А substr-ы на одну букву — на вид были идентичны, а на сравнение — нет… UPDATE помог, а как она в базе получилась и что с неё было не так — поди разберись…

[Nashev]

Разобрался. В sqlite.org/datatype3.html пишут:

In SQLite, the datatype of a value is associated with the value itself, not with its container.

То есть (если подытожить то, что там по ссылке ещё понаписано), то что указано для колонки при создании таблицы, и то, что будет хранится собственно в полях записей — вещи связанные ОООЧЕНЬ косвенно. И никто не скажет вам, что хранится не то, что вы декларировали при создании таблицы. Если не спросите.

BLOB. The value is a blob of data, stored exactly as it was input.

В частности, если вы все строки биндите как блобы, например ради того чтоб не конвертить любимый Windows-1251 в эти их UTF-ы, то в полях записей так и будут хранится блобы. Собственно, этого-то тут и хотелось. Чтоб хранились.

A TEXT value is less than a BLOB value.

Воот. Если вдруг вздумали сравнивать с литералом каким-нибудь, то будьте готовы обломаться.

When two BLOB values are compared, the result is determined using memcmp().

А если сравнивать блоб с бинденным блобом — то тоже может быть всякое, если блоб вдруг почему-то не байт в байт прям совпадает.

[Massaraksh147]

Что-то не работает. При соединении с БД и попыткой выполнить sqlite3_create_collation
выдает «library routine called out of sequence»

[Massaraksh147]

Разобрался, заработало.