AutomationRunLogStorage.php
1 year ago
AutomationRunStorage.php
2 months ago
AutomationStatisticsStorage.php
1 month ago
AutomationStorage.php
2 months ago
index.php
3 years ago
AutomationStorage.php
531 lines
| 1 | <?php declare(strict_types = 1); |
| 2 | |
| 3 | namespace MailPoet\Automation\Engine\Storage; |
| 4 | |
| 5 | if (!defined('ABSPATH')) exit; |
| 6 | |
| 7 | |
| 8 | use DateTimeImmutable; |
| 9 | use MailPoet\Automation\Engine\Data\Automation; |
| 10 | use MailPoet\Automation\Engine\Data\Step; |
| 11 | use MailPoet\Automation\Engine\Data\Subject; |
| 12 | use MailPoet\Automation\Engine\Exceptions; |
| 13 | use MailPoet\Automation\Engine\Integration\Trigger; |
| 14 | |
| 15 | /** |
| 16 | * @phpstan-type VersionDate array{id: int, created_at: \DateTimeImmutable} |
| 17 | */ |
| 18 | class AutomationStorage { |
| 19 | /** @var string */ |
| 20 | private $automationsTable; |
| 21 | |
| 22 | /** @var string */ |
| 23 | private $versionsTable; |
| 24 | |
| 25 | /** @var string */ |
| 26 | private $triggersTable; |
| 27 | |
| 28 | /** @var string */ |
| 29 | private $runsTable; |
| 30 | |
| 31 | /** @var string */ |
| 32 | private $subjectsTable; |
| 33 | |
| 34 | public function __construct() { |
| 35 | global $wpdb; |
| 36 | $this->automationsTable = $wpdb->prefix . 'mailpoet_automations'; |
| 37 | $this->versionsTable = $wpdb->prefix . 'mailpoet_automation_versions'; |
| 38 | $this->triggersTable = $wpdb->prefix . 'mailpoet_automation_triggers'; |
| 39 | $this->runsTable = $wpdb->prefix . 'mailpoet_automation_runs'; |
| 40 | $this->subjectsTable = $wpdb->prefix . 'mailpoet_automation_run_subjects'; |
| 41 | } |
| 42 | |
| 43 | public function createAutomation(Automation $automation): int { |
| 44 | global $wpdb; |
| 45 | $automationHeaderData = $this->getAutomationHeaderData($automation); |
| 46 | unset($automationHeaderData['id']); |
| 47 | $result = $wpdb->insert($this->automationsTable, $automationHeaderData); |
| 48 | if (!$result) { |
| 49 | $this->throwDatabaseError(); |
| 50 | } |
| 51 | $id = $wpdb->insert_id; // phpcs:ignore Squiz.NamingConventions.ValidVariableName.MemberNotCamelCaps |
| 52 | $this->insertAutomationVersion($id, $automation); |
| 53 | $this->insertAutomationTriggers($id, $automation); |
| 54 | return $id; |
| 55 | } |
| 56 | |
| 57 | public function updateAutomation(Automation $automation): void { |
| 58 | global $wpdb; |
| 59 | $oldRecord = $this->getAutomation($automation->getId()); |
| 60 | if ($oldRecord && $oldRecord->equals($automation)) { |
| 61 | return; |
| 62 | } |
| 63 | $result = $wpdb->update($this->automationsTable, $this->getAutomationHeaderData($automation), ['id' => $automation->getId()]); |
| 64 | if ($result === false) { |
| 65 | $this->throwDatabaseError(); |
| 66 | } |
| 67 | $this->insertAutomationVersion($automation->getId(), $automation); |
| 68 | $this->insertAutomationTriggers($automation->getId(), $automation); |
| 69 | } |
| 70 | |
| 71 | public function getAutomationVersionDates(int $automationId): array { |
| 72 | global $wpdb; |
| 73 | |
| 74 | $data = $wpdb->get_results( |
| 75 | $wpdb->prepare( |
| 76 | 'SELECT id, created_at FROM %i WHERE automation_id = %d ORDER BY id DESC', |
| 77 | $this->versionsTable, |
| 78 | $automationId |
| 79 | ), |
| 80 | ARRAY_A |
| 81 | ); |
| 82 | |
| 83 | return is_array($data) ? array_map( |
| 84 | function($row): array { |
| 85 | /** @var array{id: string, created_at: string} $row */ |
| 86 | return [ |
| 87 | 'id' => absint($row['id']), |
| 88 | 'created_at' => new \DateTimeImmutable($row['created_at']), |
| 89 | ]; |
| 90 | }, |
| 91 | $data |
| 92 | ) : []; |
| 93 | } |
| 94 | |
| 95 | /** |
| 96 | * @param int[] $versionIds |
| 97 | * @return Automation[] |
| 98 | */ |
| 99 | public function getAutomationWithDifferentVersions(array $versionIds): array { |
| 100 | global $wpdb; |
| 101 | if (!$versionIds) { |
| 102 | return []; |
| 103 | } |
| 104 | |
| 105 | $versionIds = array_map('intval', $versionIds); |
| 106 | $data = $wpdb->get_results( |
| 107 | $wpdb->prepare( |
| 108 | ' |
| 109 | SELECT a.*, v.id AS version_id, v.steps |
| 110 | FROM %i as a, %i as v |
| 111 | WHERE v.automation_id = a.id AND v.id IN (' . implode(',', array_fill(0, count($versionIds), '%d')) . ') |
| 112 | ORDER BY v.id DESC |
| 113 | ', |
| 114 | array_merge( |
| 115 | [ |
| 116 | $this->automationsTable, |
| 117 | $this->versionsTable, |
| 118 | ], |
| 119 | $versionIds |
| 120 | ) |
| 121 | ), |
| 122 | ARRAY_A |
| 123 | ); |
| 124 | return is_array($data) ? array_map( |
| 125 | function($row): Automation { |
| 126 | return Automation::fromArray((array)$row); |
| 127 | }, |
| 128 | $data |
| 129 | ) : []; |
| 130 | } |
| 131 | |
| 132 | public function getAutomation(int $automationId, ?int $versionId = null): ?Automation { |
| 133 | global $wpdb; |
| 134 | |
| 135 | if ($versionId) { |
| 136 | $automations = $this->getAutomationWithDifferentVersions([$versionId]); |
| 137 | return $automations ? $automations[0] : null; |
| 138 | } |
| 139 | |
| 140 | $data = $wpdb->get_row( |
| 141 | $wpdb->prepare( |
| 142 | ' |
| 143 | SELECT a.*, v.id AS version_id, v.steps |
| 144 | FROM %i as a, %i as v |
| 145 | WHERE v.automation_id = a.id AND a.id = %d |
| 146 | ORDER BY v.id DESC |
| 147 | LIMIT 1 |
| 148 | ', |
| 149 | $this->automationsTable, |
| 150 | $this->versionsTable, |
| 151 | $automationId |
| 152 | ), |
| 153 | ARRAY_A |
| 154 | ); |
| 155 | return $data ? Automation::fromArray((array)$data) : null; |
| 156 | } |
| 157 | |
| 158 | /** @return Automation[] */ |
| 159 | public function getAutomations( |
| 160 | ?array $status = null, |
| 161 | ?string $orderBy = 'id', |
| 162 | ?string $order = 'DESC', |
| 163 | ?int $page = null, |
| 164 | ?int $perPage = null, |
| 165 | ?string $search = null |
| 166 | ): array { |
| 167 | global $wpdb; |
| 168 | |
| 169 | $statusFilter = ''; |
| 170 | $statusParams = []; |
| 171 | if ($status) { |
| 172 | $statusFilter = ' AND a.status IN (' . implode(',', array_fill(0, count($status), '%s')) . ')'; |
| 173 | $statusParams = $status; |
| 174 | } |
| 175 | |
| 176 | // Handle search |
| 177 | $searchFilter = ''; |
| 178 | $searchParams = []; |
| 179 | if ($search !== null && trim($search) !== '') { |
| 180 | $searchFilter = ' AND a.name LIKE %s'; |
| 181 | $searchParams[] = '%' . $wpdb->esc_like($search) . '%'; |
| 182 | } |
| 183 | |
| 184 | // Handle ordering |
| 185 | $validOrderByColumns = ['id', 'name', 'status', 'created_at', 'updated_at']; |
| 186 | $orderByColumn = $orderBy && in_array($orderBy, $validOrderByColumns, true) ? $orderBy : 'id'; |
| 187 | $orderDirection = $order && strtoupper($order) === 'ASC' ? 'ASC' : 'DESC'; |
| 188 | $orderClause = " ORDER BY a.{$orderByColumn} {$orderDirection}"; |
| 189 | |
| 190 | // Handle pagination |
| 191 | $limitClause = ''; |
| 192 | if ($page !== null && $perPage !== null && $perPage > 0) { |
| 193 | $currentPage = max(1, $page); |
| 194 | $offset = ($currentPage - 1) * $perPage; |
| 195 | $limitClause = $wpdb->prepare(' LIMIT %d OFFSET %d', $perPage, $offset); |
| 196 | } |
| 197 | |
| 198 | $data = $wpdb->get_results( |
| 199 | // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- The number of replacements is dynamic. |
| 200 | $wpdb->prepare( |
| 201 | ' |
| 202 | SELECT a.*, v.id AS version_id, v.steps |
| 203 | FROM %i AS a |
| 204 | INNER JOIN %i as v ON (v.automation_id = a.id) |
| 205 | WHERE v.id = ( |
| 206 | SELECT MAX(id) FROM %i WHERE automation_id = v.automation_id |
| 207 | ) |
| 208 | ' . $statusFilter . /* phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The status filter uses placeholders. */ ' |
| 209 | ' . $searchFilter . /* phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The search filter uses placeholders. */ ' |
| 210 | ' . $orderClause . /* phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The order clause is sanitized. */ ' |
| 211 | ' . $limitClause, /* phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The limit clause is prepared. */ |
| 212 | array_merge( |
| 213 | [ |
| 214 | $this->automationsTable, |
| 215 | $this->versionsTable, |
| 216 | $this->versionsTable, |
| 217 | ], |
| 218 | $statusParams, |
| 219 | $searchParams |
| 220 | ) |
| 221 | ), |
| 222 | ARRAY_A |
| 223 | ); |
| 224 | return array_map(function ($automationData) { |
| 225 | /** @var array $automationData - for PHPStan because it conflicts with expected callable(mixed): mixed)|null */ |
| 226 | return Automation::fromArray($automationData); |
| 227 | }, (array)$data); |
| 228 | } |
| 229 | |
| 230 | /** @return int[] */ |
| 231 | public function getAutomationIdsBySubject(Subject $subject, ?array $runStatus = null, ?int $inTheLastSeconds = null): array { |
| 232 | global $wpdb; |
| 233 | |
| 234 | $statusFilter = $runStatus ? 'AND r.status IN (' . implode(',', array_fill(0, count($runStatus), '%s')) . ')' : ''; |
| 235 | $inTheLastFilter = isset($inTheLastSeconds) ? 'AND r.created_at > DATE_SUB(NOW(), INTERVAL %d SECOND)' : ''; |
| 236 | |
| 237 | $result = $wpdb->get_col( |
| 238 | // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- The number of replacements is dynamic. |
| 239 | $wpdb->prepare( |
| 240 | ' |
| 241 | SELECT DISTINCT a.id |
| 242 | FROM %i a |
| 243 | INNER JOIN %i r ON r.automation_id = a.id |
| 244 | INNER JOIN %i s ON s.automation_run_id = r.id |
| 245 | WHERE s.hash = %s |
| 246 | ' . $statusFilter . /* phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The condition uses placeholders. */ ' |
| 247 | ' . $inTheLastFilter . /* phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The condition uses placeholders. */ ' |
| 248 | ORDER BY a.id DESC |
| 249 | ', |
| 250 | array_merge( |
| 251 | [ |
| 252 | $this->automationsTable, |
| 253 | $this->runsTable, |
| 254 | $this->subjectsTable, |
| 255 | $subject->getHash(), |
| 256 | ], |
| 257 | $runStatus ?? [], |
| 258 | $inTheLastSeconds ? [$inTheLastSeconds] : [] |
| 259 | ) |
| 260 | ) |
| 261 | ); |
| 262 | return array_map('intval', $result); |
| 263 | } |
| 264 | |
| 265 | public function getAutomationCount(?array $status = null, ?string $search = null): int { |
| 266 | global $wpdb; |
| 267 | |
| 268 | $statusFilter = ''; |
| 269 | $statusParams = []; |
| 270 | if ($status) { |
| 271 | $statusFilter = ' AND a.status IN (' . implode(',', array_fill(0, count($status), '%s')) . ')'; |
| 272 | $statusParams = $status; |
| 273 | } |
| 274 | |
| 275 | $searchFilter = ''; |
| 276 | $searchParams = []; |
| 277 | if ($search !== null && trim($search) !== '') { |
| 278 | $searchFilter = ' AND a.name LIKE %s'; |
| 279 | $searchParams[] = '%' . $wpdb->esc_like($search) . '%'; |
| 280 | } |
| 281 | |
| 282 | return (int)$wpdb->get_var( |
| 283 | $wpdb->prepare( |
| 284 | 'SELECT COUNT(*) FROM %i AS a WHERE 1=1' |
| 285 | . $statusFilter /* phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The status filter uses placeholders. */ |
| 286 | . $searchFilter, /* phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The search filter uses placeholders. */ |
| 287 | array_merge( |
| 288 | [$this->automationsTable], |
| 289 | $statusParams, |
| 290 | $searchParams |
| 291 | ) |
| 292 | ) |
| 293 | ); |
| 294 | } |
| 295 | |
| 296 | /** @return string[] */ |
| 297 | public function getActiveTriggerKeys(): array { |
| 298 | global $wpdb; |
| 299 | return $wpdb->get_col( |
| 300 | $wpdb->prepare( |
| 301 | ' |
| 302 | SELECT DISTINCT t.trigger_key |
| 303 | FROM %i AS a |
| 304 | JOIN %i as t |
| 305 | WHERE a.status = %s AND a.id = t.automation_id |
| 306 | ORDER BY trigger_key DESC |
| 307 | ', |
| 308 | $this->automationsTable, |
| 309 | $this->triggersTable, |
| 310 | Automation::STATUS_ACTIVE |
| 311 | ) |
| 312 | ); |
| 313 | } |
| 314 | |
| 315 | /** @return Automation[] */ |
| 316 | public function getActiveAutomationsByTrigger(Trigger $trigger): array { |
| 317 | return $this->getActiveAutomationsByTriggerKey($trigger->getKey()); |
| 318 | } |
| 319 | |
| 320 | public function getActiveAutomationsByTriggerKey(string $triggerKey): array { |
| 321 | global $wpdb; |
| 322 | $data = $wpdb->get_results( |
| 323 | $wpdb->prepare( |
| 324 | ' |
| 325 | SELECT a.*, v.id AS version_id, v.steps |
| 326 | FROM %i AS a |
| 327 | INNER JOIN %i as t ON (t.automation_id = a.id) |
| 328 | INNER JOIN %i as v ON (v.automation_id = a.id) |
| 329 | WHERE a.status = %s |
| 330 | AND t.trigger_key = %s |
| 331 | AND v.id = ( |
| 332 | SELECT MAX(id) FROM %i WHERE automation_id = v.automation_id |
| 333 | ) |
| 334 | ', |
| 335 | $this->automationsTable, |
| 336 | $this->triggersTable, |
| 337 | $this->versionsTable, |
| 338 | Automation::STATUS_ACTIVE, |
| 339 | $triggerKey, |
| 340 | $this->versionsTable |
| 341 | ), |
| 342 | ARRAY_A |
| 343 | ); |
| 344 | return array_map(function ($automationData) { |
| 345 | /** @var array $automationData - for PHPStan because it conflicts with expected callable(mixed): mixed)|null */ |
| 346 | return Automation::fromArray($automationData); |
| 347 | }, (array)$data); |
| 348 | } |
| 349 | |
| 350 | public function getCountOfActiveByTriggerKeysAndAction(array $triggerKeys, string $actionKey): int { |
| 351 | global $wpdb; |
| 352 | return (int)$wpdb->get_var( |
| 353 | $wpdb->prepare( |
| 354 | ' |
| 355 | SELECT COUNT(*) |
| 356 | FROM %i AS a |
| 357 | INNER JOIN %i as t ON (t.automation_id = a.id) AND t.trigger_key IN (' . implode(',', array_fill(0, count($triggerKeys), '%s')) . ') |
| 358 | INNER JOIN %i as v ON v.id = (SELECT MAX(id) FROM %i WHERE automation_id = a.id) |
| 359 | WHERE a.status = %s |
| 360 | AND v.steps LIKE %s |
| 361 | ', |
| 362 | array_merge( |
| 363 | [ |
| 364 | $this->automationsTable, |
| 365 | $this->triggersTable, |
| 366 | ], |
| 367 | $triggerKeys, |
| 368 | [ |
| 369 | $this->versionsTable, |
| 370 | $this->versionsTable, |
| 371 | Automation::STATUS_ACTIVE, |
| 372 | '%"' . $wpdb->esc_like($actionKey) . '"%', |
| 373 | ] |
| 374 | ) |
| 375 | ) |
| 376 | ); |
| 377 | } |
| 378 | |
| 379 | public function deleteAutomation(Automation $automation): void { |
| 380 | global $wpdb; |
| 381 | $automationId = $automation->getId(); |
| 382 | $logsDeleted = $wpdb->query( |
| 383 | $wpdb->prepare( |
| 384 | ' |
| 385 | DELETE FROM %i |
| 386 | WHERE automation_run_id IN ( |
| 387 | SELECT id |
| 388 | FROM %i |
| 389 | WHERE automation_id = %d |
| 390 | ) |
| 391 | ', |
| 392 | $wpdb->prefix . 'mailpoet_automation_run_logs', |
| 393 | $this->runsTable, |
| 394 | $automationId |
| 395 | ) |
| 396 | ); |
| 397 | if ($logsDeleted === false) { |
| 398 | $this->throwDatabaseError(); |
| 399 | } |
| 400 | |
| 401 | $runsDeleted = $wpdb->delete($this->runsTable, ['automation_id' => $automationId]); |
| 402 | if ($runsDeleted === false) { |
| 403 | $this->throwDatabaseError(); |
| 404 | } |
| 405 | |
| 406 | $versionsDeleted = $wpdb->delete($this->versionsTable, ['automation_id' => $automationId]); |
| 407 | if ($versionsDeleted === false) { |
| 408 | $this->throwDatabaseError(); |
| 409 | } |
| 410 | |
| 411 | $triggersDeleted = $wpdb->delete($this->triggersTable, ['automation_id' => $automationId]); |
| 412 | if ($triggersDeleted === false) { |
| 413 | $this->throwDatabaseError(); |
| 414 | } |
| 415 | |
| 416 | $automationDeleted = $wpdb->delete($this->automationsTable, ['id' => $automationId]); |
| 417 | if ($automationDeleted === false) { |
| 418 | $this->throwDatabaseError(); |
| 419 | } |
| 420 | } |
| 421 | |
| 422 | public function truncate(): void { |
| 423 | global $wpdb; |
| 424 | $result = $wpdb->query($wpdb->prepare('TRUNCATE %i', $this->automationsTable)); |
| 425 | if ($result === false) { |
| 426 | $this->throwDatabaseError(); |
| 427 | } |
| 428 | |
| 429 | $result = $wpdb->query($wpdb->prepare('TRUNCATE %i', $this->versionsTable)); |
| 430 | if ($result === false) { |
| 431 | $this->throwDatabaseError(); |
| 432 | } |
| 433 | |
| 434 | $result = $wpdb->query($wpdb->prepare('TRUNCATE %i', $this->triggersTable)); |
| 435 | if ($result === false) { |
| 436 | $this->throwDatabaseError(); |
| 437 | } |
| 438 | } |
| 439 | |
| 440 | public function getNameColumnLength(): int { |
| 441 | global $wpdb; |
| 442 | $nameColumnLengthInfo = $wpdb->get_col_length($this->automationsTable, 'name'); |
| 443 | if (is_array($nameColumnLengthInfo) && isset($nameColumnLengthInfo['length']) && is_int($nameColumnLengthInfo['length'])) { |
| 444 | return $nameColumnLengthInfo['length']; |
| 445 | } |
| 446 | return 255; |
| 447 | } |
| 448 | |
| 449 | private function getAutomationHeaderData(Automation $automation): array { |
| 450 | $automationHeader = $automation->toArray(); |
| 451 | unset($automationHeader['steps']); |
| 452 | return $automationHeader; |
| 453 | } |
| 454 | |
| 455 | private function insertAutomationVersion(int $automationId, Automation $automation): void { |
| 456 | global $wpdb; |
| 457 | $dateString = (new DateTimeImmutable())->format(DateTimeImmutable::W3C); |
| 458 | $data = [ |
| 459 | 'automation_id' => $automationId, |
| 460 | 'steps' => $automation->toArray()['steps'], |
| 461 | 'created_at' => $dateString, |
| 462 | 'updated_at' => $dateString, |
| 463 | ]; |
| 464 | $result = $wpdb->insert($this->versionsTable, $data); |
| 465 | if (!$result) { |
| 466 | $this->throwDatabaseError(); |
| 467 | } |
| 468 | } |
| 469 | |
| 470 | private function insertAutomationTriggers(int $automationId, Automation $automation): void { |
| 471 | global $wpdb; |
| 472 | $triggerKeys = []; |
| 473 | foreach ($automation->getSteps() as $step) { |
| 474 | if ($step->getType() === Step::TYPE_TRIGGER) { |
| 475 | $triggerKeys[] = $step->getKey(); |
| 476 | } |
| 477 | } |
| 478 | |
| 479 | // insert/update |
| 480 | if ($triggerKeys) { |
| 481 | $result = $wpdb->query( |
| 482 | // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- The number of replacements is dynamic. |
| 483 | $wpdb->prepare( |
| 484 | 'INSERT IGNORE INTO %i (automation_id, trigger_key) VALUES ' . implode(',', array_fill(0, count($triggerKeys), '(%d, %s)')), |
| 485 | array_merge( |
| 486 | [$this->triggersTable], |
| 487 | ...array_map(function (string $key) use ($automationId) { |
| 488 | return [$automationId, $key]; |
| 489 | }, $triggerKeys) |
| 490 | ) |
| 491 | ) |
| 492 | ); |
| 493 | if ($result === false) { |
| 494 | $this->throwDatabaseError(); |
| 495 | } |
| 496 | } |
| 497 | |
| 498 | // delete |
| 499 | if ($triggerKeys) { |
| 500 | $result = $wpdb->query( |
| 501 | $wpdb->prepare( |
| 502 | 'DELETE FROM %i WHERE automation_id = %d AND trigger_key NOT IN (' . implode(',', array_fill(0, count($triggerKeys), '%s')) . ')', |
| 503 | array_merge( |
| 504 | [ |
| 505 | $this->triggersTable, |
| 506 | $automationId, |
| 507 | ], |
| 508 | $triggerKeys |
| 509 | ), |
| 510 | ) |
| 511 | ); |
| 512 | } else { |
| 513 | $result = $wpdb->query( |
| 514 | $wpdb->prepare( |
| 515 | 'DELETE FROM %i WHERE automation_id = %d', |
| 516 | $this->triggersTable, |
| 517 | $automationId |
| 518 | ) |
| 519 | ); |
| 520 | } |
| 521 | if ($result === false) { |
| 522 | $this->throwDatabaseError(); |
| 523 | } |
| 524 | } |
| 525 | |
| 526 | private function throwDatabaseError(): void { |
| 527 | global $wpdb; |
| 528 | throw Exceptions::databaseError($wpdb->last_error); // phpcs:ignore Squiz.NamingConventions.ValidVariableName.MemberNotCamelCaps |
| 529 | } |
| 530 | } |
| 531 |