SQLite 应引入 Rust 式 Edition 版本管理

背景

SQLite 是一个令人惊叹的数据库引擎。它被广泛用于嵌入式项目中的本地数据存储，甚至某些服务器软件（如 lobste.rs）也运行在 SQLite 之上。与传统的 RDBMS（关系数据库管理系统）不同，SQLite 不是一个独立进程，而是一个以库形式提供的 RDBMS，这意味着软件可以保持自包含。同时，与传统的文件格式相比，你无需编写自定义的序列化和解析器。在某些方面，它兼具了两者的优势。

然而，SQLite 存在一个巨大问题：它的默认设置全是错误的。本文作者认为，SQLite 应当像 Rust 那样引入“editions”（版本机制），以便在版本升级时安全地更改默认行为，从而避免破坏现有项目的同时，让新项目从更合理的默认配置中受益。

核心内容

糟糕的默认设置 #1：外键约束默认被忽略

你没看错。外键约束可以说是确保数据库一致性和避免悬挂引用的主要工具。一个典型的外键约束定义如下：

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  display_name TEXT
);
CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  content TEXT NOT NULL,
  FOREIGN KEY(user_id) REFERENCES users(id)
);

其他所有 RDBMS 的典型行为是：posts 表中的 user_id 必须引用一个有效的 users.id，没有提供有效用户 ID 就不能创建新帖子，也不能不删除帖子就删除用户，否则会触发外键约束违反错误。作者已知的唯一一个默认不强制外键约束的 RDBMS 就是 SQLite。

更糟糕的是，SQLite 倾向于重用 ROWID。在上面的例子中，INTEGER PRIMARY KEY 列成了表 ROWID 的别名（ROWID 是 SQLite 为每一行分配的唯一整数 ID）。ROWID 的分配算法（详见 SQLite 文档）在某些情况下会导致 ID 被重用。这意味着一个悬挂引用很容易变成指向错误行的引用——这比单纯的悬挂引用更糟糕，因为所有查询看起来都是正常的，甚至不会在查找时产生错误。

以下是一个假设的操作用以演示问题：

-- Bob 创建了一个用户账户
INSERT INTO users (display_name) VALUES ('Bob');
SELECT * FROM users;
-- id | display_name
-- 1  | Bob
-- Bob 发表了一篇介绍帖
INSERT INTO posts (user_id, content) VALUES (1, 'Hello, I am Bob');
SELECT u.display_name, p.content FROM users as u, posts as p WHERE u.id = p.user_id;
-- display_name | content
-- Bob          | Hello, I am Bob
-- Bob 删除了自己的账户，但我们忘记删除帖子。
-- SQLite 没有产生错误，因为它忽略了我们的外键。
DELETE FROM users WHERE id = 1;
-- Alice 创建了一个账户。
-- Alice 因为 ROWID 算法得到了 Bob 曾经拥有的 ID。
INSERT INTO users (display_name) VALUES ('Alice');
SELECT * FROM users;
-- id | display_name
-- 1  | Alice
-- Alice 现在继承了 Bob 的老帖子！
SELECT u.display_name, p.content FROM users as u, posts as p WHERE u.id = p.user_id;
-- display_name | content
-- Alice        | Hello, I am Bob

修复方法是使用 PRAGMA 启用外键：

PRAGMA foreign_keys = ON;

如果一开始就执行了这条命令，有问题的 DELETE 会报错：

DELETE FROM users WHERE id = 1;
-- Runtime error: FOREIGN KEY constraint failed (19)

糟糕的默认设置 #2：列可以存储错误的数据类型

SQLite 有一个简单的类型系统：值可以是 NULL、INTEGER、REAL（双精度浮点数）、TEXT 或 BLOB（二进制数据）。因此，列可以被定义为允许这些类型的值。然而，定义为 INTEGER 的列并不限制只能存储整数；相反，SQLite 认为该列“使用 INTEGER affinity”（亲和类型）。这意味着：

