Python Sqlite3 部署FTS5 中文拼音全文检索 simple分词器

因为项目需要配置一个基于sqlite数据库的拼音全文检索，找到了Wang Fenjin的解决方案，一个基于jieba分词器的Simple FTS5 分词器：

Simple: 一个支持中文和拼音搜索的 sqlite fts5插件

Simple分词器实现的需求比较明确：

搜索的核心是建倒排索引，建索引的核心是分词器。 跟名字一样，Simple 分词器的规则非常简单：

1. 空白符跳过
2. 连续的数字作为整体是一个索引
3. 连续的英文字母作为整体并转换成小写索引
4. 中文字单独建索引，并且把中文字转成拼音后也建搜索，这样就能同时支持中文和拼音检索。另外把拼音首字母也建索引，这样搜索 zjl 就能命中 “周杰伦”。
5. 其他字符统一单独建索引，这样搜索 😊 也能搜到

Simple分词器主要功能

1. simple tokenizer 支持中文和拼音的分词，并且可通过开关控制是否需要支持拼音；
2. simple_query() 函数实现自动组装 match query 的功能，用户不用学习 fts5 query 的语法；
3. simple_highlight() 实现连续高亮 match 的词汇，与 sqlite 自带的 highlight 类似，但是 ；
4. simple_highlight 实现了连续 match 的词汇分到同一组的逻辑，理论上用户更需要这样；
5. simple_highlight_pos() 实现返回 match 的词汇位置，用户可以自行决定怎么使用；
6. simple_snippet() 实现截取 match 片段的功能，与 sqlite 自带的 snippet 功能类似，同样是增强连续 match 的词汇分到同一组的逻辑；
7. jieba_query() 实现jieba分词的效果，在索引不变的情况下，可以实现更精准的匹配。可以通过 -DSIMPLE_WITH_JIEBA=OFF 关掉结巴分词的功能 ；
8. jieba_dict() 指定 dict 的目录，只需要调用一次，需要在调用 jieba_query() 之前指定。（这一点得注意）

Simple分词器的具体使用方法

工作流如下：

SQLITE加载simple分词器插件 --> SQLITE加载字典 --> 创建虚拟表 --> 创建触发器 --> 正常使用

1. 下载编译好的分词器插件：

Simple分词器下载地址支持中文和拼音的 SQLite fts5 全文搜索扩展 ｜ A SQLite3 fts5 tokenizer which supports Chinese and PinYin - Releases · wangfenjin/simple
目前有Linux（Arm）、Ubuntu（x86）、MacOS、Win几个版本的版本。

2. 下载后将资源解压并存放在自定的路径下：

   路径下有一个libsimple.dylib(macos) (win环境为simple.dll, linux环境为libsimple.so)，以及一个字典文件夹dict（保存着jieba分词的字典，可以自行修改）；

3. sqlite数据库加载simple分词器 和 字典

conn = sqlite3.connect('sqlite数据库路径')
conn.enable_load_extension(True)
conn.load_extension('simple分词器的绝对路径')
cur = conn.cursor()
cur.execute(f"SELECT jieba_dict('dict文件夹的绝对路径')

4. 创建虚拟表并将原表数据插入虚拟表中

CREATE VIRTUAL TABLE IF NOT EXISTS record_fts USING fts5
(ID UNINDEXED,
KEYWORD,
TITLE,
SUMMARY,
OWNER,
AGENT,
TYPE,
REGION,
URL,
tokenize = 'simple');

注意最后的 tokenize = ‘simple’

INSERT INTO record_fts(ID, KEYWORD, TITLE, SUMMARY, OWNER, AGENT, TYPE, REGION, URL)
SELECT ID, KEYWORD, TITLE, SUMMARY, OWNER, AGENT, TYPE, REGION, URL FROM record;

5. 创建after-insert、after-update和after-delete三个触发器，确保虚拟表和主表的数据一致性

after-insert SQL:

CREATE TRIGGER record_ai AFTER INSERT ON record
BEGIN
	INSERT INTO record_fts(id, KEYWORD, TITLE, SUMMARY, OWNER, AGENT, TYPE, REGION, URL)
	VALUES (new.id, new.KEYWORD, new.TITLE, new.SUMMARY, new.OWNER, new.AGENT, new.TYPE, new.REGION, new.URL);
END;

after-update SQL:

CREATE TRIGGER record_au AFTER UPDATE ON record
BEGIN
UPDATE record_fts SET
ID = new.ID,
KEYWORD = new.KEYWORD,
TITLE = new.TITLE,
SUMMARY = new.SUMMARY,
OWNER = new.OWNER,
AGENT = new.AGENT,
TYPE = new.TYPE,
REGION = new.REGION,
URL = new.URL
WHERE rowid = old.id;
END; 

after-delete SQL:

CREATE TRIGGER record_ad AFTER DELETE ON record
BEGIN
DELETE FROM record_fts WHERE rowid = old.id;
END;

创建虚拟表以及三个触发器后，我的项目数据库可以自动在有数据变动的时候同步至虚拟表中，确保全文检索所依据的虚拟表数据与源数据保持同步。

6. 从数据库获取全文检索数据

主要使用如下query SQL:

MATCH 函数后可以选择用 simple_query 或者 jieba_query，jieba_query() 实现jieba分词的效果，在索引不变的情况下，可以实现更精准的匹配。

SELECT TITLE, SUMMARY, URL FROM record_fts WHERE record_fts MATCH jieba_query(?);

这样，就完成了基于sqlite和simple分词器的全文检索，可以直接搜索中文字词，也可以搜索对应的拼音，效果如下：

重要的事情是要在初始化数据库连接后，加载simple分词器插件，并指定字典dict文件夹所在的绝对路径.