se/SE/se-lib/Core/AsyncJobsDB.php

<?php

/**
 * 
	`CRM_ASYNC_JOB_LOG`:
		`ID`
		`ID_FUNCTION`
		`ID_SOURCE_JOB`
		`P_ID`
		`DATE`
		`VERSION`
		`JOB_NAME`
		`LOCK_VALUE`
		`USER`
		`A_STATUS`
		`A_RECORD_CREATE_DATE`
		`A_RECORD_CREATE_AUTHOR`
		`A_RECORD_UPDATE_DATE`
		`A_RECORD_UPDATE_AUTHOR`
 * 
 * 
 */

Lib::loadClass('Request');

class Core_AsyncJobsDB {

	static $VERSION = 1; // `CRM_CONFIG`.`CONF_KEY` = 'Core_AsyncJobs__version'
	static $CRM_CONFIG_VERSION_KEY = 'Core_AsyncJobs__version';

	static function insert($item) {
		if (empty($item['JOB_NAME'])) throw new Exception("Missing JOB_NAME in Core_AsyncJobsDB::insert");

		return DB::getPDO()->insert('CRM_ASYNC_JOB_LOG', array_merge([
			'ID_FUNCTION' => 0, // int(11) NOT NULL,
			// 'ID_SOURCE_JOB' int(11) NOT NULL DEFAULT 0, -- id job
			// 	'P_ID' int(11) NOT NULL DEFAULT 0, -- parent job id
			'DATE' => date("Y-m-d"),// date NOT NULL,
			// 'VERSION' => 0, // int(11) NOT NULL DEFAULT 0,
			'JOB_NAME' => '', // varchar(200) NOT NULL, -- copy from CRM_ASYNC_FUNCTIONS
			'LOCK_VALUE' => '', // varchar(200) NOT NULL DEFAULT '', -- second part for unique JOB_NAME eg. primaryKey
			'USER' => User::getLogin(), // varchar(20) NOT NULL, -- user who start this function
			'A_STATUS' => 'WAITING',
			'A_RECORD_CREATE_DATE' => "NOW()",
			'A_RECORD_CREATE_AUTHOR' => User::getLogin(),
			// 	UNIQUE KEY 'JOB_LOCK' ('JOB_NAME', 'LOCK_VALUE', 'USER'), -- add USER to store user click and respond that job is running by other user
			// 	KEY 'DATE' ('DATE'),
			// 	KEY 'A_STATUS' ('A_STATUS'),
			// 	KEY 'A_RECORD_UPDATE_DATE' ('A_RECORD_UPDATE_DATE')
		], $item));
	}

