CODEWITHSUNDEEP
androidsqliteandroid-sqlite
Running Turtle
Running Turtle

Reputation: 12752

How to limit the total number of records for a given table?

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.

Upvotes: 0

Views: 777

Answers (2)

Biraj Zalavadia
Biraj Zalavadia

Reputation: 28484

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.

Upvotes: 1

Dariusz
Dariusz

Reputation: 22311

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;

Upvotes: 0

Related Questions