Too often it's just a list of issues and a wish that everyone else will change.
In (mild) defense of SQLITE_BUSY - busy_timeout just tells sqlite to sleep and retry up to the timeout when it receives SQLITE_BUSY. It seems like a sensible default for a library to leave that up the calling code - which may have something else it could do while it waits. However, that logic often gets missed!
Because it not tied to the data but to the code.
Instead, what I think should be is that the PRAGMAs become "data" that is always checked in full with "if manually set" and then on next "open" THEY GET APPLIED.
That is.
(and in the command line when open interactively they show up).
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.
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’d be interested to learn if there are any db implementations that take this approach, or reasons this wouldn’t work.
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
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.
https://sqlite.org/forum/forumpost/1b9d073a37ca5998
I personally find this idea interesting and would like if SQLite meaningfully moved away from Postel's Law.
But I suppose it would be nice to have a standard way to refer to those defaults, in a cross-runtime way.
https://github.com/drizzle-team/drizzle-orm/discussions/2435
I guess if foreign keys are handled properly then that's not a problem by definition? But it sounds wrong somehow.
That's default behavior, but it can be altered when creating a table. See;
If a parent table ID gets reused, then it's a potential to expose data to a wrong user -- security broked.
They even had an sqlite4:
Today I noticed I could do `pragma foreign_key = ON`, and despite the pragma being wrong (it should be foreign_keys, plural), it reported nothing. In fact, it reports nothing with the correct pragma either. So check your pragmas!
https://www.postfix.org/postconf.5.html#compatibility_level
https://www.postfix.org/COMPATIBILITY_README.html
You get a warning whenever you depend on the deprecated old default until you either move forward or specifically commit to the old behavior.
It's like comparing old php with a strongly typed language.
There is not even a date type...
It’s a product that allows you to do sql like things without a database server. If you need to have database server behavior, you’re using the wrong product.
Different tools for different situations!
It’s a great tool if you want to give a local app its own database. If you need concurrent writes and full ACID guarantees of an industrial strength database, use an industrial strength database.
Yes, other databases will require you to read more manual pages and configure a service. Higher up front cost. Not “lightweight.” But given enough operating time there is a certain unarguable lightness to using the right tool for the job.
- https://sqlite.org/testing.html
- https://sqlite.org/mostdeployed.html
EDIT: added links