Room database migration with missing 1.json schema file

Question

I am about the migrate my room database from 1 to 2. In version 1 exportSchema was set to false. I was unaware of the impact at the time. Therefore no 1.json schema file is available on device running the app so far.

In version 1 there is a class, let's call it Mango as follows:

@Entity(tableName="mango)
data class Mango(
    @PrimaryKey(autoGenerate = true) val id: Int =0,
    val carbs: Float = 0f
){...}

In version 2 the field carbs should change to carbohydrate. This is how I do it in my RoomDatabase class.

@Database(
    entities = [Mango::class], version = 2, exportSchema = true
)
abstract class AppDatabase : RoomDatabase() {
...
        fun getDatabase(
        context: Context
    ): AppDatabase {
        return INSTANCE ?: synchronized(this) {
            val instance = Room.databaseBuilder(
                context,
                AppDatabase::class.java,
                "my_database"
            ).addMigrations(MIGRATION_1_2).build()
            INSTANCE = instance
            instance
        }
    }
 ...

val MIGRATION_1_2 = object : Migration(1, 2) {
override fun migrate(database: SupportSQLiteDatabase) {
    database.execSQL("ALTER TABLE Mango RENAME COLUMN cabrs TO carbohydrate")
}

It works on emulator in android studio. And this is how I test it.

1. Uninstall the app
2. Run version 1
3. Switch code to version 2 and run version 2

Result: Working

I have created a release version and sent it via google play to test it in a real life stuation, and I get the following error when I update the app with the new version 2:

Fatal Exception: android.database.sqlite.SQLiteException: near "COLUMN": syntax error (code 
1): , while compiling: ALTER TABLE Mango RENAME COLUMN carbs TO carbohydrate
#################################################################
Error Code : 1 (SQLITE_ERROR)
Caused By : SQL(query) error or missing database.
(near "COLUMN": syntax error (code 1): , while compiling: ALTER TABLE Mango RENAME COLUMN 
carbs TO carbohydrate).

If it is because of the missing 1.json file, then how can I fix this?

Thanks

Answer 1

If it is because of the missing 1.json file, then how can I fix this?

The issue is probably not the schema but is probably that the "real life run" was on an android version that doesn't include release 3.25.0 or greater of SQLite.

• Typically unsupported features result in a Syntax Error that can be confusing is it tends to mention where the error was found as it doesn't know about the new syntax.

i.e. only devices with API 30+ (when a jump was made from 3.22.0 to 3.28.0)

as per https://developer.android.com/reference/android/database/sqlite/package-summary

SQLite 3.25.0 release documentation:-

2018-09-15 (3.25.0)

• Add support for window functions
• Enhancements the ALTER TABLE command:
  • Add support for renaming columns within a table using ALTER TABLE table RENAME COLUMN oldname TO newname. -Fix table rename feature so that it also updates references to the renamed table in triggers and views.
• ....

If you need to target devices less than API 30, then you may not be able to use AutoMigration but will instead have a manual migration that:-

1. renames the original table
2. create the new table (copy the SQL from the createAllTables method in the class that is the same name as the @Database annotated class but suffixed with _Impl that can be found in the java(generated) via the Android View (I believe CRTL B can also be used)).
3. then execute the SQL INSERT INTO <new_table> SELECT * FROM <the_renamed_original_table>
   1. Note that this assumes that the columns are in exactly the same position. It would be safer to use INSERT INTO new_table (<ALL_THE_COLUMNS_NAMES_OF_THE_NEW_TABLE_COMMA_SEPARATED>) SELECT (<THE_RESPECTIVE_COLUMNS_OF_THE_RENAMED_TABLE_COMMA_SEPARATED>)

• Note anything enclosed within < and > should be changed accordingly, the enclosed text explains the change(s)

• Note the above is in-principle code, it has not been compiled/run or tested so may contain some minor errors.