I'm searching to implement a kind of 'activity log' table where actions a person does are saved inside a sqlite table after which given to the consumer to ensure that they are able to begin to see the latest activity they've done. However, naturally, I do not feel it's important to help keep each and every little bit of history, and so i am wondering if there's a method to configure the table to begin pruning older rows when a maximum set limit is arrived at.
For instance, when the limit is 100, and that is the number of rows there presently have been in the table, when another action is placed, the earliest row is instantly removed to ensure that you will find always no more than 100 rows. It is possible to method to configure the sqlite table to get this done? Or would I must operate a cron job? I am pretty a new comer to sqlite so I apologize if this sounds like a stupid question haha.
Thanks I appreciate any help everyone can provide me.
Clarification Edit: At a moment, I must display the final 100 (for instance) actions/occasions (rows) on the table.
Another option would be to precreate 100 rows and rather than
UPDATE to update the earliest row.
Presuming the table includes a
datetime area, the query
UPDATE ... WHERE datetime = (SELECT min(datetime) FROM logtable)
can get the job done.
Edit: display the final 100 records
SELECT * FROM logtable ORDER BY datetime DESC LIMIT 100
Update: this is a method to create 130 "dummy" rows by utilizing 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');
You can produce a trigger that fires on Place, but an easy method to approach this, may be to merely possess a scheduled job that runs periodically (say once per week) and removes records in the table.