How do I notify a process of an SQLite database ch

Question

Let's say I have two or more processes dealing with an SQLite database - a "player" process and many "editor" processes.

The "player" process reads the database and updates a view - in my case it would be a waveform being mixed to the soundcard depending on events stored in the database.

An "editor" process is any editor for that database: it changes the database constantly.

Now I want the player to reflect the editing changes quickly.

I know that SQLite supplies hooks to trace database changes within the same process, but there seems to be little info on how to do this with multiple processes.

I could poll the database constantly, compare records and trigger events, but that seems to be quite inefficient, especially when the database grows to a large size.

I am thinking about using a log table and triggers, but I wonder if there is a simpler method.

Answer 1

I think in that case, I would make a process to manage the database read/writes.

Each editor that want to make some modifications to the database makes a call to this proccess, be it through IPC or network, or whatever method.

This process can then notify the player of a change in the database. The player, when he wants to retrieve some data should make a request of the data it wants to the process managing the database. (Or the db process tells it what it needs, when it notifies of a change, so no request from the player needed)

Doing this will have the advantage of having only one process accessing the SQLite DB, so no locking or concurrency issues on the database.