Datasette row URLs break for binary data as a primary key

[simonw]

Spotted this while browsing a FTS table:

Schema is:

CREATE TABLE 'releases_fts_idx'(
  segid,
  term,
  pgno,
  PRIMARY KEY(segid, term)
) WITHOUT ROWID;

Got a 404 error when I clicked the link to e.g. /content/releases_fts_idx/3,b~270thei~27

[simonw]

I think the bigger problem here is that a row page with a binary primary key cannot even be linked to.

Maybe need some kind of way of representing hex() in a URL to a row page?

[simonw]

Relevant code:

datasette/datasette/views/row.py

Lines 17 to 21 in 2170269

	class RowView(DataView):
	name = "row"
	async def data(self, request, default_labels=False):
	resolved = await self.ds.resolve_row(request)

Which calls:

datasette/datasette/app.py

Lines 1643 to 1651 in 2170269

	async def resolve_row(self, request):
	db, table_name, _ = await self.resolve_table(request)
	pk_values = urlsafe_components(request.url_vars["pks"])
	sql, params, pks = await row_sql_params_pks(db, table_name, pk_values)
	results = await db.execute(sql, params, truncate=True)
	row = results.first()
	if row is None:
	raise RowNotFound(db.name, table_name, pk_values)
	return ResolvedRow(db, table_name, sql, params, pks, pk_values, results.first())

Which calls:

datasette/datasette/utils/__init__.py

Lines 1234 to 1246 in 2170269

	async def row_sql_params_pks(db, table, pk_values):
	pks = await db.primary_keys(table)
	use_rowid = not pks
	select = "*"
	if use_rowid:
	select = "rowid, *"
	pks = ["rowid"]
	wheres = [f'"{pk}"=:p{i}' for i, pk in enumerate(pks)]
	sql = f"select {select} from {escape_sqlite(table)} where {' AND '.join(wheres)}"
	params = {}
	for i, pk_value in enumerate(pk_values):
	params[f"p{i}"] = pk_value
	return sql, params, pks

[asg017]

Sometimes people store UUIDs or ULIDs as compact BLOBs instead of their more verbose TEXT format.

(don't really see much value in that personally but I guess it's something to consider)