Android room how to increment count field and updating specific row fields

I have the following schema I am implementing for counting button taps for a user . For example if the the user clicks on apple button I would like to store the type as primary key which would be a unique entry followed by the count for the number of times the user taps on apple button and for each of those i would also save the time associated with those clicks . So I am trying to learn room database and wanted to understand how I could update unique fruit types rows with with the new associated count and timestamp . Would I have to query the table getByType(type) to get the existing data entry and then update it everytime with the new count and timestamp values everytime or is there a better way of doing this using room api’s

 @Entity
 data class ClickEntity(
   @PrimaryKey
   val type: String,
   val clickCount: Int? = null,
   val clickTime: Long? = null
 )


 @Dao
internal interface ClickEntityDao {
@Query("SELECT * FROM ClickEntity ORDER BY type")
fun getAll(): List<ClickEntity>

@Query("SELECT * FROM ClickEntity WHERE type=:type")
fun getByType(entity: ClickEntity): ClickEntity

// QUESTION: type, count, time -> how do I increment counter and update time while updating an existing entry
@Insert(onConflict = OnConflictStrategy.REPLACE)
fun update(entity: ClickEntity) 


@Query("DELETE FROM ClickEntity")
fun clear()
}

Answer

I believe the following could do what you want:-

@Query("UPDATE ClickEntity SET clickCount = clickCount + 1, clickTime = :clickTime WHERE type=:type")
fun incrementClick(type: String, clickTime: Long)

For example the following :-

    dao.insert(ClickEntity("Apple",0,0))
    dao.incrementClick("Apple",System.currentTimeMillis());

results in :-

i.e. when inserted count and time were 0 after the update count has been incremented and the current time has been set accordingly. So no need to get the data and then apply (as long as you are aware of the type which you should if it equates to the button).

Additional re comment

I would still need to insert once before updating and incrmenting ! Is there a shorter version to insertorupdate at the same time.

Yes, perhaps. However, if you have a button then I’d suggest inserting when you know that you should have the button.

If the App is targeted at android versions that have SQLite version 3.24.0 (Android API 30+ version 11+) then you could use an UPSERT (INSERT with an ON CONFLICT() DO UPDATE clause).

e.g.

@Query("INSERT INTO clickEntity VALUES(:type,0,:clickTime) ON CONFLICT(type) DO UPDATE SET clickCount = clickCount +1, clickTime = :clickTime ")
fun insertOrUpdate(type: String, clickTime: Long)

• Note the above has not been tested BUT shows a syntax error (perhaps because of the targeted android versions).

The alternative would be to utilise INSERT OR REPLACE which would simply be a matter of using:-

@Insert(onConflictStategy = OnConflictStrategy.REPLACE)
fun insert(ClickEntity("the_type",the_new_count,the_current_timestamp)

BUT you would have to retrieve the count.

HOWEVER a more complex solution could be to get the count + 1 for the type with a subquery. This is complicated as when first inserting there is no count. This can be resolved using the SQLite coalesce function. This would require @Query as @Insert expects an Entity object. So a solution that Inserts or replaces (updates but actually deletes and inserts to perform the update) could be :-

@Query("INSERT OR REPLACE INTO clickentity VALUES(:type,coalesce((SELECT clickCount FROM clickEntity WHERE type = :type),-1) + 1,strftime('%s','now') * 1000)")
fun insertOrReplace(type: String)

So:-

• If the type doesn’t exist it will insert a new row in doing so it:-
  • tries to get the current value of clickCount column for the type, but as no such row exists null is returned, so the coalesce function returns -1 and 1 is added so the count is 0 for a new row.
  • gets the current time (to the second) using SQLite’s strftime function and multiplies it by 1000 to bring it inline with an Android TimeStamp (adds milliseconds).
• If the type exists it will replace the row with another row in doing so it:-
  • finds the count from the existing row and adds 1

Example (of both INSERT OR REPLACE options) :-

Following on from the above code the following was added in addition to the changes/additions of the Dao’s as above (NOT the UPSERT) :-

    // Adds new
    dao.insert(ClickEntity("Grape",0, System.currentTimeMillis()))
    // Updates (cheated with clickCount knowing it would be 1)
    dao.insert(ClickEntity("Grape",1,System.currentTimeMillis()))
    // Adds new
    dao.insertOrReplace("Apricot")
    // Updates
    dao.insertOrReplace("Apricot")

The database then looks like (all of Android Studio Show) :-

• Banana shows how CURRENT_TIMESTAMP is stored which would cause issues.
• Pear is just using strftime(‘%s’,’now’) i.e. no millisecs
• as you can see both grape and apricot are as expected i.e. the count has been incremented.