Creating a SQLITE table with a fixed number of items

Question

I am creating a database where I will store some statistics about the battery. Everytime the battery drops 1 percent, I want to add a row to my sqlite database with some info.

This is working pretty well, but with time, this table is becoming really huge.

What I would like to do is to set a maximum number of records (let's say 100 for instance).

Is that possible to set the number of records in my table or will I need to check this table everytime and clean it by code?

Here is my code:

create table batterystat (_id integer primary key autoincrement, value integer not null, ....)

And my query

public Cursor fetchAllStats{
   return db.query(databaseName,myColumns,null,null,null,null,null);
}

Thank a lot for any help.

NOTE: "you will have to do that by yourself and delete the extra rows by code" may be a correct answer if that is the case :-p

Answer 1

I would use a trigger and a delete query like this:

CREATE TRIGGER batterystat_trigger AFTER INSERT ON batterystat 
BEGIN 
    DELETE FROM batterystat where _id NOT IN (SELECT _id from batterystat ORDER BY insertion_date DESC LIMIT 100)
END