migrating data with pre-populated database android room

my app mostly work with local database for this i am using room Prepopulate database for my project with Hilt and other jetpack component . Using DB Browser to create db file

Room.databaseBuilder(appContext, AppDatabase::class.java, "Sample.db")
    .createFromAsset("database/myapp.db")
//.fallbackToDestructiveMigration() 
//.addMigrations(MIGRATION_1_2)
    .build()

database is simple with 3 column id name and isAlive , isAlive is a boolean type and this will toggle as true or false .

suppose - initially their will be 10 rows only , in next update their will be 5 more rows and 1 new column strength, and this new column will be different for all 15 rows . How to migrate this without loosing previous data and adding new data

if i use .fallbackToDestructiveMigration() then all 15 rows with 4 column will be visible but isAlive value will be lost

if i use .addMigrations(MIGRATION_1_2) then my new 5 row are not getting view and strength column is set to 0

val MIGRATION_1_2 = object : Migration(2, 3) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("alter table user add column strength not null default 0")
    }
}

You could, in the Migration (as you need both the existing data and the new asset data), in addition to ALTERING the structure, open the asset as a database extract the data and then on a per row basis,

try to insert the data, but to IGNORE the insert if the row exists (i.e. duplicate id)

if the id exists to instead update the columns that are not user changeable (i.e. the isAlive column should be left asis).

I believe that you would have issues/complexities if you attempted this via Room.

The following is a working solution that instead uses the good old SQliteDatabase to handle the prepackgaed database and do the above.

@Database(entities = [User::class], exportSchema = false, version = 2 /* CHANGED FOR V2 */)
abstract class AppDatabase: RoomDatabase() {
    abstract fun getTheDAOs(): TheDAOs

    companion object {
        private var instance: AppDatabase?=null
        const val workingCopyExtension = "_wc" /* FOR MIGRATION */
        const val databaseName = "Sample.db" /* ADDED/SUGGESTED as value is used more than once */
        const val assetFileName = "database/myapp.db" /* ADDDED SUGGESTED as value is used more than once */
        lateinit var appContext: Context

        fun getInstance(context: Context): AppDatabase {
            appContext = context
            if (instance == null) {
                instance = Room.databaseBuilder(context,AppDatabase::class.java, databaseName)
                    .allowMainThreadQueries() /* For brevity and convenience of demo */
                    .addMigrations(MIGRATION_1_2)
                    .createFromAsset(assetFileName)
                    .build()
            }
            return instance as AppDatabase
        }

        val MIGRATION_1_2 = object: Migration(1,2) {
            @SuppressLint("Range")
            override fun migrate(database: SupportSQLiteDatabase) {
                database.execSQL("alter table user add column strength integer not null default 0")

                /* cope the asset file */
                getAssetFileCopy(context = appContext, assetFileName, databaseName)
                /* open the asset as an SQLiteDatabase */
                val asset = SQLiteDatabase.openDatabase(appContext.getDatabasePath(databaseName +workingCopyExtension).path,null,0)
                /* Extract all the data from the asset (now database) */
                val csr = asset.query("user",null,null,null,null,null,null,null)
                val cv = ContentValues()
                /* Loop through the extracted asset data */
                while (csr.moveToNext()) {
                    /* Prepare to INSERT OR IGNORE row as per Cursor */
                    cv.clear()
                    cv.put("id",csr.getInt(csr.getColumnIndex("id")))
                    cv.put("name",csr.getString(csr.getColumnIndex("name")))
                    cv.put("isAlive",csr.getInt(csr.getColumnIndex("isAlive")))
                    cv.put("strength",csr.getInt(csr.getColumnIndex("strength")))
                    /* Do the INSERT OR IGNORE testing the returned value (-1 if not inserted else 1 or greater) */
                    /* if inserted then as row did not exist then values are as per the asset so no need to do anything*/
                    /* if not inserted then the row existed so the strength should be applied i.e. as per the asset update */
                    if (database.insert("user",SQLiteDatabase.CONFLICT_IGNORE,cv) <= 0) {
                        cv.clear()
                        cv.put("strength",csr.getInt(csr.getColumnIndex("strength")))
                        database.update("user",OnConflictStrategy.IGNORE,cv,"id=?", arrayOf<String>(csr.getString(csr.getColumnIndex("id"))))
                    }
                }
                /* Clean up (close the Cursor and delete the )*/
                csr.close()
                asset.close() /* not too worried about closing it as it has not been changed */
                File(appContext.getDatabasePath(databaseName + workingCopyExtension).path).delete()
            }
        }

        /* As it says create a copy of the asset in the device's databases folder */
        fun getAssetFileCopy(context: Context,assetFileName: String, databaseName: String) {
            val assetInputStream = context.assets.open(assetFileName)
            val workingCopy = File(context.getDatabasePath(databaseName + workingCopyExtension).path)
            if (workingCopy.exists()) {
                workingCopy.delete()
            }
            assetInputStream.copyTo(workingCopy.outputStream())
        }
    }
}

• Note that to reduce the possibility of typos repeated names/values have largely been assigned to val's.
• Note run on the mainThread (aka .alloMainThreadQueries) for the convenience of testing

Testing

V1 was originally loaded from the asset based upon:-

After loading the isAlive values were all flipped using @Query("UPDATE user SET isAlive= NOT isAlive")

So the database then being (via App Inspection):-

• i.e. isAlive values have been flipped (true to false and false to true) to reflect user changes.

The asset then changed to:-

• note that the isAlive is as per the original asset (even rows are true aka not flipped)

Using the changed asset, the new strength column and V2 the resultant database (via App Inspection) is:-

• 5 new rows added
• 10 existing rows have the flipped isAlive
• 10 existing rows have the strength applied as per the asset

Final test App is uninstalled and rerun at V2. The database:-

i.e. the new install is with data as per the asset (isAlive aren't flipped)