SQLite и UNICODE

Первая часть — вводная.
Вторая часть — быстрый старт.
Третья часть — особенности.

Несмотря на то, что эта тема затрагивалась на Хабре и раньше, некоторые ключевые вещи не прозвучали. В этой статье делается попытка «закрыть тему». Замечания по дополнению/исправлению приветствуются.

Формат строк в базе

БД SQLite может в базе хранить текст (строковые значения) в формате UTF-8 или в формате UTF-16. При этом порядок байт в 16-битных символах UTF-16 может быть как little-endian, так и big-endian.

То есть в реальности есть три разных формата БД SQLite: UTF-8, UTF-16le, UTF-16be.

Любой из этих форматов может быть использован на любой платформе (то есть ничего не мешает создать на x86 базу с форматом UTF-16be, хотя это неразумно, см. ниже).

Формат строк — это настройка БД, которая задается до создания базы. После создания базы сменить формат нельзя; попытки это сделать молча игнорируются ядром SQLite.

Итак,
формат строк у базы SQLite может быть одним из:
— UTF-8 (используется по умолчанию);
— UTF-16le (родной для x86);
— UTF-16be
и сменить его после создания БД нельзя.

Примечания

1. И UTF-8, и UTF-16 (см. «суррогатные пары») используют переменное (не фиксированное) число байт для хранения одного символа.

2. ATTACH-ить к базе можно базу только с таким же форматом строк, иначе будет ошибка.

3. Из вашей версии SQLite поддержка UTF-16 возможно была «удалена» при сборке (см. sqlite3.c на предмет SQLITE_OMIT_UTF16).

Передача строк в вызовы API

SQLite API вызовы (на языке C) делятся на два типа: принимающие строки в формате UTF-16 (порядок байт «родной» для платформы) и принимающие строки в формате UTF-8. Например:

int sqlite3_prepare_v2(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);

int sqlite3_prepare16_v2(
  sqlite3 *db,            /* Database handle */
  const void *zSql,       /* SQL statement, UTF-16 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const void **pzTail     /* OUT: Pointer to unused portion of zSql */
);

Если формат строки базы не совпал с форматом строки, которую передали в API, то переданная строка «на лету» перекодируется в формат базы. Этот процесс — плохой, поскольку отнимает ресурсы и, разумеется, его следует избегать. Однако, он плохой не только из-за этого, см. ниже.

Collation: способ сравнения строк

Следующая тема связана с упорядочиванием строк относительно друг друга (по возрастанию или убыванию) и получению ответа на вопрос «равны ли эти две строки». Не существует «естественного» способа сравнить две строки. Как минимум, встает вопрос: регистро-зависимое или регистро-независимое сравнение? Более того, в одной и той же базе данных могут использоваться оба таких сравнения для разных полей.

В SQLite (да и в любой базе) вводится понятие collation: способ сравнения двух строк между собой. Существуют стандартные (встроенные) collation и можно создавать свои в любом количестве.

Collation это, по сути, метод, который получает строки A и B и возвращает один из трех результатов:
«строка A меньше строки B»,
«строки A и B равны»,
«строка A больше строки B».

Но это не все. Сравнение строк обязано быть транзитивным, иначе оно «поломает» механизмы поиска, который исходят из определенных предположений. Более строго: для всех строк A,B и C должно быть гарантировано, что:
1. Если A==B, то B==A.
2. Если A==B и B==C, то A==C.
3. Если A<B то B>A.
4 Если A<B и B<C, то A<C.
Если это не так (то есть вы создали collation, который нарушает какое-то из правил и используете его), то «the behavior of SQLite is undefined» («поведение SQLite не определено»).

Обычно collation задается для столбца таблицы и определяет какой тип сравнения используется для значений этого столбца.

А когда вообще нужно задействовать сравнение строк?
1. для создания и обновления индекса по строковым значениям;
2. в процессе вычисления SQL выражений со строковыми значениями с операциями "=", "<", ">"
(… WHERE name = 'Alice').

Итак,
когда SQLite необходимо сравнить две строки, то это сравнение всегда основано на каком-то collation.

Если collation-ы у сравниваемых строк не совпадают, то используется хитрый механизм предпочтения одного collation другому, на который лучше не нарываться.

Стандартные (встроенные) collation

Полноценная поддержка сравнения любых UNICODE символов (без учета регистра) требует довольно много дополнительных данных (таблиц). Разработчики SQLite не стали «раздувать» ядро и встроили простейшие методы сравнения.

