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:

Read User data from Db (2 columns) store them somewhere temporarily
Delete Entire database and recreate from the asset I updated
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?

score 2 · Accepted Answer · answered Mar 31 '22 at 11:07

2

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()

answered Mar 31 '22 at 11:07

Vít Kapitola

499
1
5
9

Hey there, thanks for answering, For me it's a design pattern kind of question, it's why I didnt add any code. I understand what you mean if thats the case then I should use shared preferences, but the constraint is that I have 4 tables each with 10 columns, and 2 of those columns for each of those 4 tables would be difficult to maintain in shared preferences I feel, given that I cannot store array's/lists, only strings and ints, is difficult. – Noha Mar 31 '22 at 11:11
For destructive migration I already have it and I can also manually delete the database from my activity as well. So no issue there. – Noha Mar 31 '22 at 11:15
You are welcome. What about this idea. Firstly create a new db from assets with a new name. Then migrate columns you need, then delete the old database and after that rename the new database. I guess that this way of doing your migration avoids user data loss. Does this make sense? – Vít Kapitola Mar 31 '22 at 11:24
Yes thats what I want to do, but how to migrate the columns is not clear. Basically the user data is stored in their local copy of the db. They use my app and their data (game level + progress) is stored. during an app update how can I read this information and how write it back to the newly created DB from assets. Do I code this In the RoomDatabase class or in the Main activity for example – Noha Mar 31 '22 at 12:46
1

Thats a Question. I would do that in the Application class. Store version code to SharedPrefs and when changes run the migration. Maybe a [connection](https://tableplus.com/blog/2018/07/sqlite-how-to-copy-table-to-another-database.html) to the new database would be helpfull. i didn't try it out, but it should work in one db transaction. Let me know if it works. – Vít Kapitola Mar 31 '22 at 13:17
1

Or export the asset database to SQL file (can be done in db browser for SQlite). Then would be easy just to work in one database and do everythink in one transaction. Firstly select columns which you need, then removes all from this db, run insertion Query and insert selected columns. This you can do in RoomDatabase -> migrations methods. Best solution from my point of view. What do you think? – Vít Kapitola Mar 31 '22 at 13:24
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/243519/discussion-between-vit-kapitola-and-noha). – Vít Kapitola Apr 01 '22 at 10:53

MikeT · Answer 2 · 2022-04-01T12:37:18.183

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.

score 0 · Answer 3 · answered Sep 28 '22 at 20:50

Here's a simple approach:

Rename the db files already in the system. db.db, db.db-shm, db.db-wal to db_old.db, db_old.db-shm, db_old.db-wal

private const val dbName = "db.db"
private const val dbNameOld = "db_old.db"
private const val dbShm = "-shm"
private const val dbWal = "-wal"

val dbFile = context.applicationContext.getDatabasePath(dbName)
val dbFileShm = context.applicationContext.getDatabasePath(dbName + dbShm)
val dbFileWal = context.applicationContext.getDatabasePath(dbName + dbWal)
oldPathAndName = dbFile.parent?.toString() + File.separator + dbNameOld
if (dbFile.exists())    dbFile.renameTo(File(oldPathAndName))
if (dbFileShm.exists()) dbFileShm.renameTo(File(oldPathAndName + dbShm))
if (dbFileWal.exists()) dbFileWal.renameTo(File(oldPathAndName + dbWal))

Build the renamed database files with the "new" name db_old.db. There's no need for .createFromAsset or .fallbackToDestructiveMigration() since the data is already there and it will migrate it for you if you set the migrations correctly.
```
val oldDb = Room.databaseBuilder(context.applicationContext, MyDatabase::class.java, dbNameOld)
    .build()
```
Now build another database with the previous name db.db. This time, use .createFromAsset and you can include, but don't need .fallbackToDestructiveMigration(). This will build another database from scratch (since there's no longer an existing database with this name) using your updated database.
```
val newDb = Room.databaseBuilder(context.applicationContext, MyDatabase::class.java, dbName)
    .createFromAsset("PreLoadedDb.db")
    .build()
```
Now you have two variables, one with the old (updated schema) database that contains the user data, and the other without user data but with your own updated information. Now you can simply query the old database for the user data and insert it into the new database. The benefit here is you can use Room's code (with a DAO) to do this, making it a whole lot easier to do.
```
suspend fun transferData() {
    oldDb.userDao().getUserData().forEach {
        newDb.userDao().insertUserData(it)
    }
}
```
Once your data is transferred. Close the old database and delete the old files db_old.db, db_old.db-shm, db_old.db-wal. This makes further database updates no problem, since the name always remains the same.
```
oldDb.close()
File(oldPathAndName).delete()
File(oldPathAndName + dbShm).delete()
File(oldPathAndName + dbWal).delete()
```

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

3 Answers3

Linked