500 error if a table has only primary keys

[simonw]

I have a m2m table that has a primary key across both columns.

When I tried to edit the schema I got a 500 error. Sentry says:

[simonw]

Relevant code:

datasette-edit-schema/datasette_edit_schema/utils.py

Lines 54 to 76 in d6a7099

	def potential_primary_keys(conn, table_name, columns, max_string_len=128):
	# First we run a query to check the max length of each column + if it has any nulls
	selects = []
	params = []
	for column in columns:
	selects.append('max(length("{}")) as "maxlen.{}"'.format(column, column))
	selects.append(
	'sum(case when "{}" is null then 1 else 0 end) as "nulls.{}"'.format(
	column, column
	)
	)
	sql = 'select {} from "{}"'.format(", ".join(selects), table_name)
	cursor = conn.cursor()
	cursor.execute(sql)
	row = cursor.fetchone()
	potential_columns = []
	for i, column in enumerate(columns):
	maxlen = row[i * 2] or 0
	nulls = row[i * 2 + 1] or 0
	if maxlen < max_string_len and nulls == 0:
	potential_columns.append(column)
	if not potential_columns:
	return []

Called from here:

datasette-edit-schema/datasette_edit_schema/__init__.py

Lines 477 to 483 in d6a7099

	# Now do potential primary keys against non-float columns
	non_float_columns = [
	c["name"] for c in columns if c["type"] is not float and not c["is_pk"]
	]
	potential_pks = await database.execute_fn(
	lambda conn: potential_primary_keys(conn, table, non_float_columns)
	)

[simonw]

Created a manual test DB like this:

echo '{"one": 1, "two": 2}' | sqlite-utils insert two_pks.db two_pks --pk one --pk two -

And this so I can test foreign key creation:

echo '{"one": 1, "two": 2}' | sqlite-utils insert two_pks.db other --pk one -

[simonw]

Fixed this problem but found you cannot set a primary key as a foreign key reference, which prevents you from using this plugin to create a m2m table with foreign key references. I'll fix that in a separate issue.