events for data in the database, what are the ways?

Question

PHP

There is a table with tasks, each task has an execution time, let's say 09/30/11 14:50 date in Unixtime, how to monitor these dates and send a notification 5 minutes before the start of the task?

so far I know only one solution with an infinite loop and sql query cron
execution

what other options are there?

mysql database, languages ​​php, python

Answer 1

And who adds tasks? If the external code is generally beautiful, we add our own piece to it, and when doing something with the database, we simply ask the database what else is planned to be done there, then we ask at at for the current list of what is planned to be executed, compare the received data, and issue the necessary commands that will bring the list of at-tasks to the form you need.
And then we get the separation - and mysql "twitches" (although, what kind of twitch is it?) Only on the fact of changes in it, and the standard utility is engaged in scheduling (it will obviously do this no worse than a self-written daemon).
Well, if the “base change” event passes secretly from us, it remains to do a poll once a minute in the loop (more often it’s not necessary, you store events with minute precision) and, if the list in the database has changed against the background of the last time, we poll at, and further according to the given plan. Less elegant, but quite functional.

Answer 2

how often is the task table updated? after each update, you can run a php script that will take the time of the tasks, take 5 minutes, write tasks in the crontab: execution time, send a notification about the task to the mail. If you do not want to give access to the crontab, you can write to a sh file, and connect it to the cron.
It if at once so offhand. I'll think about it now)

Answer 3

1. it is necessary to catch the appearance of new records in the database in any way possible (one way or another, this can be done either in the application that replenishes the database, or by a trigger that performs an action 'outside', if the database does not allow anything successful ... periodically (2 times more often than the shortest interval) to make the simplest possible request - for example, the current value of the sequence in the table
1. a daemon (1 process) is written, which should catch the event from the appearance of a record in the database and calculate the firing time of the nearest timer (the simplest request to the table, sorting by the timer operation time - limit 1) and wait either for the timer to fire or for the next event to add a record

Answer 4

Run a cron once a minute a script that selects tasks for execution, sends notifications and sets a completion flag. If something doesn't work once, you can always, albeit belatedly, do it the next time you start it. The simplest, most reliable and not resource-intensive solution, IMHO.
The demons will hang, devour the memory.
Writing each task to cron means difficulties and inaccuracies in editing (you either need to rewrite all tasks, or search by id, which you still need to somehow add there, in cron).
There is another option, but it is unlikely to suit you, because. Do you send email notifications as soon as possible?

Answer 5

A perversion came to my mind, for which do not hit hard, but ...
Write a procedure with an infinite loop and with SLEEP () in it + other activity that you need ...
+ and a query somewhere in the database to destroy the cycle ... For example, it will be value in some config table, work furiously: 1 or 0. You can trigger such a function with a trigger after trying to change something there ... or just with a select ... Or how tuning you can call such a procedure with some parameter, for example, the number of seconds and duration …
It was the first option.
The second option is a procedure:
... you do something there
SLIP for the right amount of time
... did you sleep? you can get rid of...

Answer 6

Here, I don’t want to pull records to tables every minute, I want them to be stored in RAM within a certain interval

Everything correctly told you, what's wrong with making a request to the database every minute?
If you really don't want to request every minute, run a request every 5 minutes and then send notifications once a minute.