libSQL 附带的 SQLite 杂项扩展完全指南：表值函数、虚拟表、自定义 VFS 与内置 SQL 函数

libSQL 附带的 SQLite 杂项扩展完全指南表值函数、虚拟表、自定义 VFS 与内置 SQL 函数【免费下载链接】libsqllibSQL is a fork of SQLite that is both Open Source, and Open Contributions.项目地址: https://gitcode.com/GitHub_Trending/li/libsql本指南围绕 libsql-sqlite3/ext/misc/README.md 展开系统讲解 libSQLSQLite 的开源分支ext/misc目录下数十个小型可加载扩展的定位、用法与实现原理。读完本文你将掌握 carray、generate_series、CSV、ZIP、unionvtab/swarmvtab 等虚拟表扩展的建表与查询方式理解 memvfs 纯内存 VFS 与 dbdump 转储库的工作原理并能以 rot13、series 为模板自己编写自定义 SQL 函数与虚拟表。一、ext/misc 是什么一个小型可加载扩展的百宝箱在 libSQL 仓库中libsql-sqlite3/ext/misc目录收集了一批单文件实现的轻量扩展每个扩展除 dbdump.c 外都是一份独立的 C 源文件可通过 SQLite 的可加载扩展机制.load指令或sqlite3_load_extension()在运行时按需挂载到任意数据库连接上。正如 README 开篇所述该目录的定位是a collection of smaller loadable extensions——它刻意保持每个扩展只聚焦一个功能全部实现都写在单个.c文件里源文件头部注释即是最权威的使用手册。扩展按类型大致可分为四类类别代表文件核心能力表值函数table-valued functioncarray.c、series.c把一段内存数组、一组等差数列当成 SQL 表查询虚拟表virtual tablecsv.c、zipfile.c、unionvtab.c把 CSV 文件、ZIP 归档、多个数据库中的同构表暴露为 SQL 表自定义 SQL 函数rot13.c、shathree.c、sha1.c新增 rot13()、sha3() 等标量/聚合/查询哈希函数基础设施层memvfs.c、dbdump.c内存 VFS 实现、近似.dump的数据库转储库编译与加载方式README 指向的是 SQLite 官方的可加载扩展编译说明核心流程在 libSQL 仓库中同样适用。以 carray 为例Linux 下的标准做法是# 在 libsql-sqlite3/ext/misc 目录下用系统 sqlite3 开发头编译共享库 gcc -g -fPIC -shared carray.c -o carray.so随后在sqlite3命令行 shell 中加载并使用.load ./carray SELECT * FROM carray(...);每个扩展的初始化入口函数名由源文件名去掉数字派生而来这是 shathree.c 特意命名为shathree而非sha3的原因详见下文例如 carray.c#L544 中的sqlite3_carray_init、csv.c#L956 中的sqlite3_csv_init。如果库的编译选项禁用了虚拟表SQLITE_OMIT_VIRTUALTABLE对应的虚拟表扩展源码会整体跳过编译如 carray.c 第 87 行、csv.c 第 51 行所示。二、表值函数carray 与 generate_series2.1 carray把 C 数组暴露成 SQL 表carray.c 是官方推荐的自定义**表值函数table-valued function**入门范例。它的用法是SELECT * FROM carray($ptr, 5);这条查询把地址$ptr处的 C 数组当作一张 5 行的表返回。$ptr必须由宿主程序通过sqlite3_bind_pointer()接口以指针类型carray绑定例如static int aX[] { 53, 9, 17, 2231, 4, 99 }; int i sqlite3_bind_parameter_index(pStmt, $ptr); sqlite3_bind_pointer(pStmt, i, aX, carray, 0);可选的第三参数指定数组元素类型合法取值与默认值见 carray.c#L29-L35第三参数含义int32默认32 位有符号整型数组int6464 位有符号整型数组double双精度浮点数组char*字符串指针数组struct iovec内存块数组BLOB其实现原理是典型的伪装成函数的虚拟表carray 内部声明的表结构为CREATE TABLE carray( value, pointer HIDDEN, count HIDDEN, ctype TEXT HIDDEN );见 carray.c#L42-L47。三个隐藏列pointer、count、ctype承载函数参数可见列value逐元素产出数组内容当pointer/count未受约束时表为空。除了sqlite3_carray_init注册的carray模块该文件还顺带注册了一个inttoptr(integer)函数用于在 SQL 层把整数转换为指针见 carray.c#L552-L555。配套的头文件 carray.h 定义了CARRAY_INT32等标志位常量供宿主程序绑定指针时描述数据类型。2.2 generate_series等差数列生成器series.c 实现了与 PostgreSQL 同名的generate_series(start, stop, step)表值函数参数为 64 位有符号整数-- 0 到 100步长 5 SELECT * FROM generate_series(0,100,5); -- 0 到 100步长 1 SELECT * FROM generate_series(0,100); -- 20 到 29配合 LIMIT SELECT * FROM generate_series(20) LIMIT 10; -- 0、-5、-10 ... -100负步长 SELECT * FROM generate_series(0,-100,-5); -- 空序列 SELECT * FROM generate_series(0,-1);参数默认值规则见 series.c#L23-L26start必填stop缺省为 4294967295即(132)-1step缺省为 1且 0 被视为 1。序列第 n 个值满足V[n] start n*step且sgn(V[n]-stop)*sgn(step) 0。与 carray 相同函数参数在内部被翻译为对隐藏列的等值约束SELECT * FROM generate_series(0,100,5); -- 等价于 SELECT * FROM generate_series WHERE start0 AND stop100 AND step5;一个值得注意的工程细节generate_series 的xCreate方法为 NULL意味着它不能用CREATE VIRTUAL TABLE ... USING generate_series创建而是一张始终存在的内置表见 series.c#L79-L83。2024 年 8 月的更新series.c#L94-L111还让xBestIndex支持针对value列的范围约束使得WHERE value BETWEEN $SB AND $EB可以等效替换start/stop约束帮助查询规划器缩小生成范围。从源码结构看xBestIndex在 start/stop 都已知时返回极小代价、缺失任一约束时返回极大代价以此引导连接顺序。该文件也因此被 README 推荐为自定义虚拟表实现的最佳模板。三、面向数据文件的虚拟表CSV 与 ZIP3.1 csv把 CSV 文件变成 SQL 表csv.c 实现了一个只读 CSV 虚拟表。最基本用法.load ./csv CREATE VIRTUAL TABLE temp.csv USING csv(filenameFILENAME); SELECT * FROM csv;默认情况下列名依次为c1、c2、c3……也可以用schema参数自定义表结构或用data参数直接内嵌 CSV 文本而不读文件见 csv.c#L16-L37CREATE VIRTUAL TABLE temp.csv2 USING csv( filename ../http.log, schema CREATE TABLE x(date,ipaddr,url,referrer,userAgent) );参数行为规则提供columnsN时按 N 列解析columns与schema都缺省时列数与列名由 CSV 首行推断。实现层面模块内部维护一个CsvReader上下文csv.c#L72-L86以 1024 字节缓冲CSV_INBUFSZ逐块读取文件支持字段跨行、带引号与转义的标准 CSV 语义错误信息上限为 200 字节CSV_MXERR。初始化函数在注册csv模块的同时还会注册一个csv_wr模块见 csv.c#L956-L967后者是用于测试的假写模块。若以-DSQLITE_TEST编译还会启用一组仅供虚拟表测试的调试特性。3.2 zipfile直接读写 ZIP 归档zipfile.c 提供了对 ZIP 归档文件的读写虚拟表。列结构见 zipfile.c#L105列含义name归档内文件名主键modePOSIX 文件权限位mtime修改时间自 1970 起的秒数sz解压后大小data文件内容method压缩方法0存储、8deflate典型查询SELECT name, sz, datetime(mtime,unixepoch) FROM zipfile($filename);读取时依赖 zlib 完成解压因此编译需要链接-lz。源码头注释明确列出了当前局限zipfile.c#L20-L26不支持加密、不支持跨多文件的归档、不支持 zip64 扩展、只支持 zlib 的 inflate/deflate 压缩方法。写入时支持把行插入/更新/删除映射为对归档内文件的增删改。四、跨库聚合虚拟表unionvtab 与 swarmvtabunionvtab.c 同时实现两个只读虚拟表解决一张大表被拆散存放在多个数据库文件的场景。前置条件见 unionvtab.c#L16-L27所有源表必须是 rowid 表不能是虚拟表、WITHOUT ROWID 表或视图列定义必须完全一致名称、顺序、类型不允许用户自定义_rowid_列各表持有的 rowid 区间互不重叠。4.1 unionvtab多库同构表的逻辑合并CREATE VIRTUAL TABLE name USING unionvtab(sql-statement);创建或打开时实现会执行该 SQL 语句语句每返回一行即对应一张源表四列依次为所在数据库名main、temp或某个 ATTACH 库名NULL 表示按常规方式搜索所有库表名该表可容纳的最小 rowid整数该表可容纳的最大 rowid整数。4.2 swarmvtab按需开关数据库文件swarmvtab 更进一步源表可以位于磁盘上任意数据库文件中由实现自动打开/关闭文件。旧语法CREATE VIRTUAL TABLE name USING swarmvtab(sql-statement, callback);此时 SQL 语句第一列须返回可打开数据库文件的路径或 URIcallback可选是在文件缺失时被调用的应用自定义 SQL 函数。新语法支持更丰富的选项见 unionvtab.c#L75-L135CREATE VIRTUAL TABLE name USING swarmvtab( sql-statement [, options] ); -- 合法选项 -- missingudf-function-name 文件缺失时调用的 UDF -- opencloseudf-function-name 打开前/关闭后被调用的 UDF -- maxopeninteger 同时保持打开的文件数上限默认 9 -- :sql-parametertext-value 绑定到 SQL 语句中的同名参数SQL 语句在旧语法的 4 列之外还可返回第 5 列context其文本不被 swarmvtab 使用仅作为额外参数透传给missing/openclose两个 UDF。当一个数据库需要使用时调用序列为见 unionvtab.c#L113-L121SELECT openclose-udf(db filename, context, 0); -- 打开前 SELECT missing-udf(db filename, context); -- 仅当文件不在磁盘时 ... swarmvtab 使用数据库 ... SELECT openclose-udf(db filename, context, 1); -- 关闭后:参数选项允许把文本值绑定进 SQL 语句例如CREATE VIRTUAL TABLE swarm USING swarmvtab( SELECT :path || localfile, tbl, min, max FROM swarmdir, :path/home/user/databases/, missingmissing_func );五、自定义 SQL 函数rot13 与 sha35.1 rot13写新 SQL 函数的最小模板rot13.c 全文件仅 115 行是 README 钦定的自定义 SQL 函数模板。它同时实现两样东西标量函数rot13(X)把 ASCII 字母按字母表旋转 13 位rot13(rot13(X)) X非 ASCII 字符原样保留实现见 rot13.c#L24-L33排序规则COLLATE rot13使xy COLLATE rot13等价于rot13(x)rot13(y) COLLATE binary见 rot13.c#L81。标量函数体遵循 SQLite 扩展的标准范式通过sqlite3_value_text()读取入参、逐字节变换、sqlite3_result_text()返回结果入参为 NULL 时直接返回短文本走栈上 100 字节缓冲区、长文本走sqlite3_malloc64()见 rot13.c#L42-L70。这套context argc argv的函数签名就是编写一切自定义 SQL 函数的骨架。5.2 shathreesha3() / sha3_agg() / sha3_query()shathree.c 按 NIST FIPS 202 标准实现三个函数见 shathree.c#L15-L38sha3(X[, SIZE])计算 X 的 SHA3 哈希。文本按 UTF-8 编码、BLOB 按原始字节、数值先转 UTF-8 文本再哈希X 为 NULL 返回 NULLsha3_agg(Y[, SIZE])聚合函数对 Y 的所有取值含 NULL 行求哈希建议配合ORDER BY保证输入顺序sha3_query(Z[, SIZE])执行 Z 这条 SQL 语句并对查询结果求哈希。SIZE可选取值限定为 224 / 256 / 384 / 512缺省 256。为避免类型歧义sha3_agg在哈希前会给每个值加上类型前缀NULL 记N、整数记I 8 字节大端、实数记F 8 字节大端、文本记Tnnn: 内容、BLOB 记Bnnn: 内容见 shathree.c#L43-L90。sha3_query除按行插入前缀R外编码方式与sha3_agg一致。因此下列断言全部为真源码注释中的自检用例SELECT sha3(1) sha3(1); SELECT sha3(hello) sha3(x68656c6c6f); WITH a(x) AS (VALUES(xyzzy)) SELECT sha3_agg(x) sha3(T5:xyzzy) FROM a; WITH a(x) AS (VALUES(x010203)) SELECT sha3_agg(x) sha3(x42333a010203) FROM a;若想排除 NULL 行可叠加FILTER(WHERE ...)SELECT sha3_agg(x ORDER BY rowid) FILTER(WHERE x NOT NULL) FROM t1;关于文件名shathree.c而非sha3.c是因为 SQLite 根据源文件名去掉数字推导默认入口点——若命名为 sha3.c入口点将与早先的 sha1.c 冲突见 README 与 shathree.c 头注释。实现层面还包含大小端探测逻辑SHA3_BYTEORDER宏见 shathree.c#L124-L130优先用预处理器在编译期判定字节序必要时在运行时判定。六、memvfs整个数据库驻留于一块内存的 VFSmemvfs.c 实现一个自定义 VFS把整个数据库文件放进一块应用提供的内存缓冲区README 将其作为实现一个简单自定义 VFS的范例。注册后通过 URI 打开见 memvfs.c#L20-L39sqlite3_open_v2(file:/whatever?ptr0xf05538sz14336maxsz65536, db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_URI, memvfs);URI 查询参数语义参数必填含义ptr是承载数据库的内存缓冲区地址sz是数据库文件的当前大小maxsz否默认等于sz缓冲区可容纳的最大尺寸freeonclose否为真时连接关闭会对ptr调用sqlite3_free()参数值支持十进制或十六进制URI 中的文件名本身被忽略。由于没有地方存放回滚日志或 WAL 日志数据库必须使用journal_modeMEMORY或journal_modeNONEmemvfs.c#L16-L17。从源码结构看该 VFS 的sqlite3_vfs注册名为memvfsmemvfs.c#L111并实现了xOpen、xRead、xWrite、xTruncate、xShmMap、xFetch/xUnfetch等完整的 VFS 方法集同时把随机数、休眠等能力委托给其下层 VFSORIGVFS宏见 memvfs.c#L56。七、dbdump近似.dump的转储库dbdump.c 与前面所有扩展不同它不是可加载扩展而是一个 C 库提供近似 SQLite shell 中.dump命令的转储能力。导出的核心函数签名见 dbdump.c#L19-L24int sqlite3_db_dump( sqlite3 *db, const char *zSchema, /* main、temp 或 ATTACH 库名 */ const char *zTable, /* NULL 表示转储全部表 */ void (*xCallback)(void*, const char*), void *pArg );输出为可精确重建数据库的 UTF-8 文本 SQLROWID 保留逐段回调给xCallback签名设计为与fputs()兼容。zSchema缺省通常为mainzTable为 NULL 时转储全部表。返回SQLITE_OK或错误码。若以-DDBDUMP_STANDALONE编译会额外生成一个main()使其成为命令行工具参数依次为数据库文件名、schema、可选的表名见 dbdump.c#L41-L45。实现内部以DText动态字符串累积 SQL 片段并处理标识符引用appendText见 dbdump.c#L94并支持 writable_schema 模式。八、README 未展开但同目录可用的扩展一览除了上述 README 点名的扩展同目录还包含大量功能完整的单文件扩展全部可在 libsql-sqlite3/ext/misc 中直接查阅与编译使用例如文本与编码base64.c、base85.c、basexx.c、totype.c、uint.c、percentile.c、normalize.c、randomjson.c、uuid.c文件与网络fileio.c读写文件/目录、sqlar.cSQLite 归档格式、appendvfs.c把 DB 追加在可执行文件尾部、blobio.creadblob/writeblob、compress.ccompress/uncompress基于 zlib搜索与模糊匹配spellfix.c拼写纠正、fuzzer.c模糊查找、amatch.c近似字符串匹配注册approximate_match模块见 amatch.c#L1499、regexp.c、closure.c传递闭包、completion.cSQL 补全调试与审计vfslog.c、vfsstat.c、vfstrace.c、vtablog.c、btreeinfo.csqlite_btreeinfo表、memstat.c、memtrace.c、pcachetrace.c、showauth.c、explain.cexplain虚拟表、stmt.c、stmtrand.c、mmapwarm.c、noop.c、eval.c、remember.c、nextchar.c、wholenumber.c、carray.h配套的vtshim.c、templatevtab.c、qpvtab.c、ieee754.c、decimal.c、prefixes.c、anycollseq.c、zorder.c、cksumvfs.c带校验和的自定义 VFS、fossildelta.c、scrub.c。这些扩展的头注释同样包含完整用法说明是研究 SQLite 扩展 APIsqlite3_create_function、sqlite3_create_module、sqlite3_declare_vtab的现成素材。九、关于 json1已内置进 amalgamationREADME 特别指出json1.c提供处理 JSON 的各类 SQL 函数与表值函数但它已经内置于 SQLite amalgamation无需也无法作为独立扩展加载。这一说法在 libSQL 仓库中得到印证仓库中对应实现位于 libsql-sqlite3/src/json.c并配套了 json-enhancements.md 与 jsonb.md 两份增强文档。也就是说使用 libSQL 时json()、json_extract()、json_each()等函数开箱即用不依赖 ext/misc 目录。十、总结与选型建议libsql-sqlite3/ext/misc的价值在于把 SQLite 扩展机制的关键范例浓缩在几十个单文件源码中可直接对照学习也可直接编译复用想让外部数据参与 SQL 查询CSV 用csv.cZIP 用zipfile.c跨库大表用unionvtab.c/swarmvtab.c想快速生成数据数值序列用series.c内存数组用carray.c想扩展 SQL 语法能力自定义函数参考rot13.c哈希与完整性校验参考shathree.c想改造存储层内存数据库参考memvfs.c转储参考dbdump.c。每个文件都可在 libsql-sqlite3/ext/misc 中直接阅读源码头注释获取权威用法说明README 本身即是对这些扩展的导航索引两者结合使用效果最佳。【免费下载链接】libsqllibSQL is a fork of SQLite that is both Open Source, and Open Contributions.项目地址: https://gitcode.com/GitHub_Trending/li/libsql创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考