やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

📌 ？
トップ > SQLite3 > SQLite3学習　FTS5のテーブル作成と初期化

SQLite3学習　FTS5のテーブル作成と初期化

SQLite3

　ファイルサイズと検索速度のトレードオフ設定方法。

成果物

情報源

FTS5用テーブル作成

　ふつうのテーブルは以下のように定義する。

CREATE TABLE posts (id int primary key, title text, content text);

　FTS5用のテーブルは以下のように定義する。

CREATE VIRTUAL TABLE posts USING fts5(title, content);

　特徴は以下の[]でくくった部分。

CREATE [VIRTUAL] TABLE posts [USING fts5](title, content);

　また、以下のような特徴もある。

  • 型は定義できない（すべてtext型となる）
  • 列名として定義できない名前: rowid, rank, テーブル名

列オプション

　列の動作を変更するオプション。今の所UNINDEXEDのみ。

UNINDEXED

　UNINDEXED列オプションで修飾された列の内容は、FTSインデックスに追加されない。これは、MATCHクエリとFTS5補助関数の目的で、列に一致するトークンが含まれていないことを意味する。

　たとえばuuid列の内容をFTSインデックスに追加せぬようにするには以下コード。

CREATE VIRTUAL TABLE customers USING fts5(name, addr, uuid UNINDEXED);

　使いどころは？　おそらくFTSクエリによる検索ではなくSQLクエリによる検索をする列に対してつけるものだと思われる。

設定オプション

prefix index

　デフォルトでは、FTS5はドキュメントセット内の各トークンインスタンスの場所を記録する単一のインデックスを保持する。つまり、1回のルックアップが必要なため、完全なトークンのクエリは高速だが、範囲スキャンが必要なため、プレフィックストークンのクエリは低速になる可能性がある。たとえば、プレフィックストークンabc*を照会するにはabc以上abd未満のすべてのトークンの範囲スキャンが必要。

　プレフィックスインデックスは、プレフィックストークンのクエリを高速化するために使用される文字で特定の長さのプレフィックストークンのすべてのインスタンスの場所を記録する個別のインデックスである。たとえば、プレフィックストークンabc*のクエリを最適化するには、3文字のプレフィックスのプレフィックスインデックスが必要。

　FTS5テーブルにプレフィックスインデックスを追加するにはprefixオプションを単一の正の整数または1つ以上の正の整数値の空白区切りリストを含むテキスト値に設定する。指定された整数ごとにプレフィックスインデックスが作成される。単一のCREATE VIRTUAL TABLEステートメントの一部として複数の「プレフィックス」オプションが指定されている場合、すべてが適用される。

-- Two ways to create an FTS5 table that maintains prefix indexes for
-- two and three character prefix tokens.
CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2 3');
CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix=2, prefix=3);

　使いどころは？　おそらくプレフィクス検索を頻繁にするテーブルに対して設定する。ただしインデックス増大によりファイルサイズも増えるだろう。

tokenizer

　トークナイザを指定する。トークナイザはテーブル内のテキストをトークンに分解する。

-- The following are all equivalent
CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = 'porter ascii');
CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = "porter ascii");
CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = "'porter' 'ascii'");
CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = '''porter'' ''ascii''');

-- But this will fail:
CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = '"porter" "ascii"');

-- This will fail too:
CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = 'porter' 'ascii');

　組込のトークナイザは以下3つ。

　使いどころは？　むしろFTSはトークナイザ次第で決まる。MeCabのように日本語を区切りトークンにしてくれると嬉しいのだが、組込には存在しない。組込トークナイザは英語用だったり1文字単位だったりで、その用途にしか使えない。

unicode61 トークナイザ

オプション デフォルト値 値の範囲 概要
remove_diacritics 1 0,1,2 発音記号の削除。0:しない。1:バグにより一部削除せず。2:正しく削除
categories L* N* Co Unicode character category トークン文字に対応するUnicodeカテゴリを設定する。
tokenchars 空白文字や句読点であってもトークンとみなす文字の設定。
separators トークン文字であっても区切り文字とみなす文字の設定。

　設定コード例は以下。

-- Create an FTS5 table that does not remove diacritics from Latin
-- script characters, and that considers hyphens and underscore characters
-- to be part of tokens. 
CREATE VIRTUAL TABLE ft USING fts5(a, b, 
    tokenize = "unicode61 remove_diacritics 0 tokenchars '-_'"
);

-- Create an FTS5 table that, as well as the default token character classes,
-- considers characters in class "Mn" to be token characters.
CREATE VIRTUAL TABLE ft USING fts5(a, b, 
    tokenize = "unicode61 categories 'L* N* Co Mn'"
);

　使いどころは？　1文字単位で区切りたいときにtokenchars,separatorsを使えそう？　だがそれは全文検索として価値ある使い方なのかわからない。

