SQLite FTS5 中文检索实践

发表于 更新于 分类于 评论： 阅读次数：

本文记录了我在探索和实践 SQLite FTS5 中英文全文检索过程中遇到的一些问题。

一、前言

近期试用 SQLite FTS5，发现英文全文检索效果还不错。但我手头有很多数据是中文的，所以在网上寻找相关方案并实践了一下。本文主要是总结一下动手实践中遇到的各种问题。

主要问题/关键点如下：

  • 如何检查 SQLite3 是否支持 .load 命令
  • 如何检查 SQLite3 是否已启用 fts5 模块
    • 检查 select fts5(?1); 命令是否报错
  • 如何编译 SQLite3
    • 查看官方文档以及源码 README
    • 根据文档尝试编译
  • 如何编译 simple 扩展
    • 查看源码 README
    • 根据文档尝试编译
  • 如何加载并使用 SQLite 扩展
    • 使用 .load 加载 SQLite 扩展
    • 使用扩展模块提供的方法。比如，用 jieba_dict() 来加载 jieba 分词数据
  • 在 DB Browser for SQLite 加载扩展
  • 中文检索效果验证

二、SQLite 特性检查

一个很容易忽视的问题是，已安装好的 SQLite 可能缺少某些特性。比如，并不支持加载扩展模块，或者不支持 FTS5。所以在 SQLite 中动手实践中文全文检索时，有必要检查一下当前 SQLite 可支持的特性。

2.1 是否支持扩展模块

我的 Mac 上的 SQLite 是通过 brew install sqlite3 来安装的。在我不使用加载扩展模块的情况，这个 SQLite 工作良好。但当我执行 .load libsimple 尝试加载 libsimple 动态库时，遇到以下奇怪的错误：

1
Error: unknown command or invalid arguments

SQLite 提示这里有未知的命令或者无效的参数。反复检查后确认参数是正确的，所以推测只能是 .load 命令出问题了。

我在 Stackoverflow 上找到类似的问题， sqlite3 does not have a load extension enabled。有人在问题回复给出有价值的线索，

Most likely your sqlite compiled with SQLITE_OMIT_LOAD_EXTENSION. Get the one build without this flag, or build it yourself.

SQLite 官网 提到，如果指定 SQLITE_OMIT_LOAD_EXTENSION 编译选项，编译出来的 SQLite 中会完全忽略扩展机制。

1
2
3
4
5
6
7
8
9
10
11
SQLite version 3.39.4 2022-09-29 15:55:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .help

.auth ON|OFF             Show authorizer callbacks
.backup ?DB? FILE        Backup DB (default "main") to FILE
.bail on|off             Stop after hitting an error.  Default OFF
.binary on|off           Turn binary output on or off.  Default OFF
.cd DIRECTORY            Change the working directory to DIRECTORY

可以在 SQLite 中使用 .help 命令检查是否有 .load 这一行。如果找不到 .load，说明当前的 SQLite 是不支持加载扩展的。

-w1988

不能加载扩展的 SQLite 无法通过外部扩展方式来支持中文分词器，也就无从支持中文全文检索。

2.2 是否支持 FTS5

我下载 SQLite 3.39.4 源码编译了一个新的 SQLite，以支持加载扩展。

我在新的 SQLite 尝试加载 libsimple，又碰到了另一个奇怪的错误：

1
2
3
4
5
6
SQLite version 3.39.4 2022-09-29 15:55:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load /usr/local/bin/libsimple.dylib
Error: error during initialization:

猜测新的 SQLite 没有开启 fts5。使用 select fts5(?1); 验证一下，果然如此。

1
2
3
4
5
6
7
8
SQLite version 3.39.4 2022-09-29 15:55:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select fts5(?1);
Parse error: no such function: fts5
  select fts5(?1);
         ^--- error here

-w1899

三、SQLite 扩展编译

3.1 编译 SQLite

这里的源码下载自 SQLite 3.39.4。

  1. 源码解压到根目录 sqlite-version-3.39.4
  2. 新建一个 bld 目录 (与根目录同级)

目录如下：

1
2
3
4
5
6
.
├── bld
├── sqlite-version-3.39.4
│   ├── src
│   └── vsixtest
└── sqlite-version-3.39.4.zip

开始编译：

1
2
3
cd bld
../sqlite-version-3.39.4/configure
make

-w1134

也可以参考 How to compile sqlite。这里提到了具体的编译选项。

1
2
3
4
5
gcc -Os -I. -DSQLITE_THREADSAFE=0 -DSQLITE_ENABLE_FTS4 \
   -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_JSON1 \
   -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_EXPLAIN_COMMENTS \
   -DHAVE_READLINE \
   shell.c sqlite3.c -ldl -lm -lreadline -lncurses -o out/sqlite3

3.2 编译 simple

simple 的编译就比较简单了，

