Automatically hide type=shadow tables in SQLite 3.37 or higher

[simonw]

For filtering virtual table's "shadow tables" (ex the FTS5 _content and most the spatialite tables), you can use pragma_table_list (first appeared in SQLite 3.37 (2021-11-27), which has a type column that calls out type="shadow" tables https://www.sqlite.org/pragma.html#pragma_table_list

Originally posted by @asg017 in https://github.com/simonw/datasette/issues/2104#issuecomment-1641082395

[simonw]

The code for this can go here: https://github.com/simonw/datasette/blob/7818e8b9d15a3d50c16f080dc7fe4b5e8eb3d241/datasette/database.py#L460-L519

It will need to gracefully handle that missing pragma.

[simonw]

We're going to change something here too: we're going to NOT hide FTS virtual tables (or indeed any other virtual table) by default any more.

Reason is that FTS tables are actually OK to browse, e.g. https://datasette.io/content/repos_fts

And some SQLite FTS tables don't correspond to a content table, so should be visible anyway.