Tables starting with an underscore should be treated as hidden

[simonw]

Plugins can then take advantage of this pattern, for example:

• Manage mode - UI for creating database-backed tokens datasette-auth-tokens#8

[simonw]

Relevant code:

datasette/datasette/database.py

Lines 391 to 451 in 0f7192b

	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')
	"""
	)
	).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 from metadata.json
	db_metadata = self.ds.metadata(database=self.name)
	if "tables" in db_metadata:
	hidden_tables += [
	t
	for t in db_metadata["tables"]
	if db_metadata["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

[asg017]

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

[simonw]

We really need this for Datasette Cloud, we have _hidden_but_not_hidden tables all over the place!

[simonw]

Had to figure out how to run a LIKE query with a literal _ in it - this is the syntax:

or name like '\_%' escape '\'

[simonw]

There isn't any documentation on hidden tables right now, fixing that.

[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

Neat!

Aside: Datasette doesn't allow select * from pragma_table_list() now but I can fix that easily.

[simonw]

Added new documentation listing all of the different types of hidden table here: https://docs.datasette.io/en/latest/pages.html#hidden-tables