Как отсортировать текст в sqlite3 с указанной локалью?

Ответ 1

SQLite поддерживает интеграцию с ICU, Согласно файлу Readme, sqlite/ext/icu/README.txt каталог sqlite/ext/icu/ содержит исходный код расширения SQLite "ICU", интеграция библиотеки "Международные компоненты для Unicode" с SQLite.

1. Features

    1.1  SQL Scalars upper() and lower()
    1.2  Unicode Aware LIKE Operator
    1.3  ICU Collation Sequences
    1.4  SQL REGEXP Operator

Ответ 2

Я принял ответ Doug Currie, но я хочу добавить "алгоритм", как это сделать, потому что документация sqlite3 очень странная (по крайней мере, для меня).

Хорошо, у нас есть рабочий sqlite3 и теперь:

• Загрузить расширение ICU для sqlite

• Скомпилируйте его:

  gcc -shared icu.c `icu-config --ldflags` -o libSqliteIcu.so
  

  Это для Linux. Мне также необходимо установить дополнительный пакет разработки ICU:

  sudo apt-get install libicu-dev
  

  Я работаю над 64-битной архитектурой, и я получаю ошибку с __relocation R_X86_64_32S__ (что бы это ни значило:). GCC предложил добавить -fPIC для компиляции параметров, и это помогло.

• Запустите sqlite3. Мы можем загрузить расширение командой:

  .load './libSqliteIcu.so'
  

  Предполагая, что он находится в текущем каталоге, мы также можем указать весь путь.

• Создать новую сортировку:

  SELECT icu_load_collation('pl_PL', 'POLISH');
  

  Первый параметр - желаемый язык, а второй - это (он может быть любым).

• Теперь мы можем сортировать данные с нашей новой локалью:

  SELECT * FROM some_table ORDER BY name COLLATE POLISH;
  

  И это нечувствительно к регистру!

Ответ 3

Если вы не можете позволить себе скомпилировать расширение ICU, вы можете сделать UDF так же. В PHP/PDO:

$pdo->sqliteCreateFunction('locale',
    function ($data, $locale = 'root')
    {
        static $collators = array();

        if (isset($collators[$locale]) !== true)
        {
            $collators[$locale] = new \Collator($locale);
        }

        return $collators[$locale]->getSortKey($data);
    }
);

Пример использования:

SELECT * FROM "table" ORDER BY locale("column", 'pt_PT');

Я не ожидаю, что такой подход будет таким же эффективным, как родное расширение, но он, безусловно, более портативен.

Ответ 4

Для тех, кто не может создать расширение самостоятельно, я сделал скомпилированные версии для MacOS и Linux здесь: http://files.tempel.org/Various/Sqlite3ICUExtention

Версии Linux для Intel 32 и 64-битной версии были построены на Ubuntu 16, если это имеет значение.

Как правило, вы не должны доверять скомпилированному коду, предоставленному другими, но я довольно публичный человек, то есть я бы рискнул, если бы предоставил "плохую" версию. И чтобы убедиться, что на моем сервере не было ни атаки, ни взлома посредником, вот хеш MD5 для 3 файлов:

libSqliteIcu-i386.so = 6decd73f27d9c61243128e798304508f
libSqliteIcu-x86_64.so = b127c8a1f65503c91c61a21732eb11be
sqlite3_icu_extension.dylib = a29d59f6b74e7ef234691729b82da660