Background
I am considering various architecture options. Although I have used SQL for many years as a query language, I have limited experience with triggers and stored procedures.
Problem: PHP and Python integration via MySQL
In the red corner of the boxing ring, I have ready-made PHP that processes some XML, which is uploaded to the server via HTTP, and writes it to the MySQL database.
In the blue corner of the ring, I have a daemon written in Python that wants to quickly find out the data that was clicked, so it can process it in real time.
The boxing ring itself is Ubuntu (possibly Apache, but not yet confirmed).
Possible solutions
I consider three different ways of communicating between processes.
Rewrite PHP completely in Python. It will probably take too long to be viable.
Run PHP so that it also writes XML to the socket. Ask the Python thread to listen on the socket. It seems doable. It will need to be rewritten every time a new version of PHP is released. It also seems a little ugly.
Write a SQL trigger that informs Python code that the database has been updated and can receive the latest regular SQL queries. This is a technology that I am not familiar with, and therefore this question:
Question
Is there a standard idiom for notifying a running Python program that another process has updated a MySQL table?
Possible answers?
I guess the answer will include MySQL triggers, but I'm happy to be told that I bark the wrong tree.
From my research so far, I believe that one answer was to cause the execution of an arbitrary executable file , which then could speak through the socket the main process, but I was hoping there might be something a little more direct - like calling an SQL API, which blocks until the trigger starts.
source share