[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. );
  7. -- remove oldest record from the fifo
  8. -- after each insert into the fifo
  9. create trigger fifo_limit after insert on fifo
  10. begin
  11.     delete from fifo
  12.     where id <= (select max(id) from fifo) - 10000; -- max size is 10000
  13. 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. );
  7. -- table to store the fifo size limit
  8. create table fifo_size (
  9.     max_entries integer
  10. );
  12. -- set the maximum size of the fifo
  13. -- this can be changed latter
  14. insert into fifo_size values(5);
  16. -- remove any records beyond the fifo size limit
  17. -- whenever the limit is changed
  18. create trigger fifo_resize after update on fifo_size
  19. begin
  20.     delete from fifo
  21.     where id <= (select max(id) from fifo) - (select max_entries from
  22. fifo_size);
  23. end;
  25. -- remove oldest record from the fifo
  26. -- after each insert into the fifo
  27. create trigger fifo_limit after insert on fifo
  28. begin
  29.     delete from fifo
  30.     where id <= (select max(id) from fifo) - (select max_entries from
  31. fifo_size);
  32. 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