January 30, 2023
By: Kevin'

SQLite 扩展

  1. 应用自定义函数 (Application-Defined SQL Functions)
  2. 库扩展 (Library Extensions)

SQLite 以其轻量, 便捷而著称, 虽然它本身不像其他大型数据库那样拥有丰富的功能, 但SQLite 强大的可扩展性可按需要增加的功能.

例如, SQLite 本身没有 UUID 函数, 但如果需要, 完全可以通过多种方式来实现. 可以自己编写代码, 也可以借助开源库, 添加 UUID 功能.

如果熟悉 SQL, 可能会想到使用 SQL 的数据定义语言 (DDL) 来创建函数, 就像这样:

-- 错误的尝试! SQLite 不支持这种方式创建函数
CREATE FUNCTION uuid()
RETURNS TEXT
AS
BEGIN
    -- ... UUID 生成逻辑 ...
END;

但遗憾的是, 这种方法在 SQLite 中行不通. SQLite 不支持 使用 CREATE FUNCTION 语句直接创建函数.

虽然不能直接创建 SQL 函数, SQLite支持两种扩展方法: 应用自定义函数 (Application-Defined SQL Functions) 和 库扩展 (Library Extensions).

应用自定义函数 (Application-Defined SQL Functions)

什么是应用自定义函数?

简单来说, 就是用编程语言(比如 JavaScript, Python 等)编写一个函数, 然后将这个函数注册到 SQLite. 注册后就可以像使用内置函数一样在 SQL 语句中使用自定义的函数了!

实现步骤:

无论哪种语言, 添加应用自定义函数的总体流程都非常相似:

  1. 编写函数: 使用熟悉的编程语言编写一个函数, 实现想要的功能(例如, 生成 UUID).
  2. 注册函数: 使用 SQLite 提供的 API, 将编写的函数注册到 SQLite 数据库连接中, 并为函数指定一个在 SQL 中使用的名称.
  3. 使用函数: 在 SQL 语句中, 像调用普通 SQL 函数一样调用注册的函数.

示例 1: JavaScript (使用 better-sqlite3 库)

首先, 安装 better-sqlite3 库:

npm install better-sqlite3

然后, 在JavaScript 代码中, 可以这样实现:

const Database = require('better-sqlite3');
const crypto = require('crypto'); // 引入 Node.js 的 crypto 库用于生成 UUID

// 打开 SQLite 数据库 (如果不存在则创建)
const db = new Database('my_database.db');

// 注册应用自定义函数 "uuid"
db.function('uuid', () => crypto.randomUUID());

// 验证函数是否工作
const result = db.prepare("SELECT uuid()").pluck().get();
console.log("生成的 UUID:", result);

db.close();

代码解释:

  • 引入了 better-sqlite3 库和 Node.js 的 crypto 库.
  • 使用 new Database('my_database.db') 打开或创建了一个名为 my_database.db 的 SQLite 数据库.
  • db.function('uuid', ...) 注册了一个名为 uuid 的应用自定义函数. 第二个参数是一个匿名函数, 该函数使用 crypto.randomUUID() 生成 UUID 并返回.
  • 执行 SQL 查询 SELECT uuid() 来调用我们自定义的函数, 并将结果打印到控制台.

运行这段 JavaScript 代码, 成功生成并输出了一个 UUID, 证明自定义函数已经成功注册并可以在 SQLite 中使用了!

示例 2: Python (使用 sqlite3 库)

Python 的 sqlite3 库也提供了非常方便的方式来注册应用自定义函数.

import sqlite3
import uuid

# 连接到 SQLite 数据库 (如果不存在则创建)
conn = sqlite3.connect('my_database.db')

# 注册应用自定义函数 "uuid"
conn.create_function("uuid", 0, lambda: str(uuid.uuid4()))

# 创建游标对象
cursor = conn.cursor()

# 执行 SQL 查询, 调用自定义函数
cursor.execute("SELECT uuid()")

# 获取查询结果
result = cursor.fetchone()[0]
print("生成的 UUID:", result)

# 关闭连接
conn.close()

