How can I limit the number of rows in Android SQLite table

Question

I create an Android app that has "recents history". I would like to limit the size of this table to a maximum of 50 rows (according to their insert date).

I saw several threads that talk about limiting the number of deleted rows, but I'm not sure if this feature is even enabled in Android's SQLite.

Can anyone help here?

Thanks

Answer 1

Create a trigger

CREATE TRIGGER delete_till_50 INSERT ON _table WHEN (select count(*) from _table)>50  BEGIN     DELETE FROM _table WHERE _table._id IN  (SELECT _table._id FROM _table ORDER BY _table._id limit (select count(*) -50 from _table )); END; 

EDIT:

You can change
DELETE FROM ... WHERE ... IN ...
to
DELETE FROM ... WHERE ... NOT IN ...
as Mojo Risin wrote. I'm not sure about difference of performance for large tables for using IN and NOT IN, but for your problem it's no difference.

Answer 2

I think sql can't manage the number of rows in your table so you'll have to manage it by yourself. You can execute query after data insertion that will reduce the data - something like this should work

DELETE FROM table where _id NOT IN (SELECT _id from table ORDER BY insertion_date DESC LIMIT 50)