Keep away from the hidden risks of insert-or-replace
SQLite added “upsert” performance in model 3.24. Upserting means updating a row if it already exists, or inserting a brand new row if not. Sadly SQLite model 3.24 shouldn’t be accessible on Android till API stage 30, so it’s not supported by Room (though the staff has indicated plans so as to add @Upsert in a future replace). However with a easy DAO perform we are able to add upsert performance to our Android app.
Earlier than we dive in, let’s take into account why this matter issues. At first look this will likely appear to be an issue that has already been solved. We will already use an insert with a battle technique of changing the prevailing row.
@Insert(onConflict = OnConflictStrategy.REPLACE)
So why fear about upserting? The refined reply is that these will be options to barely totally different issues. After we insert with a “change” battle technique, the outdated row is definitely deleted and a brand new row is added. After we upsert, we are literally updating the present row. One state of affairs the place that is notably vital is with international keys configured to cascade deletions. This was the precise state of affairs I confronted that led me to search for upsert performance.
Suppose we now have a desk of Notebooks and a desk of Notes the place there’s a one-to-many relationship between the 2. Every pocket book has a novel id as its main key, and every word has a international key indicating the pocket book to which it belongs.
Now suppose the person edits the title of a pocket book. Let’s say we now have a single add/edit display screen, from which we name our customary DAO Insert perform.
@Insert(onConflict = OnConflictStrategy.REPLACE)
enjoyable insertNotebook(pocket book: Pocket book)
It will enable us to both insert a brand new row for this pocket book if we didn’t have one already (if that is an “add”) or else change the prevailing row (if that is an “edit”). However do you see the issue? I didn’t.
After we insert with a “change” battle technique, the outdated row is definitely deleted and a brand new row is added. And as quickly because the outdated row is deleted, the cascading deletion is triggered and all rows within the notes desk with this international key are deleted. After this operation, we can have the very same information in our notebooks desk however can have deleted all corresponding notes from the notes desk. Speak about a debugging nightmare. We want an answer that may replace the row as a substitute of deleting-and-inserting it. We want upsert.
Ideally, Room would offer an @Upsert annotation to deal with this for us. Since we don’t have one, let’s look into how we are able to upsert manually. First, we have to take away the OnConflictStrategy in order that an insertion battle will throw an exception. We’ll depend on this in our upsert perform, which can simply wrap an insert in a attempt/catch block. If insert throws a SQLiteConstraintException then we all know there was an present row for that pocket book and we are able to name replace as a substitute. Right here’s the code.
One nuance value mentioning is with upserting lists. Room helps utilizing @Insert or @Replace on a Checklist of entities. This offers each comfort and in addition a efficiency enchancment since Room performs the majority insertion as a single transaction, which boasts super efficiency features with SQLite.
Sadly, we are able to’t upsert an inventory like this, since a battle on any row will trigger all the transaction to be rolled again. Because of this to upsert an inventory we’ll must iterate over the record and attempt every particular person insertion.
Be warned, this will trigger important efficiency hits in giant lists. If you’re calling upsert on an inventory that you simply anticipate to have 20 objects the distinction will probably be negligible. If you’re calling upsert on an inventory of two,000 objects you’ll want to watch it intently and presumably go along with a distinct strategy.
And that’s it! Up, upsert away. Let me know should you discovered this useful. Higher but, drop by the issue I opened to bug the Room staff about including an @Upsert annotation that may make this text out of date.
