Additional Column Metadata?

(1) By seth (swillits) on 2022-10-12 00:23:28 [link] [source]

I'm adding SQLite support into a db-viewing tool. sqlite3_table_column_metadata provides some metadata but not all. I don't see an API, or SQL path, to find:

Is a table column is a generated column? If so, what is the expression SQL, and is it stored or virtual?
What is the default value (or expression) of a table column?
Is a table column declared to allow NULL values?
What are the FK, Unique, and Check constraints?

The only way I see to get much of this is to get the CREATE statement from the sqlite_schema table and write a parser. That, or I really have to dig into the implementation and add some new custom functions. Is there another option I'm missing?

I tried to find past discussion on this but didn't come across any. Thanks!

(2) By anonymous on 2022-10-12 05:53:55 in reply to 1 [link] [source]

PRAGMA schema.table_info(table-name);

gets you some of that info.

(3) By seth (swillits) on 2022-10-12 15:08:00 in reply to 2 [link] [source]

Aha! I missed that. Thank you!

(4) By Keith Medcalf (kmedcalf) on 2022-10-12 15:27:57 in reply to 2 [link] [source]

pragma <schema>.table_xinfo(<table>);

also executable as

select * from pragma_table_xinfo where schema='<schema>' and arg='<table>';

if you want to see hidden columns as well.

(5) By seth (swillits) on 2022-10-21 01:05:17 in reply to 1 [source]

I've found the columns, indexes, PK, FKs, nullability, and default value in the different pragmas, but the one thing I still can't find is:

• Is a table column is a generated column? If so, what is the expression SQL, and is it stored or virtual?

This doesn't seem to be anywhere besides the CREATE statement? Am I overlooking something?

(6) By anonymous on 2022-10-21 04:47:17 in reply to 5 [link] [source]

given :

CREATE TABLE t(
   a INTEGER PRIMARY KEY,
   b INT,
   c TEXT,
   d INT AS (a*abs(b)),
   e TEXT AS (sha3(a||b||c||d)) STORED -- since i was playing around storing hashes
);

you can tell which are stored, or virtual with:

pragma table_xinfo(t);

which results in:

┌─────┬──────┬─────────┬─────────┬────────────┬────┬────────┐
│ cid │ name │  type   │ notnull │ dflt_value │ pk │ hidden │
├─────┼──────┼─────────┼─────────┼────────────┼────┼────────┤
│ 0   │ a    │ INTEGER │ 0       │ NULL       │ 1  │ 0      │
│ 1   │ b    │ INT     │ 0       │ NULL       │ 0  │ 0      │
│ 2   │ c    │ TEXT    │ 0       │ NULL       │ 0  │ 0      │
│ 3   │ d    │ INT     │ 0       │ NULL       │ 0  │ 2      │
│ 4   │ e    │ TEXT    │ 0       │ NULL       │ 0  │ 3      │
└─────┴──────┴─────────┴─────────┴────────────┴────┴────────┘

other than:

SELECT sql 
FROM sqlite_schema 
WHERE tbl_name = 't'; -- or some variation thereof

which gives the original sql, unfortunately as a jumble of bytes (rather than structured).

Thus, you know a generated column's name, affinity/type, and whether it's virtual or stored.  It would be useful to have a hook on the expression without parsing the original SQL, but alas I know of none.

You could try regex on the DDL patterns for generated columns (that would be a useful regex to have handy if anyone wants to post that) from the sql.  Then you could do a 'case' on the 'hidden' column type to extract the generated column expression from the 'sql' in "sqlite_schema".  (hope that makes sense).