marsmodule/MarsmoduleDB

# MarsmoduleDB — 兼容 SQLite 的 Rust 数据库引擎与 VDD 验证项目 MarsmoduleDB 是一个实验性的、独立且兼容 SQLite 的嵌入式数据库 引擎，完全使用纯 Rust 编写：不包含 C，没有 FFI，全局使用 `#![forbid(unsafe_code)]`， 实现字节兼容的文件 I/O，并提供对 cargo 友好的嵌入 API。已发布的 crate 名为 `sqlite-rust`。 它同时也是使用 Marsmodule VDD Plugin 工作流进行基于 agent 团队的软件开发的一个公开验证项目。该项目刻意追求真实而非玩具级别的规模：其存储、SQL 执行、兼容性测试、Fuzzing、 文档以及审查证据均是在一个要求严苛的数据库系统上开发出来的。 ## 项目存在的原因 MarsmoduleDB 有三个目标： 1. 使用 纯 Rust 构建一个实用、自由授权且兼容 SQLite 的嵌入式数据库引擎。 2. 在一个严肃且长期运行的软件系统（而非玩具示例）上， 验证基于 agent 团队的开发模式。 3. 发布透明的工程证据——兼容性测试、Fuzzing、 架构说明和局限性——使得该开发方法能够通过真实的工件来评估。 ## 项目状态一览 | 领域 | 当前状态 | | --- | --- | | 核心存储 | 兼容 SQLite 的 page/header/record 处理，B-tree 读取，多级 INSERT 增长，溢出页，freelist 重用，rollback journal 恢复 | | SQL 执行 | SELECT, joins, aggregates, subqueries, CTEs, window functions, 标量/数学函数，type affinity，collation 支持 | | DML 和 DDL | INSERT, UPDATE, DELETE, CREATE/DROP TABLE, CREATE/DROP INDEX, CREATE/DROP VIEW, ALTER TABLE ADD COLUMN, constraints, 冲突处理, UPSERT | | 兼容性测试 | 写入操作与 `/usr/bin/sqlite3 3.45.1` 进行差异化测试，并使用 `PRAGMA integrity_check` 进行验证 | | Fuzzing | `cargo-fuzz` 目标覆盖 SQL 解析和兼容 SQLite 的字节解码；崩溃会被转化为稳定的回归测试 | | 生产就绪性 | 实验性。在生产使用前，请评估兼容性、持久性、并发性和安全性需求 | | VDD 验证价值 | 为 Marsmodule VDD Plugin agent 团队工作流提供真实世界的验证目标 | ## 状态 一个纯 Rust、二进制兼容的 SQLite 文件格式移植版本——没有 C，没有 FFI， 全局使用 `#![forbid(unsafe_code)]`。每一次写入都会与 真实的 `/usr/bin/sqlite3 3.45.1` 进行差异化测试，并使用 `PRAGMA integrity_check` 进行验证。 当前功能： **文件格式与存储** - Page 级 I/O（Pager，LRU page 缓存）；字节兼容的数据库 header 和 record - B-tree 叶子节点 + 内部节点读取；有序的多叶表扫描 - INSERT 时的多级 B-tree 增长（通过追加式 spine 平衡实现任意深度/大小） - 超大 TEXT/BLOB 的溢出页；freelist 页面重用 - 带有崩溃恢复的 Rollback journal —— `BEGIN` / `COMMIT` / `ROLLBACK` 事务 **SQL —— 查询 (SELECT)** - 投影；带有布尔三值逻辑（`AND` / `OR` / `NOT` / 括号）的 `WHERE` - `ORDER BY`（多键，ASC/DESC）；`LIMIT` / `OFFSET` - `GROUP BY` / `HAVING`；聚合函数（`COUNT` / `SUM` / `MIN` / `MAX` / `AVG` / `group_concat`） - `INNER JOIN` 和 `LEFT [OUTER] JOIN`；FROM/JOIN 表别名（`FROM t [AS] x`、`JOIN u [AS] y`）以及自连接 - 标量子查询；基于字面量列表或子查询的 `IN` / `NOT IN` - 派生表 —— `FROM`/`JOIN` 中的子查询（`FROM (SELECT …) [AS] x`），带有可选别名 - 非递归和递归 CTE（`WITH [RECURSIVE] … AS (SELECT …) SELECT …`） - Window 函数 —— `ROW_NUMBER()`、`RANK()`、`DENSE_RANK()`，以及带有 `OVER ([PARTITION BY …] [ORDER BY …])` 和默认 frame 的聚合 window 函数（`COUNT`/`SUM`/`MIN`/`MAX`/`AVG`）；显式 frame、LAG/LEAD/NTILE、命名 windows、FILTER 暂缓支持 **SQL —— DML 和 DDL** - `INSERT` —— 多行，列 `DEFAULT`，`INTEGER PRIMARY KEY` rowid 别名 - 带有增量索引维护的 `UPDATE` 和 `DELETE` - `CREATE TABLE` / `DROP TABLE`；`CREATE [UNIQUE] INDEX` / `DROP INDEX` - `CREATE VIEW` / `DROP VIEW`；`ALTER TABLE ADD COLUMN` - `CREATE VIRTUAL TABLE … USING vec0(embedding float[N])` —— 原生、纯 Rust 的 `vec0` 向量存储：float32 向量以小端序 IEEE-754 `f32[N]` BLOB 形式存储在底层的 rowid 表中；支持 `INSERT`（JSON `'[…]'` 文本或 `f32` BLOB）、`DELETE`（包括重新索引形式 `DELETE FROM v WHERE rowid IN (SELECT … )`）以及读回，其行为与真实的 `sqlite-vec` 0.1.9 扩展相匹配 - vec0 KNN 搜索 —— `SELECT rowid, distance FROM v WHERE embedding MATCH '[…]' AND k = ?` 返回按欧几里得 (L2) 距离升序排列的 `k` 个最近向量，并作为 `distance` 列呈现，其行为与 `sqlite-vec` 0.1.9 相匹配（L2 kernel 使用融合乘加运算以实现与基准预言机完全一致的比特级对齐）。查询向量可以是 JSON `'[…]'` 文本、`f32` BLOB 或 `?` 参数；约束可以是 `k = ?` 或 `LIMIT`；如果 `MATCH` 缺少这两者，则会产生类型化的错误。支持 rowid 连接 `… FROM v JOIN chunks c ON c.id = v.rowid ORDER BY v.distance`。维度不匹配或格式错误的查询向量会产生类型化的 `ExecError`，而绝不会引发 panic - **Vector Phase 1 —— 已完成。** 真实消费者 的端到端 vec0 索引 → 搜索 → 重新索引 → 搜索工作负载在 MarsmoduleDB 上与 `sqlite-vec` 0.1.9 预言机完全收敛：schema 创建、在单个事务中扩展索引、KNN 连接搜索以及基于 `IN (subquery)` 删除的重新索引，全部与预言机实现逐位匹配 (REQ-067) - CHECK constraints —— 列级和表级，在 INSERT/UPDATE 写入前强制执行（NULL/Unknown 通过；仅 FALSE 拒绝；产生类型化的 `CheckConstraintViolation`） - UNIQUE 和 PRIMARY KEY 列/表约束 —— 自动创建 `sqlite_autoindex__` 底层索引并强制执行唯一性（NULL 键豁免；重复项被拒绝并产生类型化的 `UniqueConstraintViolation`） - INSERT 冲突解决 —— `INSERT OR IGNORE`（跳过违反约束的行）和 `INSERT OR REPLACE`（删除冲突行然后插入）；默认使用 OR ABORT - UPSERT —— `INSERT … ON CONFLICT [(target)] DO NOTHING` 和 `DO UPDATE SET col = excluded.col [, …] [WHERE …]`（excluded.* 指代原本应插入的行） **表达式与类型** - 算术运算符（`+ - * / %`，一元 `±`）；`CASE WHEN` - `LIKE` / `GLOB` 模式匹配（带有 `ESCAPE`） - Blob 字面量（`x'HH..'`/`X'HH..'`）以及 `''` 字符串转义合并（单引号字符串中的双引号在 tokenize 阶段会被解码为单个引号） - 标量和数学函数（`COALESCE`、`LENGTH`、`SUBSTR`、`TRIM`、`ABS`、`ROUND`、`printf`/`format`、`last_insert_rowid()` 等）；`CAST` - Type affinity（存储 / 比较 / 读取时）；`COLLATE` (BINARY / NOCASE / RTRIM) **嵌入 API** - `Connection::open` / `execute` / `query`；`Row::get::()` 类型化读取 - `Connection::last_insert_rowid()` —— 该连接上最近一次成功 INSERT 的 rowid（在任何插入之前为 `0`）；同时也是 SQL 函数 `last_insert_rowid()` - `?` 绑定参数 —— 类型化字面量，在构建时即杜绝注入（OWASP A03） **兼容性与测试** - 每次写入均与真实的 `/usr/bin/sqlite3 3.45.1` 进行差异化测试 - 对写入的文件运行 `PRAGMA integrity_check` 预言机；零 mocks **已知局限性** - INSERT 仅为追加式 —— 不支持有序插入；显式指定小于等于当前最大值的 rowid 会被拒绝 - 无 WAL / 并发支持 - 不支持触发器，或常规的约束强制执行（除了 UNIQUE 索引探测、NOT NULL、CHECK constraint 强制执行以及 UNIQUE/PRIMARY KEY 列约束强制执行之外） - 感知 collation 的索引不在范围内（索引键保持 BINARY） 查看 [CHANGELOG.md](CHANGELOG.md) 获取完整的版本历史。 ## Crate 布局 ``` Cargo.toml # workspace manifest crates/sqlite-rust/ Cargo.toml src/ lib.rs # #![forbid(unsafe_code)], re-exports value.rs # Value: the shared decoded-column entity (zero deps; consumed by btree, schema, eval, sql) collation.rs # Collation::{Binary,Nocase,Rtrim} + collate(a,b,c) -> Ordering (BINARY byte compare, NOCASE ASCII-only fold, RTRIM trailing-space strip); Collation::from_name case-insensitive resolver error.rs # Error, PageError, HeaderError, CellError, RecordError, SchemaError, TokenizeError, ParseError, ExecError schema.rs # read_schema → HashMap; read_table_defs → HashMap (rootpage + CREATE TABLE sql) eval/ mod.rs # execute: SelectStmt + Pager → Vec; dispatches to aggregate path (has_aggregate || !group_by.is_empty()), join path (!joins.is_empty()), or non-aggregate single-table path; re-exports execute_insert, execute_delete, execute_update aggregate.rs # aggregate path: validate → collect+WHERE → group → fold (COUNT/SUM/MIN/MAX/AVG/group_concat) → HAVING filter → sort → LIMIT → project join.rs # join path: JoinSchema cross-table resolver (qualified/ambiguous) → nested-loop combine (INNER + LEFT-OUTER NULL-fill) → WHERE → ORDER BY → LIMIT → project over combined rows order.rs # value_order / value_order_collated (SQLite storage-class total order, COLLATE-aware text branch) + sort_rows (stable multi-key ORDER BY pass) predicate.rs # WHERE evaluation: operand interpretation, value comparison matrix, COLLATE selection rule (explicit > column > BINARY) insert.rs # execute_insert: InsertStmt + Pager → i64 rowid; SQLite record + cell encoding, leaf-page mutation; root-type dispatch split.rs # split_leaf_root: depth-1 leaf-page split — interior root + two leaf children delete.rs # execute_delete: DeleteStmt + Pager → usize; depth-0/1 = full-page / K-C-N rebuild via build_leaf; depth≥2 = rebuild-by-reinsert (scan survivors → free non-root pages to freelist → re-insert ascending); frees overflow chains via free_overflow_chain update.rs # execute_update: UpdateStmt + Pager → usize; re-encode matched rows under original rowid, keep unmatched verbatim, rebuild affected leaves via build_leaf; works at any tree depth (rowid immutable); frees/reallocates overflow chains overflow.rs # write_overflow_chain, free_overflow_chain, encode_leaf_cell_maybe_overflow — write/free paths for overflow pages (eval layer) subquery.rs # materialise-once subquery resolver (ADR-024): resolve_where, ResolvedWhere, eval_scalar, in_membership compound.rs # execute_compound: compound SELECT eval (UNION/UNION ALL/INTERSECT/EXCEPT); iterative left-assoc fold; sort_and_dedup for DISTINCT ops; NULL=NULL set-context equality (ADR-REQ-049) cte.rs # CTE resolution: Ctx scope threading, materialize (named derived table), cycle guard (CircularReference) + depth backstop (ASVS V5); iterative fixpoint for recursive terms (materialize_recursive, safety cap RecursiveCteLimit, ASVS V5) function.rs # per-row scalar evaluator: COALESCE/IFNULL (lazy), LENGTH/UPPER/LOWER/SUBSTR/TRIM/LTRIM/RTRIM/TYPEOF (eager), CAST (affinity coercion); SUBSTR bounds-checked (ASVS V5 fail-safe); ABS/ROUND/scalar MAX/MIN/RANDOM (ADR-026): math scalar dispatch, checked_abs overflow guard, round_half_away, std-only PRNG; INSTR, REPLACE, HEX, QUOTE, CHAR, UNICODE, NULLIF, printf/format (SQLite-pinned format-string scanner, shared escape_quotes helper) format/ constants.rs # MAGIC, HEADER_SIZE, EMPTY_LEAF_HEADER header.rs # DatabaseHeader: parse / validate / serialize pager/ io.rs # PageIo: open / read_page / write_page cache.rs # PageCache: LRU, disk_reads() counter sql/ mod.rs # re-exports: tokenize, Token, TokenKind, Keyword, parse, parse_insert, parse_delete, parse_update, BinOp, Expr, Literal, SelectColumn, SelectStmt, OrderByTerm, Direction, AggFunc, AggArg, agg_output_name, HavingExpr, HavingOperand, Subquery, InsertStmt, DeleteStmt, UpdateStmt, CompoundOp, CompoundSelect, parse_compound, parse_compound_with_params token.rs # Token, TokenKind, Keyword types (Keyword includes Insert/Into/Values/Delete/Update/Set) tokenizer.rs # tokenize: single-pass char_indices scanner ast.rs # SelectStmt (with order_by, group_by, joins), JoinSpec, JoinKind, SelectColumn (Star/Expr/Aggregate), AggFunc, AggArg, agg_output_name, OrderByTerm, Direction, Expr, Literal, BinOp, InsertStmt, DeleteStmt, UpdateStmt, CompoundOp, CompoundSelect types parser.rs # parse: &[Token] → Result; parse_insert: &[Token] → Result; parse_delete: &[Token] → Result; parse_update: &[Token] → Result; parse_compound: &[Token] → Result; parse_compound_with_params: &[Token], params → Result btree/ mod.rs # re-exports: Value (from crate::value), Row, LeafPageReader, TableScan, InteriorPageReader, collect_leaf_pages, read_varint varint.rs # read_varint: 1–9 byte SQLite varint decoder record.rs # Row, decode_record: serial-type → Rust value (Value lives in crate::value) leaf.rs # LeafPageReader: 0x0D page buffer → Vec interior.rs # InteriorPageReader: 0x05 page buffer → child page numbers (pure, no I/O) scan.rs # TableScan: ordered multi-leaf iteration via Pager; collect_leaf_pages; scan_from_root overflow.rs # local_payload_len (spec-exact X/M/K formula), read_overflow_chain — read path for overflow pages (btree layer) tests/ req_001_page_io.rs # E2E integration tests (page I/O) req_002_btree_leaf.rs # E2E + unit tests (btree leaf reader) req_003_btree_interior.rs # E2E + adversarial tests (interior traversal) req_004_schema_reader.rs # E2E + adversarial tests (schema reader) req_025_scalar_functions.rs # E2E tests (scalar SQL functions: COALESCE/IFNULL/LENGTH/UPPER/LOWER/SUBSTR/TRIM/TYPEOF/CAST) req_026_math_scalar_functions.rs # E2E tests (math scalar functions: ABS/ROUND/MAX/MIN scalar/RANDOM) req_059_scalar_functions.rs # E2E tests (extended scalar functions: INSTR/REPLACE/HEX/QUOTE/CHAR/UNICODE/NULLIF) req_063_printf_format.rs # E2E differential tests (printf/format: conversions, flags/width/precision, %q/%Q/%w, float fidelity) vs sqlite3 oracle req_060_sql_literals.rs # E2E differential tests (blob literals, '' escape collapse, QUOTE round-trip) req_064_last_insert_rowid.rs # E2E differential tests (last_insert_rowid API + SQL fn: auto/explicit/REPLACE/UPSERT physical-insert gating) vs sqlite3 oracle fixtures/ # real sqlite3-created .db files (btree_test.db, btree_multi.db, btree_deep.db, btree_schema.db) ``` ## 构建 ``` cargo build -p sqlite-rust ``` ## 测试 ``` cargo test -p sqlite-rust --test req_001_page_io cargo test -p sqlite-rust --test req_002_btree_leaf cargo test -p sqlite-rust --test req_003_btree_interior cargo test -p sqlite-rust --test req_004_schema_reader cargo test -p sqlite-rust --test req_005_sql_tokenizer cargo test -p sqlite-rust --test req_006_sql_parser cargo test -p sqlite-rust --test req_007_select_executor cargo test -p sqlite-rust --test req_008_insert_parser cargo test -p sqlite-rust --test req_009_insert_executor cargo test -p sqlite-rust --test req_010_leaf_page_split cargo test -p sqlite-rust --test req_011_delete_executor cargo test -p sqlite-rust --test req_012_update_executor cargo test -p sqlite-rust --test req_013_order_by cargo test -p sqlite-rust --test req_014_aggregates cargo test -p sqlite-rust --test req_015_join cargo test -p sqlite-rust --test req_016_multipage_dml cargo test -p sqlite-rust --test req_017_overflow cargo test -p sqlite-rust --test req_018_create_table cargo test -p sqlite-rust --test req_019_drop_table cargo test -p sqlite-rust --test req_020_transactions cargo test -p sqlite-rust --test req_021_limit_offset cargo test -p sqlite-rust --test req_022_having cargo test -p sqlite-rust --test req_023_embedding_api cargo test -p sqlite-rust --test req_024_subquery cargo test -p sqlite-rust --test req_025_scalar_functions cargo test -p sqlite-rust --test req_026_math_scalar_functions cargo test -p sqlite-rust --test req_027_case_when cargo test -p sqlite-rust --test req_028_like_glob cargo test -p sqlite-rust --test req_059_scalar_functions cargo test -p sqlite-rust --test req_063_printf_format cargo test -p sqlite-rust --test req_060_sql_literals cargo test -p sqlite-rust --test req_064_last_insert_rowid cargo test -p sqlite-rust --test req_065_vec0_storage cargo test -p sqlite-rust --test req_066_vec0_knn cargo test -p sqlite-rust --test req_067_mm_nav_convergence cargo test -p sqlite-rust --test req_029_create_index cargo test -p sqlite-rust --test req_030_create_view cargo test -p sqlite-rust --test req_031_index_maintenance cargo test -p sqlite-rust --test req_032_type_affinity cargo test -p sqlite-rust --test req_033_drop_index cargo test -p sqlite-rust --test req_034_drop_view cargo test -p sqlite-rust --test req_035_collate cargo test -p sqlite-rust --test req_038_boolean_where cargo test -p sqlite-rust --test req_039_in_list cargo test -p sqlite-rust --test req_040_alter_table_add_column cargo test -p sqlite-rust --test req_041_insert_default cargo test -p sqlite-rust --test req_045_arithmetic cargo test -p sqlite-rust --test req_048_fuzz_regressions cargo test -p sqlite-rust --test req_049_compound_select cargo test -p sqlite-rust --test req_050_cte_with cargo test -p sqlite-rust --test req_051_recursive_cte cargo test -p sqlite-rust --test req_052_table_aliases cargo test -p sqlite-rust --test req_053_from_subquery cargo test -p sqlite-rust --test mvp_sqlite_compat cargo test -p sqlite-rust --test phase0_smoke_perf -- --nocapture ``` ### 分阶段差异化冒烟测试 + 性能测试 每个构建阶段都有其专属的差异化冒烟测试，该测试会在同一次运行中，针对真实的 `/usr/bin/sqlite3 3.45.1` 预言机运行截至该阶段已实现的 每一个功能，并测量性能。对于每个工作负载，测试会打印一个 Rust 对比 sqlite3 的比率（`rust_median / sqlite3_median`），并标记任何 慢 10% 以上的操作。阶段 0 对应 `phase0_smoke_perf`；后续阶段复用位于 `tests/support/` 中的共享测试套件。 正确性是一个**硬性门禁**（结果不匹配或 `integrity_check` 失败 均会导致测试失败）。性能信号**目前仅作报告之用** —— 虽然会测量并报告“慢 10% 以内”的目标，但该目标**直到性能阶段 之前都不会强制执行**，且绝不会以牺牲兼容性为代价来追求性能。 报告的比率包含已测量出的 sqlite3 进程启动基准时间，从而使 比较的公平性更加透明（使用 `-- --nocapture` 运行以查看 表格）。 ## 模糊测试 MarsmoduleDB 接收两种不受信任的输入源 —— SQL 字符串和 兼容 SQLite 的数据库字节 —— 而产品的承诺是内存安全： 每一个输入都必须返回 `Ok` 或类型化的 `Err`，绝不会引发 panic、挂起或 无限制的内存分配。一个 [`cargo-fuzz`](https://github.com/rust-fuzz/cargo-fuzz) 通道（`fuzz/`）覆盖了风险最高的四个纯解析/解码路径。 Fuzz crate 被**排除在工作空间之外**（`exclude = ["fuzz"]`）： 上述常规的 stable 通道（`build`、`test`、`clippy`、`fmt`）绝不会编译它，也 绝不需要 nightly 工具链。运行 fuzzer 需要 nightly 工具链以及 `cargo-fuzz`： ``` rustup toolchain install nightly cargo install cargo-fuzz ``` 构建所有目标，然后针对每个目标进行简短的冒烟测试（30 秒）： ``` cargo +nightly fuzz build cargo +nightly fuzz run sql_tokenize_parse -- -max_total_time=30 cargo +nightly fuzz run db_header_parse -- -max_total_time=30 cargo +nightly fuzz run btree_leaf_read -- -max_total_time=30 cargo +nightly fuzz run record_decode -- -max_total_time=30 ``` 每个目标都应用了明确的输入限制（64 KiB 的 SQL 上限；固定的 100 字节 header；≤ 65536 字节的 page；在任何分配之前进行声明长度与 剩余长度的检查），并且同时接受 `Ok` 和类型化的 `Err` —— 只有 panic/挂起/溢出 才会导致运行失败。 相同的对抗性输入会在 **stable** 上通过 `cargo test -p sqlite-rust --test req_048_fuzz_regressions` 进行重放，这也是 在常规 CI 通道中运行的不复发验证；任何被 Fuzzing 活动发现的 崩溃都会被精简并加入到该测试集中。 **执行节奏：** Fuzzing 活动会在每个**阶段结束时**（与 各阶段的差异化冒烟测试 + 性能门禁一起）运行 —— 不会在每次 CI 运行时执行，也不会在 每次 REQ 时执行。 ## Lint 与格式化 ``` cargo clippy -p sqlite-rust --all-targets -- -D warnings cargo fmt --all --check ``` ## 用法 对于嵌入式消费者，推荐的入口点是 `Connection —— 打开一个 真实的 `.db` 文件，然后执行 DML/DDL 的 `execute` 或 `SELECT` 的 `query`。参数通过 `?` 占位符进行绑定，并以 `&[Value]` 的形式提供；绑定的值是类型化 字面量，绝不会拼接到 SQL 中，因此在构建时就杜绝了注入 （OWASP A03）： ``` use std::path::Path; use sqlite_rust::{Connection, Value}; let mut conn = Connection::open(Path::new("my.db"))?; // Parameterized INSERT — `?` placeholders bind the &[Value] in order. let affected = conn.execute( "INSERT INTO users(id, name) VALUES (?, ?)", &[Value::Integer(1), Value::Text("alice".into())], )?; // == 1 // Parameterized SELECT — `Rows` is an iterator of `Row`; `get::` is typed. let rows = conn.query("SELECT id, name FROM users WHERE id = ?", &[Value::Integer(1)])?; // Scalar functions — usable in projection, WHERE, ORDER BY. let rows = conn.query( "SELECT UPPER(name), LENGTH(name), COALESCE(note, 'none') FROM t", &[], )?; for row in rows { let id: i64 = row.get(0)?; let name: String = row.get(1)?; // a NULL column reads back as None via get::> println!("{id} {name}"); } // Math scalar functions — ABS, ROUND, scalar MAX/MIN, RANDOM. // ABS: NULL→NULL; INTEGER stays INTEGER (overflow → ExecError); TEXT/BLOB coerced to numeric. // ROUND: always returns REAL; half-away-from-zero; default digits=0. // MAX(a,b)/MIN(a,b): 2-arg scalar form; NULL if any arg is NULL. // RANDOM(): non-deterministic full-range i64; type verified via TYPEOF. let rows = conn.query( "SELECT ABS(val), ROUND(val, 2), MAX(val, 0.0), RANDOM() FROM t WHERE ABS(val) > 5 ORDER BY ABS(val)", &[], )?; // printf / format — SQLite format-string formatting (format() is a byte-identical // alias). Conversions %d %i %s %x %X %o %f %e %E %g %G %c %q %Q %w %%, with // flags/width/precision (e.g. %05d, %-8.2f, %.3s, %*d). Pinned to sqlite3 output, // not C printf (%c is the first char of the arg-as-text; missing arg = NULL). let rows = conn.query( "SELECT printf('%s=%05d', name, id), format('%.2f', val) FROM t", &[], )?; // CASE WHEN conditional expressions — searched form (condition per branch) and // simple form (subject evaluated once, compared to each WHEN value). // NULL condition → false; NULL subject never matches NULL WHEN; lazy short-circuit. // Usable in SELECT projection, WHERE, ORDER BY, and as function arguments. let rows = conn.query( "SELECT id, CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' WHEN score >= 70 THEN 'C' ELSE 'F' END AS grade FROM students ORDER BY CASE WHEN score IS NULL THEN 1 ELSE 0 END, score DESC", &[], )?; // LIKE / GLOB string pattern matching — value-producing infix operators. // LIKE: case-insensitive ASCII, `%` = any run, `_` = one char, optional ESCAPE. // GLOB: case-sensitive, `*` = any run, `?` = one char, `[a-z]`/`[^0-9]` classes. // NOT LIKE / NOT GLOB negate; a NULL operand yields NULL (not 0). Usable in // SELECT projection, WHERE, and CASE WHEN. Bound `?` patterns stay inert data. let rows = conn.query( "SELECT name FROM files WHERE name LIKE '%.txt' AND name NOT GLOB '[._]*'", &[], )?; let escaped = conn.query( "SELECT 'sale_price' LIKE 'sale\\_price' ESCAPE '\\' FROM t", &[], )?; // Boolean WHERE — OR, unary NOT, and parenthesised groups, with SQLite // precedence (OR < AND < NOT < comparison) and three-valued NULL logic // (a NULL operand is Unknown; NOT (v = 2) excludes NULL v). Drives // SELECT / UPDATE / DELETE. A pure AND-chain stays byte-identical. let picked = conn.query( "SELECT a, b, c FROM t WHERE (a = 1 OR b = 2) AND NOT (c = 9) ORDER BY a, b, c", &[], )?; // COLLATE — choose the text comparison/sort rule. BINARY (default) is byte // compare; NOCASE is case-insensitive over ASCII A-Z; RTRIM ignores trailing // spaces. An explicit operand `COLLATE` overrides a column's declared collation; // a column declared `name TEXT COLLATE NOCASE` applies NOCASE implicitly. let by_name = conn.query( "SELECT name FROM users WHERE name = 'foo' COLLATE NOCASE ORDER BY name COLLATE NOCASE", &[], )?; // CREATE INDEX — builds an on-disk index B-tree (0x0A leaf, depth-1) over a // table, back-filling every existing row in key order. UNIQUE enforces key // uniqueness (NULL keys exempt) on back-fill and on later INSERTs; a colliding // INSERT returns ExecError::UniqueConstraintViolation with the table unchanged. // IF NOT EXISTS on an existing index is a no-op. The file passes the real // sqlite3 `PRAGMA integrity_check`. UPDATE/DELETE on an indexed table now // maintain every index incrementally (REQ-031): a DELETE drops each deleted // row's index entries; an UPDATE moves the entries of indexes whose key changed // (UNIQUE recheck before any write) and skips unchanged-key indexes. conn.execute("CREATE INDEX idx_email ON users (email)", &[])?; conn.execute("CREATE UNIQUE INDEX idx_user_dept ON users (user_id, dept)", &[])?; // DROP INDEX — the inverse of CREATE INDEX (REQ-033): removes the index's // type='index' sqlite_master row by rebuilding page 1's schema leaf, frees its // single 0x0A leaf root page to the SQLite freelist, and bumps the file change // counter + schema cookie like every schema change — leaving the table and any // sibling indexes untouched. IF EXISTS on an absent index is a byte-unchanged // no-op; a missing index without it returns ExecError::UnknownIndex with the // file unchanged. An interior-root (multi-page) index is out of scope and // returns ExecError::NotSupported before any write. Post-drop files pass the // real sqlite3 `PRAGMA integrity_check`. conn.execute("DROP INDEX idx_email", &[])?; conn.execute("DROP INDEX IF EXISTS maybe_missing", &[])?; // CREATE VIEW — stores a named SELECT in sqlite_master (type='view', rootpage=0, // the CREATE VIEW text verbatim). The view owns no b-tree; querying it expands // the stored SELECT at execution time ("view = derived table"), then applies the // outer query's WHERE / ORDER BY / LIMIT / projection. An optional rename list // renames the output columns. IF NOT EXISTS on an existing view is a no-op; a // duplicate without it returns ExecError::ViewAlreadyExists with the file // unchanged. A view over a missing base table is accepted and fails only at // query time (ExecError::UnknownTable); a self-referential view returns // ExecError::ViewRecursion rather than overflowing the stack. The file is // byte-identical to real sqlite3's and passes `PRAGMA integrity_check`. conn.execute("CREATE VIEW active_users AS SELECT name, email FROM users", &[])?; conn.execute("CREATE VIEW dept_names (label) AS SELECT dept FROM users", &[])?; let rows = conn.query("SELECT email FROM active_users WHERE name = ?", &[name])?; // DROP VIEW — the inverse of CREATE VIEW (REQ-034): removes the view's // type='view' sqlite_master row by rebuilding page 1's schema leaf, and bumps // the file change counter + schema cookie like every schema change. A view owns // no B-tree (rootpage=0), so — unlike DROP TABLE/INDEX — no page is freed. Tables // and indexes survive intact. IF EXISTS on an absent view is a byte-unchanged // no-op; a missing view without it returns ExecError::UnknownView with the file // unchanged. Post-drop files pass the real sqlite3 `PRAGMA integrity_check`. conn.execute("DROP VIEW active_users", &[])?; conn.execute("DROP VIEW IF EXISTS maybe_missing", &[])?; // CREATE VIRTUAL TABLE … USING vec0 — the native, pure-Rust vec0 vector store // (REQ-065, phase 1). The vtab's own sqlite_master row mirrors sqlite-vec // (type='table', rootpage=0, verbatim CREATE VIRTUAL TABLE text); the f32[N] // vectors live in a backing rowid table "__vchunks" created through the // ordinary CREATE TABLE path, so integrity_check and close/reopen come for free. // A vector is supplied as a JSON array text ('[…]') or a little-endian f32 BLOB; // both store the identical canonical N·4-byte little-endian f32[N] BLOB, and a // SELECT reads it back byte-for-byte. Dimension mismatch, malformed JSON, a BLOB // whose byte length is not a multiple of 4, an unknown module, or a non-float[N] // column are typed errors (ExecError::VirtualTable / ParseError) — never a panic. conn.execute("CREATE VIRTUAL TABLE vec_chunks USING vec0(embedding float[4])", &[])?; conn.execute("INSERT INTO vec_chunks(rowid, embedding) VALUES (1, '[1,2,3,4]')", &[])?; let rows = conn.query("SELECT embedding FROM vec_chunks", &[])?; // -> the f32[4] BLOB // vec0 has no conflict-replace (matching sqlite-vec 0.1.9): INSERT OR REPLACE / // OR IGNORE / a duplicate INSERT on an existing rowid all error; replace a vector // with DELETE then INSERT. conn.execute("DELETE FROM vec_chunks WHERE rowid = 1", &[])?; // ALTER TABLE ADD COLUMN — the first schema-mutation DDL (REQ-040). Splices the // new column into the verbatim stored CREATE TABLE sql (re-encoding only that one // sqlite_master row, every other cell verbatim) and bumps the change counter + // schema cookie. No existing data page is rewritten: a row stored before the ALTER // is narrower than the new schema, so its missing trailing column is filled at READ // time with the column's DEFAULT (or NULL) — composing with read-affinity (a REAL // DEFAULT 1 reads back as 1.0). COLUMN is optional. PRIMARY KEY / UNIQUE, a // NOT NULL column without a non-NULL DEFAULT, and a duplicate column name are all // rejected before any write (file byte-unchanged); a missing table returns // ExecError::UnknownTable. Files stay byte-faithful — real sqlite3 reads the spliced // schema and passes `PRAGMA integrity_check`. conn.execute("ALTER TABLE users ADD COLUMN age INTEGER DEFAULT 0", &[])?; conn.execute("ALTER TABLE users ADD nickname TEXT", &[])?; // COLUMN keyword optional // INSERT honors column DEFAULT for omitted columns (REQ-041). An INSERT that omits // a column stores that column's DEFAULT constant (integer/real/text/NULL) rather // than always NULL; the injected default receives store-time affinity for free // (REAL DEFAULT 1 stored as 1.0). An omitted column with no DEFAULT stores NULL; an // omitted NOT NULL column with no usable (non-NULL) default returns a typed // ExecError::NotNullConstraintViolation with the file byte-unchanged. So after the // ALTER above, INSERT INTO users(name) VALUES('x') stores age=0 (the DEFAULT), and a // new INSERT now agrees with the read-time pad applied to pre-ALTER rows. conn.execute("INSERT INTO users(name) VALUES ('x')", &[])?; // age defaults to 0 ``` ``` use sqlite_rust::{Pager, read_schema}; let mut pager = Pager::open("my.db")?; // Returns HashMap: table name → root page number let schema = read_schema(&mut pager)?; let root = schema["users"]; ``` ``` use sqlite_rust::{tokenize, Token, TokenKind, Keyword}; // Returns Vec or TokenizeError let tokens = tokenize("SELECT * FROM users")?; // tokens[0] == Token { kind: TokenKind::Keyword(Keyword::Select), lexeme: "SELECT".to_string() } ``` ``` use sqlite_rust::{parse, SelectStmt, ParseError}; // Returns SelectStmt or ParseError let tokens = tokenize("SELECT * FROM users")?; let stmt = parse(&tokens)?; // stmt.table == "users"; stmt.columns == [SelectColumn::Star] ``` ``` use sqlite_rust::{execute, parse, tokenize, ExecError, Pager}; // Evaluate a parsed SELECT against a real database → Vec (or ExecError) let mut pager = Pager::open_readonly("my.db")?; let stmt = parse(&tokenize("SELECT name FROM users WHERE id = 3 LIMIT 1")?)?; let rows = execute(&stmt, &mut pager)?; // rows[0].values() == [Value::Text("carol".to_string())] ``` ``` use sqlite_rust::{execute, parse, tokenize, Pager}; // ORDER BY: rows are sorted before LIMIT/projection are applied let mut pager = Pager::open_readonly("my.db")?; let stmt = parse(&tokenize("SELECT * FROM users ORDER BY name ASC, id DESC LIMIT 3")?)?; let rows = execute(&stmt, &mut pager)?; // rows are sorted primarily by name ascending, ties broken by id descending, // matching the row sequence the real sqlite3 CLI returns for the same query ``` ``` use sqlite_rust::{execute, parse, tokenize, Pager}; // Aggregate functions with GROUP BY: groups rows, computes COUNT/SUM/MIN/MAX/AVG per group. // Results are cross-checked row-for-row against the real sqlite3 CLI. let mut pager = Pager::open_readonly("scores.db")?; let stmt = parse(&tokenize( "SELECT name, COUNT(*), SUM(score) FROM scores GROUP BY name ORDER BY SUM(score) DESC" )?)?; let rows = execute(&stmt, &mut pager)?; // One row per distinct name, ordered by SUM(score) descending. // COUNT(*) returns 0 for empty tables; SUM/AVG/MIN/MAX return NULL for all-NULL groups. // Bare non-aggregate column without GROUP BY returns Err(ExecError::InvalidQuery). ``` ``` use sqlite_rust::{execute, parse, tokenize, Pager}; // JOIN: combines two tables row-by-row (nested loop). Qualified `table.column` // references resolve across both tables; an unqualified name shared by both is // Err(ExecError::AmbiguousColumn). LEFT JOIN keeps every left row, NULL-filling // the right columns when nothing matches. Cross-checked against the real sqlite3 CLI. let mut pager = Pager::open_readonly("company.db")?; let stmt = parse(&tokenize( "SELECT employees.name, departments.name FROM employees \ LEFT OUTER JOIN departments ON employees.dept_id = departments.id \ ORDER BY employees.name" )?)?; let rows = execute(&stmt, &mut pager)?; // stmt.joins == vec![JoinSpec { kind: JoinKind::LeftOuter, table: "departments", alias: None, on: ... }] // Bare JOIN == INNER JOIN; an unknown join table returns Err(ExecError::UnknownTable). // FROM/JOIN table aliases (`FROM t [AS] x`, `JOIN u [AS] y`) name each source; an // alias shadows the table name, so the original name (`employees.name` above) then // resolves to Err(ExecError::UnknownTable). Aliasing one table twice expresses a // self-join — two distinct relations. Cross-checked against the real sqlite3 CLI. let stmt = parse(&tokenize( "SELECT e.name, m.name FROM employees AS e \ JOIN employees AS m ON e.mgr_id = m.id ORDER BY e.name" )?)?; let rows = execute(&stmt, &mut pager)?; ``` ``` use sqlite_rust::{parse_insert, tokenize, InsertStmt, Value}; // Returns InsertStmt or ParseError; values are interpreted into Value at parse time. // The Insert/Into/Values keywords are new Keyword variants this REQ. let stmt = parse_insert(&tokenize("INSERT INTO users (id, name) VALUES (1, 'alice')")?)?; // stmt.table == "users" // stmt.columns == Some(vec!["id".to_string(), "name".to_string()]) // stmt.values == vec![Value::Integer(1), Value::Text("alice".to_string())] ``` ``` use sqlite_rust::{execute_insert, parse_insert, tokenize, ExecError, Pager}; // Encode a parsed INSERT's row and append it to the table's leaf page → the // assigned auto-rowid (1 for an empty table, else max(rowid) + 1). The page is // written through the Pager, producing bytes identical to what sqlite3 writes. let mut pager = Pager::open("my.db")?; let stmt = parse_insert(&tokenize("INSERT INTO users VALUES (4, 'newbie', 99, 1.25)")?)?; let rowid = execute_insert(&stmt, &mut pager)?; // == 4 // Scope: a full leaf triggers a depth-1 B-tree split (REQ-010) — the insert // succeeds and grows the file by two pages. Only an interior-root table whose // rightmost leaf is full returns Err(ExecError::PageFull). An unknown table is // ExecError::UnknownTable. ``` ``` use sqlite_rust::{parse_delete, tokenize, DeleteStmt}; // Returns DeleteStmt or ParseError let stmt = parse_delete(&tokenize("DELETE FROM users WHERE id = 3")?)?; // stmt.table == "users"; stmt.where_clause == Some(Expr::BinOp { ... }) ``` ``` use sqlite_rust::{execute_delete, parse_delete, tokenize, ExecError, Pager}; // Remove matching rows → count of deleted rows (any tree depth: single-leaf, depth-1 interior-root, or multi-level depth≥2). let mut pager = Pager::open("my.db")?; let stmt = parse_delete(&tokenize("DELETE FROM users WHERE id = 3")?)?; let deleted = execute_delete(&stmt, &mut pager)?; // == 1 ``` ``` use sqlite_rust::{parse_update, tokenize, UpdateStmt, Value}; // Returns UpdateStmt or ParseError; SET RHS values are interpreted into Value at parse time. let stmt = parse_update(&tokenize("UPDATE users SET name = 'bob' WHERE id = 2")?)?; // stmt.table == "users" // stmt.assignments == vec![("name".to_string(), Value::Text("bob".to_string()))] // stmt.where_clause == Some(Expr::BinOp { ... }) ``` ``` use sqlite_rust::{execute_update, parse_update, tokenize, ExecError, Pager}; // Modify matching rows → count of updated rows (any tree depth: single-leaf, depth-1 interior-root, or multi-level depth≥2). // Matched rows are re-encoded under their original rowids; unmatched rows are kept verbatim. let mut pager = Pager::open("my.db")?; let stmt = parse_update(&tokenize("UPDATE users SET name = 'robert' WHERE id = 2")?)?; let updated = execute_update(&stmt, &mut pager)?; // == 1 ``` ## 环境要求 - Rust stable 1.96.0+ - `sqlite3` CLI (3.45.1+) —— 执行 E2E 完整性检查测试必需 ## 许可证与免责声明 MarsmoduleDB 采用 Apache License 2.0 授权。有关完整的法律条款，请参见 [LICENSE](LICENSE)； 有关归属声明，请参见 [NOTICE](NOTICE)。 MarsmoduleDB 基于“原样”提供，不附带任何种类的明示或暗示的保证或条件。 用户有责任评估其是否适合自身的使用场景， 包括生产使用、数据持久性、兼容性和安全性需求。 MarsmoduleDB 是一个独立的、兼容 SQLite 的 Rust 数据库引擎。它未与 SQLite 项目有关联、未受其赞助，也未获得其认可。SQLite 是 Hipp, Wyrick & Company, Inc. 的商标。

标签：AI辅助开发, Rust, SQLite兼容, SQL解析, 可视化界面, 存储引擎, 嵌入式数据库, 数据库, 网络流量审计, 通知系统