This looks like a great solution. Just another point, what if I want to fill this "ring buffer" automatically by using triggers on other tables, and be able to reset the counter of the ring buffer when I arrive at the max integer value of the primary key. For example :
-- the table to be used as a fifo create table fifo ( id integer primary key autoincrement, data varchar ); -- table to store the fifo size limit create table fifo_size ( max_entries integer ); -- set the maximum size of the fifo -- this can be changed latter insert into fifo_size values(5); -- remove any records beyond the fifo size limit -- whenever the limit is changed create trigger fifo_resize after update on fifo_size begin delete from fifo where id <= (select max(id) from fifo) - (select max_entries from fifo_size); end; -- remove oldest record from the fifo -- after each insert into the fifo create trigger fifo_limit after insert on fifo begin delete from fifo where id <= (select max(id) from fifo) - (select max_entries from fifo_size); end; --Create table for any data storage CREATE TABLE Data (ID PRIMARY KEY AUTOINCREMENT, FirstName VARCHAR, LastName VARCHAR); --Create trigger on Data table when an event occurs on one of the fields CREATE TRIGGER Data_Update AFTER UPDATE OF FirstName ON Data BEGIN INSERT INTO fifo VALUES (NULL, new.FirstName); ----------------------- -- There, what if I get the error SQLITE_FULL while the INSERT INTO Data request??? -- I'm really not sure of the syntax, but could this work? ----------------------- ON CONFLICT UPDATE fifo SET ID = ID - (SELECT MIN(ID)-1 FROM fifo); INSERT INTO fifo VALUES (NULL, new.FirstName); END; Regards Julien Le jeudi 22 Décembre 2005 18:02, Dennis Cote a écrit : > Adler, Eliedaat wrote: > >>Sean Wrote === > >>I'm considering using SQLLite for an embedded project. Some of the > > > >data I'd like to store is timestamped sensor readings. I'd like to know > >if there is a way to >configure a table so that it acts like a fixed > >length FIFO queue, e.g. stores 10,000 records then once full drops off > >the oldest record each time a new one is inserted. > > > >.>Can anyone tell me if we can do that with SQLLite? > >===== > > > >You could use a simple trigger (example for queue of size 10000 below) > >and a self-incrementing key column: > > > > create table myqueue (limit_id integer not null primary key, myvalue > >integer) ; > > > > create trigger queue_limit after insert on myqueue > > begin > > update myqueue set limit_id = limit_id-1 where (select > >max(limit_id) from myqueue ) > 10000; > > delete from myqueue where limit_id <=0 ; > > end ; > > > >INSERT TO MYQUEUE: > > insert into myqueue (myvalue) values (1) ; > > insert into myqueue (myvalue) values(2) ; > > ...... > > insert into myqueue (myvalue) values (10050) ; > >RESULTS: > >limit_id myvalue > >1 51 > >2 52 > >..... > >10000 10050 > > > >Note: Its not very efficient. > > > >Regards, > >Eli > > The following SQL should do what you want. It is also quite efficient > because it always uses the implicit index associated with the integer > primary key field, and usually only touches the first or last record. > > -- the table to be used as a fifo > create table fifo ( > id integer primary key autoincrement, > data varchar > ); > > -- remove oldest record from the fifo > -- after each insert into the fifo > create trigger fifo_limit after insert on fifo > begin > delete from fifo > where id <= (select max(id) from fifo) - 10000; -- max size is 10000 > end; > > The following slightly more complicated version allows the size of the > fifo to be changed on the fly. > > -- the table to be used as a fifo > create table fifo ( > id integer primary key autoincrement, > data varchar > ); > > -- table to store the fifo size limit > create table fifo_size ( > max_entries integer > ); > > -- set the maximum size of the fifo > -- this can be changed latter > insert into fifo_size values(5); > > -- remove any records beyond the fifo size limit > -- whenever the limit is changed > create trigger fifo_resize after update on fifo_size > begin > delete from fifo > where id <= (select max(id) from fifo) - (select max_entries from > fifo_size); > end; > > -- remove oldest record from the fifo > -- after each insert into the fifo > create trigger fifo_limit after insert on fifo > begin > delete from fifo > where id <= (select max(id) from fifo) - (select max_entries from > fifo_size); > end; > > The following is a sample run of the second version using fifo sizes of > 5 and 8 for demonstration. > > insert into fifo values(NULL, 'one'); > insert into fifo values(NULL, 'two'); > insert into fifo values(NULL, 'three'); > select * from fifo; > 1|one > 2|two > 3|three > insert into fifo values(NULL, 'four'); > insert into fifo values(NULL, 'five'); > insert into fifo values(NULL, 'six'); > select * from fifo; > 2|two > 3|three > 4|four > 5|five > 6|six > insert into fifo values(NULL, 'seven'); > insert into fifo values(NULL, 'eight'); > insert into fifo values(NULL, 'nine'); > select * from fifo; > 5|five > 6|six > 7|seven > 8|eight > 9|nine > update fifo_size set max_entries = 8; > insert into fifo values(NULL, 'ten'); > insert into fifo values(NULL, 'eleven'); > insert into fifo values(NULL, 'twelve'); > select * from fifo; > 5|five > 6|six > 7|seven > 8|eight > 9|nine > 10|ten > 11|eleven > 12|twelve > insert into fifo values(NULL, 'thirteen'); > insert into fifo values(NULL, 'fourteen'); > insert into fifo values(NULL, 'fifteen'); > select * from fifo; > 8|eight > 9|nine > 10|ten > 11|eleven > 12|twelve > 13|thirteen > 14|fourteen > 15|fifteen > > For either case, the fifo table can eventually exhaust the available id > values (after a very long time because of the 64 bit id values). If this > happens you will receive an SQLITE_FULL error when you try to do an > insert (because of the autoincrement constraint on the id). When this > happens you will need to run the following update to reset the lowest id > to 1 before repeating the failed insert. > > update fifo > set id = id - (select min(id) - 1 from fifo); > > HTH > Dennis Cote -- ---------------------------- Julien LEFORT Com2gether 16, Allée de la verte vallée 14000 CAEN tel : +33 2 31 15 61 42 fax : +33 2 31 53 76 14
