Как стать автором
Обновить

SQLite и полноценный UNICODE

Время на прочтение 2 мин
Количество просмотров 30K
Наверно многим известна embed база данных SQLite. SQLite полностью поддерживает кодировки UTF-8 и UTF-16. Но есть один нюанс, для строковых и текстовых полей, символы которых выходят за пределы ASCII таблицы, не работает нечувствительный к регистру LIKE и ORDER BY.
Например:
sqlite> SELECT "ы" LIKE "Ы";
0
в то время как
sqlite> SELECT "s" LIKE "S";
1
Давайте разберемся как же это исправить.

Продолжительное гугленье наводит нас на ICU экстеншн для SQLite. Как видно из readme, ICU расширение подменяет функции upper() и lower(), которые и отвечают за преобразование регистра символов. Помимо этого, данный экстеншн добавляет реализацию оператора REGEXP, для выборки текстовых полей по регулярному выражению(SQLite на уровне языка поддерживает REGEXP оператор, но функция, его реализующая поставляется без реализации, с расчетом на пользовательскую реализацию).

Чтобы начать работу с ICU экстеншеном, сначало его нужно скомпилировать в динамическую библиотеку.
Для этого понадобится библиотека поддержки юникода icu, и сам код ICU экстеншена:
Для Mac OS(при условии что установлен macports):
$ sudo port install icu
$ wget http://www.sqlite.org/cvstrac/getfile?f=sqlite/ext/icu/icu.c
$ gcc -dynamiclib icu.c -o libsqliteicu.dylib `icu-config --cppflags` `icu-config --ldflags`
Либо Debian:
$ sudo apt-get install libicu-dev
$ wget http://www.sqlite.org/cvstrac/getfile?f=sqlite/ext/icu/icu.c
$ gcc -shared icu.c -o libsqliteicu.so `icu-config --cppflags` `icu-config --ldflags`

Теперь запускаем SQLite3 CLI, и радуемся результату:)
$ sqlite3
загружаем расширение
sqlite> .load libsqliteicu.dylib
устанавливаем русский collation
sqlite> SELECT icu_load_collation('ru_RU', 'RUSSIAN');
sqlite> SELECT "ы" LIKE "Ы";
1

Но, в итоге все оказывается не так то просто. Чтобы загрузить экстеншн через API а не через CLI, нужно вызвать функцию sqlite3_enable_load_extension. Если драйвер SQLite для вашего языка имеет обертку для этой функии, или же вы пишите на C/C++ — то все в порядке. Но вот дравер для Ruby, ruby-sqlite3, данную функцию не поддерживает…

Первая мысль — добавить эту функцию в драйвер:) Но, нашелся вариант по-проще. Оказывается, ICU расширение можно встроить в SQLite.
$ wget http://www.sqlite.org/sqlite-amalgamation-3.6.13.tar.gz
$ tar xzfv sqlite*
$ cd sqlite*
$ CFLAGS='-Os -DSQLITE_ENABLE_ICU' CPPFLAGS=`icu-config --cppflags` LDFLAGS=`icu-config --ldflags` ./configure
$ make && sudo make install

После этого переустанавливаем адаптер для Ruby(чтобы пересобрался), и радуемся решению проблемы:)

Но, естественно не обошлось и без минусов. Из-за зависимости от ICU, библиотека будет весить несколько мегабайт, что не очень хорошо для embed базы данных. Если же sqlite используется для сайта(как в моем случае), то это волновать не должно.
Решением может быть создание собственного collation с помощью sqlite3_create_collation.

UPD: Для тех кто использует Ruby библиотеку Sequel. Как я сказал выше, ICU экстеншн также добавляет оператор REGEXP. Но, если в Sequel попытаться выполнить запрос с регулярными выражениями, например так
p DB[:artists].filter(:name => /^a.*/i).all
то кинется эксепшн, о том, что SQLite не поддерживает регулярные выражение.
Чтобы это обойти, я написал небольшой monkey patch.
Теги:
Хабы:
+23
Комментарии 22
Комментарии Комментарии 22

Публикации

Истории

Ближайшие события

Московский туристический хакатон
Дата 23 марта – 7 апреля
Место
Москва Онлайн
Геймтон «DatsEdenSpace» от DatsTeam
Дата 5 – 6 апреля
Время 17:00 – 20:00
Место
Онлайн