SQLite Ring Buffer

[Jordan Kuehn]

In the spirit of all the DB discussion yesterday which has coincided well with some tools I've been working on this week, I've got a question of my own.

 

Essentially I just want to make a ring buffer stored in an SQLite database (sure, it could be any type of db) to buffer some data acquisition for use as pre-trigger data as well as a local window.  With that in mind I added a trigger to ShaunR's Data Logging Example and increased the decimation factor high enough to not decimate (didn't want to mess with the rest of the example).  The trigger code is below for a buffer of 1000 entries.  Now, this seems to work fine and not blow up the size of the db or anything with my limited testing, but are there any issues with simply deleting preceding rows and allowing rowid to continue increasing?  If there is an issue with this implementation, what proposed solutions do you have?  I could create pointers and such and manually iterate around in LV when inserting, but I really want the db to handle this and have it be transparent to my application.

 

I have an understanding of writing to and querying db's and use them a lot for test specs and results, dynamic field population, etc., but I haven't done much in regards to buffering or storing acquired data and am working on exploring how doing more work on the db side can make things easy on the LV side.

DELETE FROM graph WHERE rowid%1000=NEW.rowid%1000 AND rowid!=NEW.rowid;

Edited January 9, 2016 by Jordan Kuehn

[ShaunR]

It's an unusual use case and I wouldn't recommend a DB for this since there is a lot of overhead for realising a relational DB that you just don't need.

However. I would suggest you UPDATE rather than DELETE. You wouldn't clear a memory location before writing a new value to it in  a ring buffer. You'd just overwrite because it is more efficient. DELETE is an extremely expensive operation compared to UPDATE as well as more esoteric things like fragmentation (Vacuum resolves this but can take a very long time)

 

Thinking about what you are doing a bit more. You are not using a ring buffer, are you? You have a fixed length FIFO. What you probably want is INSERT OR UPDATE that isn't directly supported by SQLite, but can be emulated. The easy one would be INSERT with the REPLACE conflict condition but I think that just does a delete then insert so performance wise, you are no better off. The implementation is easier than messing with triggers, though.

Edited January 9, 2016 by ShaunR

[Jordan Kuehn]

That's a fair point and yes it behaves more like a fixed length fifo that crawls along in memory rather than a ring buffer.  The UPDATE command would require some note-keeping of where the pointer(s) are on the LV side, but does sound like the *correct* way to do this.  I would like to be able to insert element(s) and read the entire buffer already in order in LV without doing that.  I think a VIEW would perhaps be the way to go for the retrieval side, I'm not sure about the insertion.

 

As far as not using a db, that's also a fair point and I may not ever wind up using this for my stated purpose, but I think it could still be useful especially when combined with additional tables that perform some historical logging and such.  At the very least it's getting me to explore the sqlite toolkits more.

[ShaunR]

I would like to be able to insert element(s) and read the entire buffer already in order in LV without doing that. 

Ah. Yes. But you can read it out in any order you like by just by using the ORDER BY clause. That's the beauty of DBs. The "View" isn't defined by the data structure,

Edited January 9, 2016 by ShaunR

[Jordan Kuehn]

Ah. Yes. But you can read it out in any order you like by just by using the ORDER BY clause. That's the beauty of DBs. The "View" isn't defined by the data structure,

 

ORDER BY looks like it would work if I also include a tick count or something with my data. Works for me on the retrieval side, and that is a nice aspect to the DB.  On the insertion side, I suppose I could create an auto-increment variable in the DB that would modulo with my buffer size and provide the rowid to UPDATE.

 

I also like the idea of setting up triggers to say, average my buffer and store a point in another table each time it wraps around.

[ShaunR]

ORDER BY looks like it would work if I also include a tick count or something with my data. Works for me on the retrieval side, and that is a nice aspect to the DB.  On the insertion side, I suppose I could create an auto-increment variable in the DB that would modulo with my buffer size and provide the rowid to UPDATE.

 

I also like the idea of setting up triggers to say, average my buffer and store a point in another table each time it wraps around.

 

Now you're talking.

Edited January 9, 2016 by ShaunR

[drjdpowell]

This schema seems to work (I did a 10-element buffer instead of 1000):

CREATE TABLE Buffer (Value);
CREATE TABLE BufferIndex (I);
CREATE TRIGGER BufferIncr BEFORE INSERT ON Buffer
    FOR EACH ROW BEGIN   
       UPDATE BufferIndex SET I = ((SELECT I from BufferIndex)+1)%10;     
    END;
INSERT INTO BufferIndex (I) VALUES (-1)  -- Initial index is -1

Then insert data (the current time in this example) into the buffer with:

INSERT OR REPLACE INTO Buffer (rowID, Value) Values ((SELECT I FROM BufferIndex),time('now'))

The Trigger serves to increment the index before the INSERT.

 

To get the ordered elements one can use a View:

CREATE VIEW OrderedBuffer AS
    SELECT Value FROM Buffer,BufferIndex WHERE Buffer.rowID>I   
    UNION ALL 
    SELECT Value FROM Buffer,BufferIndex WHERE Buffer.rowID<=I;

[Jordan Kuehn]

That looks very promising!  I'll give it a try.  I thought that using a VIEW might be a good approach for retrieval.  Thanks!  I've used your toolkit as well and will probably use it for this implementation.

[drjdpowell]

Just playing around, you can simplify the INSERTs by using an INSTEAD OF trigger on the View:

CREATE TRIGGER NewValue INSTEAD OF INSERT ON OrderedBuffer
  FOR EACH ROW BEGIN    
     INSERT OR REPLACE INTO Buffer (rowID, Value) Values ((SELECT I FROM BufferIndex),NEW.Value);
  END;

Then you can insert into the View with a simpler statement that avoids referring to the index at all:

INSERT INTO OrderedBuffer (Value) Values (time('now'))

[Jordan Kuehn]

The combined schema from drjdpowell's suggestions with the trigger I alluded to at the end.  Works well enough right now while I'm playing around with it.  I like the streamlined View and Inserts and simple interface with LV.  Thanks for the help.

BEGIN IMMEDIATE;   -- wrap initial work in a single transaction

DROP TABLE Buffer;
DROP TABLE BufferIndex;
DROP VIEW OrderedBuffer;
DROP TABLE History;

CREATE TABLE Buffer (Value);
CREATE TABLE BufferIndex (I);
CREATE TRIGGER BufferIncr BEFORE INSERT ON Buffer
    FOR EACH ROW BEGIN   
       UPDATE BufferIndex SET I = ((SELECT I from BufferIndex)+1)%100;     
    END;
INSERT INTO BufferIndex (I) VALUES (-1);  -- Initial index is -1

CREATE VIEW OrderedBuffer AS
    SELECT Value FROM Buffer,BufferIndex WHERE Buffer.rowID>I   
    UNION ALL 
    SELECT Value FROM Buffer,BufferIndex WHERE Buffer.rowID<=I;

CREATE TRIGGER NewValue INSTEAD OF INSERT ON OrderedBuffer
  FOR EACH ROW BEGIN    
     INSERT OR REPLACE INTO Buffer (rowID, Value) Values ((SELECT I FROM BufferIndex),NEW.Value);
  END;

CREATE TABLE History (Value);
CREATE TRIGGER AvgBuffer AFTER INSERT ON Buffer 
  WHEN (SELECT I from BufferIndex) = 0
  BEGIN
     INSERT INTO History(Value) VALUES ((SELECT avg(Value) FROM Buffer)); 
  END; 

COMMIT;