[SQLite] Création d'une table circulaire

Création d'une table circulaire [SQLite] - SQL/NoSQL - Programmation

Marsh Posté le 22-12-2005 à 16:19:27    

Bonjour,
J'utilise SQLite dans un système embarqué, et je souhaite créer une table qui corresponde à un buffer circulaire. C'est à dire, arrivé à un certain nombre d'enregistrements, je retourne au début en écrasant les données les plus anciennes.
QQun a une idée?
Merci
 
Julien


Message édité par ruliano le 22-12-2005 à 16:38:14
Reply

Marsh Posté le 22-12-2005 à 16:19:27   

Reply

Marsh Posté le 23-12-2005 à 02:05:35    

Tu utilise un champ date dans ta table.
 
Il faut remplir la table de ce nombre d'enregistrement à vide et à la date de création préalablement et on ne fait que des update dessus à la position voulue.
 
Pour trouver où écrire; prendre le rowid de la 1er date la plus ancienne   comme position dans la table.

Reply

Marsh Posté le 23-12-2005 à 10:53:18    

ç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 :
  1. -- the table to be used as a fifo
  2. create table fifo (
  3.     id integer primary key autoincrement,
  4.     data varchar
  5. );
  6. -- remove oldest record from the fifo
  7. -- after each insert into the fifo
  8. create trigger fifo_limit after insert on fifo
  9. begin
  10.     delete from fifo
  11.     where id <= (select max(id) from fifo) - 10000; -- max size is 10000
  12. end;


 
The following slightly more complicated version allows the size of the  
fifo to be changed on the fly.
 

Code :
  1. -- the table to be used as a fifo
  2. create table fifo (
  3.     id integer primary key autoincrement,
  4.     data varchar
  5. );
  6. -- table to store the fifo size limit
  7. create table fifo_size (
  8.     max_entries integer
  9. );
  10. -- set the maximum size of the fifo
  11. -- this can be changed latter
  12. insert into fifo_size values(5);
  13. -- remove any records beyond the fifo size limit
  14. -- whenever the limit is changed
  15. create trigger fifo_resize after update on fifo_size
  16. begin
  17.     delete from fifo
  18.     where id <= (select max(id) from fifo) - (select max_entries from
  19. fifo_size);
  20. end;
  21. -- remove oldest record from the fifo
  22. -- after each insert into the fifo
  23. create trigger fifo_limit after insert on fifo
  24. begin
  25.     delete from fifo
  26.     where id <= (select max(id) from fifo) - (select max_entries from
  27. fifo_size);
  28. end;


 
The following is a sample run of the second version using fifo sizes of  
5 and 8 for demonstration.
 

Code :
  1. insert into fifo values(NULL, 'one');
  2. insert into fifo values(NULL, 'two');
  3. insert into fifo values(NULL, 'three');
  4. select * from fifo;


1|one
2|two
3|three

Code :
  1. insert into fifo values(NULL, 'four');
  2. insert into fifo values(NULL, 'five');
  3. insert into fifo values(NULL, 'six');
  4. select * from fifo;


2|two
3|three
4|four
5|five
6|six

Code :
  1. insert into fifo values(NULL, 'seven');
  2. insert into fifo values(NULL, 'eight');
  3. insert into fifo values(NULL, 'nine');
  4. select * from fifo;


5|five
6|six
7|seven
8|eight
9|nine

Code :
  1. update fifo_size set max_entries = 8;
  2. insert into fifo values(NULL, 'ten');
  3. insert into fifo values(NULL, 'eleven');
  4. insert into fifo values(NULL, 'twelve');
  5. select * from fifo;


5|five
6|six
7|seven
8|eight
9|nine
10|ten
11|eleven
12|twelve

Code :
  1. insert into fifo values(NULL, 'thirteen');
  2. insert into fifo values(NULL, 'fourteen');
  3. insert into fifo values(NULL, 'fifteen');
  4. 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 :
  1. update fifo
  2.     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

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed