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

    Наверно многим известна 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.
    Share post

    Similar posts

    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 22

      +5
      Можно обойтись без самосборки. 15 марта этого года в unstable появилсь сборка sqlite3, решающая данную проблему.

      $ echo $LANG
      ru_RU.UTF-8
      $ sudo apt-get -qq -t unstable install sqlite3
      $ sqlite3
      sqlite> SELECT "ы" LIKE "Ы";
      1
      

        +1
        Хорошая новость. Но в macports как всегда через год появится, если вообще появится…
          0
          ну так соберите и сами и отправьте мэинтейнерам MacPorts
            0
            Собрать пакет — знаний не хватает. Но я конечно попытаюсь:) А фич реквест — да, в любом случае напишу.
            0
            Хочу такое счастье на ipad. Не думаю что Apple одобрит sqlite3_enable_load_extension(), да и свежего, а значит, сырого кода sqlite тоже долго ждать прийдется.

            Спасибо автору за пост, без статьи про грабли я бы не узнал что такие грабли есть.
          0
          А для windows есть решение?
            0
            К сожалению windows'а у меня нет… Но думаю, точно так же можно собрать с помощью MSVC. В крайнем случае попробовать cygwin.
            ICU для windows есть: icu-project.org/download/4.0.html#ICU4C
            При компилляции SQLite с поддержкой ICU нужно указать дефайн SQLITE_ENABLE_ICU(-DSQLITE_ENABLE_ICU для gcc, для MSVC не знаю). Ну и соответствено путь к хидерам и к самой либе ICU.
            Попробуйте. Отпишитесь если удастся:)
            0
            Я прошу прощения, но

            Давайте разберемЬся как же это исправить.

            ?
              0
              Упс, спасибо, исправлено:)
              +1
              Я решал эту проблему другим образом.
              Переопределил функцию LIKE и стал отрабатывать ее сам.

              Кроме того я таким методом сразу устранил проблему иньекций и прочего.
                0
                Тоже вариант, тогда и REGEXP тоже до кучи, мне ее в SQLite всегда не хватает.
                И, как понимаю, вы от зависимости от ICU избавились?
                  0
                  Можно подробнее, как это сделать?
                    +1
                    В исходниках функция likeFunc овечает за обработку LIKE, просто ее переписать и собрать измененный исходник.
                    0
                    Это, наверное, должно дико тормозить?
                    0
                    Достаточно пропатчить массив sqlite3UpperToLower (надо бы его расширить) + исправить где-надо range, если мне память не изменяет.
                      0
                      Все конечно замечательно, но как быть если регистронезависимый поиск сделать надо, но перекомпилять невозможно (шаред хостинг к примеру).
                        0
                        так как гугл по запросу «sqlite icu lower» выдает ссылку на эту страницу одной из первых, небольшой апдейт :)
                        1) sqlite3 на текущий момент собирается без icu (как написано в ченджлоге, это создало проблем больше, чем решило)
                        2) для AMD64 при сборке icu надо указывать параметр -fPIC
                        3) чтобы собранный с icu sqlite3 правильно установить в Ubuntu, надо configure вызывать с параметром prefix, иначе либа установиться в папку /usr/local/, и системно будет использоваться версия, установленная apt-get
                        $ CFLAGS='-Os -DSQLITE_ENABLE_ICU' CPPFLAGS=`icu-config --cppflags` LDFLAGS=`icu-config --ldflags` ./configure --prefix=/usr/
                        

                          0
                          3) как правило во всех линуксах:
                          в /usr — только то, что в пакетах (deb, rpm, ...)
                          а собранное самостоятельно — в /usr/local
                          0
                          дополнения для debian:

                          1. wget в stable записывает имя файла как getfile\?f\=sqlite%2Fext%2Ficu%2Ficu.c => лучше поправить:
                          $ wget www.sqlite.org/cvstrac/getfile?f=sqlite/ext/icu/icu.c -O icu.c

                          2. для сборки необходим пакет libsqlite3-dev:
                          $ sudo apt-get install libsqlite3-dev
                            0
                            Ленивый вариант для русского LIKE в sqlite-amalgamation-3_7_2 (работает без icu):
                            -# define GlogUpperToLower(A) if( A<0x80 ) { A = sqlite3UpperToLower[A]; }
                            +# define GlogUpperToLower(A) if( A<0x80 ) { A = sqlite3UpperToLower[A]; } if ( A >= 0x0410 && A <= 0x042f) { A += 0x20; }
                              0
                              Мда, жаль без патча никак
                                0
                                Ну может когда нибудь в официальную сборку введут, и не надо будет мучиться. sqlite сразу в разы станет популярнее.

                              Only users with full accounts can post comments. Log in, please.