5 ms·

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 an

by kccqzy 3mo ago

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 3mo ago
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 3mo ago
  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 3mo ago
    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 3mo ago
      Yes, the problem exists either way. Editions just exacerbate the problem.
    - PunchyHamster 3mo ago
      feature-set would be a better idea, because it is more explicit on what you are enabling
  - amluto 3mo ago
    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.
appplication 3mo ago
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 3mo ago
  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 3mo ago
    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
    - afiori 3mo ago
      it would be sorta enough to bundle the wasm binary
      - Certhas 3mo ago
        That's the idea behind the future file format: https://github.com/future-file-format/F3 https://github.com/future-file-format/F3 Bundle a wasm decoder as a fallback when native decoder isn't available.
        mort96 3mo ago
        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.
ijustlovemath 3mo ago
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 3mo ago
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.
Rendello 3mo ago
> 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 https://www.sqlite.org/formatchng.html --- Interesting PS, grepping sqlite.org for "backwards compat": https://pastebin.com/Q7b7h4eM https://pastebin.com/Q7b7h4eM
- mort96 3mo ago
  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.

by kccqzy 3mo ago

tptacek 3mo ago

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 3mo ago
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 3mo ago
  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 3mo ago
    Yes, the problem exists either way. Editions just exacerbate the problem.
  - PunchyHamster 3mo ago
    feature-set would be a better idea, because it is more explicit on what you are enabling
- amluto 3mo ago
  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.

appplication 3mo ago

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 3mo ago
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 3mo ago
  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
  - afiori 3mo ago
    it would be sorta enough to bundle the wasm binary
    - Certhas 3mo ago
      That's the idea behind the future file format: https://github.com/future-file-format/F3 https://github.com/future-file-format/F3 Bundle a wasm decoder as a fallback when native decoder isn't available.
      - mort96 3mo ago
        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.

ijustlovemath 3mo ago

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 3mo ago

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.

Rendello 3mo ago

> 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 https://www.sqlite.org/formatchng.html --- Interesting PS, grepping sqlite.org for "backwards compat": https://pastebin.com/Q7b7h4eM https://pastebin.com/Q7b7h4eM

mort96 3mo ago
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.