Dropping a column with Room AutoMigration-CodePudding

I am trying to drop a column oldColumn from my table EverChangingTable.

I bumped the version number in my @Database annotation...

I added an AutoMigration with a spec consistent with what the Room error is asking (however the Room error seems to contradict itself).

Entity:

@Entity
data class EverChangingTable(
    @PrimaryKey
    val id: Long,
    val thing: Double,
    // Removing the old column
    // @ColumnInfo(name = "oldColumn", defaultValue = "0")
    // val oldColumn: Long,
)

Database and AutoMigrations:

@Database(
    entities = [
        EverChangingTable::class,
        NeverChangingTable::class,
    ],
    version = 101,
    exportSchema = true,
    autoMigrations = [
        ...,
        AutoMigration(from = 100, to = 101, spec = MyDb.DeleteOldColumn::class),
    ]

    @DeleteColumn(
        tableName = "EverChangingTable",
        columnName = "oldColumn",
    )
    class DeleteOldColumn : AutoMigrationSpec
}

There is a compile time error that is telling me to either rename or delete the column.

          AutoMigration Failure: Please declare an interface extending 'AutoMigrationSpec',
          and annotate with the @RenameColumn or @RemoveColumn annotation to specify the
          change to be performed:
          1) RENAME:
              @RenameColumn(
                      tableName = "EverChangingTable",
                      fromColumnName = "oldColumn",
                      toColumnName = <NEW_COLUMN_NAME>
              )
          2) DELETE:
              @DeleteColumn=(
                      tableName = "EverChangingTable",
                      columnName = "oldColumn"
              )

First, the error says to declare an interface, but actually requires a class. Second the error says to use @RemoveColumn, gives the example of @DeleteColumn, but I'm using @DeleteColumn and it's not working, still giving me the same error.

How do you delete a column with an AutoMigration?

CodePudding user response：

The following works (in theory see the GOTCHA)

@Database(
    entities = [
        EverChangingTable::class,
        /*NeverChangingTable::class,*/
    ],
    version = DB_VERSION,
    exportSchema = true,
    autoMigrations = [
        AutoMigration(100,101,DeleteOldColumn::class)
    ]
)

abstract class MyDb: RoomDatabase()
@DeleteColumn(tableName = "EverChangingTable", columnName = "oldColumn")
class DeleteOldColumn : AutoMigrationSpec

First run (version 100)

with EverChangingTable being :-

@Entity(tableName = "EverChangingTable")
data class EverChangingTable(
    @PrimaryKey
    val id: Long,
    val thing: Double,
    //Removing the old column
    @ColumnInfo(name = "oldColumn", defaultValue = "0")
    val oldColumn: Long
)

and the following in an activity (MainActivity) to force use of the database:-

const val DB_VERSION = 100;
class MainActivity : AppCompatActivity() {
    lateinit var db: MyDb
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = Room.databaseBuilder(this,MyDb::class.java,"mydb.db")
            .allowMainThreadQueries()
            .build()
        /* ensure that the database is accessed (opened) by doing something */
        db.getOpenHelper().writableDatabase
    }
}

Then using App Inspection :-

i.e. olcColumn exists and the version is 100

Second Run (Version 101)

with EverChangingTable now as :-

@Entity(tableName = "EverChangingTable")
data class EverChangingTable(
    @PrimaryKey
    val id: Long,
    val thing: Double,
    //Removing the old column
    //@ColumnInfo(name = "oldColumn", defaultValue = "0")
    //val oldColumn: Long
)

and MainActivity changed to use

const val DB_VERSION = 101;

Then the compile is fine:-

The GOTCHA

When run it crashes due to :-

2022-07-12 07:20:09.061 17414-17414/com.example.so72941592kotlinroomautomigrateremovecolumn E/SQLiteLog: (1) no such table: _new_ in "INSERT INTO `_new_` (`id`,`thing`) SELECT `id`,`thing` FROM `EverChangingTable`"
2022-07-12 07:20:09.582 17414-17414/com.example.so72941592kotlinroomautomigrateremovecolumn E/SQLiteLog: (1) no such table: _new_ in "INSERT INTO `_new_` (`id`,`thing`) SELECT `id`,`thing` FROM `EverChangingTable`"

Now looking at the generated code (as pointed to in the log) then it is :-

@SuppressWarnings({"unchecked", "deprecation"})
class MyDb_AutoMigration_100_101_Impl extends Migration {
  private final AutoMigrationSpec callback = new DeleteOldColumn();

  public MyDb_AutoMigration_100_101_Impl() {
    super(100, 101);
  }

  @Override
  public void migrate(@NonNull SupportSQLiteDatabase database) {
    database.execSQL("CREATE TABLE IF NOT EXISTS `_new_EverChangingTable` (`id` INTEGER NOT NULL, `thing` REAL NOT NULL, PRIMARY KEY(`id`))");
    database.execSQL("INSERT INTO `_new_` (`id`,`thing`) SELECT `id`,`thing` FROM `EverChangingTable`");
    database.execSQL("DROP TABLE `EverChangingTable`");
    database.execSQL("ALTER TABLE `_new_` RENAME TO `EverChangingTable`");
    callback.onPostMigrate(database);
  }
}

As can be seen it creates a table, based upon the new schema (i.e. no oldColumn) called _new_EverChangingTable

BUT then refers to it is only _new_, i.e. the generated code is incorrect and this is a bug but would otherwise undertake the correct migration actions.

Room versions 2.5.0-alpha02 and also 2.5.0-alpha01 were used, both with the same result.

BUG - Circumvention

To get around the bug, the following was successful :-

const val DB_VERSION = 101;
class MainActivity : AppCompatActivity() {
    lateinit var db: MyDb
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = Room.databaseBuilder(this,MyDb::class.java,"mydb.db")
            .allowMainThreadQueries()
            .addMigrations(Mig100To101)
            .build()
        /* ensure that the database is accessed (opened) by doing something */
        db.getOpenHelper().writableDatabase
    }    
    val Mig100To101 = Migration(100,101) {
        it.execSQL("CREATE TABLE IF NOT EXISTS `_new_EverChangingTable` (`id` INTEGER NOT NULL, `thing` REAL NOT NULL, PRIMARY KEY(`id`))")
        it.execSQL("INSERT INTO `_new_EverChangingTable` (`id`,`thing`) SELECT `id`,`thing` FROM `EverChangingTable`")
        it.execSQL("DROP TABLE `EverChangingTable`")
        it.execSQL("ALTER TABLE `_new_EverChangingTable` RENAME TO `EverChangingTable`")
    }
}

and finally :-

CodePudding user response：

You mentioned wrongly (Table name)

Update your delete column annotation

@DeleteColumn(
    tableName = "EverChangingTable",
    columnName = "oldColumn",
)