4.0rc3

Breaking changes:

• table.foreign_keys now returns ForeignKey objects that are dataclasses rather than namedtupleinstances, so they can no longer be unpacked or indexed as (table, column, other_table,other_column) tuples - access their fields by name instead. Compound (multi-column) foreign keys are now represented as a single ForeignKey with is_compound=True and populated columns/other_columns tuples, where column and other_column are None. Previously they were returned as one ForeignKey per column, misleadingly suggesting several independent foreign keys. See Upgrading from 3.x to 4.0 for details. (#594)
• Removed support for using sqlean.py as a drop-in replacement for the Python standard library sqlite3 module. sqlite-utils will now use pysqlite3 if it is installed, otherwise it will use sqlite3 from the standard library.
• The db.ensure_autocommit_off() context manager has been renamed to db.ensure_autocommit_on(), because the old name described the opposite of what it did. The method temporarily puts the connection into driver-level autocommit mode - by setting isolation_level = None - so that statements such as PRAGMA journal_mode=wal can run outside of an implicit transaction. (#705)

Compound foreign key support:

• Tables can now be created with compound foreign keys, by passing tuples of column names in foreign_keys=: foreign_keys=[(("campus_name", "dept_code"), "departments")]. The referenced columns default to the compound primary key of the other table. Compound keys are rendered as table-level FOREIGN KEY constraints in the generated schema. See Compound foreign keys.
• table.transform() now preserves compound foreign keys, applying any column renames to them. Dropping a column that is part of a compound foreign key drops the whole constraint, matching the existing single-column behavior. drop_foreign_keys= accepts a bare column name - dropping any foreign key that column participates in - or a tuple of columns to target a compound key precisely.
• table.add_foreign_key() and db.add_foreign_keys() accept tuples of column names to add a compound foreign key to an existing table.
• db.index_foreign_keys() creates a single composite index for a compound foreign key.

Other foreign key improvements:

• ForeignKey now exposes on_delete and on_update fields reflecting the foreign key’s ONDELETE/ON UPDATE actions, and table.transform() preserves those actions. Previously a transform silently stripped clauses such as ON DELETE CASCADE from the table schema.
• table.add_foreign_key() accepts new on_delete= and on_update= parameters for creating foreign keys with actions, e.g. table.add_foreign_key("author_id", "authors", "id",on_delete="CASCADE"). (#530)
• Foreign keys declared as REFERENCES other_table with no explicit column are now resolved to the other table’s primary key by table.foreign_keys, instead of reporting other_column=None.
• Fixed a TypeError when sorting ForeignKey objects where some were compound.

Case-insensitive column matching:

Column names passed to Python API methods are now matched against the table schema case-insensitively, mirroring how SQLite itself treats identifiers. Previously many methods accepted mixed-case identifiers in the SQL they generated but then failed - or silently did nothing - when performing Python-side comparisons against the schema. (#760) Fixes include:

• table.insert() and table.upsert() now populate table.last_pk correctly when the pk=argument uses different casing to the table schema or the record keys - previously this raised a KeyError after the row had already been written.
• Upserts no longer raise or misbehave when the casing of pk= differs from the casing of the record keys. The primary key columns are correctly excluded from the generated DO UPDATE SET clause.
• table.transform() arguments types=, rename=, drop=, pk=, not_null=, defaults=, column_order= and drop_foreign_keys= all resolve column names case-insensitively. Previously options like rename={"name": "title"} against a column called Name were silently ignored.
• db.create_table(..., transform=True) now recognizes existing columns that differ only by case, instead of attempting to add them again and failing with duplicate column name. The casing used in the existing schema is preserved.
• table.lookup() returns the primary key value even if pk= casing differs from the schema, and recognizes existing unique indexes case-insensitively instead of creating redundant ones.
• table.extract() and table.convert() - including multi=True and output= - accept column names in any casing.
• Foreign key columns are validated and recorded using the casing of the actual schema columns, in foreign_keys= when creating tables, db.add_foreign_keys(), table.add_foreign_key() and table.add_column(fk_col=...). Duplicate foreign key detection is also case-insensitive.
• table.create() with pk=, not_null=, defaults= or column_order= referencing columns using different casing no longer creates an unwanted extra primary key column or raises a ValueError.

Everything else:

• Fixed a bug where table.transform() could convert DEFAULT TRUE, DEFAULT FALSE and DEFAULTNULL column defaults into quoted string defaults when rebuilding a table. Thanks, Vincent Gao. (#764)