Cron Expression implement with database and CronTab

[JarbyDev]

you talked about you cron expression work with a database where the Cron Schedule is store and Crontab, can you provide an Sample of that. i want to know how make the Cron grab the Cron Schedule from the databse thanks

[fruitl00p]

We personally do it via two tables:

CREATE TABLE `jobs` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `cronTabId` int(10) unsigned NOT NULL DEFAULT '0',
 `command` varchar(255) NOT NULL,
 `scheduleDatetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `startDatetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `endDatetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `queued` tinyint(1) NOT NULL DEFAULT '0',
 `errorLevel` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `result` text NOT NULL,
 PRIMARY KEY (`id`),
 KEY `queued` (`queued`),
 KEY `endDatetime_startDatetime` (`endDatetime`,`scheduleDatetime`),
 KEY `cronTabId` (`cronTabId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

and a table tab

CREATE TABLE `tab` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `title` varchar(255) NOT NULL,
 `description` text NOT NULL,
 `command` varchar(255) NOT NULL,
 `cronDefinition` varchar(255) NOT NULL,
 `lastTimestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `active` bit(1) NOT NULL DEFAULT b'1',
 PRIMARY KEY (`id`),
 KEY `active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Then we use a webhook / cron like system where we look in the tabs table, then insert into the jobs table and run those via workers (sqs/gearman etc)

i.e. using PHP:

$activeTabs; // some query to select from tabs where active =1
foreach ($activeTabs as $tab) {
    $cron = Cron\CronExpression::factory($tab['cronDefinition']);
    if ($cron->getPreviousRunDate()->format('U') > strtotime($tab['lastTimestamp'])) {
        $jobsCreated[(int)$tab['id']] = $this->createJob($tab['command'], $tab['id']);
    }
}

/// etc...

the create job function would insert into jobs the crontTabId, actual Command and scheduled datetime (now / 0000-00-00)

then we'd spool the jobs into some queue system (like Amazon SQS / beanstalkd, gearman etc) They just get a 'run jobId XX' command and query the job, execute the command etc store the results back into the table...

[mtdowling]

This question isn't specific to this project, so it's better suited for places like stackoverflow.