feat(cpp): extend SQLite hybrid tables and automatic TsFile export Support inferred schemas from external TsFile tables, mutable hot rows, transactional sealing, and export with automatic sealing. Add table diagnostics, directory ownership checks, rollback and publication regressions, and Chinese and English user manuals.
diff --git a/cpp/src/sqlite/CMakeLists.txt b/cpp/src/sqlite/CMakeLists.txt index b8c6c16..2bec9d2 100644 --- a/cpp/src/sqlite/CMakeLists.txt +++ b/cpp/src/sqlite/CMakeLists.txt
@@ -19,6 +19,24 @@ find_package(SQLite3 3.31 REQUIRED) +# Apple's SDK SQLite header disables extension loading. Require headers from +# the same extension-capable SQLite installation used by the application. +include(CheckCXXSourceCompiles) +set(_tsfile_saved_required_includes "${CMAKE_REQUIRED_INCLUDES}") +set(CMAKE_REQUIRED_INCLUDES "${SQLite3_INCLUDE_DIRS}") +unset(TSFILE_SQLITE_CAN_LOAD_EXTENSIONS CACHE) +check_cxx_source_compiles(" +#include <sqlite3ext.h> +#ifdef SQLITE_OMIT_LOAD_EXTENSION +#error SQLite extension loading is disabled +#endif +int main() { return 0; } +" TSFILE_SQLITE_CAN_LOAD_EXTENSIONS) +set(CMAKE_REQUIRED_INCLUDES "${_tsfile_saved_required_includes}") +if (NOT TSFILE_SQLITE_CAN_LOAD_EXTENSIONS) + message(FATAL_ERROR "SQLite headers disable extension loading. Set SQLite3_INCLUDE_DIR and SQLite3_LIBRARY to an extension-capable SQLite installation (for example Homebrew SQLite on macOS).") +endif () + add_library(tsfile_sqlite MODULE tsfile_sqlite.cc) target_include_directories(tsfile_sqlite PRIVATE ${CMAKE_SOURCE_DIR}/src
diff --git a/cpp/src/sqlite/README.md b/cpp/src/sqlite/README.md index 81c043c..99e8335 100644 --- a/cpp/src/sqlite/README.md +++ b/cpp/src/sqlite/README.md
@@ -21,54 +21,77 @@ # SQLite + TsFile extension -`tsfile_sqlite` is an experimental SQLite loadable extension that keeps recent -rows in SQLite and seals older rows into immutable TsFile table-model segments. +`tsfile_sqlite` is an experimental SQLite extension for querying TsFile table-model +files and maintaining writable tables with mutable SQLite hot rows and immutable +TsFile history. Documentation: -- [User guide](USER_GUIDE.md): build, load, configure, query, seal, deploy, and - operate the extension. -- [Technical guide](TECHNICAL_GUIDE.md): architecture, virtual-table callbacks, - storage layout, query planning, and transaction/crash consistency. +- User manual: [English](USER_GUIDE_EN.md) | + [Chinese](USER_GUIDE.md). +- Technical report: [English](TECHNICAL_GUIDE_EN.md) | + [Chinese](TECHNICAL_GUIDE.md). -Build it with: +Build from the repository root: ```bash cmake -S cpp -B cpp/build/sqlite \ -DBUILD_SQLITE_EXTENSION=ON -DTSFILE_BUILD_SHARED=ON -DBUILD_TEST=ON -cmake --build cpp/build/sqlite --target tsfile_sqlite +cmake --build cpp/build/sqlite --target tsfile_sqlite TsFile_Sqlite_Test -j +ctest --test-dir cpp/build/sqlite/test -R '^TsFileSqliteTest$' --output-on-failure ``` -The extension is emitted next to `libtsfile` in the build `lib` directory. -Load it and create a hybrid table with: +Use SQLite 3.31 or newer with extension loading enabled. On macOS, select +extension-capable headers and libraries, for example by adding +`-DSQLite3_INCLUDE_DIR=/opt/homebrew/opt/sqlite/include` and +`-DSQLite3_LIBRARY=/opt/homebrew/opt/sqlite/lib/libsqlite3.dylib` for Homebrew on +Apple Silicon. Deploy the extension next to `libtsfile` from the same build. + +All three creation modes use `tsfile_hybrid`: ```sql -.load ./tsfile_sqlite +.load /absolute/path/to/tsfile_sqlite +-- Empty writable table: declare its schema and time unit. CREATE VIRTUAL TABLE sensor USING tsfile_hybrid( - directory='/absolute/path/to/segments', - timestamp_precision='ms', - column='time:TIMESTAMP:TIME', - column='device:STRING:TAG', - column='temperature:DOUBLE:FIELD' + time TIMESTAMP TIME, + device STRING TAG, + temperature DOUBLE FIELD, + directory='/absolute/path/to/sensor-segments', + timestamp_precision='ms' ); + +-- Existing history plus a writable hot area: infer the source schema. +CREATE VIRTUAL TABLE continued USING tsfile_hybrid( + file='/archive/history.tsfile', + source_table='sensor', + directory='/absolute/path/to/continued-segments' +); + +-- Existing history without a directory: query only. +CREATE VIRTUAL TABLE temp.history USING tsfile_hybrid( + file='/archive/history.tsfile', + source_table='sensor' +); + +INSERT INTO sensor VALUES (1700000000000, 'device-1', 21.5); +SELECT * FROM sensor ORDER BY time; +SELECT tsfile_seal('main.sensor', 1700000000001); +SELECT tsfile_export('main.sensor', '/export/sensor-001'); +SELECT * FROM tsfile_table_info('main.sensor'); +SELECT * FROM tsfile_verify('main.sensor'); ``` -Seal the half-open historical interval ending at `cutoff` with: +The writable external-file mode requires a known time unit; supply +`timestamp_precision='ms'`, `'us'`, or `'ns'` when the source has no precision +property. New rows must be strictly later than the selected source table's maximum +time. External files stay unchanged. Actual updates or deletes of cold rows fail +with `SQLITE_READONLY` and roll back the whole statement. -```sql -INSERT INTO sensor(_tsfile_command, _tsfile_cutoff) -VALUES ('seal', 1700000000000); -``` +Export automatically seals all current hot rows, commits that seal, then publishes +an independent snapshot as one standard TsFile (zero files for an empty table). +It must run outside an explicit transaction. An output failure after sealing keeps +the committed seal; the error reports the stage. -The generated `<table>_data`, `<table>_segments`, and `<table>_config` tables -are SQLite shadow tables. A seal is synchronous and participates in the -surrounding SQLite transaction. Data at or above the watermark remains mutable; -attempts to insert or modify older data return `SQLITE_CONSTRAINT`. - -The supported field types are `BOOLEAN`, `INT32`, `INT64`, `FLOAT`, `DOUBLE`, -`TEXT`, `STRING`, `BLOB`, `DATE`, and `TIMESTAMP`. The first column must be a -`TIMESTAMP:TIME`, TAG columns must be non-null `STRING`, and the TAG columns -together with time form the unique key. The directory is created on table -creation and must be dedicated to that logical table; dropping the virtual -table does not delete already exported segment files. +This version replaces the prototype's `column=` syntax, hidden management columns, +and shadow-table layout. It does not automatically migrate prototype databases.
diff --git a/cpp/src/sqlite/TECHNICAL_GUIDE.md b/cpp/src/sqlite/TECHNICAL_GUIDE.md index e746ca4..a867134 100644 --- a/cpp/src/sqlite/TECHNICAL_GUIDE.md +++ b/cpp/src/sqlite/TECHNICAL_GUIDE.md
@@ -19,425 +19,147 @@ --> -# tsfile_sqlite 技术手册 +# SQLite + TsFile 技术报告 -本文说明 SQLite + TsFile Hybrid MVP 的设计边界、内部数据结构、SQLite -virtual table 回调、冷热读写路径以及事务和崩溃一致性。 +本文说明当前实验实现；日常操作与排障见 [中文用户手册](USER_GUIDE.md)， +英文版本见 [Technical report](TECHNICAL_GUIDE_EN.md)。 -实现入口是 `tsfile_sqlite.cc`，对 SQLite 注册的模块名为 `tsfile_hybrid`。 +## 1. 实现结构 -## 1. 目标和非目标 +`tsfile_sqlite.cc` 实现统一虚拟表 `tsfile_hybrid`，负责参数、schema、冷热读取、 +热区写入、封存和事务回调。`tsfile_sqlite_management.inc` 位于同一匿名命名空间， +提供 seal/export 管理函数和状态、校验虚拟表。集成测试位于 +`../../test/sqlite/tsfile_sqlite_test.cc`，使用真实 SQLite 连接和 TsFile 读写器。 -### 1.1 目标 +连接维护按 SQLite schema 与逻辑表名索引的表对象。每张表保存列、模式、源文件映射、 +时间精度、watermark、待提交文件和 savepoint 标记。游标当前在内存中收集查询结果。 -- 让应用通过一张 SQLite 逻辑表查询热数据和 TsFile 历史数据； -- 让近期可变数据保留完整 SQLite 事务和 CRUD 语义； -- 通过显式时间水位把历史数据导出为标准、不可变 TsFile； -- 使文件生成、SQLite manifest 更新和热数据删除具备事务一致性； -- 复用 libtsfile 的 table model、Tablet writer 和 C++ ResultSet reader。 +显式定义加目录、外部文件加目录、外部文件不加目录三种建表方式，最终都形成同一套 +列结构，再通过 `sqlite3_declare_vtab` 声明给 SQLite。前两种可写，第三种只读。 +文件推断只处理明确指定的 source_table，并扫描其最大时间；空源表通过完整 schema +元数据识别。普通源文件的隐含时间列名为 time，扩展生成文件使用 Property +`tsfile_sqlite.time_column` 保存自定义时间列名。 -### 1.2 非目标 +重连从 SQLite 恢复已登记的结构，不因外部文件变化重新推断。源文件缺失时仍能查看 +结构和诊断，实际读取通过文件特征检查后才继续。 -- 不修改 SQLite pager、B-tree、WAL、VFS 或 SQL parser； -- 不让 TsFile 成为普通 SQLite database file； -- 不提供历史 correction/tombstone； -- 不实现自动 seal、compaction、分布式 manifest 或在线备份协议； -- 不在 MVP 中承诺跨数据源的物理输出顺序。 +## 2. SQLite 内部状态 -因此当前实现是 SQLite loadable extension，而不是 SQLite 内核 fork。若应用不 -希望执行 `.load`，未来可以把相同模块静态链接到定制 SQLite，并通过 -`sqlite3_auto_extension()` 注册；这不会改变本文的数据模型。 - -## 2. 总体架构 - -```text - SQLite connection - | - SQL / transaction / planner - | - tsfile_hybrid virtual table - / \ - SQLite shadow hot table TsFile segment files - CRUD, WAL, constraints immutable table model - \ / - merged virtual cursor -``` - -主要组件职责： - -| 组件 | 职责 | +| 对象 | 内容 | | --- | --- | -| SQLite | SQL 执行、事务、WAL/journal、锁、残余谓词、排序和聚合 | -| `tsfile_sqlite` | virtual table 适配、schema 校验、冷热路由、seal、manifest | -| `libtsfile` | TsFile table model 编码、文件 footer、查询和 ResultSet | +| `<table>_tsfile$hot` | 可写表的业务列及独立内部行号 | +| `<table>_tsfile$segments` | 文件路径、cutoff、行数、文件内部表名及内容指纹 | +| `<table>_tsfile$config` | 结构、模式、精度、目录、watermark、源文件及最大时间 | +| `<table>_tsfile$key` | 热数据的逻辑唯一键索引 | -扩展使用 `sqlite3ext.h` 和 `SQLITE_EXTENSION_INIT1/2` 获取宿主 SQLite API， -导出标准入口 `sqlite3_extension_init`。入口首次执行时初始化 libtsfile，然后 -通过 `sqlite3_create_module_v2()` 注册 `tsfile_hybrid`。 +只读表不创建 hot。内部对象与逻辑表处于相同 SQLite schema，名称全部转义；冲突时 +拒绝创建。schema 以带长度的列名、TsFile 类型和类别序列保存，不因 SQLite 类型映射 +丢失原类型信息。直接修改内部对象不属于受支持接口。 -## 3. 构建集成 +每个 TAG 在唯一索引中使用 `(tag IS NULL)` 和 `coalesce(tag,'')` 两个分量，最后 +追加 TIME。这使 NULL 键相互冲突，同时保留 NULL 与空字符串的区别。无 TAG 时只有 +TIME 键。源文件原有重复行保持原样，新数据必须晚于源表最大时间，不能覆盖历史。 -根 CMake 增加默认关闭的选项： +热区内部行号选择不与业务列冲突的 `tsfile$rowid` 名称，业务 rowid/oid/_rowid_ 不影响 +内部定位。冷行使用负的合成行号，仅用于操作内定位，不是稳定业务主键。 -```text -BUILD_SQLITE_EXTENSION=OFF -``` +## 3. 查询和写入 -启用时： +xBestIndex 提供整数时间范围、BINARY TAG 等值和列裁剪信息。xFilter 读取热区及登记 +冷文件，并使用每段保存的 source_table 映射；事务内新封存的段从本次临时路径读取。 +支持的时间和 TAG 条件下推给 TsFile，剩余谓词、关联、聚合、排序和分页由 SQLite +执行，结果顺序需要显式 ORDER BY。 -- 拒绝 Windows； -- 要求 `TSFILE_BUILD_SHARED=ON`； -- 通过 `find_package(SQLite3 3.31 REQUIRED)` 校验 SQLite； -- 构建 `MODULE` library `tsfile_sqlite`； -- 链接同一构建中的 `tsfile` target； -- Linux 使用 `$ORIGIN`，macOS 使用 `@loader_path` 查找同目录 `libtsfile`； -- 不给模块添加 Unix 默认的 `lib` 前缀。 +xUpdate 检查类型、TIME 非空、唯一键和 watermark，只写入热区。实际命中冷行的 +UPDATE/DELETE，以及只读表实际写入，返回非约束类错误 SQLITE_READONLY，保证 +IGNORE/FAIL 也不能留下同一语句的部分热区修改。SQLite 不调用零行 DML 的 xUpdate， +所以只读表上的无目标行操作可以作为空操作成功。 -测试 target `TsFile_Sqlite_Test` 动态加载刚构建的扩展，避免测试到系统中其他 -版本的插件。 +watermark 是可接受新时间的闭下界。普通空表从 INT64_MIN 开始；有源数据时为 +source_max + 1。可写表的 NULL watermark 表示时间空间耗尽，与只读表通过 mode 区分。 +xBegin 重新加载 SQLite 中的边界，避免另一连接封存后仍使用旧缓存允许迟到写入。 -## 4. Virtual table schema +## 4. 文件归属 -扩展解析重复的 `column=name:TYPE:CATEGORY` 参数，并向 SQLite 声明： +可写目录在建表时必须不存在或为空，不能与其他自有目录重合、嵌套或包含源文件。 +扩展解析已有祖先的真实路径，以独占创建并 fsync 的 `.tsfile-owner` 记录归属。 +标记绑定 SQLite 数据库规范路径及表名；内存数据库使用连接身份。改变 ATTACH 别名 +不改变归属，但复制或移动数据库不能直接复用原可写目录。重连和写事务检查归属。 -```sql -CREATE TABLE x( - <public columns>, - _tsfile_command TEXT HIDDEN, - _tsfile_cutoff INTEGER HIDDEN -); -``` +失败或回滚建表仅回收本次创建且拥有的标记和空目录。DROP 保留已提交文件及标记， +避免事务回滚后失去目录归属；后续清理由调用方负责。 -两个隐藏列只用于把控制命令送入 `xUpdate`，普通 `SELECT *` 不会返回它们。 +内容特征使用文件大小和遍历全部字节的 FNV-1a 指纹，用于检测变化，不提供密码学 +完整性证明。外部源文件必须由调用方保持路径和内容不变。扩展不因文件后缀就删除 +未知 `.tmp` 或 `.tsfile`，也不把源文件父目录当作自己拥有的目录。 -初始化阶段验证： +## 5. 显式 seal 与事务 -- 目录是绝对路径； -- 时间精度属于 `ms/us/ns`； -- 第一列且唯一 TIME 列是 `TIMESTAMP:TIME`； -- 至少一个 `STRING:TAG`； -- 列名非空、大小写不重复且不占用隐藏列名称。 +管理函数只接受独立顶层 SELECT。seal 创建内部 savepoint，并通过虚拟表的零行 +UPDATE 使 SQLite 注册该表的写事务回调；随后直接执行封存。因此封存参与同一连接 +的外层事务及 savepoint。连接上的管理重入保护阻止嵌套管理操作。 -模块调用： +非空封存流程如下： -```c -sqlite3_vtab_config(db, SQLITE_VTAB_DIRECTONLY); -sqlite3_vtab_config(db, SQLITE_VTAB_CONSTRAINT_SUPPORT, 1); -``` +1. 读取符合半开边界的热行，按可空 TAG 和时间排序。 +2. 写入临时 TsFile、完成 footer 并 fsync，保存 schema 和已知精度。 +3. 在 SQLite 事务中登记最终路径及指纹、删除对应热行、推进 watermark。 +4. xSync 重新打开临时文件检查元数据，以不覆盖已有目录项的原子 rename 发布文件， + 再 fsync 自有目录。 +5. SQLite 提交登记状态，xCommit 清除本次待提交文件记录。 -`DIRECTONLY` 限制从 view、trigger 等间接 schema 对象调用虚拟表，减少恶意 -数据库文件在连接打开时触发外部文件访问的风险。约束支持标志允许 `xUpdate` -向 SQLite 返回精确的约束错误。 +空封存只推进边界，不生成文件。显式 cutoff 为 int64 半开上界；export 自动封存 +可以额外覆盖 INT64_MAX 并将可写时间标记为耗尽。 -## 5. Shadow tables +xRollback 只删除本次操作拥有的临时或已发布文件。xSavepoint 按 SQLite savepoint ID +记录待提交文件数，xRollbackTo 删除其后的文件并恢复缓存配置，xRelease 移除释放的 +标记。回滚跨越新建表时，允许 SQLite 已删除 config，并释放本次新目录资源。 -假设逻辑表名为 `sensor`，`xCreate` 在逻辑表所属 schema 中创建： +macOS 使用 `renamex_np` 的 RENAME_EXCL，Linux 使用 `renameat2` 的 RENAME_NOREPLACE。 +已有目录项，包括悬空符号链接，均不会被替换。不支持该操作的文件系统或内核会报错。 -### 5.1 `sensor_data` +文件持久化先于 SQLite 提交文件引用。进程在 SQLite 提交前中断可能留下未登记文件， +但 SQLite 仍保留旧热行，该文件不参与查询。verify 可以报告未登记的 TsFile，重连 +不会猜测归属并自动删除。持久性依赖 SQLite journal/synchronous 设置和文件系统 +正确执行 fsync，这不是跨文件系统的分布式事务。 -```sql -CREATE TABLE sensor_data( - time INTEGER NOT NULL, - device TEXT NOT NULL, - temperature REAL, - UNIQUE(device, time) -); -``` +## 6. 自动封存导出 -真实列会根据用户 schema 展开。所有 TAG 加 TIME 形成复合唯一键。它是唯一的 -可变数据存储，rowid 由 SQLite 分配。 +export 禁止放入用户显式事务，先检查目标路径，再在内部事务中验证并捕获冷数据， +对可写表注册写事务后读取热数据。SQLite 的快照和写锁使并发冲突明确失败，不会让 +一次导出静默混入边界检查之外的写入。 -### 5.2 `sensor_segments` +本次所有热行自动封存并提交后，将捕获的完整数据排序、重写到独立 staging 目录。 +非空输出为一个 `part-000001.tsfile`，空表输出空目录。输出只含所选逻辑表，文件内部 +表名使用逻辑表名，保留已知时间精度。源文件的其他表不会被带出。 -```sql -CREATE TABLE sensor_segments( - path TEXT PRIMARY KEY, - cutoff INTEGER NOT NULL, - row_count INTEGER NOT NULL -); -``` +输出完成后 fsync staging，以不覆盖方式原子发布目标目录，再 fsync 父目录。 +封存提交与输出发布为两个阶段；第二阶段失败不会把冷数据变回热数据，也不会回退 +watermark。错误说明封存是否已提交；父目录 fsync 失败时还会说明完整输出已发布。 +普通失败清理本次已知 staging 文件，进程中断可以留下隔离目录，不发布部分结果。 +封存提交后的新写入不进入本次输出，仍保留在热区。 -这是冷段 manifest。`path` 是绝对文件路径，`cutoff` 是生成该段时的新水位， -`row_count` 用于诊断和后续优化。 +## 7. 诊断和验证范围 -### 5.3 `sensor_config` +`tsfile_table_info` 从当前事务视图读取配置、热行数和文件数。`tsfile_verify` 检查 +登记文件是否存在、Reader 是否可解析元数据、指定表 schema 是否存在，以及全文 +指纹是否一致；不逐页解码所有数据。它只扫描自有目录当前层的普通 `.tsfile`，不跟随 +扫描项的符号链接，也不递归或扫描外部源文件父目录。状态为 OK、MISSING、CORRUPT、 +MISMATCH、UNREGISTERED，仅报告，不注册、修复或删除文件。 -```sql -CREATE TABLE sensor_config( - id INTEGER PRIMARY KEY CHECK(id = 1), - watermark INTEGER NOT NULL, - precision TEXT NOT NULL, - directory TEXT NOT NULL, - schema TEXT NOT NULL -); -``` +集成测试覆盖三种建表、NULL 逻辑键、无 TAG、源表映射、空及多表文件、精度、INT64 +耗尽、业务 rowid 列、冷热写约束、事务/savepoint、重连、并发边界、源文件变化与缺失、 +目录归属、自动导出和标准 Reader 回读。回归还覆盖封存已提交后的导出失败、回滚跨越 +建表、复制数据库抢占原目录，以及发布时已有目录项。 -初始 watermark 是 `INT64_MIN`。schema 以列名、TsFile 类型枚举和类别枚举组成 -签名。`xConnect` 重新打开逻辑表时验证 precision、directory 和 schema 签名， -不一致返回 `SQLITE_CORRUPT`。 +额外子进程检查在 DELETE/WAL 两种 journal 模式下，封存提交前后直接退出并重新打开 +数据库，验证数据与冷热状态。这些检查不代表每个断电或文件系统故障点都已验证。 +构建及测试命令见 [用户手册](USER_GUIDE.md)。 -`xShadowName` 将 `_data`、`_segments` 和 `_config` 后缀报告给 SQLite。名称通过 -schema 限定和 identifier quoting 生成，因此 attached database 的 shadow -table 不会错误创建到 `main`。 +## 8. 当前限制 -`xDestroy` 删除三个 shadow table，但刻意不删除 TsFile 文件，避免 `DROP TABLE` -隐式执行不可恢复的外部文件删除。 - -## 6. 类型转换 - -扩展内部用 `Value` 保存精确类型、NULL 标志、数值或带长度的字节串。 - -写入方向： - -```text -sqlite3_value -> Value -> SQLite hot binding / Tablet value -``` - -读取方向： - -```text -SQLite column / C++ ResultSet -> Value -> sqlite3_result_* -``` - -TEXT、STRING 和 BLOB 都使用显式指针加长度复制，不把内容当作零结尾 C 字符串， -因此空字符串、嵌入 `\0` 的 BLOB 和 NULL 可以区分。冷读直接使用 C++ -`ResultSet`，不依赖当前 C wrapper 的字符串接口。 - -SQLite 是动态类型系统，扩展在 `xUpdate` 中做严格运行时检查。INT32/DATE 会 -额外检查 int32 上下界。BOOLEAN 接受 INTEGER，并归一化为 0 或 1。 - -## 7. 热区 DML 路径 - -所有 `INSERT/UPDATE/DELETE` 通过 `xUpdate` 路由： - -### INSERT - -1. 将 SQLite values 转成 schema 指定的 `Value`； -2. 验证 TIME 和 TAG 非 NULL； -3. 验证 `time >= watermark`； -4. 参数化插入 `_data`； -5. 由 shadow table 的 UNIQUE 约束检查 `(TAG..., TIME)`。 - -### UPDATE - -1. 冷行的合成 rowid 为负，直接返回 `SQLITE_CONSTRAINT`； -2. 不允许改变 SQLite rowid； -3. 校验完整的新行值和新时间； -4. 按正 rowid 更新 `_data`。 - -### DELETE - -- 负 rowid 返回 `SQLITE_CONSTRAINT`； -- 正 rowid 从 `_data` 删除。 - -因为 DML 最终是同一连接上的 SQLite shadow table DML，所以 journal/WAL、事务 -隔离、写锁和回滚仍由 SQLite 提供。 - -## 8. 查询规划和执行 - -### 8.1 `xBestIndex` - -当前接受以下可用约束： - -- TIME 的 `>`、`>=`、`<`、`<=`； -- 使用 BINARY collation 的 TAG `=`。 - -对于接受的约束，`argvIndex` 被编码进 `idxStr`。`omit` 保持为 0，要求 SQLite -对返回行再次执行原 SQL 条件。这样即使 libtsfile 与 SQLite 在边界、类型或 -collation 上存在差异，也不会产生错误结果。 - -`sqlite3_index_info.colUsed` 用于计算投影列。TIME 和所有下推约束涉及的列会被 -强制加入内部投影，以便执行边界检查和 SQLite 残余检查。 - -MVP 不设置 `orderByConsumed`，也不下推跨冷热来源的 `LIMIT/OFFSET`。 - -### 8.2 `xFilter` - -`xFilter` 解码约束和投影，然后依次调用： - -```text -read_hot() -> read_cold() -> HybridCursor.rows -``` - -当前 cursor 会物化本次查询的所有候选行。`xNext/xEof/xColumn/xRowid` 在该 -内存数组上实现 SQLite cursor 接口。这使 MVP 的合并逻辑简单，但不适合无界 -大结果集；后续应改造成 hot/cold 流式 cursor 和 k-way merge。 - -### 8.3 热读取 - -`read_hot` 动态生成参数化 SQL，只选择投影需要的列，并把接受的 TIME/TAG -约束写入 `_data` 查询。结果按 hot rowid 读取，然后执行一次内部边界和 TAG -字节比较；返回 SQLite 后仍有 SQLite 的最终残余检查。 - -### 8.4 冷读取 - -`read_cold` 从 manifest 按 path 遍历每个 TsFile： - -1. 为当前文件创建 `TsFileReader`； -2. 将 TAG 等值条件组合成 `TagFilterBuilder` AND filter； -3. 将开闭时间范围转换为 TsFileReader 的闭区间； -4. 只请求投影所需的 value columns； -5. 从 C++ ResultSet 恢复精确类型、NULL 和字节长度； -6. 关闭 ResultSet 和 reader。 - -当前 manifest 的 cutoff 尚未用于跳过不相交的段；reader 会对每个段应用时间 -范围。FIELD 谓词不进入 TsFile reader，由 SQLite 在结果返回后复核。 - -### 8.5 Rowid - -热行直接暴露 SQLite `_data.rowid`，通常为正值。冷行按 -`(segment ordinal, row ordinal)` 编码为负 int64。负号同时充当不可变标志， -使 `xUpdate` 可以拒绝冷行修改。 - -冷 rowid 只对一次遍历有意义，不属于持久存储协议。 - -## 9. Seal 写路径 - -控制语句： - -```sql -INSERT INTO sensor(_tsfile_command, _tsfile_cutoff) -VALUES ('seal', ?); -``` - -在 `xUpdate` 中识别命令，cutoff 必须是 SQLite INTEGER 且不能小于当前 -watermark。 - -### 9.1 段生成 - -`write_segment()` 完成： - -1. 确保绝对目录存在； -2. 使用表名、进程 ID 和单调计数器生成不冲突的 `.tmp`/`.tsfile` 路径； -3. 以 `O_CREAT | O_EXCL` 创建临时文件； -4. 从 `_data` 读取 `[watermark, cutoff)`； -5. 按所有 TAG、TIME 排序； -6. 每 1024 行填充一个 table-model `Tablet`； -7. 通过 `TsFileTableWriter` 写入； -8. 写入时间精度 property、flush、footer 并 fsync 文件。 - -若区间没有行，writer 仍会被正确关闭，随后删除空临时文件。调用方仍更新 -watermark，但不插入 manifest 记录。 - -### 9.2 SQLite 状态变更 - -非空段写完临时文件后，`seal()` 在当前 SQLite 事务中： - -1. 向 `_segments` 插入最终文件路径、cutoff 和 row count； -2. 从 `_data` 删除 `[watermark, cutoff)`； -3. 更新 `_config.watermark`； -4. 把文件记录到当前事务的 `pending` 数组。 - -这些 shadow table 修改与调用 seal 的外层 SQL 事务相同，不单独提交。 - -## 10. 事务回调和文件原子性 - -SQLite 数据库事务不能直接回滚外部文件，因此扩展通过 virtual table transaction -callbacks 协调两者。 - -```text -xBegin - | -xUpdate(seal): write .tmp + update transactional shadow state - | -xSync: validate .tmp -> atomic rename -> fsync directory - | -SQLite commits database/WAL - | -xCommit: forget pending state -``` - -### 10.1 `xSync` - -SQLite 准备提交时，扩展对每个 pending 文件执行： - -1. 使用独立 `TsFileReader` 重新打开临时文件，验证 footer 可读； -2. 确认最终路径不存在； -3. 在同一目录执行原子 `rename()`； -4. `fsync()` 目录，持久化目录项。 - -只有全部成功，SQLite 才继续提交数据库事务。 - -### 10.2 `xRollback` - -回滚时删除本事务仍存在的 `.tmp`，也删除已经 rename 但数据库事务未提交的 -`.tsfile`，然后从 `_config` 重新加载 watermark。 - -### 10.3 Savepoint - -`xSavepoint` 记录 pending 数组长度。`xRollbackTo` 删除 savepoint 之后创建的 -文件并重新加载事务可见的配置，`xRelease` 移除相应标记。 - -### 10.4 崩溃窗口 - -| 崩溃位置 | SQLite 恢复结果 | 文件结果与恢复方式 | -| --- | --- | --- | -| 写临时文件过程中 | 热数据和旧 manifest 保留 | 遗留 `.tmp`，下次 seal 清理 | -| 临时文件完成、rename 前 | 热数据和旧 manifest 保留 | 遗留 `.tmp`，下次 seal 清理 | -| rename 后、SQLite COMMIT 前 | 热数据和旧 manifest 保留 | 遗留未引用 `.tsfile`，下次 seal 清理 | -| SQLite COMMIT 后 | 新 manifest、水位和热表删除持久化 | manifest 引用的 `.tsfile` 已完成 rename 和目录 fsync | - -`cleanup_orphans()` 在 seal 已取得 SQLite 写事务的上下文中运行。它读取 manifest， -删除独占目录内未被引用且不属于当前 pending 集合的 `.tmp` 和 `.tsfile`。这也是 -为什么目录独占不是建议，而是正确性前提。 - -## 11. 并发模型 - -- 热 DML、manifest 和配置依赖 SQLite 的连接事务与写锁； -- seal 是同步写操作，不会在后台线程与 SQLite 事务脱离运行； -- 读事务看到由 SQLite 快照决定的 shadow state； -- manifest 只在 SQLite 事务提交后对其他连接可见； -- TsFile 发布使用同目录原子 rename，读取者不会观察到半写文件。 - -当前设计不提供跨进程独立修改 TsFile 目录的协调机制。目录必须只由拥有 SQLite -manifest 的扩展实例管理。 - -## 12. 安全和运维边界 - -- `DIRECTONLY` 限制间接调用，但加载 native extension 本身仍等价于加载本地 - 代码，应用应只加载可信二进制； -- 配置要求绝对路径，避免工作目录变化导致数据库重新打开到不同数据集； -- SQL identifier 和字符串均经过 quoting，运行时值使用 prepared statement； -- TsFile 文件权限当前为 `0644`，目录创建权限当前为 `0755`，最终仍受进程 - `umask` 影响； -- `DROP TABLE` 不清理外部文件； -- SQLite backup API 不会自动包含 TsFile，备份协议必须覆盖数据库和目录。 - -## 13. 测试覆盖 - -`cpp/test/sqlite/tsfile_sqlite_test.cc` 当前覆盖： - -- 热数据 CRUD； -- seal 的 commit 和 rollback； -- watermark 对旧时间写入的约束； -- INT32 越界； -- 冷行 UPDATE/DELETE 拒绝； -- NULL、空 TEXT 和包含零字节的 BLOB 往返； -- attached database 的 shadow table 隔离。 - -实现开发过程中还应持续补充： - -- 多连接 WAL 并发； -- 多次 seal 和多 TAG 查询； -- 独立 TsFileReader 验证 property/schema/row count； -- write/close/fsync/rename/commit 各故障点注入； -- 重启和孤儿文件清理； -- SQLite 3.45.3 主版本与 3.31 最低版本矩阵； -- Linux/macOS CI。 - -## 14. 当前限制与后续方向 - -当前 MVP 的主要技术债务： - -1. 把查询候选行全部物化到内存，应改为流式 merge cursor； -2. manifest 仅保存 cutoff，尚未记录每段 min/max time、TAG 统计和校验信息； -3. 没有跨段 compaction，段数增加后每次查询都要打开更多文件； -4. 没有 correction/tombstone，无法修改冷历史； -5. 没有后台 seal 调度和限流； -6. 没有冷段删除与 retention policy； -7. schema 演进需要新建逻辑表； -8. 没有一体化在线备份、迁移和目录重定位工具； -9. 尚未实现 Windows 文件同步和原子发布语义。 - -若继续演进，建议优先处理流式查询、segment pruning、故障注入测试和一致性备份 -协议，再考虑自动 seal、compaction 和历史修正。 +查询、封存及导出会在内存中收集数据，导出排序并重写完整快照，文件指纹也需要完整 +读取文件；当前尚不是限制内存用量的流式实现。早期原型的 column=、隐藏管理列及 +内部表布局已替换，不自动迁移原型数据库。首版不提供后台封存、compaction、自动 +保留期、历史修正、批量文件注册、原地 schema 演进或完整数据库备份恢复。 +Linux 特有文件发布路径需要 Linux CI 验证；本地验证环境是 macOS 和 Homebrew SQLite。
diff --git a/cpp/src/sqlite/TECHNICAL_GUIDE_EN.md b/cpp/src/sqlite/TECHNICAL_GUIDE_EN.md new file mode 100644 index 0000000..d159ee9 --- /dev/null +++ b/cpp/src/sqlite/TECHNICAL_GUIDE_EN.md
@@ -0,0 +1,223 @@ +<!-- + + Licensed to the Apache Software Foundation (ASF) under one + or more contributor license agreements. See the NOTICE file + distributed with this work for additional information + regarding copyright ownership. The ASF licenses this file + to you under the Apache License, Version 2.0 (the + "License"); you may not use this file except in compliance + with the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, + software distributed under the License is distributed on an + "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + KIND, either express or implied. See the License for the + specific language governing permissions and limitations + under the License. + +--> + +# SQLite + TsFile technical report + +This report describes the current experimental implementation. The [user +manual](USER_GUIDE_EN.md) covers daily operations and troubleshooting. There is one primary virtual +table module, `tsfile_hybrid`, with per-table writable or read-only mode. + +## Components and state + +| File | Responsibility | +| --- | --- | +| `tsfile_sqlite.cc` | Arguments, schema inference, hot/cold query adapter, writes, seal, filesystem ownership and virtual-table transaction callbacks | +| `tsfile_sqlite_management.inc` | Seal/export SQL functions and diagnostic virtual tables, included in the adapter's anonymous namespace | +| `../../test/sqlite/tsfile_sqlite_test.cc` | Integration tests using real SQLite connections and TsFile readers/writers | +| `CMakeLists.txt` | Loadable module, compatible SQLite headers, runtime library paths | + +A connection owns a registry of connected `HybridTable` objects keyed by SQLite +schema and logical table name. Each table stores its normalized columns, mode, +source mapping, precision, watermark, pending files and savepoint marks. +`HybridCursor` materializes rows for SQLite's xNext/xColumn callbacks. Management +functions resolve their schema-qualified arguments through the same connection +and enlist the same table transaction callbacks. + +Three creation modes converge on a common column vector and +`sqlite3_declare_vtab`: explicit columns with a writable directory, inferred +columns with a writable directory, and inferred columns without a directory. +An ordinary source has an implicit `time` TIME column. Extension-produced files +preserve a custom TIME name in `tsfile_sqlite.time_column`. Source inference selects +one file table, validates its schema, scans that table for maximum time and records +file identity before/after the scan. Empty table schemas are obtained through the +reader's complete schema metadata even when no data index exists. + +Persistent inferred tables reconnect from stored schema rather than reinferring +from a potentially changed source. This keeps PRAGMA column metadata and +file diagnostics available when a source disappears. Actual data access checks the +registered file identity and fails on missing or changed files. + +## SQLite storage + +All identifiers are quoted. Internal objects live in the same SQLite schema as +the logical table, including attached and temporary databases. + +| Object | Content | +| --- | --- | +| `<table>_tsfile$hot` | Writable tables only: private row ID and business columns | +| `<table>_tsfile$segments` | Path, cutoff, row count, file-internal table name, file identity | +| `<table>_tsfile$config` | Watermark, precision, directory, serialized schema, mode, source path/table/max time | +| `<table>_tsfile$key` | Unique index over normalized TAG values and TIME | + +Schema serialization uses length-prefixed names plus original TsFile type and +category codes. SQLite's mapped INTEGER/REAL/TEXT/BLOB declarations therefore do +not erase TsFile semantics. Object collisions fail creation; the extension does +not adopt existing user tables. xShadowName identifies the three shadow table +suffixes; direct application writes to shadow state are unsupported. + +The private hot row-ID column chooses an unused name starting at `tsfile$rowid`. +Business columns named `rowid`, `oid` or `_rowid_` do not control internal row +identity. Cold rows receive negative synthetic IDs; these identify immutable +rows during an operation and are not stable business keys. + +For each TAG the unique index contains `(tag IS NULL)` and `coalesce(tag,'')`, +followed by TIME after all TAG components. This makes two NULL key components +conflict while distinguishing NULL from empty string. Zero-TAG tables use TIME +alone. Source duplicates are retained because new rows must be later than the +selected source maximum, rather than being merged into historical keys. + +## Query and write paths + +xBestIndex describes integer time ranges, BINARY TAG equalities and projections. +xFilter reads matching hot rows and registered cold segments; it applies file +mapping when the source table differs from the logical table. It uses the pending +temporary path for segments sealed inside an uncommitted transaction. TsFile +readers receive supported time and TAG filters; SQLite retains residual predicate +evaluation and owns joins, aggregates, ORDER BY and LIMIT semantics. + +xUpdate validates types, TIME non-nullness, uniqueness and watermark before +writing the hot shadow table. Actual cold UPDATE/DELETE and actual read-only +writes return `SQLITE_READONLY`, a non-constraint error that aborts the whole +statement even with IGNORE/FAIL. SQLite may optimize zero-row DML away without +calling xUpdate, so read-only no-ops can succeed. + +The watermark is an inclusive lower bound. Explicit empty tables start at +INT64_MIN; writable source-backed tables start at source_max+1. A NULL watermark +represents exhausted time space for writable tables, or absence of a writable +boundary for read-only tables. Mode disambiguates these states. xBegin reloads +persisted config to avoid a stale cached watermark after another connection seals. + +## Filesystem ownership + +A writable directory must be absent or empty at creation and cannot overlap +another owned directory or contain the external source. Existing ancestors are +canonicalized. A `.tsfile-owner` marker is created exclusively and synced. It +contains the canonical SQLite database path and table name; in-memory databases +use a connection-local identity. Reconnect and write transactions validate the +marker. An ATTACH alias can change, but a copied/moved database cannot silently +share the original writable directory. + +The table tracks newly created directories so a failed or rolled-back creation +can remove its own marker and empty directories. DROP preserves committed files +and the marker; removing a marker during transactional DROP would break a later +rollback. The caller is responsible for eventual archival or deletion. + +File identity is size plus a full-byte FNV-1a fingerprint. It detects ordinary +replacement/change and is not a cryptographic guarantee. External files remain +caller-owned and must be immutable. The extension never scans an external parent +for ownership, and never deletes unknown files merely because of `.tmp` or +`.tsfile` suffixes. + +## Seal and transaction callbacks + +`tsfile_seal` requires a standalone top-level SELECT. It creates an internal +savepoint and performs a zero-row UPDATE of the virtual table to enlist SQLite's +virtual-table write callbacks. The hot-to-cold transition then shares SQLite's +transaction and savepoint lifetime. A reentrancy guard prevents nested management +operations. Management functions and diagnostic tables are direct-only interfaces. + +For a nonempty seal: + +1. Collect eligible hot rows and sort by nullable TAG values, then time. +2. Write a new temporary TsFile with schema/precision, finish its footer and fsync. +3. Register the intended final path and identity, delete eligible hot rows and + update watermark inside the SQLite transaction. +4. In xSync, reopen the temporary file to check metadata, publish it with an + atomic no-replace rename and fsync the owned directory. +5. SQLite commits its own state. xCommit forgets the pending ownership records. + +Empty seals update only the watermark. Explicit cutoff is half-open; automatic +export can use an inclusive final bound for INT64_MAX and mark exhaustion. + +xRollback removes only files owned by the pending operation and restores cached +configuration from SQLite. xSavepoint records pending-file counts keyed by SQLite +savepoint ID; xRollbackTo removes subsequent files and reloads config. Rolling +back past a newly created virtual table tolerates its already-removed config and +releases the new directory resources. xRelease removes released savepoint marks. + +File publication uses `renamex_np(..., RENAME_EXCL)` on macOS and Linux +`renameat2(..., RENAME_NOREPLACE)`. An existing directory entry, including a +dangling symlink, is never overwritten. A filesystem/kernel without the required +operation causes an I/O error rather than a replacement fallback. + +Files become durable before SQLite can commit a reference to them. A process +interruption before SQLite commit can leave an unregistered file while SQLite +retains the old hot rows; the file is ignored and can be reported by verify. +The extension deliberately does not guess ownership and delete such files on +reconnect. These guarantees depend on the SQLite journal/synchronous configuration +and the filesystem honoring fsync; no cross-filesystem transaction is introduced. + +## Export snapshot and publication + +Export rejects explicit user transactions and non-standalone calls before side +effects. It validates output paths, opens an internal transaction, validates and +captures cold rows, and enlists writable state before collecting hot rows. SQLite +transaction locking prevents a concurrent writer from being silently included +between the snapshot and boundary update; an incompatible snapshot upgrade fails. + +All captured hot rows are sealed, and the internal savepoint is released to +commit that transition. The captured logical rows are then sorted and rewritten +to one standard TsFile in a unique sibling staging directory. A nonempty output +contains `part-000001.tsfile`; an empty output is an empty directory. The source +file's unrelated tables are never copied into the output. + +The staging directory is synced, published through atomic no-replace rename, and +its parent is synced. Generation/publication failures clean the operation's known +staging file where possible and report that sealing already committed. A crash +may leave an isolated staging directory. If the final parent fsync fails, the +error explicitly reports that complete output has already been published. +Automatic seal and output publication are two separate committed stages; an +output failure cannot restore mutability of already sealed rows. Later writes +are outside the captured output and remain in the hot area. + +## Diagnostics + +`tsfile_table_info` reads config and row/file counts in the caller's transaction +view. `tsfile_verify` enumerates registered files and checks Reader metadata, +selected schema existence and content fingerprint. Its statuses are OK, MISSING, +CORRUPT and MISMATCH. It also reports unregistered regular `.tsfile` files in the +owned directory as UNREGISTERED, without following scan symlinks or recursing. +Verification does not decode every page, repair files or change registration. + +Both interfaces are eponymous-only virtual tables with fixed visible columns and +a hidden schema-qualified table argument. Their diagnostic row IDs are ephemeral. + +## Validation and remaining limits + +The SQLite integration suite covers the three creation modes, nullable logical +keys, no-TAG tables, source/target mapping, empty and multi-table sources, precision, +watermark exhaustion, rowid-named columns, hot CRUD, immutable cold rows, +transactions/savepoints, persistent reconnect, concurrent connection boundaries, +source loss/change, directory ownership, automatic export and round-trip reading. +Regression cases also exercise output failure after seal commit, rollback across +CREATE, copied-database directory reuse, and existing entries during publication. + +Additional subprocess checks reopen databases after abrupt exit before or after +seal commit in both DELETE and WAL journal modes. These checks cover those +boundaries, not every power-loss or filesystem fault. Build and test commands are provided in the [user guide](USER_GUIDE_EN.md). + +Queries, seal and export currently materialize rows, and export rewrites the full +snapshot. Whole-file fingerprint checks also add I/O. This is a functional +implementation, not a bounded-memory streaming or performance-tuned engine. +There is no schema migration for the prototype shadow layout, background seal, +compaction, automatic retention, historical correction, multi-file registration, +or full database backup/restore. Linux-specific publication code needs Linux CI; +local validation was on macOS with Homebrew SQLite.
diff --git a/cpp/src/sqlite/USER_GUIDE.md b/cpp/src/sqlite/USER_GUIDE.md index a814721..5a87136 100644 --- a/cpp/src/sqlite/USER_GUIDE.md +++ b/cpp/src/sqlite/USER_GUIDE.md
@@ -19,551 +19,429 @@ --> -# tsfile_sqlite 用户手册 +# SQLite + TsFile 用户手册 -`tsfile_sqlite` 是一个实验性的 SQLite loadable extension。它提供 -`tsfile_hybrid` 虚拟表，让一张逻辑表同时使用两种物理存储： +使用 `tsfile_sqlite`，你可以通过 SQL 查询已有 TsFile，也可以持续写入新数据， +再将它们保存为 TsFile。新增数据先保存在 SQLite 中，可以更新和删除；封存后的 +数据保存在 TsFile 中，仍然可以查询，但不能再修改。 -- 尚未封存的近期数据保存在 SQLite shadow table 中，支持事务和 CRUD； -- 已封存的历史数据保存在不可变的 TsFile 段文件中； -- 应用继续对同一张虚拟表执行 SQL，扩展自动合并冷热数据。 +本手册带你完成一次建表、读写、导出和重新读取，然后介绍日常使用与排障。 +示例使用 SQLite 命令行；应用程序也可以执行相同的 SQL。 +[English](USER_GUIDE_EN.md) · [技术报告](TECHNICAL_GUIDE.md) -它适合以追加为主、近期数据偶尔需要修正、历史数据可以冻结的时序场景。 -它不是 SQLite 通用表的替代存储引擎，也不会自动把已有 SQLite 表转换为 -TsFile。 +## 1. 准备运行环境 -## 1. 环境要求 +需要 Linux 或 macOS、支持加载扩展的 SQLite 3.31 或更高版本，以及同一次构建生成的 +`tsfile_sqlite` 和共享库 `libtsfile`。已有这两个库时，可以直接进入下一节。 +当前支持 TsFile 的表模型文件。 -- Linux 或 macOS； -- SQLite 3.31 或更高版本； -- 支持加载扩展的 SQLite 构建； -- CMake 构建时启用共享版 `libtsfile`； -- TsFile 目录必须使用绝对路径，并由一张逻辑表独占。 - -当前 MVP 不支持 Windows。 - -## 2. 构建 - -在仓库根目录执行： +从源码构建时，在仓库根目录执行： ```bash cmake -S cpp -B cpp/build/sqlite \ -DBUILD_SQLITE_EXTENSION=ON \ -DTSFILE_BUILD_SHARED=ON \ -DBUILD_TEST=ON - cmake --build cpp/build/sqlite --target tsfile_sqlite -j ``` -产物位于构建目录的 `lib` 子目录： +构建产物位于 `cpp/build/sqlite/lib`。Linux 扩展名为 `tsfile_sqlite.so`，macOS 为 +`tsfile_sqlite.dylib`。部署时将扩展和 `libtsfile` 放在同一目录。 -- Linux：`cpp/build/sqlite/lib/tsfile_sqlite.so` -- macOS：`cpp/build/sqlite/lib/tsfile_sqlite.dylib` - -扩展依赖同一次构建产生的 `libtsfile`。默认 RPATH 会从扩展所在目录寻找 -`libtsfile`，部署时建议把二者放在同一目录。 - -运行扩展测试： +macOS 系统 SDK 的 SQLite 头文件禁用了扩展加载。使用 Homebrew SQLite 时，可以 +改用下面的配置命令，然后执行上面的构建命令： ```bash -cmake --build cpp/build/sqlite --target TsFile_Sqlite_Test -j -ctest --test-dir cpp/build/sqlite/test -R TsFileSqliteTest \ - --output-on-failure +cmake -S cpp -B cpp/build/sqlite \ + -DBUILD_SQLITE_EXTENSION=ON \ + -DTSFILE_BUILD_SHARED=ON \ + -DBUILD_TEST=ON \ + -DSQLite3_INCLUDE_DIR="$(brew --prefix sqlite)/include" \ + -DSQLite3_LIBRARY="$(brew --prefix sqlite)/lib/libsqlite3.dylib" ``` -## 3. 加载扩展 +命令行也应使用支持扩展加载的 SQLite。Homebrew 安装的命令可通过 +`"$(brew --prefix sqlite)/bin/sqlite3"` 启动。 -### 3.1 SQLite CLI +## 2. 跑通第一个示例 + +本节的步骤可以按顺序执行，完成后会得到一个 SQLite 数据库和一个可独立读取的 +TsFile。示例使用 `/tmp/tsfile-demo`；请选择一个尚不存在的目录。重复练习时换一个 +目录名，并替换后续示例中的路径。正式数据应使用持久存储目录。 + +### 打开数据库并加载扩展 + +在终端执行： + +```bash +mkdir /tmp/tsfile-demo +sqlite3 /tmp/tsfile-demo/demo.db +``` + +进入 SQLite 后，将下面的扩展路径替换为构建产物的绝对路径： ```sql .load /absolute/path/to/tsfile_sqlite +.headers on +.mode column ``` -SQLite CLI 通常会根据平台自动补全 `.so` 或 `.dylib` 后缀。也可以传入完整 -文件名。 +每次重新打开连接都需要加载扩展。`.load` 可以使用完整的 `.so` 或 `.dylib` 文件名。 -### 3.2 C/C++ 应用 - -```c -sqlite3_enable_load_extension(db, 1); - -char *error = NULL; -int rc = sqlite3_load_extension( - db, "/absolute/path/to/tsfile_sqlite", NULL, &error); - -sqlite3_enable_load_extension(db, 0); -``` - -应用应在打开数据库连接后、访问 hybrid 表之前加载扩展。生产环境建议加载 -完成后立即关闭动态扩展加载能力。 - -## 4. 创建逻辑表 - -以下按目标功能设计定义建表语法；示例表达待实现接口，不代表当前原型已支持。 -列定义采用 `列名 类型 [类别]`，省略类别时默认为 `FIELD`；`TIME` 和 `TAG` 显式声明。 +### 创建一张表并写入数据 ```sql CREATE VIRTUAL TABLE sensor USING tsfile_hybrid( time TIMESTAMP TIME, device STRING TAG, - region STRING TAG, temperature DOUBLE FIELD, - status STRING FIELD, - payload BLOB FIELD, - directory='/var/lib/example/sensor', + directory='/tmp/tsfile-demo/sensor-segments', timestamp_precision='ms' ); + +INSERT INTO sensor VALUES + (1000, 'd1', 21.5), + (2000, 'd1', 22.0), + (3000, 'd2', 19.0); ``` -列定义按书写顺序组成 schema，表级选项使用 `key=value`。未知选项、重复的表级 -选项和不合法的列定义在建表时返回明确错误。标识符支持双引号转义，例如 -`"sensor value" DOUBLE`；字符串选项使用单引号。 +这里的 time 保存毫秒时间戳，device 标识设备，temperature 保存测量值。 +`directory` 是这张表封存数据时使用的独占目录，扩展会创建它；建表时它必须不存在 +或为空。此时三行数据都在 SQLite 中，还没有封存。 -模块参数如下： +### 查询和修改 -| 参数 | 要求 | -| --- | --- | -| `directory` | 必填、绝对路径、由当前逻辑表独占 | -| `timestamp_precision` | 必填，只能是 `ms`、`us` 或 `ns` | -| `column` | 可重复，格式为 `名称:类型:类别` | +```sql +SELECT time, device, temperature FROM sensor ORDER BY time; +``` -列定义的目标规则如下： +结果为： -- 恰好一个 `TIME` 列，必须是第一列，类型为 `TIMESTAMP`，值不能为 `NULL`； -- `TAG` 列可以有零个或多个；存在时类型必须为 `STRING`，值允许为 SQL `NULL`； -- 有 TAG 时，全部 TAG 与 TIME 共同组成唯一键；无 TAG 时，TIME 单独组成唯一键； -- 不声明类别的列默认为 `FIELD`，FIELD 值可以为 `NULL`； -- 列名不能仅靠 ASCII 大小写区分，例如 `Temperature` 和 `temperature` 视为重名； -- 封存通过第 7 节的管理 UDF 发起，业务 schema 不需要声明或操作 - `_tsfile_command`、`_tsfile_cutoff` 控制列。 +```text +time device temperature +1000 d1 21.5 +2000 d1 22.0 +3000 d2 19.0 +``` -NULL TAG 的目标语义： +修正第二条记录，再查看结果： -- SQL `NULL`、空字符串 `''` 和字符串 `'null'` 是三个不同值，封存和查询必须保留区别； -- 判定逻辑唯一键时，相同位置的两个 NULL TAG 视为同一个键分量。例如同一 TIME 下， - 两条 `(device=NULL, region='cn-east')` 记录冲突； -- NULL 查询使用 `IS NULL`，普通 WHERE 表达式继续遵循 SQLite 的 NULL 语义， - 不把 `= NULL` 改成相等比较； -- 热数据的唯一键检查必须显式处理 NULL，不能仅依赖 SQLite 默认 UNIQUE 对 NULL - 的处理，也不能用可能与实际 TAG 冲突的字符串替换 NULL。 +```sql +UPDATE sensor SET temperature=22.5 WHERE time=2000 AND device='d1'; +SELECT temperature FROM sensor WHERE time=2000 AND device='d1'; +``` -无 TAG 表可按以下方式定义；每个时间戳最多对应一行： +查询返回 `22.5`。尚未封存的数据也可以通过普通 DELETE 删除。 + +### 导出为 TsFile + +```sql +SELECT tsfile_export('main.sensor', '/tmp/tsfile-demo/export-001'); +``` + +返回 `1`，表示生成了一个文件： + +```text +/tmp/tsfile-demo/export-001/part-000001.tsfile +``` + +这次调用会自动封存当前三行数据，再导出完整表内容，不需要提前执行 seal。 +导出文件是独立副本，可以交给标准 TsFile Reader 读取。原表仍然能查到三行数据， +但这些行已经封存，不能再更新或删除。 + +查看现在还有多少可修改的数据，以及下一次写入允许的起点： + +```sql +SELECT hot_rows, watermark FROM tsfile_table_info('main.sensor'); +``` + +结果为 `hot_rows=0`、`watermark=3001`：热数据已全部封存，后续新增时间必须不小于 +3001。导出函数中的 `main.sensor` 指当前数据库的 sensor 表，管理操作需要带上这个 +数据库前缀。 + +### 直接查询刚导出的文件 + +```sql +CREATE VIRTUAL TABLE temp.history USING tsfile_hybrid( + file='/tmp/tsfile-demo/export-001/part-000001.tsfile', + source_table='sensor' +); + +SELECT time, device, temperature FROM history ORDER BY time; +``` + +结果与刚才导出的三行数据一致，包括修正后的 `22.5`。没有提供 directory，所以 +history 只用于查询。`temp` 表在关闭连接后消失，文件仍然保留。 + +这里不需要声明列。`source_table='sensor'` 选择文件内部的 sensor 表，扩展会读取 +它的列结构；SQLite 中的名称 history 可以与文件内部表名不同。 + +### 基于这份文件继续写入 + +```sql +CREATE VIRTUAL TABLE continued USING tsfile_hybrid( + file='/tmp/tsfile-demo/export-001/part-000001.tsfile', + source_table='sensor', + directory='/tmp/tsfile-demo/continued-segments' +); + +INSERT INTO continued VALUES (4000, 'd1', 23.0); +SELECT time, device, temperature FROM continued ORDER BY time; +``` + +这次查询返回四行。前三行仍从原 TsFile 读取，新增的 4000 行保存在 SQLite 热区， +可以继续修改；原文件和 history 的查询结果不变。 + +文件中最大时间是 3000，因此 continued 只能接受晚于 3000 的新数据，即使写入的是 +另一个设备也一样。提供 directory 就表示为后续写入准备独立存储空间。 + +## 3. 使用你自己的数据 + +### 从空表开始采集 + +按照示例中的 sensor 建表方式，换成业务列名、独占目录和实际时间单位。 +每列使用 `列名 类型 类别` 声明：第一列是 `TIMESTAMP TIME`，设备等标识列使用 +`STRING TAG`，测量值使用 FIELD。 + +TAG 可以有多个，也可以没有。例如每个时间只记录一个值时： ```sql CREATE VIRTUAL TABLE readings USING tsfile_hybrid( time TIMESTAMP TIME, - value DOUBLE, - directory='/var/lib/example/readings', + value DOUBLE FIELD, + directory='/tmp/tsfile-demo/readings-segments', timestamp_precision='ms' ); ``` -所有 TAG 列与 TIME 列共同组成唯一键。例如上表的唯一键是： +readings 每个时间戳只能新增一条记录；sensor 则以 `(device, time)` 区分记录。 +列名包含空格时使用双引号，例如 `"sensor value" DOUBLE FIELD`。列名不能仅靠 +大小写区分，每列都要写明 TIME、TAG 或 FIELD。 -```text -(device, region, time) -``` +### 打开已有 TsFile -### 4.1 数据类型映射 +先确认文件的绝对路径和文件内部表名，再按照 history 或 continued 的示例建表。 +只查询时省略 directory；需要追加时提供一个新的独占目录。每次建表选择一个文件 +中的一张表，不从文件名猜测表名，也不自动读取文件中的其他表。 -| TsFile 类型 | SQLite 表现 | 写入要求 | -| --- | --- | --- | -| `BOOLEAN` | INTEGER | 必须传 SQLite INTEGER；0 为假，非 0 为真 | -| `INT32` | INTEGER | 必须在 int32 范围内 | -| `INT64` | INTEGER | SQLite int64 | -| `FLOAT` | REAL | INTEGER 或 REAL | -| `DOUBLE` | REAL | INTEGER 或 REAL | -| `TEXT` | TEXT | SQLite TEXT | -| `STRING` | TEXT | SQLite TEXT | -| `BLOB` | BLOB | SQLite BLOB，保留长度和二进制零字节 | -| `DATE` | INTEGER | 必须在 int32 范围内 | -| `TIMESTAMP` | INTEGER | SQLite int64 | - -扩展不会换算时间戳。`timestamp_precision` 仅声明整数时间戳的单位，并写入 -非空 TsFile 段的 `tsfile_sqlite.timestamp_precision` property。 - -### 4.2 创建后的固定配置 - -schema、目录和时间精度会记录在配置 shadow table 中。数据库重新打开时， -扩展会验证这些信息是否与 `CREATE VIRTUAL TABLE` 中保存的参数一致。 - -当前版本不支持修改 schema、目录或时间精度。需要变更时，应创建一张新的 -逻辑表并迁移数据。 - -## 5. 写入和修改热数据 - -普通 DML 的用法与 SQLite 表一致： +文件建表不再写列定义。创建后可以查看 SQLite 识别到的结构： ```sql -INSERT INTO sensor(time, device, region, temperature, status) -VALUES (1700000000000, 'device-1', 'cn-east', 21.5, 'ok'); - -UPDATE sensor -SET temperature = 22.0 -WHERE device = 'device-1' - AND region = 'cn-east' - AND time = 1700000000000; - -DELETE FROM sensor -WHERE device = 'device-1' - AND region = 'cn-east' - AND time = 1700000000000; +PRAGMA table_info(continued); ``` -热数据实际写入 `<虚拟表名>_data` shadow table，因此自动使用 SQLite 的 -rollback journal/WAL、锁、唯一约束、事务和 savepoint。 +示例的三列为 `time INTEGER`、`device TEXT` 和 `temperature REAL`。查询和写入使用 +这里显示的列名。普通 TsFile 的时间列通常显示为 time；由本扩展生成的文件也会保留 +自定义时间列名。 + +如果文件没有时间精度信息，只查询时可以保持 unknown。追加数据前必须确认原文件 +时间单位，并在建表参数中补充 `timestamp_precision='ms'`、`'us'` 或 `'ns'`。 +已带精度的文件会自动继承该精度；显式填写的值必须与它一致。 + +请保持源文件路径和内容不变。新增数据写入 SQLite，不会追加到或改写这个源文件。 +源文件后续被替换时，原表不会自动刷新。 + +### 选择正确的值和时间单位 + +时间戳直接存储为整数，不自动在秒、毫秒、微秒之间换算。例如源数据是秒而表声明为 +ms，应用需要先完成单位换算，再写入正确的毫秒值。 + +| 声明类型 | 写入值 | +| --- | --- | +| BOOLEAN | 整数；0 为假，非 0 为真，查询返回 0 或 1 | +| INT32、DATE | int32 范围内的整数 | +| INT64、TIMESTAMP | int64 范围内的整数 | +| FLOAT、DOUBLE | 整数或小数 | +| STRING、TEXT | 文本 | +| BLOB | 二进制值，保留长度和零字节 | + +TIME 不能为 NULL。TAG 和 FIELD 可以为 NULL；TAG 必须是 STRING 类型。 +同一时间、相同 TAG 组合的新记录会冲突。在这个唯一键中，相同位置的 NULL 也视为 +相同分量，因此不能用 NULL 绕过重复检查。NULL、空字符串和文本 `'null'` 各不相同。 +查找 NULL 使用 `IS NULL`。 + +已有源文件中的重复记录会按原样查询，不会在建表时自动去重。 + +## 4. 日常查询、修改与封存 + +### 用普通 SQL 查询 + +对逻辑表执行查询时，无需区分数据在 SQLite 还是 TsFile 中。可以使用条件、关联、 +聚合和排序。例如在完成第二节后： + +```sql +SELECT device, avg(temperature) AS avg_temperature +FROM continued +WHERE time >= 1000 AND time < 5000 +GROUP BY device +ORDER BY device; +``` + +需要固定顺序时写出 ORDER BY。时间范围和设备等 TAG 条件有助于减少扫描量。 +不要把隐含 rowid 当作持久业务主键；封存和重新查询可能改变它。 + +### 成批写入或修正近期数据 ```sql BEGIN; - -INSERT INTO sensor(time, device, region, temperature) -VALUES (1700000001000, 'device-1', 'cn-east', 22.1); - -UPDATE sensor -SET status = 'checked' -WHERE device = 'device-1' AND time = 1700000001000; - +INSERT INTO continued VALUES (5000, 'd2', 20.0); +UPDATE continued SET temperature=23.5 WHERE time=4000 AND device='d1'; COMMIT; ``` -## 6. 查询冷热数据 +需要取消整批修改时，用 ROLLBACK 代替 COMMIT。热数据也支持 savepoint。 +通常应通过时间和 TAG 精确定位要修改的记录。 -无论数据位于 SQLite 还是 TsFile，都查询同一张虚拟表： +若 UPDATE 或 DELETE 实际命中任意已封存行，整条语句都会失败，包括对其他热行的 +修改；IGNORE、FAIL 等冲突选项也不能跳过这个限制。仅查询冷数据不受影响。 + +### 提前冻结一段历史 + +当某个时间之前的数据不再需要修正时，可以主动封存，不必等到导出： ```sql -SELECT time, device, temperature, status -FROM sensor -WHERE device = 'device-1' - AND time >= 1700000000000 - AND time < 1700086400000 -ORDER BY time; +SELECT tsfile_seal('main.continued', 4500); ``` -常规 SQLite SQL 仍然可用，包括 FIELD 条件、表达式、聚合、排序和分页： +如果已按本节顺序操作，返回 `1`：4000 的记录被封存，5000 的记录继续留在热区。 +4500 是不包含的上界，时间恰好等于 4500 的记录也会留在热区。此后新数据必须不早于 +4500。封存不改变查询结果。 -```sql -SELECT device, avg(temperature) -FROM sensor -WHERE time >= 1700000000000 - AND temperature IS NOT NULL -GROUP BY device; -``` +即使边界之前没有热行，较大的 cutoff 仍会推进写入起点。因此，应按业务允许的迟到 +和修正窗口选择 cutoff。重复使用当前边界返回 0，使用更早的边界会失败。 -为了得到更好的 TsFile 扫描效率，查询应尽量包含： - -- 整数时间范围； -- 使用 `BINARY` collation 的 TAG 等值条件； -- 只选择需要的列。 - -扩展会把这些条件和投影下推到冷热读取路径。FIELD 条件、排序、聚合、 -`LIMIT/OFFSET` 由 SQLite 在合并结果上处理。扩展不会声称原始输出已经满足 -`ORDER BY`，因此需要稳定顺序时必须显式写出 `ORDER BY`。 - -所有已下推的约束仍由 SQLite 二次检查，以保证 SQL 结果正确。 - -## 7. 封存历史数据 - -使用两个隐藏列发送 `seal` 命令： - -```sql -INSERT INTO sensor(_tsfile_command, _tsfile_cutoff) -VALUES ('seal', 1700086400000); -``` - -`cutoff` 是不包含的上界。上述操作封存： - -```text -旧 watermark <= time < 1700086400000 -``` - -其中 `time == 1700086400000` 的行仍在热区，可以继续修改。 - -一次非空 seal 会： - -1. 按全部 TAG、TIME 排序读取待封存热数据； -2. 使用 Tablet 批量写入临时 TsFile； -3. 校验并将临时文件原子改名为 `.tsfile`； -4. 在 manifest 中登记文件； -5. 从热表删除已封存行； -6. 将 watermark 推进到 cutoff。 - -如果区间内没有数据，不生成 TsFile，但仍会推进 watermark。cutoff 不能小于 -当前 watermark。 - -seal 是同步写操作，在完成期间会占用 SQLite 写事务。可以显式把它放入事务： - -目标设计使用显式管理 UDF 封存数据： - -```sql -SELECT tsfile_seal('main.sensor', 1700086400000); -``` - -`tsfile_seal(table_name, cutoff)` 的接口契约： - -- `table_name` 指定一张 hybrid 逻辑表，示例中为 main schema 下的 sensor； -- `cutoff` 必须为整数，单位与该表的 timestamp_precision 相同； -- 封存 `[当前 watermark, cutoff)`，等于 cutoff 的行继续留在热区； -- cutoff 小于当前 watermark 时返回约束错误，等于 watermark 时返回 0； -- 成功返回本次封存的行数。空区间返回 0，但 cutoff 更大时仍推进 watermark； -- 取得写权限后重新读取 watermark。封存与普通写入使用同一事务协调； -- 自动提交模式下，独立语句完成时提交；显式事务内不擅自提交外层事务， - 返回行数仅表示本事务已执行封存，最终持久化取决于外层 COMMIT； -- 失败时撤销本次调用的元数据、热数据删除和待发布文件，不能部分封存。 - -也可以由调用方显式控制事务： +seal 同步执行，可以参与显式事务： ```sql BEGIN IMMEDIATE; -SELECT tsfile_seal('main.sensor', 1700086400000); -COMMIT; +SELECT tsfile_seal('main.continued', 5001); +ROLLBACK; ``` -该 UDF 属于有副作用的管理操作，规定以独立顶层 `SELECT` 调用，不支持放入 -逐行查询、视图、触发器或其他 schema 表达式。注册时不标记为 deterministic， -并限制间接调用。业务接口不再要求向隐藏控制列插入数据。 +这里的回滚会撤销本次封存，5000 的记录仍是热数据。正式保留封存结果时改用 COMMIT。 +在外层事务提交之前，seal 返回成功只表示当前事务内操作成功。 -如果事务回滚，manifest、watermark 和热数据删除都会回滚，扩展也会删除本次 -事务产生的临时文件或已经改名的段文件。 +## 5. 导出和交付数据 -## 8. Watermark 和冷数据不可变性 +每次需要一份独立的完整数据副本时，直接执行 export，并换用一个尚不存在的输出目录： -watermark 把逻辑时间轴分成两部分： - -```text -time < watermark 冷区：不可修改 -time >= watermark 热区：允许 INSERT/UPDATE/DELETE +```sql +SELECT tsfile_export('main.continued', '/tmp/tsfile-demo/export-002'); ``` -以下操作会返回约束错误： +先提交或回滚当前事务，再单独执行这条 SELECT。export 和 seal 都应独立调用， +不要放入逐行查询、视图、触发器或其他表达式中。 -- 插入 `time < watermark` 的行； -- 把热行的时间更新到 watermark 之前； -- 更新或删除已经位于 TsFile 的冷行； -- 写入重复的 `(所有 TAG, TIME)` 唯一键。 +export 包含本次数据视图中的外部历史、已封存记录和全部当前热记录。它自动封存热数据， +所以输出包含刚写入的数据；自动封存提交之后才到达的新写入留给下一次导出。 +只读文件表也可以 export，输出只包含选中的表。 -当前版本没有 correction 或 tombstone。如果业务必须修正历史数据，需要重建 -逻辑表或在业务层保留单独的修正数据。 +当前非空表导出一个 `part-000001.tsfile`，返回 1；空表导出空目录，返回 0。 +输出文件内部表名使用当前逻辑表名，例如这次是 continued。交付给其他使用者时，同时 +告知这个表名，便于对方通过 source_table 选择它。已知时间精度随文件保留。 -## 9. 内部状态与诊断 +输出目录的父目录必须已经存在。不要把输出放在表的自有段目录内，也不要让它包含 +或替代源文件；和外部文件放在同一个父目录下可以。已有输出路径不会被覆盖。 +删除导出副本不会影响原表，但若另建了引用该副本的表，例如第二节的 history，则 +仍需保留副本供它读取。 -每张名为 `sensor` 的 hybrid 表拥有三个 SQLite shadow table： +导出成功后，本次热数据已经冻结，新写入必须晚于这些数据的最大时间。若需要继续 +修改近期记录，请在修改完成后再导出。 -| 表 | 内容 | +### 导出失败后如何处理 + +先看错误信息，再查询 `tsfile_table_info` 确认热行数和 watermark： + +| 错误发生时的状态 | 下一步 | | --- | --- | -| `sensor_data` | 可变热数据 | -| `sensor_segments` | TsFile 路径、cutoff 和行数 manifest | -| `sensor_config` | watermark、精度、目录和 schema 签名 | +| 路径检查失败或自动封存尚未提交 | 修正路径、权限或文件问题后重试；本次操作没有提交封存变化 | +| 已提交自动封存，生成输出失败 | 数据仍可查询，但已经封存；修复输出问题后使用新目录重试 | +| 完整输出已发布，父目录同步失败 | 先检查目标目录及文件，不要直接覆盖；需要重新导出时使用新目录 | -可以只读查看它们进行诊断： +失败不会把已经提交的冷数据恢复成可修改的热数据。进程中断可能留下名称包含 +`.tsfile-export-` 的临时目录，不要把它当作已完成的交付结果。 + +导出保存表数据，不保存原 SQLite 数据库的全部表、业务配置或冷热状态。空表导出也 +不保存表定义。它不能替代完整的业务数据库备份。 + +## 6. 查看状态和处理常见问题 + +### 确认还能写入什么数据 ```sql -SELECT watermark, precision, directory FROM sensor_config; - -SELECT path, cutoff, row_count -FROM sensor_segments -ORDER BY cutoff; - -SELECT count(*) AS hot_rows FROM sensor_data; +SELECT mode, hot_rows, watermark, append_available, timestamp_precision +FROM tsfile_table_info('main.continued'); ``` -内部对象采用带符号的命名形式 `"<逻辑表名>_tsfile$<用途>"`。例如 sensor 对应： +hot_rows 是尚可修改的热行数，watermark 是新增时间的最小允许值。mode 为 readonly +时只能查询。append_available 为 1 表示仍有可表示的新时间，但写入仍需满足唯一键 +等约束。状态只反映查询当时的情况，最终以写操作结果为准。 -| 内部表 | 用途 | +若最大时间已达到 INT64_MAX，append_available 为 0，watermark 为 NULL，不能再 +追加更晚数据，原有数据仍可查询和导出。显式 seal 的半开 int64 上界无法覆盖时间 +为 INT64_MAX 的热行；export 可以自动封存它。 + +### INSERT、UPDATE 或 DELETE 失败 + +先用上面的状态查询检查模式和时间边界。引用文件但没有指定 directory 的表只读； +需要追加时，以新名称和新目录另建可写表。没有目标行的写语句可能作为空操作成功， +这不表示只读表变成了可写表。 + +可写表中，新时间必须不小于 watermark。引用外部文件时，这意味着严格晚于源表的 +最大时间，不能回填历史空隙。再检查是否重复了 `(全部 TAG, time)`、TIME 是否为 NULL， +以及类型和数值范围是否正确。UPDATE/DELETE 失败时还应确认目标记录尚未封存。 + +### 源文件找不到、查询失败或怀疑文件变化 + +```sql +SELECT path, status, detail FROM tsfile_verify('main.continued'); +``` + +按报告检查对应路径： + +| 状态 | 如何处理 | | --- | --- | -| `"sensor_tsfile$hot"` | 可变热数据 | -| `"sensor_tsfile$segments"` | TsFile 文件登记信息 | -| `"sensor_tsfile$config"` | schema、精度、watermark 和目录配置 | +| OK | 本次文件检查通过 | +| MISSING | 确认文件是否被移动或删除，恢复登记路径下的原文件 | +| CORRUPT | 文件无法打开或解析，检查读取权限及文件是否完整 | +| MISMATCH | 文件与登记时不一致，核对是否被替换，恢复原始文件 | +| UNREGISTERED | 自有目录中有未登记 TsFile，先核对来源，不要直接将其当作表数据或删除 | -名字必须统一做标识符转义。建表前检查全部派生名；任何同名对象都使创建失败并明确 -报告冲突，不覆盖或复用用户对象。符号用于提高辨识度，不能被当作绝不撞名的保证。 -最后一个下划线之前保留完整逻辑表名，以维持 SQLite 的 shadow 表识别关系。 +verify 只检查和报告，不修复、注册或删除文件。它也不会扫描外部源文件旁边的其他 +文件。检查包括文件特征和元数据，但不逐页解码全部数据。 -这些名字属于内部实现，不作为业务 API。日常诊断通过公开状态接口完成；用户能够 -在 schema 中查看内部对象，但不应直接修改它们。绕过虚拟表写入会破坏 watermark、 -文件登记和实际文件之间的一致性。 +### 加载扩展或建表失败 -热行使用 SQLite 的正 rowid；冷行使用扩展生成的负 rowid。冷 rowid 是内部 -实现标识，不应作为跨查询或跨版本稳定的业务主键。 +加载报告 not authorized 时，确认 SQLite 支持扩展加载；应用连接需要先启用加载。 +找不到 libtsfile 时，检查扩展和共享库是否来自同次构建并位于同一目录。 -## 10. 数据目录和生命周期 +建表失败时，核对绝对路径、源文件内部表名和时间精度。新可写表应使用空或不存在的 +独占目录，不能与另一张表的目录重合或嵌套。引用文件时不要同时声明列；从空表开始 +时则要声明列、目录和精度。 -每张逻辑表必须使用独占目录。扩展会在 seal 前清理目录中未被 manifest 引用的 -`.tmp` 和 `.tsfile` 文件，用来恢复进程崩溃后遗留的孤儿文件。因此不要把手工 -创建的 TsFile、其他表的段文件或任何同后缀文件放进该目录。 +### 查看建表语句时为什么没有列定义 -执行： +`sqlite_schema.sql` 保存的是创建虚拟表时的 SQL，文件建表不会在这里展开推断列。 +使用 `PRAGMA table_info(表名)` 查看实际结构。较旧 SQLite 若不识别 sqlite_schema， +可以使用兼容名称 sqlite_master。 -```sql -DROP TABLE sensor; -``` +## 7. 关闭、重开和管理数据文件 -会删除虚拟表及其三个 shadow table，但不会删除已经导出的 TsFile。删除或归档 -这些文件需要由运维流程显式完成。 +退出 SQLite 后，重新打开同一个数据库并加载扩展，即可继续使用持久表。列结构、 +热数据和写入边界会保留，不需要重复 CREATE。使用 temp 创建的表随连接消失，其 +热数据也会丢弃，已经生成或引用的 TsFile 则保留。 -## 11. TsFile 导出、导入与文件校验（功能设计草案） +保留 SQLite 数据库、源文件和各表的自有段目录。不要单独移动或改写仍被表引用的 +文件，也不要把目录中新出现的文件当作自动加入表的数据。 -本节定义面向标准 TsFile 的交换和查询接口。导出产物只有 `.tsfile` 文件，不导出 -SQLite 数据库、热表、WAL、shadow 表或独立的 JSON 清单，也不承诺恢复源表的可写状态。 -导入接口及只读语义在 11.2 节定义。 +可写目录中的 `.tsfile-owner` 记录归属，绑定数据库路径及表名。复制或移动 SQLite +数据库后，不能直接复用原目录继续写入；迁移前应规划数据和路径的处理。不要直接 +修改名称含 `_tsfile$` 的内部表来改路径或改数据。 -### 11.1 仅导出 TsFile +确认不再需要某张逻辑表时，才执行 `DROP TABLE 表名`。这会删除该表的 SQLite 热数据 +和登记，保留外部源文件、已封存文件及目录归属标记。后续归档或删除文件之前，先确认 +没有其他表还在引用它们。 -```sql -SELECT tsfile_export('main.sensor', '/export/sensor-001'); -``` - -`tsfile_export(table_name, output_directory)` 导出指定逻辑表已经落到 TsFile 的 -数据，成功返回产出的文件数。SQLite 中尚未封存的热数据不在导出范围内。 -如需导出这些热数据，调用方先显式 seal 到选定 cutoff，提交后再执行 export。 -export 本身不封存、不修改源数据、不推进 watermark。 - -目标目录必须为绝对路径且尚不存在，并且不得与源数据目录重合或互相嵌套。 -输出示例： - -```text -sensor-001/ - part-000001.tsfile - part-000002.tsfile -``` - -- 表名、列类型和类别从 TsFile 自身元数据读取；可用的时间精度放在文件 Properties。 -- 输出文件只包含所选逻辑表的数据。如果输入段同时含其他表，需要导出所选表为独立 - TsFile，不能直接复制而意外带出其他表。文件内部表名使用该逻辑表的导出名称。 -- 文件可由标准 TsFile Reader 独立读取，不依赖本扩展的 JSON 清单或 SQLite 文件。 -- 没有已封存数据时返回 0，目标目录为空；不会制造可恢复空表或热数据的假象。 - -第一版采用保守的一致性方式：取得源 SQLite 数据库的写保留锁，在同一事务视图内 -固定该表的文件登记集合，复制或重写完成后释放锁。期间阻塞该数据库的其他写入。 -对外部只读文件也要检查读取错误和文件变化；外部修改不属于 SQLite 锁的保护范围。 - -先在目标旁的专用暂存目录生成文件，校验 footer、schema 和已知精度，完成文件同步， -再原子发布整个目录并同步父目录。失败不发布完整目标，源文件不变。 -仅允许独立管理调用，不接受用户已有的显式事务；成功返回时输出目录已发布。 - -### 11.2 扫描目录并建立只读查询表 - -```sql -SELECT tsfile_import('/data/archive'); -``` - -`tsfile_import(directory [, target_schema])` 默认把发现的表注册到 `main`；可显式 -指定一个已经存在的 SQLite schema。成功返回新注册的逻辑表数量。 - -```sql -SELECT tsfile_import('/data/archive', 'archive'); -``` - -这里的导入指登记外部 TsFile 并建立查询入口：默认直接引用原文件，不复制、移动、 -重写文件，也不把行装入 SQLite 热表。源目录必须是可持续访问的绝对路径，文件需 -由调用方保持不可变。SQLite 中只保存查询所需的文件登记和 schema 元数据。 - -扫描与分组规则： - -1. 第一版扫描指定目录当前层的普通 `.tsfile` 文件，不递归子目录，不跟随符号链接； - 非 TsFile 文件忽略，扩展名匹配但无法读取的文件使本次导入失败并报告路径。 -2. 从每个文件的元数据枚举全部表名；一个文件包含多张表时分别登记，不按文件名猜 - 表名。同一张表出现在多个文件中时，组成同一个只读逻辑表。 -3. 同名表要求列定义、类型、类别和顺序兼容；第一版要求完全一致，不自动补列、 - 类型提升或统一不同 schema。按 SQLite 标识符比较产生的大小写冲突应明确报错。 -4. 接受普通 TsFile，不要求来自本扩展，也不要求携带私有快照清单。时间精度属性 - 存在时读取；缺失时标记为 unknown 并按原始整数时间查询，不默认为 ms。 - 同名表的已知精度必须一致；已知/未知混合也拒绝自动合并，避免混淆时间单位。 -5. 同名表跨文件的结果按 `UNION ALL` 语义读取，重叠时间和重复逻辑键保留，不自动 - 覆盖、去重或取最新值。只读导入不对外部数据强加可写 hybrid 表的唯一键约束。 -6. 保存“SQLite 逻辑表 → 文件路径集合 → 各文件内部表名”的映射，所有路径和表名 - 都按数据处理并正确转义。文件内部表名不会因 SQL 名称变化而被重写。 - -用户通过普通 SQL 查询，例如目录内包含 sensor 和 meter 两个表时： - -```sql -SELECT time, device, temperature FROM sensor ORDER BY time; -SELECT count(*) FROM meter; -``` - -所有导入表默认且固定为只读：拒绝 INSERT、UPDATE、DELETE 和 seal，即使当前表为空 -也不接受写入。只读表不创建可写热区，不恢复源库 watermark，也不因为时间较新就 -自动允许修改。持续追加写入仍使用另行创建的可写 hybrid 表。 - -注册时先完整枚举、验证并建立映射，再在同一个 SQLite 事务中发布所有表。目标 -schema 中任何同名用户表、虚拟表或所需内部对象冲突，都使整批失败；不覆盖、不 -合并到已存在的表。对同一目录重复调用也遵循此规则，不会重复追加登记。 - -导入要求独立管理调用，不能嵌入已有用户显式事务。取得目标写锁后再次检查对象 -冲突；失败回滚本次新建的所有登记，源文件保持不变。空目录或未发现表时返回 0。 -本次扫描后新加入目录的文件不会自动进入已注册表，第一版不提供后台监控或刷新； -如需重新登记，可删除相关只读逻辑表后再导入，删除逻辑表不会删除外部文件。 - -### 11.3 文件所有权、移动与校验 - -```sql -SELECT * FROM tsfile_verify('main.sensor'); -``` - -`tsfile_verify(table_name)` 是只读表值接口，返回 -`segment_id, path, status, detail`。状态包括 `OK`、`MISSING`、`CORRUPT`、 -`MISMATCH` 和 `UNREGISTERED`。导入时建立文件指纹，verify 对比文件内容、schema -和可用精度；普通文件不需要预先存储本扩展的校验属性。 - -- 自有 hybrid 段与外部只读引用必须有明确的所有权区别。导入不取得源文件的删除权， - DROP TABLE、seal 清理和失败恢复都不得删除外部引用文件。 -- 文件被移走或删除时，访问该文件的查询失败并报告表名、文件标识和原路径，不能 - 静默跳过；verify 可集中报告问题，不承诺持续监控或自动搜索新路径。 -- 同路径文件被替换或修改时，完整 verify 对比导入指纹并报告差异；普通查询的轻量 - 检查不宣称能够发现所有字节修改。调用方必须保证登记后外部文件保持不可变。 -- 外部目录新增文件不会自动导入；verify 报告发现的未登记 TsFile，且不删除它们。 - 自有目录中的未知文件也不能仅凭 `.tsfile` 后缀被孤儿清理删除。 -- 复制到其他位置且保留源文件不影响已有查询；移动目录后应在保留源数据的前提下 - 重新建立登记，不直接改写内部表路径。 - -导出目录只包含 TsFile，导入后得到只读数据集；这一过程不是恢复完整 SQLite 数据库 -或可写 hybrid 状态的备份协议。 - -### 11.4 验收场景 - -- 导出目录只含标准 `.tsfile`；热数据不被导出，watermark 不改变，无冷数据返回 0； -- 目录内多文件、多表能被完整发现，同表跨文件汇成一个只读表； -- 不带本扩展私有 Properties 的普通 TsFile 可以查询，缺失精度标为 unknown； -- 多文件重叠时间和重复键按 UNION ALL 保留；NULL TAG 与空字符串不混淆； -- schema 不兼容、精度冲突、表名冲突及损坏文件使批量导入失败且不留下部分注册； -- 对导入表执行 INSERT、UPDATE、DELETE、seal 均被拒绝，源文件字节保持不变； -- 文件移走、替换和新增分别被诊断为缺失、变化和未登记；verify 不修改任何文件； -- 删除只读逻辑表、导入失败及进程重启清理都不会删除外部文件； -- 对导出复制、文件同步、目录发布和导入登记提交注入故障，验证源数据不变及 - 目标没有部分可见的发布结果。 - -## 12. 常见问题 - -### 加载时报 `not authorized` 或扩展加载被禁用 - -确认 SQLite 构建允许 loadable extension，并在应用连接上调用 -`sqlite3_enable_load_extension()`。CLI 使用 `.load` 即可。 - -### 加载时报找不到 `libtsfile` - -把当前构建对应的 `libtsfile` 与 `tsfile_sqlite` 放到同一目录，避免混用不同 -版本的库。必要时检查平台动态链接器的搜索路径。 - -### 创建表时报目录错误 - -`directory` 必须是绝对路径，父目录必须可创建或可写。连接已有数据库时，该 -目录必须仍然存在。 - -### seal 后无法更新某些行 - -这是 watermark 的预期行为。任何 `time < watermark` 的数据已经进入不可变 -冷区。 - -### 查询没有固定顺序 - -虚拟表会合并多个来源，但不承诺自然顺序。需要顺序时使用显式 `ORDER BY`。 - -## 13. MVP 限制 - -- seal 只能显式、同步执行； -- 冷数据不可更新或删除； -- 不支持后台自动封存、compaction 和冷段删除； -- 不支持原地 schema 演进； -- 查询游标当前会在内存中汇集冷热结果； -- 不跨冷热来源下推排序和 `LIMIT/OFFSET`； -- Windows 尚未支持； -- 这仍是实验性扩展，接口和内部格式可能继续演进。 +当前版本不自动迁移早期原型数据库，也不支持原地修改列结构或把只读表切换为可写表。 +查询和导出会在内存中收集数据，导出还会重写完整表；处理大表前应按实际数据量评估 +内存和执行时间。封存与导出均由调用方主动执行，没有后台定时封存。
diff --git a/cpp/src/sqlite/USER_GUIDE_EN.md b/cpp/src/sqlite/USER_GUIDE_EN.md new file mode 100644 index 0000000..94581b0 --- /dev/null +++ b/cpp/src/sqlite/USER_GUIDE_EN.md
@@ -0,0 +1,487 @@ +<!-- + + Licensed to the Apache Software Foundation (ASF) under one + or more contributor license agreements. See the NOTICE file + distributed with this work for additional information + regarding copyright ownership. The ASF licenses this file + to you under the Apache License, Version 2.0 (the + "License"); you may not use this file except in compliance + with the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, + software distributed under the License is distributed on an + "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + KIND, either express or implied. See the License for the + specific language governing permissions and limitations + under the License. + +--> + +# SQLite + TsFile user manual + +Use `tsfile_sqlite` to query existing TsFile data with SQL, or collect new data and +save it as TsFile. New rows stay in SQLite, where you can update or delete them. +Sealing moves them into TsFile: they remain queryable but can no longer be changed. + +This manual walks through creating a table, writing and querying rows, exporting, +and reopening the exported data. It then covers daily operations and troubleshooting. +Examples use the SQLite CLI; applications can execute the same SQL. +[中文](USER_GUIDE.md) · [Technical report](TECHNICAL_GUIDE_EN.md) + +## 1. Prepare your environment + +Use Linux or macOS with SQLite 3.31 or newer and extension loading enabled. You +need `tsfile_sqlite` and the shared `libtsfile` from the same build. If you already +have them, proceed to the tutorial. This extension supports TsFile table-model files. + +To build from source, run from the repository root: + +```bash +cmake -S cpp -B cpp/build/sqlite \ + -DBUILD_SQLITE_EXTENSION=ON \ + -DTSFILE_BUILD_SHARED=ON \ + -DBUILD_TEST=ON +cmake --build cpp/build/sqlite --target tsfile_sqlite -j +``` + +The libraries are produced in `cpp/build/sqlite/lib`. The extension is +`tsfile_sqlite.so` on Linux and `tsfile_sqlite.dylib` on macOS. Keep it alongside +`libtsfile` when deploying. + +Apple SDK SQLite headers disable extension loading. For Homebrew SQLite, use this +configuration command, then the build command above: + +```bash +cmake -S cpp -B cpp/build/sqlite \ + -DBUILD_SQLITE_EXTENSION=ON \ + -DTSFILE_BUILD_SHARED=ON \ + -DBUILD_TEST=ON \ + -DSQLite3_INCLUDE_DIR="$(brew --prefix sqlite)/include" \ + -DSQLite3_LIBRARY="$(brew --prefix sqlite)/lib/libsqlite3.dylib" +``` + +The CLI must also support loading extensions. Start Homebrew's CLI with +`"$(brew --prefix sqlite)/bin/sqlite3"`. + +## 2. Complete your first example + +Follow these steps in order to create a SQLite database and a standalone TsFile. +Use a new directory for each run; the examples use `/tmp/tsfile-demo`. If it already +exists, choose another name and replace that path throughout the examples. Use +persistent storage for production data. + +### Open a database and load the extension + +In your terminal: + +```bash +mkdir /tmp/tsfile-demo +sqlite3 /tmp/tsfile-demo/demo.db +``` + +In SQLite, substitute the absolute path to your built extension: + +```sql +.load /absolute/path/to/tsfile_sqlite +.headers on +.mode column +``` + +Load the extension on every new connection. You can include its `.so` or `.dylib` +suffix in the path. + +### Create a table and write rows + +```sql +CREATE VIRTUAL TABLE sensor USING tsfile_hybrid( + time TIMESTAMP TIME, + device STRING TAG, + temperature DOUBLE FIELD, + directory='/tmp/tsfile-demo/sensor-segments', + timestamp_precision='ms' +); + +INSERT INTO sensor VALUES + (1000, 'd1', 21.5), + (2000, 'd1', 22.0), + (3000, 'd2', 19.0); +``` + +Here time is a millisecond timestamp, device identifies a device, and temperature +holds a measurement. The extension creates the dedicated directory used for future +sealed data; it must be absent or empty when the table is created. These three rows +are initially mutable SQLite data. + +### Query and correct a value + +```sql +SELECT time, device, temperature FROM sensor ORDER BY time; +``` + +Expected result: + +```text +time device temperature +1000 d1 21.5 +2000 d1 22.0 +3000 d2 19.0 +``` + +Correct the second measurement and read it back: + +```sql +UPDATE sensor SET temperature=22.5 WHERE time=2000 AND device='d1'; +SELECT temperature FROM sensor WHERE time=2000 AND device='d1'; +``` + +The result is `22.5`. You can also use ordinary DELETE on rows that have not been sealed. + +### Export to TsFile + +```sql +SELECT tsfile_export('main.sensor', '/tmp/tsfile-demo/export-001'); +``` + +The result is `1`, for the generated file: + +```text +/tmp/tsfile-demo/export-001/part-000001.tsfile +``` + +Export automatically seals all three current rows and exports the complete table; +no preceding seal call is needed. This is an independent copy readable by a +standard TsFile Reader. The original table still returns the same rows, but they +are now sealed and cannot be updated or deleted. + +Check the remaining mutable rows and the minimum allowed time for new writes: + +```sql +SELECT hot_rows, watermark FROM tsfile_table_info('main.sensor'); +``` + +The result is `hot_rows=0`, `watermark=3001`. Future inserts need a timestamp of at +least 3001. In management calls, `main.sensor` means sensor in the current main +database; include the database qualifier when naming a table. + +### Query the exported file directly + +```sql +CREATE VIRTUAL TABLE temp.history USING tsfile_hybrid( + file='/tmp/tsfile-demo/export-001/part-000001.tsfile', + source_table='sensor' +); + +SELECT time, device, temperature FROM history ORDER BY time; +``` + +The result contains the same three rows, including the corrected `22.5`. No +directory was supplied, so history is query-only. The temp table disappears when +the connection closes; the file remains. + +There are no column declarations here: source_table selects the file's sensor +table and the extension reads its schema. The SQLite name history can differ +from the table name inside the file. + +### Continue writing from that file + +```sql +CREATE VIRTUAL TABLE continued USING tsfile_hybrid( + file='/tmp/tsfile-demo/export-001/part-000001.tsfile', + source_table='sensor', + directory='/tmp/tsfile-demo/continued-segments' +); + +INSERT INTO continued VALUES (4000, 'd1', 23.0); +SELECT time, device, temperature FROM continued ORDER BY time; +``` + +The query returns four rows. The first three are read from the original TsFile; +the new row at 4000 is mutable SQLite hot data. The source file and history's +query results remain unchanged. + +The file's maximum time is 3000, so new rows in continued must be strictly later +than 3000, even for a different device. Providing a directory gives this table +its own storage for subsequent sealing. + +## 3. Use your own data + +### Start collecting into an empty table + +Adapt the sensor example with your column names, dedicated directory and actual +time unit. Declare each column as `name TYPE CATEGORY`: the first is +`TIMESTAMP TIME`, identifiers are `STRING TAG`, and measurements are FIELD columns. + +You can have multiple TAG columns or none. For a single reading at each timestamp: + +```sql +CREATE VIRTUAL TABLE readings USING tsfile_hybrid( + time TIMESTAMP TIME, + value DOUBLE FIELD, + directory='/tmp/tsfile-demo/readings-segments', + timestamp_precision='ms' +); +``` + +readings accepts one new row per timestamp; sensor distinguishes new rows by +`(device, time)`. Use double quotes for names containing spaces, such as +`"sensor value" DOUBLE FIELD`. Names cannot differ only by ASCII letter case. +Every column needs an explicit TIME, TAG or FIELD category. + +### Open an existing TsFile + +Confirm its absolute path and internal table name, then follow the history or +continued example. Omit directory for query-only use; provide a new dedicated +directory to append rows. Each creation selects one table in one file. It does +not guess the table name from the filename or include other tables automatically. + +Do not add column declarations when opening a file. Inspect the inferred columns: + +```sql +PRAGMA table_info(continued); +``` + +The tutorial shows `time INTEGER`, `device TEXT` and `temperature REAL`. Use these +names in your queries. Ordinary TsFile sources expose the implicit time column as +time; files generated by this extension also preserve a custom TIME column name. + +A file without precision metadata can be queried with precision unknown. To +append, first determine its actual time unit and add `timestamp_precision='ms'`, +`'us'` or `'ns'` to the creation arguments. If the file already records a unit, it +is inherited; any explicit option must match. + +Keep source paths and contents unchanged. New rows go into SQLite, without +modifying or appending to the external file. Replacing that file does not refresh +the registered table. + +### Supply the right values and time unit + +Timestamps are stored as integers without automatic conversion. If your input is +in seconds and the table uses ms, convert the input to milliseconds before writing. + +| Declared type | Write value | +| --- | --- | +| BOOLEAN | Integer; zero is false, nonzero true; read as 0 or 1 | +| INT32, DATE | Integer within signed int32 range | +| INT64, TIMESTAMP | Integer within signed int64 range | +| FLOAT, DOUBLE | Integer or real number | +| STRING, TEXT | Text | +| BLOB | Binary value, preserving length and zero bytes | + +TIME cannot be NULL. TAG and FIELD values can be NULL; TAG columns must be STRING. +New rows with the same complete TAG combination and time conflict. For uniqueness, +two NULLs in the same key position count as equal, so NULL cannot bypass the key +check. NULL, empty text and the literal text `'null'` are distinct. Use IS NULL +when querying a NULL value. + +Existing duplicate rows in a source file are returned unchanged, without automatic +deduplication when the table is created. + +## 4. Query, edit and seal during daily use + +### Query with ordinary SQL + +Query the logical table regardless of where rows are stored. Conditions, joins, +aggregates and sorting work through SQLite. After completing the tutorial: + +```sql +SELECT device, avg(temperature) AS avg_temperature +FROM continued +WHERE time >= 1000 AND time < 5000 +GROUP BY device +ORDER BY device; +``` + +Specify ORDER BY when order matters. Time ranges and device/TAG conditions can +reduce scanning. Do not use implicit rowids as persistent business keys; sealing +and later queries can change them. + +### Write or correct a batch + +```sql +BEGIN; +INSERT INTO continued VALUES (5000, 'd2', 20.0); +UPDATE continued SET temperature=23.5 WHERE time=4000 AND device='d1'; +COMMIT; +``` + +Use ROLLBACK instead of COMMIT to cancel the batch. Hot writes also support +savepoints. Locate rows precisely using their time and TAG values. + +If an UPDATE or DELETE actually targets any sealed row, the entire statement fails, +including changes to other hot rows. IGNORE and FAIL do not bypass this rule. +Reading cold data remains allowed. + +### Freeze history before exporting + +When data before a chosen time no longer needs correction, seal it explicitly: + +```sql +SELECT tsfile_seal('main.continued', 4500); +``` + +Following this manual in order, the result is `1`: the row at 4000 is sealed and +the row at 5000 stays hot. The cutoff excludes 4500 itself. New rows now need a time +of at least 4500, and queries continue returning the same data. + +A larger cutoff advances the write boundary even when no hot rows qualify. Choose +it according to your late-arrival and correction window. Repeating the current +boundary returns zero; using an earlier boundary fails. + +Seal is synchronous and can participate in an explicit transaction: + +```sql +BEGIN IMMEDIATE; +SELECT tsfile_seal('main.continued', 5001); +ROLLBACK; +``` + +This rollback undoes the seal, leaving the row at 5000 hot. Use COMMIT to retain +it. A successful seal call inside a transaction is not durable until that outer +transaction commits. + +## 5. Export and deliver data + +Whenever you need a complete independent data copy, call export with a new output +directory: + +```sql +SELECT tsfile_export('main.continued', '/tmp/tsfile-demo/export-002'); +``` + +Commit or roll back any current transaction first, then execute this SELECT on +its own. Invoke export and seal as standalone calls, outside row queries, views, +triggers or other expressions. + +Export includes the external history, previously sealed rows and all current hot +rows in its captured data view. It automatically seals those hot rows, so recent +writes are included. Writes arriving after the automatic seal commits are left +for the next export. A read-only file table can also export its selected table. + +A nonempty table produces one `part-000001.tsfile` and returns 1; an empty table +produces an empty directory and returns 0. The file's internal table name is the +current logical name, continued in this example. Tell recipients this name so they +can select it with source_table. Known time precision is preserved. + +The output's parent directory must exist. Keep output outside the table's owned +segment directory and do not let it contain or replace a source file. It can be a +sibling of an external file. Existing output paths are never overwritten. +Deleting an export does not affect the original table. However, keep the export +if another table references it, as history does in the tutorial. + +After export, captured hot rows are frozen and future writes must be later than +their maximum time. Finish any necessary corrections before exporting. + +### Recover from an export failure + +Read the error and check hot_rows and watermark through tsfile_table_info: + +| State reported by the error | Next step | +| --- | --- | +| Path validation failed or automatic seal did not commit | Fix paths, permissions or files and retry; no seal changes were committed | +| Automatic seal committed but output failed | Data remains readable but is sealed; fix the output issue and retry with a new directory | +| Complete output published but parent-directory sync failed | Inspect the target and files first; use another directory if exporting again | + +A failure does not make committed cold rows mutable again. An interrupted process +may leave a temporary directory containing `.tsfile-export-` in its name; do not +treat it as a completed delivery. + +Export saves table data, not the complete SQLite database, business configuration +or hot/cold state. Empty exports do not preserve a table definition. Export cannot +replace a complete application database backup. + +## 6. Check status and troubleshoot + +### Determine what can still be written + +```sql +SELECT mode, hot_rows, watermark, append_available, timestamp_precision +FROM tsfile_table_info('main.continued'); +``` + +hot_rows counts mutable rows; watermark is the minimum new timestamp. Mode readonly +means query-only. append_available=1 means later timestamps remain representable, +but writes must still satisfy other constraints. This describes the current view; +the actual write result is authoritative. + +At INT64_MAX, append_available becomes 0 and watermark NULL when time space is +exhausted. Existing data remains readable and exportable. An explicit half-open +int64 seal cutoff cannot cover a hot row at INT64_MAX; automatic export sealing can. + +### An INSERT, UPDATE or DELETE fails + +Check mode and watermark first. A file table created without directory is +read-only; create another named table with a new directory to append data. A write +with no target rows may succeed as a no-op, which does not make a read-only table +writable. + +For writable tables, new time must be at least watermark. For external history, +this means strictly after the source maximum; historical gaps cannot be backfilled. +Then check for a duplicate `(all TAGs, time)` key, NULL TIME, wrong types or +out-of-range values. For UPDATE/DELETE, also confirm the target rows are not sealed. + +### A source is missing or queries report file problems + +```sql +SELECT path, status, detail FROM tsfile_verify('main.continued'); +``` + +Use the reported paths to investigate: + +| Status | Action | +| --- | --- | +| OK | The current file checks passed | +| MISSING | Check whether the file moved or was deleted; restore the original at its registered path | +| CORRUPT | The file cannot be opened or parsed; check read permissions and completeness | +| MISMATCH | The file differs from registration; investigate replacement and restore the original | +| UNREGISTERED | Investigate the unregistered TsFile's origin before treating it as data or deleting it | + +Verify reports only. It does not repair, register or delete files, or inspect other +files beside an external source. It checks file identity and metadata, without +decoding every data page. + +### Loading or creating a table fails + +For not authorized, check SQLite extension-loading support; applications must +enable loading on the connection first. For a missing libtsfile, verify that it +and the extension are from the same build and are deployed together. + +For creation failures, check absolute paths, the file-internal table name and +precision. Use an empty or absent directory dedicated to this table, without +overlapping or nesting another table's directory. File-based creation has no +explicit column declarations; empty-table creation needs columns, directory and +precision. + +### The saved creation SQL has no column definitions + +sqlite_schema.sql stores the virtual-table creation SQL and does not expand +inferred columns. Use `PRAGMA table_info(table_name)` to inspect the actual schema. +On older SQLite versions, sqlite_master is the compatible name for sqlite_schema. + +## 7. Reopen tables and manage their files + +Reopen the same database and load the extension to use persistent tables again. +Their columns, hot rows and write boundaries are retained; do not repeat CREATE. +Temporary tables and their hot rows disappear when their connection closes, while +referenced and generated TsFile files remain. + +Retain the SQLite database, external source files and each table's segment directory. +Do not move or rewrite files still referenced by a table. New files appearing in a +directory are not automatically added to query results. + +The `.tsfile-owner` marker binds a writable directory to the database path and +table name. A copied or moved SQLite database cannot simply reuse the original +directory for writes; plan data and path handling before moving a deployment. +Do not modify internal tables containing `_tsfile$` to change paths or data. + +Use DROP TABLE only when the logical table is no longer needed. It removes SQLite +hot rows and registration, while retaining external files, sealed files and the +directory marker. Check that no other tables reference these files before later +archiving or deleting them. + +The current version does not automatically migrate prototype databases or support +changing columns or switching read-only tables to writable in place. Queries and +exports collect rows in memory, and export rewrites the full table. Evaluate memory +and runtime with representative data before using large tables. Sealing and export +are caller-initiated; there is no background sealing schedule.
diff --git a/cpp/src/sqlite/tsfile_sqlite.cc b/cpp/src/sqlite/tsfile_sqlite.cc index 8dd5f0c..befaa13 100644 --- a/cpp/src/sqlite/tsfile_sqlite.cc +++ b/cpp/src/sqlite/tsfile_sqlite.cc
@@ -26,6 +26,10 @@ #include <sys/stat.h> #include <sys/types.h> #include <unistd.h> +#ifndef __APPLE__ +#include <linux/fs.h> +#include <sys/syscall.h> +#endif #include <algorithm> #include <cctype> @@ -33,7 +37,9 @@ #include <cstdlib> #include <cstring> #include <limits> +#include <map> #include <memory> +#include <set> #include <sstream> #include <string> #include <utility> @@ -96,6 +102,10 @@ }; struct HybridTable; +struct Connection { + std::map<std::string, HybridTable*> tables; + bool managing = false; +}; struct HybridCursor : sqlite3_vtab_cursor { HybridTable* table = nullptr; @@ -105,17 +115,28 @@ struct HybridTable : sqlite3_vtab { sqlite3* db = nullptr; + Connection* connection = nullptr; std::string db_name; std::string table_name; std::string directory; std::string precision; + std::string source_file; + std::string source_table; + std::string source_identity; + bool readonly = false; + bool exhausted = false; + bool source_has_rows = false; + int64_t source_max = kNoWatermark; std::vector<Column> columns; int time_index = -1; std::vector<int> tag_indexes; int64_t watermark = kNoWatermark; std::vector<PendingFile> pending; - std::vector<size_t> savepoint_marks; + std::map<int, size_t> savepoint_marks; uint64_t file_counter = 0; + bool new_directory_owner = false; + bool uncommitted_create = false; + std::vector<std::string> created_directories; }; struct ConstraintSpec { @@ -180,17 +201,54 @@ return rc; } -bool parse_key_value(const char* arg, std::string& key, std::string& value) { - if (arg == nullptr) return false; - const char* equal = std::strchr(arg, '='); - if (equal == nullptr) return false; - key.assign(arg, static_cast<size_t>(equal - arg)); - value.assign(equal + 1); - if (value.size() >= 2 && ((value.front() == '\'' && value.back() == '\'') || - (value.front() == '"' && value.back() == '"'))) { - value = value.substr(1, value.size() - 2); +std::string trim(const std::string& value) { + size_t a = value.find_first_not_of(" \t\r\n"); + if (a == std::string::npos) return ""; + return value.substr(a, value.find_last_not_of(" \t\r\n") - a + 1); +} + +bool sql_token(const std::string& input, size_t& pos, std::string& token) { + while (pos < input.size() && + std::isspace(static_cast<unsigned char>(input[pos]))) + ++pos; + token.clear(); + if (pos == input.size()) return false; + char quote = input[pos]; + if (quote == '\'' || quote == '"' || quote == '`' || quote == '[') { + char end = quote == '[' ? ']' : quote; + ++pos; + while (pos < input.size()) { + char c = input[pos++]; + if (c == end) { + if (pos < input.size() && input[pos] == end) { + token += end; + ++pos; + } else + return true; + } else + token += c; + } + return false; } - return true; + size_t start = pos; + while (pos < input.size() && + !std::isspace(static_cast<unsigned char>(input[pos])) && + input[pos] != '=') + ++pos; + token = input.substr(start, pos - start); + return !token.empty(); +} + +bool parse_key_value(const char* arg, std::string& key, std::string& value) { + std::string input = arg == nullptr ? "" : arg; + size_t pos = 0; + if (!sql_token(input, pos, key)) return false; + while (pos < input.size() && + std::isspace(static_cast<unsigned char>(input[pos]))) + ++pos; + if (pos == input.size() || input[pos++] != '=') return false; + if (!sql_token(input, pos, value)) return false; + return trim(input.substr(pos)).empty(); } bool parse_column(const std::string& spec, Column& column, std::string& error) { @@ -252,20 +310,96 @@ std::string shadow_name(const HybridTable* table, const char* suffix) { return quote_id(table->db_name) + "." + - quote_id(table->table_name + suffix); + quote_id(table->table_name + "_tsfile$" + + (std::string(suffix) == "_data" ? "hot" : suffix + 1)); +} + +std::string hot_rowid(const HybridTable* table) { + std::string name = "tsfile$rowid"; + for (;;) { + bool collision = false; + for (const auto& column : table->columns) + if (lower(column.name) == name) { + collision = true; + break; + } + if (!collision) return quote_id(name); + name += '$'; + } } std::string schema_signature(const HybridTable* table) { std::ostringstream out; - for (size_t i = 0; i < table->columns.size(); ++i) { - if (i) out << ';'; - out << table->columns[i].name << ':' - << static_cast<int>(table->columns[i].type) << ':' - << static_cast<int>(table->columns[i].category); - } + for (const auto& column : table->columns) + out << column.name.size() << ':' << column.name << ':' + << static_cast<int>(column.type) << ':' + << static_cast<int>(column.category) << ';'; return out.str(); } +bool restore_source_schema(HybridTable* table, std::string& error) { + sqlite3_stmt* stmt = nullptr; + std::string sql = + "SELECT schema,precision,source_max,source_file,source_table,mode " + "FROM " + + shadow_name(table, "_config") + " WHERE id=1"; + int rc = sqlite3_prepare_v2(table->db, sql.c_str(), -1, &stmt, nullptr); + if (rc != SQLITE_OK || sqlite3_step(stmt) != SQLITE_ROW) { + sqlite3_finalize(stmt); + error = "stored TsFile configuration is unavailable"; + return false; + } + auto text = [stmt](int i) { + const unsigned char* value = sqlite3_column_text(stmt, i); + return value ? std::string(reinterpret_cast<const char*>(value), + sqlite3_column_bytes(stmt, i)) + : std::string(); + }; + std::string signature = text(0), precision = text(1); + bool valid = text(3) == table->source_file && + text(4) == table->source_table && + text(5) == (table->readonly ? "readonly" : "writable") && + (table->precision.empty() || table->precision == precision); + table->precision = precision; + table->source_has_rows = sqlite3_column_type(stmt, 2) != SQLITE_NULL; + table->source_max = sqlite3_column_int64(stmt, 2); + sqlite3_finalize(stmt); + std::istringstream input(signature); + while (valid && input.peek() != std::char_traits<char>::eof()) { + size_t length = 0; + char delimiter = 0; + int type = 0, category = 0; + if (!(input >> length >> delimiter) || delimiter != ':' || + length > signature.size()) { + valid = false; + break; + } + std::string name(length, '\0'); + if (!input.read(&name[0], length) || !(input >> delimiter) || + delimiter != ':' || !(input >> type >> delimiter) || + delimiter != ':' || !(input >> category >> delimiter) || + delimiter != ';') { + valid = false; + break; + } + if (category != static_cast<int>(ColumnCategory::TIME) && + category != static_cast<int>(ColumnCategory::TAG) && + category != static_cast<int>(ColumnCategory::FIELD)) { + valid = false; + break; + } + table->columns.push_back({name, static_cast<TSDataType>(type), + static_cast<ColumnCategory>(category)}); + } + if (!valid || table->columns.empty()) { + error = + "stored TsFile schema is invalid or conflicts with creation " + "options"; + return false; + } + return true; +} + std::string file_stem(const std::string& table_name) { std::string stem = "tsfile"; for (char c : table_name) { @@ -277,22 +411,6 @@ return stem; } -bool ensure_directory(const std::string& path) { - if (path.empty() || path[0] != '/') return false; - size_t pos = 1; - while (pos <= path.size()) { - pos = path.find('/', pos); - std::string part = - path.substr(0, pos == std::string::npos ? path.size() : pos); - if (!part.empty() && mkdir(part.c_str(), 0755) != 0 && errno != EEXIST) - return false; - if (pos == std::string::npos) break; - ++pos; - } - struct stat st {}; - return stat(path.c_str(), &st) == 0 && S_ISDIR(st.st_mode); -} - bool directory_exists(const std::string& path) { struct stat st {}; return stat(path.c_str(), &st) == 0 && S_ISDIR(st.st_mode); @@ -306,6 +424,19 @@ return rc; } +int publish_without_replacing(const std::string& from, const std::string& to) { +#ifdef __APPLE__ + return renamex_np(from.c_str(), to.c_str(), RENAME_EXCL) == 0 + ? SQLITE_OK + : SQLITE_IOERR; +#else + return syscall(SYS_renameat2, AT_FDCWD, from.c_str(), AT_FDCWD, to.c_str(), + RENAME_NOREPLACE) == 0 + ? SQLITE_OK + : SQLITE_IOERR; +#endif +} + int close_writer_and_sync(WriteFile& write_file, TsFileTableWriter& writer) { // Keep a duplicate descriptor because TsFileTableWriter::close() writes // the footer and closes the original descriptor. This explicit fsync is @@ -338,7 +469,7 @@ error = "BOOLEAN requires INTEGER"; return false; } - out.b = sqlite3_value_int(value) != 0; + out.b = sqlite3_value_int64(value) != 0; break; case common::INT32: case common::DATE: @@ -485,49 +616,172 @@ return true; } -int create_shadow_tables(HybridTable* table, bool insert_config) { - std::ostringstream data; - data << "CREATE TABLE " << shadow_name(table, "_data") << " ("; - for (size_t i = 0; i < table->columns.size(); ++i) { - if (i) data << ','; - data << quote_id(table->columns[i].name) << ' ' - << sqlite_type(table->columns[i].type); - if (table->columns[i].category == ColumnCategory::TIME || - table->columns[i].category == ColumnCategory::TAG) - data << " NOT NULL"; +std::string file_identity(const std::string& path) { + int fd = open(path.c_str(), O_RDONLY); + if (fd < 0) return ""; + struct stat st {}; + if (fstat(fd, &st) != 0 || !S_ISREG(st.st_mode)) { + close(fd); + return ""; } - data << ", UNIQUE("; - bool first = true; - for (int index : table->tag_indexes) { - if (!first) data << ','; - first = false; - data << quote_id(table->columns[index].name); - } - data << ',' << quote_id(table->columns[table->time_index].name) << "))"; - int rc = exec_sql(table->db, data.str(), table); - if (rc != SQLITE_OK) return rc; + uint64_t hash = 14695981039346656037ULL; + unsigned char buffer[16384]; + ssize_t n; + while ((n = read(fd, buffer, sizeof(buffer))) > 0) + for (ssize_t i = 0; i < n; ++i) { + hash ^= buffer[i]; + hash *= 1099511628211ULL; + } + close(fd); + if (n < 0) return ""; + return std::to_string(st.st_size) + ":" + std::to_string(hash); +} +std::shared_ptr<TableSchema> source_schema(TsFileReader& reader, + const std::string& name) { + for (const auto& schema : reader.get_all_table_schemas()) + if (lower(schema->get_table_name()) == lower(name)) return schema; + return nullptr; +} + +bool infer_source(HybridTable* table, std::string& error) { + table->source_identity = file_identity(table->source_file); + TsFileReader reader; + if (table->source_identity.empty() || + reader.open(table->source_file) != common::E_OK) { + error = "cannot read source file: " + table->source_file; + return false; + } + auto schema = source_schema(reader, table->source_table); + if (!schema) { + error = "source table not found: " + table->source_table; + reader.close(); + return false; + } + auto properties = reader.get_tsfile_properties(); + std::string time_name = "time"; + auto time_property = properties.find("tsfile_sqlite.time_column"); + if (time_property != properties.end() && !time_property->second.is_null) + time_name.assign(time_property->second.value.begin(), + time_property->second.value.end()); + table->columns.push_back( + {time_name, common::TIMESTAMP, ColumnCategory::TIME}); + auto names = schema->get_measurement_names(); + auto fields = schema->get_measurement_schemas(); + auto categories = schema->get_column_categories(); + for (size_t i = 0; i < fields.size(); ++i) + table->columns.push_back( + {names[i], fields[i]->data_type_, categories[i]}); + auto precision = properties.find("tsfile_sqlite.timestamp_precision"); + if (precision != properties.end()) { + std::string known(precision->second.value.begin(), + precision->second.value.end()); + if (precision->second.is_null || + (known != "ms" && known != "us" && known != "ns") || + (!table->precision.empty() && table->precision != known)) { + error = "invalid or conflicting timestamp_precision: " + + table->source_file; + reader.close(); + return false; + } + table->precision = known; + } + if (table->precision.empty()) table->precision = "unknown"; + if (!reader.get_table_schema(table->source_table)) { + reader.close(); + return true; + } + ResultSet* result = nullptr; + int rc = reader.query(table->source_table, names, kNoWatermark, + std::numeric_limits<int64_t>::max(), result); + if (rc != common::E_OK) { + error = "cannot query source table"; + reader.close(); + return false; + } + bool next = false; + while ((rc = result->next(next)) == common::E_OK && next) { + table->source_has_rows = true; + table->source_max = + std::max(table->source_max, result->get_value<int64_t>(1)); + } + reader.destroy_query_data_set(result); + reader.close(); + if (rc != common::E_OK || + file_identity(table->source_file) != table->source_identity) { + error = "source file unreadable or changed during creation"; + return false; + } + if (!table->readonly && table->source_has_rows) { + table->exhausted = + table->source_max == std::numeric_limits<int64_t>::max(); + if (!table->exhausted) table->watermark = table->source_max + 1; + } + return true; +} + +int create_shadow_tables(HybridTable* table, bool insert_config) { + int rc = SQLITE_OK; + if (!table->readonly) { + std::ostringstream data; + data << "CREATE TABLE " << shadow_name(table, "_data") << " (" + << hot_rowid(table) << " INTEGER PRIMARY KEY"; + for (size_t i = 0; i < table->columns.size(); ++i) { + data << ','; + data << quote_id(table->columns[i].name) << ' ' + << sqlite_type(table->columns[i].type); + if (table->columns[i].category == ColumnCategory::TIME) + data << " NOT NULL"; + } + data << ')'; + rc = exec_sql(table->db, data.str(), table); + if (rc != SQLITE_OK) return rc; + std::ostringstream unique; + unique << "CREATE UNIQUE INDEX " << quote_id(table->db_name) << '.' + << quote_id(table->table_name + "_tsfile$key") << " ON " + << quote_id(table->table_name + "_tsfile$hot") << '('; + for (int index : table->tag_indexes) { + std::string name = quote_id(table->columns[index].name); + unique << '(' << name << " IS NULL),coalesce(" << name << ",''),"; + } + unique << quote_id(table->columns[table->time_index].name) << ')'; + rc = exec_sql(table->db, unique.str(), table); + if (rc != SQLITE_OK) return rc; + } std::ostringstream segments; segments << "CREATE TABLE " << shadow_name(table, "_segments") << " (path TEXT PRIMARY KEY, cutoff INTEGER NOT NULL, row_count " - "INTEGER NOT NULL)"; + "INTEGER NOT NULL, source_table TEXT NOT NULL, identity TEXT " + "NOT NULL)"; rc = exec_sql(table->db, segments.str(), table); if (rc != SQLITE_OK) return rc; std::ostringstream config; - config << "CREATE TABLE " << shadow_name(table, "_config") - << " (id INTEGER PRIMARY KEY CHECK(id=1), watermark INTEGER NOT " - "NULL, precision TEXT NOT NULL, directory TEXT NOT NULL, schema " - "TEXT NOT NULL)"; + config + << "CREATE TABLE " << shadow_name(table, "_config") + << " (id INTEGER PRIMARY KEY CHECK(id=1), watermark INTEGER, precision " + "TEXT NOT NULL, " + "directory TEXT NOT NULL, schema TEXT NOT NULL, mode TEXT NOT NULL, " + "source_file TEXT NOT NULL, source_table TEXT NOT NULL, source_max " + "INTEGER)"; rc = exec_sql(table->db, config.str(), table); if (rc != SQLITE_OK) return rc; if (insert_config) { std::ostringstream insert; insert << "INSERT INTO " << shadow_name(table, "_config") - << "(id,watermark,precision,directory,schema) VALUES(1," - << kNoWatermark << ',' << quote_sql(table->precision) << ',' + << " VALUES(1," + << (table->readonly || table->exhausted + ? "NULL" + : std::to_string(table->watermark)) + << ',' << quote_sql(table->precision) << ',' << quote_sql(table->directory) << ',' - << quote_sql(schema_signature(table)) << ')'; + << quote_sql(schema_signature(table)) << ',' + << quote_sql(table->readonly ? "readonly" : "writable") << ',' + << quote_sql(table->source_file) << ',' + << quote_sql(table->source_table) << ',' + << (table->source_has_rows ? std::to_string(table->source_max) + : "NULL") + << ')'; rc = exec_sql(table->db, insert.str(), table); if (rc != SQLITE_OK) return rc; } @@ -542,6 +796,8 @@ if (rc != SQLITE_OK) return rc; rc = sqlite3_step(stmt); if (rc == SQLITE_ROW) { + table->exhausted = + !table->readonly && sqlite3_column_type(stmt, 0) == SQLITE_NULL; table->watermark = sqlite3_column_int64(stmt, 0); const unsigned char* precision = sqlite3_column_text(stmt, 1); std::string stored_precision = @@ -576,43 +832,84 @@ if (i) sql << ','; sql << quote_id(table->columns[i].name) << ' ' << sqlite_type(table->columns[i].type); - if (table->columns[i].category == ColumnCategory::TIME || - table->columns[i].category == ColumnCategory::TAG) + if (table->columns[i].category == ColumnCategory::TIME) sql << " NOT NULL"; } - sql << ",_tsfile_command TEXT HIDDEN,_tsfile_cutoff INTEGER HIDDEN)"; + sql << ")"; return sqlite3_declare_vtab(table->db, sql.str().c_str()); } bool parse_args(HybridTable* table, int argc, const char* const* argv, - std::string& error) { + std::string& error, bool create) { + std::set<std::string> options; for (int i = 3; i < argc; ++i) { std::string key, value; if (!parse_key_value(argv[i], key, value)) { - error = "module arguments must be key=value"; - return false; + std::string input = argv[i], name, type, category; + size_t pos = 0; + if (!sql_token(input, pos, name) || !sql_token(input, pos, type) || + !sql_token(input, pos, category) || + !trim(input.substr(pos)).empty()) { + error = "column must be name TYPE CATEGORY"; + return false; + } + Column column; + if (!parse_column("placeholder:" + type + ":" + category, column, + error)) + return false; + column.name = name; + table->columns.push_back(column); + continue; } key = lower(key); + if (!options.insert(key).second) { + error = "duplicate option: " + key; + return false; + } if (key == "directory") table->directory = value; + else if (key == "file") + table->source_file = value; + else if (key == "source_table") + table->source_table = value; else if (key == "timestamp_precision") table->precision = lower(value); - else if (key == "column") { - Column column; - if (!parse_column(value, column, error)) return false; - table->columns.push_back(column); - } else { + else { error = "unknown tsfile_hybrid option: " + key; return false; } } - if (table->directory.empty() || table->directory[0] != '/') { + if (options.count("timestamp_precision") && table->precision != "ms" && + table->precision != "us" && table->precision != "ns") { + error = "timestamp_precision must be ms, us, or ns"; + return false; + } + table->readonly = options.count("file") && !options.count("directory"); + if (options.count("file")) { + if (table->source_file.empty() || table->source_file[0] != '/' || + table->source_table.empty() || !table->columns.empty()) { + error = + "file requires an absolute path, source_table, and inferred " + "columns"; + return false; + } + if (create ? !infer_source(table, error) + : !restore_source_schema(table, error)) + return false; + } else if (options.count("source_table")) { + error = "source_table requires file"; + return false; + } + if (!table->readonly && + (table->directory.empty() || table->directory[0] != '/')) { error = "directory must be an absolute path"; return false; } if (table->precision != "ms" && table->precision != "us" && - table->precision != "ns") { - error = "timestamp_precision must be ms, us, or ns"; + table->precision != "ns" && + !(table->readonly && table->precision == "unknown")) { + error = + "timestamp_precision must be ms, us, or ns for a writable table"; return false; } if (table->columns.empty() || @@ -622,13 +919,9 @@ return false; } for (size_t i = 0; i < table->columns.size(); ++i) { - if (table->columns[i].name.empty()) { - error = "column name cannot be empty"; - return false; - } - if (lower(table->columns[i].name) == "_tsfile_command" || - lower(table->columns[i].name) == "_tsfile_cutoff") { - error = "column name is reserved by tsfile_hybrid"; + if (table->columns[i].name.empty() || + sqlite_type(table->columns[i].type).empty()) { + error = "column name or type is unsupported"; return false; } for (size_t j = 0; j < i; ++j) @@ -655,39 +948,171 @@ error = "TIME column must be the first column"; return false; } - if (table->tag_indexes.empty()) { - error = "at least one TAG column is required"; - return false; - } return true; } +std::string directory_owner(const HybridTable* table) { + const char* filename = + sqlite3_db_filename(table->db, table->db_name.c_str()); + std::string database; + if (filename && filename[0]) { + char* resolved = realpath(filename, nullptr); + database = resolved ? resolved : filename; + free(resolved); + } else { + database = + "connection:" + + std::to_string(reinterpret_cast<uintptr_t>(table->connection)) + + ':' + table->db_name; + } + return "tsfile-sqlite:1\n" + database + '\n' + table->table_name + '\n'; +} + +int check_directory_owner(HybridTable* table) { + if (table->readonly) return SQLITE_OK; + int fd = open((table->directory + "/.tsfile-owner").c_str(), O_RDONLY); + if (fd < 0) { + set_error(table, "TsFile directory ownership record is missing"); + return SQLITE_CANTOPEN; + } + const std::string expected = directory_owner(table); + std::string actual(expected.size() + 1, '\0'); + ssize_t bytes = read(fd, &actual[0], actual.size()); + close(fd); + if (bytes != static_cast<ssize_t>(expected.size()) || + actual.compare(0, expected.size(), expected) != 0) { + set_error( + table, + "TsFile directory belongs to a different SQLite database or table"); + return SQLITE_CONSTRAINT; + } + return SQLITE_OK; +} + +void release_new_directory(HybridTable* table) { + if (table->new_directory_owner) + unlink((table->directory + "/.tsfile-owner").c_str()); + table->new_directory_owner = false; + for (auto it = table->created_directories.rbegin(); + it != table->created_directories.rend(); ++it) + rmdir(it->c_str()); + table->created_directories.clear(); +} + +int acquire_directory(HybridTable* table) { + if (table->readonly) return SQLITE_OK; + // Resolve existing ancestors before testing ownership so aliases and '..' + // cannot evade a parent's exclusive directory reservation. + std::string path = table->directory; + std::vector<std::string> missing; + char* resolved = nullptr; + while (!(resolved = realpath(path.c_str(), nullptr))) { + size_t slash = path.find_last_of('/'); + if (slash == std::string::npos || path.empty()) return SQLITE_CANTOPEN; + std::string component = path.substr(slash + 1); + if (component.empty() || component == "." || component == "..") + return SQLITE_CANTOPEN; + missing.push_back(component); + path = slash == 0 ? "/" : path.substr(0, slash); + } + std::string existing(resolved); + free(resolved); + for (std::string ancestor = existing; !ancestor.empty();) { + if (access((ancestor + "/.tsfile-owner").c_str(), F_OK) == 0) { + set_error(table, "directory is already owned by a TsFile table: " + + ancestor); + return SQLITE_CONSTRAINT; + } + size_t slash = ancestor.find_last_of('/'); + if (slash == std::string::npos || ancestor == "/") break; + ancestor = slash == 0 ? "/" : ancestor.substr(0, slash); + } + path = existing; + for (auto it = missing.rbegin(); it != missing.rend(); ++it) { + path += (path == "/" ? "" : "/") + *it; + if (mkdir(path.c_str(), 0755) != 0) return SQLITE_CANTOPEN; + table->created_directories.push_back(path); + } + DIR* dir = opendir(path.c_str()); + if (!dir) return SQLITE_CANTOPEN; + bool empty = true; + while (dirent* entry = readdir(dir)) + if (std::strcmp(entry->d_name, ".") && + std::strcmp(entry->d_name, "..")) { + empty = false; + break; + } + closedir(dir); + if (!empty) { + set_error(table, "new table directory must be empty: " + path); + return SQLITE_CONSTRAINT; + } + int fd = open((path + "/.tsfile-owner").c_str(), + O_WRONLY | O_CREAT | O_EXCL, 0600); + if (fd < 0) return SQLITE_CANTOPEN; + // This marker reserves the directory across connections and databases; + // it is never inferred from a filename suffix or another table's files. + std::string owner = directory_owner(table); + bool ok = write(fd, owner.data(), owner.size()) == + static_cast<ssize_t>(owner.size()); + ok = fsync(fd) == 0 && ok; + close(fd); + table->new_directory_owner = true; + if (!ok || sync_directory(path) != SQLITE_OK) return SQLITE_IOERR_FSYNC; + return SQLITE_OK; +} + int init_table(HybridTable* table, int argc, const char* const* argv, bool create, char** error_message) { + table->uncommitted_create = create; table->db_name = argv[1] == nullptr ? "main" : argv[1]; table->table_name = argv[2] == nullptr ? "" : argv[2]; std::string error; - if (!parse_args(table, argc, argv, error)) { + if (!parse_args(table, argc, argv, error, create)) { if (error_message) *error_message = sqlite3_mprintf("%s", error.c_str()); return SQLITE_ERROR; } - if (create && !ensure_directory(table->directory)) { - if (error_message) - *error_message = sqlite3_mprintf("cannot create TsFile directory"); - return SQLITE_CANTOPEN; + if (create) { + int rc = acquire_directory(table); + if (rc != SQLITE_OK) { + if (error_message) + *error_message = sqlite3_mprintf( + "%s", table->zErrMsg ? table->zErrMsg + : "cannot acquire TsFile directory"); + return rc; + } } - if (!create && !directory_exists(table->directory)) { + if (!create && !table->readonly && !directory_exists(table->directory)) { if (error_message) *error_message = sqlite3_mprintf("TsFile directory does not exist"); return SQLITE_CANTOPEN; } + if (!create) { + int rc = check_directory_owner(table); + if (rc != SQLITE_OK) { + if (error_message) + *error_message = sqlite3_mprintf( + "%s", table->zErrMsg ? table->zErrMsg + : "invalid directory owner"); + return rc; + } + } if (declare_table(table) != SQLITE_OK) return SQLITE_ERROR; sqlite3_vtab_config(table->db, SQLITE_VTAB_DIRECTONLY); sqlite3_vtab_config(table->db, SQLITE_VTAB_CONSTRAINT_SUPPORT, 1); if (create) { int rc = create_shadow_tables(table, true); if (rc != SQLITE_OK) return rc; + if (!table->source_file.empty()) { + rc = exec_sql(table->db, + "INSERT INTO " + shadow_name(table, "_segments") + + " VALUES(" + quote_sql(table->source_file) + + ",0,0," + quote_sql(table->source_table) + "," + + quote_sql(table->source_identity) + ")", + table); + if (rc != SQLITE_OK) return rc; + } } else { int rc = load_config(table); if (rc != SQLITE_OK) return rc; @@ -697,11 +1122,17 @@ int create_or_connect(sqlite3* db, void* aux, int argc, const char* const* argv, sqlite3_vtab** vtab, char** error_message, bool create) { - (void)aux; std::unique_ptr<HybridTable> table(new HybridTable()); table->db = db; + table->connection = static_cast<Connection*>(aux); int rc = init_table(table.get(), argc, argv, create, error_message); - if (rc != SQLITE_OK) return rc; + if (rc != SQLITE_OK) { + release_new_directory(table.get()); + return rc; + } + table->connection + ->tables[lower(table->db_name) + "." + lower(table->table_name)] = + table.get(); *vtab = table.release(); return SQLITE_OK; } @@ -717,6 +1148,8 @@ int xDisconnect(sqlite3_vtab* vtab) { HybridTable* table = static_cast<HybridTable*>(vtab); + table->connection->tables.erase(lower(table->db_name) + "." + + lower(table->table_name)); delete table; return SQLITE_OK; } @@ -734,6 +1167,9 @@ rc = exec_sql(table->db, "DROP TABLE IF EXISTS " + shadow_name(table, "_config"), table); + release_new_directory(table); + table->connection->tables.erase(lower(table->db_name) + "." + + lower(table->table_name)); delete table; return rc; } @@ -899,6 +1335,7 @@ const std::vector<std::pair<int, std::string>>& tag_eq, const std::vector<int>& projection) { HybridTable* table = cursor->table; + if (table->readonly) return SQLITE_OK; std::ostringstream sql; sql << "SELECT "; for (size_t i = 0; i < projection.size(); ++i) { @@ -906,7 +1343,7 @@ if (i) sql << ','; sql << quote_id(table->columns[column].name); } - sql << ",rowid FROM " << shadow_name(table, "_data"); + sql << ',' << hot_rowid(table) << " FROM " << shadow_name(table, "_data"); if (!specs.empty()) { sql << " WHERE "; for (size_t i = 0; i < specs.size(); ++i) { @@ -934,7 +1371,7 @@ } } } - sql << " ORDER BY rowid"; + sql << " ORDER BY " << hot_rowid(table); sqlite3_stmt* stmt = nullptr; int rc = sqlite3_prepare_v2(table->db, sql.str().c_str(), -1, &stmt, nullptr); @@ -1044,8 +1481,8 @@ const std::vector<std::pair<int, std::string>>& tag_eq, const std::vector<int>& projection) { HybridTable* table = cursor->table; - std::string sql = "SELECT path FROM " + shadow_name(table, "_segments") + - " ORDER BY path"; + std::string sql = "SELECT path,source_table,identity FROM " + + shadow_name(table, "_segments") + " ORDER BY path"; sqlite3_stmt* stmt = nullptr; int rc = sqlite3_prepare_v2(table->db, sql.c_str(), -1, &stmt, nullptr); if (rc != SQLITE_OK) return rc; @@ -1061,12 +1498,33 @@ while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) { const unsigned char* path = sqlite3_column_text(stmt, 0); if (path == nullptr) continue; + std::string read_path = reinterpret_cast<const char*>(path); + std::string selected = + reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1)); + std::string identity = + reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2)); + for (const auto& pending : table->pending) + if (pending.final_path == read_path && !pending.temporary.empty() && + !pending.renamed) + read_path = pending.temporary; + if (file_identity(read_path) != identity) { + set_error(table, "missing or changed file for " + + table->table_name + ": " + read_path); + sqlite3_finalize(stmt); + return SQLITE_IOERR; + } TsFileReader reader; - int reader_rc = reader.open(reinterpret_cast<const char*>(path)); + int reader_rc = reader.open(read_path); if (reader_rc != common::E_OK) { sqlite3_finalize(stmt); return SQLITE_IOERR; } + if (!reader.get_table_schema(selected) && + source_schema(reader, selected)) { + reader.close(); + ++segment_ordinal; + continue; + } storage::Filter* tag_filter = nullptr; storage::TagFilterBuilder tag_builder(schema.get()); for (const auto& condition : tag_eq) { @@ -1094,9 +1552,8 @@ reader.close(); continue; } - int query_rc = - reader.query(table->table_name, value_columns, query_lower, - query_upper, result, tag_filter); + int query_rc = reader.query(selected, value_columns, query_lower, + query_upper, result, tag_filter); if (query_rc != common::E_OK) { delete tag_filter; reader.close(); @@ -1232,11 +1689,9 @@ int check_mutable(HybridTable* table, const std::vector<Value>& values) { const Value& time = values[table->time_index]; - if (time.is_null || time.type != common::TIMESTAMP || + if (table->exhausted || time.is_null || time.type != common::TIMESTAMP || time.i64 < table->watermark) return SQLITE_CONSTRAINT; - for (int index : table->tag_indexes) - if (values[index].is_null) return SQLITE_CONSTRAINT_NOTNULL; return SQLITE_OK; } @@ -1285,9 +1740,12 @@ } int delete_hot(HybridTable* table, sqlite3_int64 rowid) { - if (rowid < 0) return SQLITE_CONSTRAINT; - std::string sql = - "DELETE FROM " + shadow_name(table, "_data") + " WHERE rowid=?"; + if (rowid < 0) { + set_error(table, "TsFile cold rows are immutable"); + return SQLITE_READONLY; + } + std::string sql = "DELETE FROM " + shadow_name(table, "_data") + " WHERE " + + hot_rowid(table) + "=?"; sqlite3_stmt* stmt = nullptr; int rc = sqlite3_prepare_v2(table->db, sql.c_str(), -1, &stmt, nullptr); if (rc == SQLITE_OK) { @@ -1300,7 +1758,10 @@ int update_hot(HybridTable* table, int argc, sqlite3_value** argv, sqlite3_int64 rowid) { - if (rowid < 0) return SQLITE_CONSTRAINT; + if (rowid < 0) { + set_error(table, "TsFile cold rows are immutable"); + return SQLITE_READONLY; + } std::vector<Value> values(table->columns.size()); std::string error; for (size_t i = 0; i < table->columns.size(); ++i) { @@ -1318,7 +1779,7 @@ if (i) sql << ','; sql << quote_id(table->columns[i].name) << "=?"; } - sql << " WHERE rowid=?"; + sql << " WHERE " << hot_rowid(table) << "=?"; sqlite3_stmt* stmt = nullptr; rc = sqlite3_prepare_v2(table->db, sql.str().c_str(), -1, &stmt, nullptr); if (rc == SQLITE_OK) { @@ -1332,25 +1793,26 @@ return rc; } -int write_segment(HybridTable* table, int64_t cutoff, PendingFile& file, - sqlite3_int64& row_count) { - if (!ensure_directory(table->directory)) return SQLITE_CANTOPEN; - std::ostringstream base; - do { - base.str(""); - base.clear(); - base << table->directory << '/' << file_stem(table->table_name) << '-' - << static_cast<long long>(getpid()) << '-' - << table->file_counter++; - file.temporary = base.str() + ".tmp"; - file.final_path = base.str() + ".tsfile"; - } while (access(file.temporary.c_str(), F_OK) == 0 || - access(file.final_path.c_str(), F_OK) == 0); +int write_rows(HybridTable* table, std::vector<Row> rows, + const std::string& path, bool* created) { + *created = false; + std::stable_sort(rows.begin(), rows.end(), + [table](const Row& a, const Row& b) { + for (int index : table->tag_indexes) { + const Value& x = a.values[index]; + const Value& y = b.values[index]; + if (x.is_null != y.is_null) return x.is_null; + if (!x.is_null && x.bytes != y.bytes) + return x.bytes < y.bytes; + } + return a.values[0].i64 < b.values[0].i64; + }); WriteFile write_file; - int flags = O_WRONLY | O_CREAT | O_EXCL | O_TRUNC; - int rc = write_file.create(file.temporary, flags, 0644); - if (rc != common::E_OK) return SQLITE_CANTOPEN; - std::shared_ptr<TableSchema> schema = build_tsfile_schema(table); + if (write_file.create(path, O_WRONLY | O_CREAT | O_EXCL, 0644) != + common::E_OK) + return SQLITE_CANTOPEN; + *created = true; + auto schema = build_tsfile_schema(table); TsFileTableWriter writer(&write_file, schema.get()); std::vector<std::string> names; std::vector<TSDataType> types; @@ -1362,36 +1824,12 @@ } const int max_rows = 1024; Tablet tablet(table->table_name, names, types, categories, max_rows); - std::string sql = "SELECT " + quote_id(table->columns[0].name); - for (size_t i = 1; i < table->columns.size(); ++i) - sql += "," + quote_id(table->columns[i].name); - sql += " FROM " + shadow_name(table, "_data") + " WHERE " + - quote_id(table->columns[0].name) + " >= ? AND " + - quote_id(table->columns[0].name) + " < ? ORDER BY "; - bool first = true; - for (int index : table->tag_indexes) { - if (!first) sql += ','; - first = false; - sql += quote_id(table->columns[index].name); - } - sql += ',' + quote_id(table->columns[0].name); - sqlite3_stmt* stmt = nullptr; - rc = sqlite3_prepare_v2(table->db, sql.c_str(), -1, &stmt, nullptr); - if (rc != SQLITE_OK) return rc; - sqlite3_bind_int64(stmt, 1, table->watermark); - sqlite3_bind_int64(stmt, 2, cutoff); uint32_t tablet_rows = 0; - while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) { - int64_t timestamp = sqlite3_column_int64(stmt, 0); - tablet.add_timestamp(tablet_rows, timestamp); + for (const Row& row : rows) { + tablet.add_timestamp(tablet_rows, row.values[0].i64); for (size_t i = 1; i < table->columns.size(); ++i) { const Column& column = table->columns[i]; - Value value; - if (!row_value_from_sqlite(stmt, static_cast<int>(i), column, - value)) { - sqlite3_finalize(stmt); - return SQLITE_ERROR; - } + const Value& value = row.values[i]; if (value.is_null) continue; int add_rc = common::E_OK; switch (column.type) { @@ -1428,86 +1866,80 @@ add_rc = common::E_TYPE_NOT_SUPPORTED; } if (add_rc != common::E_OK) { - sqlite3_finalize(stmt); return SQLITE_ERROR; } } ++tablet_rows; - ++row_count; if (tablet_rows == max_rows) { - if (writer.write_table(tablet) != common::E_OK) { - sqlite3_finalize(stmt); - return SQLITE_IOERR; - } + if (writer.write_table(tablet) != common::E_OK) return SQLITE_IOERR; tablet.reset(); tablet_rows = 0; } } - sqlite3_finalize(stmt); - if (rc != SQLITE_DONE) return rc; - if (tablet_rows > 0 && writer.write_table(tablet) != common::E_OK) + if (tablet_rows && writer.write_table(tablet) != common::E_OK) return SQLITE_IOERR; - if (row_count == 0) return close_writer_and_sync(write_file, writer); - std::vector<uint8_t> precision(table->precision.begin(), - table->precision.end()); - if (writer.add_tsfile_property("tsfile_sqlite.timestamp_precision", - precision) != common::E_OK) + if (table->precision != "unknown") { + std::vector<uint8_t> precision(table->precision.begin(), + table->precision.end()); + if (writer.add_tsfile_property("tsfile_sqlite.timestamp_precision", + precision) != common::E_OK) + return SQLITE_IOERR; + } + std::vector<uint8_t> time_name(table->columns[0].name.begin(), + table->columns[0].name.end()); + if (writer.add_tsfile_property("tsfile_sqlite.time_column", time_name) != + common::E_OK) return SQLITE_IOERR; if (writer.flush() != common::E_OK) return SQLITE_IOERR; return close_writer_and_sync(write_file, writer); } -void cleanup_orphans(HybridTable* table) { - std::vector<std::string> referenced; - std::string sql = "SELECT path FROM " + shadow_name(table, "_segments"); - sqlite3_stmt* stmt = nullptr; - if (sqlite3_prepare_v2(table->db, sql.c_str(), -1, &stmt, nullptr) == - SQLITE_OK) { - while (sqlite3_step(stmt) == SQLITE_ROW) { - const unsigned char* path = sqlite3_column_text(stmt, 0); - if (path != nullptr) - referenced.emplace_back(reinterpret_cast<const char*>(path)); - } - } - sqlite3_finalize(stmt); - DIR* directory = opendir(table->directory.c_str()); - if (directory == nullptr) return; - while (dirent* entry = readdir(directory)) { - std::string name(entry->d_name); - bool candidate = - name.size() > 7 && name.substr(name.size() - 7) == ".tsfile"; - candidate = candidate || - (name.size() > 4 && name.substr(name.size() - 4) == ".tmp"); - if (!candidate) continue; - std::string path = table->directory + "/" + name; - bool pending = false; - for (const PendingFile& file : table->pending) { - if (path == file.temporary || path == file.final_path) { - pending = true; - break; - } - } - if (pending) continue; - if (std::find(referenced.begin(), referenced.end(), path) == - referenced.end()) - unlink(path.c_str()); - } - closedir(directory); +int write_segment(HybridTable* table, int64_t cutoff, PendingFile& file, + sqlite3_int64& row_count, bool all = false) { + HybridCursor cursor; + cursor.table = table; + std::vector<int> projection; + for (size_t i = 0; i < table->columns.size(); ++i) projection.push_back(i); + int rc = read_hot(&cursor, {}, nullptr, table->watermark, true, cutoff, all, + {}, projection); + if (rc != SQLITE_OK) return rc; + row_count = cursor.rows.size(); + if (row_count == 0) return SQLITE_OK; + if (!directory_exists(table->directory)) return SQLITE_CANTOPEN; + struct stat st {}; + do { + std::string base = table->directory + '/' + + file_stem(table->table_name) + '-' + + std::to_string(getpid()) + '-' + + std::to_string(table->file_counter++); + file.temporary = base + ".tmp"; + file.final_path = base + ".tsfile"; + } while (lstat(file.temporary.c_str(), &st) == 0 || + lstat(file.final_path.c_str(), &st) == 0); + bool created = false; + rc = write_rows(table, std::move(cursor.rows), file.temporary, &created); + // An O_EXCL failure can mean another directory entry appeared after the + // check. Cleanup must not unlink a path this operation never created. + if (!created) file.temporary.clear(); + return rc; } -int seal(HybridTable* table, int64_t cutoff) { +int seal(HybridTable* table, int64_t cutoff, sqlite3_int64* sealed = nullptr, + bool all = false) { if (cutoff < table->watermark) return SQLITE_CONSTRAINT; - cleanup_orphans(table); + if (table->readonly) return SQLITE_READONLY; + if (table->exhausted) return SQLITE_CONSTRAINT; PendingFile file; sqlite3_int64 row_count = 0; - int rc = write_segment(table, cutoff, file, row_count); + int rc = write_segment(table, cutoff, file, row_count, all); if (rc != SQLITE_OK) { unlink(file.temporary.c_str()); return rc; } if (row_count > 0) { - std::string insert = "INSERT INTO " + shadow_name(table, "_segments") + - "(path,cutoff,row_count) VALUES(?,?,?)"; + std::string insert = + "INSERT INTO " + shadow_name(table, "_segments") + + "(path,cutoff,row_count,source_table,identity) VALUES(?,?,?,?,?)"; sqlite3_stmt* stmt = nullptr; rc = sqlite3_prepare_v2(table->db, insert.c_str(), -1, &stmt, nullptr); if (rc == SQLITE_OK) { @@ -1515,6 +1947,10 @@ SQLITE_TRANSIENT); sqlite3_bind_int64(stmt, 2, cutoff); sqlite3_bind_int64(stmt, 3, row_count); + sqlite3_bind_text(stmt, 4, table->table_name.c_str(), -1, + SQLITE_TRANSIENT); + std::string identity = file_identity(file.temporary); + sqlite3_bind_text(stmt, 5, identity.c_str(), -1, SQLITE_TRANSIENT); rc = sqlite3_step(stmt); } sqlite3_finalize(stmt); @@ -1525,7 +1961,7 @@ std::string del = "DELETE FROM " + shadow_name(table, "_data") + " WHERE " + quote_id(table->columns[0].name) + " >= ? AND " + quote_id(table->columns[0].name) + - " < ?"; + (all ? " <= ?" : " < ?"); stmt = nullptr; rc = sqlite3_prepare_v2(table->db, del.c_str(), -1, &stmt, nullptr); if (rc == SQLITE_OK) { @@ -1547,36 +1983,30 @@ sqlite3_stmt* stmt = nullptr; rc = sqlite3_prepare_v2(table->db, update.c_str(), -1, &stmt, nullptr); if (rc == SQLITE_OK) { - sqlite3_bind_int64(stmt, 1, cutoff); + if (all && cutoff == std::numeric_limits<int64_t>::max()) + sqlite3_bind_null(stmt, 1); + else + sqlite3_bind_int64(stmt, 1, cutoff); rc = sqlite3_step(stmt); } sqlite3_finalize(stmt); if (rc != SQLITE_DONE) return rc; + table->exhausted = all && cutoff == std::numeric_limits<int64_t>::max(); table->watermark = cutoff; + if (sealed) *sealed = row_count; return SQLITE_OK; } int xUpdate(sqlite3_vtab* vtab, int argc, sqlite3_value** argv, sqlite3_int64* rowid) { HybridTable* table = static_cast<HybridTable*>(vtab); + if (table->readonly) { + set_error(table, "table is readonly"); + return SQLITE_READONLY; + } int public_count = static_cast<int>(table->columns.size()); if (argc == 1) return delete_hot(table, sqlite3_value_int64(argv[0])); - if (argc != public_count + 4) return SQLITE_ERROR; - sqlite3_value* command = argv[2 + public_count]; - if (sqlite3_value_type(command) != SQLITE_NULL) { - const unsigned char* text = sqlite3_value_text(command); - if (text == nullptr || - lower(reinterpret_cast<const char*>(text)) != "seal") { - set_error(vtab, "unknown _tsfile_command"); - return SQLITE_ERROR; - } - sqlite3_value* cutoff = argv[3 + public_count]; - if (sqlite3_value_type(cutoff) != SQLITE_INTEGER) { - set_error(vtab, "_tsfile_cutoff must be INTEGER"); - return SQLITE_MISMATCH; - } - return seal(table, sqlite3_value_int64(cutoff)); - } + if (argc != public_count + 2) return SQLITE_ERROR; if (sqlite3_value_type(argv[0]) == SQLITE_NULL) return insert_hot(table, argc, argv, rowid); sqlite3_int64 old_rowid = sqlite3_value_int64(argv[0]); @@ -1591,7 +2021,8 @@ HybridTable* table = static_cast<HybridTable*>(vtab); table->pending.clear(); table->savepoint_marks.clear(); - return SQLITE_OK; + int rc = check_directory_owner(table); + return rc == SQLITE_OK ? load_config(table) : rc; } int xSync(sqlite3_vtab* vtab) { HybridTable* table = static_cast<HybridTable*>(vtab); @@ -1602,8 +2033,8 @@ if (open_rc != common::E_OK) return SQLITE_IOERR; int close_rc = reader.close(); if (close_rc != common::E_OK) return SQLITE_IOERR; - if (access(file.final_path.c_str(), F_OK) == 0) return SQLITE_IOERR; - if (rename(file.temporary.c_str(), file.final_path.c_str()) != 0) + if (publish_without_replacing(file.temporary, file.final_path) != + SQLITE_OK) return SQLITE_IOERR; file.renamed = true; if (sync_directory(table->directory) != SQLITE_OK) @@ -1614,6 +2045,9 @@ } int xCommit(sqlite3_vtab* vtab) { HybridTable* table = static_cast<HybridTable*>(vtab); + table->new_directory_owner = false; + table->uncommitted_create = false; + table->created_directories.clear(); table->pending.clear(); table->savepoint_marks.clear(); return SQLITE_OK; @@ -1626,39 +2060,49 @@ } table->pending.clear(); table->savepoint_marks.clear(); + release_new_directory(table); load_config(table); return SQLITE_OK; } -int xSavepoint(sqlite3_vtab* vtab, int) { +int xSavepoint(sqlite3_vtab* vtab, int id) { HybridTable* table = static_cast<HybridTable*>(vtab); - table->savepoint_marks.push_back(table->pending.size()); + table->savepoint_marks[id] = table->pending.size(); return SQLITE_OK; } -int xRelease(sqlite3_vtab* vtab, int) { +int xRelease(sqlite3_vtab* vtab, int id) { HybridTable* table = static_cast<HybridTable*>(vtab); - if (!table->savepoint_marks.empty()) table->savepoint_marks.pop_back(); + table->savepoint_marks.erase(table->savepoint_marks.lower_bound(id), + table->savepoint_marks.end()); return SQLITE_OK; } -int xRollbackTo(sqlite3_vtab* vtab, int) { +int xRollbackTo(sqlite3_vtab* vtab, int id) { HybridTable* table = static_cast<HybridTable*>(vtab); - if (table->savepoint_marks.empty()) return SQLITE_OK; - size_t mark = table->savepoint_marks.back(); + auto it = table->savepoint_marks.find(id); + size_t mark = it == table->savepoint_marks.end() ? 0 : it->second; while (table->pending.size() > mark) { const PendingFile& file = table->pending.back(); if (!file.temporary.empty()) unlink(file.temporary.c_str()); if (file.renamed) unlink(file.final_path.c_str()); table->pending.pop_back(); } - load_config(table); - return SQLITE_OK; + table->savepoint_marks.erase(table->savepoint_marks.upper_bound(id), + table->savepoint_marks.end()); + int rc = load_config(table); + if (rc != SQLITE_OK && table->uncommitted_create) { + // SQLite has already undone this table's creation. Its shadow config + // no longer exists; the vtab is about to be disconnected. + release_new_directory(table); + return SQLITE_OK; + } + return rc; } int xRename(sqlite3_vtab*, const char*) { return SQLITE_CONSTRAINT; } int xShadowName(const char* name) { if (name == nullptr) return 0; - const char* suffixes[] = {"_data", "_segments", "_config"}; + const char* suffixes[] = {"tsfile$hot", "tsfile$segments", "tsfile$config"}; for (const char* suffix : suffixes) { size_t length = std::strlen(name), suffix_length = std::strlen(suffix); - if (length > suffix_length && + if (length == suffix_length && std::strcmp(name + length - suffix_length, suffix) == 0) return 1; } @@ -1672,6 +2116,8 @@ xSync, xCommit, xRollback, nullptr, xRename, xSavepoint, xRelease, xRollbackTo, xShadowName, nullptr}; +#include "tsfile_sqlite_management.inc" + } // namespace extern "C" int sqlite3_extension_init(sqlite3* db, char** error_message, @@ -1687,6 +2133,10 @@ } initialized = true; } - return sqlite3_create_module_v2(db, "tsfile_hybrid", &kModule, nullptr, - nullptr); + Connection* connection = new Connection(); + int rc = sqlite3_create_module_v2( + db, "tsfile_hybrid", &kModule, connection, + [](void* p) { delete static_cast<Connection*>(p); }); + if (rc != SQLITE_OK) return rc; + return register_management(db, connection); }
diff --git a/cpp/src/sqlite/tsfile_sqlite_management.inc b/cpp/src/sqlite/tsfile_sqlite_management.inc new file mode 100644 index 0000000..e423827 --- /dev/null +++ b/cpp/src/sqlite/tsfile_sqlite_management.inc
@@ -0,0 +1,582 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +// Included inside the adapter's anonymous namespace. Management operations use +// the same connection and table objects as the virtual-table transaction hooks. + +std::string qualified_name(const HybridTable* table) { + return quote_id(table->db_name) + '.' + quote_id(table->table_name); +} + +HybridTable* resolve_table(sqlite3* db, Connection* connection, + const std::string& name) { + // Split the schema qualifier outside quoted identifiers. + char quote = 0; + size_t dot = std::string::npos; + for (size_t i = 0; i < name.size(); ++i) { + char c = name[i]; + if (quote) { + if (c == quote) { + if (i + 1 < name.size() && name[i + 1] == quote) + ++i; + else + quote = 0; + } + } else if (c == '"' || c == '`' || c == '[') + quote = c == '[' ? ']' : c; + else if (c == '.') { + if (dot != std::string::npos) return nullptr; + dot = i; + } + } + if (quote || dot == std::string::npos) return nullptr; + std::string schema, table; + size_t pos = 0; + std::string a = trim(name.substr(0, dot)), b = trim(name.substr(dot + 1)); + if (!sql_token(a, pos, schema) || !trim(a.substr(pos)).empty()) + return nullptr; + pos = 0; + if (!sql_token(b, pos, table) || !trim(b.substr(pos)).empty()) + return nullptr; + sqlite3_stmt* stmt = nullptr; + const std::string sql = "SELECT * FROM " + quote_id(schema) + '.' + + quote_id(table) + " LIMIT 0"; + int rc = sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr); + sqlite3_finalize(stmt); + if (rc != SQLITE_OK) return nullptr; + auto it = connection->tables.find(lower(schema) + '.' + lower(table)); + return it == connection->tables.end() ? nullptr : it->second; +} + +bool standalone_call(sqlite3* db, const std::string& function) { + // Management functions are intentionally limited to a standalone SELECT. + // Reject row queries and compound expressions before any side effect. + int active = 0; + for (sqlite3_stmt* stmt = sqlite3_next_stmt(db, nullptr); stmt; + stmt = sqlite3_next_stmt(db, stmt)) { + if (!sqlite3_stmt_busy(stmt)) continue; + ++active; + std::string sql = trim(sqlite3_sql(stmt)); + size_t p = sql.find_first_of(" \t\r\n"); + if (p == std::string::npos || lower(sql.substr(0, p)) != "select") + return false; + sql = trim(sql.substr(p)); + p = sql.find('('); + if (p == std::string::npos || lower(trim(sql.substr(0, p))) != function) + return false; + char quote = 0; + size_t end = std::string::npos; + for (size_t i = p + 1; i < sql.size(); ++i) { + char c = sql[i]; + if (quote) { + if (c == quote) { + if (i + 1 < sql.size() && sql[i + 1] == quote) + ++i; + else + quote = 0; + } + } else if (c == '\'' || c == '"') + quote = c; + else if (c == '(') + return false; + else if (c == ')') { + end = i; + break; + } + } + if (end == std::string::npos) return false; + std::string tail = trim(sql.substr(end + 1)); + if (!tail.empty() && tail != ";") return false; + } + return active == 1; +} + +void management_error(sqlite3_context* context, int rc, + const std::string& detail) { + sqlite3_result_error(context, detail.c_str(), -1); + sqlite3_result_error_code(context, rc == SQLITE_OK ? SQLITE_ERROR : rc); +} + +struct ManagementGuard { + Connection* connection; + explicit ManagementGuard(Connection* c) : connection(c) { + c->managing = true; + } + ~ManagementGuard() { connection->managing = false; } +}; + +int enlist_table(HybridTable* table) { + // Even a zero-row virtual UPDATE enlists the module in SQLite's write + // transaction. The seal then shares xSync/xRollback and savepoint hooks. + return exec_sql(table->db, + "UPDATE " + qualified_name(table) + " SET " + + quote_id(table->columns[0].name) + '=' + + quote_id(table->columns[0].name) + " WHERE 0", + table); +} + +void rollback_management(HybridTable* table) { + exec_sql(table->db, "ROLLBACK TO tsfile_management"); + exec_sql(table->db, "RELEASE tsfile_management"); + load_config(table); +} + +void seal_function(sqlite3_context* context, int, sqlite3_value** argv) { + auto* connection = static_cast<Connection*>(sqlite3_user_data(context)); + sqlite3* db = sqlite3_context_db_handle(context); + if (connection->managing || !standalone_call(db, "tsfile_seal") || + sqlite3_value_type(argv[0]) != SQLITE_TEXT || + sqlite3_value_type(argv[1]) != SQLITE_INTEGER) { + management_error( + context, SQLITE_MISUSE, + "use SELECT tsfile_seal('schema.table', integer_cutoff)"); + return; + } + ManagementGuard guard(connection); + auto* table = resolve_table( + db, connection, + reinterpret_cast<const char*>(sqlite3_value_text(argv[0]))); + if (!table) { + management_error(context, SQLITE_ERROR, + "TsFile table not found; include its SQLite schema"); + return; + } + if (table->readonly) { + management_error(context, SQLITE_READONLY, "table is readonly"); + return; + } + int rc = exec_sql(db, "SAVEPOINT tsfile_management", table); + if (rc != SQLITE_OK) { + management_error(context, rc, sqlite3_errmsg(db)); + return; + } + rc = enlist_table(table); + sqlite3_int64 rows = 0; + if (rc == SQLITE_OK) rc = seal(table, sqlite3_value_int64(argv[1]), &rows); + if (rc == SQLITE_OK) rc = exec_sql(db, "RELEASE tsfile_management", table); + if (rc != SQLITE_OK) { + std::string detail = + table->zErrMsg ? table->zErrMsg + : "seal failed: invalid boundary or file operation"; + rollback_management(table); + management_error(context, rc, detail); + return; + } + sqlite3_result_int64(context, rows); +} + +std::string canonical_path(const std::string& path) { + char* resolved = realpath(path.c_str(), nullptr); + if (!resolved) return ""; + std::string result(resolved); + free(resolved); + return result; +} + +bool paths_overlap(const std::string& a, const std::string& b) { + return a == b || + (a.size() > b.size() && a.compare(0, b.size() + 1, b + "/") == 0) || + (b.size() > a.size() && b.compare(0, a.size() + 1, a + "/") == 0); +} + +void export_function(sqlite3_context* context, int, sqlite3_value** argv) { + auto* connection = static_cast<Connection*>(sqlite3_user_data(context)); + sqlite3* db = sqlite3_context_db_handle(context); + if (connection->managing || !standalone_call(db, "tsfile_export") || + !sqlite3_get_autocommit(db) || + sqlite3_value_type(argv[0]) != SQLITE_TEXT || + sqlite3_value_type(argv[1]) != SQLITE_TEXT) { + management_error(context, SQLITE_MISUSE, + "export requires a standalone SELECT outside an " + "explicit transaction"); + return; + } + ManagementGuard guard(connection); + auto* table = resolve_table( + db, connection, + reinterpret_cast<const char*>(sqlite3_value_text(argv[0]))); + if (!table) { + management_error(context, SQLITE_ERROR, + "TsFile table not found; include its SQLite schema"); + return; + } + std::string output = + reinterpret_cast<const char*>(sqlite3_value_text(argv[1])); + size_t slash = output.find_last_of('/'); + struct stat st {}; + if (output.empty() || output[0] != '/' || slash == std::string::npos || + slash + 1 == output.size() || lstat(output.c_str(), &st) == 0) { + management_error(context, SQLITE_CANTOPEN, + "output must be a new absolute directory"); + return; + } + std::string parent = canonical_path( + output.substr(0, slash).empty() ? "/" : output.substr(0, slash)); + if (parent.empty() || !directory_exists(parent)) { + management_error(context, SQLITE_CANTOPEN, + "output parent directory is unavailable"); + return; + } + output = parent + (parent == "/" ? "" : "/") + output.substr(slash + 1); + std::vector<std::string> sources; + if (!table->directory.empty()) + sources.push_back(canonical_path(table->directory)); + if (!table->source_file.empty()) + sources.push_back(canonical_path(table->source_file)); + for (const auto& source : sources) { + if (!source.empty() && paths_overlap(source, output)) { + management_error(context, SQLITE_CANTOPEN, + "output overlaps a source directory"); + return; + } + } + int rc = exec_sql(db, "SAVEPOINT tsfile_management", table); + if (rc != SQLITE_OK) { + management_error(context, rc, sqlite3_errmsg(db)); + return; + } + HybridCursor snapshot; + snapshot.table = table; + std::vector<int> projection; + for (size_t i = 0; i < table->columns.size(); ++i) projection.push_back(i); + // Validate and capture existing immutable data before sealing changes + // state. + rc = read_cold(&snapshot, {}, nullptr, kNoWatermark, true, + std::numeric_limits<int64_t>::max(), true, {}, projection); + if (rc == SQLITE_OK && !table->readonly) rc = enlist_table(table); + if (rc == SQLITE_OK && !table->readonly) { + HybridCursor hot; + hot.table = table; + rc = + read_hot(&hot, {}, nullptr, kNoWatermark, true, + std::numeric_limits<int64_t>::max(), true, {}, projection); + if (rc == SQLITE_OK && !hot.rows.empty()) { + int64_t maximum = kNoWatermark; + for (const auto& row : hot.rows) + maximum = std::max(maximum, row.values[0].i64); + const bool exhausted = + maximum == std::numeric_limits<int64_t>::max(); + rc = seal(table, exhausted ? maximum : maximum + 1, nullptr, + exhausted); + if (rc == SQLITE_OK) + snapshot.rows.insert(snapshot.rows.end(), hot.rows.begin(), + hot.rows.end()); + } + } + if (rc == SQLITE_OK) rc = exec_sql(db, "RELEASE tsfile_management", table); + if (rc != SQLITE_OK) { + std::string detail = + table->zErrMsg ? table->zErrMsg : "automatic seal failed"; + rollback_management(table); + management_error(context, rc, + "automatic seal not committed: " + detail); + return; + } + std::string staging = output + ".tsfile-export-XXXXXX"; + std::vector<char> buffer(staging.begin(), staging.end()); + buffer.push_back(0); + if (!mkdtemp(buffer.data())) { + management_error( + context, SQLITE_CANTOPEN, + "automatic seal committed; cannot create export staging directory"); + return; + } + staging = buffer.data(); + std::string part = staging + "/part-000001.tsfile"; + bool part_created = false; + if (!snapshot.rows.empty()) + rc = write_rows(table, std::move(snapshot.rows), part, &part_created); + if (rc == SQLITE_OK) rc = sync_directory(staging); + if (rc == SQLITE_OK) { + rc = publish_without_replacing(staging, output); + } + if (rc != SQLITE_OK) { + if (part_created) unlink(part.c_str()); + rmdir(staging.c_str()); + management_error( + context, rc, + "automatic seal committed; export output not published"); + return; + } + if (sync_directory(parent) != SQLITE_OK) { + management_error(context, SQLITE_IOERR_FSYNC, + "automatic seal committed; complete output published " + "but directory fsync failed"); + return; + } + sqlite3_result_int( + context, + access((output + "/part-000001.tsfile").c_str(), F_OK) == 0 ? 1 : 0); +} + +struct DiagnosticTable : sqlite3_vtab { + sqlite3* db = nullptr; + Connection* connection = nullptr; + bool verify = false; +}; +struct DiagnosticCursor : sqlite3_vtab_cursor { + std::vector<std::vector<Value>> rows; + size_t pos = 0; +}; +Value text_cell(const std::string& text) { + Value v; + v.type = common::STRING; + v.is_null = false; + v.bytes = text; + return v; +} +Value int_cell(sqlite3_int64 value) { + Value v; + v.type = common::INT64; + v.is_null = false; + v.i64 = value; + return v; +} + +int diagnostic_connect(sqlite3* db, void* aux, int, const char* const* argv, + sqlite3_vtab** out, char**) { + std::unique_ptr<DiagnosticTable> table(new DiagnosticTable()); + table->db = db; + table->connection = static_cast<Connection*>(aux); + table->verify = std::string(argv[0]) == "tsfile_verify"; + const char* schema = + table->verify + ? "CREATE TABLE x(segment_id INTEGER,path TEXT,status TEXT,detail " + "TEXT,requested_table TEXT HIDDEN)" + : "CREATE TABLE x(table_name TEXT,mode TEXT,timestamp_precision " + "TEXT,watermark INTEGER,append_available INTEGER," + "source_max_time INTEGER,hot_rows INTEGER,file_count " + "INTEGER,directory TEXT,source_file TEXT,source_table " + "TEXT,requested_table TEXT HIDDEN)"; + int rc = sqlite3_declare_vtab(db, schema); + if (rc != SQLITE_OK) return rc; + sqlite3_vtab_config(db, SQLITE_VTAB_DIRECTONLY); + *out = table.release(); + return SQLITE_OK; +} +int diagnostic_best(sqlite3_vtab* base, sqlite3_index_info* info) { + int hidden = static_cast<DiagnosticTable*>(base)->verify ? 4 : 11; + for (int i = 0; i < info->nConstraint; ++i) { + if (info->aConstraint[i].iColumn == hidden && + info->aConstraint[i].op == SQLITE_INDEX_CONSTRAINT_EQ && + info->aConstraint[i].usable) { + info->aConstraintUsage[i].argvIndex = 1; + info->aConstraintUsage[i].omit = 1; + info->idxNum = 1; + info->estimatedCost = 1; + return SQLITE_OK; + } + } + return SQLITE_CONSTRAINT; +} +int diagnostic_disconnect(sqlite3_vtab* p) { + delete static_cast<DiagnosticTable*>(p); + return SQLITE_OK; +} +int diagnostic_open(sqlite3_vtab*, sqlite3_vtab_cursor** out) { + *out = new DiagnosticCursor(); + return SQLITE_OK; +} +int diagnostic_close(sqlite3_vtab_cursor* p) { + delete static_cast<DiagnosticCursor*>(p); + return SQLITE_OK; +} +int diagnostic_next(sqlite3_vtab_cursor* p) { + ++static_cast<DiagnosticCursor*>(p)->pos; + return SQLITE_OK; +} +int diagnostic_eof(sqlite3_vtab_cursor* p) { + auto* c = static_cast<DiagnosticCursor*>(p); + return c->pos >= c->rows.size(); +} +int diagnostic_rowid(sqlite3_vtab_cursor* p, sqlite3_int64* id) { + *id = static_cast<DiagnosticCursor*>(p)->pos + 1; + return SQLITE_OK; +} +int diagnostic_column(sqlite3_vtab_cursor* p, sqlite3_context* ctx, + int column) { + auto* cursor = static_cast<DiagnosticCursor*>(p); + if (column >= static_cast<int>(cursor->rows[cursor->pos].size())) { + sqlite3_result_null(ctx); + return SQLITE_OK; + } + const auto& value = cursor->rows[cursor->pos][column]; + if (value.is_null) + sqlite3_result_null(ctx); + else if (value.type == common::INT64) + sqlite3_result_int64(ctx, value.i64); + else + sqlite3_result_text(ctx, value.bytes.c_str(), value.bytes.size(), + SQLITE_TRANSIENT); + return SQLITE_OK; +} +int diagnostic_filter(sqlite3_vtab_cursor* base, int, const char*, int argc, + sqlite3_value** argv) { + auto* cursor = static_cast<DiagnosticCursor*>(base); + auto* diagnostic = static_cast<DiagnosticTable*>(base->pVtab); + cursor->rows.clear(); + cursor->pos = 0; + if (argc != 1 || sqlite3_value_type(argv[0]) != SQLITE_TEXT) + return SQLITE_MISMATCH; + std::string requested = + reinterpret_cast<const char*>(sqlite3_value_text(argv[0])); + auto* table = + resolve_table(diagnostic->db, diagnostic->connection, requested); + if (!table) { + set_error(diagnostic, "TsFile table not found: " + requested); + return SQLITE_ERROR; + } + sqlite3_stmt* stmt = nullptr; + if (!diagnostic->verify) { + std::string sql = + "SELECT " + quote_sql(table->db_name + '.' + table->table_name) + + ",mode,precision,watermark,(mode='writable' AND watermark IS NOT " + "NULL),source_max," + + (table->readonly ? "0" + : "(SELECT count(*) FROM " + + shadow_name(table, "_data") + ")") + + ",(SELECT count(*) FROM " + shadow_name(table, "_segments") + + "),nullif(directory,''),nullif(source_file,''),nullif(source_table," + "'') FROM " + + shadow_name(table, "_config"); + int rc = sqlite3_prepare_v2(table->db, sql.c_str(), -1, &stmt, nullptr); + if (rc != SQLITE_OK) return rc; + while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) { + std::vector<Value> row; + for (int i = 0; i < 11; ++i) { + if (sqlite3_column_type(stmt, i) == SQLITE_NULL) + row.push_back(Value()); + else if (sqlite3_column_type(stmt, i) == SQLITE_INTEGER) + row.push_back(int_cell(sqlite3_column_int64(stmt, i))); + else + row.push_back(text_cell(reinterpret_cast<const char*>( + sqlite3_column_text(stmt, i)))); + } + row.push_back(text_cell(requested)); + cursor->rows.push_back(std::move(row)); + } + sqlite3_finalize(stmt); + return rc == SQLITE_DONE ? SQLITE_OK : rc; + } + std::string sql = "SELECT rowid,path,identity,source_table FROM " + + shadow_name(table, "_segments") + " ORDER BY rowid"; + int rc = sqlite3_prepare_v2(table->db, sql.c_str(), -1, &stmt, nullptr); + if (rc != SQLITE_OK) return rc; + std::set<std::string> registered; + while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) { + std::string path = + reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1)); + std::string identity = + reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2)); + std::string source = + reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3)); + registered.insert(path); + std::string actual = path; + for (const auto& pending : table->pending) + if (pending.final_path == path && !pending.temporary.empty() && + !pending.renamed) + actual = pending.temporary; + std::string status = "OK", + detail = "file and registered identity match"; + struct stat st {}; + if (stat(actual.c_str(), &st) != 0 && errno == ENOENT) { + status = "MISSING"; + detail = "registered file is missing"; + } else { + TsFileReader reader; + if (reader.open(actual) != common::E_OK) { + status = "CORRUPT"; + detail = "cannot parse registered TsFile"; + } else { + if (!source_schema(reader, source) || + file_identity(actual) != identity) { + status = "MISMATCH"; + detail = "file or schema differs from registration"; + } + reader.close(); + } + } + cursor->rows.push_back({int_cell(sqlite3_column_int64(stmt, 0)), + text_cell(path), text_cell(status), + text_cell(detail), text_cell(requested)}); + } + sqlite3_finalize(stmt); + if (rc != SQLITE_DONE) return rc; + DIR* dir = + table->directory.empty() ? nullptr : opendir(table->directory.c_str()); + if (dir) { + while (dirent* entry = readdir(dir)) { + std::string name = entry->d_name, + path = table->directory + '/' + name; + struct stat st {}; + if (name.size() <= 7 || name.substr(name.size() - 7) != ".tsfile" || + registered.count(path) || lstat(path.c_str(), &st) != 0 || + !S_ISREG(st.st_mode)) + continue; + cursor->rows.push_back( + {Value(), text_cell(path), text_cell("UNREGISTERED"), + text_cell("unregistered file; left unchanged"), + text_cell(requested)}); + } + closedir(dir); + } + return SQLITE_OK; +} +const sqlite3_module kDiagnosticModule = {3, + nullptr, + diagnostic_connect, + diagnostic_best, + diagnostic_disconnect, + nullptr, + diagnostic_open, + diagnostic_close, + diagnostic_filter, + diagnostic_next, + diagnostic_eof, + diagnostic_column, + diagnostic_rowid, + nullptr, + nullptr, + nullptr, + nullptr, + nullptr, + nullptr, + nullptr, + nullptr, + nullptr, + nullptr, + nullptr, + nullptr}; + +int register_management(sqlite3* db, Connection* connection) { + int rc = sqlite3_create_function_v2( + db, "tsfile_seal", 2, SQLITE_UTF8 | SQLITE_DIRECTONLY, connection, + seal_function, nullptr, nullptr, nullptr); + if (rc == SQLITE_OK) + rc = sqlite3_create_function_v2( + db, "tsfile_export", 2, SQLITE_UTF8 | SQLITE_DIRECTONLY, connection, + export_function, nullptr, nullptr, nullptr); + if (rc == SQLITE_OK) + rc = sqlite3_create_module_v2(db, "tsfile_table_info", + &kDiagnosticModule, connection, nullptr); + if (rc == SQLITE_OK) + rc = sqlite3_create_module_v2(db, "tsfile_verify", &kDiagnosticModule, + connection, nullptr); + return rc; +}
diff --git a/cpp/test/CMakeLists.txt b/cpp/test/CMakeLists.txt index 5a843dd..4a6b3c2 100644 --- a/cpp/test/CMakeLists.txt +++ b/cpp/test/CMakeLists.txt
@@ -340,7 +340,8 @@ TSFILE_SQLITE_EXTENSION_PATH="$<TARGET_FILE:tsfile_sqlite>") set_target_properties(TsFile_Sqlite_Test PROPERTIES RUNTIME_OUTPUT_DIRECTORY ${LIB_TSFILE_SDK_DIR} - SKIP_BUILD_RPATH TRUE) + SKIP_BUILD_RPATH FALSE + BUILD_RPATH "$<TARGET_FILE_DIR:tsfile>") add_test(NAME TsFileSqliteTest COMMAND TsFile_Sqlite_Test) if (APPLE) set_tests_properties(TsFileSqliteTest PROPERTIES
diff --git a/cpp/test/sqlite/tsfile_sqlite_test.cc b/cpp/test/sqlite/tsfile_sqlite_test.cc index 87d0cda..85bda68 100644 --- a/cpp/test/sqlite/tsfile_sqlite_test.cc +++ b/cpp/test/sqlite/tsfile_sqlite_test.cc
@@ -17,20 +17,28 @@ * under the License. */ +#include <dirent.h> #include <gtest/gtest.h> #include <sqlite3.h> +#include <sys/stat.h> #include <unistd.h> #include <cstdio> +#include <fstream> #include <string> +#include "common/tablet.h" +#include "reader/tsfile_reader.h" +#include "writer/tsfile_writer.h" + namespace { class TsFileSqliteTest : public ::testing::Test { protected: void SetUp() override { - directory_ = "/tmp/tsfile-sqlite-test-" + - std::to_string(static_cast<long long>(getpid())); + char directory[] = "/tmp/tsfile-sqlite-test-XXXXXX"; + ASSERT_NE(nullptr, mkdtemp(directory)); + directory_ = directory; std::string sql = "SELECT load_extension(" + quote(TSFILE_SQLITE_EXTENSION_PATH) + ")"; ASSERT_EQ(SQLITE_OK, sqlite3_open(":memory:", &db_)); @@ -46,9 +54,9 @@ directory_ + "'," "timestamp_precision='ms'," - "column='time:TIMESTAMP:TIME'," - "column='device:STRING:TAG'," - "column='temperature:DOUBLE:FIELD')")); + "time TIMESTAMP TIME," + "device STRING TAG," + "temperature DOUBLE FIELD)")); } void TearDown() override { @@ -78,13 +86,41 @@ EXPECT_EQ(SQLITE_OK, sqlite3_prepare_v2(db_, sql.c_str(), -1, &stmt, nullptr)); int result = -1; - if (stmt != nullptr && sqlite3_step(stmt) == SQLITE_ROW) { - result = sqlite3_column_int(stmt, 0); + if (stmt != nullptr) { + int rc = sqlite3_step(stmt); + EXPECT_EQ(SQLITE_ROW, rc) << sqlite3_errmsg(db_); + if (rc == SQLITE_ROW) result = sqlite3_column_int(stmt, 0); } sqlite3_finalize(stmt); return result; } + std::string source_file() { + DIR* dir = opendir(directory_.c_str()); + if (!dir) return ""; + std::string result; + while (dirent* e = readdir(dir)) { + std::string name = e->d_name; + if (name.size() > 7 && name.substr(name.size() - 7) == ".tsfile") + result = directory_ + "/" + name; + } + closedir(dir); + return result; + } + + std::string scalar_text(const std::string& sql) { + sqlite3_stmt* stmt = nullptr; + EXPECT_EQ(SQLITE_OK, + sqlite3_prepare_v2(db_, sql.c_str(), -1, &stmt, nullptr)); + std::string result; + if (stmt && sqlite3_step(stmt) == SQLITE_ROW && + sqlite3_column_text(stmt, 0)) + result = + reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0)); + sqlite3_finalize(stmt); + return result; + } + static std::string quote(const std::string& value) { std::string result = "'"; for (char c : value) result += c == '\'' ? "''" : std::string(1, c); @@ -101,18 +137,14 @@ ASSERT_EQ(SQLITE_OK, exec("INSERT INTO sensor VALUES(2,'d0',2.5)")); ASSERT_EQ(2, count("SELECT count(*) FROM sensor")); ASSERT_EQ(SQLITE_OK, exec("BEGIN")); - ASSERT_EQ(SQLITE_OK, - exec("INSERT INTO sensor(_tsfile_command,_tsfile_cutoff) " - "VALUES('seal',2)")); + ASSERT_EQ(SQLITE_OK, exec("SELECT tsfile_seal('main.sensor',2)")); ASSERT_EQ(SQLITE_OK, exec("ROLLBACK")); ASSERT_EQ(2, count("SELECT count(*) FROM sensor")); - ASSERT_EQ(0, count("SELECT count(*) FROM sensor_segments")); + ASSERT_EQ(0, count("SELECT count(*) FROM \"sensor_tsfile$segments\"")); - ASSERT_EQ(SQLITE_OK, - exec("INSERT INTO sensor(_tsfile_command,_tsfile_cutoff) " - "VALUES('seal',2)")); + ASSERT_EQ(SQLITE_OK, exec("SELECT tsfile_seal('main.sensor',2)")); ASSERT_EQ(2, count("SELECT count(*) FROM sensor")); - ASSERT_EQ(1, count("SELECT count(*) FROM sensor_segments")); + ASSERT_EQ(1, count("SELECT count(*) FROM \"sensor_tsfile$segments\"")); ASSERT_EQ(1, count("SELECT count(*) FROM sensor WHERE time=1")); ASSERT_EQ(1, count("SELECT count(*) FROM sensor WHERE time=2")); char* error = nullptr; @@ -126,19 +158,17 @@ ASSERT_EQ(SQLITE_OK, exec("CREATE VIRTUAL TABLE ints USING tsfile_hybrid(" "directory='" + directory_ + - "/ints',timestamp_precision='ms'," - "column='time:TIMESTAMP:TIME'," - "column='device:STRING:TAG'," - "column='reading:INT32:FIELD')")); + "-ints',timestamp_precision='ms'," + "time TIMESTAMP TIME," + "device STRING TAG," + "reading INT32 FIELD)")); EXPECT_EQ(SQLITE_MISMATCH, exec_raw("INSERT INTO ints VALUES(1,'d0',2147483648)")); ASSERT_EQ(SQLITE_OK, exec("INSERT INTO ints VALUES(1,'d0',42)")); - ASSERT_EQ(SQLITE_OK, - exec("INSERT INTO ints(_tsfile_command,_tsfile_cutoff) " - "VALUES('seal',2)")); - EXPECT_EQ(SQLITE_CONSTRAINT, + ASSERT_EQ(SQLITE_OK, exec("SELECT tsfile_seal('main.ints',2)")); + EXPECT_EQ(SQLITE_READONLY, exec_raw("UPDATE ints SET reading=43 WHERE time=1")); - EXPECT_EQ(SQLITE_CONSTRAINT, exec_raw("DELETE FROM ints WHERE time=1")); + EXPECT_EQ(SQLITE_READONLY, exec_raw("DELETE FROM ints WHERE time=1")); EXPECT_EQ(1, count("SELECT count(*) FROM ints " "WHERE time=1 AND reading=42")); } @@ -148,17 +178,15 @@ exec("CREATE VIRTUAL TABLE payload USING tsfile_hybrid(" "directory='" + directory_ + - "/payload',timestamp_precision='ms'," - "column='time:TIMESTAMP:TIME'," - "column='device:STRING:TAG'," - "column='payload:BLOB:FIELD'," - "column='note:TEXT:FIELD')")); + "-payload',timestamp_precision='ms'," + "time TIMESTAMP TIME," + "device STRING TAG," + "payload BLOB FIELD," + "note TEXT FIELD)")); ASSERT_EQ(SQLITE_OK, exec("INSERT INTO payload VALUES(1,'d0',X'000102','')")); ASSERT_EQ(SQLITE_OK, exec("INSERT INTO payload VALUES(2,'d0',NULL,NULL)")); - ASSERT_EQ(SQLITE_OK, - exec("INSERT INTO payload(_tsfile_command,_tsfile_cutoff) " - "VALUES('seal',3)")); + ASSERT_EQ(SQLITE_OK, exec("SELECT tsfile_seal('main.payload',3)")); EXPECT_EQ(1, count("SELECT count(*) FROM payload " "WHERE time=1 AND hex(payload)='000102'")); EXPECT_EQ(1, count("SELECT count(*) FROM payload " @@ -173,14 +201,557 @@ exec("CREATE VIRTUAL TABLE aux.attached USING tsfile_hybrid(" "directory='" + directory_ + - "/attached',timestamp_precision='ms'," - "column='time:TIMESTAMP:TIME'," - "column='device:STRING:TAG'," - "column='reading:INT32:FIELD')")); + "-attached',timestamp_precision='ms'," + "time TIMESTAMP TIME," + "device STRING TAG," + "reading INT32 FIELD)")); ASSERT_EQ(SQLITE_OK, exec("INSERT INTO aux.attached VALUES(1,'d0',7)")); - EXPECT_EQ(1, count("SELECT count(*) FROM aux.attached_data")); + EXPECT_EQ(1, count("SELECT count(*) FROM aux.\"attached_tsfile$hot\"")); EXPECT_EQ(0, count("SELECT count(*) FROM main.sqlite_master " - "WHERE name='attached_data'")); + "WHERE name='attached_tsfile$hot'")); +} + +TEST_F(TsFileSqliteTest, SqlDeclarationsAndNullableLogicalKeys) { + ASSERT_EQ(SQLITE_OK, exec("CREATE VIRTUAL TABLE modern USING tsfile_hybrid(" + "time TIMESTAMP TIME, device STRING TAG, value " + "DOUBLE FIELD, directory=" + + quote(directory_ + "-modern") + + ",timestamp_precision='ms')")); + ASSERT_EQ( + SQLITE_OK, + exec( + "INSERT INTO modern VALUES(1,NULL,1.5),(1,'',2.5),(1,'null',3.5)")); + EXPECT_EQ(SQLITE_CONSTRAINT, + exec_raw("INSERT INTO modern VALUES(1,NULL,9)")); + EXPECT_EQ(3, count("SELECT count(*) FROM modern")); + EXPECT_EQ( + 1, + count( + "SELECT count(*) FROM modern WHERE device IS NULL AND value=1.5")); + ASSERT_EQ(SQLITE_OK, + exec("UPDATE modern SET value=4 WHERE device IS NULL")); + EXPECT_EQ(SQLITE_CONSTRAINT, + exec_raw("UPDATE modern SET device=NULL WHERE device=''")); + EXPECT_EQ( + 1, count("SELECT count(*) FROM modern WHERE device='' AND value=2.5")); +} + +TEST_F(TsFileSqliteTest, NoTagsAndInvalidDeclarations) { + ASSERT_EQ( + SQLITE_OK, + exec("CREATE VIRTUAL TABLE times USING tsfile_hybrid(" + "time TIMESTAMP TIME, value DOUBLE FIELD, directory=" + + quote(directory_ + "-times") + ",timestamp_precision='ms')")); + ASSERT_EQ(SQLITE_OK, exec("INSERT INTO times VALUES(1,2)")); + EXPECT_EQ(SQLITE_CONSTRAINT, exec_raw("INSERT INTO times VALUES(1,3)")); + EXPECT_NE( + SQLITE_OK, + exec_raw("CREATE VIRTUAL TABLE bad USING tsfile_hybrid(" + "time TIMESTAMP TIME, value DOUBLE, directory=" + + quote(directory_ + "-bad") + ",timestamp_precision='ms')")); + EXPECT_NE(SQLITE_OK, + exec_raw("CREATE VIRTUAL TABLE bad USING tsfile_hybrid(" + "time TIMESTAMP TIME, value DOUBLE FIELD, directory=" + + quote(directory_ + "-bad") + + ",timestamp_precision='ms', timestamp_precision='us')")); +} + +TEST_F(TsFileSqliteTest, ExternalFileInferredSchemaAndAppendBoundary) { + ASSERT_EQ(SQLITE_OK, + exec("INSERT INTO sensor VALUES(1,'d0',1.5),(2,'d1',2.5)")); + ASSERT_EQ(SQLITE_OK, exec("SELECT tsfile_seal('main.sensor',3)")); + const std::string path = source_file(); + ASSERT_FALSE(path.empty()); + ASSERT_EQ(SQLITE_OK, + exec("CREATE VIRTUAL TABLE history USING tsfile_hybrid(file=" + + quote(path) + ",source_table='sensor')")); + EXPECT_EQ(2, count("SELECT count(*) FROM history")); + EXPECT_EQ( + "INTEGER", + scalar_text( + "SELECT type FROM pragma_table_info('history') WHERE name='time'")); + EXPECT_EQ("REAL", + scalar_text("SELECT type FROM pragma_table_info('history') WHERE " + "name='temperature'")); + EXPECT_EQ(SQLITE_READONLY, + exec_raw("INSERT INTO history VALUES(3,'d0',9)")); + EXPECT_EQ(SQLITE_OK, exec_raw("DELETE FROM history WHERE 0")); + EXPECT_EQ(SQLITE_READONLY, exec_raw("DELETE FROM history WHERE time=1")); + EXPECT_EQ(SQLITE_OK, exec_raw("UPDATE history SET temperature=8 WHERE 0")); + EXPECT_EQ(SQLITE_READONLY, + exec_raw("UPDATE history SET temperature=8 WHERE time=1")); + EXPECT_EQ(0, count("SELECT count(*) FROM sqlite_master WHERE " + "name='history_tsfile$hot'")); + ASSERT_EQ( + SQLITE_OK, + exec("CREATE VIRTUAL TABLE continuation USING tsfile_hybrid(file=" + + quote(path) + ",source_table='sensor',directory=" + + quote(directory_ + "-continuation") + ")")); + EXPECT_EQ(SQLITE_CONSTRAINT, + exec_raw("INSERT INTO continuation VALUES(2,'different',9)")); + ASSERT_EQ(SQLITE_OK, exec("INSERT INTO continuation VALUES(3,NULL,3.5)")); + ASSERT_EQ(SQLITE_OK, + exec("UPDATE continuation SET temperature=4.5 WHERE time=3")); + EXPECT_EQ(3, count("SELECT count(*) FROM continuation")); + EXPECT_EQ(SQLITE_READONLY, + exec_raw("UPDATE OR IGNORE continuation SET temperature=7")); + EXPECT_EQ(1, count("SELECT count(*) FROM continuation WHERE time=3 AND " + "temperature=4.5")); + EXPECT_EQ(SQLITE_READONLY, + exec_raw("DELETE FROM continuation WHERE time=1")); + ASSERT_EQ(SQLITE_OK, exec("SELECT tsfile_seal('main.continuation',4)")); + EXPECT_EQ(3, count("SELECT count(*) FROM continuation")); + EXPECT_EQ(1, count("SELECT count(*) FROM continuation WHERE time=3 AND " + "device IS NULL")); +} + +TEST_F(TsFileSqliteTest, SourceValidationAndPreserveUnknownFiles) { + ASSERT_EQ(SQLITE_OK, exec("INSERT INTO sensor VALUES(1,'d0',1.5)")); + ASSERT_EQ(SQLITE_OK, exec("SELECT tsfile_seal('main.sensor',2)")); + std::string path = source_file(); + EXPECT_NE(SQLITE_OK, + exec_raw("CREATE VIRTUAL TABLE bad USING tsfile_hybrid(file=" + + quote(path) + ",source_table='missing')")); + EXPECT_NE(SQLITE_OK, + exec_raw("CREATE VIRTUAL TABLE bad USING tsfile_hybrid(file=" + + quote(path) + + ",source_table='sensor', timestamp_precision='ns')")); + EXPECT_NE(SQLITE_OK, exec_raw("CREATE VIRTUAL TABLE bad USING " + "tsfile_hybrid(time TIMESTAMP TIME,file=" + + quote(path) + ",source_table='sensor')")); + const std::string unknown = directory_ + "/unrelated.tsfile"; + { + std::ofstream file(unknown); + file << "owned by caller"; + } + ASSERT_EQ(SQLITE_OK, exec("SELECT tsfile_seal('main.sensor',3)")); + EXPECT_EQ(0, access(unknown.c_str(), F_OK)); +} + +TEST_F(TsFileSqliteTest, ManagementSealRespectsTransactionsAndSavepoints) { + ASSERT_EQ( + SQLITE_OK, + exec("INSERT INTO sensor VALUES(1,'d0',1),(2,NULL,2),(3,'d0',3)")); + ASSERT_EQ(SQLITE_OK, exec("BEGIN")); + EXPECT_EQ(1, count("SELECT tsfile_seal('main.sensor',2)")); + EXPECT_EQ(3, count("SELECT count(*) FROM sensor")); + ASSERT_EQ(SQLITE_OK, exec("SAVEPOINT outer_point")); + EXPECT_EQ(1, count("SELECT tsfile_seal('main.sensor',3)")); + ASSERT_EQ(SQLITE_OK, exec("SAVEPOINT inner_point")); + EXPECT_EQ(1, count("SELECT tsfile_seal('main.sensor',4)")); + ASSERT_EQ(SQLITE_OK, exec("ROLLBACK TO outer_point")); + EXPECT_EQ(2, + count("SELECT hot_rows FROM tsfile_table_info('main.sensor')")); + EXPECT_EQ(2, + count("SELECT watermark FROM tsfile_table_info('main.sensor')")); + ASSERT_EQ(SQLITE_OK, exec("RELEASE outer_point")); + ASSERT_EQ(SQLITE_OK, exec("ROLLBACK")); + EXPECT_EQ(3, + count("SELECT hot_rows FROM tsfile_table_info('main.sensor')")); + EXPECT_EQ(3, count("SELECT tsfile_seal('main.sensor',4)")); + EXPECT_EQ(0, + count("SELECT hot_rows FROM tsfile_table_info('main.sensor')")); + EXPECT_EQ(3, count("SELECT count(*) FROM sensor")); + EXPECT_EQ(1, count("SELECT count(*) FROM tsfile_verify('main.sensor') " + "WHERE status='OK'")); +} + +TEST_F(TsFileSqliteTest, ExportAutomaticallySealsAndRoundTrips) { + ASSERT_EQ(SQLITE_OK, + exec("INSERT INTO sensor VALUES(1,NULL,1.5),(2,'d0',2.5)")); + const std::string output = directory_ + "-export"; + EXPECT_EQ( + 1, count("SELECT tsfile_export('main.sensor'," + quote(output) + ")")); + EXPECT_EQ(0, + count("SELECT hot_rows FROM tsfile_table_info('main.sensor')")); + EXPECT_EQ(3, + count("SELECT watermark FROM tsfile_table_info('main.sensor')")); + EXPECT_EQ(2, count("SELECT count(*) FROM sensor")); + ASSERT_EQ(SQLITE_OK, + exec("CREATE VIRTUAL TABLE exported USING tsfile_hybrid(file=" + + quote(output + "/part-000001.tsfile") + + ",source_table='sensor')")); + EXPECT_EQ(2, count("SELECT count(*) FROM exported")); + EXPECT_EQ(1, count("SELECT count(*) FROM exported WHERE device IS NULL AND " + "temperature=1.5")); + EXPECT_EQ(SQLITE_READONLY, exec_raw("UPDATE sensor SET temperature=9")); + ASSERT_EQ(SQLITE_OK, exec("INSERT INTO sensor VALUES(3,'d0',3.5)")); + EXPECT_EQ(2, count("SELECT count(*) FROM exported")); + EXPECT_NE(SQLITE_OK, exec_raw("SELECT tsfile_export('main.sensor'," + + quote(output) + ")")); + EXPECT_EQ(1, + count("SELECT hot_rows FROM tsfile_table_info('main.sensor')")); + ASSERT_EQ(SQLITE_OK, exec("BEGIN")); + EXPECT_NE(SQLITE_OK, exec_raw("SELECT tsfile_export('main.sensor'," + + quote(output + "-txn") + ")")); + ASSERT_EQ(SQLITE_OK, exec("ROLLBACK")); +} + +TEST_F(TsFileSqliteTest, DiagnosticsAndInt64Exhaustion) { + EXPECT_EQ("writable", + scalar_text("SELECT mode FROM tsfile_table_info('main.sensor')")); + ASSERT_EQ(SQLITE_OK, + exec("INSERT INTO sensor VALUES(9223372036854775807,'d0',1)")); + EXPECT_EQ(1, count("SELECT tsfile_export('main.sensor'," + + quote(directory_ + "-max") + ")")); + EXPECT_EQ( + 0, + count("SELECT append_available FROM tsfile_table_info('main.sensor')")); + EXPECT_EQ( + 1, + count( + "SELECT watermark IS NULL FROM tsfile_table_info('main.sensor')")); + EXPECT_EQ(1, count("SELECT count(*) FROM sensor")); + EXPECT_EQ( + SQLITE_CONSTRAINT, + exec_raw("INSERT INTO sensor VALUES(9223372036854775807,'new',2)")); + EXPECT_EQ(1, count("SELECT tsfile_export('main.sensor'," + + quote(directory_ + "-again") + ")")); + const auto path = source_file(); + ASSERT_EQ(0, unlink(path.c_str())); + EXPECT_EQ(1, count("SELECT count(*) FROM tsfile_verify('main.sensor') " + "WHERE status='MISSING'")); + EXPECT_NE(SQLITE_OK, exec_raw("SELECT * FROM sensor")); +} + +TEST_F(TsFileSqliteTest, DirectoryOwnershipAndCreationRollback) { + std::string args = + "(time TIMESTAMP TIME,value DOUBLE " + "FIELD,timestamp_precision='ms',directory="; + EXPECT_NE(SQLITE_OK, + exec_raw("CREATE VIRTUAL TABLE clash USING tsfile_hybrid" + args + + quote(directory_) + ")")); + EXPECT_NE(SQLITE_OK, + exec_raw("CREATE VIRTUAL TABLE nested USING tsfile_hybrid" + + args + quote(directory_ + "/nested") + ")")); + std::string rollback_dir = directory_ + "-rollback"; + ASSERT_EQ(SQLITE_OK, exec("BEGIN")); + ASSERT_EQ(SQLITE_OK, + exec("CREATE VIRTUAL TABLE aborted USING tsfile_hybrid" + args + + quote(rollback_dir) + ")")); + ASSERT_EQ(SQLITE_OK, exec("ROLLBACK")); + EXPECT_NE(0, access(rollback_dir.c_str(), F_OK)); + ASSERT_EQ(SQLITE_OK, exec("CREATE TABLE \"collision_tsfile$config\"(x)")); + EXPECT_NE(SQLITE_OK, + exec_raw("CREATE VIRTUAL TABLE collision USING tsfile_hybrid" + + args + quote(directory_ + "-collision") + ")")); + EXPECT_EQ( + 1, + count("SELECT count(*) FROM " + "pragma_table_info('collision_tsfile$config') WHERE name='x'")); + EXPECT_NE(0, access((directory_ + "-collision").c_str(), F_OK)); +} + +TEST_F(TsFileSqliteTest, RawMultiTablePrecisionAndExportIsolation) { + std::string path = directory_ + "-raw.tsfile"; + storage::TsFileWriter writer; + ASSERT_EQ(common::E_OK, writer.open(path)); + writer.set_generate_table_schema(false); + for (const std::string name : {"selected", "unrelated", "empty"}) { + std::vector<common::ColumnSchema> columns = { + common::ColumnSchema("device", common::STRING, + common::ColumnCategory::TAG), + common::ColumnSchema("value", common::DOUBLE, + common::ColumnCategory::FIELD)}; + auto schema = std::make_shared<storage::TableSchema>(name, columns); + ASSERT_EQ(common::E_OK, writer.register_table(schema)); + if (name == "empty") continue; + storage::Tablet tablet(name, schema->get_measurement_names(), + schema->get_data_types(), + schema->get_column_categories(), 2); + ASSERT_EQ(common::E_OK, + tablet.add_timestamp(0, name == "selected" ? 10 : 1000)); + ASSERT_EQ(common::E_OK, tablet.add_value(0, "device", "d0")); + ASSERT_EQ(common::E_OK, tablet.add_value(0, "value", 1.5)); + ASSERT_EQ(common::E_OK, writer.write_table(tablet)); + } + ASSERT_EQ(common::E_OK, writer.flush()); + ASSERT_EQ(common::E_OK, writer.close()); + std::string options = "file=" + quote(path) + ",source_table='selected'"; + ASSERT_EQ(SQLITE_OK, exec("CREATE VIRTUAL TABLE raw USING tsfile_hybrid(" + + options + ")")); + EXPECT_EQ( + "unknown", + scalar_text( + "SELECT timestamp_precision FROM tsfile_table_info('main.raw')")); + EXPECT_NE( + SQLITE_OK, + exec_raw("CREATE VIRTUAL TABLE missing_precision USING tsfile_hybrid(" + + options + ",directory=" + + quote(directory_ + "-missing-precision") + ")")); + ASSERT_EQ(SQLITE_OK, + exec("CREATE VIRTUAL TABLE renamed USING tsfile_hybrid(" + + options + ",timestamp_precision='ms',directory=" + + quote(directory_ + "-renamed") + ")")); + EXPECT_EQ(11, + count("SELECT watermark FROM tsfile_table_info('main.renamed')")); + ASSERT_EQ(SQLITE_OK, exec("INSERT INTO renamed VALUES(11,NULL,2.5)")); + EXPECT_EQ(1, count("SELECT tsfile_export('main.renamed'," + + quote(directory_ + "-selected-export") + ")")); + storage::TsFileReader reader; + ASSERT_EQ(common::E_OK, + reader.open(directory_ + "-selected-export/part-000001.tsfile")); + EXPECT_EQ(1, reader.get_all_table_schemas().size()); + EXPECT_NE(nullptr, reader.get_table_schema("renamed")); + EXPECT_EQ(nullptr, reader.get_table_schema("unrelated")); + reader.close(); + ASSERT_EQ( + SQLITE_OK, + exec("CREATE VIRTUAL TABLE empty_source USING tsfile_hybrid(file=" + + quote(path) + + ",source_table='empty',timestamp_precision='ms',directory=" + + quote(directory_ + "-empty") + ")")); + EXPECT_EQ(0, count("SELECT count(*) FROM empty_source")); + EXPECT_EQ(0, count("SELECT tsfile_export('main.empty_source'," + + quote(directory_ + "-empty-output") + ")")); + ASSERT_EQ( + SQLITE_OK, + exec("INSERT INTO empty_source VALUES(-9223372036854775808,NULL,1)")); + EXPECT_EQ(1, count("SELECT tsfile_seal('main.empty_source',0)")); +} + +TEST_F(TsFileSqliteTest, PersistentReconnectAndConcurrentWatermark) { + const std::string dbpath = directory_ + "-persistent.db"; + ASSERT_EQ(SQLITE_OK, exec("ATTACH " + quote(dbpath) + " AS persisted")); + ASSERT_EQ( + SQLITE_OK, + exec("CREATE VIRTUAL TABLE persisted.data USING tsfile_hybrid(time " + "TIMESTAMP TIME,value DOUBLE FIELD,directory=" + + quote(directory_ + "-persisted") + ",timestamp_precision='ms')")); + ASSERT_EQ(SQLITE_OK, + exec("INSERT INTO persisted.data VALUES(1,1.5),(2,2.5)")); + EXPECT_EQ(1, count("SELECT tsfile_seal('persisted.data',2)")); + sqlite3* other = nullptr; + ASSERT_EQ(SQLITE_OK, sqlite3_open(dbpath.c_str(), &other)); + sqlite3_enable_load_extension(other, 1); + ASSERT_EQ(SQLITE_OK, + sqlite3_load_extension(other, TSFILE_SQLITE_EXTENSION_PATH, + nullptr, nullptr)); + ASSERT_EQ(SQLITE_OK, sqlite3_exec(other, "SELECT * FROM data", nullptr, + nullptr, nullptr)); + EXPECT_EQ(1, count("SELECT tsfile_seal('persisted.data',3)")); + EXPECT_EQ(SQLITE_CONSTRAINT, + sqlite3_exec(other, "INSERT INTO data VALUES(2,8)", nullptr, + nullptr, nullptr)); + ASSERT_EQ(SQLITE_OK, sqlite3_exec(other, "INSERT INTO data VALUES(3,3.5)", + nullptr, nullptr, nullptr)); + EXPECT_EQ(3, count("SELECT count(*) FROM persisted.data")); + sqlite3_close(other); + ASSERT_EQ(SQLITE_OK, exec("DETACH persisted")); + ASSERT_EQ(SQLITE_OK, exec("ATTACH " + quote(dbpath) + " AS persisted")); + EXPECT_EQ(3, count("SELECT count(*) FROM persisted.data")); + EXPECT_EQ( + 1, count("SELECT hot_rows FROM tsfile_table_info('persisted.data')")); + EXPECT_EQ( + 3, count("SELECT watermark FROM tsfile_table_info('persisted.data')")); +} + +TEST_F(TsFileSqliteTest, ExportFailureRetainsCommittedSealAndManagementGuards) { + ASSERT_EQ(SQLITE_OK, exec("INSERT INTO sensor VALUES(1,'d0',1)")); + EXPECT_NE(SQLITE_OK, + exec_raw("SELECT tsfile_seal('main.sensor',2) FROM sensor")); + EXPECT_EQ(1, + count("SELECT hot_rows FROM tsfile_table_info('main.sensor')")); + const std::string output = "/tmp/" + std::string(240, 'x'); + EXPECT_NE(SQLITE_OK, exec_raw("SELECT tsfile_export('main.sensor'," + + quote(output) + ")")); + EXPECT_EQ(0, + count("SELECT hot_rows FROM tsfile_table_info('main.sensor')")); + EXPECT_EQ(1, count("SELECT count(*) FROM sensor")); + EXPECT_NE(0, access(output.c_str(), F_OK)); + EXPECT_EQ(1, count("SELECT tsfile_export('main.sensor'," + + quote(directory_ + "-retry") + ")")); +} + +TEST_F(TsFileSqliteTest, NoTagSealingAndWholeStatementColdFailures) { + ASSERT_EQ( + SQLITE_OK, + exec("CREATE VIRTUAL TABLE notags USING tsfile_hybrid(time TIMESTAMP " + "TIME,value DOUBLE FIELD,directory=" + + quote(directory_ + "-notags") + ",timestamp_precision='ms')")); + ASSERT_EQ(SQLITE_OK, exec("INSERT INTO notags VALUES(1,1.5),(2,2.5)")); + EXPECT_EQ(1, count("SELECT tsfile_seal('main.notags',2)")); + EXPECT_EQ(2, count("SELECT count(*) FROM notags")); + EXPECT_EQ(SQLITE_READONLY, exec_raw("UPDATE OR FAIL notags SET value=9")); + EXPECT_EQ(1, + count("SELECT count(*) FROM notags WHERE time=2 AND value=2.5")); + EXPECT_EQ(SQLITE_READONLY, exec_raw("DELETE FROM notags")); + EXPECT_EQ(2, count("SELECT count(*) FROM notags")); + EXPECT_EQ(SQLITE_CONSTRAINT, + exec_raw("INSERT INTO notags VALUES(3,3.5),(2,8)")); + EXPECT_EQ(2, count("SELECT count(*) FROM notags")); + EXPECT_EQ(0, count("SELECT tsfile_seal('main.notags',2)")); + EXPECT_EQ(1, count("SELECT tsfile_seal('main.notags',4)")); + EXPECT_EQ(0, count("SELECT tsfile_seal('main.notags',5)")); + EXPECT_EQ(5, + count("SELECT watermark FROM tsfile_table_info('main.notags')")); +} + +TEST_F(TsFileSqliteTest, SourceSchemaPersistsWhenFileBecomesUnavailable) { + ASSERT_EQ(SQLITE_OK, exec("INSERT INTO sensor VALUES(1,'d0',1)")); + EXPECT_EQ(1, count("SELECT tsfile_seal('main.sensor',2)")); + const std::string path = source_file(); + const std::string dbpath = directory_ + "-source.db"; + ASSERT_EQ(SQLITE_OK, exec("ATTACH " + quote(dbpath) + " AS persisted")); + ASSERT_EQ( + SQLITE_OK, + exec("CREATE VIRTUAL TABLE persisted.source USING tsfile_hybrid(file=" + + quote(path) + ",source_table='sensor',directory=" + + quote(directory_ + "-source-hot") + ")")); + ASSERT_EQ(SQLITE_OK, exec("INSERT INTO persisted.source VALUES(2,NULL,2)")); + ASSERT_EQ(SQLITE_OK, exec("DETACH persisted")); + ASSERT_EQ(0, rename(path.c_str(), (path + ".moved").c_str())); + ASSERT_EQ(SQLITE_OK, exec("ATTACH " + quote(dbpath) + " AS persisted")); + EXPECT_EQ( + 3, + count("SELECT count(*) FROM pragma_table_info('source','persisted')")); + EXPECT_EQ(1, count("SELECT count(*) FROM tsfile_verify('persisted.source') " + "WHERE status='MISSING'")); + EXPECT_NE(SQLITE_OK, exec_raw("SELECT * FROM persisted.source")); + EXPECT_EQ( + 1, count("SELECT hot_rows FROM tsfile_table_info('persisted.source')")); + ASSERT_EQ(0, rename((path + ".moved").c_str(), path.c_str())); + EXPECT_EQ(2, count("SELECT count(*) FROM persisted.source")); +} + +TEST_F(TsFileSqliteTest, QuotedSchemaAndRejectedEmptyPrecision) { + ASSERT_EQ( + SQLITE_OK, + exec("CREATE VIRTUAL TABLE \"odd.table\" USING tsfile_hybrid(\"event " + "time\" TIMESTAMP TIME,\"sensor value\" DOUBLE FIELD,directory=" + + quote(directory_ + "-quoted") + ",timestamp_precision='ms')")); + ASSERT_EQ(SQLITE_OK, exec("INSERT INTO \"odd.table\" VALUES(1,3.5)")); + EXPECT_EQ(1, count("SELECT tsfile_export('main.\"odd.table\"'," + + quote(directory_ + "-quoted-output") + ")")); + ASSERT_EQ(SQLITE_OK, + exec("CREATE VIRTUAL TABLE inferred USING tsfile_hybrid(file=" + + quote(directory_ + "-quoted-output/part-000001.tsfile") + + ",source_table='odd.table')")); + EXPECT_EQ(1, count("SELECT count(*) FROM inferred WHERE \"event time\"=1 " + "AND \"sensor value\"=3.5")); + EXPECT_NE( + SQLITE_OK, + exec_raw("CREATE VIRTUAL TABLE invalid USING tsfile_hybrid(file=" + + quote(directory_ + "-quoted-output/part-000001.tsfile") + + ",source_table='odd.table',timestamp_precision='')")); +} + +TEST_F(TsFileSqliteTest, BusinessColumnsDoNotCarryManagementCommands) { + ASSERT_EQ( + SQLITE_OK, + exec("CREATE VIRTUAL TABLE commands USING tsfile_hybrid(time TIMESTAMP " + "TIME,_tsfile_command STRING FIELD,directory=" + + quote(directory_ + "-commands") + ",timestamp_precision='ms')")); + ASSERT_EQ(SQLITE_OK, exec("INSERT INTO commands VALUES(1,'seal')")); + EXPECT_EQ( + 1, count("SELECT count(*) FROM commands WHERE _tsfile_command='seal'")); + EXPECT_EQ(2, count("SELECT count(*) FROM pragma_table_xinfo('commands')")); + EXPECT_EQ(1, count("SELECT tsfile_seal('main.commands',2)")); +} + +TEST_F(TsFileSqliteTest, RowidNamedColumnsAndBooleanValues) { + ASSERT_EQ( + SQLITE_OK, + exec("CREATE VIRTUAL TABLE aliases USING tsfile_hybrid(time TIMESTAMP " + "TIME,rowid INT64 FIELD,oid INT64 FIELD,_rowid_ INT64 FIELD,flag " + "BOOLEAN FIELD,directory=" + + quote(directory_ + "-aliases") + ",timestamp_precision='ms')")); + ASSERT_EQ(SQLITE_OK, exec("INSERT INTO aliases " + "VALUES(1,-1,88,77,4294967296),(2,-1,55,44,0)")); + EXPECT_EQ(1, count("SELECT count(*) FROM aliases WHERE time=1 AND flag=1")); + ASSERT_EQ(SQLITE_OK, exec("UPDATE aliases SET rowid=999 WHERE time=1")); + EXPECT_EQ(1, count("SELECT count(*) FROM aliases WHERE time=1 AND " + "rowid=999 AND oid=88 AND _rowid_=77")); + ASSERT_EQ(SQLITE_OK, exec("DELETE FROM aliases WHERE time=2")); + EXPECT_EQ(1, count("SELECT count(*) FROM aliases")); + EXPECT_EQ(1, count("SELECT tsfile_seal('main.aliases',3)")); + EXPECT_EQ(1, count("SELECT count(*) FROM aliases WHERE time=1 AND " + "rowid=999 AND flag=1")); +} + +TEST_F(TsFileSqliteTest, CopiedDatabaseCannotShareWritableDirectory) { + const std::string original = directory_ + "-original.db", + copy = directory_ + "-copy.db"; + ASSERT_EQ(SQLITE_OK, exec("ATTACH " + quote(original) + " AS owner")); + ASSERT_EQ( + SQLITE_OK, + exec("CREATE VIRTUAL TABLE owner.data USING tsfile_hybrid(time " + "TIMESTAMP TIME,value DOUBLE FIELD,directory=" + + quote(directory_ + "-owned") + ",timestamp_precision='ms')")); + ASSERT_EQ(SQLITE_OK, exec("INSERT INTO owner.data VALUES(1,1.5)")); + ASSERT_EQ(SQLITE_OK, exec("DETACH owner")); + { + std::ifstream in(original, std::ios::binary); + std::ofstream out(copy, std::ios::binary); + out << in.rdbuf(); + } + ASSERT_EQ(SQLITE_OK, exec("ATTACH " + quote(copy) + " AS cloned")); + EXPECT_NE(SQLITE_OK, exec_raw("INSERT INTO cloned.data VALUES(2,2.5)")); + ASSERT_EQ(SQLITE_OK, exec("DETACH cloned")); + ASSERT_EQ(SQLITE_OK, + exec("ATTACH " + quote(original) + " AS renamed_schema")); + ASSERT_EQ(SQLITE_OK, exec("INSERT INTO renamed_schema.data VALUES(2,2.5)")); + EXPECT_EQ(2, count("SELECT count(*) FROM renamed_schema.data")); +} + +TEST_F(TsFileSqliteTest, RollbackPastTableCreationPreservesOuterTransaction) { + ASSERT_EQ(SQLITE_OK, exec("CREATE TABLE keep(value)")); + ASSERT_EQ(SQLITE_OK, exec("BEGIN")); + ASSERT_EQ(SQLITE_OK, exec("INSERT INTO keep VALUES(42)")); + ASSERT_EQ(SQLITE_OK, exec("SAVEPOINT creation")); + ASSERT_EQ( + SQLITE_OK, + exec("CREATE VIRTUAL TABLE transient USING tsfile_hybrid(time " + "TIMESTAMP TIME,value DOUBLE FIELD,directory=" + + quote(directory_ + "-transient") + ",timestamp_precision='ms')")); + ASSERT_EQ(SQLITE_OK, exec("INSERT INTO transient VALUES(1,1.5)")); + EXPECT_EQ(1, count("SELECT tsfile_seal('main.transient',2)")); + ASSERT_EQ(SQLITE_OK, exec("ROLLBACK TO creation")); + ASSERT_EQ(SQLITE_OK, exec("RELEASE creation")); + ASSERT_EQ(SQLITE_OK, exec("COMMIT")); + EXPECT_EQ(42, count("SELECT value FROM keep")); + EXPECT_EQ( + 0, count("SELECT count(*) FROM sqlite_master WHERE name='transient'")); + EXPECT_NE(0, access((directory_ + "-transient").c_str(), F_OK)); +} + +TEST_F(TsFileSqliteTest, CommitNeverOverwritesExistingDirectoryEntries) { + ASSERT_EQ(SQLITE_OK, exec("INSERT INTO sensor VALUES(1,'d0',1.5)")); + ASSERT_EQ(SQLITE_OK, exec("BEGIN")); + EXPECT_EQ(1, count("SELECT tsfile_seal('main.sensor',2)")); + std::string final; + DIR* dir = opendir(directory_.c_str()); + ASSERT_NE(nullptr, dir); + while (dirent* e = readdir(dir)) { + std::string name = e->d_name; + if (name.size() > 4 && name.substr(name.size() - 4) == ".tmp") + final = + directory_ + "/" + name.substr(0, name.size() - 4) + ".tsfile"; + } + closedir(dir); + ASSERT_FALSE(final.empty()); + ASSERT_EQ(0, symlink((directory_ + "-missing").c_str(), final.c_str())); + EXPECT_NE(SQLITE_OK, exec_raw("COMMIT")); + struct stat st {}; + ASSERT_EQ(0, lstat(final.c_str(), &st)); + EXPECT_TRUE(S_ISLNK(st.st_mode)); + EXPECT_EQ( + 1, + count("SELECT count(*) FROM sensor WHERE time=1 AND temperature=1.5")); + EXPECT_EQ(1, + count("SELECT hot_rows FROM tsfile_table_info('main.sensor')")); +} + +TEST_F(TsFileSqliteTest, SealPreservesPreexistingTemporarySymlink) { + const std::string temporary = + directory_ + "/tsfilesensor-" + std::to_string(getpid()) + "-0.tmp"; + ASSERT_EQ(0, symlink((directory_ + "-missing").c_str(), temporary.c_str())); + ASSERT_EQ(SQLITE_OK, exec("INSERT INTO sensor VALUES(1,'d0',1.5)")); + EXPECT_EQ(1, count("SELECT tsfile_seal('main.sensor',2)")); + struct stat st {}; + ASSERT_EQ(0, lstat(temporary.c_str(), &st)); + EXPECT_TRUE(S_ISLNK(st.st_mode)); + EXPECT_EQ(1, count("SELECT count(*) FROM sensor")); } } // namespace