Search code examples
androidandroid-roomupsert

Confusion about what Room "Upsert" returns after a successful update

I read somewhere (can't remember the source) that Upsert usually returns -1 if an error occurs. Recently in my project I have observed that Upsert is returning -1 - even though the update is successful.

The relevant parts of the code:

***The Room is set-up as:

@Entity(tableName = "birth_defect_beneficiary_details")
data class BirthDefectBabyDetails(
    @PrimaryKey(autoGenerate = true)
    val id: Int? = null,

    val dateOfBirth: Long,
    val sex: String,

    val motherName: String,
    val fatherName: String,

    val address: String,
    val contactNumber: String,

    val condition: String,

    val patientContacted: Boolean
)

@Dao
interface BDBabyDetailsDao {

    @Query("SELECT * FROM birth_defect_beneficiary_details ORDER BY dateOfBirth DESC")
    fun getDetailsOfAllBabies(): Flow<List<BirthDefectBabyDetails>>

    @Upsert
    suspend fun upsertEntry(entry: BirthDefectBabyDetails): Long

    @Delete
    suspend fun deleteEntry(entry: BirthDefectBabyDetails): Int

    @Query("DELETE FROM birth_defect_beneficiary_details")
    suspend fun deleteAllEntries()
}

@Database(
    entities = [BirthDefectBabyDetails::class],
    version = 1,
    exportSchema = false
)
abstract class BDBabiesDatabase : RoomDatabase() {
    abstract val babyDao: BDBabyDetailsDao
}

interface BabyDetailsEntryRepository {
    fun getAllBabiesStream(): Flow<List<BirthDefectBabyDetails>>
    suspend fun upsertEntry(entry: BirthDefectBabyDetails): Long
    suspend fun deleteEntry(entry: BirthDefectBabyDetails): Int
    suspend fun deleteAllEntries()
}

class BDBabyDetailsLocalRepository @Inject constructor(
    private val babyDao: BDBabyDetailsDao
) : BabyDetailsEntryRepository {
    override fun getAllBabiesStream(): Flow<List<BirthDefectBabyDetails>> {
        return babyDao.getDetailsOfAllBabies()
    }

    override suspend fun upsertEntry(entry: BirthDefectBabyDetails): Long {
        return babyDao.upsertEntry(entry = entry)
    }

    override suspend fun deleteEntry(entry: BirthDefectBabyDetails): Int {
        return babyDao.deleteEntry(entry = entry)
    }

    override suspend fun deleteAllEntries() {
        babyDao.deleteAllEntries()
    }
}

Inside the ViewModel, where actual "upsert" is happening:

private fun upsertEntry() {

        // some codes...

        viewModelScope.launch(IO) {
            try {
                val result = babyRepository.upsertEntry(newEntry)
                Log.d("DPDC", "$result")
                if (result != -1L) {
                    triggerEvent(VMEvents.EntryAdded("B/O ${newEntry.motherName}"))
                } else {
                    triggerEvent(VMEvents.ShowError("Failed to add/update entry"))
                }
            } catch (e: Exception) {
                triggerEvent(VMEvents.ShowError("Error occurred: ${e.message}"))
            }
        }

        // some more codes...
    }

newEntry is declared as val newEntry: BirthDefectBabyDetails

Now through App Inspection inside Android Studio this is the db before editing :

enter image description here

Log cat output after edit:

enter image description here

db after edit (please note - the last entry - I have changed the gender from female to male:

enter image description here

At this point I am confused - what's happening. Even though my app is behaving - the way it should - but the Logcat tells a different story. What am I missing?

For further clarity, these are the dependencies in the build.gradle.kts (Module :app)

// room
    implementation("androidx.room:room-ktx:2.6.1")
    kapt("androidx.room:room-compiler:2.6.1")
Solution

  • I read somewhere (can't remember the source) that Upsert usually returns -1 if an error occurs.

    The value returned is the value of the id column (in your case) IF a row has been inserted, as opposed to updated. If no row has been inserted, and thus the row (according to the primary key being matched) has been updated (even though values may or may not have actually been changed), then -1 is returned.

    This returned value is consistent with an INSERT (that returns the rowid of the inserted row) but is inconsistent with an UPDATE (that returns an int/Int of the number of affected rows).

    It would be more confusing and perhaps catastrophic if the number of rows affected (updated) were returned as then you would not be able to distinguish between which and in many situations 1 would be returned which may be considered to be the rowid.

    • note rowid, as you have an integer type single column primary key, will be the same value as the id column (the id column is in fact an alias of the special rowid column).
      • the rowid column is a column that is normally hidden but for most tables always exists. When a row is inserted it will be generated unless the generation is overridden (typically by specifying a value for a column that is an alias of the rowid).

    If you delve into the underlying Room code i.e. EntityUpsertionAdapter then:-

    enter image description here

    An Error?????

    As can be seen, due to the try/catch, errors will be assumed to be due to a unique constraint conflict (a primary key is implicitly unique), even if they are not. However due to Room's pre-run checks few errors will not be (e.g. a column not found, which would be a SQLite syntax error should not happen when utilising room @.... generated code).

    However, here's one that goes beyond what Room can check. In short a row is inserted that is the largest possible value of an id column. Due to the implied use of the AUTOINCREMENT constraint (actually what autoGenerate=true implements), if an attempt is made to add another row then an SQLTE_FULL error is raised.

    So using:-

    const val max_rowid = 9223372036854775807
....

val b5 = BirthDefectBabyDetails(max_rowid,b1.dateOfBirth,b1.sex,"M001","F001",b1.address,b1.contactNumber,b1.condition,b1.patientContacted)
        upsertLoggingFullResult(b5)
        upsertLoggingFullResult(BirthDefectBabyDetails(null,200,b4.sex,b4.motherName,b4.fatherName,b4.address,b4.contactNumber,b4.condition,b4.patientContacted))
    • using your code (but run on the main thread for brevity) and the following additional functions

    :-

    fun upsertLoggingFullResult(b: BirthDefectBabyDetails): Long {
    val result: Long = dao.upsertEntry(b)
    Log.d("DBINFO_UPSRT","Result of upsert ${result}, when inserting ID={${b.id} DOB=${b.dateOfBirth} Mother=${b.motherName} Father=${b.fatherName} ...." )
    logAll("aftupsrt")
    return result
}
fun logAll(tag_suffix: String) {
    for (b in dao.getDetailsOfAllBabies()) {
        Log.d("DBINFO_$tag_suffix","ID=${b.id} DOB=${b.dateOfBirth} Mother=${b.motherName} Father=${b.fatherName} ....")
    }
}

    Then the log includes:-

    2024-06-11 11:08:41.460 D/DBINFO_aftupsrt: ID=9223372036854775807 DOB=100 Mother=M001 Father=F001 ....
2024-06-11 11:08:41.461 E/SQLiteLog: (13) statement aborts at 4: [INSERT INTO `birth_defect_beneficiary_details` (`id`,`dateOfBirth`,`sex`,`motherName`,`fatherName`,`address`,`contactNumber`,`condition`,`patientContacted`) VALUES (?,?,?,?,?,?,?,?,?)] 
2024-06-11 11:08:41.461 E/SQLiteLog: (1) statement aborts at 1: [ROLLBACK;] cannot rollback - no transaction is active
2024-06-11 11:08:41.462 D/AndroidRuntime: Shutting down VM
    • as can be seen the insert with the high id was successful, but the subsequent insert resulted in an actual crash (although the underlying SQLITE_FULL error has been captured)

    Additional re comment:-

    So, at current version of Room, is it safe to assume that upsert will return -1 if an update is successful. In such case, how to check whether the update failed. Or should I ditch Upsert altogether in favour of Insert and Update ?

    It is highly unlikely that an Update would fail (at least with your code), so -1 would typically indicate a successful update.

    • However, the insertOrUpdate below would fail, with an exception, if the UPDATE were to fail (noting that not changing values, as they have not been changed (e.g. #2 in the example below) is not considered a failure)

    However, you could consider something along the lines of:-

    A class such as :-

    data class InsertOrUpdateReturnValues(
    val rowid: Long=-1,
    val affectedRows: Int=0
)

    and the following functions in the/an @Dao annotated interface:-

    @Insert(onConflict = OnConflictStrategy.IGNORE)
fun insertOrIgnore(birthDefectBabyDetails: BirthDefectBabyDetails): Long
@Update(onConflict = OnConflictStrategy.IGNORE)
fun update(birthDefectBabyDetails: BirthDefectBabyDetails): Int

@Transaction
@Query("")
fun insertOrUpdate(birthDefectBabyDetails: BirthDefectBabyDetails): InsertOrUpdateReturnValues {
    var updateResult=0
    val insertResult = insertOrIgnore(birthDefectBabyDetails)
    if (insertResult.equals(-1L)){
        updateResult = update(birthDefectBabyDetails)
    }
    return InsertOrUpdateReturnValues(insertResult,updateResult)
}

    An example usage in an activity:-

        dao.deleteAllEntries()
    logAll("AFTDLTALL")
    var insertOrUpdateReturnValues: InsertOrUpdateReturnValues = dao.insertOrUpdate(b1)
    val b1id = insertOrUpdateReturnValues.rowid
    Log.d("DBINFO_AFTIOU1","Insert result (rowid) = ${insertOrUpdateReturnValues.rowid} Update result = ${insertOrUpdateReturnValues.affectedRows}")
    logAll("AFTIOU1")
    insertOrUpdateReturnValues = dao.insertOrUpdate(BirthDefectBabyDetails(b1id,b1.dateOfBirth,b1.sex,b1.motherName,b1.fatherName,b1.address,b1.contactNumber,b1.condition,b1.patientContacted))
    Log.d("DBINFO_AFTIOU2","Insert result (rowid) = ${insertOrUpdateReturnValues.rowid} Update result = ${insertOrUpdateReturnValues.affectedRows}")
    logAll("AFTIOU2")
    insertOrUpdateReturnValues = dao.insertOrUpdate(BirthDefectBabyDetails(b1id,b1.dateOfBirth+500,b1.sex,b1.motherName,b1.fatherName,b1.address,b1.contactNumber,b1.condition,b1.patientContacted))
    Log.d("DBINFO_AFTIOU3","Insert result (rowid) = ${insertOrUpdateReturnValues.rowid} Update result = ${insertOrUpdateReturnValues.affectedRows}")
    logAll("AFTIOU3")

    Results in:-

    2024-06-11 20:25:00.146 D/DBINFO_AFTIOU1: Insert result (rowid) = 1 Update result = 0
2024-06-11 20:25:00.147 D/DBINFO_AFTIOU1: ID=1 DOB=100 Mother=Mother001 Father=Father001 ....

2024-06-11 20:25:00.151 D/DBINFO_AFTIOU2: Insert result (rowid) = -1 Update result = 1
2024-06-11 20:25:00.152 D/DBINFO_AFTIOU2: ID=1 DOB=100 Mother=Mother001 Father=Father001 ....

2024-06-11 20:25:00.162 D/DBINFO_AFTIOU3: Insert result (rowid) = -1 Update result = 1
2024-06-11 20:25:00.164 D/DBINFO_AFTIOU3: ID=1 DOB=600 Mother=Mother001 Father=Father001 ....
    1. The row is inserted and the id 1 is returned and the update returns 0 as it was not attempted
    2. The row, as it exists (id returned is -1) is updated 1 is returned (but no values are changed)
    3. Again the row exists (id -1) and is updated (1 is returned) but the dob has been updated to 600 (100 + 500).

    As the OnConflictStrategy is IGNORE then only captured constraint conflicts will not result in an exception. You do have a little more control as you could use a different strategy for one or both the INSERT and or UPDATE.

    Which way probably makes little difference though as typically the same behaviour will be undertaken and the end result will be the same.