Существует три встроенных collation:
BINARY: обычное побайтовое сравнение двух блоков памяти: старый, добрый memcmp()
(используется по умолчанию, если не указан другой collation);
RTRIM: тоже, что и BINARY, но игнорирует концевые пробелы ('abc ' = 'abc');
NOCASE: тоже, что и BINARY, но игнорирует регистр для 26 букв латинского алфавита (и только для них).

Реализация стандартных collation

Заглянем «внутрь» SQLite и посмотрим на реализацию функций сравнения для разных collation.

Функция binCollFunc(). Если padFlag <> 0, то делает сравнение RTRIM, иначе BINARY:

/*
** Return true if the buffer z[0..n-1] contains all spaces.
*/
static int allSpaces(const char *z, int n){
  while( n>0 && z[n-1]==' ' ){ n--; }
  return n==0;
}

/*
** This is the default collating function named "BINARY" which is always
** available.
**
** If the padFlag argument is not NULL then space padding at the end
** of strings is ignored.  This implements the RTRIM collation.
*/
static int binCollFunc(
  void *padFlag,
  int nKey1, const void *pKey1,
  int nKey2, const void *pKey2
){
  int rc, n;
  n = nKey1<nKey2 ? nKey1 : nKey2;
  rc = memcmp(pKey1, pKey2, n);
  if( rc==0 ){
    if( padFlag
     && allSpaces(((char*)pKey1)+n, nKey1-n)
     && allSpaces(((char*)pKey2)+n, nKey2-n)
    ){
      /* Leave rc unchanged at 0 */
    }else{
      rc = nKey1 - nKey2;
    }
  }
  return rc;
}

А вот функция сравнения строк для collation NOCASE:

/*
**
** IMPLEMENTATION-OF: R-30243-02494 The sqlite3_stricmp() and
** sqlite3_strnicmp() APIs allow applications and extensions to compare
** the contents of two buffers containing UTF-8 strings in a
** case-independent fashion, using the same definition of "case
** independence" that SQLite uses internally when comparing identifiers.
*/

SQLITE_API int sqlite3_stricmp(const char *zLeft, const char *zRight){
  register unsigned char *a, *b;
  a = (unsigned char *)zLeft;
  b = (unsigned char *)zRight;
  while( *a!=0 && UpperToLower[*a]==UpperToLower[*b]){ a++; b++; }
  return UpperToLower[*a] - UpperToLower[*b];
}
SQLITE_API int sqlite3_strnicmp(const char *zLeft, const char *zRight, int N){
  register unsigned char *a, *b;
  a = (unsigned char *)zLeft;
  b = (unsigned char *)zRight;
  while( N-- > 0 && *a!=0 && UpperToLower[*a]==UpperToLower[*b]){ a++; b++; }
  return N<0 ? 0 : UpperToLower[*a] - UpperToLower[*b];
}

// UpperToLower - это таблица, которая переводит символ "в себя" для всех символов кроме 'A'..'Z' (которые переводятся в 'a'..'z')

Что привлекает внимание? Что строка якобы UTF-8 формата, но все символы строки обрабатываются подряд: нет извлечения и перекодировки символов в UTF-32.

Если долго медитировать над этим кодом, то можно понять, что он, как ни странно, корректно работает и на строках UTF-8, и на любой другой одно-символьной кодировке (например, windows-1251). Откровениями в понимании «почему так» можно делится в комментариях :).

Это плавно подводит нас к пониманию важного тезиса, что

SQLite не интересуется реальным форматом строки UTF-8 до момента, когда требуется выполнять перекодировку строки в UTF-16.

Разумеется, упорядочивание реальных UTF-8 строк стандартными collation даст довольно странные результаты. Но равенство будет работать корректно, а сравнение будет транзитивным и не нарушит работу SQLite.

Выходит, действительно, в базе SQLite можно хранить строки в кодировке, скажем, windows-1251 при условии, что вы нигде не используете UTF-16. Это касается и как строковых литералов внутри SQL, так и строк, передаваемых через привязку параметров.

Аргументы в пользу формата UTF-8 как формата «по умолчанию»

Давайте посмотрим на код функции API sqlite3Prepare16(), которая выполняет парсинг и компиляцию SQL оператора. Интересует нас комментарий в начале тела функции.

/*
** Compile the UTF-16 encoded SQL statement zSql into a statement handle.
*/
static int sqlite3Prepare16(
  sqlite3 *db,              /* Database handle. */ 
  const void *zSql,         /* UTF-16 encoded SQL statement. */
  int nBytes,               /* Length of zSql in bytes. */
  int saveSqlFlag,          /* True to save SQL text into the sqlite3_stmt */
  sqlite3_stmt **ppStmt,    /* OUT: A pointer to the prepared statement */
  const void **pzTail       /* OUT: End of parsed string */
){
  /* This function currently works by first transforming the UTF-16
  ** encoded string to UTF-8, then invoking sqlite3_prepare(). The
  ** tricky bit is figuring out the pointer to return in *pzTail.
  */
... // неважно
} 