代码解释:

  • pythonh导入 sqlite3 和 uuid 模块.
  • sqlite3.connect('my_database.db') 连接到 SQLite 数据库.
  • conn.create_function("uuid", 0, lambda: str(uuid.uuid4())) 注册了一个名为 uuid 的函数.
    • "uuid": 函数在 SQL 中使用的名称.
    • 0: 函数的参数数量 (arity). UUID 函数不需要参数, 所以设置为 0.
    • lambda: str(uuid.uuid4()): 一个 lambda 匿名函数, 用于生成 UUID 并将其转换为字符串返回. uuid.uuid4() 生成 UUID 对象, str() 将其转换为字符串, 因为 SQLite 默认处理字符串类型.
  • 创建游标并执行 SELECT uuid() 查询, 获取并打印结果.

运行 Python 代码, 同样可以看到成功生成并输出了 UUID, 证明 Python 中应用自定义函数也成功工作了!

库扩展 (Library Extensions)

什么是库扩展?

库扩展是一种更高级的 SQLite 扩展方式. 可以创建包含自定义功能的共享库 (shared library 或 DLL), 然后在 SQLite 中加载和使用这些扩展库. 扩展库不仅可以添加函数, 还可以实现更复杂的功能, 例如新的数据类型, 排序规则甚至虚拟表.

使用现有扩展: 以 sqlean/sqlite-uuidv4 为例

直接创建扩展库可能比较复杂. 更常见的场景是使用现有的开源扩展.

以 sqlean/sqlite-uuidv4 这个 UUID 扩展库为例, 演示如何使用它为 SQLite 添加 UUID 功能.

步骤:

  1. 下载扩展库: 访问 sqlean/sqlite-uuidv4 的 GitHub Release 页面, 下载适合平台的预编译共享库文件 (例如, macOS arm 平台的 .dylib 文件).

  2. 解压并放置: 将下载的压缩包解压, 并将解压得到的共享库文件 (例如 uuidv4.dylib) 复制到项目目录中.

  3. 移除隔离 (macOS): 如果在 macOS 上使用, 从互联网下载的库文件会被隔离. 需要使用 xattr -d com.apple.quarantine uuidv4.dylib 命令移除隔离属性 (在终端中执行, uuidv4.dylib 是库文件名).

  4. 在 SQLite 中加载扩展: 打开 SQLite 命令行工具, 连接到数据库. 使用 .load 命令加载扩展库, 指定库文件的路径 (不需要文件扩展名):

    sqlite3 my_database.db
.load ./uuidv4

  5. 使用扩展功能: 加载成功后, 就可以使用扩展库提供的功能了. sqlean/sqlite-uuidv4 提供了 uuid() 和 uuid_generate_v4() 等函数来生成 UUID. 例如:

    SELECT uuid(); -- 使用 uuid() 函数
SELECT uuid_generate_v4(); -- 使用 uuid_generate_v4() 函数

示例演示 (SQLite 命令行工具):

# 打开 sqlite3 命令行工具并连接到数据库
sqlite3 my_database.db

SQLite version 3.40.1 ...

sqlite> -- 加载 uuidv4 扩展库 (假设 uuidv4.dylib 在当前目录下)
sqlite> .load ./uuidv4

sqlite> -- 尝试使用 uuid4() 函数 (加载扩展前不存在)
sqlite> SELECT uuid4();
Error: no such function: uuid4

sqlite> -- 加载扩展后再次尝试 uuid4() 函数 (注意 sqlean/sqlite-uuidv4 提供的函数名为 uuid_generate_v4)
sqlite> SELECT uuid_generate_v4();
f47ac10b-58cc-4372-a567-0e02b2c3d479

sqlite> -- uuid() 函数也可用
sqlite> SELECT uuid();
9b1deb4d-3b7d-4bad-9bdd-2b0d7b3dcb6d

sqlite> .quit

代码解释:

  • .load ./uuidv4 命令指示 SQLite 加载当前目录下的 uuidv4 扩展库.
  • 加载扩展后, 就可以在 SQL 语句中使用 uuid_generate_v4() 和 uuid() 函数来生成 UUID 了.

库扩展的强大之处

库扩展的功能远不止添加一些函数那么简单. 例如, SQLite 官方提供的 Full-Text Search (FTS) 扩展 就实现了全文搜索功能, 它引入了虚拟表 (Virtual Table) 的概念, 并提供了一系列用于索引, 搜索和排序文本的功能, 极大地增强了 SQLite 的文本处理能力.

Tags: sqlite sql