Allow registration of custom functions via sqlite3_create_function

[SimonRichardson]

Creating custom functions for sqlite3 is commonplace, it's currently not possible to do that with dqlite. The question is, how would this work, would each function callback in each node be called, or just the leader?

The end goal would be to allow things like custom triggers.

An example of what we'd like to use it for:

// Create a trigger that will dispatch any changes that have been
// inserted in a transaction, allowing us to rollback if the dispatch
// fails.
const schema = `
CREATE TRIGGER IF NOT EXISTS insert_model_trigger
BEFORE INSERT ON model
WHEN dispatch("model.insert", NEW.key, NEW.value) != ""
BEGIN
	SELECT RAISE(ROLLBACK, "failed to call model trigger");
END;
`

// Register the custom function, that dispatches an event based on
// changes in a trigger.
sql.Register("sqlite3_custom", &sqlite3.SQLiteDriver{
	ConnectHook: func(conn *sqlite3.SQLiteConn) error {
		if err := conn.RegisterFunc("dispatch", func(name string, args ...interface{}) string {
			err := pubsub.Publish("dispatch", Delta{
				Name:   name,
				Values: args,
			})
			if err != nil {
				return err.Error()
			}
			return ""
		}, false); err != nil {
			return err
		}
		return nil
	},
})

[MathieuBordere]

Thanks @SimonRichardson , will need to think about this and discuss if we could implement this feature. I'll come back to you.

[MathieuBordere]

~~Currently with the way dqlite is implemented, with a separate instance of SQLite embedded in each of the nodes and triggers being a SQLite feature, the registered function would be triggered on all nodes that are actively replicating the database (voters and standby nodes).~~ Sorry, I'm experimenting with this, and it's not true what I said, from first experiment it looks like it's triggered once on the leader. Will have to dive more deeply though, keep you posted.

[SimonRichardson]

@MathieuBordere I'm circling back around to this, did we confirm that this is only run on the leader?

[MathieuBordere]

@MathieuBordere I'm circling back around to this, did we confirm that this is only run on the leader?

Sorry, will try to pick this back up.