[ncruces]

This changes one default that "everyone agrees about" and which you can change with a compile time option: SQLITE_DEFAULT_FOREIGN_KEYS

Then it argues for STRICT tables, recognizing that there are drawbacks without introducing a new feature (custom type aliases, CREATE TYPE alias = base).

If also doesn't even considering what it means for existing data to make tables strict, which is precisely why “there is no pragma to globally make all tables strict”.

Then it argues for setting a busy timeout, and picks 5s. Why? Why 5s and not 1 or 60s? SQLite doesn't decide, which makes perfect sense. Your OS or programming language also doesn't offer you locks with a default timeout: it's either indefinite, or an instant "try lock".

Finally: WAL mode is a different file format, unsupported on many platforms, in more danger of silent corruption. Why should it be the default?

[Rygian]

The section "The solution: editions?" in the article addresses directly the point of existing data.

The way I read it, this article does not advocate at any point to change the defaults for existing databases, but rather to start with better defaults for new databases.

Also, regarding the timeout of 5 seconds, I disagree with your premise "SQLite doesn't decide, which makes perfect sense". As the article explains, SQLite decides on the value zero (ie. instant error), which is arguably an inconvenient default.

[ncruces]

> The section "The solution: editions?" in the article addresses directly the point of existing data.

I'm sorry, where?

SQLite schema is stored as text. If you change the default interpretation of CREATE TABLE with a PRAGMA, your existing tables become STRICT, but (1) they might now have columns with invalid types (which means you have an invalid schema, and your database fails to open), (2) they may have invalid data for their strict types (which you can only figure out with a full table scan, PRAGMA integrity_check will complain).

This was discussed previously on the SQLite forum, you can read the team's position there: https://sqlite.org/forum/forumpost/0248dcf7f0ece9fb

Regarding busy_timeout, why is 5s specifically a better default? You did not engage with my argument: that 5s is no different from 1s or 60s. How do you decide?

Also discussed in the forum, with the team laying out the rational; https://sqlite.org/forum/forumpost/f0da30efa661bd9c

I think the minimum is considering the arguments by the people who promise to maintain the software for the next 25 years.

PS: I actually really like the idea of `CREATE TYPE alias = type` for use with STRICT tables. I would champion that feature request on the forum. Given how schema is saved, I disagree with making it the default. Having to mark your tables STRICT is not such a burden, IMO.

[Rygian]

> > The section "The solution: editions?" in the article addresses directly the point of existing data.

> I'm sorry, where?

Here:

quote

[...]This should be a nice middle ground which avoids breaking backwards compatibility, but lets the database engine move forwards and not be bogged down by its own history.

end quote

> Regarding busy_timeout, why is 5s specifically a better default?

According to the post we are discussing, any number greater than zero is better than the current default:

quote

The default behavior [no timeout] has lead me to writing real-world bugs, where systems would sometimes just crash. I've manually written retry loops to fix it.

end quote

Your forum links may be quite useful as a response to this comment: https://news.ycombinator.com/item?id=48928441

[ncruces]

OK, but this:

> This should be a nice middle ground which avoids breaking backwards compatibility, but lets the database engine move forwards and not be bogged down by its own history.

Does nothing to address my criticism. Handwaving "this is a nice middle ground" does not address the issues. How does the feature work when enabled on an existing database, and on a new database?

Please understand that when you write `CREATE TABLE …` this statement is copied pretty much verbatim to the schema table. And that's the metadata that's saved for the table: a verbatim copy of the DDL.

If you don't mark it STRICT any current version of SQLite will consider it not to be.

So if you make assuming tables are STRICT the default (even with a PRAGMA), you'll have to deal with tables that you assume are STRICT, but aren't: they have invalid types, or invalid data.

The STRICT feature, as it was added, is backwards compatible in the sense that: (1) all old databases work with new versions of the library, and (2) all new databases fail fast (before corrupting data) in old versions of it, as they'll refuse to parse STRICT tables.

To fail fast for existing database files, you'd need to integrity check the entire databases.

So how do you implement it, what's the alternative, exactly? Add STRICT to every table you create since enabling the PRAGMA? You could do that but, to my knowledge, it'd be the first time you'd do that (modifying the schema before writing it) in 25 years of SQLite.

Whatever you come up with must be backwards compatible, as that is the promise SQLite developers have made.

[Rygian]

Not the author of the article, but I think you are overthinking the issue.

New database ⇒ use the proposed new magic PRAGMA, start with sane defaults.

Existing database ⇒ don't touch anything, keep legacy "suboptimal" defaults.

[ncruces]

The forum post I linked to has a 3 line example:

  CREATE TABLE t1(a DATE, b JSON);
  PRAGMA strict=ON;
  INSERT INTO t1 VALUES(a,b) VALUES(?1,?2);

You (or the author) can replace the non-existent `PRAGMA strict` with whatever editions thingy you're proposing.

Just, you know, explain what should happens when you do this, because the creator of SQLite doesn't know.

When doing so consider the file format, how schema is stored, and what happens when other versions of SQLite touch your file.

Should be simple, given that I'm overthinking it. Thanks.