fts5 を利用して全文検索。

準備

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

sqlite_lib.c

#define SQLITE_ENABLE_FTS5 1
#include "..\sqlite-amalgamation-3130000\sqlite3.c"

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

トークナイザーを準備

sqlite の標準のトークナイザーは英語にしか対応していない。
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;

Sqlite で全文検索

準備

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

トークナイザーを準備

テーブルを作るよ

情報をいれるよ

検索するよ

まとめ

蛇足