[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.

[Rygian]

I would go with something like "Syntax error: pragma strict must appear before any table creation. " and consider that db file unusable.