1

I have several tables in my DB of which some contain prepopulated content that the user cannot change and others that are only filled by the user. Now I want to update the prepopulated, static content but keep the user generated content.

This Android developer guide says the following concerning my question:

Because there is an implemented migration path from version 2 to version 3, Room runs the defined migrate() method to update the database instance on the device to version 3, preserving the data that is already in the database. Room does not use the prepackaged database file, because Room uses prepackaged database files only in the case of a fallback migration.

So this means I have no possibility to, for example: from 3 columns X, Y & Z - drop columns X and Z and recreate them with new content from the updated DB file, while keeping column Y as it was?

Below is an illustration of the issue.

Visualization of the question for better understanding

Is it correct that I only have 2 options now:

  1. Drop tables completely and make user lose their generated content, so they can have the updated prepopulated content
  2. Write migrations for the new structure, but end up with empty columns X.2 and Z.2 because Room will ignore my prepopulated DB 2.0
Big_Chair
  • 2,781
  • 3
  • 31
  • 58
  • "drop table X and Z" -- you do not have tables named X and Z in your question. Do you mean columns X and Z? If so, Room does not have any sort of automated support for your scenario. Your migrations do not need to result in empty columns, though. It is your job to fill them in. After all, if you cannot fill them in (as the designer and developer of the database schema), how do you expect *Room* to fill them in? – CommonsWare Aug 25 '20 at 12:00
  • @CommonsWare Yep, just fixed the typo. But then how do I go about filling them? I imagined I would write the new content (longer texts) into the sqlite file through a DB editor (like DB Browser) and then have Room inject that new content into the existing DB on the user's device. Do you mean I have to write some logic to inject that content manually after the migration? – Big_Chair Aug 25 '20 at 12:05
  • "Do you mean I have to write some logic to inject that content manually after the migration?" -- I do not know the relationship between the content and the schema, but off the cuff, I would consider to be part of the migration. So, in addition to the other SQL that you use to wrestle your schema into your new structure, you would update the fixed content. That could be from a Web service call, for example. With effort, you could make a copy of your packaged database to a scrap file, use `ATTACH DATABASE` to get access to it, then reference that data as part of updating the fixed content. – CommonsWare Aug 25 '20 at 12:18
  • @CommonsWare Thank you, do you perhaps have a link to something where I could read more about it? I only know the very basics of database management and am a bit afraid to mess things up, so I'd like to inform myself well on that topic before attempting any of that. – Big_Chair Aug 25 '20 at 12:38
  • 1
    I am not certain what "it" is. If you mean my last sentence, the only thing I have handy is https://github.com/commonsguy/cwac-saferoom/blob/master/saferoom/src/main/java/com/commonsware/cwac/saferoom/SQLCipherUtils.java#L190-L224, which is a method that encrypts a database. There, I use `ATTACH DATABASE AS ` to attach an external database file to an open database connection, so I can refer to both. My scenario is not the same as yours (e.g., you would not have `KEY` for providing a passphrase), but it is my closest match. – CommonsWare Aug 25 '20 at 13:07
  • Anyone looking for another solution, take a look at my answer here: https://stackoverflow.com/a/73887645/14609192 – AaronC Sep 28 '22 at 21:02

1 Answers1

1

Take a look at this library, maybe it helps you solve this
https://github.com/ueen/RoomAssetHelper

You can name tables and columns that should be preserved and then rename the existing db, copy the new one and transfer the specified columns.

Example from the GitHub page:

val db = RoomAssetHelper.databaseBuilder(applicationContext,
                       AppDatabase::class.java, 
                       "chinook.db",
                       1,
                       preserve = arrayOf(TablePreserve(table = "yourTable",
                                        preserveColumns = arrayOf("yourColumn"),
                                        matchByColumns = arrayOf("id"))))
                .build()
Big_Chair
  • 2,781
  • 3
  • 31
  • 58
ueen
  • 692
  • 3
  • 9
  • 21
  • Hey man, I didn't actually consider your suggestion back then until I now had to upgrade the DB **another** time and came back here. This library is actually awesome! I was failing to correctly reset the contents manually, but it made it work, thanks again! – Big_Chair Jan 17 '21 at 16:05