	static function fetch($idJob) {
		return DB::getPDO()->fetchFirst("
			select j.*
			from CRM_ASYNC_JOB_LOG j
			where j.ID = :id
		", [
			':id' => $idJob,
		]);
	}

	static function checkAsyncJobDatabase() {
		// `CRM_CONFIG`.`CONF_KEY` = 'Core_AsyncJobs__version'
		$dbVersion = (int)DB::getPDO()->fetchValue(" select CONF_VAL from CRM_CONFIG where CONF_KEY = :key ", [ ':key' => self::$CRM_CONFIG_VERSION_KEY ]);
		if ($dbVersion < 1) self::upgradeAsyncJobDatabaseToVersion1();

		// $dbVersion = (int)DB::getPDO()->fetchValue(" select CONF_VAL from CRM_CONFIG where CONF_KEY = :key ", [ ':key' => 'Core_AsyncJobs__version' ]);
		// if ($dbVersion < 2) self::upgradeAsyncJobDatabaseToVersion2();

		$dbVersion = (int)DB::getPDO()->fetchValue(" select CONF_VAL from CRM_CONFIG where CONF_KEY = :key ", [ ':key' => self::$CRM_CONFIG_VERSION_KEY ]);

		return ($dbVersion < self::$VERSION) ? false : true;
	}
	static function upgradeAsyncJobDatabaseToVersion1() {
		// - insertOrUpdate new row in `CRM_ASYNC_FUNCTIONS` ( $TODAY, $version, $user, $jobName )
		// - $JOB_ID = fetchValue select ID from `CRM_ASYNC_FUNCTIONS` where JOB_NAME = $jobName
		//   - `CRM_ASYNC_FUNCTIONS`.`A_STATUS` default 'WAITING' - not started
		//   - `CRM_ASYNC_FUNCTIONS`.`A_STATUS`: 'NORMAL' - started
		//   - `CRM_ASYNC_FUNCTIONS`.`A_STATUS`: 'OFF_HARD' - not running
		//   - `CRM_ASYNC_FUNCTIONS`.`A_STATUS`: 'DELETED' - removed
		$sql = "
			CREATE TABLE IF NOT EXISTS `CRM_ASYNC_FUNCTIONS` ( -- list of async function definitions / config
				`ID` int(11) NOT NULL AUTO_INCREMENT,
				`ID_ZASOB` int(11) NOT NULL DEFAULT 0, -- TODO - register function in CRM_LISTA_ZASOBOW URL_ACTION to set perms
				`JOB_NAME` varchar(200) NOT NULL,
				`VERSION` int(11) NOT NULL DEFAULT 0,
				`LOCK_TYPE` enum('ROW', 'TABLE', 'SYSTEM', 'NO_LOCK') DEFAULT 'ROW',
				-- ROW - only one active job per row, eg. create pdf, close FV, @require primaryKey in JOB.LOCK_VALUE
				-- TABLE - ony one active job per table, eg. update columns, make report, sync
				-- SYSTEM - only one active job
				-- TODO USER_... - lock per user
				-- NO_LOCK - allow multiple jobs

		--		`USER` varchar(20) NOT NULL,
		--		`DATE` date NOT NULL,
		--		`A_STATUS` enum('WAITING','NORMAL','WARNING','OFF_SOFT','OFF_HARD','DELETED') DEFAULT 'WAITING',

				`A_RECORD_CREATE_DATE` datetime NOT NULL,
				`A_RECORD_CREATE_AUTHOR` varchar(20) NOT NULL,
				`A_RECORD_UPDATE_DATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
				`A_RECORD_UPDATE_AUTHOR` varchar(20) NOT NULL,
				PRIMARY KEY (`ID`),
				UNIQUE KEY `JOB_NAME` (`JOB_NAME`),
				KEY `DATE` (`DATE`),
				KEY `A_STATUS` (`A_STATUS`),
				KEY `A_RECORD_UPDATE_DATE` (`A_RECORD_UPDATE_DATE`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2;
		";
		// DB::getPDO()->execSql($sql);
		$sql = "
			CREATE TABLE IF NOT EXISTS `CRM_ASYNC_JOB_LOG` (
				`ID` int(11) NOT NULL AUTO_INCREMENT,
				`ID_FUNCTION` int(11) NOT NULL,
				`ID_SOURCE_JOB` int(11) NOT NULL DEFAULT 0, -- id job
				`P_ID` int(11) NOT NULL DEFAULT 0, -- parent job id
				`DATE` date NOT NULL,
				`VERSION` int(11) NOT NULL DEFAULT 0,
				`JOB_NAME` varchar(200) NOT NULL, -- copy from CRM_ASYNC_FUNCTIONS
				`LOCK_VALUE` varchar(200) NOT NULL DEFAULT '', -- second part for unique JOB_NAME eg. primaryKey
				`USER` varchar(20) NOT NULL, -- user who start this function
				`A_STATUS` enum('WAITING','NORMAL','WARNING','OFF_SOFT','OFF_HARD','DELETED') DEFAULT 'WAITING',

				`A_RECORD_CREATE_DATE` datetime NOT NULL,
				`A_RECORD_CREATE_AUTHOR` varchar(20) NOT NULL,
				`A_RECORD_UPDATE_DATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
				`A_RECORD_UPDATE_AUTHOR` varchar(20) NOT NULL,
				PRIMARY KEY (`ID`),
			--	UNIQUE KEY `JOB_LOCK` (`JOB_NAME`, `LOCK_VALUE`, `USER`), -- add USER to store user click and respond that job is running by other user
				KEY `DATE` (`DATE`),
				KEY `A_STATUS` (`A_STATUS`),
				KEY `A_RECORD_UPDATE_DATE` (`A_RECORD_UPDATE_DATE`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2;
		";
		DB::getPDO()->execSql($sql);

		// @usage:
		// `CRM_ASYNC_FUNCTIONS`.`JOB_NAME` = '
		//     route = ant
		// --  & path = default_db.in7_dziennik_koresp/test-bash
		//     & template = test-loop
		//     & typeName = default_db:IN7_DZIENNIK_KORESP
		//     & primaryKey = 66263
		//     & primaryKeyField = ID
		// ->  `CRM_ASYNC_FUNCTIONS`.`JOB_NAME` =
		//     namespace = default_db/IN7_DZIENNIK_KORESP
		//     ant = test-bash
		//     template = test-loop
		//     primaryKey = 66263
		// ->  `CRM_ASYNC_FUNCTIONS`.`JOB_NAME` Format: "{$type}|..."
		// ->  `CRM_ASYNC_FUNCTIONS`.`JOB_NAME` Format: "Ant|{$namespace}"
		// ->  `CRM_ASYNC_JOB_LOG`.`LOCK_VALUE` Format: "{$primaryKey}|{$func_name}|..."
		// ->  `CRM_ASYNC_JOB_LOG`.`LOCK_VALUE` Format: "{$primaryKey}|{$func_name}|{$template}"
		// -> Example: JOB_NAME = 'Ant|default_db/IN7_DZIENNIK_KORESP', LOCK_VALUE = '66263|test-bash|test-loop'

		// Add fields:
		// - function config at execution time:
		//   - eg LOCK by user | feature | namespace | no_lock -- should contain in name
		// - process result - last result - NO - result from file, separate request needed

		// IDEA: 2 tables:
		// - `Config` - for function config (create row if not exists)
		// - `Log` - for log where JOB_ID is created or used from Config table

		// then createNewJob:
		// 1. fetch job config from CRM_ASYNC_FUNCTIONS_CONFIG
		// 1.1 IF 404 then create row, read config from function (how?)
		// 1.2 IF exists then check status and lock config
		// 1.2.1 IF online and lock then return error
		// 1.2.2 IF !online and !lock then create new row in Log table and return correct JOB_ID

		self::_upgdateAsyncJobDatabaseVersion($version = 1);
	}
	static function upgradeAsyncJobDatabaseToVersion2() {
		// ...

		self::_upgdateAsyncJobDatabaseVersion($version = 2);
	}
	static function _upgdateAsyncJobDatabaseVersion($version) {
		DB::getPDO()->insertOrUpdate('CRM_CONFIG', [
			'CONF_KEY' => self::$CRM_CONFIG_VERSION_KEY,
			'@insert' => [
				'CONF_VAL' => $version,
			],
			'@update' => [
				'CONF_VAL' => $version,
			]
		]);
	}

}