Confusion regarding "Handling column default value when upgrading to Room 2.2.0"?

Question

I'm referring to https://developer.android.com/training/data-storage/room/migrating-db-versions.md#handle-default-values-migrations

I'm kinna confused with the guideline. My understanding is that

If you add a NON NULL new column

@Entity
public class Song {
    // ...
    @NonNull
    final String tag;
}

Using the following ALTER TABLE migration strategy is wrong

---

Wrong

static final Migration MIGRATION_1_2 = new Migration(1, 2) {
    @Override
    public void migrate(SupportSQLiteDatabase database) {
        database.execSQL(
            "ALTER TABLE Song ADD COLUMN tag TEXT NOT NULL DEFAULT ''");
    }
};

---

You need to use the following drop and re-create migration strategy

Correct

static final Migration MIGRATION_2_3 = new Migration(2, 3) {
    @Override
    public void migrate(SupportSQLiteDatabase database) {
        database.execSQL("CREATE TABLE new_Song (" +
                "id INTEGER PRIMARY KEY NOT NULL," +
                "name TEXT," +
                "tag TEXT NOT NULL DEFAULT '')");
        database.execSQL("INSERT INTO new_Song (id, name, tag) " +
                "SELECT id, name, tag FROM Song");
        database.execSQL("DROP TABLE Song");
        database.execSQL("ALTER TABLE new_Song RENAME TO Song");
    }
};

---

That even confuse me. Isn't both methods, will end up having the same column - tag TEXT NOT NULL DEFAULT '' ? How does the former method is marked as a problematic method?

Can someone can provide an simple example, to explain what problem might occur by using ALTER TABLE, and how drop and re-create might overcome the problem?

Thanks.

Answer 1

After having discussion with the original author via https://issuetracker.google.com/issues/137515134, here's a simple to understood example.

---

version 1, Room 2.1.0

@Entity
public class Song {
    @PrimaryKey
    final long id;
}

version 2, Room 2.1.0 (Added @NonNull)

@Entity
public class Song {
    @PrimaryKey
    final long id;
    @NonNull
    final String tag;
}

static final Migration MIGRATION_1_2 = new Migration(1, 2) {
    @Override
    public void migrate(SupportSQLiteDatabase database) {
        database.execSQL(
            "ALTER TABLE Song ADD COLUMN tag TEXT NOT NULL DEFAULT ''");
    }
};

version 3, Room 2.2.0 (Upgraded Room to 2.2.0, Added @ColumnInfo)

@Entity
public class Song {
    @PrimaryKey
    final long id;
    @ColumnInfo(defaultValue = "")
    @NonNull
    final String tag;
}

static final Migration MIGRATION_2_3 = new Migration(2, 3) {
    @Override
    public void migrate(SupportSQLiteDatabase database) {
        database.execSQL("CREATE TABLE new_Song (" +
                "id INTEGER PRIMARY KEY NOT NULL," +
                "tag TEXT NOT NULL DEFAULT '')");
        database.execSQL("INSERT INTO new_Song (id, tag) " +
                "SELECT id, tag FROM Song");
        database.execSQL("DROP TABLE Song");
        database.execSQL("ALTER TABLE new_Song RENAME TO Song");
    }
};

Reader Note

1. Database version 2 (who never went through MIGRATION_1_2) doesn't have DEFAULT '' in its SQLite schema. MIGRATION_2_3 is required.

2. Database version 2 (who already went through MIGRATION_1_2) has DEFAULT '' in its SQLite schema. MIGRATION_2_3 has no harm.

3. In SQLite, there is no way to ALTER an column to add DEFAULT '' constraint.