ça doit fonctionner en effet, sinon des mecs de la mailing list SQLite m'ont donné des éléments aussi :
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.
Code :
- -- 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.
Code :
- -- 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.
Code :
- 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
Code :
- 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
Code :
- 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
Code :
- 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
Code :
- 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.
Code :
- update fifo
- set id = id - (select min(id) - 1 from fifo);
|
HTH
Dennis Cote
Je pense donc prendre de chaque solution, je mettrai la solution utilisée une fois les tests terminés,
merci pour le coup de main, à plus
Julien