使用 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 使用 tcl 和 awk 進行原始碼融合，因此在開始前，需要安裝下列工具：

• 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 支援大量的編譯配置選項，除了 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 我不確定能否成功。

建立 pysqlite

大多數 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);')
<pysqlite2.dbapi2.Cursor object at 0x7ff7d0a2dc60>
>>> 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);')
<apsw.Cursor at 0x7fcf6b17fa80>
>>> 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_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 的功能

首先介紹下 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 諮詢。

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 官方部落格