Rolling rows in SQL table

Question

I'd like to create an SQL table that has no more than n rows of data. When a new row is inserted, I'd like the oldest row removed to make space for the new one.

Is there a typical way of handling this within SQLite?

Should manage it with some outside (third-party) code?

Answer 1

Expanding on Alex' answer, and assuming you have an incrementing, non-repeating serial column on table t named serial which can be used to determine the relative age of rows:

 CREATE TRIGGER ten_rows_only AFTER INSERT ON t
   BEGIN
     DELETE FROM t WHERE serial <= (SELECT serial FROM t ORDER BY serial DESC LIMIT 10, 1);
   END;

This will do nothing when you have fewer than ten rows, and will DELETE the lowest serial when an INSERT would push you to eleven rows.

UPDATE

Here's a slightly more complicated case, where your table records "age" of row in a column which may contain duplicates, as for example a TIMESTAMP column tracking the insert times.

sqlite> .schema t
CREATE TABLE t (id VARCHAR(1) NOT NULL PRIMARY KEY, ts TIMESTAMP NOT NULL);
CREATE TRIGGER ten_rows_only AFTER INSERT ON t
  BEGIN
    DELETE FROM t WHERE id IN (SELECT id FROM t ORDER BY ts DESC LIMIT 10, -1);
  END;

Here we take for granted that we cannot use id to determine relative age, so we delete everything after the first 10 rows ordered by timestamp. (SQLite imposes an arbitrary order on rows sharing the same ts).

Answer 2

This would be something like how you would do it. This assumes that my_id_column is auto-incrementing and is the ordering column for the table.

-- handle rolls forward
-- deletes the oldest row
create trigger rollfwd after insert on my_table when (select count() from my_table) > max_table_size
   begin
   delete from my_table where my_id_column = (select min(my_id_column) from my_table);
   end;

-- handle rolls back
-- inserts an empty row at the position before oldest entry
-- assumes all columns option or defaulted
create trigger rollbk after delete on my_table when (select count() from my_table) < max_table_size
   begin
   insert into my_table (my_id_column) values ((select min(my_id_column) from my_table) - 1);
   end;

Answer 3

Seems SQLite's support for triggers can suffice: http://www.sqlite.org/lang_createtrigger.html

Answer 4

article on fixed queues in sql: http://www.xaprb.com/blog/2007/01/11/how-to-implement-a-queue-in-sql

should be able to use the same technique to implement "rolling rows"