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
