SQLiteとUNICODE

最初の部分は序論です。
2番目の部分はクイックスタートです。
3番目の部分は機能です 。

このトピックはHabréで以前に議論されたという事実にもかかわらず、いくつかの重要な事柄は発音されていません。 この記事は「トピックを閉じる」ことを試みます。 追加/修正に関するコメントを歓迎します。

データベース内の書式文字列

SQLiteデータベースは、テキスト（文字列値）をデータベースにUTF-8形式またはUTF-16形式で保存できます。 16ビットUTF-16文字のバイト順は、リトルエンディアンまたはビッグエンディアンのいずれかです。

つまり、実際には3つの異なるSQLiteデータベース形式があります： UTF-8、UTF-16le、UTF-16beです。

これらの形式はいずれのプラットフォームでも使用できます（つまり、UTF-16be形式でx86ベースのデータベースを作成することを妨げるものはありませんが、これは不合理ですが、以下を参照してください）。

文字列の形式は、 データベースを作成する前に 設定されるデータベース設定です。 データベースを作成した後、形式を変更することはできません 。 これをサイレントに実行しようとすると、SQLiteカーネルは無視します。

だから
SQLiteデータベースの行形式は次のいずれかです。
-UTF-8（デフォルトで使用）;
-UTF-16le（x86ネイティブ）;
-UTF-16be
データベースの作成後に変更することはできません。

注釈

1. UTF-8とUTF-16の両方（「サロゲートペア」を参照）は、1バイトの文字を格納するために可変（非固定）バイト数を使用します。

2.データベースにアタッチすると、同じ文字列形式でのみベースできます。そうしないとエラーが発生します。

3. SQLiteのバージョンから、アセンブリ中にUTF-16サポートが「削除」された可能性があります（SQLITE_OMIT_UTF16についてはsqlite3.cを参照 ）。

API呼び出しに文字列を渡す

SQLite API呼び出し（C言語）は、UTF-16形式（プラットフォームのネイティブバイト順）で文字列を受信することと、UTF-8形式で文字列を受信することの2つのタイプに分けられます。 例：

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に渡された文字列の形式と一致しなかった場合、送信された文字列はその場で基本形式に変換されます。 このプロセスはリソースを消費するので、もちろん、避けるべきです。 ただし、これが悪いだけでなく、以下を参照してください。

照合：文字列比較メソッド

次のトピックは、相互に相対的な行の順序（昇順または降順）に関連しており、「これら2行は等しいですか？」という質問に対する答えを取得します。 2行を比較する「自然な」方法はありません。 少なくとも、大文字と小文字を区別するのか、大文字と小文字を区別しないのかという疑問が生じます。 さらに、同じデータベースで、異なるフィールドのこのような比較の両方を使用できます。

SQLite（および任意のデータベース）では、 照合の概念が導入されています。これは、2つの行を相互に比較する方法です。 標準（組み込み）照合があり、任意の数量で独自の照合を作成できます。

照合は、基本的に文字列AとBを取得し、次の3つの結果のいずれかを返すメソッドです。
「行Aは行Bより小さい」、
「ラインAとBは等しい」、
「行Aは行Bよりも大きい」。

しかし、それだけではありません。 文字列の比較は推移的でなければなりません。そうでなければ、特定の仮定から生じる検索メカニズムを「破壊」します。 より厳密に：すべての行A、B、Cについて、次のことを保証する必要があります。
1. A == Bの場合、B ==A。
2. A == BおよびB == Cの場合、A ==C。
3. A <B then B> Aの場合
4 A <BおよびB <Cの場合、A <C。
そうでない場合（つまり、ルールの1つに違反する照合を作成して使用する場合）、 「SQLiteの動作は未定義です」 。

通常、照合はテーブル列に設定され、その列の値に使用される比較のタイプを決定します。

そして、いつ文字列比較を使用する必要がありますか？
1.文字列値でインデックスを作成および更新します。
2.「=」、「<」、「>」の操作を含む文字列値を含むSQL式の計算中
（ ... WHERE name = 'Alice'） 。

だから
SQLiteが2つの行を比較する必要がある場合、この比較は常に何らかの照合に基づいています。

比較された文字列の照合が一致しない場合、ある照合が別の照合よりも優先されるcなメカニズムが使用されます。

標準（埋め込み）照合

UNICODE文字の比較（大文字と小文字を区別しない）を完全にサポートするには、非常に多くの追加データ（テーブル）が必要です。 SQLite開発者はカーネルを「膨張」させず、最も単純な比較方法を組み込みました。

3つの組み込み照合順序があります。
BINARY ：2つのメモリブロックの通常のバイト単位の比較：古い、良いmemcmp（）
（別の照合が指定されない限り、デフォルトで使用されます）;
RTRIM ：BINARYと同じですが、末尾のスペースを無視します（ 'abc' = 'abc'）;
NOCASE ：BINARYと同じですが、26個のラテン文字の大文字と小文字を無視します（大文字と小文字のみ）。

標準照合の実装

SQLiteの内部を見て、さまざまな照合のための比較関数の実装を見てください。

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; } 

そして、照合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-16に変換する必要があるまで、UTF-8文字列の実際の形式に関心がありません 。

