Running Scheduled Tasks from MySQL Database

I have an application I am building using CakePHP with a MySQL database. Without getting into too much of the details, it's a video surveillance as a service (VSaaS) application. I have built part of a feature that would allow a user to schedule automated snapshots. Users can choose hour and minutes in five minute increments as well as days of the week that the snapshot should reoccur. What I need to do now is build the a service or daemon that will actually execute the routine(s) necessary to fetch the snapshot, save it and write the necessary data to the database based on the interval chosen by the user which is stored in the database table described below.

Database Table Structure

 CREATE TABLE `snapshots_schedules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `device_id` int(11) DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `resolution` varchar(16) DEFAULT '320x240',
  `sunday` tinyint(1) DEFAULT '0',
  `monday` tinyint(1) DEFAULT '0',
  `tuesday` tinyint(1) DEFAULT '0',
  `wednesday` tinyint(1) DEFAULT '0',
  `thursday` tinyint(1) DEFAULT '0',
  `friday` tinyint(1) DEFAULT '0',
  `saturday` tinyint(1) DEFAULT '0',
  `time` time DEFAULT '00:00:00',
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
        PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Now, I could write a PHP script and run it as a daemon however, from past experience, this is not a secure nor scalable solution. I have searched wide and far on solutions: CakeResque, CakePHP SchedulerShell, Beanstalkd, ZeroMQ, RabbitMQ, etc. Unfortunately, these all fall short of what I am after. These services require me to store the information in their proprietary systems which would just add confusion the an application that already has many components for processing live feeds, handling camera connections and communications and more.

In a nut shell, I am wondering what the best way is and / or best language to write a continuously running service / daemon / script that will query the database and check entries in the table to see if any of those entries need to be acted upon based on the current day of the week and time.

I am definitely open to suggestions however, there obviously has to be a way to store the scheduled data somewhere retrievable so the user can see what is scheduled and remove any schedules they want.

Note: I want to stay away from cron jobs. If your suggestion involves a cron job, please do not bother replying. They are not scalable or easily manageable across a network of servers.

UPDATE - Example

A user creates a scheduled snapshot for a device on Monday's at 6:00PM. The daemon or service, would be continually checking the database for scheduled snapshots that need to be created. For example, every five minutes the daemon or service would run a SELECT query on the database table described above. If it is Monday at 6:00PM, a URL could be executed (http://domain.tld/snapshots/create/device_id) that would actual perform the task of getting the snapshot from the device, saving it, etc.

Thank you

Answers


After doing some more research, this time specifically using Node.js, it appears that there has been a plugin written for Node.js called node-cron that would allow me to do exactly what I am after. If anyone else that needs to schedule tasks finds this post and doesn't want to use cron and PHP, checkout node-cron for Node.js.

https://github.com/ncb000gt/node-cron


Need Your Help

Download excel file from page via WebApi call

c# asp.net excel asp.net-web-api xls

I'm trying to send a 9MB .xls file as a response from web api controller method. The user will click a button on the page and this will trigger the download via the browser.

Parameterizing a HQL IN clause using HqlBasedQuery?

c# nhibernate hql castle-activerecord

How do you pass a list of things for the 'in' clause in Nhibernate HQL?