SQLite3
C++11

Sqlite で全文検索

More than 1 year has passed since last update.

fts5 を利用して全文検索。


準備

まず最初に SQLITE_ENABLE_FTS5 を指定して sqlite をコンパイルし直さないといけない。


sqlite_lib.c

#define SQLITE_ENABLE_FTS5 1

#include "..\sqlite-amalgamation-3130000\sqlite3.c"


全文検索の仕組みを作る。


トークナイザーを準備


  1. sqlite の 標準のトークナイザーは英語にしか対応していない。

  2. sqlite の fts モジュールは単語単位の 前方一致か、完全一致 しか対応していない。(っぽい?)

なのでトークナイザーを作らないと行けない。


test.cpp

#include "sqlite3.h"

#include <string>
#include <sstream>

static void makeFtsIndex(
sqlite3_context *context,
int argc,
sqlite3_value **argv
) {
// tokenize text to allow fts backword/partial search.
// 'alpha beta gamma あいうえお' -> 'alpha lpha pha ha a beta eta ta a gamma amma mma ma a あいうえお いうえお うえお えお お'
// https://blog.kapeli.com/sqlite-fts-contains-and-suffix-matches

// backward search.
// select * from ftstable('eta');
//
// partial search
// select * from ftstable('eta*');

std::wstringstream result;

for (int i = 0; i < argc; ++i) {
if (sqlite3_value_type(argv[i]) == SQLITE_NULL) {
continue;
}
std::wstring value;
value = (wchar_t *)sqlite3_value_text16(argv[i]);

for (size_t _Off = 0; ;) {
size_t found = value.find(L' ', _Off);
std::wstring part = value.substr(_Off, found - _Off);

for (int j = 0, end = part.length(); j != end; ++j) {
result << L" " << part.substr(j);
}

if (found == std::wstring::npos) {
break;
}

_Off = value.find_first_not_of(L' ', found);
}
}

std::wstring retstr = result.str();
if (retstr.length()) {
retstr = retstr.substr(1);
}

sqlite3_result_text16(context, retstr.c_str(), -1, SQLITE_TRANSIENT);
}

main() {
sqlite3 *pdb;
sqlite3_open(":memory:", &pdb);
sqlite3_create_function(pdb, "makeFtsIndex", 1, SQLITE_UTF8, NULL, &makeFtsIndex, NULL, NULL);
// SQLITE_UTF8 はもしかしたら SQLITE_UTF16


部分検索が目的なので、こんな感じのトークナイザー。

N-Gram を使えみたいな情報もググったら出てくるけど、この方法と、N-Gram の方法とでメリット・デメリットが良くわかっていないのツッコミ待ち。


テーブルを作るよ

最初に普通にテーブルを作る。

CREATE TABLE FILE

(id INTEGER PRIMARY KEY AUTOINCREMENT
,path TEXT UNIQUE COLLATE NOCASE
,attributes INTEGER
,modified INTEGER
,created INTEGER
,size INTEGER
);

で、トークン情報を貯めるテーブルを作る。

CREATE VIRTUAL TABLE _fts USING fts5(

path, content='FILE', content_rowid='id'
);

で、FILE テーブルを操作した際に一緒にトークン情報を操作してくれるトリガーも作る。

CREATE TRIGGER file_ai AFTER INSERT ON FILE BEGIN

INSERT INTO _fts (rowid, path) VALUES (new.id, makeFtsIndex(new.path));
END;
CREATE TRIGGER file_ad AFTER DELETE ON FILE BEGIN
INSERT INTO _fts (fts_idx, rowid, path) VALUES('delete', old.id, makeFtsIndex(old.path));
END;
CREATE TRIGGER file_au AFTER UPDATE OF path ON FILE BEGIN
INSERT INTO _fts (fts_idx, rowid, path) VALUES('delete', old.id, makeFtsIndex(old.path));
INSERT INTO _fts (rowid, path) VALUES (new.id, makeFtsIndex(new.path));
END;

だいたい sqlite のドキュメントに載ってる情報そのまんまだけど、最初に作ったトークナイザーを利用してる。

UPDATE トリガーは path 列に変更があった時のみ反応するように変更。

_fts への操作が全部 INSERT じゃん!というのはそういうものなのだとしか言い様がない。


情報をいれるよ

INSERT INTO FILE (path, size, modified, created, attributes) VALUES (@path, @size, @modified, @created, @attributes);

普通に INSERT の SQL を発行するだけ。

トークン情報の操作は、トリガーがやってくれる。

DELETE や UPDATE もトリガー任せ。


検索するよ

SELECT * FROM FILE WHERE id IN (

SELECT rowid FROM _fts WHERE _fts MATCH 'et*'
);

これで、LIKE %et% 相当。速度比較は省略です。

検索は _fts を検索して、得られた rowid を FILE テーブルで検索という形。

fts5 は fts3/4 と少し SQL が違うので注意らしいです。

相違点はドキュメント見てくださいですー。


まとめ

結局、fts5 じゃなくてもトークナイザーの作り方でどうとでもなりそう。

新しいっぽいから fts5 を利用しましたが、SqliteManager や公式の sqlite3.exe が fts5 に対応してないよ!とか言ってくるので、結構頭きます。ふつうに fts3/4 使ったほうがいいんじゃないですかね。


蛇足

重火力の脚パーツはとても重要なのです。

-- トークン 情報削除

DROP TRIGGER file_ai;
DROP TRIGGER file_ad;
DROP TRIGGER file_au;
DROP TABLE _fts;

-- トークン 情報生成
CREATE VIRTUAL TABLE _fts USING fts5(
path, content='FILE', content_rowid='id'
);

CREATE TRIGGER file_ai AFTER INSERT ON FILE BEGIN
INSERT INTO _fts (rowid, path) VALUES (new.id, makeFtsIndex(new.path));
END;
CREATE TRIGGER file_ad AFTER DELETE ON FILE BEGIN
INSERT INTO _fts (fts_idx, rowid, path) VALUES('delete', old.id, makeFtsIndex(old.path));
END;
CREATE TRIGGER file_au AFTER UPDATE ON FILE BEGIN
INSERT INTO _fts (fts_idx, rowid, path) VALUES('delete', old.id, makeFtsIndex(old.path));
INSERT INTO _fts (rowid, path) VALUES (new.id, makeFtsIndex(new.path));
END;

-- 既存テーブルから一気に トークン情報をいれる
INSERT INTO _fts (rowid, path) SELECT id, makeFtsIndex(path) FROM FILE;