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 [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 [link] [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.