The "use strict" thing is interesting. I often hear people say, well we can't fix absurd behavior in JS because backwards compatibility! Well, we already did, and we can do it again!
SQLite should have (Rust-style) editions
21–30 of 186 posts
Re: SQLite should have (Rust-style) editions
#22Earlier quoted context omitted.
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.
Perhaps it could be an "actually portable executable"?
Re: SQLite should have (Rust-style) editions
#23SQLite 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…
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.
Re: SQLite should have (Rust-style) editions
#24SQLite 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…
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.
So for every new SQLite db you want to access would have to run a new untrusted executable? That seems… hilariously bad for security.
Re: SQLite should have (Rust-style) editions
#25Earlier quoted context omitted.
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.
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
Re: SQLite should have (Rust-style) editions
#26The "use strict" thing is interesting. I often hear people say, well we can't fix absurd behavior in JS because backwards compatibility! Well, we already did, and we can do it again!
"use stricter"
Re: SQLite should have (Rust-style) editions
#27SQLite gets so much praise here but when you start using it, you realize quickly how bad it is, the type system is by default very limited and dangerous.
It's like comparing old php with a strongly typed language.
There is not even a date type...
Re: SQLite should have (Rust-style) editions
#28SQLite 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…
> 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)--
---+----(A)--------
\
\--(B)--------
1. https://www.sqlite.org/formatchng.html
---
Interesting PS, grepping sqlite.org for "backwards compat": https://pastebin.com/Q7b7h4eM
Re: SQLite should have (Rust-style) editions
#29Sadly, the ORM layer lags here: Drizzle has no way to declare STRICT tables. The request has been open since March 2023 (issue #202, now discussion #2435) and didn't make the v1.0 beta either. The only workaround is hand-appending STRICT to generated migration SQL, which doesn't work at all if you use `drizzle-kit push`.
https://github.com/drizzle-team/drizzle-orm/discussions/2435
Re: SQLite should have (Rust-style) editions
#30SQLite 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…
> 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 S…
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.