ascii トークナイザ

　以下をのぞきunicode61と似ている。

  • すべての非ASCII文字は、常にトークン文字と見なされる
  • 大文字と小文字の区別は、ASCII文字に対してのみ実行される。A=a, Ã≠ã
  • remove_diacriticsオプションはサポートせず

　以下は数字をトークンとするコード例。

-- Create an FTS5 table that uses the ascii tokenizer, but does not
-- consider numeric characters to be part of tokens.
CREATE VIRTUAL TABLE ft USING fts5(a, b, 
    tokenize = "ascii separators '0123456789'"
);

　使いどころは？　英文なら使える？

porter トークナイザ

　英語用。correctionなどの検索ワードをcorrectedやcorrectingなどの類語に一致させることができる。

-- Two ways to create an FTS5 table that uses the porter tokenizer to
-- stem the output of the default tokenizer (unicode61). 
CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = porter); 
CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = 'porter unicode61');

-- A porter tokenizer used to stem the output of the unicode61 tokenizer,
-- with diacritics removed before stemming.
CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = 'porter unicode61 remove_diacritics 1');

　使いどころは？　英文なら使える？

外部コンテンツとコンテンツレステーブル

　通常、行がFTS5テーブルに挿入されると、さまざまなフルテキストインデックスエントリおよびその他のデータとともに、行のコピーがFTS5モジュールによって管理されるプライベートテーブルに保存される。ユーザーまたは補助関数の実装によってFTS5テーブルから列の値が要求されると、このプライベートテーブルから読み取られる。contentオプションを使用して、FTSフルテキストインデックスエントリのみを格納するFTS5テーブルを作成できる。列の値自体は通常、関連するフルテキストインデックスエントリよりもはるかに大きいため、これによりデータベースのスペースを大幅に節約できる。

　contentオプションを使用するには2つの方法がある。

  • 空の文字列に設定して、コンテンツのないFTS5テーブルを作成する。この場合、FTS5は、クエリを処理するときに元の列の値が利用できないと想定する。フルテキストクエリと一部の補助関数は引き続き使用できるが、rowid以外の列値をテーブルから読み取ることはできない。
  • FTS5が列の値を取得するためにいつでも照会できるデータベースオブジェクト（テーブル、仮想テーブル、またはビュー）の名前に設定する。これは「外部コンテンツ」テーブルとして知られている。この場合、すべてのFTS5機能を使用できるが、フルテキストインデックスの内容が指定されたデータベースオブジェクトと一致することを確認するのはユーザーの責任。そうでない場合、クエリ結果は予測できない場合がある。

コンテンツレステーブル

　コンテンツのないテーブルを作成する。設定オプションcontent=''とすることで。

CREATE VIRTUAL TABLE f1 USING fts5(a, b, c, content='');
  • UPDATE、DELETE、またはROWIDフィールドにNULL以外の値を提供しないINSERTステートメントをサポートせず
  • REPLACE競合処理をサポートせず
  • REPLACEおよびINSERT OR REPLACEステートメントは、通常のINSERTステートメントとして扱われる
  • FTS5 deleteコマンドを使用して、コンテンツのないテーブルから行を削除できる
  • ROWID以外の列値を読み取ろうとするとNULL値が返る

　以上、説明おわり。以下、検証。

　レコードを挿入し、検索してみると、データが一切入っていないことがわかる。

sqlite> insert into f1 values('AAA','BBB','CCC');
sqlite> select * from f1;
||
sqlite> select * from f1('AAA');
||
sqlite> select * from f1 where f1 match 'AAA';
||
sqlite> select * from f1 where f1 match 'a:AAA';
||

　他の自動作成されるテーブルも見てみる。

sqlite> select * from sqlite_master;
table|f1|f1|0|CREATE VIRTUAL TABLE f1 USING fts5(a, b, c, content='')
table|f1_data|f1_data|7|CREATE TABLE 'f1_data'(id INTEGER PRIMARY KEY, block BLOB)
table|f1_idx|f1_idx|8|CREATE TABLE 'f1_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID
table|f1_docsize|f1_docsize|9|CREATE TABLE 'f1_docsize'(id INTEGER PRIMARY KEY, sz BLOB)
table|f1_config|f1_config|10|CREATE TABLE 'f1_config'(k PRIMARY KEY, v) WITHOUT ROWID

sqlite> select * from f1;
||
sqlite> select * from f1_data;
1|
10|
137438953473|
sqlite> select * from f1_idx;
1||2
sqlite> select * from f1_docsize;
1|
sqlite> select * from f1_config;
version|4

　挿入したコンテンツAAA,BBB,CCCはどこにもない。

　使いどころは？　わからない。インデックスデータだけあって何の意味がある？　検索でヒットしてもデータが無いならどうしようもない。肝心のデータが削除されてしまってはスペースの節約もクソもないと思うのだが。

