3 ms·

While I doubt it will ever be enabled by default due to backwards compatibility, there is a strict tables[1] feature now. [1] https://www.sqlite.org/stricttabl

While I doubt it will ever be enabled by default due to backwards compatibility, there is a strict tables[1] feature now. [1] https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html

silvestrov 4y ago
There should be an option to have strict tables, foreign key checks and other modern features to be automatically enforced. E.g. a command line argument when creating a DB, which then taints this database as modern. Something like what html did with "<!DOCTYPE html>". Or simply accept that the time has come to make a version 4 of sqlite.
- ratrocket 4y ago
  That might have to be version 5 of sqlite. Version 4 came and went: https://sqlite.org/src4/doc/trunk/www/index.wiki https://sqlite.org/src4/doc/trunk/www/index.wiki > SQLite4 was an experimental rewrite of SQLite that was active from 2012 through 2014.
- tomsmeding 4y ago
  > There should be an option to have strict tables, foreign key checks and other modern features to be automatically enforced. I know to enable foreign key checking, and I learned strict tables from this thread. What are the "other modern features" you were referring to? EDIT: In TFA, section 8 is relevant here I guess: SQLite accepts double-quoted strings as a historical misfeature, which can be disabled with the appropriate C API functions. This is one of the "other modern features" I guess; TIL.
