Mechanism for adding multiple foreign key constraints at once

[simonw]

Needed by db-to-sqlite. It currently works by collecting all of the foreign key relationships it can find and then applying them at the end of the process.

The problem is, the add_foreign_key() method looks like this:

sqlite-utils/sqlite_utils/db.py

Lines 498 to 516 in 86bd2bb

	with self.db.conn:
	cursor = self.db.conn.cursor()
	schema_version = cursor.execute("PRAGMA schema_version").fetchone()[0]
	cursor.execute("PRAGMA writable_schema = 1")
	old_sql = self.schema
	extra_sql = ",\n FOREIGN KEY({column}) REFERENCES {other_table}({other_column})\n".format(
	column=column, other_table=other_table, other_column=other_column
	)
	# Stick that bit in at the very end just before the closing ')'
	last_paren = old_sql.rindex(")")
	new_sql = old_sql[:last_paren].strip() + extra_sql + old_sql[last_paren:]
	cursor.execute(
	"UPDATE sqlite_master SET sql = ? WHERE name = ?", (new_sql, self.name)
	)
	cursor.execute("PRAGMA schema_version = %d" % (schema_version + 1))
	cursor.execute("PRAGMA writable_schema = 0")
	# Have to VACUUM outside the transaction to ensure .foreign_keys property
	# can see the newly created foreign key.
	cursor.execute("VACUUM")

That means it's doing a full VACUUM for every single relationship it sets up - and if you have hundreds of foreign key relationships in your database this can take hours.

I think the right solution is to have a .add_foreign_keys(list_of_args) method which does the bulk operation and then a single VACUUM. .add_foreign_key(...) can then call the bulk action with a single list item.