转载

使用 Python 的 SQLite JSON1 和 FTS5 扩展

早在九月份,编程界出现一个名为 json1.c 的文件,此前这个文件一直在 SQLite 的库里面。还有,笔者也曾总结通过使用新的 json1 扩展来编译 pysqlite 的技巧。但现在随着 SQLite 3.9.0 的发布,用户已经不用再费那么大劲了。

SQLite 3.9.0 版本做了很大的升级,不仅增加了万众期待的 json1 扩展,还增加了具有全文检索的新版本 fts5 扩展模块。 fts5 扩展模块提高了复杂查询的性能,并且提供了开箱即用的 BM25 排序算法。该算法在其他相关领域排序方面也有着重大意义。使用者可通过查看 发布说明 以了解全部新增功能。

本文主要介绍如何添加 json1 和 fts5 扩展编译 SQLite。这里将使用新版 SQLite 库编译 python 驱动程序,也利用 python 新功能。由于个人很喜欢 pysqlite 和 apsw,所以下文步骤中将会包括建立两者的指令。最后,将在 peewee ORM 通过 json1 和 fts5 扩展进行查询。

使用入门

首先从获取新版 SQLite 源码入手,一种方法是通过使用 SQLite 源代码管理系统 fossil 来完成,另一种是下载一个压缩图像。 SQLite 使用 tclawk 进行源码融合,因此在开始前,需要安装下列工具:

  • tcl
  • awk (可用于大多数 unix系统)
  • fossil (可选)

该过程涉及几个步骤,这里尽量将步骤细化。首先需要为新库分配一个全新的目录,笔者把它放在 ~/bin/jqlite 中,使用者可根据个人喜好自行选择。

export JQLITE="$HOME/bin/jqlite" mkdir -p $JQLITE cd $JQLITE

通过 fossil 获取源码,运行以下命令:

fossil clone http://www.sqlite.org/cgi/src sqlite.fossil fossil open sqlite.fossil

获取快照文件,运行以下命令:

