.add_foreign_key() corrupts database if column contains a space

[simonw]

I ran this:

db["Reports"].add_foreign_key("Reported by ID", "Reporters", "id")

And got this:

~/jupyter-venv/lib/python3.9/site-packages/sqlite_utils/db.py in add_foreign_keys(self, foreign_keys)
    616         # Have to VACUUM outside the transaction to ensure .foreign_keys property
    617         # can see the newly created foreign key.
--> 618         self.vacuum()
    619 
    620     def index_foreign_keys(self):

~/jupyter-venv/lib/python3.9/site-packages/sqlite_utils/db.py in vacuum(self)
    629 
    630     def vacuum(self):
--> 631         self.execute("VACUUM;")
    632 
    633 

~/jupyter-venv/lib/python3.9/site-packages/sqlite_utils/db.py in execute(self, sql, parameters)
    234             return self.conn.execute(sql, parameters)
    235         else:
--> 236             return self.conn.execute(sql)
    237 
    238     def executescript(self, sql):

DatabaseError: database disk image is malformed

[simonw]

I bet the bug is in here:

sqlite-utils/sqlite_utils/db.py

Lines 593 to 602 in 806c210

	table_sql = {}
	for table, column, other_table, other_column in foreign_keys_to_create:
	old_sql = table_sql.get(table, self[table].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:]
	table_sql[table] = new_sql