Julien LEFORT wrote:
This looks like a great solution. Just another point,
what if I want to fill this "ring buffer" automatically by using triggers on
other tables, and be able to reset the counter of the ring buffer when I
arrive at the max integer value of the primary key.
--Create trigger on Data table when an event occurs on one of the fields
CREATE TRIGGER Data_Update AFTER UPDATE OF FirstName ON Data
BEGIN
INSERT INTO fifo VALUES (NULL, new.FirstName);
-----------------------
-- There, what if I get the error SQLITE_FULL while the INSERT INTO Data
request???
-- I'm really not sure of the syntax, but could this work?
-----------------------
ON CONFLICT UPDATE fifo SET ID = ID - (SELECT MIN(ID)-1 FROM fifo);
INSERT INTO fifo VALUES (NULL, new.FirstName);
END;
Julien,
What you have proposed won't work because there is no way to catch an
error in SQL. The SQLITE_FULL error is returned to the calling
application only.
On the other hand, this probably isn't a issue for any real application.
Even if you insert 1000 rows into the fifo every second, the 64 bit row
ids will let you do this for 292,271,023 years before you fill the rowid
space. Even if future CPUs and multiple parallel writers let you
increase your write rate by a factor of 1,000,000 you are still good for
at least 292 years.
HTH
Dennis Cote
