SQLite Forum

How to find out whether a table is STRICT?
Login

How to find out whether a table is STRICT?

(1) By Stephan (stephancb) on 2021-09-20 07:37:02 [link] [source]

The upcoming STRICT tables have already been discussed in several threads. I think it is a useful feature. For example, when processing a SELECT from a STRICT table, the code does not necessarily need to check the column type in each row and be prepared for all eventualities. How do I know whether a table is STRICT? Well, by parsing the create statement in sqlite_schema, but it would be nice to have an easier method.

Obviously sqlite_schema could get another column to indicate whether a table is strict or not, but this may not be feasible or the best. BTW, an easy indicator whether a table is with or WITHOUT ROWID would also be nice.

(2.1) By David Jones (vman59) on 2021-09-20 13:07:35 edited from 2.0 in reply to 1 [link] [source]

Maybe the pragma_table_info virtual table could implement some hidden columns, e.g.:

SELECT cid,name,strictType,affinity FROM pragma_table_info('employees');

The strictType value for traditional non-strict tables would always be null.

(3) By Larry Brasfield (larrybr) on 2021-09-20 13:22:03 in reply to 2.1 [link] [source]

One of the virtues of SQL is that newly added columns do not have to be hidden to retain compatibility with existing, sensible queries. (Use of '*' as the projection criterion, then using the column names programmatically, is not sensible.)

(4) By Keith Medcalf (kmedcalf) on 2021-09-20 21:02:06 in reply to 2.1 [link] [source]

The table_info (and table_xinfo) return information about the columns in the table. This is the wrong place to store information about the table itself. STRICT is an attribute of the table, not a column, and therefore is already stored where it belongs, with the table attribute data.

sqlite> create table x(x);
sqlite> create table y(y integer) strict;
sqlite> pragma table_xinfo(x);
┌─────┬──────┬──────┬─────────┬──────┬─────────┬────────────┬────┬───────┬─────────┬────────┐
│ cid │ name │ type │   aff   │ coll │ notnull │ dflt_value │ pk │ rowid │ autoinc │ hidden │
├─────┼──────┼──────┼─────────┼──────┼─────────┼────────────┼────┼───────┼─────────┼────────┤
│ -1  │      │      │ INTEGER │      │ 0       │            │ 1  │ 1     │ 0       │ 1      │
│ 0   │ x    │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
└─────┴──────┴──────┴─────────┴──────┴─────────┴────────────┴────┴───────┴─────────┴────────┘
sqlite> pragma table_xinfo(y);
┌─────┬──────┬─────────┬─────────┬──────┬─────────┬────────────┬────┬───────┬─────────┬────────┐
│ cid │ name │  type   │   aff   │ coll │ notnull │ dflt_value │ pk │ rowid │ autoinc │ hidden │
├─────┼──────┼─────────┼─────────┼──────┼─────────┼────────────┼────┼───────┼─────────┼────────┤
│ -1  │      │         │ INTEGER │      │ 0       │            │ 1  │ 1     │ 0       │ 1      │
│ 0   │ y    │ INTEGER │ INTEGER │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
└─────┴──────┴─────────┴─────────┴──────┴─────────┴────────────┴────┴───────┴─────────┴────────┘
sqlite> pragma table_list;
┌────────┬────────┬────────────────────┬───────┬────┬─────┬─────┬────┬────┬───────┬────────┬──────┐
│ schema │  type  │        name        │ ncols │ ro │ eph │ rid │ pk │ ai │ named │ strict │ shad │
├────────┼────────┼────────────────────┼───────┼────┼─────┼─────┼────┼────┼───────┼────────┼──────┤
│ main   │ table  │ y                  │ 1     │ 0  │ 0   │ 1   │ 0  │ 0  │ 0     │ 1      │ 0    │
│ main   │ table  │ x                  │ 1     │ 0  │ 0   │ 1   │ 0  │ 0  │ 0     │ 0      │ 0    │
│ main   │ table  │ sqlite_master      │ 5     │ 1  │ 0   │ 1   │ 0  │ 0  │ 0     │ 0      │ 0    │
└────────┴────────┴────────────────────┴───────┴────┴─────┴─────┴────┴────┴───────┴────────┴──────┘

(5) By Keith Medcalf (kmedcalf) on 2021-09-20 21:06:33 in reply to 4 [source]

Note that the as-distributed version SQLite3 does not have a table_list pragma to report the list of tables and table information stored in the internal schema, however, this does not mean that there is not quite a lot of useful information stored in the internal data dictionary for each table.

(6) By ddevienne on 2021-09-21 09:45:01 in reply to 5 [link] [source]

That's a shame, I like the look of your table_list pragma Keith!

So here's to wishing SQLite grows something like that in the future.

PS: Can table_list be added w/o modifying the SQLite source code, as a pure addition?

(7.2) By Keith Medcalf (kmedcalf) on 2021-09-21 19:36:45 edited from 7.1 in reply to 6 [link] [source]

Unfortunately you have to modify the SQLite3 source code to add/change pragma's. It is very easy to do, but I do not think there is a way to dynamically add in pragma extensions.

I modified table_[x]info to add aff, coll, rowid, autoinc and the cid=-1 internal rowid column (affinity, collation, whether a rowid or not, and if a rowid, whether autoincrement is in effect).

I added database_info(schema, type, name), trigger_list(seq, name, tr_tm, op).

table_list shows schema, type, name, ncols, ro, eph, rid, pk, ai, named, strict, shad for each table. (respectively the schema/name/type (table, etable, vtable, view), number of columns, whether read only, whether ephemeral, whether has a rowid, whether has a primary key, whether the rowid uses autoincrement, whether the rowid is "named", whether the table is strict, and whether it is a shadow table or not.

Patches to mkpragmatab.tcl.patch and pragma.c.patch are on http://www.dessus.com/files/mkpragmatab.tcl.patch and http://www.dessus.com/files/pragma.c.patch respectively.

I believe the patches are up-to-date. I regenerate them from time to time as the SQLite3 code changes.

(8) By SeverKetor on 2021-09-21 19:41:53 in reply to 6 [link] [source]

Looks like an official version was actually just added in https://www.sqlite.org/src/info/2c0e7ae541e9ecb8

(9) By bokwoon on 2021-09-24 07:29:09 in reply to 8 [link] [source]

Awesome, I've been using an ugly hack to filter out shadow tables during db introspection: SELECT * FROM sqlite_schema WHERE type = 'table' AND sql NOT LIKE 'CREATE TABLE ''%' (since I noticed all FTS5 shadow tables started with single quotes). With this pragma_table_list I can join on the tables that are actually user-created tables.