← All changes | database/Migrations/ConversationsMigrator.php +48 -3 1.5.2 → 2.4.5 View file →

		@@ -12,9 +12,9 @@
12	12
13	13	$charsetCollate = $wpdb->get_charset_collate();
14	14	$table = $wpdb->prefix . static::$tableName;
15	15
16		- if ($wpdb->get_var("SHOW TABLES LIKE '$table'") != $table) {
	16	+ if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table)) != $table) {
17	17	$sql = "CREATE TABLE $table (
18	18	`id` BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
19	19	`serial` INT(11) UNSIGNED DEFAULT 1,
20	20	`ticket_id` BIGINT(20) UNSIGNED NOT NULL,
		@@ -25,10 +25,55 @@
25	25	`content_hash` VARCHAR(192) NULL,
26	26	`message_id` VARCHAR(192) NULL,
27	27	`is_important` ENUM('yes', 'no') DEFAULT 'no',
28	28	`created_at` TIMESTAMP NULL,
29		- `updated_at` TIMESTAMP NULL
	29	+ `updated_at` TIMESTAMP NULL,
	30	+ INDEX `idx_ticket_id` (`ticket_id`),
	31	+ INDEX `idx_person_id` (`person_id`),
	32	+ INDEX `idx_created_at` (`created_at`),
	33	+ INDEX `idx_person_id_created_at` (`person_id`, `created_at`)
30	34	) $charsetCollate;";
31		- dbDelta($sql);
	35	+ $created = dbDelta($sql);
	36	+ return $created;
	37	+ } else {
	38	+ static::alterTable($table);
	39	+ }
	40	+
	41	+ return false;
	42	+ }
	43	+
	44	+ public static function alterTable($table)
	45	+ {
	46	+ static::addMissingIndexes($table);
	47	+ }
	48	+
	49	+ public static function addMissingIndexes($table)
	50	+ {
	51	+ global $wpdb;
	52	+
	53	+ // Our own table name, not user input. esc_sql() because %i needs WP 6.2+.
	54	+ $table = esc_sql($table);
	55	+
	56	+ $existing_indexes = $wpdb->get_results("SHOW INDEX FROM `{$table}`"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	57	+ $existing_index_names = [];
	58	+
	59	+ foreach ($existing_indexes as $index) {
	60	+ $existing_index_names[] = $index->Key_name;
	61	+ }
	62	+
	63	+ // idx_person_id_created_at serves Advanced Reports' agent + date range queries.
	64	+ // Without it MySQL scans the agent's whole history via idx_person_id.
	65	+ $indexes = [
	66	+ 'idx_ticket_id' => '`ticket_id`',
	67	+ 'idx_person_id' => '`person_id`',
	68	+ 'idx_created_at' => '`created_at`',
	69	+ 'idx_person_id_created_at' => '`person_id`, `created_at`',
	70	+ ];
	71	+
	72	+ foreach ($indexes as $index_name => $columns) {
	73	+ if (!in_array($index_name, $existing_index_names)) {
	74	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- all identifiers are either esc_sql()'d or hardcoded literals.
	75	+ $wpdb->query("ALTER TABLE `{$table}` ADD INDEX `{$index_name}` ({$columns})");
	76	+ }
32	77	}
33	78	}
34	79	}