SQLite: a migration that rebuilds a table silently drops every complex index on it #86

Problem

SQLite cannot alter a column, drop a column, or add or drop a key, foreign key, unique or check constraint in place, so EF Core's SqliteMigrationsSqlGenerator rebuilds the table (CREATE TABLE "ef_temp_…", copy, DROP TABLE, rename) and re-creates the indexes in EF's model. Complex indexes are not in EF's model. Every complex index on the table, unique ones included, is gone after the migration, the migration applies cleanly, and the pending-changes check reports nothing, since the snapshot still says the index exists.

b.Property(x => x.Nickname).IsRequired();          // V1; V2 makes it optional
b.HasComplexIndex(x => x.Email.Value, isUnique: true, indexName: "ux_members_email");

Applied for real on SQLite (in memory), with the stock generator and the migration's target model:

after migration 1: IX_members_Nickname, ux_members_email
migration 2:  CREATE TABLE "ef_temp_members" (…); INSERT …; DROP TABLE "members";
              ALTER TABLE "ef_temp_members" RENAME TO "members"; CREATE INDEX "IX_members_Nickname" …
after migration 2: IX_members_Nickname

Duplicate emails are accepted from then on. The same for a DropColumn on the table, and for a complex index added in the same migration as such a change: its CREATE INDEX runs before the rebuild.

Why the differ cannot simply re-create it

The fix used for SQL Server (#84), dropping the index before the base operations and creating it after them, does not work here: SqliteMigrationsSqlGenerator.RewriteOperations (EF Core release/10.0) emits every table rebuild after all other operations of the migration, so any CREATE INDEX the differ emits still runs before the rebuild and is lost with the old table. Verified: with the drop/create bracket in place the index is still gone.

Fix for 5.5.0: fail loudly

At migrations add on SQLite, when the base operations make SQLite rebuild a table that carries a complex index in the target model, throw with the table, the operation, the indexes it would lose, and the remedy: split the change into three migrations (remove the complex index declarations on that table; make the change; declare them again), none of which rebuilds a table carrying a complex index.

Later

Re-creating the indexes after the rebuild needs a step that runs after EF's rewrite: a wrapper around the SQLite migrations SQL generator registered by UseComplexIndexes(), with the runtime-wiring sentinel so a missing registration fails loudly. That is a new runtime component and needs its own design; proposed for 5.6.0.

Upgrading note

A SQLite database migrated with such a change before this fix is missing those indexes, while its snapshot lists them. Check with SELECT name FROM sqlite_master WHERE type = 'index' and re-create them with migrationBuilder.Sql(...) in a new migration.

Found by the 5.5.0 pre-tag analysis (core). Present since the first release.