Room Migration Failed When Adding Table-CodePudding

I am trying to perform a migration on my database.

The table I am trying to add is as follows:

@Entity(tableName = "Recipe_Of_Day_Entity")
data class RecipeOfDayDTO(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id : String = UUID.randomUUID().toString(),
    @ColumnInfo(name = "vegetarian")
    val vegetarian: Boolean,
    @ColumnInfo(name = "vegan")
    val vegan: Boolean,
    @ColumnInfo(name = "glutenFree")
    val glutenFree: Boolean,
    @ColumnInfo(name = "dairyFree")
    val dairyFree: Boolean,
    @ColumnInfo(name = "veryHealthy")
    val veryHealthy: Boolean,
    @ColumnInfo(name = "image")
    val image: String?,
    @ColumnInfo(name = "imageType")
    val imageType: String?,
    @ColumnInfo(name = "instructions")
    val instructions: String?)

The migration object is defined below:

val MIGRATION_4_5: Migration = object : Migration(4, 5) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // https://developer.android.com/reference/android/arch/persistence/room/ColumnInfo
        database.execSQL("CREATE TABLE IF NOT EXISTS `RecipeOfDayDTO` (`id` TEXT NOT NULL, `vegetarian` "  
                "INTEGER NOT NULL, `vegan` INTEGER NOT NULL, `glutenFree` INTEGER NOT NULL, `dairyFree` INTEGER NOT NULL,"  
                "`veryHealthy` INTEGER NOT NULL, `image` TEXT NOT NULL, `imageType` TEXT NOT NULL, `instructions` TEXT NOT NULL)")
    }
}

I am building the database as below:

@Database(entities = [IngredientDataClassDTO::class, RecipeNotificationClassDTO::class, RecipeOfDayDTO::class], version = 5, exportSchema = true)
abstract class IngredientDatabase : RoomDatabase() {
.
.
.
Room.databaseBuilder(
                        context.applicationContext,
                        IngredientDatabase::class.java,
                        "saved_ingredient_database"
                    )
           
                        // https://medium.com/androiddevelopers/understanding-migrations-with-room-f01e04b07929
                        .addMigrations(MIGRATION_4_5)
                        .build()

I have made sure that there are no difference between the expected and found tables as suggested in

I don't want to destroy my previous database so I am avoiding using fallbackToDestructiveMigration(). Invalidating & Restarting did not remove the error. Has anyone else encountered this issue?

CodePudding user response：

RecipeOfDayDTO.image, RecipeOfDayDTO.imageType, RecipeOfDayDTO.instructions is nullable String, so your migration SQL must be like that;

database.execSQL("CREATE TABLE IF NOT EXISTS `RecipeOfDayDTO` (`id` TEXT NOT NULL, `vegetarian` "  
                "INTEGER NOT NULL, `vegan` INTEGER NOT NULL, `glutenFree` INTEGER NOT NULL, `dairyFree` INTEGER NOT NULL,"  
                "`veryHealthy` INTEGER NOT NULL, `image` TEXT NULL, `imageType` TEXT NULL, `instructions` TEXT NULL)")```

CodePudding user response：

The way to match the expected and found is to make the changes to the class(es) annotated with @Entity, ensure that the class is defined as an entity in the list of entities of the @Database class and then compile (CTRL F9) and to then inspect the generated java (via Android View).

You look at the class that is he same name but suffixed with _Impl as the class annotated with @Database and at the method named createAllTables which has the SQL that Room expects for each table. It is then a simple matter of copying the respective SQL for the new table(s). For other changes such as altering a table you can base the resultant table(s)/columns on the SQL found in the generated java.

e.g. Adding RecipeOfDayDTO to an existing project and then adding the it to the @Database class

results in the following method being found in the generated java :-

  @Override
  public void createAllTables(SupportSQLiteDatabase _db) {
    _db.execSQL("CREATE TABLE IF NOT EXISTS `NoteEntity` (`id` INTEGER, `startRef` INTEGER NOT NULL, `endRef` INTEGER NOT NULL, `content` TEXT NOT NULL, PRIMARY KEY(`id`))");
    _db.execSQL("CREATE TABLE IF NOT EXISTS `Recipe_Of_Day_Entity` (`id` TEXT NOT NULL, `vegetarian` INTEGER NOT NULL, `vegan` INTEGER NOT NULL, `glutenFree` INTEGER NOT NULL, `dairyFree` INTEGER NOT NULL, `veryHealthy` INTEGER NOT NULL, `image` TEXT, `imageType` TEXT, `instructions` TEXT, PRIMARY KEY(`id`))");
    _db.execSQL("CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)");
    _db.execSQL("INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, 'c8707866be882430083eee62243a71ed')");
  }

So it's just a matter of copying the SQL :-

"CREATE TABLE IF NOT EXISTS `Recipe_Of_Day_Entity` (`id` TEXT NOT NULL, `vegetarian` INTEGER NOT NULL, `vegan` INTEGER NOT NULL, `glutenFree` INTEGER NOT NULL, `dairyFree` INTEGER NOT NULL, `veryHealthy` INTEGER NOT NULL, `image` TEXT, `imageType` TEXT, `instructions` TEXT, PRIMARY KEY(`id`))"

into the migration, no need to spend time trying to ascertain what does and doesn't match.

With version 2.4.0-aplh01 and greater Room also support Automated Migrations (which would handle adding a table without complexities). However, they rely upon the exportSchema option being true and such a schema existing for the before and after versions.

see https://developer.android.com/training/data-storage/room/migrating-db-versions