もちろん、標準の照合で実際のUTF-8文字列を配置すると、かなり奇妙な結果が生成されます。 しかし、平等は正しく機能し、比較は推移的であり、SQLiteを混乱させることはありません。

実際、UTF-16をどこでも使用していないという条件で、たとえばwindows-1251のエンコーディングでSQLiteデータベースに文字列を保存できることがわかりました。 これは、SQL内の文字列リテラルとパラメーターバインディングを介して渡される文字列の両方に適用されます。

デフォルト形式としてUTF-8形式を支持する引数

SQLステートメントを解析およびコンパイルするsqlite3Prepare16（） API関数コードを見てみましょう。 関数本体の最初のコメントに興味があります。

 /* ** 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. */ ... //  } 

つまり、

UTF-16形式のSQLステートメントのパーサーは、現在SQLiteカーネルに存在しません（将来の外観を除外しません） 。

そのため、SQLステートメントを含む文字列がUTF-16形式で送信される場合、 常に最初にUTF-8形式に変換されます。

したがって、UTF-8形式を支持して：
-SQLのコンパイル時に余分な変換はありません。
-データは（通常）使用するディスク容量が少なくなります。
-UTF-16がどこでも使用されていない場合は、バイトごとのエンコードでデータを保存できます（そして、自己記述の照合では新しい文字列形式が考慮されます）。

独自の照合を作成して使用する方法

sqlite3_create_collat​​ion_v2（） API 関数を使用します 。

  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; 

同じ照合に対して複数の関数を指定できますが、異なる形式を受け入れます。

SQLiteはフォーマットのメソッドを選択しようとします。そうでない場合（送信された文字列と登録された関数のフォーマットが異なる場合）、変換はその場で再び実行されます。 最初の行が2番目の行よりも小さい場合、比較関数は負の数を返します。 ゼロ-行が等しい場合。 最初の行が2番目の行よりも大きい場合は正の数。 すでに述べたように、比較は推移的でなければなりません。

次のような照合順序（「RU」という名前）を作成します。
-キリル文字とラテン文字の大文字と小文字を区別しない比較
-他のすべてのキャラクターの通常の比較。
-文字「」のアルファベットの正しい位置（より正確には、「」は「e」と等しいと見なされます）。

これは、これまでのところ、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); 

テーブルを作成するときに、 名前列の比較タイプを設定します。

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

重要！ 照合登録は、データベースに関連して実行されます （データベース自体ではありません）。 これは、基本的にコードをSQLiteコードに添付することです。 同じ照合を指定しなかった別の接続では、このベースを使用できません。

LIKE、上部（）、下部（）など

照合コード「RU」は、もちろん、他のアルファベットをサポートするために簡単に変更できます。 また、Windows API呼び出しを使用してLowerTableテーブルにデータを入力すると、（ほぼ）完全なUNICODE比較を行うことができます。

なぜ「ほぼ」？ UNICODEの「正規化」、つまり、明確な表現への縮小などがあります。 Googleが助けになります！

ただし、UNICODEのサポートは、照合のみで見つかるわけではありません。 またあります：
-LIKE演算子（パターンマッチング）;
-SQL関数lower（）およびupper（） （ストリング文字を小文字/大文字に変換）。

その他の文字列操作関数： fold（）、title（）など

これらは、照合をまったく使用しない別個のメカニズムです。

ただし、SQLiteでは、これらの関数（LIKEも関数です）を独自の関数でオーバーライドできます。

これを行うには、 sqlite3_create_function_v2（） API呼び出しを使用します。

ほぼ完全なユニコードの小さな血

以前の記事では、 ICUライブラリー：Unicodeの国際コンポーネントについて説明しました 。 これは、UNICODEの完全なサポートです。 ただし、問題は、膨大な量のコードとデータをドラッグすることであり、95％のケースでは必要ありません。 SQLiteにすでにICUが組み込まれている場合は、さらに読むことをスキップできます。

そのため、このコードから不要なものを見つけ出し、ICUから一種の「スクイーズ」を作成した賢い人が1人いました。

彼の元のメッセージは、どうやらこれです。

これがその目的です。 ICUコードに基づいて、彼はDLL（通常はsqlite3u.dll ）にコンパイルされるsqlite3_unicode.cファイルを作成しました。 結果のDLLはsqlite3_unicode_init（）関数をエクスポートします：

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

この関数を呼び出して接続すると、次のようになります。
-lower、upper、fold、title、unaccent関数のほぼ完全なUNICODEバージョンを登録します。
- ほぼ完全なUNICODE大文字と小文字を区別しないLIKEを導入します。

このDLLのサイズは80 KBのみで、すぐに動作します（テーブルが使用されます）。 「ほぼ」という句は重要です。これは完全なUNICODEではありませんが、95％のケースでこのライブラリで十分です。

注。

1. LIKEがオーバーライドされると、SQLiteはインデックスによって最適化できません（LIKE 'XXX％'はAによるインデックスを使用しません（ある場合））。

2.一般的に言えば、関数lower（）、upper（）などは、データベースエンジンにある必要はありません。アプリケーションコードでこれらの変換を実行することができます。

Yuz zis librari et yor oun risk 、つまり、この記事の著者は一切責任を負いません。