There's very little to it, really, just a messages table with a id INTEGER PRIMARY KEY AUTOINCREMENT column (autoincrement to prevent id reuse, which is otherwise legal), a payload TEXT NOT NULL column (which is usually JSON encoded), and, in my case, a TEXT json annotations column, with some computed indexes. A publisher just pushes rows in, and a subscriber takes an exclusive lock to grab the first row matching the annotations that it cares about (I use DELETE RETURNING, but you can make it work however you need).
You can use `PRAGMA data_version` on a dedicated thread to watch for changes and notify other waiters via a condition variable. It's not the nicest solution, because it's just a loop around a query, but it gets the job done.
A req-rep pattern can be done by doing a `INSERT ... RETURNING id` and having the the other side re-push into the same or a different message queue with an annotation referring to that id. Alternatively, you could have a table with a req, rep, and status column to coordinate it all.
It's far from everything you'd need from a complete, robust message broker, but for small single or multi-process message queue with a max of a few dozen readers and writers, it gets the job done nicely. In a single process, you can even replace the data_version loop thread with `sqlite3_commit_hook` on writers to notify readers that something has changed via the condition_variable.
There's very little to it, really, just a messages table with a id INTEGER PRIMARY KEY AUTOINCREMENT column (autoincrement to prevent id reuse, which is otherwise legal), a payload TEXT NOT NULL column (which is usually JSON encoded), and, in my case, a TEXT json annotations column, with some computed indexes. A publisher just pushes rows in, and a subscriber takes an exclusive lock to grab the first row matching the annotations that it cares about (I use DELETE RETURNING, but you can make it work however you need).
You can use `PRAGMA data_version` on a dedicated thread to watch for changes and notify other waiters via a condition variable. It's not the nicest solution, because it's just a loop around a query, but it gets the job done.
A req-rep pattern can be done by doing a `INSERT ... RETURNING id` and having the the other side re-push into the same or a different message queue with an annotation referring to that id. Alternatively, you could have a table with a req, rep, and status column to coordinate it all.
It's far from everything you'd need from a complete, robust message broker, but for small single or multi-process message queue with a max of a few dozen readers and writers, it gets the job done nicely. In a single process, you can even replace the data_version loop thread with `sqlite3_commit_hook` on writers to notify readers that something has changed via the condition_variable.