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 使用 tcl
和 awk
进行源码融合,因此在开始前,需要安装下列工具:
该过程涉及几个步骤,这里尽量将步骤细化。首先需要为新库分配一个全新的目录,笔者把它放在 ~/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
目录中。
代码下载完成后,把它和 SQLite 源代码树放在同一目录下。SQLite 支持大量的 编译配置选项 ,除了 json1
和 fts5
,还有很多其他有效的选择。
编译遵循典型的 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 我不确定能否成功。
大多数 python 开发者对 pysqlite 一定不陌生,在 Python 标准库中 pysqlite
或多或少的和 sqlite3
模块相似。要建立和 libsqlite3 相对应的 pysqlite,唯一需要做的是修改 setup.cfg
文件使其指向刚才创建的 include
和 lib
目录。 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 的步骤几乎和建立 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` 安装文件。
json1
扩展中具有一些简洁特性,尤其是 json_tree
和 json_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_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_src
和 tags_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 咨询。
本小节中的代码均是之前 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
扩展的简单功能,如果使用者查询该文档,将会发现其更多强大的功能。以下是一些例子:
在 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 官方博客