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 #2104 (comment)

[simonw]

The code for this can go here:

datasette/datasette/database.py

Lines 460 to 519 in 7818e8b

	async def hidden_table_names(self):
	# Mark tables 'hidden' if they relate to FTS virtual tables
	hidden_tables = [
	r[0]
	for r in (
	await self.execute(
	"""
	select name from sqlite_master
	where rootpage = 0
	and (
	sql like '%VIRTUAL TABLE%USING FTS%'
	) or name in ('sqlite_stat1', 'sqlite_stat2', 'sqlite_stat3', 'sqlite_stat4')
	or name like '\\_%' escape '\\'
	"""
	)
	).rows
	]
	has_spatialite = await self.execute_fn(detect_spatialite)
	if has_spatialite:
	# Also hide Spatialite internal tables
	hidden_tables += [
	"ElementaryGeometries",
	"SpatialIndex",
	"geometry_columns",
	"spatial_ref_sys",
	"spatialite_history",
	"sql_statements_log",
	"sqlite_sequence",
	"views_geometry_columns",
	"virts_geometry_columns",
	"data_licenses",
	"KNN",
	"KNN2",
	] + [
	r[0]
	for r in (
	await self.execute(
	"""
	select name from sqlite_master
	where name like "idx_%"
	and type = "table"
	"""
	)
	).rows
	]
	# Add any tables marked as hidden in config
	db_config = self.ds.config.get("databases", {}).get(self.name, {})
	if "tables" in db_config:
	hidden_tables += [
	t for t in db_config["tables"] if db_config["tables"][t].get("hidden")
	]
	# Also mark as hidden any tables which start with the name of a hidden table
	# e.g. "searchable_fts" implies "searchable_fts_content" should be hidden
	for table_name in await self.table_names():
	for hidden_table in hidden_tables[:]:
	if table_name.startswith(hidden_table):
	hidden_tables.append(table_name)
	continue
	return hidden_tables

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.