CREATE TABLE `def_recurring` (
`Guid` char(50) NOT NULL,
`ModuleType` varchar(50) NOT NULL,
`Package` char(255) DEFAULT NULL,
`Project` varchar(100) DEFAULT NULL,
`Name` varchar(255) NOT NULL,
`CronExpression` varchar(100) NOT NULL,
`NextRunDate` datetime DEFAULT NULL,
`LastRunDate` datetime DEFAULT NULL,
`RunCounter` int(11) DEFAULT 0,
`Active` char(2) DEFAULT 'Y',
`Payload` text DEFAULT NULL,
`CreatedBy` varchar(255) DEFAULT NULL,
`CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`Guid`),
KEY `idx_module_type` (`ModuleType`),
KEY `idx_package` (`Package`),
KEY `idx_active` (`Active`),
KEY `idx_next_run` (`NextRunDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
INSERT INTO def_recurring
(Guid, ModuleType, Package, Project,
Name, CronExpression, NextRunDate,
Active, Payload, CreatedBy, CreatedDate)
VALUES
('{guid}', '{module_type}', '{package}',
'{project}', '{name}',
'{cron_expression}', '{next_run_date}',
'Y', '{payload}', '{created_by}', now())
SELECT *
FROM def_recurring
WHERE Active = 'Y'
AND ModuleType = '{module_type}'
AND (Package = '{package}' OR '{package}' = '')
AND NextRunDate <= now()
ORDER BY NextRunDate
UPDATE def_recurring
SET LastRunDate = '{last_run_date}'
WHERE Guid = '{guid}'
AND Active = 'Y'
AND ModuleType = '{module_type}'
AND (Package = '{package}' OR '{package}' = '')
AND NextRunDate <= now()
AND NextRunDate = '{expected_next_run_date}'
AND (LastRunDate IS NULL OR LastRunDate < NextRunDate)
UPDATE def_recurring
SET NextRunDate = '{next_run_date}',
RunCounter = RunCounter + 1
WHERE Guid = '{guid}'
AND Active = 'Y'
AND ModuleType = '{module_type}'
AND (Package = '{package}' OR '{package}' = '')
AND NextRunDate = '{expected_next_run_date}'
AND LastRunDate = '{last_run_date}'
UPDATE def_recurring
SET LastRunDate = NULL
WHERE Guid = '{guid}'
AND Active = 'Y'
AND ModuleType = '{module_type}'
AND (Package = '{package}' OR '{package}' = '')
AND NextRunDate = '{expected_next_run_date}'
AND LastRunDate = '{last_run_date}'
SELECT *
FROM def_recurring
WHERE ModuleType = '{module_type}'
AND Package = '{package}'
ORDER BY Active DESC, NextRunDate
UPDATE def_recurring
SET Active = 'N'
WHERE Guid = '{guid}'