- tehbeard 4y ago
  The strict keyword per that doc already taints the a table for pre 3.37.0 (2021-11-27) libraries. (There is a way around that for pre 3.37 per the doc, but it's mainly enabling the PRAGMA used for db recovery operations that treats the strict at the end as an error and ignores it to "facilitate recovery"). So the solution your after would be some additional calls when initializing the database to enable the FK checks (alongside any other app related PRAGMA calls like checking the data_version, configuring journal or page size), and ensure any tables created have STRICT applied.
chrismorgan 4y ago
Unfortunately I found strict mode not very usable, because it only allows the six basic datatype names and doesn’t do column affinity determination <https://www.sqlite.org/datatype3.html#determination_of_column_affinity https://www.sqlite.org/datatype3.html#determination_of_colum...>. This thwarts one of the more practical forms of strictness in querying libraries, automatic column type mapping (e.g. mapping DATE (which has NUMERIC affinity) to your language’s date type, or conceivably some other convention like mapping TEXT_Foo to your own Foo type). I’m perplexed as to the rationale for this restriction which feels just gratuitous and unnecessary.
- tehbeard 4y ago
  That feels like a bit of a hack, tbh. Playing off a mechanism I think? was made for making porting from other day syntaxes easier? Is sqlite converting an iso8601 to a timestamp, or would it just store as given when type coercion fails due to dashes and spaces? From memory, it's the latter. It would be slightly more brittle, but surely this metadata should be at app/orm layer rather than the database?
  - chrismorgan 4y ago
    > surely this metadata should be at app/orm layer rather than the database? I cannot fathom why you would say this, because the way I see it of course it belongs in the database as part of the table definition, as it’s obviously part of the logical schema. Sure, you can’t actually enforce invariants for individual types so that they would be just conventional aliases for the underlying affinities, but that doesn’t mean you should avoid specifying meaningful types. Which of these would you prefer: CREATE TABLE example ( id BLOB PRIMARY KEY, created INTEGER, data TEXT ); CREATE TABLE example ( id uuid PRIMARY KEY, created timestamp, data json, ); I know that I want the latter, because it makes my life much easier when reading the schema, and lets code automatically use the right types based on inspecting the schema (though you will need to define a mapping of SQL type names to your programming language’s types, since they are still only informational rather than structural like in most SQL databases). Ideally you might be able to define your own datatype affinities (SQL even defines a suitable syntax: CREATE TYPE uuid AS BLOB), but it’s not so bad leaning into the built-in rules with BLOB_uuid, INTEGER_timestamp and TEXT_json (… though on reflection I admit this is rather perilous due to the precise affinity determination rules, shown in the example “TEXT_point” which would be INTEGER due to containing “int”, so maybe it is actually better that strict mode doesn’t blithely use the current affinity determination rules on the expressed type). (Actually on the DATE thing I was forgetting and thinking that was a regular feature but it’s actually just the fallback affinity where a type is specified but not matched by any other rule, NUMERIC. Strike out that example as a canonical definition or anything, then. But the rest of the point still stands.)

silvestrov 4y ago

There should be an option to have strict tables, foreign key checks and other modern features to be automatically enforced. E.g. a command line argument when creating a DB, which then taints this database as modern. Something like what html did with "<!DOCTYPE html>". Or simply accept that the time has come to make a version 4 of sqlite.

ratrocket 4y ago
That might have to be version 5 of sqlite. Version 4 came and went: https://sqlite.org/src4/doc/trunk/www/index.wiki https://sqlite.org/src4/doc/trunk/www/index.wiki > SQLite4 was an experimental rewrite of SQLite that was active from 2012 through 2014.
tomsmeding 4y ago
> There should be an option to have strict tables, foreign key checks and other modern features to be automatically enforced. I know to enable foreign key checking, and I learned strict tables from this thread. What are the "other modern features" you were referring to? EDIT: In TFA, section 8 is relevant here I guess: SQLite accepts double-quoted strings as a historical misfeature, which can be disabled with the appropriate C API functions. This is one of the "other modern features" I guess; TIL.
tehbeard 4y ago
The strict keyword per that doc already taints the a table for pre 3.37.0 (2021-11-27) libraries. (There is a way around that for pre 3.37 per the doc, but it's mainly enabling the PRAGMA used for db recovery operations that treats the strict at the end as an error and ignores it to "facilitate recovery"). So the solution your after would be some additional calls when initializing the database to enable the FK checks (alongside any other app related PRAGMA calls like checking the data_version, configuring journal or page size), and ensure any tables created have STRICT applied.

chrismorgan 4y ago

Unfortunately I found strict mode not very usable, because it only allows the six basic datatype names and doesn’t do column affinity determination <https://www.sqlite.org/datatype3.html#determination_of_column_affinity https://www.sqlite.org/datatype3.html#determination_of_colum...>. This thwarts one of the more practical forms of strictness in querying libraries, automatic column type mapping (e.g. mapping DATE (which has NUMERIC affinity) to your language’s date type, or conceivably some other convention like mapping TEXT_Foo to your own Foo type). I’m perplexed as to the rationale for this restriction which feels just gratuitous and unnecessary.

tehbeard 4y ago
That feels like a bit of a hack, tbh. Playing off a mechanism I think? was made for making porting from other day syntaxes easier? Is sqlite converting an iso8601 to a timestamp, or would it just store as given when type coercion fails due to dashes and spaces? From memory, it's the latter. It would be slightly more brittle, but surely this metadata should be at app/orm layer rather than the database?
- chrismorgan 4y ago
  > surely this metadata should be at app/orm layer rather than the database? I cannot fathom why you would say this, because the way I see it of course it belongs in the database as part of the table definition, as it’s obviously part of the logical schema. Sure, you can’t actually enforce invariants for individual types so that they would be just conventional aliases for the underlying affinities, but that doesn’t mean you should avoid specifying meaningful types. Which of these would you prefer: CREATE TABLE example ( id BLOB PRIMARY KEY, created INTEGER, data TEXT ); CREATE TABLE example ( id uuid PRIMARY KEY, created timestamp, data json, ); I know that I want the latter, because it makes my life much easier when reading the schema, and lets code automatically use the right types based on inspecting the schema (though you will need to define a mapping of SQL type names to your programming language’s types, since they are still only informational rather than structural like in most SQL databases). Ideally you might be able to define your own datatype affinities (SQL even defines a suitable syntax: CREATE TYPE uuid AS BLOB), but it’s not so bad leaning into the built-in rules with BLOB_uuid, INTEGER_timestamp and TEXT_json (… though on reflection I admit this is rather perilous due to the precise affinity determination rules, shown in the example “TEXT_point” which would be INTEGER due to containing “int”, so maybe it is actually better that strict mode doesn’t blithely use the current affinity determination rules on the expressed type). (Actually on the DATE thing I was forgetting and thinking that was a regular feature but it’s actually just the fallback affinity where a type is specified but not matched by any other rule, NUMERIC. Strike out that example as a canonical definition or anything, then. But the rest of the point still stands.)