Run pks_for_table in inspect, executing once at build time rather than constantly

[simonw]

Right now several Datasette views call the await self.pks_for_table(...) method to figure out what primary keys are set for a specific table. This executes a PRAGMA table_info SQL query.

It would be faster and more efficient to execute this query for each table as part of the inspect() method.

[simonw]

datasette/datasette/app.py

Lines 93 to 102 in 446d47f

	async def pks_for_table(self, name, table):
	rows = [
	row for row in await self.execute(
	name,
	'PRAGMA table_info("{}")'.format(table)
	)
	if row[-1]
	]
	rows.sort(key=lambda row: row[-1])
	return [str(r[1]) for r in rows]

[simonw]

While I'm at it, doing the same thing for fts_table detection is worth considering:

datasette/datasette/app.py

Lines 598 to 603 in 446d47f

	# _search support:
	fts_table = None
	fts_sql = detect_fts_sql(table)
	fts_rows = list(await self.execute(name, fts_sql))
	if fts_rows:
	fts_table = fts_rows[0][0]

[simonw]

Could also identify all views for that database, which would save on these queries:

datasette/datasette/app.py

Lines 543 to 545 in b2188f0

	is_view = bool(list(await self.execute(name, "SELECT count(*) from sqlite_master WHERE type = 'view' and name=:n", {
	'n': table,
	}))[0][0])