Confusion on the defaultValue migration requirement for upgrade after Room 2.1.0

Question

In Room 2.1.0, it is common to have the following code

Version 2

@Entity(tableName = "password")
public class Password {
    @ColumnInfo(name = "dummy0")
    @NonNull
    public String dummy0;

}

public class Migration_1_2 extends Migration {
    public Migration_1_2() {
        super(1, 2);
    }

    @Override
    public void migrate(@NonNull SupportSQLiteDatabase database) {
        database.execSQL("ALTER TABLE password ADD COLUMN dummy0 TEXT NOT NULL DEFAULT ''");
    }
}

---

Migration guideline from

• https://developer.android.com/training/data-storage/room/migrating-db-versions.md#handle-default-values-migrations
• https://developer.android.com/jetpack/androidx/releases/room

are quite confusing.

Note: If your database schema already has default values, such as those added via ALTER TABLE x ADD COLUMN y INTEGER NOTNULL DEFAULT z, and you decide to define default values via @ColumnInfo to the same columns, then you might need to provide a migration to validate the unaccounted default values. See Room Migrations for more information.

---

Before upgrading to 2.2.3, there are 2 possibility

1. We have a dummy0 column with default value if migration runs.
2. Or, we have a dummy0 column without default value if this is fresh DB.

When we upgrade to Room 2.1.0 to Room 2.2.3, everything still work fine for both 2 cases, without having to add additional migration code, for drop-and-recreate table.

We do further testing.

Version 3

@Entity(tableName = "password")
public class Password {
    @ColumnInfo(name = "dummy0")
    @NonNull
    public String dummy0;

    @ColumnInfo(name = "dummy1")
    @NonNull
    public String dummy1;
}

public class Migration_2_3 extends Migration {
    public Migration_2_3() {
        super(2, 3);
    }

    @Override
    public void migrate(@NonNull SupportSQLiteDatabase database) {
        database.execSQL("ALTER TABLE password ADD COLUMN dummy1 TEXT NOT NULL DEFAULT ''");
    }
}

Still work fine.

Version 4

@Entity(tableName = "password")
public class Password {
    @ColumnInfo(name = "dummy0")
    @NonNull
    public String dummy0;

    @ColumnInfo(name = "dummy1")
    @NonNull
    public String dummy1;

    @ColumnInfo(name = "dummy2", defaultValue = "")
    @NonNull
    public String dummy2;
}

public class Migration_3_4 extends Migration {
    public Migration_3_4() {
        super(3, 4);
    }

    @Override
    public void migrate(@NonNull SupportSQLiteDatabase database) {
        database.execSQL("ALTER TABLE password ADD COLUMN dummy2 TEXT NOT NULL DEFAULT ''");
    }
}

Still work fine.

---

So, I'm confusing? Under what use case, we need to actually drop-and-recreate table?

Answer 1

I believe that the issue is not when adding a new column, but if the default value is applied/changed/removed to an existing column. It is then that you may have to recreate the affected table(s).

e.g. if you changed :-

@ColumnInfo(name = "dummy0")
@NonNull
public String dummy0;

to add a default value

@ColumnInfo(name = "dummy0", defaultValue = "")
@NonNull
public String dummy0;

Then there would be a schema mismatch because the expected schema would have DEFAULT '' whilst the found schema (original database) has no default coded.

• This would require that the table be dropped and recreated as you can't ALTER to change the column's attributes.

If prior to 2.2.0 you had a previous non-room generated schema that included default values and the Entities weren't altered accordingly then you would get the clash because expected schema doesn't have a default whilst the found schema contains DEFAULT = ''.

• This would require that the Entities be changed accordingly.

#Example

Assuming the current Entity is :-

@Entity(tableName = "password")
public class Password {
    @PrimaryKey
    public Long id;
    @ColumnInfo(name = "dummy0")
    @NonNull
    public String dummy0;

    @ColumnInfo(name = "dummy1")
    @NonNull
    public String dummy1;
}

Then the generated code to create the table is :-

_db.execSQL("CREATE TABLE IF NOT EXISTS `password` (`id` INTEGER, `dummy0` TEXT NOT NULL, `dummy1` TEXT NOT NULL, PRIMARY KEY(`id`))");

• App was run with the above to create the database.

If now for Version 2 it is changed to :-

@Entity(tableName = "password")
public class Password {
    @PrimaryKey
    public Long id;
    @ColumnInfo(name = "dummy0", defaultValue = "" /*<<<<<<<<<< ADDED */)
    @NonNull
    public String dummy0;

