0

I'm using sqlite3 as a caching tool for an android app.

Basically, a services fetches data from a server at a regular interval and inserts the new records inside a sqlite3 table. The data is then used to populate UI inside activities and fragments.

Because the data is short-lived, it does not need to be persisted long-term.

In order to save space and resources, how can I make sure that say, only the 100 most recent records are kept and older entries are automatically deleted ?

I've heard of TRIGGERS but not too sure about how to implement them. Any pointers would be appreciated.

Running Turtle
  • 12,360
  • 20
  • 55
  • 73
  • possible duplicate of [How to test if COUNT() is greater than a number for SQLite trigger?](http://stackoverflow.com/questions/18736775/how-to-test-if-count-is-greater-than-a-number-for-sqlite-trigger) – CL. May 21 '14 at 12:22

2 Answers2

1

Follow the steps

1) Add one column in your table "timestamp"

2) During insert the record set the "timestamp" with current time in milliseconds.

3) Create Trigger like this 

CREATE TRIGGER yourtriggername AFTER INSERT 
ON yourtable WHEN (SELECT COUNT(*) FROM yourtable) >100
BEGIN
DELETE FROM yourtable WHERE timestamp = (SELECT MIN(timestamp) FROM yourtable)
END

4) Replace "yourtable" with actual table name

5) The above trigger will called every time and check whether the total records in table exceeds 100 it will remove the record whose "timestamp" is minimum.

Biraj Zalavadia
  • 28,348
  • 10
  • 61
  • 77
0
select entry_id
from entries
order by create_date desc
limit 1 offset 100;


delete from entries where create_date < 
    (select create_date from entries where entry_id = obtained_entry_id);

Or just:

delete from entries where create_date < 
    (select create_date from entries by create_date desc limit 1 offset 100);

Trigger to enforce it:

CREATE TRIGGER truncate_entries AFTER INSERT ON entries 
  BEGIN
    --the delete statement from above
  END;
Dariusz
  • 21,561
  • 9
  • 74
  • 114
  • thanks for the answer. The command to delete entries is clear enough, but how would you make sure it is executed automatically after an insert ? – Running Turtle May 21 '14 at 12:26