SQLite Forum

about "strict" mode
My further 2c here:

1. Consider that strict mode won't ever be backward compatible, in the sense that the entire domain of existing SQLite databases were not subjected to strict mode upon creation, so opening an old DB in a strict=ON connection will very likely fail (unless the previous schema makers were very strict themselves).

2. The moment a programmer chooses to invoke "strict mode", I cannot see why they would ever want to turn back to the old ways, so being able to turn strict mode on and off at a whim seems completely unneeded for future use.

3. Any table/schema made with "strict = ON" would be perfectly able to open and be used happily by any previous version of SQLite, so there really is not ever any reason to embed strict-ness in the DB file itself, other than perhaps a flag that indicates the last schema write was done with a strict-mode connection (or not).

4. One might still have old DBs and have to maintain them, while using the newest build of SQLite, so that provides a clear case where being able to turn strict-mode OFF for a connection *prior to opening the DB* could be useful, but it must be *PRIOR to opening the DB file*, and if it is a pragma, then that switch-over must become a no-op if done after a DB is already opened, or like some other pragmas, perhaps require a rebuild (vacuum) to get enabled on the DB and then heartily FAIL hard if the schema fails the strictness check.

Disabling/Enabling strict-mode at build-level though might have a lot of advantages as I imagine one could get rid of a whole lot of nonsense-checking code that won't be needed in strict mode anymore.

We've been lobbying hard here for strict-mode and love that this is becoming a reality, but we should understand that it is a hard paradigm shift and should not be compromised. I would probably start by using two builds myself, perhaps using the old in my older projects (until I can adapt them) and the new strict build for anything recent and going forward.

PS: Nice to have tools would be: Some simple check to see if a DB already complies to "strict" or not, and perhaps something to tell you what things in the current DB file needs to change for it to comply to "strict" mode.
Probably nice additions to add to the CLI (else we might put out such a tool, since we need to make one for our purposes anyway.)

Very excited to start using strict-mode! :)