I'm doing a migration to create new sql table called: category. I had to do a manual migration. When I run the app, the migration fails because the expected object has indices and the found object doesn't, as below (at the end):

java.lang.IllegalStateException: Migration didn't properly handle: category(data.local.CategoryEntity).
                                                                                                 

Expected:                                                                                                
TableInfo{name='category', columns={name=Column{name='name', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, color=Column{name='color', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, parentId=Column{name='parentId', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[ForeignKey{referenceTable='category', onDelete='CASCADE', onUpdate='CASCADE', columnNames=[parentId], referenceColumnNames=[id]}], indices=[Index{name='index_category_parentId', unique=false, columns=[parentId], orders=[ASC]}]}
  
                                                                                               
Found:                                                                                             
TableInfo{name='category', columns={color=Column{name='color', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, name=Column{name='name', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, parentId=Column{name='parentId', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[ForeignKey{referenceTable='category', onDelete='CASCADE', onUpdate='CASCADE', columnNames=[parentId], referenceColumnNames=[id]}], indices=[]}

 

Here's my CategoryEntity.kt:

@Entity(tableName = "category",
    foreignKeys = [ForeignKey(
        entity = CategoryEntity::class,
        parentColumns = arrayOf("id"),
        childColumns = arrayOf("parentId"),
        onDelete = ForeignKey.CASCADE,
        onUpdate = ForeignKey.CASCADE
    )],
    indices=[
        Index(value = arrayOf("parentId"))
    ]
)
data class CategoryEntity(
    @PrimaryKey(autoGenerate = true) val id: Int = 0,
    @ColumnInfo(name = "name") val name: String,
    @ColumnInfo(name = "parentId") val parentId: Int?,
    @ColumnInfo(name = "color") val color: String
)

                                                                                              

Here's my AppDatabase.kt (I don't know how to make the app connect the foreign key constraint index with the expected result)

@Database(
    entities = [
                ToDoEntity::class,
                CategoryEntity::class
               ],
    version = 2

)
@TypeConverters(Converters::class)
abstract class AppDatabase: RoomDatabase() {

    abstract val toDoDao: ToDoDao
    abstract val categoryDao: CategoryDao

    companion object Migrations {
        val M_1_2 = object : Migration(1, 2) {
            override fun migrate(database: SupportSQLiteDatabase) {
                database.execSQL(
                    "CREATE TABLE IF NOT EXISTS `category` ("  
                            "`id` INTEGER NOT NULL, "  
                            "`name` TEXT NOT NULL, "  
                            "`parentId` INTEGER, "  
                            "`color` TEXT NOT NULL , "  
                            "PRIMARY KEY(`id`),"  
                            "CONSTRAINT `index_category_parentId` FOREIGN KEY(parentId) REFERENCES category(id) ON UPDATE CASCADE ON DELETE CASCADE"  
                            ")"
                )
            }
        }
    }
}

How can I define the indices in the manual migration? Ps: The index cannot be a unique for parentId.

CodePudding user response：

How can I define the indices in the manual migration?

Let Room tell you.

1. Compile the project (which you have done, but suggested as a step to always ensure that changes made are caught).

2. In Android View look the java(generated) open it.

3. Find the sub-directory (open all in turn) that has a file (class) that is the same name as the @Database annotated class but suffixed with _Impl i.e. AppDatabase_Impl

4. Find the method createAllTables and the SQL for creating all the components, including the indexes ('CREATE INDEX ....'). Copy and paste the SQL and use in the Migration.

   • This SQL will create the components EXACTLY AS EXPECTED.
   • Note ignore the room_master_table (create table and insert) these are Room specific and room will create them as required
     • used to store the hash of the schema which is compared against the compiled hash as part of the processess of detecting schema changes (i.e. changes made to the @Entity annotated classes listed via the entities parameter of the @Database annotation)