6 ms·

I have been particularly looking forward to that. Although I have to admit that I'm a bit... disappointed(?)... that previous versions of SQLite won't be able t

I have been particularly looking forward to that. Although I have to admit that I'm a bit... disappointed(?)... that previous versions of SQLite won't be able to open such tables (unless they use PRAGMA writable_schema=ON). I wonder if SQLite didn't store the schema for a table as a "CREATE TABLE..." string, but instead something a bit more... structured, then such things could be added in a more backwards compatible way?

deleted 5y ago
[deleted]
cryptonector 5y ago
This could never be added in a backwards-compatible way if the existing CHECK() functionality was not sufficient already. And yes, it'd be very nice to have a relational metaschema that doesn't suck for SQL schema. So far all such metaschemas I've seen leave a lot to be desired.
- porker 5y ago
  What kind of thing do you mean by a relational metaschema?
  - formerly_proven 5y ago
    Describing SQL tables with an SQL table
    - jhgb 5y ago
      That's called a catalog. (https://reldb.org/c/index.php/twelve-rules/ https://reldb.org/c/index.php/twelve-rules/, Rule #4)
- ComputerGuru 5y ago
  The existing check syntax would cover inserts but wouldn’t have supported the behavior of the new “any” type in a strict table. What would be nice is if SQLite had separate “compatible with” and “read-only compatible with” fields, since the read-only behavior should not have changed at all (although I suppose describing the table would fail depending on how the presence of the STRICT modifier is codified).
  - cryptonector 5y ago
    What they could have done is translated a STRICT table w/o ANY-typed columns to an older CREATE TABLE with CHECK()s. But anyways.
- smitty1e 5y ago
  > a relational metaschema that doesn't suck for SQL schema Something like an object-relational mapper, e.g. SQLAlchemy?
- saurik 5y ago
  Well, in a different concept of how compatibility was thought of with respect to stored schemes, you could have feature flags marked as "this feature is entirely optional to understand", "this feature is required to understand", and a third case that would work here and be extremely useful "this feature is required to understand if you want to write to the database, but if you merely open it read only it is optional".

deleted 5y ago

[deleted]

cryptonector 5y ago

This could never be added in a backwards-compatible way if the existing CHECK() functionality was not sufficient already. And yes, it'd be very nice to have a relational metaschema that doesn't suck for SQL schema. So far all such metaschemas I've seen leave a lot to be desired.

porker 5y ago
What kind of thing do you mean by a relational metaschema?
- formerly_proven 5y ago
  Describing SQL tables with an SQL table
  - jhgb 5y ago
    That's called a catalog. (https://reldb.org/c/index.php/twelve-rules/ https://reldb.org/c/index.php/twelve-rules/, Rule #4)
ComputerGuru 5y ago
The existing check syntax would cover inserts but wouldn’t have supported the behavior of the new “any” type in a strict table. What would be nice is if SQLite had separate “compatible with” and “read-only compatible with” fields, since the read-only behavior should not have changed at all (although I suppose describing the table would fail depending on how the presence of the STRICT modifier is codified).
- cryptonector 5y ago
  What they could have done is translated a STRICT table w/o ANY-typed columns to an older CREATE TABLE with CHECK()s. But anyways.
smitty1e 5y ago
> a relational metaschema that doesn't suck for SQL schema Something like an object-relational mapper, e.g. SQLAlchemy?
saurik 5y ago
Well, in a different concept of how compatibility was thought of with respect to stored schemes, you could have feature flags marked as "this feature is entirely optional to understand", "this feature is required to understand", and a third case that would work here and be extremely useful "this feature is required to understand if you want to write to the database, but if you merely open it read only it is optional".