    @ColumnInfo(name = "dummy1")
    @NonNull
    public String dummy1;
}

Then the generated code is :-

_db.execSQL("CREATE TABLE IF NOT EXISTS `password` (`id` INTEGER, `dummy0` TEXT NOT NULL DEFAULT '', `dummy1` TEXT NOT NULL, PRIMARY KEY(`id`))");

Running with a dumb/empty migration (1-2) then :-

• The found schema (original DB) has :- defaultValue='null'
• But the expected schema has :- defaultValue=''''

As per :-

2020-01-11 19:11:15.300 12539-12539/a.so59691979 E/AndroidRuntime: FATAL EXCEPTION: main
    Process: a.so59691979, PID: 12539
    java.lang.RuntimeException: Unable to start activity ComponentInfo{a.so59691979/a.so59691979.MainActivity}: java.lang.IllegalStateException: Migration didn't properly handle: password(a.so59691979.Password).
     Expected:
    TableInfo{name='password', columns={dummy0=Column{name='dummy0', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue=''''}, dummy1=Column{name='dummy1', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[]}
     Found:
    TableInfo{name='password', columns={dummy0=Column{name='dummy0', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, dummy1=Column{name='dummy1', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[]}
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3270)

#Example Fix

Using a migration of :-

Migration M1_2 = new Migration(1,2) {
    @Override
    public void migrate(@NonNull SupportSQLiteDatabase database) {

        // CREATE SQL Copied from the generated Java PasswordDatabase_Impl (name changed)
        final String SQL_CREATE_NEW_PASSWORDTABLE =
                "CREATE TABLE IF NOT EXISTS `password_new` (`id` INTEGER, `dummy0` TEXT NOT NULL DEFAULT '', `dummy1` TEXT NOT NULL, PRIMARY KEY(`id`))";
        database.execSQL(SQL_CREATE_NEW_PASSWORDTABLE);
        database.execSQL("INSERT INTO `password_new` SELECT * FROM `password`");
        database.execSQL("ALTER TABLE `password` RENAME TO `password_old`");
        database.execSQL("ALTER TABLE `password_new` RENAME TO `password`");
        database.execSQL("DROP TABLE IF EXISTS `password_old`");
    }
}

Fixes the issue.

#Code

The following code was used for producing the above :-

Password.java

/*
//Original
@Entity(tableName = "password")
public class Password {
    @PrimaryKey
    public Long id;
    @ColumnInfo(name = "dummy0")
    @NonNull
    public String dummy0;

    @ColumnInfo(name = "dummy1")
    @NonNull
    public String dummy1;
}
*/

// New
@Entity(tableName = "password")
public class Password {
    @PrimaryKey
    public Long id;
    @ColumnInfo(name = "dummy0", defaultValue = "" /*<<<<<<<<<< ADDED */)
    @NonNull
    public String dummy0;

    @ColumnInfo(name = "dummy1")
    @NonNull
    public String dummy1;
}

• Original was initially used

PasswordDatabase.java

@Database(version = 2, entities = {Password.class})
public abstract class PasswordDatabase extends RoomDatabase {
}

• Initiall version was 1

MainActivity.java

public class MainActivity extends AppCompatActivity {

    PasswordDatabase passwordDatabase;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        passwordDatabase = Room.databaseBuilder(
                this,
                PasswordDatabase.class,
                "passworddb"
        )
                .allowMainThreadQueries()
                .addMigrations(M1_2)
                .build();
        passwordDatabase.getOpenHelper().getWritableDatabase();
    }

    Migration M1_2 = new Migration(1,2) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {

            // CREATE SQL Copied from the generated Java PasswordDatabase_Impl (name changed)
            final String SQL_CREATE_NEW_PASSWORDTABLE =
                    "CREATE TABLE IF NOT EXISTS `password_new` (`id` INTEGER, `dummy0` TEXT NOT NULL DEFAULT '', `dummy1` TEXT NOT NULL, PRIMARY KEY(`id`))";
            database.execSQL(SQL_CREATE_NEW_PASSWORDTABLE);
            database.execSQL("INSERT INTO `password_new` SELECT * FROM `password`");
            database.execSQL("ALTER TABLE `password` RENAME TO `password_old`");
            database.execSQL("ALTER TABLE `password_new` RENAME TO `password`");
            database.execSQL("DROP TABLE IF EXISTS `password_old`");
        }
    };
}

• Initially the body of M1_2 was empty (so as to force the error)