Migration_20230831_143755_Db.php — MailPoet – Newsletters, Email Marketing, and Automation 5.36.0

mailpoet / lib / Migrations / Db / Migration_20230831_143755_Db.php

mailpoet / lib / Migrations / Db Last commit date

Migration_20230831_143755_Db.php

151 lines

1	<?php declare(strict_types = 1);
2
3	namespace MailPoet\Migrations\Db;
4
5	if (!defined('ABSPATH')) exit;
6
7
8	use MailPoet\Doctrine\WPDB\Connection;
9	use MailPoet\Migrator\DbMigration;
10
11	class Migration_20230831_143755_Db extends DbMigration {
12	public function run(): void {
13	global $wpdb;
14	$logsTable = $wpdb->prefix . 'mailpoet_automation_run_logs';
15
16	// add "updated_at" column
17	if (!$this->columnExists($logsTable, 'updated_at')) {
18	$this->connection->executeStatement("ALTER TABLE $logsTable CHANGE `started_at` `started_at` timestamp NULL"); // prevent ER_TOO_MUCH_AUTO_TIMESTAMP_COLS
19	$this->connection->executeStatement("ALTER TABLE $logsTable ADD COLUMN updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `started_at`");
20	}
21
22	// add "step_type" column
23	if (!$this->columnExists($logsTable, 'step_type')) {
24	$this->connection->executeStatement("ALTER TABLE $logsTable ADD COLUMN step_type VARCHAR(255) NOT NULL DEFAULT 'action' AFTER `step_id`");
25
26	// Temporarily use a full column definition to drop default in WP Playground.
27	// DROP DEFAULT is not yet supported by the SQLite integration.
28	if (Connection::isSQLite()) {
29	$this->connection->executeStatement("ALTER TABLE $logsTable CHANGE COLUMN step_type step_type VARCHAR(255) NOT NULL");
30	} else {
31	$this->connection->executeStatement("ALTER TABLE $logsTable ALTER COLUMN step_type DROP DEFAULT");
32	}
33	}
34
35	// add "step_key" column
36	if (!$this->columnExists($logsTable, 'step_key')) {
37	$this->connection->executeStatement("ALTER TABLE $logsTable ADD COLUMN step_key VARCHAR(255) NOT NULL DEFAULT '' AFTER `step_type`");
38
39	// Temporarily use a full column definition to drop default in WP Playground.
40	// DROP DEFAULT is not yet supported by the SQLite integration.
41	if (Connection::isSQLite()) {
42	$this->connection->executeStatement("ALTER TABLE $logsTable CHANGE COLUMN step_key step_key VARCHAR(255) NOT NULL");
43	} else {
44	$this->connection->executeStatement("ALTER TABLE $logsTable ALTER COLUMN step_key DROP DEFAULT");
45	}
46	}
47
48	// add "run_number" column
49	if (!$this->columnExists($logsTable, 'run_number')) {
50	$this->connection->executeStatement("ALTER TABLE $logsTable ADD COLUMN run_number INT NOT NULL DEFAULT 1 AFTER `updated_at`");
51
52	// Temporarily use a full column definition to drop default in WP Playground.
53	// DROP DEFAULT is not yet supported by the SQLite integration.
54	if (Connection::isSQLite()) {
55	$this->connection->executeStatement("ALTER TABLE $logsTable CHANGE COLUMN run_number run_number INT NOT NULL");
56	} else {
57	$this->connection->executeStatement("ALTER TABLE $logsTable ALTER COLUMN run_number DROP DEFAULT");
58	}
59	}
60
61	// go through automation data and backfill step keys and trigger logs
62	$this->backfillStepKeysAndTriggers();
63
64	// fix mix of 'complete' and 'completed' statuses
65	$this->connection->executeStatement("UPDATE $logsTable SET status = 'complete' WHERE status = 'completed'");
66
67	// fix empty values for errors and data
68	$this->connection->executeStatement("ALTER TABLE $logsTable CHANGE `data` `data` longtext NOT NULL AFTER run_number");
69	$this->connection->executeStatement("UPDATE $logsTable SET data = '{}' WHERE data = '[]' OR data IS NULL");
70	$this->connection->executeStatement("UPDATE $logsTable SET error = NULL WHERE error = '[]' OR error IS NULL");
71
72	// remove "completed_at" column (with "updated_at" it's no longer needed), backfill "updated_at"
73	if ($this->columnExists($logsTable, 'completed_at')) {
74	$this->connection->executeStatement("UPDATE $logsTable SET updated_at = COALESCE(completed_at, started_at)");
75	$this->connection->executeStatement("ALTER TABLE $logsTable DROP COLUMN completed_at");
76	}
77
78	// add unique index, remove no longer needed index
79	if (!$this->indexExists($logsTable, 'automation_run_id_step_id')) {
80	$this->connection->executeStatement(
81	"DELETE t1 FROM $logsTable as t1, $logsTable as t2 WHERE t1.id < t2.id AND t1.automation_run_id = t2.automation_run_id AND t1.step_id=t2.step_id"
82	);
83	$this->connection->executeStatement("ALTER TABLE $logsTable ADD UNIQUE automation_run_id_step_id (automation_run_id, step_id)");
84	}
85	if ($this->indexExists($logsTable, 'automation_run_id')) {
86	$this->connection->executeStatement("ALTER TABLE $logsTable DROP INDEX automation_run_id");
87	}
88	}
89
90	private function backfillStepKeysAndTriggers(): void {
91	global $wpdb;
92	$logsTable = $wpdb->prefix . 'mailpoet_automation_run_logs';
93	$runsTable = $wpdb->prefix . 'mailpoet_automation_runs';
94	$versionsTable = $wpdb->prefix . 'mailpoet_automation_versions';
95
96	$triggerAddedMap = [];
97	while (true) {
98	$data = $this->connection->executeQuery("
99	SELECT rl.id, rl.automation_run_id, rl.step_id, rl.started_at, v.steps
100	FROM {$logsTable} rl
101	JOIN {$runsTable} r ON r.id = rl.automation_run_id
102	JOIN {$versionsTable} v ON v.id = r.version_id
103	WHERE rl.step_key = ''
104	ORDER BY rl.id ASC
105	LIMIT 50
106	")->fetchAllAssociative();
107
108	if (count($data) === 0) {
109	break;
110	}
111
112	$queries = [];
113	/** @var array<int, array{id:int, automation_run_id:int, step_id:int, started_at:string, steps:string}> $data */
114	foreach ($data as $item) {
115	/** @var array $steps */
116	$steps = json_decode(strval($item['steps']), true);
117	$id = intval($item['id']);
118	$stepId = strval($item['step_id']);
119	$stepKey = strval($steps[$stepId]['key'] ?? 'unknown');
120	$triggerId = $steps['root']['next_steps'][0]['id'];
121	$triggerKey = $steps['root']['next_steps'][0]['key'] ?? 'unknown';
122
123	$queries[] = "UPDATE {$logsTable} SET step_key = '{$stepKey}' WHERE id = {$id}";
124
125	// backfill triggers
126	$runId = intval($item['automation_run_id']);
127	if (!isset($triggerAddedMap[$runId])) {
128	$startedAt = strval($item['started_at']);
129	$date = "DATE_SUB('$startedAt', INTERVAL 1 SECOND)";
130	$queries[] = "
131	INSERT INTO {$logsTable} (automation_run_id, step_id, step_type, step_key, status, started_at, updated_at, run_number, data)
132	VALUES ($runId, '$triggerId', 'trigger', '$triggerKey', 'complete', $date, $date, 1, '{}')
133	";
134	$triggerAddedMap[$runId] = true;
135	}
136	}
137
138	$nativeConnection = $this->connection->getNativeConnection();
139	// If the connection is a mysqli object, we can use the multi_query method
140	if (is_object($nativeConnection) && get_class($nativeConnection) === 'mysqli') {
141	$query = implode(';', $queries);
142	$nativeConnection->multi_query($query);
143	} else { // Otherwise, we need to execute each query individually (e.g. PDO or SQLite)
144	foreach ($queries as $query) {
145	$this->connection->executeStatement($query);
146	}
147	}
148	}
149	}
150	}
151