Limiting number of rows in SQLite

Question

I have a situation where I just want to limit only 50 rows in a table. If user inserts a new row after that then first row (which was inserted very first) should get deleted and new row gets inserted, so that count remains same. I know that I can have an rowid field and while inserting new record I can check if there are already 50 rows so delete the smallest rowid and then insert the new one. But just wanna know if there is any better solution so that I don't have to do 3 database operations (1. query #of rows, 2. delete minimum, 3. insert)

Answer 1

I know a way that works, but it's a little ugly. It relies on carefully constructed constraints and on seeding the database. For brevity, I'm using just five rows instead of 50.

create table test (
  row_num integer primary key
    check ((round(row_num) = row_num) and (row_num between 1 and 5)),
  other_columns char(1) not null default 'x',
  row_timestamp timestamp 
    not null unique 
    default current_timestamp
);

The expression round(row_num = row_num) guarantees you have integers in the row_num column. Otherwise, SQLite would let you insert 1.54 or 'wibble' in there.

The other_columns column is just a placeholder for your actual data.

insert into test (row_num, row_timestamp) values
(1, '2015-01-01 08:00:01'),
(2, '2015-01-01 08:00:02'),
(3, '2015-01-01 08:00:03'),
(4, '2015-01-01 08:00:04'),
(5, '2015-01-01 08:00:05');

The actual timestamp values don't really mean anything. Not yet, anyway. Seeding the database like this means that, from now on, you only have to execute update statements. If the table were empty to start with, you'd have to deal with different logic for inserts and updates. For example, you'd have to count rows to figure out whether to insert or to update.

create trigger update_timestamp
after update on test 
for each row
begin
  update test 
  set row_timestamp = strftime('%Y-%m-%d %H:%M:%f', 'now')
  where row_num = OLD.row_num;
end;

The "update_timestamp" trigger makes SQLite maintain the timestamp with fractions of a second (%f). Might depend on whether the underlying OS supports fractional precision.

create trigger no_deletes
after delete on test
for each row
begin
  -- There might be a more elegant way to prevent deletes.
  -- This way just inserts exactly what a delete statement deletes.
  insert into test (row_num, other_columns, row_timestamp) 
  values (OLD.row_num, OLD.other_columns, OLD.row_timestamp);
end;

---

Now you can update data. You update your own data, which here is just the placeholder other_columns, and SQLite takes care of the rest.

update test 
set other_columns = 'b' 
where row_timestamp = (select min(row_timestamp) from test);
select * from test order by row_timestamp desc;

row_num     other_columns  row_timestamp          
----------  -------------  -----------------------
1           b              2015-03-08 12:43:21.926
5           x              2015-01-01 08:00:05    
4           x              2015-01-01 08:00:04    
3           x              2015-01-01 08:00:03    
2           x              2015-01-01 08:00:02