GENERATED column support

[nattaylor]

I think this is a feature request... perhaps I should just try to contribute it myself, but thought I'd check in case support is planned already.

For a table with the following schema, datasette 0.51.1 doesn't pick up the GENERATED columns and the column list only contains (rowid, body) If I edit the SQL and select the generated columns, it will happily show them.

At first glance it appears that def table_column_details(conn, table): would have to be refactored to use a different methodology to get the columns, since PRAGMA table_info(deeds); returns just 0|body|TEXT|0||0 so maybe it wouldn't be worth it.

CREATE TABLE deeds (
    body TEXT,
    id INT GENERATED ALWAYS AS (json_extract(body, '$.id')) STORED,
    consideration INT GENERATED ALWAYS AS (json_extract(body, '$.consideration')) STORED
);

[simonw]

I'm treating this as a bug - these columns should definitely be visible in Datasette.

I created my own test database using SQLite from Homebrew like this:

/usr/local/Cellar/sqlite/3.33.0/bin/sqlite3 deeds.db << EOF
CREATE TABLE deeds (
    body TEXT,
    id INT GENERATED ALWAYS AS (json_extract(body, '$.id')) STORED,
    consideration INT GENERATED ALWAYS AS (json_extract(body, '$.consideration')) STORED
);
INSERT INTO deeds (body) VALUES ('{
    "id": 1,
    "consideration": "This is the consideration"
}');
EOF

[simonw]

It looks like PRAGMA table_info skips "hidden" columns: https://www.sqlite.org/pragma.html#pragma_table_info

But PRAGMA table_xinfo does not: https://www.sqlite.org/pragma.html#pragma_table_xinfo

Compare https://latest.datasette.io/fixtures?sql=select+*+from+pragma_table_info%28%27searchable%27%29 to https://latest.datasette.io/fixtures?sql=select+*+from+pragma_table_xinfo%28%27searchable%27%29 - the xinfo one has an additional hidden column.

[simonw]

Generated column support was added in SQLite 3.31.0, so any unit tests I write for this should use skipIf to only run on that version or later.

[simonw]

This change will also have an impact on how hidden virtual FTS tables are displayed, since apparently those have some hidden columns: https://latest.datasette.io/fixtures?sql=select+*+from+pragma_table_xinfo%28%27searchable_fts%27%29

cid	name	type	notnull	dflt_value	pk	hidden
0	text1		0		0	0
1	text2		0		0	0
2	name with . and spaces		0		0	0
3	searchable_fts		0		0	1
4	docid		0		0	1
5	__langid		0		0	1

[nattaylor]

@simonw thanks for investigating so quickly. If it is undesirable to change that hidden behavior, maybe something like this is a suitable workaround:

SELECT * FROM pragma_table_xinfo('deeds') where hidden in (0,2);
0|body|TEXT|0||0|0
1|id|INT GENERATED ALWAYS|0||0|2
2|consideration|INT GENERATED ALWAYS|0||0|2

[simonw]

I'm OK exposing hidden columns, unless someone comes up with a pressing reason not to.

[simonw]

Ouch, the tests pass on my laptop but failed in CI: https://github.com/simonw/datasette/actions/runs/392367997

Lots of failures look like this:

ERROR: conn=<sqlite3.Connection object at 0x7f44f0494030>, sql = 'select rowid,  from facetable order by rowid limit 51', params = {}: near "from": syntax error

Note the select rowid, from... - so it looks like invalid SQL queries are being constructed maybe due to mis-detecting columns somehow.

I wonder why it didn't fail on my laptop?

[simonw]

Working on this in a pull request: #1117

[simonw]

Here's the problem: https://www.sqlite.org/changes.html#version_3_26_0

2018-12-01 (3.26.0)

• Added PRAGMA table_xinfo that works just like PRAGMA table_info except that it also shows hidden columns in virtual tables.

CI is running 3.22.0.