Jetpack Compose Room @Upsert implementation
New developer here, and I've been working on an app that I have functionally completed except for one thing, updating a character. A user can create their own character and delete it, but not update yet. I've been struggling with finding information on using Upsert to update information, and I would be okay if I had to use Update. I'm just not sure how to implement it in my application at this point. I am using states with onEvents to interact with my database so far, as you can see below. Any help would be appreciated, even pointing to the right resources to learn what I need to.
My entity:
@Entity
data class Character(
val characterName: String,
val characterClass: String,
val characterLevel: Int,
val characterKeyAbilityScore: Int,
@PrimaryKey(autoGenerate = true)
val id: Int = 0
)
Dao:
@Dao
interface CharacterDao{
@Upsert
suspend fun upsertCharacter(character: Character)
@Delete
suspend fun deleteCharacter(character: Character)
// Sorting options if needed later
@Query("SELECT * FROM character ORDER BY characterName COLLATE NOCASE ASC")
fun getCharactersOrderedByName(): Flow<List<Character>>
}
Database
@Database(
entities = [Character::class],
version = 1
)
abstract class CharacterDatabase:RoomDatabase() {
abstract val dao: CharacterDao
}
My event so far goes like...
sealed interface CharacterEvent {
object SaveCharacter: CharacterEvent
data class SetCharacterName(val characterName:String): CharacterEvent
etc....
}
In my event, I'm not sure how to implement it. I would assume something like:
object UpdateCharacter: CharacterEvent
Finally, my viewModel for Save Character is along the lines of:
fun onEvent(event: CharacterEvent){
when(event){
CharacterEvent.SaveCharacter -> {
val characterName = state.value.characterName
val characterClass = state.value.characterClass
val characterLevel = state.value.characterLevel
val characterKeyAbilityMod = state.value.characterKeyAbilityScore
if (characterName.isBlank() || characterClass.isBlank()){
return // Level and ability mod are default 0.
}
val character = Character(
characterName = characterName,
characterClass = characterClass,
characterLevel = characterLevel,
characterKeyAbilityScore = characterKeyAbilityMod
)
viewModelScope.launch {
dao.upsertCharacter(character)
}
// Reset values to default.
_state.update {
it.copy(
characterName = "",
characterClass = "",
characterLevel = 0,
characterKeyAbilityScore = 0
)
}
}
I'm just struggling to find resources on this more than I ever had with any coding language before.
I've been struggling with finding information on using Upsert
In short the basics (what matters is the value of the primary key is the key determining factor) are at https://developer.android.com/reference/kotlin/androidx/room/Upsert
i.e. it says
The implementation of the method will insert its parameters into the database if it does not already exists (checked by primary key). If it already exists, it will update its parameters in the database.
So if you create a Character using
upsertCharacter("Fred","class1",1,0)
Then a row will be inserted, the id will very likely be 1 (if this is the first character).
If you repeat the upsert a second character will be add BUT the id will very likely be 2 with all others values as they were. The reason being that there is no conflict.
However, if you used (after the above)
upsert("Not Fred","whatever",100,222,1) **The 1 being the id**
then the row with an id will be updated so name becomes "Not Fred" and so on.
The id that uniquely identifies the row being the all important factor. This is because, being the primary key, that has an implicit UNIQUE constraint results in a constraint conflict as 1 already exists so instead of inserting the OR UPDATE updates all the other values.
So what you have to do is ascertain the id so in all likelyhood you would be better of using:-
fun upsertCharacter(character: Character): Long
The Long value returned being the generated id.
Demo
Using your code asis except that:-
- the upsert returns the Long,
- for brevity and convenience
alloMainThreadQueriesmethod has been used for the databaseBuilder, and - thus suspend has not been used
Then the following code :-
var c1 = Character("Fred","Class1",1,0)
val firstCharacter = dao.upsertCharacter(c1)
val secondCharacter = dao.upsertCharacter(c1)
dao.upsertCharacter(Character("Not Fred", "whatever",100,222,firstCharacter.toInt()))
for (c in dao.getAllCharacters()) {
Log.d("DBINFO","Name is ${c.characterName} Class is ${c.characterClass} .... ID is ${c.id}")
}
results in the log including:-
D/DBINFO: Name is Not Fred Class is whatever .... ID is 1
D/DBINFO: Name is Fred Class is Class1 .... ID is 2
So the very first row had been updated from Fred to Not Fred ...., but as no id was specified on either of the first 2 upserts that 2 rows exist.
Importantly that retrieving the id has allowed that to be used to drive the upsert that actually updates rather than inserts.
Additional
Considering the comment
Because I have a foreign key in another table with a cascade delete and from what I've gathered, replace deletes everything and then you only get your row updated back
The the @Upsert is, I believe, not what you want (iirc this just uses INSERT OR REPLACE as the SQLite UPSERT is in later version of SQLite).
As such you would want something that will insert or update.
Such as, for example:-
@Transaction
@Query("")
fun updateOrInsert(character: Character): Long {
/* assume an update, to differentiate between update and insert negate the id */
var rv: Long = 0 - character.id.toLong()
/* First try to update the Character
an @Update will return the number of affected rows
so if 0 the id does not exist, then insert
*/
if (updateCharacter(character)< 1) {
rv = insertCharacter(character)
}
return rv
}
So this will try to update, if nothing is updated it will insert, noting that it is reliant upon the respective @Update annotated function and in the case of nothing to update the @Insert annotated function.
In case there is a need to differntiate between whether an insert or update occurred, if the latter the id is negated but still the id.
Moving onto to demonstrating the above
A table (@Entity) TeamOfTwo, that has foreign key definitions referring to the Character table is introduced along with a POJO to get a TeamOfTwo with it's 2 Characters (Player1 and Player2) :-
@Entity(
foreignKeys = [
ForeignKey(entity = Character::class, parentColumns = ["id"], childColumns = ["player1"], onDelete = ForeignKey.CASCADE, onUpdate = ForeignKey.CASCADE),
ForeignKey(entity = Character::class, parentColumns = ["id"], childColumns = ["player2"], onDelete = ForeignKey.CASCADE, onUpdate = ForeignKey.CASCADE)
]
)
data class TeamOfTwo(
@PrimaryKey
val id: Long?=null,
@ColumnInfo(index = true)
val player1: Long,
@ColumnInfo(index = true)
val player2: Long
)
data class TeamOfTwoWithCharacterInfo(
@Embedded
val teamOfTwo: TeamOfTwo,
@Relation(entity = Character::class, parentColumn = "player1", entityColumn = "id")
val character1: Character,
@Relation(entity = Character::class, parentColumn = "player2", entityColumn = "id")
val character2: Character
)
To both utilise the updateOrInsert function, and to demonstrate that CASCADEing does not occur, then CharacaterDao has become:-
@Dao
interface CharacterDao{
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insertCharacter(character: Character): Long
@Upsert
/*suspend*/ fun upsertCharacter(character: Character)
@Delete
/*suspend*/ fun deleteCharacter(character: Character): Int
@Update
fun updateCharacter(character: Character): Int
@Transaction
@Query("")
fun updateOrInsert(character: Character): Long {
/* assume an update, to differentiate between update and insert negate the id */
var rv: Long = 0 - character.id.toLong()
/* First try to update the Character
an @Update will return the number of affected rows
so if 0 the id does not exist, then insert
*/
if (updateCharacter(character)< 1) {
rv = insertCharacter(character)
}
return rv
}
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(teamOfTwo: TeamOfTwo): Long
// Sorting options if needed later
@Query("SELECT * FROM character ORDER BY characterName COLLATE NOCASE ASC")
fun getCharactersOrderedByName(): /*Flow<*/List<Character>/*>*/
@Query("SELECT * FROM teamoftwo")
fun getAllTeamOfTwosWithCharacterInfo(): List<TeamOfTwoWithCharacterInfo>
}
- note that the functions return suitable values (insert the generated id, update/delete the number of affected rows)
- these returned values can be of great use and or importance
- you will notice that insert returns a Long, it is more correct to use Long for id's (e.g. as in TeamOfTwo)
Lastly some activity code to demonstrated the preservation of the children (i.e. the parent is not replaced):-
class MainActivity : AppCompatActivity() {
lateinit var db: TheDatabase
lateinit var dao: CharacterDao
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
db = TheDatabase.getInstance(this)
dao = db.getCharacterDao()
/* Add some CCharacter rows (using the updateOrInsert function rather then the insertCharacter function )*/
val c1id = dao.updateOrInsert(Character("C1","A",1,0))
val c2id = dao.updateOrInsert(Character("C2","B",2,99, 10000))
val c3id = dao.updateOrInsert(Character("C3","C",3,33))
/* Add some TeamOfTwo rows*/
dao.insert(TeamOfTwo(player1 = c1id, player2 = c2id))
dao.insert(TeamOfTwo(player1 = c2id, player2 = c3id))
dao.insert(TeamOfTwo(player1 = c1id, player2 = c3id))
dao.insert(TeamOfTwo(player1 = c3id, player2 = c1id))
/* Write the TeamOfTwo along with the 2 Characters to the log BEFORE an actual update */
logTeamOfTwos("BEFORE")
/* UPSERT (knowing that it should update Character C2)*/
/* NOTE that c2id is what determines what is updated*/
dao.updateOrInsert(Character("C9","Z",55, characterKeyAbilityScore = 77,c2id.toInt()))
/* Results AFTER */
logTeamOfTwos("AFTER")
}
fun logTeamOfTwos(tag_suffix: String) {
for (totwci in dao.getAllTeamOfTwosWithCharacterInfo()) {
Log.d("DBIFNO_${tag_suffix}","" +
"Team of 2 ID = ${totwci.teamOfTwo.id} P1 ID = ${totwci.teamOfTwo.player1} P2 ID = ${totwci.teamOfTwo.player2}" +
"\n\tPlayer 1 details are ${totwci.character1.characterName} ...." +
"\n\tPlayer 2 details are ${totwci.character2.characterName} ....")
}
}
}
- note the @Database has
.allowMainThreadQuesriesfor brevity (and hence why suspends and Flows have been commented out)
Result
The log when run includes:-
2023-10-08 20:46:50.811 D/DBIFNO_BEFORE: Team of 2 ID = 1 P1 ID = 1 P2 ID = 10000
Player 1 details are C1 ....
Player 2 details are C2 ....
2023-10-08 20:46:50.811 D/DBIFNO_BEFORE: Team of 2 ID = 2 P1 ID = 10000 P2 ID = 10001
Player 1 details are C2 ....
Player 2 details are C3 ....
2023-10-08 20:46:50.811 D/DBIFNO_BEFORE: Team of 2 ID = 3 P1 ID = 1 P2 ID = 10001
Player 1 details are C1 ....
Player 2 details are C3 ....
2023-10-08 20:46:50.811 D/DBIFNO_BEFORE: Team of 2 ID = 4 P1 ID = 10001 P2 ID = 1
Player 1 details are C3 ....
Player 2 details are C1 ....
2023-10-08 20:46:50.816 D/DBIFNO_AFTER: Team of 2 ID = 1 P1 ID = 1 P2 ID = 10000
Player 1 details are C1 ....
Player 2 details are C9 ....
2023-10-08 20:46:50.816 D/DBIFNO_AFTER: Team of 2 ID = 2 P1 ID = 10000 P2 ID = 10001
Player 1 details are C9 ....
Player 2 details are C3 ....
2023-10-08 20:46:50.817 D/DBIFNO_AFTER: Team of 2 ID = 3 P1 ID = 1 P2 ID = 10001
Player 1 details are C1 ....
Player 2 details are C3 ....
2023-10-08 20:46:50.817 D/DBIFNO_AFTER: Team of 2 ID = 4 P1 ID = 10001 P2 ID = 1
Player 1 details are C3 ....
Player 2 details are C1 ....
AS can be seen the P1 and P2 id's (the referenced Character id) are unchanged and remain (have not been CASCADED away) but that C2 has been changed to C9 (i.e the Character has been updated)
The Database itself, via App Inspection, has:-
i.e. all three Characters were C2 has been updated to C9 along with the other changed values BUT the id is unchanged at 1000.
- the 1000 was used to highlight the importance as such of the id (Primary Key)
Bar the relatively flexible @Query annotation, the other annotations (@Insert, @Update, @Upsert, @Delete) are for convenience and cover the more likely use. They are not all-encompassing and at times there is a need to resort to alternatives.