1
2
3
4
mkdir build; cd build
cmake ..
make -j 12
make install

编译输出如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
[100%] Linking CXX executable ../src/simple_tests
[100%] Built target simple_tests

[ 51%] Built target cppjieba
[ 67%] Built target simple
[ 74%] Built target simple_cpp_example
[ 80%] Built target gtest
[ 87%] Built target gtest_main
[100%] Built target simple_tests
Install the project...
-- Install configuration: "Release"
-- Installing: /usr/local/lib/libsqlite3.a
-- Up-to-date: /usr/local/include/sqlite3.h
-- Up-to-date: /usr/local/include/sqlite3ext.h
-- Installing: /usr/local/cmake/SQLite3Config.cmake
-- Installing: /usr/local/cmake/SQLite3Config-release.cmake
-- Installing: /usr/local/include/sqlite3_config.h
-- Installing: /usr/local/bin/sqlite3
-- Up-to-date: /usr/local/bin/dict
-- Up-to-date: /usr/local/bin/dict/pos_dict
-- Installing: /usr/local/bin/dict/pos_dict/prob_trans.utf8
-- Installing: /usr/local/bin/dict/pos_dict/prob_emit.utf8
-- Installing: /usr/local/bin/dict/pos_dict/char_state_tab.utf8
-- Installing: /usr/local/bin/dict/pos_dict/prob_start.utf8
-- Installing: /usr/local/bin/dict/stop_words.utf8
-- Installing: /usr/local/bin/dict/hmm_model.utf8
-- Installing: /usr/local/bin/dict/user.dict.utf8
-- Installing: /usr/local/bin/dict/idf.utf8
-- Installing: /usr/local/bin/dict/jieba.dict.utf8
-- Installing: /usr/local/bin/libsimple.dylib
-- Installing: /usr/local/bin/simple_cpp_example

不过我不太明白，为什么编译 simple 扩展时又给重编了一个 SQLite 并且安装到 /usr/local/bin/sqlite3。

四、SQLite 扩展使用

4.1 命令行

在命令行下加载扩展并使用，操作上还是比较简单的。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
simple % /usr/local/bin/sqlite3
SQLite version 3.32.3 2020-06-18 14:00:33
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select fts5(?1);

sqlite> .load /usr/local/bin/libsimple.dylib
sqlite> select jieba_dict('.');
./
sqlite> CREATE VIRTUAL TABLE t1 USING fts5(text, tokenize = 'simple');
sqlite> INSERT INTO t1 VALUES ('广东深圳');
sqlite> INSERT INTO t1 VALUES ('深圳宝安中心');
sqlite> select simple_highlight(t1, 0, '[', ']') as text from t1 where text match jieba_query('深圳');

主要的问题可能是文档中并没有特别明确地说明 jieba_dict() 函数的路径参数该如何指定 Issue 2。如果路径参数不正确，会出现以下错误：

1
2
simple/build/cppjieba/src/cppjieba/include/cppjieba/DictTrie.hpp:203 FATAL exp: [ifs.is_open()] false. open ./jieba.dict.utf8 failed.
zsh: abort      /usr/local/bin/sqlite3

从上述错误信息中的类名及路径名，我们不难找到正确的目录。从下图可以看到，该目录下有 jieba 需要加载的数据文件。

-w1371

小技巧：如果编译 simple 时你仔细观察，会发现其实已经拷贝了一份数据到 /usr/local/bin/dict/ 目录，所以你也可以指定为这个路径。

再试一次。见下图：

  1. 我们用 jieba_dict() 函数指定正确的路径
  2. 使用 深圳 和 sz 作为关键字，成功检索！

-w1268

4.2 DB Browser for SQLite

DB Browser for SQLite 是一个开源的 SQLite 管理工具，简单易用。但是我没法在其中加载 /usr/local/bin/libsimple.dylib 这个路径下的扩展。错误如下：

-w418

Issue #3357 提到了相同的错误。我从这个 Issue 中找到了一个修复版本。我的 Macbook M2 安装修复版本后可正常加载 simple 扩展。

按照 Issue #3357 中的讨论，修复版本仅仅是关闭了 DB Browser for SQLite 的 ‘Hardend Runtime’ security option

Good morning. Here’s a build for testing. (I’ve double checked that on my Mac, I’m able to load the extension just fine)
However, there is one issue that needs to be discussed. I’ve turned off the ‘Hardend Runtime’ security option to allow users to load external libaries (signed with a different team id or unsigned), but in this case Apple does not allow notarization.

1
SELECT jieba_dict('/usr/local/bin/dict/')

-w1337

-w1329

-w1333

按照 DB Browser for SQLite 官方的说法，最新的待发布版本应当也修复了这个无法加载扩展的问题，不过我的机器上问题仍存在。

暂且就使用 DB Browser for SQLite 的临时修复版本吧。

-w676

五、效果演示

TODO

六、参考文章