SQLite should have (Rust-style) editions

You're using CSV, what did you expect? CSV was never meant for data exchange between two systems that do not know of each other's existence. Basically every CSV file is its own dialect.

> The section "The solution: editions?" in the article addresses directly the point of existing data.

I'm sorry, where?

SQLite schema is stored as text. If you change the default interpretation of CREATE TABLE with a PRAGMA, your existing tables become STRICT, but (1) they might now have columns with invalid types (which means you have an invalid schema, and your database fails to open), (2) they may have invalid data for their strict types (which you can only figure out with a full table scan, PRAGMA integrity_check will complain).

This was discussed previously on the SQLite forum, you can read the team's position there: https://sqlite.org/forum/forumpost/0248dcf7f0ece9fb

Regarding busy_timeout, why is 5s specifically a better default? You did not engage with my argument: that 5s is no different from 1s or 60s. How do you decide?

Also discussed in the forum, with the team laying out the rational; https://sqlite.org/forum/forumpost/f0da30efa661bd9c

I think the minimum is considering the arguments by the people who promise to maintain the software for the next 25 years.

PS: I actually really like the idea of `CREATE TYPE alias = type` for use with STRICT tables. I would champion that feature request on the forum. Given how schema is saved, I disagree with making it the default. Having to mark your tables STRICT is not such a burden, IMO.

Counterpoint: each new edition will add bloat to SQLite as future edition will need to keep each past edition pragma set for backward compatibility.

As SQLite is often used embedded, bloat matters.

So I suggest that "PRAGMA edition" to be only be a shortcut to a list of PRAGMA commands, that would be expanded at the library level: PRAGMA edition would never appear in the DB file. As such, the build of the library would just support a limited set of editions, with a removal policy in default builds. Maybe editions could even be defined at runtime (a system table?) as a way to load them dynamically if old editions are needed beyond builtin support (think about the state of SQLite in 2046).

> 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.

So this was a write to a column that did not have INTEGER affinity. If it was intended to be used as a boolean, then it should have INTEGER affinity. I know because I've tried hard to enter integer- and float-like strings as strings in INTEGER affinity columns, and I haven't managed to; I could only insert them as BLOBs, or prefix the string with say '\' and check/remove at the application level. (That was for an ontology-like database, where table EAttribute.eatvalue could have any type.)

Exactly. In cases where I expect long-running parallel connections from separate processes to the same sqlite file, I make sure that all read transactions do `BEGIN DEFERRED` so `COMMIT` releases the read locks, and all write transactions do `BEGIN IMMEDIATE` so that `SQLITE_BUSY` timeout is not side-stepped.

There was one case where all transactions were implemented using nested `SAVEPOINT bla` so `BEGIN IMMEDIATE` could not be used without more hassle, so this ended all “I know I'm going to write” transactions to instantly update a single-row table so that their lock would not begin as DEFERRED and eventually switch to `IMMEDIATE`; this way almost all `SQLITE_BUSY` side-steppings disappeared. (timeout was set to 30 seconds but all read/write transactions were instrumented to have less than 5 seconds duration).