← All changes | database/Migrations/TicketsMigrator.php +61 -28 1.10.1 → 2.4.5 View file →

		@@ -13,9 +13,9 @@
13	13	$charsetCollate = $wpdb->get_charset_collate();
14	14
15	15	$table = $wpdb->prefix . static::$tableName;
16	16
17		- if ($wpdb->get_var("SHOW TABLES LIKE '$table'") != $table) {
	17	+ if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table)) != $table) {
18	18	$sql = "CREATE TABLE $table (
19	19	`id` BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
20	20	`customer_id` BIGINT(20) UNSIGNED NULL,
21	21	`agent_id` BIGINT(20) UNSIGNED NULL,
		@@ -41,8 +41,11 @@
41	41	`first_response_time` INT(11) NULL, /* Seconds took for first contact */
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	+ `created_by` BIGINT(20) UNSIGNED NULL,
	46	+ `serial_number` BIGINT UNSIGNED NULL,
	47	+ `ticket_number` VARCHAR(192) NULL DEFAULT NULL,
45	48	`created_at` TIMESTAMP NULL,
46	49	`updated_at` TIMESTAMP NULL,
47	50	INDEX `idx_customer_id` (`customer_id`),
48	51	INDEX `idx_agent_id` (`agent_id`),
		@@ -48,21 +51,28 @@
48	51	INDEX `idx_agent_id` (`agent_id`),
49	52	INDEX `idx_mailbox_id` (`mailbox_id`),
50	53	INDEX `idx_product_id` (`product_id`),
51	54	INDEX `idx_priority` (`priority`),
	55	+ INDEX `idx_client_priority` (`client_priority`),
52	56	INDEX `idx_status` (`status`),
53		- INDEX `idx_created_at` (`created_at`)
	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	65	) $charsetCollate;";
55	66	$created = dbDelta($sql);
56	67	return $created;
57	68	} else {
58		- static::alterTable($table);
59		- }
	69	+ static::alterTable($table); }
60	70
61	71	return false;
62	72	}
63	73
64		- public static function alterTable($table)
	74	+ public static function alterTable($table)
65	75	{
66	76	static::addMissingColumns($table);
67	77	static::addMissingIndexes($table);
68	78	}
		@@ -70,19 +80,37 @@
70	80	public static function addMissingColumns($table)
71	81	{
72	82	global $wpdb;
73	83
74		- // Escape table name
	84	+ // Our own table name, not user input. esc_sql() because %i needs WP 6.2+.
75	85	$table = esc_sql($table);
76	86
77		- // Get existing columns
78		- $existing_columns = $wpdb->get_col("DESC `$table`", 0);
79		-
80		- // Add waiting_since column if missing (beta user migration)
	87	+ $existing_columns = $wpdb->get_col("DESC `{$table}`", 0); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	88	+
81	89	if (!in_array('waiting_since', $existing_columns)) {
82		- $query = 'ALTER TABLE `' . $table . '` ADD `waiting_since` TIMESTAMP NULL AFTER `last_customer_response`';
83		- $wpdb->query($query);
	90	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
	91	+ $wpdb->query("ALTER TABLE `{$table}` ADD `waiting_since` TIMESTAMP NULL AFTER `last_customer_response`");
84	92	}
	93	+
	94	+ // Agent who created the ticket on behalf of the customer.
	95	+ if (!in_array('created_by', $existing_columns)) {
	96	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
	97	+ $wpdb->query("ALTER TABLE `{$table}` ADD `created_by` BIGINT(20) UNSIGNED NULL AFTER `closed_by`");
	98	+ }
	99	+
	100	+ if (!in_array('serial_number', $existing_columns)) {
	101	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
	102	+ $wpdb->query("ALTER TABLE `{$table}` ADD `serial_number` BIGINT UNSIGNED NULL AFTER `created_by`");
	103	+
	104	+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is sanitized via esc_sql().
	105	+ $wpdb->query("UPDATE `{$table}` SET `serial_number` = `id` WHERE `serial_number` IS NULL");
	106	+ }
	107	+
	108	+ if (!in_array('ticket_number', $existing_columns)) {
	109	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
	110	+ $wpdb->query("ALTER TABLE `{$table}` ADD `ticket_number` VARCHAR(192) NULL DEFAULT NULL AFTER `serial_number`");
	111	+ }
	112	+
85	113	}
86	114
87	115	public static function addMissingIndexes($table)
88	116	{
		@@ -87,13 +115,12 @@
87	115	public static function addMissingIndexes($table)
88	116	{
89	117	global $wpdb;
90	118
91		- // Escape table name
	119	+ // Our own table name, not user input. esc_sql() because %i needs WP 6.2+.
92	120	$table = esc_sql($table);
93	121
94		- // Get existing indexes
95		- $existing_indexes = $wpdb->get_results("SHOW INDEX FROM `$table`");
	122	+ $existing_indexes = $wpdb->get_results("SHOW INDEX FROM `{$table}`"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
96	123	$existing_index_names = [];
97	124
98	125	foreach ($existing_indexes as $index) {
99	126	$existing_index_names[] = $index->Key_name;
		@@ -98,24 +125,30 @@
98	125	foreach ($existing_indexes as $index) {
99	126	$existing_index_names[] = $index->Key_name;
100	127	}
101	128
102		- // Desired indexes
103	129	$indexes = [
104		- 'idx_customer_id' => 'customer_id',
105		- 'idx_agent_id' => 'agent_id',
106		- 'idx_mailbox_id' => 'mailbox_id',
107		- 'idx_product_id' => 'product_id',
108		- 'idx_priority' => 'priority',
109		- 'idx_status' => 'status',
110		- 'idx_created_at' => 'created_at',
	130	+ 'idx_customer_id' => '`customer_id`',
	131	+ 'idx_agent_id' => '`agent_id`',
	132	+ 'idx_mailbox_id' => '`mailbox_id`',
	133	+ 'idx_product_id' => '`product_id`',
	134	+ 'idx_priority' => '`priority`',
	135	+ 'idx_client_priority' => '`client_priority`',
	136	+ 'idx_status' => '`status`',
	137	+ 'idx_created_at' => '`created_at`',
	138	+ 'idx_resolved_at' => '`resolved_at`',
	139	+ 'idx_status_resolved_at' => '`status`, `resolved_at`',
	140	+ 'idx_ticket_number' => '`ticket_number`(191)',
	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`',
111	145	];
112		-
113		- // Add missing indexes
114		- foreach ($indexes as $index_name => $column_name) {
	146	+ foreach ($indexes as $index_name => $columns) {
115	147	if (!in_array($index_name, $existing_index_names)) {
116		- $sql = "ALTER TABLE `$table` ADD INDEX `$index_name` (`$column_name`)";
117		- $wpdb->query($sql);
	148	+ $indexType = $index_name === 'uniq_serial_number' ? 'UNIQUE KEY' : 'INDEX';
	149	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- all identifiers are either esc_sql()'d or hardcoded literals.
	150	+ $wpdb->query("ALTER TABLE `{$table}` ADD {$indexType} `{$index_name}` ({$columns})");
118	151	}
119	152	}
120	153	}
121	154	}