I have a database in Android with Room from which I have deleted a column. I was doing the migration, and I saw that it was not as simple as doing a DROP of the deleted column.
Then I have seen that I have to take a series of steps, creating a provisional table that will later be the new table with the deleted column, but the problem is that this table contains a field that is a String Array that I don't know how to declare in SQL.
@Entity(tableName = "recipe_table")
data class RecipesDb(
@PrimaryKey
@ColumnInfo(name = "id")
val id: Long,
@ColumnInfo(name = "name")
val name: String,
@ColumnInfo(name = "category")
val category: List<String>,
@ColumnInfo(name = "isRecommended")
val isRecommended: Boolean,
@ColumnInfo(name = "images")
val images: List<String>,
@ColumnInfo(name = "ingredients")
val ingredients: List<String>,
@ColumnInfo(name = "date")
val date: Long,
@ColumnInfo(name = "time")
val time: Int,
@ColumnInfo(name = "difficult")
val difficult: String,
@ColumnInfo(name = "originalUrl")
val originalURL: String? = null,
@ColumnInfo(name = "author")
val author: String,
@ColumnInfo(name = "siteName")
val siteName: String
)
And now I have removed the ingredients
column. I wanted to do something like this:
private val MIGRATION_3_2 = object : Migration(3,2) {
override fun migrate(database: SupportSQLiteDatabase) {
//Drop column isn't supported by SQLite, so the data must manually be moved
with(database) {
execSQL("CREATE TABLE Users_Backup (id INTEGER, name TEXT, PRIMARY KEY (id))")
execSQL("INSERT INTO Users_Backup SELECT id, name FROM Users")
execSQL("DROP TABLE Users")
execSQL("ALTER TABLE Users_Backup RENAME to Users")
}
}
}
But when I declare the new temporary table User_Backup, I have no idea how to specify that one of the fields is an Array. In the end I was able to do it with Room's AutoMigrations and creating an interface, but I would like to know how to do it this way as well.
The simple way is to compile the code (Ctrl+F9) with the changed @Entity annotated classes in the list of entities of the @Database annotation.
Then look at the generated java (visible via the Android View in Android Studio). There will be a class that is the same name as the @Database annotated class but suffixed with _Impl.
In this class there will be a method that is named createAllTables, This includes the SQL that room uses for creating the tables.
Just copy and paste the appropriate SQL and then change the table name, this will not only use the correct type but also apply the correct column constraints that Room expects.
I would suggest
execSQL("DROP TABLE IF EXISTS the_backup_table_name;")
before you create a table (just in case it already exists)execSQL("DROP TABLE Users")
to use execSQL("DROP TABLE IF EXISTS the_original_table_name")
I would use:-
private val MIGRATION_3_2 = object : Migration(3,2) {
override fun migrate(database: SupportSQLiteDatabase) {
//Drop column isn't supported by SQLite, so the data must manually be moved
with(database) {
execSQL("DROP TABLE IF EXISTS Users_Backup")
execSQL("CREATE TABLE IF NOT EXISTS ....) //<<<<< SEE NOTES BELOW, the SQL MUST BE CHANGED.
execSQL("INSERT INTO Users_Backup SELECT id, name FROM Users")
execSQL("ALTER TABLE Users RENAME TO Old_Users")
execSQL("ALTER TABLE Users_Backup RENAME to Users")
execSQL("DROP TABLE IF EXISTS Old_users")
}
}
}