Sqlite Migrations: Table rebuilds

[bricelam]

In SQLite, the following operations will require rebuilding the table - https://docs.microsoft.com/en-us/ef/core/providers/sqlite/limitations.

Other databases also have a smaller set of limitations that require a table rebuild (i.e. changing Identity on a column in SQL Server).

[ErikEJ]

Yes, great RDBMS

[bricelam]

Re-opening. I've "regressed" this. (not sure if it ever actually worked since there weren't any tests)

[natemcmaster]

I've discovered a limitation that makes me wonder if the complication of rebuilds will exceed their value.

The limitation PRAGMA foreign_keys=on/off; does not work within a transaction.

Consider the following setup:

Table "Parent" - no FK's
Table "Children" - references "Parent" via a FK

1. Scenario 1 - renaming "Parent" to "NewTableName". If the alter table statement executes with PRAGMA foreign_keys=ON;, then SQLite will handle updating the "Children" reference automatically. Otherwise, it will break the FK relationship.
2. Scenario 2 - rebuilding "Parent" ( to imitate drop column or some other operation not natively available). This must be executed with PRAGMA foreign_keys=OFF; otherwise the rebuild looks to SQLite like Scenario 1 and "Children" will end up reference a table which is deleted in the rebuild.

Scenario 1 and 2 could be resolved by just executing the proper PRAGMA foreign_keys before renaming/rebuilding if not for this limitation:

This pragma is a no-op within a transaction; foreign key constraint enforcement may only be enabled or disabled when there is no pending BEGIN or SAVEPOINT.

https://www.sqlite.org/pragma.html#pragma_foreign_keys

This means we cannot execute an entire migration within one transaction, but need to break SQLite migrations into multiple transactions. Reverting to the beginning of a migration becomes impossible.

[ErikEJ]

You are entering a World of Pain 😃

[natemcmaster]

Moving to backlog to work on high-priority items.
Consider again after #1141 and #2558.

[bricelam]

@natemcmaster Did you try the approach recommended in the SQLite documentation: Making Other Kinds Of Table Schema Changes? Triggers will be lost. Views may be broken. Indexes (like columns) would be re-created from the model snapshot. But otherwise it looks like it'll work.

Note, we won't be able to use the simpler and faster procedure since SQLite doesn't support variables and Migrations is designed in a way that lets you generate SQL scripts.

[bricelam]

Making this a pri1 issue given the new information.

[bricelam]

Nothing really new here, just jotting down some notes since I started thinking about this again today:

// UNDONE: Not supported by SQLite
//migrationBuilder.AlterColumn<string>(
//    name: "Title",
//    table: "Posts",
//    nullable: false,
//    defaultValue: "",
//    oldClrType: typeof(string),
//    oldType: "TEXT",
//    oldNullable: true);

