← All changes | database/Migrations/TicketsMigrator.php +104 -9 1.5.4 → 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,19 +41,114 @@
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		- `updated_at` TIMESTAMP NULL
	49	+ `updated_at` TIMESTAMP NULL,
	50	+ INDEX `idx_customer_id` (`customer_id`),
	51	+ INDEX `idx_agent_id` (`agent_id`),
	52	+ INDEX `idx_mailbox_id` (`mailbox_id`),
	53	+ INDEX `idx_product_id` (`product_id`),
	54	+ INDEX `idx_priority` (`priority`),
	55	+ INDEX `idx_client_priority` (`client_priority`),
	56	+ 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`)
47	65	) $charsetCollate;";
48		- dbDelta($sql);
	66	+ $created = dbDelta($sql);
	67	+ return $created;
49	68	} else {
50		- // @todo: We will remove this on final release
51		- // This is only for beta users
52		- $existing_columns = $wpdb->get_col("DESC {$table}", 0);
53		- if(!in_array('waiting_since', $existing_columns)) {
54		- $query = 'ALTER TABLE '.$table.' ADD `waiting_since` timestamp NULL AFTER `last_customer_response`';
55		- $wpdb->query($query);
	69	+ static::alterTable($table); }
	70	+
	71	+ return false;
	72	+ }
	73	+
	74	+ public static function alterTable($table)
	75	+ {
	76	+ static::addMissingColumns($table);
	77	+ static::addMissingIndexes($table);
	78	+ }
	79	+
	80	+ public static function addMissingColumns($table)
	81	+ {
	82	+ global $wpdb;
	83	+
	84	+ // Our own table name, not user input. esc_sql() because %i needs WP 6.2+.
	85	+ $table = esc_sql($table);
	86	+
	87	+ $existing_columns = $wpdb->get_col("DESC `{$table}`", 0); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	88	+
	89	+ if (!in_array('waiting_since', $existing_columns)) {
	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`");
	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	+
	113	+ }
	114	+
	115	+ public static function addMissingIndexes($table)
	116	+ {
	117	+ global $wpdb;
	118	+
	119	+ // Our own table name, not user input. esc_sql() because %i needs WP 6.2+.
	120	+ $table = esc_sql($table);
	121	+
	122	+ $existing_indexes = $wpdb->get_results("SHOW INDEX FROM `{$table}`"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	123	+ $existing_index_names = [];
	124	+
	125	+ foreach ($existing_indexes as $index) {
	126	+ $existing_index_names[] = $index->Key_name;
	127	+ }
	128	+
	129	+ $indexes = [
	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`',
	145	+ ];
	146	+ foreach ($indexes as $index_name => $columns) {
	147	+ if (!in_array($index_name, $existing_index_names)) {
	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})");
56	151	}
57	152	}
58	153	}
59	154	}