SQLite 扩展应用
SQLite 根据作者的说法，它不是要跟 PostgreSQL/Oracle/MSSQL/MySQL 比，而是要取代 fopen()，让应用开发者不用自己考虑文件格式的解析和序列化。我们来看看 SQLite 中比较少注意到的功能。
PRAGMA
- PRAGMA journal_mode=WAL。本地数据库可以用 WAL 模式加快读写速度，减少多进程冲突。
- PRAGMA cache_size=-kibibytes，加大你的缓存。
- PRAGMA application_id 和 PRAGMA user_version，让程序给自己的数据库标上识别码和版本号。
权限机制
SQLite 有一个很简单的权限系统，能对 SQL 虚拟机的操作指令设置回调 [1]，来判断是否允许操作。拿 Python 举例 [2]，例如我们要仅允许只读操作。
import sqlite3
SQLITE_FUNCTION = 31
def sql_auth(sqltype, arg1, arg2, dbname, source):
if sqltype in (sqlite3.SQLITE_READ, sqlite3.SQLITE_SELECT, SQLITE_FUNCTION):
return sqlite3.SQLITE_OK
else:
return sqlite3.SQLITE_DENY
conn = sqlite3.connect('some.db')
conn.set_authorizer(sql_auth)
cur = conn.cursor()
# ...
这里就只允许 READ、SELECT 和 FUNCTION 操作。Python 3.7 及以上版本的标准库才包含了 SQLITE_FUNCTION 等新定义的常数，所以先手动定义。[3] 具体操作代码参见 SQLite 文档。
| [1] | https://www.sqlite.org/c3ref/set_authorizer.html |
| [2] | https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.set_authorizer |
| [3] | https://bugs.python.org/issue28985 |
自定义函数、集合函数、排序规则
这个功能在 SQLite 中可能更有用一点。比如你可以把三角函数、统计函数等引入 SQLite，甚至加入版本比较。
例如：
import math
import sqlite3
import statistics
from distutils.version import LooseVersion
class MedianAgg:
def __init__(self):
self.data = []
def step(self, value):
self.data.append(value)
def finalize(self):
return statistics.median(self.data)
cmp = lambda a, b: ((a > b) - (a < b))
vercomp = lambda a, b: cmp(LooseVersion(a), LooseVersion(b))
conn = sqlite3.connect(":memory:")
conn.create_function("sin", 1, math.sin)
conn.create_function("pi", 0, lambda: math.pi)
conn.create_aggregate("median", 1, MedianAgg)
conn.create_collation("vercomp", vercomp)
cur = conn.cursor()
print(cur.execute("SELECT sin(pi()/2)").fetchone()[0])
# 1.0
cur.execute("CREATE TABLE test(i INTEGER PRIMARY KEY, ver TEXT)")
cur.execute("INSERT INTO test VALUES (1, '1.1.3')")
cur.execute("INSERT INTO test VALUES (2, '1.10.1')")
cur.execute("INSERT INTO test VALUES (3, '10.1')")
cur.execute("INSERT INTO test VALUES (5, '2.4')")
print(cur.execute("SELECT median(i) FROM test").fetchone()[0])
# 2.5
cur.execute("SELECT ver FROM test ORDER BY ver COLLATE vercomp")
print(cur.fetchall())
# [('1.1.3',), ('1.10.1',), ('2.4',), ('10.1',)]
扩展模块
扩展模块能把自定义函数、虚表之类的实现封装在一个动态库（.so/.dll）中，灵活地扩展 SQLite 的功能。除了有加载 CSV、Zip 文件的扩展之外，还有像 SpatiaLite 这种库。它实现了一套地理信息处理工具，能处理坐标系的映射、点线面之间的运算等，使 SQLite 数据库可以作为地理信息交换的一种格式。
其他
- 使劲加索引，索引要尽量能唯一索引到数据。性能问题通常都是索引不足引起的。PRIMARY KEY 和 UNIQUE 约束会自动加唯一索引。SQLite 号称能储存 2T 数据，要处理大量数据还是得加索引。
- 大事务操作，例如 VACUUM，需要很大的临时空间。这个临时空间通常是系统临时空间，例如 /tmp，或 TMPDIR 环境变量指定的。临时空间不足可能会导致即使数据库所在的文件系统还有空间却显示磁盘已满。
- 用 sqlite3 命令行的 .backup 在线备份数据库，不要直接复制。直接复制也要把 -journal、-wal 和 -shm 带上。
- SQLite 不检查数据类型，一列可以储存任意数据类型。
- INTEGER PRIMARY KEY 就是 ROWID。尽量不要使用 AUTOINCREMENT 关键字，会变慢。AUTOINCREMENT 是用于防止删除的行号被重用。
- 除了 INTEGER PRIMARY KEY 和 WITHOUT ROWID 表，其他主键都可以为 NULL。
- SQLite 还有 json 支持、fts3/4/5 全文搜索模块。
总结
SQLite 是用于客户端的数据库系统，可以作为一种程序自定义的数据交换格式。通过加入扩展函数和模块，可以用 SQLite 完成更复杂的数据处理，用 SQL 的高度抽象来减少程序代码。