// Create a new table with the desired schema
migrationBuilder.CreateTable(
    name: "ef_temp_Posts",
    columns: table => new
    {
        Id = table.Column<int>(nullable: false)
            .Annotation("Sqlite:Autoincrement", true),
        Title = table.Column<string>(nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Posts", x => x.Id);
    });

// Create temporary UNIQUE indexes to enforce during INSERT
migrationBuilder.CreateIndex(
    name: "ef_temp_IX_Posts_Title",
    table: "ef_temp_Posts",
    column: "Title",
    unique: true);

// Copy data from the old table. Use IFNULL to specify a default value for newly
// required columns
migrationBuilder.Sql(@"
    INSERT INTO ef_temp_Posts (Id, Title)
    SELECT Id, IFNULL(Title, '')
    FROM Posts;
");

// Suspend foreign key enforcement during the swap
// NB: This commits the current transaction. We can't rollback the migration if
// anything after this fails. We can mitigate it by doing rebuilds as late as
// possible
migrationBuilder.Sql("PRAGMA foreign_keys = 0;", suppressTransaction: true);

// Swap in the new table
migrationBuilder.DropTable(
    name: "Posts");
migrationBuilder.RenameTable(
    name: "ef_temp_Posts",
    newName: "Posts");

// NB: This will cause an integrity check which may fail if foreign keys were
// not previously enforced and inconsistent data has crept in
migrationBuilder.Sql("PRAGMA foreign_keys = 1;", suppressTransaction: true);

// Drop temporary UNIQUE indexes
migrationBuilder.DropIndex(
    name: "ef_temp_IX_Posts_Title",
    table: "Posts",
    column: "Title");

// Rebuild any indexes
migrationBuilder.CreateIndex(
    name: "IX_Posts_Title",
    table: "Posts",
    column: "Title",
    unique: true);

[bricelam]

I looked into using PRAGMA defer_foreign_keys to avoid suppressTransaction: true, but dropping the table sill cascade-deletes dependents.

We discussed the consequences of suppressTransaction: true in a design meeting today and decided that it is OK to do this by default on SQLite. We did this for certain scenarios in EF6 on SQL Server and never heard any negative feedback. An option to disable it (and thus table rebuilds) would, however, be nice to have.

We also discussed blindly turning foreign keys back on (when we don't know if they were enabled in the first place) and decided that it's OK. If you turn them off for performance reasons, your data should still be consistent otherwise additional things in EF will break. The default foreign key behavior will just be reset when the connection is closed and re-opened after migrating anyway.

Other points that came up in the discussion:

• We should warn when we do a rebuild that any changes you've made outside of your EF model may be lost. (But this shouldn't really be an issue if you're using migraitons to manage your database schema)
• There's no value in re-using parts of the query or update pipeline to generate the INSERT INTO..SELECT statements. Just use the usual mechanism for generating SQL in migrations
• No need to support table rebuilds on SQL Server as part of this. Re-openeing SqlServer Migrations: Rebuild column when IDENTITY changes #2100 for doing column rebuilds when IDENTITY changes

[danielmeza]

From the SQLiteStudio there is a short sintax to clone the original table

CREATE TABLE sqlitestudio_temp_table AS SELECT *
                                          FROM Table;

this way we can reduce the complexity.

[ir2000]

@danielmeza you would lose primary key and foreign keys

A table created using CREATE TABLE AS has no PRIMARY KEY and no constraints of any kind. The default value of each column is NULL. The default collation sequence for each column of the new table is BINARY.
https://www.sqlite.org/lang_createtable.html#createtabas

[InteXX]

@ir2000 @danielmeza

Yes, in that single statement data is lost. But it's a valuable technique for cloning the schema.

One could then clear out the data and repopulate, bringing all keys along this time.

[danielmeza]

@ir2000 yes but we not need that info in the temp table.

[danielmeza]

@bricelam we should cover in this version escenarios with an existing db? Example: code first from db escenarios.

[Damus765]

@danielmeza I would say we need to create every constraint in the "temp table". Because what you call "temp table" is not really temporary. It is in fact the new table that will be kept once renamed. So I'm not sure CREATE TABLE AS is the best way to create the new table.

[danielmeza]

@Damus765 I was missing the rename process, I thought that the data will be copy to the new table so the process goes like this. Create the temp table -> delete the old table -> create the new table with the new schema -> copy data back from the temp table -> delete the temp table.

[Damus765]

@danielmeza According to the SQLite documentation, the recommended steps are as follows:

1. Create new table
2. Copy data from old table into new table
3. Drop old table
4. Rename new into old

Only one data copy is required and you don't need a temp table.

[bricelam]

@danielmeza Our strategy for using migrations with existing databases is tracked by #2167

[bricelam]

More related issues for SQL Server: #12586, #16758, #18965 & #20943

[bricelam]

But seriously, my six-year-old wasn't even born when I filed this issue. Crazy.

May all your rebuilding dreams come true! Check out our daily builds if you're eager to give it a try. Let me know if you find any bugs. (I still have a bit more testing to do myself.)