• 如果你试图插入一个 TEXT 值，并且它是整数的有效字符串表示，则会转换为整数并存储。
• 如果你试图插入一个 TEXT 值，并且它是实数的有效字符串表示，则会转换为实数（双精度浮点数）并存储。
• 否则，值按原样存储。

其他亲和类型的规则不同但更简单：

• BLOB affinity：值按原样存储。
• TEXT affinity：BLOB、TEXT 和 NULL 按原样存储，但数值转换为 TEXT。
• REAL affinity：与 INTEGER affinity 类似，但整数值转换为 REAL。

实际演示如下：

CREATE TABLE music (
  id INTEGER PRIMARY KEY,
  name TEXT,
  duration_sec INTEGER
);
INSERT INTO music (name, duration_sec) VALUES ('Lost In Hollywood', 321);
INSERT INTO music (name, duration_sec) VALUES ('Comfortably Numb', 382);
INSERT INTO music (name, duration_sec) VALUES ('The Way of All Flesh', 'Way too long, I mean come on');
SELECT * FROM music;
-- id | name                   | duration_sec
-- 1  | Lost In Hollywood      | 321
-- 2  | Comfortably Numb       | 382
-- 3  | The Way of All Flesh   | Way too long, I mean come on

作者认为无需解释数据库如此不关心数据验证有多糟糕。SQLite 通过语法规则询问“你想要在列中放入什么类型”，但又允许任意类型的数据进入列。作者曾不得不清理一个项目，其中某些代码意外地将字符串 '1' 和 '0' 写入了本应存储布尔值（1 和 0）的列，调试过程非常痛苦。

幸运的是，SQLite 有“严格表”（strict tables）的概念，可以在插入错误类型时产生类型错误：

CREATE TABLE music (
  id INTEGER PRIMARY KEY,
  name TEXT,
  duration_sec INTEGER
) strict;
INSERT INTO music (name, duration_sec) VALUES ('The Way of All Flesh', 'Way too long, I mean come on');
-- Runtime error: cannot store TEXT value in INTEGER column music.duration_sec (19)

遗憾的是，没有 PRAGMA 可以全局将所有表设为严格表，你必须手动为每个表添加 strict 标签。

关于严格表存在一些反对意见。SQLite 的作者曾撰文表达他们对“灵活类型”的偏好。作者个人认为那篇文章很奇怪：它没有提供任何例子说明为什么将 BLOB 插入 INTEGER 列会有用，只是说明了有时需要一个可以存储任意类型值的列。严格表对此有解决方案——ANY 数据类型。你仍然可以创建接受任意值的列，只需明确声明即可。

来自 lobste.rs 用户 zie 的一个更好的论点是：SQLite 的严格表不仅强制类型，还改变了类型说明符的解析规则。在非严格表中，SQLite 根据以下规则确定列的亲和类型（来自 SQLite 文档）：

• 如果声明的类型包含字符串 "INT"，则分配 INTEGER affinity。
• 如果声明的类型包含 "CHAR"、"CLOB" 或 "TEXT" 之一，则分配 TEXT affinity（例如 VARCHAR 包含 "CHAR" 所以变成 TEXT）。
• 如果声明的类型包含 "BLOB" 或未指定类型，则分配 BLOB affinity。
• 如果声明的类型包含 "REAL"、"FLOA" 或 "DOUB" 之一，则分配 REAL affinity。
• 否则，affinity 为 NUMERIC。

这一规则结合 SQLite 的松散类型，使得你可以给列指定 DATETIME、KEY_VALUE_SET 或 COLOR 这样的类型名称，然后让数据库连接器/封装层自动知道如何序列化和反序列化这些自定义类型列。而严格表会改变这个行为——需要更仔细地处理。但作者认为这不能成为默认使用非严格表的理由。

（原文此处截断，但核心内容已完整。）

关键要点

• 外键约束默认关闭：SQLite 是唯一一个默认不强制外键约束的主流 RDBMS，这极易导致数据不一致和悬挂引用，尤其是结合 ROWID 重用机制后问题更严重。用户必须手动执行 PRAGMA foreign_keys = ON; 才能启用。
• **列类型检查