Strict tables, affinity and type names

(1) By anonymous on 2022-07-08 23:46:46 [link] [source]

Hello,

I was wondering why the notion of strict table was tied to a notion of "strict type names". I'm interested in having strict tables from an affinity point of view but not from the type name point of view. That is I'm still interested in being able to use these names to define columns rather than the limited set you have to use for being able to add the STRICT keyword.

The reason is that these extended names are returned by pragma_table_info which I'm using to map values to specific datatypes of my programming language. For example to map a BOOLEAN column to the bool type in my language. With a strict table my booleans have to become INTEGER and I can no longer distinguish them via pragma_table_info.

(2) By SeverKetor on 2022-07-09 03:01:09 in reply to 1 [link] [source]

Which language are you using? There might be an alternate method for doing it. For example, in Python you can enable an option on the DB connection to parse types as part of column names, allowing you to do stuff like SELECT IsWhatever AS "IsWhatever [Bool]" FROM table, and the resulting rows will have keys of "IsWhatever" and boolean values (assuming you defined a converter for Bool)

(3) By anonymous on 2022-07-09 12:36:16 in reply to 2 [link] [source]

I'm devising my own abstraction. But hungarian notation is a bit meh when you have a specific place to specify the types. I mean I would like my strict table column to be defined as say "is_public" BOOLEAN not "is_public_bool" INTEGER.

I'm not sure I see the rationale of tying strictness to type names rather than affinity.

(4) By Keith Medcalf (kmedcalf) on 2022-07-10 17:52:36 in reply to 1 [source]

I would like to see the following change made to strict tables.

When parsing a "datatype specification" (as especially in a create table statement) then I would like to see strict (1) require the presence of a proper datatype name but also (2) allow the presence of a user-defined datatype name.

That is, the current STRICT syntax requires the use of a specific set of type identifiers (INT, INTEGER, REAL, TEXT, BLOB, ANY). Only one of these is allowed.

There may also be a user-defined-type (nonsense string) that DOES NOT CONTAIN any of the STRICT typewords above, but allows the additional name to be accessible by the sqlite_column_decltype (ie, stored in the internal schema structure as the datatype, just like it is now if you do not use STRICT).

This would allow you to declare things like:

create table x(insertDate datetime text not null) strict;

which would treat the table exactly the same as if declared:

create table x(insertDate text not null) strict;

EXCEPT that the Column Declaration Type (sqlite3_column_decltype) returns "datetime text" instead of "text".

That is, it allows you to declare strict tables but also add annotated datatypes for use by application/interfaces.

(5) By SeverKetor on 2022-07-10 20:28:41 in reply to 4 [link] [source]

That would be interesting. In the past I suggested being able to define new datatypes as one of the existing ones with an optional check constraint (for example, defining JSON as TEXT with the constraint value IS NULL OR json_valid(value), or BOOL as INTEGER with value in (0, 1)), but in the absence of that, your idea would be a simple and intuitive way of doing it. I'd prefer adding an extra keyword to various columns then having to choose between STRICT and simpler handling of various datatypes in application logic.