Handling Room Database update by reacreating from asset except for few columns I need to persist - Android Development

Question

My database has game configuration data and dynamic user data. Every time I update the app, I change the configuration data a lot (records are updated and new records are added), and it easy to just delete the database and recreate from asset. All is updated except the user data which needs to persist and is not impacted by the game configuration update.

So what I would like to do to avoid lengthy migration code:

1. Read User data from Db (2 columns) store them somewhere temporarily
2. Delete Entire database and recreate from the asset I updated
3. Write back the user data into those 2 columns.

From a technical point of view I'm always fidning it difficult to achieve this, and I'm wondering if someone knows if it's possible or has done it before?

Answer 1

If there are just two columns I suggest you to remove it from db and store it somewhere else. For example in SharedPreferences. Or this is not what you can do?

I am missing details of your implementation.

If you are using Room, it would be a great opportunity to try out Destructive migrations. When building the db, just enable destructive migrations and the work is done automatically when db version is updated.

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

Answer 2

and it easy to just delete the database and recreate from asset.

Instead of deleting the database rename it, thus it is then still available.

Use the prePackedDatabase callback to apply the data from the renamed version (when the callback is called the prepackaged database has been copied) and then delete the renamed database.

You may find this useful How do I use Room's prepackagedDatabaseCallback?

Here's an untested (but sucessfully compiled) example.

The example uses the following @Entity annotated class TheTable

@Entity
data class TheTable(
    @PrimaryKey
    val id: Long? = null,
    val config1: String,
    val user1: String,
    val user2: String
)

The @Database annotated class, TheDatabase, checks to see if the renamed database exists, if so then it extracts the data from the renamed database and updates the respective rows based upon the id column (assumes typically integer id column). :-

const val DBNAME = "TheDatabase.db"
const val RENAMEDDBNAME = "renamed_$DBNAME"
@Database(entities = [TheTable::class], version = 1, exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDAO

    companion object {
        var instance: TheDatabase? = null

        fun getInstance(context: Context): TheDatabase {
            if (instance == null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java, DBNAME)
                    .allowMainThreadQueries()
                    .createFromAsset(DBNAME, ppdbc)
                    .build()
            }
            return instance as TheDatabase
        }

        val ppdbc = object : PrepackagedDatabaseCallback() {
            @SuppressLint("Range")
            override fun onOpenPrepackagedDatabase(db: SupportSQLiteDatabase) {
                super.onOpenPrepackagedDatabase(db)

                val db_directory = File(db.path).parentFile.path
                val renamed_db_path = db_directory + File.separator + RENAMEDDBNAME
                val renamed_db_exists = File(renamed_db_path).exists()
                if(renamed_db_exists) {
                    val renamed_db = SQLiteDatabase.openDatabase(renamed_db_path,null,SQLiteDatabase.OPEN_READWRITE)
                    db.beginTransaction()
                    val csr = renamed_db.query("thetable",null,null,null,null,null,"id")
                    val cv = ContentValues()
                    while (csr.moveToNext()) {
                        cv.clear()
                        cv.put("user1",csr.getString(csr.getColumnIndex("user1")))
                        cv.put("user2",csr.getString(csr.getColumnIndex("user2")))
                        db.update("thetable",OnConflictStrategy.IGNORE,cv,"id=?", arrayOf(csr.getLong(csr.getColumnIndex("id"))))
                    }
                    db.setTransactionSuccessful() //<<<<< only set if all is ok, if not set then changes would be rolled back
                    db.endTransaction()
                    csr.close()
                    renamed_db.close()
                    File(renamed_db_path).delete()
                }
            }
        }
    }
}

• obviously it is not the exact code that you want but purely an example that could fit the question asked but would very likely need tailoring accordingly.