← All changes | database/Migrations/TicketsMigrator.php +13 -18 2.2.0 → 2.4.5 View file →

		@@ -51,20 +51,23 @@
51	51	INDEX `idx_agent_id` (`agent_id`),
52	52	INDEX `idx_mailbox_id` (`mailbox_id`),
53	53	INDEX `idx_product_id` (`product_id`),
54	54	INDEX `idx_priority` (`priority`),
	55	+ INDEX `idx_client_priority` (`client_priority`),
55	56	INDEX `idx_status` (`status`),
56	57	INDEX `idx_created_at` (`created_at`),
57	58	INDEX `idx_resolved_at` (`resolved_at`),
58	59	INDEX `idx_status_resolved_at` (`status`, `resolved_at`),
59	60	INDEX `idx_ticket_number` (`ticket_number`(191)),
	61	+ INDEX `idx_waiting_since_id` (`waiting_since`, `id`),
	62	+ INDEX `idx_updated_at_id` (`updated_at`, `id`),
	63	+ INDEX `idx_response_count_id` (`response_count`, `id`),
60	64	UNIQUE KEY `uniq_serial_number` (`serial_number`)
61	65	) $charsetCollate;";
62	66	$created = dbDelta($sql);
63	67	return $created;
64	68	} else {
65		- static::alterTable($table);
66		- }
	69	+ static::alterTable($table); }
67	70
68	71	return false;
69	72	}
70	73
		@@ -77,23 +80,19 @@
77	80	public static function addMissingColumns($table)
78	81	{
79	82	global $wpdb;
80	83
81		- // $table is always $wpdb->prefix . 'fs_tickets' — not user input.
82		- // esc_sql() is the correct escaping for SQL identifiers; $wpdb->prepare()
83		- // cannot quote identifiers in WP < 6.2 (no %i placeholder available).
	84	+ // Our own table name, not user input. esc_sql() because %i needs WP 6.2+.
84	85	$table = esc_sql($table);
85	86
86		- // Get existing columns
87	87	$existing_columns = $wpdb->get_col("DESC `{$table}`", 0); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
88	88
89		- // Add waiting_since column if missing (beta user migration)
90	89	if (!in_array('waiting_since', $existing_columns)) {
91	90	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
92	91	$wpdb->query("ALTER TABLE `{$table}` ADD `waiting_since` TIMESTAMP NULL AFTER `last_customer_response`");
93	92	}
94	93
95		- // Add created_by column to track agent who created ticket on behalf of customer
	94	+ // Agent who created the ticket on behalf of the customer.
96	95	if (!in_array('created_by', $existing_columns)) {
97	96	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
98	97	$wpdb->query("ALTER TABLE `{$table}` ADD `created_by` BIGINT(20) UNSIGNED NULL AFTER `closed_by`");
99	98	}
		@@ -116,14 +115,11 @@
116	115	public static function addMissingIndexes($table)
117	116	{
118	117	global $wpdb;
119	118
120		- // $table is always $wpdb->prefix . 'fs_tickets' — not user input.
121		- // esc_sql() is the correct escaping for SQL identifiers; $wpdb->prepare()
122		- // cannot quote identifiers in WP < 6.2 (no %i placeholder available).
	119	+ // Our own table name, not user input. esc_sql() because %i needs WP 6.2+.
123	120	$table = esc_sql($table);
124	121
125		- // Get existing indexes
126	122	$existing_indexes = $wpdb->get_results("SHOW INDEX FROM `{$table}`"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
127	123	$existing_index_names = [];
128	124
129	125	foreach ($existing_indexes as $index) {
		@@ -129,10 +125,8 @@
129	125	foreach ($existing_indexes as $index) {
130	126	$existing_index_names[] = $index->Key_name;
131	127	}
132	128
133		- // Desired indexes — keys and values (including composite column lists) are
134		- // all hardcoded string literals; no user input reaches these queries.
135	129	$indexes = [
136	130	'idx_customer_id' => '`customer_id`',
137	131	'idx_agent_id' => '`agent_id`',
138	132	'idx_mailbox_id' => '`mailbox_id`',
		@@ -137,8 +131,9 @@
137	131	'idx_agent_id' => '`agent_id`',
138	132	'idx_mailbox_id' => '`mailbox_id`',
139	133	'idx_product_id' => '`product_id`',
140	134	'idx_priority' => '`priority`',
	135	+ 'idx_client_priority' => '`client_priority`',
141	136	'idx_status' => '`status`',
142	137	'idx_created_at' => '`created_at`',
143	138	'idx_resolved_at' => '`resolved_at`',
144	139	'idx_status_resolved_at' => '`status`, `resolved_at`',
		@@ -143,16 +138,16 @@
143	138	'idx_resolved_at' => '`resolved_at`',
144	139	'idx_status_resolved_at' => '`status`, `resolved_at`',
145	140	'idx_ticket_number' => '`ticket_number`(191)',
146	141	'uniq_serial_number' => '`serial_number`',
	142	+ 'idx_waiting_since_id' => '`waiting_since`, `id`',
	143	+ 'idx_updated_at_id' => '`updated_at`, `id`',
	144	+ 'idx_response_count_id' => '`response_count`, `id`',
147	145	];
148		-
149		- // Add missing indexes. $table is esc_sql()'d above; $index_name and
150		- // $columns are hardcoded array literals — no user input reaches this query.
151	146	foreach ($indexes as $index_name => $columns) {
152	147	if (!in_array($index_name, $existing_index_names)) {
	148	+ $indexType = $index_name === 'uniq_serial_number' ? 'UNIQUE KEY' : 'INDEX';
153	149	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- all identifiers are either esc_sql()'d or hardcoded literals.
154		- $indexType = $index_name === 'uniq_serial_number' ? 'UNIQUE KEY' : 'INDEX';
155	150	$wpdb->query("ALTER TABLE `{$table}` ADD {$indexType} `{$index_name}` ({$columns})");
156	151	}
157	152	}
158	153	}