How to store only 100 elements in a table

Question

I have a room table called scores_table. I only want to store the last 100. How can I do this using Room?

@Entity(
    tableName = "score_table",
    indices = [
        Index(value = ["id"])
    ]
)
data class ScoreEntity(
    @PrimaryKey 
    @ColumnInfo(name = "id")
    var id: Long = 0

    @ColumnInfo(name = "creation_time")
    var creationTime: Long = System.currentTimeMillis()
    ...
)

To insert I use this:

@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insert(entity: ScoreEntity)

Have a `@Transaction` function that deletes the rows that you don't want along with inserting any new ones. You would need a column in the table that allows you to determine what the "last" ones are. — CommonsWare, Sep 13 '19 at 23:39
https://stackoverflow.com/questions/44711911/android-room-database-transactions I think this link can help — Ishtdeep Hora, Sep 14 '19 at 01:05
Just to add what was already said: not sure what's the purpose of the `creation_time` column, but if you have it just to know what are the freshest 100 rows, then you can use `rowid` / autoincrement primary key instead. Then, within your `@Transaction` method you can simply obtain an id of the inserted row (`@Insert` methods can return `Long`) and delete everything that has an id lower or equal to `returnedIdValue - 100`. — Mateusz Herych, Sep 15 '19 at 15:06
@MateuszHerych that is pretty smart. If you post it I would happily accept the answer. — J_Strauton, Oct 20 '19 at 22:27

score 0 · Answer 1 · answered Oct 21 '19 at 19:08

Not sure what's the purpose of the creation_time column, but if you have it just to know what are the freshest 100 rows, then you can use rowid / autoincrement primary key instead.

Then, within your @Transaction method you can simply obtain an id of the inserted row (@Insert methods can return Long, which represents an identifier of a just inserted row) and delete everything that has an id lower or equal to returnedIdValue - 100

How to store only 100 elements in a table

1 Answers1