Allow .insert(..., foreign_keys=()) to auto-detect table and primary key

[simonw]

The foreign_keys= argument currently takes a list of triples:

db["usages"].insert_all(
    usages_to_insert,
    foreign_keys=(
        ("line_id", "lines", "id"),
        ("definition_id", "definitions", "id"),
    ),
)

As of #16 we have a mechanism for detecting the primary key column (the third item in this triple) - we should use that here too, so foreign keys can be optionally defined as a list of pairs.

[simonw]

Maybe foreign_keys could even optionally just be a list of columns - it could then attempt to detect the related tables based on some rules-of-thumb and raise an error if there's no obvious candidate.

Rules:

• If the column name ends in _id, remove that suffix and look for a matching table.
• Try for a table which is the column name without the _id suffix with an s appended to it
• Try for a table that's the exact match for the column name

If none of these rules match, raise an error.

So the above example could be further simplified to:

db["usages"].insert_all(
    usages_to_insert,
    foreign_keys=["line_id", "definition_id"]
)

[simonw]

If I'm going to do this then I should make the other_table and other_column arguments optional here too:

sqlite-utils/sqlite_utils/cli.py

Lines 201 to 215 in 2fed87d

	@click.argument("table")
	@click.argument("column")
	@click.argument("other_table")
	@click.argument("other_column")
	def add_foreign_key(path, table, column, other_table, other_column):
	"""
	Add a new foreign key constraint to an existing table. Example usage:
	$ sqlite-utils add-foreign-key my.db books author_id authors id
	WARNING: Could corrupt your database! Back up your database file first.
	"""
	db = sqlite_utils.Database(path)
	try:
	db[table].add_foreign_key(column, other_table, other_column)

[simonw]

Still need to add this mechanism to .create_table() - this code here is all that needs to be modified - it needs to learn to deal with the alternative syntax for foreign keys and guess the missing data if necessary:

sqlite-utils/sqlite_utils/db.py

Lines 115 to 119 in d645032

	def create_table(
	self, name, columns, pk=None, foreign_keys=None, column_order=None, hash_id=None
	):
	foreign_keys = foreign_keys or []
	foreign_keys_by_name = {fk[0]: fk for fk in foreign_keys}

[simonw]

I'm going to reuse the ForeignKey named tuple here:

sqlite-utils/sqlite_utils/db.py

Lines 17 to 19 in d645032

	ForeignKey = namedtuple(
	"ForeignKey", ("table", "column", "other_table", "other_column")
	)