Release sqlite-utils 4.0 stable

[simonw]

After the two RCs I think this is pretty well baked. I've been running test suites from datasette and llm against it which are giving me confidence it's ready to go.

The only slightly tricky bit here is co-ordinating the release of sqlite-migrate. Current plan:

• Release a sqlite-migrate version that pins to sqlite-utils<4
• Release v4
• Release a new sqlite-migrate version that pins to sqlite-utils>=4.0 and eliminates all the code, just doing from sqlite_utils import Migrations. Retire this package in favor of sqlite-utils 4 sqlite-migrate#18

[simonw]

Refs:

• Run the new sqlite-utils 4.0rc in CI in a few places #758

[simonw]

Some issues to resolve before the 4 stable release, as fixing them later would require a 5 release:

• ensure_autocommit_off() is confusing #705
  • Rename ensure_autocommit_off() to ensure_autocommit_on() #776
• SQLite is case insensitive for column names, sqlite-utils is not #760
• Maybe compound foreign keys?
  • Support for compound (composite) foreign keys #117
  • Represent compound foreign keys in table.foreign_keys output #594

[simonw]

On compound foreign keys, I don't think I'm going to add support for creating them BUT issue #594 is about how they are currently incorrectly represented in table.foreign_keys output - as two separate keys, with the position information lost - which feels bad.

[simonw]

Landed quite comprehensive compound foreign key support in:

• table.foreign_keys now handles compound foreign keys #770

Including creating them!

[simonw]

There's enough in here that I think we should do another RC, so I can easily test it in datasette and llm.

[simonw]

More things to land before 4.0 final:

• Honor --no-headers for --fmt and --table output (#566) #751
• cli: output JSON without escaping non-ASCII characters #741
• Don't extract NULL values into a lookup row (#186) #750
• Fix KeyError in insert_all when pk column is missing from a single record #745
• Handle ignored inserts with explicit primary keys #756

[simonw]

LLM test suite passed with rc3 - https://github.com/simonw/llm/actions/runs/28770652532/job/85303561750

Datasette failed but it was that same issue as #775 so I fixed it in simonw/datasette@9a0b78b - still got a failure though: https://github.com/simonw/datasette/actions/runs/28770858457/job/85304155543

    def inner(conn):
        db = sqlite_utils.Database(conn)
        table_names = db.table_names()
        for table in db.tables:
            for fk in table.foreign_keys:
                other_table = fk.other_table
                other_column = fk.other_column
                message = 'Column "{}.{}" references other column "{}.{}" which does not exist'.format(
                    table.name, fk.column, other_table, other_column
                )
                assert other_table in table_names, message + " (bad table)"
>               assert other_column in db[other_table].columns_dict, (
                    message + " (bad column)"
                )
E               AssertionError: Column "catalog_columns.None" references other column "catalog_tables.None" which does not exist (bad column)
E               assert None in {'database_name': <class 'str'>, 'table_name': <class 'str'>, 'rootpage': <class 'int'>, 'sql': <class 'str'>}
E                +  where {'database_name': <class 'str'>, 'table_name': <class 'str'>, 'rootpage': <class 'int'>, 'sql': <class 'str'>} = <Table catalog_tables (database_name, table_name, rootpage, sql)>.columns_dict

[simonw]

That Datasette failure isn't a surprise, the test is test_internal_foreign_key_references and it's running into the changes we made with respect to compound foreign keys.