← All changes | database/Migrations/TicketsMigrator.php +15 -47 trunk → 2.1.0 View file →

		@@ -42,10 +42,8 @@
42	42	`total_close_time` INT(11) NULL, /* Seconds took for closing this ticket */
43	43	`resolved_at` TIMESTAMP NULL,
44	44	`closed_by` BIGINT(20) UNSIGNED NULL,
45	45	`created_by` BIGINT(20) UNSIGNED NULL,
46		- `serial_number` BIGINT UNSIGNED NULL,
47		- `ticket_number` VARCHAR(192) NULL DEFAULT NULL,
48	46	`created_at` TIMESTAMP NULL,
49	47	`updated_at` TIMESTAMP NULL,
50	48	INDEX `idx_customer_id` (`customer_id`),
51	49	INDEX `idx_agent_id` (`agent_id`),
		@@ -51,23 +49,16 @@
51	49	INDEX `idx_agent_id` (`agent_id`),
52	50	INDEX `idx_mailbox_id` (`mailbox_id`),
53	51	INDEX `idx_product_id` (`product_id`),
54	52	INDEX `idx_priority` (`priority`),
55		- INDEX `idx_client_priority` (`client_priority`),
56	53	INDEX `idx_status` (`status`),
57		- INDEX `idx_created_at` (`created_at`),
58		- INDEX `idx_resolved_at` (`resolved_at`),
59		- INDEX `idx_status_resolved_at` (`status`, `resolved_at`),
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`),
64		- UNIQUE KEY `uniq_serial_number` (`serial_number`)
	54	+ INDEX `idx_created_at` (`created_at`)
65	55	) $charsetCollate;";
66	56	$created = dbDelta($sql);
67	57	return $created;
68	58	} else {
69		- static::alterTable($table); }
	59	+ static::alterTable($table);
	60	+ }
70	61
71	62	return false;
72	63	}
73	64
		@@ -99,22 +90,8 @@
99	90	if (!in_array('created_by', $existing_columns)) {
100	91	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
101	92	$wpdb->query("ALTER TABLE `{$table}` ADD `created_by` BIGINT(20) UNSIGNED NULL AFTER `closed_by`");
102	93	}
103		-
104		- if (!in_array('serial_number', $existing_columns)) {
105		- // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
106		- $wpdb->query("ALTER TABLE `{$table}` ADD `serial_number` BIGINT UNSIGNED NULL AFTER `created_by`");
107		-
108		- // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is sanitized via esc_sql().
109		- $wpdb->query("UPDATE `{$table}` SET `serial_number` = `id` WHERE `serial_number` IS NULL");
110		- }
111		-
112		- if (!in_array('ticket_number', $existing_columns)) {
113		- // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
114		- $wpdb->query("ALTER TABLE `{$table}` ADD `ticket_number` VARCHAR(192) NULL DEFAULT NULL AFTER `serial_number`");
115		- }
116		-
117	94	}
118	95
119	96	public static function addMissingIndexes($table)
120	97	{
		@@ -132,34 +109,25 @@
132	109	foreach ($existing_indexes as $index) {
133	110	$existing_index_names[] = $index->Key_name;
134	111	}
135	112
136		- // Desired indexes — keys and values (including composite column lists) are
137		- // all hardcoded string literals; no user input reaches these queries.
	113	+ // Desired indexes — keys and values are all hardcoded string literals.
138	114	$indexes = [
139		- 'idx_customer_id' => '`customer_id`',
140		- 'idx_agent_id' => '`agent_id`',
141		- 'idx_mailbox_id' => '`mailbox_id`',
142		- 'idx_product_id' => '`product_id`',
143		- 'idx_priority' => '`priority`',
144		- 'idx_client_priority' => '`client_priority`',
145		- 'idx_status' => '`status`',
146		- 'idx_created_at' => '`created_at`',
147		- 'idx_resolved_at' => '`resolved_at`',
148		- 'idx_status_resolved_at' => '`status`, `resolved_at`',
149		- 'idx_ticket_number' => '`ticket_number`(191)',
150		- 'uniq_serial_number' => '`serial_number`',
151		- 'idx_waiting_since_id' => '`waiting_since`, `id`',
152		- 'idx_updated_at_id' => '`updated_at`, `id`',
153		- 'idx_response_count_id' => '`response_count`, `id`',
	115	+ 'idx_customer_id' => 'customer_id',
	116	+ 'idx_agent_id' => 'agent_id',
	117	+ 'idx_mailbox_id' => 'mailbox_id',
	118	+ 'idx_product_id' => 'product_id',
	119	+ 'idx_priority' => 'priority',
	120	+ 'idx_status' => 'status',
	121	+ 'idx_created_at' => 'created_at',
154	122	];
	123	+
155	124	// Add missing indexes. $table is esc_sql()'d above; $index_name and
156		- // $columns are hardcoded array literals — no user input reaches this query.
157		- foreach ($indexes as $index_name => $columns) {
	125	+ // $column_name are hardcoded array literals — no user input reaches this query.
	126	+ foreach ($indexes as $index_name => $column_name) {
158	127	if (!in_array($index_name, $existing_index_names)) {
159	128	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- all identifiers are either esc_sql()'d or hardcoded literals.
160		- $indexType = $index_name === 'uniq_serial_number' ? 'UNIQUE KEY' : 'INDEX';
161		- $wpdb->query("ALTER TABLE `{$table}` ADD {$indexType} `{$index_name}` ({$columns})");
	129	+ $wpdb->query("ALTER TABLE `{$table}` ADD INDEX `{$index_name}` (`{$column_name}`)");
162	130	}
163	131	}
164	132	}
165	133	}