Is it possible in SQLite to limit the size of a table to the number of records and how to organize it? For example, the table lists a total of 100 rows, after which the oldest entries are deleted and new ones are written.

  • And for what purpose is it necessary? - newman

3 answers 3

You can write a trigger to insert new records into the table.
Suppose you have a table _table with fields

  • _id (key of some kind)
  • _time (record creation time)

Then the trigger will look like this.

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

When you insert a new entry, the oldest entries will be deleted. The database will always be no more than 100 entries.

    No, it is impossible to limit the number of entries, but there is one trick that can help:

    • Create table
    • Fill it with hundreds of records, each one millisecond older than the previous one.
    • Instead of an insert operation, perform an update operation on the oldest entry.

      The FILO stack is designed specifically for this.
      How to implement it with specific objects is a matter of technique.
      But to understand, to reflect it in the brain is just necessary ...

      enter image description here .