How can I persist user data on updating/migrating a database?-CodePudding

My database with products (name, price) gets initialized using createFromAsset. This works for static data, however it contains a column Favorite for marking a product as favorite.

The asset I initialize the database with could look like :

Name	Price	Favorite
Product A	5,99	no
Product B	6,99	no

I want to update the database; change a product's price and add a new product. However, I want the Favorite column to keep the value set by the user. If user marked "Product B" favorite and I change its price and add a Product C, this is what the database should look like after migration:

Name	Price	Favorite
Product A	5,99	no
Product B	1,99	yes
Product C	6,99	no

How to achieve this using Android Room? The only workaround I found :

Use fallbackToDestructiveMigration.
Update asset .db file so that it includes Product C.
Update database version.
-> Old database gets deleted, user sees Product C and updated price on Product B.

@Database(
    entities = arrayOf(Product::class),
    version = 2,
    exportSchema = true
)

fun getDatabase(context: Context, scope: CoroutineScope): ProductDatabase {
            return INSTANCE ?: synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    ProductDatabase ::class.java,
                    "product_database"
                ).createFromAsset("database/products.db")
                    .fallbackToDestructiveMigration()
                    .build()
                INSTANCE = instance
                instance
            }
        }

However, this resets the Favorite column. I also tried AutoMigration, but that leaves the existing database unaltered so the user doesn't see Product C and updated Product B's price.

How would I solve this? Do I need to store the favorites in a separate database?

CodePudding user response：

I don't know much about room migrations, but you can save a map of ProductID -> isFavorite to SharedPreferences or local storage.

Now that I think about it, you don't even need a map. Just a list of favorite products IDs, so next time you load the app, you set up the favorites column.

However, I think you should put the favorite productIDs in a separate table.

I think there might be something about room migration rules (like there is for Realm DB), but I don't know enough of that to tell you. Good luck!

CodePudding user response：

Here's an adaptation of

where the respective V1 or V2 would be used to overwrite products.db accordingly.

First Run (using products.db in asset as at version 1 i.e. productsV1.db copied to replace products.db )

D/PRDCTINFO_RUN: ID is 1 NAME is Product A PRICE is 5.99 FAVOURITE is false DBVERSION=1
D/PRDCTINFO_RUN: ID is 2 NAME is Product B PRICE is 6.99 FAVOURITE is false DBVERSION=1
D/PRDCTINFO_TOG: ID is 1 NAME is Product A PRICE is 5.99 FAVOURITE is false DBVERSION=1
D/PRDCTINFO_TOG: ID is 2 NAME is Product B PRICE is 6.99 FAVOURITE is true DBVERSION=1

Second run (App just rerun)

D/PRDCTINFO_RUN: ID is 1 NAME is Product A PRICE is 5.99 FAVOURITE is false DBVERSION=1
D/PRDCTINFO_RUN: ID is 2 NAME is Product B PRICE is 6.99 FAVOURITE is true DBVERSION=1
D/PRDCTINFO_TOG: ID is 1 NAME is Product A PRICE is 5.99 FAVOURITE is false DBVERSION=1
D/PRDCTINFO_TOG: ID is 2 NAME is Product B PRICE is 6.99 FAVOURITE is true DBVERSION=1

as can be seen B is true before and after

Third Run - increase to use updated V2 asset and DATABASE_VERSION changed to 2

2022-09-17 10:52:58.183 D/VERSIONINFO: Asset Version is 2. Actual DB Version is 1 Code Db Version is 2
2022-09-17 10:52:58.184 D/APPLYASSET: As the asset version is greater than the actual version then apply data from the asset.


2022-09-17 11:06:09.717 D/VERSIONINFO: Asset Version is 2. Actual DB Version is 1 Code Db Version is 2
2022-09-17 11:06:09.717 D/APPLYASSET: As the asset version is greater than the actual version then apply data from the asset.
2022-09-17 11:06:09.777 E/SQLiteDatabase: Error inserting favourite=0 id=1 name=Product A price=5.99
    android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: Product.id (code 1555 SQLITE_CONSTRAINT_PRIMARYKEY)
        at ....
2022-09-17 11:06:09.778 E/SQLiteDatabase: Error inserting favourite=0 id=2 name=Product B price=1.99
    android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: Product.id (code 1555 SQLITE_CONSTRAINT_PRIMARYKEY)
        at ....
2022-09-17 11:06:09.897 D/MIGRATIONINFO: Migration Invoked FROM=1 TO=2

2022-09-17 11:06:09.967 D/PRDCTINFO_RUN: ID is 1 NAME is Product A PRICE is 5.99 FAVOURITE is false DBVERSION=2
2022-09-17 11:06:09.968 D/PRDCTINFO_RUN: ID is 2 NAME is Product B PRICE is 1.99 FAVOURITE is true DBVERSION=2
2022-09-17 11:06:09.970 D/PRDCTINFO_RUN: ID is 3 NAME is Product C PRICE is 7.99 FAVOURITE is false DBVERSION=2

As can be seen:-

The respective versions (3 of them) have been logged as expected, and thus that the change has been detected.
That there were trapped UNIQUE constraints AS EXPECTED i.e. the inserts of the two existing rows were ignored (albeit that the trapped errors were logged)
That the dummy Migration was invoked as expected and did nothing.
That B still has true for the Favourite
The the Price for B has been changed
That the new Product C has been added