Improve and document foreign_keys=... argument to insert/create/etc

[simonw]

The foreign_keys= argument to table.insert_all() and friends can be used to specify foreign key relationships that should be created.

It is not yet documented. It also requires you to specify the SQLite type of each column, even though this can be detected by introspecting the referenced table:

cols = [c for c in self.db[other_table].columns if c.name == other_column]
cols[0].type

Relates to #2

[simonw]

It looks like the type information isn't actually used for anything at all, so this:

sqlite-utils/tests/test_create.py

Lines 97 to 103 in f8d3b7c

	fresh_db["m2m"].insert(
	{"one_id": 1, "two_id": 1},
	foreign_keys=(
	("one_id", "INTEGER", "one", "id"),
	("two_id", "INTEGER", "two", "id"),
	),
	)

Could actually be written like this:

    fresh_db["m2m"].insert(
        {"one_id": 1, "two_id": 1},
        foreign_keys=(
            ("one_id", "one", "id"),
            ("two_id", "two", "id"),
        ),
)

[simonw]

Sanity checking those foreign keys would be worthwhile.

[simonw]

This involves a breaking API change. I need to call that out in the README and also fix my two other projects which use the old four-tuple version of foreign_keys=:

https://github.com/simonw/db-to-sqlite/blob/c2f8e93bc6bbdfd135de3656ea0f497859ae49ff/db_to_sqlite/cli.py#L30-L42

And

https://github.com/simonw/russian-ira-facebook-ads-datasette/blob/e7106710abdd7bdcae035bedd8bdaba75ae56a12/fetch_and_build_russian_ads.py#L71-L74

I'll also need to set a minimum version for sqlite-utils in the db-to-sqlite setup.py:

https://github.com/simonw/db-to-sqlite/blob/c2f8e93bc6bbdfd135de3656ea0f497859ae49ff/setup.py#L25

[simonw]

Re-opening this until I've fixed the other two projects.

[simonw]

Need to put out a new release of sqlite-utils so db-to-sqlite can depend on it.

[simonw]

Released: https://sqlite-utils.readthedocs.io/en/latest/changelog.html#v0-14

[simonw]

Both projects have been upgraded.