外部コンテンツテーブル

　外部コンテンツFTS5テーブルは、コンテンツオプションを同じデータベース内のテーブル、仮想テーブル、またはビュー（以下「コンテンツテーブル」）の名前に設定することによって作成される。FTS5で列の値が必要になると、値が必要な行のROWIDをSQL変数にバインドして、次のようにコンテンツテーブルを照会する。

SELECT <content_rowid>, <cols> FROM <content> WHERE <content_rowid> = ?;

　上記では、<content>はコンテンツテーブルの名前に置き換えられます。デフォルトでは、<content_rowid>はリテラルテキストrowidに置き換えられる。または、content_rowidオプションがCREATE VIRTUAL TABLEステートメント内で、そのオプションの値によって設定されている場合。<cols>は、FTS5テーブルの列名のコンマ区切りリストに置き換えられる。

-- If the database schema is: 
CREATE TABLE tbl (a, b, c, d INTEGER PRIMARY KEY);
CREATE VIRTUAL TABLE fts USING fts5(a, c, content=tbl, content_rowid=d);

-- Fts5 may issue queries such as:
SELECT d, a, c FROM tbl WHERE d = ?;

　コンテンツテーブルは、次のようにクエリすることもできます。

SELECT <content_rowid>, <cols> FROM <content> ORDER BY <content_rowid> ASC;
SELECT <content_rowid>, <cols> FROM <content> ORDER BY <content_rowid> DESC;

　外部コンテンツFTS5テーブルのコンテンツがコンテンツテーブルで最新の状態に保たれるようにすることは、依然としてユーザーの責任です。これを行う1つの方法は、トリガーを使用することです。例えば：

-- Create a table. And an external content fts5 table to index it.
CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c);
CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', content_rowid='a');

-- Triggers to keep the FTS index up to date.
CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;
CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
END;
CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;
  • REPLACE競合処理をサポートせず
  • REPLACE競合処理を指定する操作は、ABORTを使用する

　以上、説明おわり。以下、検証。

　上記テーブルとトリガーを作成したあと、以下のようにデータ挿入してみる。

insert into tbl(b,c) values('BBB', 'CCC');

　ちゃんとデータが入っていることを以下で確認。

sqlite> select * from tbl;
1|BBB|CCC

　tblはFTS5テーブルではないため、FTSクエリを使ってもエラーになる。

sqlite> select * from tbl('BBB');
Error: 'tbl' is not a function
sqlite> select * from tbl where tbl = 'BBB';
Error: no such column: tbl

　トリガーによってFTSテーブルに追加されたはず。確認してみる。

table|tbl|tbl|7|CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c)
table|fts_idx|fts_idx|0|CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', content_rowid='a')
table|fts_idx_data|fts_idx_data|8|CREATE TABLE 'fts_idx_data'(id INTEGER PRIMARY KEY, block BLOB)
table|fts_idx_idx|fts_idx_idx|9|CREATE TABLE 'fts_idx_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID
table|fts_idx_docsize|fts_idx_docsize|10|CREATE TABLE 'fts_idx_docsize'(id INTEGER PRIMARY KEY, sz BLOB)
table|fts_idx_config|fts_idx_config|11|CREATE TABLE 'fts_idx_config'(k PRIMARY KEY, v) WITHOUT ROWID
sqlite> select * from fts_idx;
BBB|CCC
sqlite> select * from fts_idx_data;
1|
10|
137438953473|
sqlite> select * from fts_idx_idx;
1||2
sqlite> select * from fts_idx_docsize;
1|
sqlite> select * from fts_idx_config;
version|4

　使いどころは？　デフォルトのFTS用テーブル名を変更するってことかな？

columnsize

　通常、FTS5は、個別のテーブルのメインFTS5テーブルに挿入されたトークンの各列値のサイズを格納する特別なバッキングテーブルをデータベース内に保持する。このバッキングテーブルは、 xColumnSize API関数によって使用され、組み込みのbm25ランキング関数によって使用される（他のランキング関数にも役立つ可能性がある）。

　スペースを節約するために、columnsizeオプションをゼロに設定することにより、このバッキングテーブルを省略できる。

-- ディスクに保存されたxColumnSize()値のないテーブル： 
CREATE VIRTUAL TABLE ft USING fts5(a, b, c, columnsize=0);

-- ディスク上にxColumnSize()値を格納するテーブルを作成する3つの同等の方法：
CREATE VIRTUAL TABLE ft USING fts5(a, b, c);
CREATE VIRTUAL TABLE ft USING fts5(a, b, c, columnsize=1);
CREATE VIRTUAL TABLE ft USING fts5(a, b, columnsize='1', c);
  • columnsizeオプションを0または1以外の値に設定するとエラーになる

