SQLite is slightly different from Rust in that it is a data container. It’s somewhat more common for people to move SQLite database files from one machine to another and then inspect using the command line tool. And it is often the case that the embedded SQLite version in your app is a newer version than whatever version /usr/bin/sqlite3 happens to be. Adding editions to your SQLite file will probably break this use case of using an older version to read a database written by a newer version because it does not know what has changed in a new edition.

Not a big deal though. Probably just need better ops to bundle the command-line utility that’s the same version as what’s used in your app.

▲ tptacek 3 hours ago | parent | next [-]

For some of these pragmas you have the same issue with or without "editions", right? Busy timeout is per-connection. And then: if you're running in WAL mode, you, the user, have to know that, or risk messing up the database by copying just the .db file rather than vacuuming-into.

▲

kccqzy 3 hours ago | parent [-]

Editions make the problem worse by requiring the version not only to support the underlying pragmas but also to understand the edition mapping.

Example: PRAGMA foo=1 is introduced in 2027. PRAGMA edition=2030 implies this foo pragma. Now you unnecessarily lock out three years worth of releases.

▲

tptacek 3 hours ago | parent [-]

I don't see how you don't have the pragma compatibility problem either way. The edition proposal captures a bunch of behaviors that already exist.

	▲	kccqzy 3 hours ago \| parent [-]
		Yes, the problem exists either way. Editions just exacerbate the problem.

▲ ijustlovemath 3 hours ago | parent | prev | next [-]

editions could be self-describing as to certain semantic changes, and you could embed that with the file. older versions could safely ignore it and newer versions parse and run it. you could also force things like "editions must be declared early", "editions are one way only" etc to get some level of security in the adoption of the change

▲ arijun 3 hours ago | parent | prev | next [-]

I think you can maintain full backward compatibility with all these changes. SQLite has a bunch of meta-data that you can use to see that no earlier editions have modified your database since you last wrote. You make the new editions follow these strict rules, but if an old edition modifies the DB in the interim, you fall back to the original rules, until it’s verified compliant again.

▲ appplication 3 hours ago | parent | prev | next [-]

It seems SQLite could be evolve to solve this by just bundling itself entirely in the data files? After all, the binary is less than 1MB, anywhere you’re putting a database surely has at least that much overhead, for most applications it’s less than a drop in the bucket.

I’d be interested to learn if there are any db implementations that take this approach, or reasons this wouldn’t work.

▲

mort96 3 hours ago | parent | next [-]

Well you'd have the problem that an sqlite database file created on a Linux AMD64 box could be copied to an AArch64 macOS machine to be read there. And quite a lot of (cross platform) software build their file formats on top of SQLite.

	▲	appplication 3 hours ago \| parent \| next [-]
		I think my silly and unserious naive response would be linear scaling bundled binaries with number of platforms doesn’t seem to be that materially different in terms of total size. But I see your point, didn’t consider the architectures
	▲	andai 3 hours ago \| parent \| prev [-]
		Perhaps it could be an "actually portable executable"? https://news.ycombinator.com/item?id=26273960

▲

arijun 3 hours ago | parent | prev [-]

So for every new SQLite db you want to access would have to run a new untrusted executable? That seems… hilariously bad for security.

	▲	appplication 3 hours ago \| parent [-]
		Fair point! Was mostly an intrusive technical thought

▲ Rendello 2 hours ago | parent | prev [-]

> SQLite is slightly different from Rust in that it is a data container.

I think this is the key.

From sqlite.org [1]:

> [Since 2004], the file format has been fully backwards compatible.

> By "backwards compatible" we mean that newer versions of SQLite can always read and write database files created by older versions of SQLite. It is often also the case that SQLite is "forwards compatible", that older versions of SQLite can read and write database files created by newer versions of SQLite. But there are sometimes forward compatibility breaks. Sometimes new features are added to the file format

---

Given editions (A) and (B), what does backwards compatibility look like? Must (B) be backwards compatible with (A)?

If yes -> editions are backwards compatible but not necessarily forwards compatible, which is the current status quo:

    -------(A)----(B)--

If no -> editions are not backwards compatible, the edition space is bifurcated:

    ---+----(A)--------
        \
         \--(B)--------

Now you may have to worry about backwards compatibility with (A)..(Z). What happens when you import a file from edition (Y)?

1. https://www.sqlite.org/formatchng.html

---

Interesting PS, grepping sqlite.org for "backwards compat": https://pastebin.com/Q7b7h4eM

▲

mort96 2 hours ago | parent [-]

The answer to the question "What happens when you import a file from edition (Y)" should, ideally, be exactly the same as the answer to the question "What happens when you import a file created with parameters foo, bar and baz set to values a, b and c". There's really nothing new here.

Some parameters are properties of the database file itself, such as (I believe) journal_mode. These parameters probably result in issues with earlier versions of sqlite; if you create a file with journal_mode WAL, you're not gonna be able to open it in a version of sqlite without WAL support. Same with strict tables; if you have a database which contains strict tables, I assume you're going to encounter issues if you try to open it in a version of sqlite too old to support strict tables. But anything new enough to support the individual database file features shouldn't have any trouble. This is true whether we're talking about an edition pragma or individual parameters set the old way.

Some parameters are properties of the connection, like the foreign_keys parameter. If you're trying to open a database file in a version of sqlite which doesn't support foreign key validation at all, just don't set the foreign_keys parameter.

	▲	Rendello 2 hours ago \| parent [-]
		A lot of this discussion mirrors the SQLite forum's discussion on the never-implemented "PRAGMA strict": https://sqlite.org/forum/forumpost/1b9d073a37ca5998 I personally find this idea interesting and would like if SQLite meaningfully moved away from Postel's Law.