Sqlite で全文検索

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;