410 lines
15 KiB
PHP
Executable File
410 lines
15 KiB
PHP
Executable File
<?php
|
|
/*
|
|
*
|
|
* 2. PLT-Level
|
|
*
|
|
* Overview:
|
|
* This PHP script automates database updates by processing rows marked for action in various tables.
|
|
* It dynamically executes predefined functions based on table-specific instructions stored in the database.
|
|
* Functions are executed in PHP, Python, or JavaScript environments depending on configuration.
|
|
*
|
|
* How It Works:
|
|
* 1. The script queries the `SYS_PRD_BND.Tables` table to determine which tables require processing.
|
|
* 2. For each identified table, it selects rows updated since the last run (based on the `LastUpdated` column).
|
|
* 3. Each row is individually processed through:
|
|
* - PHP-level triggers defined in system-level or database-level code.
|
|
* - Python-level triggers similarly defined.
|
|
* 4. If a trigger modifies the row, the script updates the database with new values.
|
|
* 5. Errors encountered during processing are logged and sent as notifications via Telegram.
|
|
*
|
|
* Tables and Their Purpose:
|
|
* - SYS_PRD_BND.Tables:
|
|
* Defines active tables and their respective PHP, Python, or JavaScript trigger codes.
|
|
* Columns: Name, onUpdate_phpCode, onUpdate_pyCode, onUpdate_jsCode, LastUpdated, LastError
|
|
*
|
|
* - SYS_PRD_BND.Constants:
|
|
* Holds constants that are injected into the PHP environment during execution.
|
|
* Columns: Name, Type, Value
|
|
*
|
|
* - SYS_PRD_BND.SupportFunctions:
|
|
* Stores reusable PHP functions available during trigger execution.
|
|
* Columns: Name, InputArgs_json, PhpCode
|
|
*
|
|
* - SYS_PRD_BND.PyPi:
|
|
* Lists external Python libraries imported into Python trigger execution.
|
|
* Columns: LibName, AliasName
|
|
*
|
|
* - SYS_PRD_BND.Npm:
|
|
* Lists external Node modules imported into JavaScript trigger execution.
|
|
* Columns: PackageName, AliasName, VersionOrTag
|
|
*
|
|
* Dynamic Tables:
|
|
* - Application-specific tables, each must include at least:
|
|
* - LastUpdated (timestamp for tracking)
|
|
* - Primary key columns for row identification (defined externally)
|
|
*
|
|
* Important Functions:
|
|
* - sendTelegramMessage($message, $dstUsers): Sends notifications to a specified Telegram group.
|
|
* - runProcess($command, $code, &$stdout, &$error): Executes external PHP/Python code securely.
|
|
*
|
|
* Usage:
|
|
* Ensure proper configuration of constants, Telegram bot token, and required Python modules.
|
|
* Regularly schedule this script to automate database maintenance and data integrity tasks.
|
|
*
|
|
*
|
|
*
|
|
*
|
|
* Expects tables:
|
|
* SYS_PRD_BND.Tables (Name, onUpdate_phpCode, onUpdate_pyCode, LastUpdated, LastError)
|
|
* SYS_PRD_BND.Constants (Name, Type, Value)
|
|
* SYS_PRD_BND.SupportFunctions (Name, InputArgs_json, PhpCode)
|
|
* SYS_PRD_BND.PyPi (LibName, AliasName)
|
|
* DynamicTables (LastUpdated, [PrimaryKeyColumns], ...)
|
|
*/
|
|
|
|
// Auto-include all files in this folder with extension .inc.php
|
|
foreach(scandir(__DIR__) as $filename) if (substr($filename,-8) == ".inc.php") require_once __DIR__."/$filename";
|
|
|
|
/**
|
|
* Processes all active tables, updating rows based on trigger conditions.
|
|
* This function scans each active table, executes dynamic trigger code,
|
|
* and handles errors accordingly.
|
|
*/
|
|
function processAllTheActiveTables() {
|
|
echo "Scanning all the tables that need updating...\n";
|
|
$activeTables = sql("SELECT Name, onUpdate_phpCode, onUpdate_pyCode, onUpdate_jsCode, LastUpdated FROM SYS_PRD_BND.Tables");
|
|
|
|
foreach ($activeTables as $activeTable) {
|
|
processActiveTable($activeTable);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Processes a single active table.
|
|
*/
|
|
function processActiveTable($activeTable) {
|
|
extract($activeTable);
|
|
echo "Found Table: " . greenText($Name) . "\n";
|
|
echo "Scanning rows in table $Name that require trigger execution:\n";
|
|
|
|
ensureLastUpdatedColumnExists($Name);
|
|
|
|
$rowsToProcess = sql_read_and_hydrate("SELECT * FROM $Name WHERE LastUpdated > '$LastUpdated'");
|
|
|
|
foreach ($rowsToProcess as $unprocessedRow) {
|
|
processTableRow($activeTable, $unprocessedRow);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Processes all cron jobs that match the current time.
|
|
* Uses a temp file to ensure jobs run only once per minute,
|
|
* even if the script crashes and restarts.
|
|
*/
|
|
function processCronJobs() {
|
|
$lockFile = '/tmp/cron_last_run.json';
|
|
$currentTimeKey = date("Y-m-d H:i"); // Unique ID for this specific minute
|
|
|
|
// 1. CHECK PREVIOUS RUN (File Persistence)
|
|
if (file_exists($lockFile)) {
|
|
$data = json_decode(file_get_contents($lockFile), true);
|
|
|
|
// If the stored time matches right now, we already ran.
|
|
if (($data['last_run'] ?? '') === $currentTimeKey) {
|
|
return;
|
|
}
|
|
}
|
|
|
|
// 2. UPDATE TRACKER IMMEDIATELY
|
|
// We write to the file now to "claim" this minute.
|
|
file_put_contents($lockFile, json_encode(['last_run' => $currentTimeKey]));
|
|
|
|
echo "Scanning all the cron jobs that need to be run...\n";
|
|
|
|
// 3. FETCH JOBS
|
|
// We check for an exact match OR a wildcard (-1)
|
|
$sql = "SELECT id, PhpCode
|
|
FROM SYS_PRD_BND.CronJobs
|
|
WHERE
|
|
(Minute = MINUTE(NOW()) OR Minute = -1)
|
|
AND (Hour = HOUR(NOW()) OR Hour = -1)
|
|
AND (DayOfMonth = DAY(NOW()) OR DayOfMonth = -1)
|
|
AND (Month = MONTH(NOW()) OR Month = -1)
|
|
AND (DayOfWeek = DAYOFWEEK(NOW()) OR DayOfWeek = -1)";
|
|
|
|
// 4. EXECUTION LOOP
|
|
$jobs = sql($sql);
|
|
|
|
if ($jobs) {
|
|
foreach ($jobs as $cronJob) {
|
|
if (!empty($cronJob["PhpCode"])) {
|
|
// Pass a unique identifier (cronJob_ID) to the sandbox runner
|
|
runPHPCronCode("cronJob_" . $cronJob["id"], $cronJob["PhpCode"], $cronJob["id"]);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
/**
|
|
* Executes database-level dynamic PHP trigger code.
|
|
*/
|
|
function runPHPCronCode($functionName,$code,$cronJobId) {
|
|
echo "Running PHP CRON code for cronJobId $cronJobId...\n";
|
|
$phpCode = generatePHPCronCode($functionName, $code,$cronJobId);
|
|
$result = runSandboxedPHP($phpCode,$stdout,$stderr);
|
|
|
|
handleCronJobExecutionResult($result, $stdout,$stderr,$cronJobId);
|
|
}
|
|
|
|
|
|
/**
|
|
* Ensures the 'LastUpdated' column exists, adding it if missing.
|
|
*/
|
|
function ensureLastUpdatedColumnExists($_tableName) {
|
|
echo "Ensuring LastUpdated column is created if not exists..\n";
|
|
list($dbName,$tableName) = (strpos($_tableName,".") ? explode(".",$_tableName) : ["",$_tableName]);
|
|
sql((empty($dbName)?"":"USE $dbName; ")."ALTER TABLE `$tableName` ADD COLUMN IF NOT EXISTS LastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
|
|
}
|
|
|
|
/**
|
|
* Processes an individual row for triggers.
|
|
*/
|
|
function processTableRow($activeTable, $unprocessedRow) {
|
|
$functionName = "handleNew" . str_replace(".", "__", $activeTable["Name"]) . "Row";
|
|
|
|
if (function_exists($functionName)) {
|
|
runSystemLevelTrigger($functionName, $activeTable, $unprocessedRow);
|
|
}
|
|
|
|
if (!empty($activeTable['onUpdate_phpCode'])) {
|
|
runPHPCodeTrigger($functionName, $activeTable, $unprocessedRow);
|
|
}
|
|
|
|
if (!empty($activeTable['onUpdate_pyCode'])) {
|
|
runPythonCodeTrigger($functionName, $activeTable, $unprocessedRow);
|
|
}
|
|
|
|
if (!empty($activeTable['onUpdate_jsCode'])) {
|
|
runJavascriptCodeTrigger($functionName, $activeTable, $unprocessedRow);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Executes system-level PHP trigger function.
|
|
*/
|
|
function runSystemLevelTrigger($functionName, $activeTable, &$row) {
|
|
echo "Calling system-level function " . greenText($functionName) . "\n";
|
|
$error = '';
|
|
|
|
if ($functionName($row, $error) === false) {
|
|
updateTriggerError($activeTable['Name'], $error);
|
|
} else {
|
|
clearTriggerError($activeTable['Name']);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Executes database-level dynamic PHP trigger code.
|
|
*/
|
|
function runPHPCodeTrigger($functionName, $activeTable, $row) {
|
|
echo "Running PHP Code trigger...\n";
|
|
$phpCode = generatePHPTriggerCode($functionName, $activeTable['onUpdate_phpCode'], $row);
|
|
$result = runSandboxedPHP($phpCode,$stdout,$stderr);
|
|
|
|
handleTriggerExecutionResult($result, $stdout,$stderr,$row, $activeTable);
|
|
}
|
|
|
|
/**
|
|
* Executes database-level dynamic Python trigger code.
|
|
*/
|
|
function runPythonCodeTrigger($functionName, $activeTable, $row) {
|
|
echo "Running Python Code trigger...\n";
|
|
$pyCode = generatePythonTriggerCode($functionName, $activeTable['onUpdate_pyCode'], $row);
|
|
$result = runSandboxedPython($pyCode,$stdout,$stderr);
|
|
|
|
handleTriggerExecutionResult($result, $stdout, $stderr, $row, $activeTable);
|
|
}
|
|
|
|
/**
|
|
* Executes database-level dynamic JavaScript trigger code.
|
|
*/
|
|
function runJavascriptCodeTrigger($functionName, $activeTable, $row) {
|
|
echo "Running JavaScript Code trigger...\n";
|
|
$jsCode = generateJSTriggerCode($functionName, $activeTable['onUpdate_jsCode'], $row);
|
|
$result = runSandboxedJavascript($jsCode, $stdout, $stderr);
|
|
|
|
handleTriggerExecutionResult($result, $stdout, $stderr, $row, $activeTable);
|
|
}
|
|
|
|
/**
|
|
* Helper functions implementations.
|
|
*/
|
|
|
|
function updateDatabaseRow($tableName, $originalRow, $newRowValue) {
|
|
if (empty($originalRow) || empty($newRowValue)) return;
|
|
$pkColsName = getTblPrimaryKeyColName($tableName);
|
|
if (empty($pkColsName)) { echo redText("ERROR: No PRIMARY KEY found for table: $tableName\n"); return; }
|
|
$pkColsValues = array_map(fn($cName) => $originalRow[$cName], $pkColsName);
|
|
|
|
$setStatements = [];
|
|
foreach ($newRowValue as $k => $v) {
|
|
|
|
// Skip unchanged values (strict for scalars; JSON-compare for arrays)
|
|
if (array_key_exists($k, $originalRow)) {
|
|
$old = $originalRow[$k];
|
|
$equal = (is_array($v) || is_array($old))
|
|
? json_encode($v) === json_encode($old)
|
|
: $v === $old;
|
|
if ($equal) continue;
|
|
}
|
|
|
|
// Special case: columns ending in `_VEC` -> VEC_FromText('<json>')
|
|
if (function_exists('str_ends_with') ? str_ends_with($k, '_VEC') : preg_match('/_VEC$/', $k)) {
|
|
// Accept arrays/objects or already-JSON strings
|
|
$jsonText = is_string($v) ? $v : json_encode($v, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
|
|
$jsonText = str_replace("'", "''", $jsonText);
|
|
$value = "VEC_FromText('{$jsonText}')";
|
|
} else {
|
|
// Default quoting/encoding
|
|
$value = is_numeric($v)
|
|
? $v
|
|
: "'" . str_replace("'", "''", is_string($v) ? $v : json_encode($v)) . "'";
|
|
}
|
|
|
|
$setStatements[] = "$k = $value";
|
|
}
|
|
|
|
// Nothing changed -> no UPDATE
|
|
if (empty($setStatements)) return;
|
|
|
|
$whereStatements = [];
|
|
foreach ($pkColsName as $k) {
|
|
$val = is_numeric($originalRow[$k]) ? $originalRow[$k] : "'" . $originalRow[$k] . "'";
|
|
$whereStatements[] = "$k = $val";
|
|
}
|
|
|
|
$sql_instruction = "UPDATE $tableName SET " . implode(", ", $setStatements) . " WHERE " . implode(" AND ", $whereStatements);
|
|
try { sql($sql_instruction); }
|
|
catch (Exception $e) {
|
|
echo redText("ERROR: while trying $sql_instruction\n\n"); print_r($e->getMessage());
|
|
}
|
|
}
|
|
|
|
function updateTriggerError($tableName, $error) {
|
|
sql("UPDATE SYS_PRD_BND.Tables SET LastError = '" . str_replace("'", '"', $error) . "', LastUpdated = NOW() WHERE Name = '$tableName'");
|
|
sendTelegramMessage("*ERROR* on trigger function for table $tableName : " . $error, "DatabaseGroup");
|
|
}
|
|
|
|
function clearTriggerError($tableName) {
|
|
sql("UPDATE SYS_PRD_BND.Tables SET LastError = '', LastUpdated = NOW() WHERE Name = '$tableName'");
|
|
}
|
|
function updateCronError($cronJobId, $error) {
|
|
// Sanitize single quotes to prevent SQL breakage
|
|
$safeError = str_replace("'", '"', $error);
|
|
|
|
// Update the CronJobs table with the error
|
|
sql("UPDATE SYS_PRD_BND.CronJobs
|
|
SET LastError = '$safeError',
|
|
LastUpdated = NOW()
|
|
WHERE id = '$cronJobId'"); // Ensure 'id' matches your actual primary key column name
|
|
|
|
// Send the alert
|
|
sendTelegramMessage("*ERROR* on Cron Job #$cronJobId : " . $error, "DatabaseGroup");
|
|
}
|
|
|
|
function clearCronError($cronJobId) {
|
|
// Clear the error field
|
|
sql("UPDATE SYS_PRD_BND.CronJobs
|
|
SET LastError = '',
|
|
LastUpdated = NOW()
|
|
WHERE id = '$cronJobId'");
|
|
}
|
|
|
|
|
|
function getConstantsDefinition() {
|
|
$constants = "";
|
|
foreach (sql("SELECT Name, Type, Value FROM SYS_PRD_BND.Constants") as $const) {
|
|
$value = $const["Type"] != "String" ? $const["Value"] : '"' . $const["Value"] . '"';
|
|
$constants .= "define(\"{$const['Name']}\", $value);\n";
|
|
}
|
|
return $constants;
|
|
}
|
|
|
|
function getPythonImports() {
|
|
$imports = "";
|
|
foreach (sql("SELECT LibName, AliasName FROM SYS_PRD_BND.PyPi") as $module) {
|
|
$alias = !empty($module["AliasName"]) ? " as {$module['AliasName']}" : "";
|
|
$imports .= "import {$module['LibName']}{$alias}\n";
|
|
}
|
|
return $imports;
|
|
}
|
|
|
|
function getNodeRequires() {
|
|
$requires = "";
|
|
foreach (sql("SELECT PackageName, AliasName FROM SYS_PRD_BND.Npm") as $module) {
|
|
$alias = !empty($module['AliasName']) ? $module['AliasName'] : basename($module['PackageName']);
|
|
$alias = preg_replace('/[^A-Za-z0-9_]/', '_', $alias);
|
|
$requires .= "const $alias = require('{$module['PackageName']}');\n";
|
|
}
|
|
return $requires;
|
|
}
|
|
|
|
function getJSConstantsDefinition() {
|
|
$constants = "";
|
|
foreach (sql("SELECT Name, Type, Value FROM SYS_PRD_BND.Constants") as $const) {
|
|
switch ($const['Type']) {
|
|
case 'String':
|
|
$val = '`' . str_replace('`', '\\`', $const['Value']) . '`';
|
|
break;
|
|
case 'Json':
|
|
$val = json_encode(json_decode($const['Value'], true));
|
|
break;
|
|
default:
|
|
$val = $const['Value'];
|
|
}
|
|
$constants .= "const {$const['Name']} = {$val};\n";
|
|
}
|
|
return $constants;
|
|
}
|
|
|
|
function getJavascriptSupportFunctionsDefinition() {
|
|
$functions = "";
|
|
foreach (sql("SELECT Name, InputArgs_json, JavascriptCode FROM SYS_PRD_BND.SupportFunctions WHERE JavascriptCode IS NOT NULL") as $f) {
|
|
$args = implode(', ', array_keys(json_decode($f['InputArgs_json'], true)));
|
|
$functions .= "function {$f['Name']}($args) {\n{$f['JavascriptCode']}\n}\n";
|
|
}
|
|
return $functions;
|
|
}
|
|
function greenText($string) { $green = "\033[0;32m"; $reset = "\033[0m"; return $green . $string . $reset ; }
|
|
function blueText($string) { $green = "\033[0;34m"; $reset = "\033[0m"; return $green . $string . $reset ; }
|
|
function redText($string) { $green = "\033[0;31m"; $reset = "\033[0m"; return $green . $string . $reset ; }
|
|
function sendTelegramMessage($message, $dstUsers) {
|
|
global $BOT_TOKEN, $CHAT_IDS;
|
|
|
|
if (is_string($dstUsers)) $dstUsers = [$dstUsers];
|
|
|
|
foreach($dstUsers as $dstUser) {
|
|
if (!isset($CHAT_IDS[$dstUser]))
|
|
continue;
|
|
else
|
|
$CHAT_ID = $CHAT_IDS[$dstUser];
|
|
|
|
|
|
$JSON_RAW_DATA = json_encode([
|
|
'chat_id' => $CHAT_ID,
|
|
'text' => $message,
|
|
'parse_mode' => 'markdown'
|
|
]);
|
|
|
|
$curl = curl_init();
|
|
curl_setopt($curl, CURLOPT_URL, "https://api.telegram.org/bot$BOT_TOKEN/sendMessage");
|
|
curl_setopt($curl, CURLOPT_HTTPHEADER, array('Content-Type: application/json'));
|
|
curl_setopt($curl, CURLOPT_POST, 1);
|
|
curl_setopt($curl, CURLOPT_POSTFIELDS, $JSON_RAW_DATA);
|
|
curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
|
|
|
|
$response = curl_exec($curl);
|
|
//print_r($response);
|
|
curl_close($curl);
|
|
}
|
|
}
|