curl 'https://www.sqlite.org/src/tarball/sqlite.tar.gz?ci=trunk' | tar xz mv sqlite/* .

如果你更喜欢使用官方正式版,可在 SQLite 下载页 下载 autoconf 的压缩包,并将内容解压到 $JQLITE 目录中。

利用 json1 和 fts5 编译 SQLite

代码下载完成后,把它和 SQLite 源代码树放在同一目录下。SQLite 支持大量的 编译配置选项 ,除了 json1fts5 ,还有很多其他有效的选择。

编译遵循典型的 configure -> make -> make install 顺序 :

export CFLAGS="-DSQLITE_ENABLE_COLUMN_METADATA=1 / -DSQLITE_ENABLE_DBSTAT_VTAB=1 / -DSQLITE_ENABLE_FTS3=1 / -DSQLITE_ENABLE_FTS3_PARENTHESIS=1 / -DSQLITE_ENABLE_FTS5=1 / -DSQLITE_ENABLE_JSON1=1 / -DSQLITE_ENABLE_RTREE=1 / -DSQLITE_ENABLE_UNLOCK_NOTIFY / -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT / -DSQLITE_SECURE_DELETE / -DSQLITE_SOUNDEX / -DSQLITE_TEMP_STORE=3 / -fPIC" LIBS="-lm" ./configure --prefix=$JQLITE --enable-static --enable-shared make make install

在 SQLite3 Source Checkout 中,应该有一个 lib/libsqlite3.a 文件。如果文件不存在,检查控制器的输出,查看错误日志。我在 arch 和 ubuntu 上都已执行成功,但 fapple 和 windoze 我不确定能否成功。

创建 pysqlite

大多数 python 开发者对 pysqlite 一定不陌生,在 Python 标准库中 pysqlite 或多或少的和 sqlite3 模块相似。要建立和 libsqlite3 相对应的 pysqlite,唯一需要做的是修改 setup.cfg 文件使其指向刚才创建的 includelib 目录。 git clone https://github.com/ghaering/pysqlite cd pysqlite/ cp ../sqlite3.c . echo -e "library_dirs=$JQLITE/lib" >> setup.cfg echo -e "include_dirs=$JQLITE/include" >> setup.cfg LIBS="-lm" python setup.py build_static

测试安装,进入 build/lib.linux-xfoobar/ 目录,启动 Python 解释器,运行以下命令:

```

from pysqlite2 import dbapi2 as sqlite conn = sqlite.connect(':memory:') conn.execute('CREATE VIRTUAL TABLE testing USING fts5(data);') conn.execute('SELECT json(?)', (1337,)).fetchone() (u'1337',) ```

接下来就看你心情了,你可以运行 python setup.py 安装文件,也可以把新建的 pysqlite2 (可在 build/lib.linux.../ 目录下查看)链接到 $PYTHONPATH 。如果想同时使用 virtualenv 和 $PYTHONPATH ,可以先激活 virtualenv,然后返回 pysqlite 目录下运行 setup.py 来安装文件。

创建 apsw

创建 apsw 的步骤几乎和建立 pysqlite 相同。 cd $JQLITE git clone https://github.com/rogerbinns/apsw cd apsw cp ../sqlite3{ext.h,.h,.c} . echo -e "library_dirs=$SQLITE_SRC/lib" >> setup.cfg echo -e "include_dirs=$SQLITE_SRC/include" >> setup.cfg LIBS="-lm" python setup.py build 为了测试新的 apsw 库,更改目录到 build/libXXX 。启动 Python 解释器,运行下列命令: ```

import apsw conn = apsw.Connection(':memory:') cursor = conn.cursor() cursor.execute('CREATE VIRTUAL TABLE testing USING fts5(data);') cursor.execute('SELECT json(?)', (1337,)).fetchone() (u'1337',) `` 可通过运行 Python setup.py 安装文件来安装新 apsw 全系统,或者链接 apsw.so 库(可在 build/lib.linux.../ 查看)到 $PYTHONPATH。如果开发者想同时使用 virtualenv 和 apsw ,可以先激活 virtualenv,然后返回 apsw 目录下运行 setup.py` 安装文件。

使用 JSON 扩展

json1 扩展中具有一些简洁特性,尤其是 json_treejson_each 函数/虚拟表( 详情 )。为了展示这些新功能,本文特意利用 peewee (小型 Python ORM)编写了一些 JSON 数据并进行查询。

原打算从 GitHub 的 API 上获取测试数据,但为了展示最少冗长这个特性,特意选择编写一个小的 JSON 文件( 详情 )。其结构如下: [{ "title": "My List of Python and SQLite Resources", "url": "http://charlesleifer.com/blog/my-list-of-python-and-sqlite-resources/", "metadata": {"tags": ["python", "sqlite"]} }, { "title": "Using SQLite4's LSM Storage Engine as a Stand-alone NoSQL Database with Python" "url": "http://charlesleifer.com/blog/using-sqlite4-s-lsm-storage-engine-as-a-stand-alone-nosql-database-with-python/", "metadata": {"tags": ["nosql", "python", "sqlite", "cython"]} }, ...]

如果更愿意以 IPython 格式查看代码, 参考此处 。

填充数据库

获取 JSON 数据文件和进行解码: ```

import json, urllib2 fh = urllib2.urlopen('http://media.charlesleifer.com/downloads/misc/blogs.json') data = json.loads(fh.read()) data[0] {u'metadata': {u'tags': [u'python', u'sqlite']}, u'title': u'My List of Python and SQLite Resources', u'url': u'http://charlesleifer.com/blog/my-list-of-python-and-sqlite-resources/'} ```

现在,需要告知 peewee 怎样去访问我们数据库,通过存入 SQLite 数据库的方式使用自定义的 pysqlite 接口。这里使用的是刚刚编译完成的 pysqlite2 ,虽然它和 tojqlite 有所混淆,但这并不冲突。在定义数据库类后,将创建一个内存数据库。(注:在接下来的2.6.5版本中,如果其使用比 sqlite3 更新版本编译,peewee 将自动使用 pysqlite2 )。 ```

from pysqlite2 import dbapi2 as jqlite from peewee import * from playhouse.sqlite_ext import * class JQLiteDatabase(SqliteExtDatabase): ... def _connect(self, database, **kwargs): ... conn = jqlite.connect(database, **kwargs) ... conn.isolation_level = None ... self._add_conn_hooks(conn) ... return conn ... db = JQLiteDatabase(':memory:') ```

利用 JSON 数据填充数据库十分简单。首先使用单一 TEXT 字段创建一个通用表。此时,SQLite 不会显示 JSON 数据单独的列/数据类型,所以需要使用 TextField : ```

class Entry(Model): ... data = TextField() ... class Meta: ... database = db ... Entry.create_table() with db.atomic(): ... for entry_json in data: ... Entry.create(data=json.dumps(entry_json)) ... ```

JSON 的功能

首先介绍下 json_extract() 。它通过点/括号的路径来描述要找的元素(postgres 使用的是[])。数据库的每个 Entry 中包含单一数据列,每个数据列中又包含一个 JSON 对象。每个 JSON 对象包括一个标题,一个 URL 和顶层的元数据键,下面是提取作品标题的代码: ```

title = fn.json_extract(Entry.data, '$.title') query = (Entry ... .select(title.alias('title')) ... .order_by(title) ... .limit(5)) ... [row for row in query.dicts()] [{'title': u'A Tour of Tagging Schemas: Many-to-many, Bitmaps and More'}, {'title': u'Alternative Redis-Like Databases with Python'}, {'title': u'Building the SQLite FTS5 Search Extension'}, {'title': u'Connor Thomas Leifer'}, {'title': u'Extending SQLite with Python'}] ```

对应下面 SQL 创建的查询:

SELECT json_extract("t1"."data", '$.title') AS title FROM "entry" AS t1 ORDER BY json_extract("t1"."data", '$.title') LIMIT 5

在接下来的例子中,将提取包含特定标签的条目。利用 json_each() 函数搜索标签列表。该函数类似于表(实际指的是虚表),返回筛选后的指定 JSON 路径,下面是如何检索标题为「Sqlite」条目的代码。 ```

from peewee import Entity tags_src = fn.json_each(Entry.data, '$.metadata.tags').alias('tags') tags_ref = Entity('tags')

query = (Entry ... .select(title.alias('title')) ... .from_(Entry, tags_src) ... .where(tags_ref.value == 'sqlite') ... .order_by(title)) ... [row for row, in query.tuples()] [u'Building the SQLite FTS5 Search Extension', u'Extending SQLite with Python', u'Meet Scout, a Search Server Powered by SQLite', u'My List of Python and SQLite Resources', u'Querying Tree Structures in SQLite using Python and the Transitive Closure Extension', u"Using SQLite4's LSM Storage Engine as a Stand-alone NoSQL Database with Python", u'Web-based SQLite Database Browser, powered by Flask and Peewee'] ```

上述查询的 SQL 有助阐明整个过程:

SELECT json_extract("t1"."data", '$.title') AS title FROM "entry" AS t1, json_each("t1"."data", '$.metadata.tags') AS tags WHERE ("tags"."value" = 'sqlite') ORDER BY json_extract("t1"."data", '$.title')

随着查询变得更加复杂,可通过使用 Peewee 对象对查询进行封装,使之变得更加有用,同时也使得代码能够重用。

下面是 json_each() 的另一个例子。这次将筛选每个条目中的标题,并建立相关标签的字符串,字符串中用逗号分隔。这里将再次使用上文定义的 tags_srctags_ref 。 ```

query = (Entry ... .select( ... title.alias('title'), ... fn.group_concat(tags_ref.value, ', ').alias('tags')) ... .from_(Entry, tags_src) ... .group_by(title) ... .limit(5)) ... [row for row in query.tuples()] [(u'A Tour of Tagging Schemas: Many-to-many, Bitmaps and More', u'peewee, sql, python'), (u'Alternative Redis-Like Databases with Python', u'python, walrus, redis, nosql'), (u'Building the SQLite FTS5 Search Extension', u'sqlite, search, python, peewee'), (u'Connor Thomas Leifer', u'thoughts'), (u'Extending SQLite with Python', u'peewee, python, sqlite')] ```

为了清晰起见,这里是对应的 SQL 查询语句: SELECT json_extract("t1"."data", '$.title') AS title, group_concat("tags"."value", ', ') AS tags FROM "entry" AS t1, json_each("t1"."data", '$.metadata.tags') AS tags GROUP BY json_extract("t1"."data", '$.title') LIMIT 5

最后介绍的功能是 json_tree() 。如同 json_each()json_tree() 同样是一个多值函数,同样与表类似。但不同但时 json_each() 仅返回特定路径的 children,而 json_tree() 将递归遍历全部对象,返回全部的 children。

如果标签键嵌套在条目的任意位置,下面是如何匹配给定标签条目的代码: ```

tree = fn.json_tree(Entry.data, '$').alias('tree') parent = fn.json_tree(Entry.data, '$').alias('parent')

tree_ref = Entity('tree') parent_ref = Entity('parent')

query = (Entry ... .select(title.alias('title')) ... .from_(Entry, tree, parent) ... .where( ... (tree_ref.parent == parent_ref.id) & ... (parent_ref.key == 'tags') & ... (tree_ref.value == 'sqlite')) ... .order_by(title)) ... [title for title, in query.tuples()] [u'Building the SQLite FTS5 Search Extension', u'Extending SQLite with Python', u'Meet Scout, a Search Server Powered by SQLite', u'My List of Python and SQLite Resources', u'Querying Tree Structures in SQLite using Python and the Transitive Closure Extension', u"Using SQLite4's LSM Storage Engine as a Stand-alone NoSQL Database with Python", u'Web-based SQLite Database Browser, powered by Flask and Peewee'] ```

在上述代码中选取了 Entry 自身,以及代表该 Entry 子节点的二叉树。因为每个树节点包含对父节点的引用,我们可以十分简单搜索命名为「标签」的父节点,该父节点包含值为「sqlite」的子节点。 下面是 SQL 实现语句: SELECT json_extract("t1"."data", '$.title') AS title FROM "entry" AS t1, json_tree("t1"."data", '$') AS tree, json_tree("t1"."data", '$') AS parent WHERE ( ("tree"."parent" = "parent"."id") AND ("parent"."key" = 'tags') AND ("tree"."value" = 'sqlite')) ORDER BY json_extract("t1"."data", '$.title')

这仅是 json1 扩展功能的一个方面 ,在接下来的几周将会尝试使用其更多的功能。请在此处给我留言,或者如果对该扩展存在特定的问题,可通过邮件向 sqlite-users 咨询。

FTS5 与 Python

本小节中的代码均是之前 JSON 示例中的代码,这里将使用 Entry 数据文件的标题并且用它们填充搜索索引。peewee 2.6.5版本将包含 FTS5Model 功能,目前该功能可在 Github 主分支上可用。

重新回到之前的 JSON 例子中去,新建另一张表,作为 Entry 数据的查询索引。

fts5 扩展要求所有的列不包含任何类型或约束。用于表示一列的唯一附加信息是无索引,意味着只能存储数据并不能进行数据查询。

对 entry 模型定义一个查询索引,以实现通过查询标题来确定相关的 URL。为此,需要将 url 字段定义为无索引。 ``` class EntryIndex(FTS5Model): title = SearchField() url = SearchField(unindexed=True) class Meta: database = db options = {'tokenize': 'porter', 'prefix': '2,3'}

EntryIndex.create_table() ```

对于 fts5 扩展,该可选字典提供了附加元数据进行标记字段,以及通过前缀的长度存储快速前缀查询。利用 SQL 创建表的语句如下: CREATE VIRTUAL TABLE "entryindex" USING fts5 ( "title" , "url" UNINDEXED, prefix=2,3, tokenize=porter)

为了填充索引,将使用一对 JSON 函数从 Entry 模型中复制数据: title = fn.json_extract(Entry.data, '$.title').alias('title') url = fn.json_extract(Entry.data, '$.url').alias('url') query = Entry.select(title, url).dicts() with db.atomic(): for entry in query: EntryIndex.create(**entry)

索引填充后,进行一些查询: ```

query = EntryIndex.search('sqlite').limit(3) for result in query: ... print result.title

Extending SQLite with Python Building the SQLite FTS5 Search Extension My List of Python and SQLite Resources 实现上述查询的 SQL 语句为: SELECT "t1"."title", "t1"."url" FROM "entryindex" AS t1 WHERE ("entryindex" MATCH 'sqlite') ORDER BY rank ```

同样可对查询后的结果进行检索: ```

query = EntryIndex.search('sqlite AND python', with_score=True) for result in query: ... print round(result.score, 3), result.title

-1.259 Extending SQLite with Python -1.059 My List of Python and SQLite Resources -0.838 Querying Tree Structures in SQLite using Python and the Transitive Closure Extension ```

这些结果是非常准确,用于上述查询的 SQL 语句如下:

SELECT "t1"."title", "t1"."url", rank AS score FROM "entryindex" AS t1 WHERE ("entryindex" MATCH 'sqlite AND python') ORDER BY rank

本文中只是简要介绍了 fts5 扩展的简单功能,如果使用者查询该文档,将会发现其更多强大的功能。以下是一些例子:

  • 多列索引,在排序时分配不同的权重
  • 前缀查询、引述语、相邻的行的关键词
  • 上述查询类型与布尔型运算符结合
  • unicode61默认编码转化器、porter分解器禁止使用
  • 用于定义排序功能和断词的新的 C API。
  • 词汇表,用于查询词的数量和检查索引

感谢阅读

在 SQLite 添加 JSON 扩展对该项目和用户来说都是一件好事。Postgresql 和 MySQL 都已支持 JSON 数据类型,很高兴能 SQLite 跟随他们的脚步。但并不是任何条件下均需要是 JSON 数据格式,例如某些情况下需要用到专用的嵌入式文件存储库 UnQLite 。

json1.c 文件同样值得注意。Dr. Hipp 提到:json1.c 现在只是第一步,未来还有更多的发展空间。因此,无论当前版本存在任何问题,我始终坚信将来发布的版本中性能和 APIS 两个方面都会有很大的改善。还有一点,我相信他会考虑使用更高效的二进制格式。

很高兴看到 SQLite 在全文查询扩展模块上不断地自我完善和提高。为用户提供一个内置算法和一个用户可自行添加所需内容的 API。

原文地址:http://charlesleifer.com/blog/using-the-sqlite-json1-and-fts5-extensions-with-python/

OneAPM 能够帮你查看 Python 应用程序的方方面面,不仅能够监控终端的用户体验,还能监控服务器监性能,同时还支持追踪数据库、第三方 API 和 Web 服务器的各种问题。想阅读更多技术文章,请访问 OneAPM 官方技术博客 。

本文转自 OneAPM 官方博客

正文到此结束
Loading...