То есть,

парсера SQL операторов в формате UTF-16 в данный момент в ядре SQLite не существует (что не исключает его появления в будущем).

Итак, если строка с SQL оператором передается в формате UTF-16 она всегда вначале переводится в формат UTF-8.

Таким образом, в пользу формата UTF-8:
— нет лишних преобразований при компиляции SQL;
— данные (как правило) занимают меньше места на диске;
— можно хранить данные в любой «байт-на-символ» кодировке, если UTF-16 нигде не используется (а самописные collation-ы учитывают новый формат строки).

Как создать и использовать собственный collation

Используем функцию API sqlite3_create_collation_v2():

 int sqlite3_create_collation_v2(
  sqlite3*,  // соединение с БД
  const char *zName,  // имя 
  int eTextRep,  // в каком формате ожидает строки
  void *pArg, // custom-аргумент
  int(*xCompare)(void*,int,const void*,int,const void*), // функция сравнения строк
  void(*xDestroy)(void*) 
);

В параметре eTextRep необходимо указать в каком из форматов ожидаются строки:

  SQLITE_UTF8     = 1;
  SQLITE_UTF16    = 2;
  SQLITE_UTF16BE  = 3;
  SQLITE_UTF16LE  = 4;
  SQLITE_ANY      = 5;

Можно задать несколько функций для одной и той же collation, но принимающие разные форматы.

SQLite пытается подобрать метод под формат, иначе (если форматы передаваемой строки и зарегистрированной функции различаются) опять выполняется перекодировка «на лету». Функция сравнения должна вернуть отрицательное число, если первая строка меньше второй; ноль — если строки равны; положительное число, если первая строка больше второй. Сравнение, как уже говорилось, должно быть транзитивным.

Создадим collation (по имени «RU»), который даст нам:
— регистронезависимое сравнение для кириллицы и латиницы
— обычное сравнение для всех остальных символов;
— корректную позицию в алфавите буквы «ё» (точнее, «ё» считается равной «е»).

Это, пока, не полноценная поддержка UNICODE, но это простое решение, которое подходит в 95% случаев.

Примеры будут на Delphi, не пугайтесь.

unit UnicodeUnit;

interface

// прочесть один символ из строки в формате UTF-8 и вернуть его UTF-32 код или -1, если не удалось прочесть
function GetCharUTF8(var P: PByte; var L: integer): integer;

// перевести символ в UTF-32 на нижний регистр (переводит только латиницу и киррилицу!)
function LowerUTF32(ACode: integer): integer;

// сравнить две UTF-8 строки (case-insensitive для латиницы и кириллицы)
function CompareUTF8IgnoreCase(L1: integer; P1: PByte; L2: integer; P2: PByte): integer;


implementation

uses SysUtils;

// элемент таблицы разбора UTF-8
type
  TParseItem = record
    Mask: integer;
    Count: integer;
  end;


var
// таблица для разбора UTF-8
  ParseTable: array[0..255] of TParseItem;

// таблица для быстрой смены регистра  
  LowerTable: array[0..65535] of integer;

function CompareUTF8IgnoreCase(L1: integer; P1: PByte; L2: integer; P2: PByte): integer;
var
  C1, C2: integer;
begin

  repeat

    if (L1 = 0) and (L2 = 0) then begin
      result := 0;
      exit;
    end;

    // считываем по очередному символу
    C1 := GetCharUTF8(P1, L1); if C1 < 0 then begin result := -1; exit; end;
    C2 := GetCharUTF8(P2, L2); if C2 < 0 then begin result := +1; exit; end;

    // сравниваем без учета регистра для латиницы и кириллицы
    if C1 < 65536 then C1 := LowerTable[C1];
    if C2 < 65536 then C2 := LowerTable[C2];


    if C1 < C2 then begin
      result := -1;
      exit;
    end else if C1 > C2 then begin
      result := +1;
      exit;
    end;

  until false;

end;


function LowerUTF32(ACode: integer): integer;
begin

  case ACode of

    1105, 1025: // ё, Ё
      result := 1077; // е

    1040..1071:
      result := ACode + 32; // кириллица

    Ord('A')..Ord('Z'):
      result := ACode + 32; // латинские

  else
    result := ACode;

  end;
end;


function GetCharUTF8(var P: PByte; var L: integer): integer;
var
  B: byte;
  I: integer;

