But I think this would:
CREATE TABLE ring_buffer (key INTEGER PRIMARY KEY AUTOINCREMENT, stuff TEXT);
CREATE TRIGGER delete_tail AFTER INSERT ON ring_buffer
BEGIN
DELETE FROM ring_buffer WHERE key%10=NEW.key%10 AND key!=NEW.key;
END;
INSERT INTO ring_buffer (stuff) VALUES('Stuff 1');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 2');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 3');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 4');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 5');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 6');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 7');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 8');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 9');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 10');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 11');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 12');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 13');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 15');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 16');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 17');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 18');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 19');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 20');
INSERT INTO ring_buffer (stuff) VALUES('Stuff 21');
SELECT * FROM ring_buffer;
11|Stuff 11
12|Stuff 12
13|Stuff 13
14|Stuff 15
15|Stuff 16
16|Stuff 17
17|Stuff 18
18|Stuff 19
19|Stuff 20
20|Stuff 21
> -----Original Message-----
> From: Richard B. Boulton [mailto:[EMAIL PROTECTED]
> Sent: 24 December 2005 10:44
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] ring buffer table
>
>
> oops, just realised that doesn't work when you get further than 10.
>
>
> > -----Original Message-----
> > From: Richard B. Boulton [mailto:[EMAIL PROTECTED]
> > Sent: 24 December 2005 10:37
> > To: sqlite-users@sqlite.org
> > Subject: RE: [sqlite] ring buffer table
> >
> >
> > If you used an INTEGER PRIMARY KEY AUTOINCREMENT could you
> > use a simple trigger and a modulus of the newly inserted rowid?
> >
> > e.g. for a dimension of 10:
> >
> > CREATE TABLE ring_buffer (key INTEGER PRIMARY KEY
> > AUTOINCREMENT, stuff TEXT);
> > CREATE TRIGGER delete_tail AFTER INSERT ON ring_buffer
> > BEGIN
> > DELETE FROM ring_buffer WHERE key=NEW.key%10 AND key!=NEW.key;
> > END;
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 1');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 2');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 3');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 4');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 5');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 6');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 7');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 8');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 9');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 10');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 11');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 12');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 13');
> > INSERT INTO ring_buffer (stuff) VALUES('Stuff 14');
> > SELECT * FROM ring_buffer;
> >
> > 5|Stuff 5
> > 6|Stuff 6
> > 7|Stuff 7
> > 8|Stuff 8
> > 9|Stuff 9
> > 10|Stuff 10
> > 11|Stuff 11
> > 12|Stuff 12
> > 13|Stuff 13
> > 14|Stuff 14
> >
> > > Hi,
> > > I would like to implement a log table with a finite
> > > dimension, for exemple a
> > > table with 500 records, and when the last record is set in
> > > the table I would
> > > like to come back at the first tuplet and write over the
> > > previous value
> > > recorded. I think it's the way SQLite journal is implmented.
> > > Is there any way to declare this table with these properties
> > > included, so I
> > > don't have to add code to do this function?
> > > Thanks
> > >
> > > Julien
> > >
> > > ----------------------------
> > > 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
> > >
> >
>
