Roomdatabase Duplicates Same Primarykey Item-CodePudding

I am getting messages from notifications and saving it to Roomdatabase. The problem is I am using username as a primarykey but when the data coming which has the same primarykey, roomdatabase saving it as a new object. I used OnConflictStrategy.REPLACE and it doesn't work. Here is my classes. Note: I am saving users and messages by using separate Entity objects.

@Entity
data class UserEntity(
   @PrimaryKey(autoGenerate = false) val user: String,
   val lastMessage: String,
   val lastTime: String
)

DAO

@Insert(onConflict = OnConflictStrategy.REPLACE)
suspend fun insertUser(entity: UserEntity)

INCOMING DATAS

    val from = bundle.getString(NotificationCompat.EXTRA_TITLE)
    val message = bundle.getString(NotificationCompat.EXTRA_TEXT)

    val filteredFrom = from?.replace(
       "\\(.*\\)".toRegex(),
       ""
    )?.filter {!it.isWhitespace()}

SAVING METHOD

repository.addUserToRoomDatabase(
                        UserEntity(
                        user = filteredFrom,
                        lastMessage = message,
                        lastTime = time
                    )
                )

CodePudding user response：

I believe that your issue is NOT due to Room's apparent mishandling but that the issue is elsewhere and that if you check the actual database using App Inspection then you will see that this is so.

Demonstration

Using your UserEntity class and your insertUser function (albeit without the suspend) and an additional DAO to extract the data (so the @Dao annotated class AllDAO) is :-

@Dao
interface AllDAO {

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insertUser(entity: UserEntity)

    @Query("SELECT * FROM userentity")
    fun getAllUsers(): List<UserEntity>
    
}

Along with the following to demonstrate (and a suitable @Database annotated class):-

class MainActivity : AppCompatActivity() {

    lateinit var db: TheDatabase
    lateinit var dao: AllDAO
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = TheDatabase.getInstance(this)
        dao = db.getAllDAO()
        dao.insertUser(UserEntity("Fred","1st Fred","0000"))
        dao.insertUser(UserEntity("Mary","1st Mary","1111"))
        dao.insertUser(UserEntity("Jane","1st Jane","2222"))
        logAllUsers("_RUN1")
        dao.insertUser(UserEntity("Fred","2nd Fred","4444"))
        dao.insertUser(UserEntity("Mary","2nd Mary","5555"))
        dao.insertUser(UserEntity("Jane","2nd Jane","6666"))
        logAllUsers("_RUN2")


    }
        fun logAllUsers(prefix: String) {
            for (u in dao.getAllUsers()) {
                Log.d("USERINFO${prefix}","User is ${u.user} Last Message is ${u.lastMessage} LastTime is ${u.lastTime}")
            }
        }
}

results in the log containing:-

2022-04-28 05:50:34.735 D/USERINFO_RUN1: User is Fred Last Message is 1st Fred LastTime is 0000
2022-04-28 05:50:34.735 D/USERINFO_RUN1: User is Mary Last Message is 1st Mary LastTime is 1111
2022-04-28 05:50:34.735 D/USERINFO_RUN1: User is Jane Last Message is 1st Jane LastTime is 2222


2022-04-28 05:50:34.741 D/USERINFO_RUN2: User is Fred Last Message is 2nd Fred LastTime is 4444
2022-04-28 05:50:34.742 D/USERINFO_RUN2: User is Mary Last Message is 2nd Mary LastTime is 5555
2022-04-28 05:50:34.742 D/USERINFO_RUN2: User is Jane Last Message is 2nd Jane LastTime is 6666

That is, it appears, from the output that the rows are being replaced not duplicated.

Using App Inspection, this confirms the case, as it shows only the last 3 replaced rows :-

If using/running the following query using App inspection SELECT rowid,* FROM userentity then it displays :-

This further confirms that Room is acting as expected.

That is when first run, the first 3 inserts will create rows with rowids 1,2 and 3.

When the second set are inserted, the the row with rowid 1 (user Fred) is deleted and a new row inserted and the rowid will be the next, which is 4 and so on (2 --> 5, 3 --> 6).

If you do see what appears to be duplicate rows, then it is likely that the user is not in fact the same. You could perhaps use, via App Inspection, the query SELECT rowid,length(user),* FROM userentity

Here again using a query in App Inspection an apparently errant row is inserted using INSERT INTO userentity VALUES ('Fred ','ooops','9999') (NOTICE the space after Fred)

Fred would appear to have been duplicated a row for Fred but the space makes a difference. Using the 2nd query it can be seen that yes there are 2 rows with Fred but the second has 5 as the length of Fred.

As you are "filtering" then perhaps that is where your issue lies.