Limit the number of rows in a room database

Question

How can I limit the number of rows in an Android room database by removing the oldest item in the row and inserting the newest one?

I am guessing its a standard query when adding an item to the database?

EDIT: I want to limit a database table to have a max row count of say 20. If that limit is reached, we remove the oldest item and insert the new one by keeping the current row count to 20.

Answer 1

Here is a sample solution:

Query is :

@Query("SELECT * FROM user LIMIT :limit OFFSET :offset")
    User[] loadAllUsersByPage(int limit,int offset);

Here, it will list users based on limit and offset.

if loadAllUsersByPage(2,0) it will return the first 2 rows from the table.

if loadAllUsersByPage(2,1) it will return 2nd and 3rd rows from table.

but if loadAllUsersByPage(-1,10) then it will serve the first 10 rows from the table.

Answer 2

I think you can insert the data into your table then remove all the rows except last 20 (limit)

To delete you can use the following query

DELETE FROM tableName where id NOT IN (SELECT id from tableName ORDER BY id DESC LIMIT 20)

In this case, id is the primary key which is set to auto increment. You can use date as key as well if you are storing them by date

Answer 3

You can limit columns/rows by doing this: this query will return the new data and remove old data when its reach its limit.

Explanation:

1. First query is select all data order by descending
2. Second query is remove data from columns/rows id > 20

If you want your table only have 20 row then set the OFFSET to 20, the LIMIT is represent how many rows inserted & deleted at once.

In my example I remove 1 row (the oldest/last row) when the user inputs 1 new data

  @Query("SELECT * FROM my_table ORDER BY timeStamp DESC")
  fun getAllData(): List<MyEntityClass>

  @Query("DELETE FROM my_table WHERE id IN (SELECT id FROM my_table ORDER BY timeStamp DESC LIMIT 1 OFFSET 20)")
  fun removeOldData()

Answer 4

Assuming:

Your table is

    create table example_table (
      ts timestamp,
      uid number(19),
      some_other_field varchar(64)
    );

And you don't want to care about running some query manually.

Use database triggers:

    create trigger
      if not exists -- I don't actually know if you DB will support this line.
                    -- Might want to remove it if it's not.
    example_table_limiter
    on example_table
    after insert
    begin
      delete
      from example_table
      where ts in (
        select ts
        from example_table
        order by ts 
        limit -1 -- we don't want to limit how many rows we want to delete
        offset 25 -- but we want to offset query result so it leaves 25 rows in table
      );
    end;

"Offset without limit" syntax is inspired by this answer.

To enable your trigger in java:

Simple Android, where you can override SQLiteOpenHelper:

    public class DataBaseSchemaHelper extends SQLiteOpenHelper {
      @Override
      public void onCreate(SQLiteDatabase db) {     
        db.execSQL(<trigger string from above>);
      }
    }

Android Room version:

     public class MyDatabase extends RoomDatabase {
       @Override
       public void init(DatabaseConfiguration _config) {
         super.init(_config);
         getOpenHelper().getWritableDatabase().execSQL(<trigger string from above>);
       }
     }

Answer 5

Follow this steps :

1> get count of rows of that table

your_count = SELECT count( * ) FROM table_name;

2> if count is >(greater than) 20 than to get oldest record

SELECT *
  FROM table_name
 ORDER BY entry_Date ASC
 LIMIT 1;

3> now delete these selected records

4> insert new datas

NOTE : if you are inserting multiple entries than put this in loop