　FTS5テーブルがcolumnsize=0で構成されているが、コンテンツのないテーブルではない 場合、xColumnSize API関数は引き続き機能するが、実行速度ははるかに遅くなる。この場合、値を読み取ってデータベースから直接返すのではなく、テキスト値自体を読み取り、オンデマンドでその中のトークンをカウントする。

　または、テーブルもコンテンツのないテーブルである場合、以下が適用されます。

  • xColumnSize APIは常に-1を返す。columnsize=0で構成されたコンテンツのないFTS5テーブル内に格納されている値のトークンの数を決定する方法はない。
  • 挿入された各行には、明示的に指定されたROWID値が必要。contentsizeテーブルがcolumnsize=0で構成されている場合、ROWIDにNULL値を挿入しようとすると、SQLITE_MISMATCHエラーになる
  • テーブルのすべてのクエリは、フルテキストクエリである必要がある。つまり、テーブル名列を左オペランドとしてMATCHまたは=演算子を使用するか、テーブル値関数構文を使用する必要がある。フルテキストクエリではないクエリはエラーになる。

　（columnsize=0が指定されていない限り）xColumnSize値が格納されるテーブルの名前は<name> _docsizeである。ここで、<name>はFTS5テーブル自体の名前である。sqlite3_analyzerのツールは、columnsize=0を使用してFTS5テーブルを再作成することによって保存される可能性がある。スペースを決定するために、既存のデータベースで使用することができる。

　使いどころは？　関連性順にソートしないときファイルサイズ減量したければ使う。

detail

　ドキュメント内の各用語について、FTS5によって維持されるFTSインデックスには、ドキュメントのROWID、用語を含む列の列番号、および列値内の用語のオフセットが格納される。detailオプションを使用して、この情報の一部を省略できる。これにより、データベースファイル内でインデックスが消費するスペースが削減されるが、システムの機能と効率も低下する。

　詳細オプションはfull(デフォルト)、column、none。

-次の2行は同等。「detail」のデフォルト値が「full」であるため
CREATE VIRTUAL TABLE ft1 USING fts5(a, b, c);
CREATE VIRTUAL TABLE ft1 USING fts5(a, b, c, detail=full);

CREATE VIRTUAL TABLE ft2 USING fts5(a, b, c, detail=column);
CREATE VIRTUAL TABLE ft3 USING fts5(a, b, c, detail=none);

　detail=columnのとき、FTSインデックスは各用語に対してROWIDと列番号のみを記録し、用語オフセット情報を省略する。これにより、次の制限が生じる。

  • NEARクエリは使用不可
  • フレーズクエリは使用不可
  • テーブルもないと仮定すると、contentlessテーブル、xInstCount、xInst、xPhraseFirstとxPhraseNext`は通常より遅くなる。FTSインデックスから必要なデータを直接読み取るのではなく、オンデマンドでドキュメントテキストを読み込んでトークン化する必要があるため
  • テーブルもコンテンツのないテーブルである場合、xInstCount、xInst、xPhraseFirst、およびxPhraseNext APIは、現在の行に一致するフレーズがまったく含まれていないように動作する（xInstCount()は0を返す）

　詳細オプションがnoneに設定されている場合、各用語に対してFTSインデックスはROWIDのみを記録します。列とオフセットの両方の情報は省略されます。detail=columnモードの上記の制限に加えて、これには次の追加の制限があります。

  • 列フィルタークエリは使用できません。
  • テーブルもコンテンツのないテーブルではないと仮定すると、 xPhraseFirstColumnと xPhraseNextColumnは通常より遅くなります。
  • テーブルもコンテンツのないテーブルである場合、xPhraseFirstColumnおよびxPhraseNextColumn APIは、現在の行に一致するフレーズがまったく含まれていないように動作します（つまり、xPhraseFirstColumn（）は反復子をEOFに設定します）。

　電子メールの大規模なセット（ディスク上で1636 MiB）のインデックスを作成した1つのテストでは、FTSインデックスはdetail=fullのディスクで743 MiB、detail=columnで340 MiB、detail=noneで134 MiBだった。

　使いどころは？　わからない。トークンに完全一致したらヒットさせるだけでいいときはファイルサイズを減らすことができる、ということだろう。ANDやORも使えないとなると、かなり厳しい。

所感

　有効なトークナイザを作らねばFTSは役に立たない。仮にできたとしても、本当にそれが最善であるかはわからない。無駄なキーワードをインデックスとして作りまくれば、無駄にファイルサイズを圧迫する。

　FTSのトークナイザは検索ワードの自動抽出処理である。

　もっと厳選した意味付けをすることで、ファイルサイズを削減しつつ、理解しやすく探せる気がする。それは全文検索ではなくカテゴライズやタギング、メタデータによる検索になるだろう。

対象環境

$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux

前回まで