Create A Trigger Table And Watch Grafana Save Changes

Time to do some serious business now. Let's actually watch some changes in Grafana and respond to them.

The simplest response I can think of is logging to stdout some nice text.

First, I'll make a trigger table watching the dashboards table, then figure out what happens afterwards.

The Trigger Table

First let's grab a database real quick.

; docker run -v $(pwd):/mnt/ -it --entrypoint bash gwyn-baseline-adds-data-source:1
$ cp /var/lib/grafana/grafana.db /mnt/
ctrl+d

Ta da!

Now a quick trigger table:

-- Ensure the _gsync_dashboard_tracker table has the necessary columns
CREATE TABLE IF NOT EXISTS _gsync_dashboard_tracker (
    _rowid INTEGER,
    id INTEGER,
    title TEXT,  -- updated from 'input' to 'title'
    _version INTEGER,
    _updated INTEGER
);

-- Create the trigger to track inserts in the dashboard table

CREATE TRIGGER IF NOT EXISTS gsync_dashboard_insert_tracker
AFTER INSERT OR UPDATE ON dashboard
BEGIN
    INSERT INTO _gsync_dashboard_tracker (_rowid, id, title, _version, _updated)
    VALUES (new.rowid, new.id, new.title, 1, CAST(strftime('%s', 'now') AS INTEGER));
END; 

This triggers correctly; I get entries in _gsync_dashboard_tracker when inserts or updates happen. Great. What should actually happen, though?

The Wrong Approach

This exploration has been pretty interesting and neat and all, but I'm starting to reach the conclusion it's the wrong approach.

What it accomplishes is updating a table whenever a different table is updated. The reason is that the design I thought I wanted is to have a callback in a go process that responds to changes in this table.

I hadn't considered a simple problem: what if the daemon dies? Does it try to register another callback it can respond to? The limitation of go callbacks for sqlite databases is that you can't have the callback registered outside of the current database connection. If the current db connection didn't create the callback trigger, I don't think it can register a callback to automatically respond to the trigger changes; it'd need to be monitoring the table actively. If that's the case, why do I need the trigger table in the first place?

I have an idea towards a simpler design: a simple daemon that dumps the contents of the dashboard table to json files. I can monitor the json files for changes using fsnotify.

I learned a good load about using sqlite so far in this project, though. I never had confidence with it, but now I've got a handle on it, so that's got to be worth something, right?