Room - insert or replace, but keep a database field?

Question

I have in my local database a table called event with the fields:

• id (PK)
• name (text)
• date (time)
• favorite (int, 0 or 1)

The server would give me a list of events, without the favorite field, the favorite field is only local.

I'm using this code to insert events

@Insert(onConflict = OnConflictStrategy.REPLACE)
void insertEvents(List<Event> events);

The thing is that, if an event is already stored in the local database, it will loose the favorite property, and it will always be false after is inserted and replaced.

Is there a way to bulk insert like this, but keep the favorite field?

Answer 1

I have had the same situation. This is how I solved it. Since the data coming from the server is immutable and would reset local values, I decided to keep a table for the data coming from the server and another for the "local" data.

Table ServerData for remote data.

Table ServerDataAttrs for local only data.

ServerDataAttrs contains all local columns along with the primary key id from table ServerData.

ServerData.id == ServerDataAttrs.id

This way, each row from ServerDataAttrs is related to its correspondent row in ServerData (the other way around is not required, that is why I use later LEFT JOIN). You can always replace data on ServerData and local data from ServerDataAttrs will remain untouched.

I did not set a foreign key to avoid accidental removal. I deleted the local data only when calling delete method from its DAO.

For querying the data, you can create a POJO that has all remote and local values together.

public class ServerDataWithAttrsPojo {
   private long id;
   private String name;
   ...
   private int favorite;

   // public constructor, getters and setters...
}

The query must be then a JOIN between both tables.

SELECT sd.id, sd.name, ...., sda.favorite
FROM ServerData sd LEFT JOIN ServerDataAttrs sda ON sd.id = sda.id

I use LEFT JOIN just in case an entity from ServerData does not have a row in ServerDataAttrs as I mentioned before.

Answer 2

I've had the same problem and this is the scenario of how I've solved it.

• Check if there are favorite items in your DB and fetch them.
• Fetch the data from API.
• Insert all of the data into DB.
• Update your DB with the previously fetched items that have been marked as favorite.