Hacker News Reader

Comment by kccqzy | Hacker News Reader

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.

View on HN

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.

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.

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.

Yes, the problem exists either way. Editions just exacerbate the problem.

feature-set would be a better idea, because it is more explicit on what you are enabling

There’s no need to store the literal edition in the DB file. Instead the edition could be a library construct that sets appropriate flags. So if you set edition 2026 you get WAL, etc.

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.

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

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.

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.

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

it would be sorta enough to bundle the wasm binary

That's the idea behind the future file format:

https://github.com/future-file-format/F3

Bundle a wasm decoder as a fallback when native decoder isn't available.

The issue then is that instead of just bundling a tiny 1MB sqlite library with your program, you have to bundle a huge WASM runtime. I looked at the size of wasm on my system just to get an idea and it's 46MB; that's huge.

For context, I have a game I'm working on where I build stand-alone builds with all dependencies bundled. The app bundle is 14MB right now. If I added sqlite, it would grow to 15MB. If I added sqlite-but-with-wasm, it'd more than quadruple in size to 60MB.

Ugh I can't edit now but just noticed I should've said I looked at the size of wasmtime, not "the size of wasm".

Perhaps it could be an "actually portable executable"?

https://news.ycombinator.com/item?id=26273960

FWIW, that's existed for a while. Source at https://github.com/jart/cosmopolitan/tree/master/third_party... with downloadable binary at https://cosmo.zip/pub/cosmos/bin/sqlite3 .

  % curl -LO 'https://cosmo.zip/pub/cosmos/bin/sqlite3'
    % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  100 4845k  100 4845k    0     0  1384k      0  0:00:03  0:00:03 --:--:-- 1384k
  % chmod +x sqlite3
  % file sqlite3
  sqlite3: DOS/MBR boot sector; partition 1 : ID=0x7f, active, start-CHS (0x0,0,1), end-CHS (0x3ff,255,63), startsector 0, 4294967295 sectors
  % ./sqlite3
  SQLite version 3.40.0 2022-11-16 12:10:08
  Enter ".help" for usage hints.
  Connected to a transient in-memory database.
  Use ".open FILENAME" to reopen on a persistent database.
  sqlite>:

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

Fair point! Was mostly an intrusive technical thought

I mean.... it does sound like a fun project

There is a way to do this with sqlite in readonly mode using the append vfs [0] which allows you to put your DB appended to the end of the executable, and then the executable can read it (like a zip file the header is then at the end of the db rather than the front). Or you could embed a normal db as a binary blob with the linker. Allowing writes is more tricky because most OSes don't typically allow executables to edit their own executable memory (on unix I believe executables always load as r/x or r/o depending on the region, maybe with some minor exceptions).

And a lot of users of sqlite statically link sqlite within their executable, they actually recommend that instead of dynamically linking.

[0] https://sqlite.org/vfs.html ctrl-f for 'append'

Edit: I got confused between the sqlar command and the append vfs so fixed it.

> 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

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.

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.

Reply on news.ycombinator.com