Tracking Sqlite Changes

grafanasink wants to track changes in the grafana sqlite database as they're being written.

I see two options: an outside observer trying to track changes, by comparing them with a known good data source, or sqlite somehow tracking inserts and updates itself.

Can Go Register A Callback?

go-sqlite3 lets you provide a callback function when a database mutation happens.

It didn't work for my needs. Following the code is tricky as it crosses the C boundary, but any mutations happening outside of the current connection didn't trigger the hook. The behaviour sounds like it wraps Sqlite's Update Hook.

This quote from the update_hook documentation seems fairly damning for this approach:

The sqlite3_update_hook() interface registers a callback function with the database connection identified by the first argument

Whomp whomp.

Plan B: sqlite somehow tracking inserts and updates itself

The truth is that I don't know much about sqlite, but the docs are pretty great and showed it shouldn't be bad to create a trigger that'll keep track of changes in the grafana database table.

Plan: start showing I know how to use a trigger. Then, see if the trigger can automatically insert some data from the original insert, so ultimately I can add a trigger to Grafana's sqlite db that will track the name of an updated dashboard and the content of the update.

Start Simple

sqlite> CREATE TABLE inputs(input text);
sqlite> CREATE TABLE triggered(tracking text);
sqlite> CREATE TRIGGER inputtrigger AFTER INSERT ON inputs BEGIN INSERT INTO triggered VALUES("trig"); END;
sqlite> INSERT INTO inputs VALUES("hi friend");
sqlite> select * from triggered;
trig
sqlite> 
                    

Insert Some Info From The Original Query

I realised at this point that I'm building an audit table. Once I put the name to the concept I found really good prior art" to guide me.

The mask field in there isn't something I need while I'm toying around, but looks valuable for when I implement this against grafana itself.

sqlite> CREATE TABLE _inputs_history ( _rowid INTEGER, id INTEGER, input TEXT, _version INTEGER, _updated INTEGER);
sqlite> CREATE TRIGGER input_insert_history
   ...> AFTER INSERT ON inputs
   ...> BEGIN 
   ...> INSERT INTO _inputs_history(_rowid, id, input, _version, _updated) 
   ...> VALUES (new.rowid, new.id, new.input, 1, unixepoch('subsec'));
   ...> END;
sqlite> INSERT INTO inputs(input) VALUES("hi friend");
Parse error: no such column: new.id

The first bug! I had thought I got a free ID column, but the Primary Key docs show that I have to explicitly request one with the PRIMARY KEY keywords.

I can't add a primary key after the fact to my inputs table; it's not supported. Time to delete and recreate:

sqlite> DROP TABLE inputs;
sqlite> CREATE TABLE inputs(
(x1...> id INTEGER PRIMARY KEY,
(x1...> input TEXT
(x1...> );
sqlite> INSERT INTO inputs(input) VALUES("hi friend");
sqlite> select * from _inputs_history;
//nothing here

Second learning, this one from the docsTriggers are automatically dropped when the table that they are associated with (the table-name table) is dropped.

sqlite> CREATE TRIGGER input_insert_history AFTER INSERT ON inputs BEGIN INSERT INTO _inputs_history(_rowid, id, input, _version, _updated)  VALUES (new.rowid, new.id, new.input, 1, unixepoch('subsec')); END;
sqlite> INSERT INTO inputs(input) VALUES("hi friend");
sqlite> select * from _inputs_history;
2|2|hi friend|1|1728051238.601

Okay! Enough for the proof of concept stuff. I can move on to investigating the grafana db now. That'll be for next time in this project.