Create a FIFO table with SQlite for Android application

Question

I would like to create a FIFO table in order to save only the most 50 recent infomations by deleting the oldest elements when a new infomation arrives. I can do it by manipulating ID in the table but I don't think it is the best solution. Any idea of doing it well?

Answer 1

You dont need to play with IDs in order to create a FIFO logic. The best would be to add another column as DATETIME in your table which automatically inserts current time-stamp that will help you to select records in ascending order with respect to this column. Your new column should be something like:

DateAdded DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

Make sure when ever you insert new record, you must do a COUNT check of total records in this table and if necessary delete the oldest record with respect to DateAdded. Moreover, you can make use of LIMIT and/or MAX in your select-query when it comes to delete the oldest record.

Answer 2

Instead of checking for date time, sorting your items, and whatnot, you can just assume that the first row in your table is the last to be inserted.

In your Content Provider's insert(Uri uri, ContentValues cv), before doing your db.insert call, you can first query the number of items on that table using getCount() and delete the first row if count>50. Then proceed with your insert call.