SQLite should have (Rust-style) editions

SQLite 应该引入（Rust 风格的）版本机制

SQLite is an amazing database engine. I use it as a database for plenty of embedded projects, and I don’t think it’s an exaggeration to call it the industry standard for local data storage. Some server software even uses it; for example, lobste.rs is now running on SQLite. SQLite 是一个令人惊叹的数据库引擎。我将其用于许多嵌入式项目，我认为称其为本地数据存储的行业标准并不夸张。一些服务器软件也在使用它；例如，lobste.rs 目前就在运行于 SQLite 之上。

Unlike traditional RDBMSes (Relational DataBase Management Systems), SQLite is not a separate process; it’s an RDBMS as a library, meaning your software remains self contained. Unlike traditional file formats, you don’t need to write custom serializers and parsers. In some ways, it’s the best of both worlds. 与传统的 RDBMS（关系型数据库管理系统）不同，SQLite 不是一个独立的进程；它是一个以库形式存在的 RDBMS，这意味着你的软件可以保持自包含。与传统的文件格式不同，你不需要编写自定义的序列化器和解析器。在某些方面，它兼具了两者的优点。

There’s just one huge problem though. Its defaults are all wrong. 然而，它存在一个巨大的问题。它的默认设置全都是错的。

Bad default #1: Foreign key constraints are ignored by default

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

You read that right. Foreign key constraints are arguably the primary tool we have to ensure that a database remains consistent and don’t have dangling references. As a quick primer, this is how an SQL foreign key constraint looks: 你没看错。外键约束可以说是我们确保数据库保持一致性且没有悬空引用的主要工具。简单介绍一下，SQL 外键约束看起来是这样的：

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)
);

The typical behavior for all other RDBMSes would be that the user_id column of a post must always reference the ID of a valid user. You can’t create a new post without providing a valid user ID, you can’t delete a user without also deleting its posts, lest you get a foreign key constraint violation error. 对于所有其他 RDBMS，典型的行为是：帖子的 user_id 列必须始终引用有效用户的 ID。如果不提供有效的用户 ID，你就无法创建新帖子；如果不删除其对应的帖子，你就无法删除用户，否则会收到外键约束违规错误。

The only RDBMS I’m aware of which doesn’t enforce this by default is SQLite. This is made even worse by SQLite’s tendency to re-use ROWID. You see, in this example, those INTEGER PRIMARY KEY rows become aliases for the table’s ROWID, which is a unique integer ID assigned to every row of a table in SQLite. 据我所知，唯一默认不强制执行此操作的 RDBMS 就是 SQLite。更糟糕的是，SQLite 有重用 ROWID 的倾向。你看，在这个例子中，那些 INTEGER PRIMARY KEY 行变成了表 ROWID 的别名，这是 SQLite 为表中每一行分配的唯一整数 ID。

The algorithm for assigning ROWID is a bit complicated (more details in the SQLite documentation), but it results in ID re-use in some cases. This means that a dangling reference easily results in a reference to the wrong column, which is even worse than a dangling reference because everything will seem fine. You don’t even get an error during lookup. 分配 ROWID 的算法有点复杂（详情请参阅 SQLite 文档），但在某些情况下会导致 ID 重用。这意味着悬空引用很容易导致引用到错误的列，这比悬空引用更糟糕，因为一切看起来都很正常。你在查询时甚至不会收到任何错误。

Just look at this hypothetical sequence of operations in our toy database schema: 看看我们在玩具数据库模式中的这一系列假设操作：

-- Bob creates a user account
INSERT INTO users (display_name) VALUES ('Bob');
-- Bob deletes his account, but we forgot to delete the posts.
-- SQLite doesn't produce an error because it ignores our foreign key.
DELETE FROM users WHERE id = 1;

-- Alice creates an account.
-- Alice gets the same ID that Bob had due to the ROWID algorithm.
INSERT INTO users (display_name) VALUES ('Alice');

-- Alice has now inherited Bob's old post!
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

The fix is to enable foreign_keys with a pragma: PRAGMA foreign_keys = ON;. If we had done this in the beginning, the buggy DELETE would have produced an error: Runtime error: FOREIGN KEY constraint failed (19). 解决方法是使用 pragma 启用外键：PRAGMA foreign_keys = ON;。如果我们一开始就这么做，那个有问题的 DELETE 操作就会报错：Runtime error: FOREIGN KEY constraint failed (19)。

Bad default #2: Columns can store the wrong data type

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

SQLite has a simple type system: a value can be NULL, an INTEGER, a REAL (aka a double precision float), TEXT, or a BLOB (aka binary data). Consequently, a column can be defined to hold values of any of those types. However, a column defined as an INTEGER column isn’t restricted to only integers; instead, SQLite considers it to “use INTEGER affinity”. SQLite 有一个简单的类型系统：值可以是 NULL、INTEGER、REAL（即双精度浮点数）、TEXT 或 BLOB（即二进制数据）。因此，列可以被定义为保存这些类型中的任何一种。然而，定义为 INTEGER 的列并不局限于只存储整数；相反，SQLite 认为它“使用 INTEGER 亲和性（affinity）”。

What this means is essentially: If you try to insert a TEXT value, and it is a valid string representation of an integer, it is converted to an integer and stored as such. If you try to insert a TEXT value, and it is a valid string representation of a real number, it is converted to a real and stored as such. Otherwise, the value is stored as-is. 这意味着：如果你尝试插入一个 TEXT 值，且它是整数的有效字符串表示，它会被转换为整数并存储。如果你尝试插入一个 TEXT 值，且它是实数的有效字符串表示，它会被转换为实数并存储。否则，该值将按原样存储。

I don’t think I need to explain why it’s a bad idea for a database to be so careless about data validation. It would be one thing if SQLite was an explicitly dynamically typed document database, but it’s not. SQLite asks me through its syntax rules, “What type do you want to go into this column”. 我想我不需要解释为什么数据库在数据验证上如此粗心是个坏主意。如果 SQLite 是一个显式的动态类型文档数据库那还说得过去，但它不是。SQLite 通过语法规则问我：“你想在这个列中放入什么类型”。

I once had to clean up a project where some code had accidentally been writing the strings ‘1’ and ‘0’ to a column which was intended to store booleans (1 and 0). That was not a fun debugging story. 我曾经不得不清理一个项目，其中的一些代码意外地将字符串 ‘1’ 和 ‘0’ 写入了一个本应存储布尔值（1 和 0）的列中。那可不是一段有趣的调试经历。

Luckily, SQLite has the concept of strict tables, which makes SQLite produce a type error when the wrong type is inserted into a column: 幸运的是，SQLite 引入了严格表（strict tables）的概念，这使得当错误的类型被插入到列中时，SQLite 会产生类型错误：

CREATE TABLE music (
    id INTEGER PRIMARY KEY,
    name TEXT,
    duration_sec INTEGER
) strict;