I have a SQLite database populated by a C++ application. I am also working on a web interface to allow different diplays using Javascript and PHP. There will only be one instance of the interface open at any given time but it will be on another machine in the network from the SQLite file and C++ application.
There are a couple fields I would like to be able to update from the interface. I can create a trigger to let the C++ application know when data is changed, but if PHP makes the change, the trigger targets that connection and throws an error (since the callback function is defined in the C++ code). I tried enabling shared cache connections for everything involved but the SQLite trigger still can't cross the applications.
Is there a way to automatically notify the C++ application of changes or will I have to regularly poll the database if we continue using SQLite?
The documentation I've been able to find all suggest the latter, but most of it is either vague or older so I'm hoping there's a workaround I haven't been able to find.
I think that it is simply not possible to execute an own C++ function when PHP updates an SQLite database as PHP is using its internal SQLite library to update the database.
So unless you want to mess with PHP and recompile with a custom SQLite implementation (which I really recommend against) you're out of luck.
Alternatives:
You could watch the SQLite file itself on the disk, and you can execute your program if that is updated. However, filesystem watching daemons are not really working well with network drives.
Polling the database is the safest option - that always works, but might be slow, especially because you might open the same SQLite file from two programs at once.
Anyway, if you need to access the database from more than one place (PHP program and C++ program) it might be time to use a different database engine.