begin

  if L > 0 then begin

    B := P^; Inc(P); Dec(L);

    with ParseTable[B] do
      if Count > 0 then begin

        // забираем данные из первого байта
        result := B and Mask;

        // собираем данные из остальных байт
        for I := 1 to Count - 1 do

          if L > 0 then begin
            result := (result shl 6) or (P^ and $3F);
            Inc(P); Dec(L);
          end else begin
            result := -1;
            exit;
          end;

        exit;
      end;

  end;

  result := -1;
  
end;

var
  I: integer;

initialization

// заполняем таблицу разбора UTF-8
  for I := 0 to 255 do
    with ParseTable[I] do
      if I <= 127 then begin
        Mask := $7F;
        Count := 1;
      end else if I >= 248 then begin
        Mask := 0;
        Count := 0;
      end else if I >= 240  then begin
        Mask := 7;
        Count := 4;
      end else if I >= 224  then begin
        Mask := 15;
        Count := 3;
      end else if I >= 192  then begin
        Mask := $1F;
        Count := 2;
      end else begin
        Mask := 0;
        Count := 0;
      end;

  // заполняем Lower
  for I := 0 to 65535 do
    LowerTable[I] := LowerUTF32(I);


end.

Код, вообщем, простой и наверное не нуждается в дополнительных пояснениях.

Как использовать:

// функция обратного вызова для сравнения строк
function RU_CollationCompare(UserData: pointer; l1: integer; p1: pointer; l2: integer; p2: pointer): integer; cdecl;
begin
  result := CompareUTF8IgnoreCase(L1, P1, L2, P2);
end;

...

// после открытия соединения задаем collation RU
sqlite3_create_collation(Fdb, 'RU', SQLITE_UTF8, nil, RU_CollationCompare);

При создании таблицы задаем тип сравнения для столбца name:

CREATE TABLE foo(
  name TEXT COLLATE RU,
  ...
)

Важно! Регистрация collation выполняется в соединении к БД (не в самой БД). Это, по сути, присоединение своего кода к коду SQLite. Другое соединение, которое не задало такой же collation, не сможет использовать эту базу.

LIKE, upper(), lower() и пр.

Код collation-а «RU», разумеется, нетрудно доработать для поддержки других алфавитов. А можно сделать (почти) полноценное UNICODE сравнение, если использовать Windows API вызовы для заполнения таблицы LowerTable.

Почему «почти»? Есть такое понятие как «нормализация» UNICODE, то есть приведение к однозначному представлению. Гугл в помощь!

Однако, поддержка UNICODE находится не только в одних collation. Существует еще:
— оператор LIKE (сравнение по шаблону);
— SQL функции lower() и upper() (переводят символы строки в нижний/верхний регистр).

И некоторые другие функции манипулирования строками: fold(), title() и пр.

Это отдельные механизмы, которые никак collation не используют.

SQLite позволяет, однако, переопределить эти функции (LIKE — это тоже функция) своими.

Для этого служит вызов API sqlite3_create_function_v2().

Почти полноценный UNICODE малой кровью

В предыдущих статьях упоминались библиотеки ICU: International Components for Unicode. Это и есть полноценная поддержка UNICODE. Беда, однако, в том, что это тащит за собой гигантское количество кода и данных, который в 95% случаев не нужен. Если в вашем SQLite уже есть встроенный ICU, то дальше можно не читать.

Так вот, нашелся один умный парень, выпиливший из этого кода не нужное и создавший некий «выжимок» из ICU.

Его оригинальное сообщение, видимо, это.

Речь вот о чем. На базе кода ICU он создал файл sqlite3_unicode.c, который компилируется в DLL (обычно это sqlite3u.dll). Полученная DLL экспортирует функцию sqlite3_unicode_init():

function sqlite3_unicode_init(db: TSQLiteDB): Integer; cdecl; external 'sqlite3u.dll' name 'sqlite3_unicode_init';

Если вызвать эту функцию для соединения, то она:
— зарегистрирует почти полноценные UNICODE-версии функций lower,upper,fold,title, unaccent;
— введет почти полноценный UNICODE регистронезависимый LIKE.

Размер этой DLL всего 80 Kb и работает она быстро (используются таблицы). Оговорка «почти» важна — это не полноценный UNICODE, но в 95% случаев этой библиотеки хватит.

Примечания.

1. если LIKE переопределен, то SQLite не сможет его оптимизировать индексом (A LIKE 'XXX%' не будет использовать индекс по A, если он есть).

2. функции lower(),upper() и пр., вообще говоря, не обязаны быть в движке базы, можно и в коде приложения выполнять эти преобразования.

Юз зис лайбрари эт ёр оун риск, то есть автор этой статьи не несет ответственности никогда и ни за что.

Автор: Krovosos