30

I am looking to implement a sort of 'activity log' table where actions a user does are stored in a sqlite table and then presented to the user so that they can see the latest activity they have done. However, naturally, I don't feel it is necessary to keep every single bit of history, so I am wondering if there is a way to configure the table to start pruning older rows once a maximum set limit is reached.

For example, if the limit is 100, and that's how many rows there currently are in the table, when another action is inserted, the oldest row is automatically removed so that there are always a maximum of 100 rows. Is there a way to configure the sqlite table to do this? Or would I have to run a cron job?

Clarification Edit: At any given moment, I would like to display the last 100 (for example) actions/events (rows) of the table.

ROMANIA_engineer
  • 54,432
  • 29
  • 203
  • 199
Jorge Israel Peña
  • 36,800
  • 16
  • 93
  • 123

3 Answers3

22

Another solution is to precreate 100 rows and instead of INSERT use UPDATE to update the oldest row.
Assuming that the table has a datetime field, the query

UPDATE ...
WHERE datetime = (SELECT min(datetime) FROM logtable)

can do the job.

Edit: display the last 100 entries

SELECT * FROM logtable
ORDER BY datetime DESC
LIMIT 100

Update: here is a way to create 130 "dummy" rows by using join operation:

CREATE TABLE logtable (time TIMESTAMP, msg TEXT);
INSERT INTO logtable DEFAULT VALUES;
INSERT INTO logtable DEFAULT VALUES;
-- insert 2^7 = 128 rows
INSERT INTO logtable SELECT NULL, NULL FROM logtable, logtable, logtable,
   logtable, logtable, logtable, logtable;
UPDATE logtable SET time = DATETIME('now');
Nick Dandoulakis
  • 42,588
  • 16
  • 104
  • 136
  • I guess I'm missing something, but wouldn't that always only update the last row? I guess I forgot to mention in my original post, but I would like to show for example the last 5 actions/events regardless of how old they are, not just the last one. Thanks though. – Jorge Israel Peña Jan 10 '10 at 03:39
  • 2
    @Blaenk: Assuming that your update sets the datetime field to the current time for each row it touches, this should cycle through the rows in time order. – Drew Hall Jan 10 '10 at 03:47
  • Oohhh, and then also update the datetime field of course, which would then make it the newest row, right? I see. Hmm, this is interesting. Is there a simple way of precreating a certain amount of 'dummy' rows? – Jorge Israel Peña Jan 10 '10 at 04:01
  • @Blaenk, I don't know if you can insert 100 dummy rows with a query in SQLite. You can do that, of course, with code. On getting the last N rows see my edit. – Nick Dandoulakis Jan 10 '10 at 09:06
  • 1
    While your dummy rows generation works well, it will create the same date for all rows killing your method of updating only the oldest records (all will be updated using your query). Something like UPDATE logs SET time = DATETIME(DATETIME('now'), '+'||rowid||' minutes'); fixed the issue for me. – tribe84 Sep 14 '15 at 17:10
  • Thank you very much, this answer helped me - I was looking to create dummy rows with default values in sqlite. – Vada Poché May 22 '17 at 05:07
4

You could create a trigger that fires on INSERT, but a better way to approach this, might be to simply have a scheduled job that runs periodically (say once a week) and deletes records from the table.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • oh okay that's what I figured (cron), but I wanted to make sure there wasn't a built-in mechanism for this kind of situation. – Jorge Israel Peña Jan 10 '10 at 01:55
  • 2
    If you're familiar with triggers, would you be kind enough to provide an example one that would be of use in this situation? That is, if they're not too complicated, if they are, don't worry about it. – Jorge Israel Peña Jan 10 '10 at 04:02
3

There are a couple of ways to constrain a table to 100 rows. (For brevity, 5 rows in the code below.) Tested in SQLite version 3.7.9.

All this code relies on a kind of quirk in the way SQLite handles data type declarations. (It seems quirky to me, anyway.) SQLite lets you insert nonsense like 3.14159 and 'wibble' into a bare integer column. But it lets you insert only integers into a column declared integer primary key or integer primary key autoincrement.

FOREIGN KEY constraint

Use a foreign key constraint to a table of valid id numbers to guarantee that the id numbers are in the range you want. Foreign key constraints work even on autoincrementing columns.

pragma foreign_keys=on;
create table row_numbers (n integer primary key);

insert into row_numbers values (1);
insert into row_numbers values (2);
insert into row_numbers values (3);
insert into row_numbers values (4);
insert into row_numbers values (5);

create table test_row_numbers (
  row_id integer primary key autoincrement,
  other_columns varchar(35) not null,
  foreign key (row_id) references row_numbers (n)
);

insert into test_row_numbers (other_columns) values ('s');
insert into test_row_numbers (other_columns) values ('s');
insert into test_row_numbers (other_columns) values ('s');
insert into test_row_numbers (other_columns) values ('s');
insert into test_row_numbers (other_columns) values ('s');

Sixth insert fails with "Error: foreign key constraint failed".

I don't think Using an autoincrement is entirely safe. On other platforms, a rollback would leave a gap in the sequence. If you don't use an autoincrement, you can safely insert rows by picking the id number out of "row_numbers".

insert into test_row_numbers values
(
  (select min(n) 
   from row_numbers 
   where n not in 
     (select row_id from test_row_numbers)), 
  's'
);

CHECK() constraint

The primary key constraint below guarantees the id numbers will be integers. The CHECK() constraint guarantees the integers will be in the right range. Your application might still have to deal with gaps caused by rollbacks.

create table test_row_numbers (
  row_id integer primary key autoincrement,
  other_columns varchar(35) not null,
  check (row_id between 1 and 5)
);
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185