← All changes | database/Migrations/TicketsMigrator.php +115 -10 1.5.1 → 2.4.0 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,
		@@ -20,9 +20,8 @@
20	20	`customer_id` BIGINT(20) UNSIGNED NULL,
21	21	`agent_id` BIGINT(20) UNSIGNED NULL,
22	22	`mailbox_id` BIGINT(20) UNSIGNED NULL,
23	23	`product_id` BIGINT(20) UNSIGNED NULL,
24		- `ticket_type_id` BIGINT(20) UNSIGNED NULL,
25	24	`product_source` VARCHAR(192) NULL,
26	25	`privacy` VARCHAR(100) DEFAULT 'private',
27	26	`priority` VARCHAR(100) DEFAULT 'normal',
28	27	`client_priority` VARCHAR(100) DEFAULT 'normal',
		@@ -42,19 +41,125 @@
42	41	`first_response_time` INT(11) NULL, /* Seconds took for first contact */
43	42	`total_close_time` INT(11) NULL, /* Seconds took for closing this ticket */
44	43	`resolved_at` TIMESTAMP NULL,
45	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,
46	48	`created_at` TIMESTAMP NULL,
47		- `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`)
48	65	) $charsetCollate;";
49		- dbDelta($sql);
	66	+ $created = dbDelta($sql);
	67	+ return $created;
50	68	} else {
51		- // @todo: We will remove this on final release
52		- // This is only for beta users
53		- $existing_columns = $wpdb->get_col("DESC {$table}", 0);
54		- if(!in_array('waiting_since', $existing_columns)) {
55		- $query = 'ALTER TABLE '.$table.' ADD `waiting_since` timestamp NULL AFTER `last_customer_response`';
56		- $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	+ // $table is always $wpdb->prefix . 'fs_tickets' — not user input.
	85	+ // esc_sql() is the correct escaping for SQL identifiers; $wpdb->prepare()
	86	+ // cannot quote identifiers in WP < 6.2 (no %i placeholder available).
	87	+ $table = esc_sql($table);
	88	+
	89	+ // Get existing columns
	90	+ $existing_columns = $wpdb->get_col("DESC `{$table}`", 0); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	91	+
	92	+ // Add waiting_since column if missing (beta user migration)
	93	+ if (!in_array('waiting_since', $existing_columns)) {
	94	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
	95	+ $wpdb->query("ALTER TABLE `{$table}` ADD `waiting_since` TIMESTAMP NULL AFTER `last_customer_response`");
	96	+ }
	97	+
	98	+ // Add created_by column to track agent who created ticket on behalf of customer
	99	+ if (!in_array('created_by', $existing_columns)) {
	100	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
	101	+ $wpdb->query("ALTER TABLE `{$table}` ADD `created_by` BIGINT(20) UNSIGNED NULL AFTER `closed_by`");
	102	+ }
	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	+ }
	118	+
	119	+ public static function addMissingIndexes($table)
	120	+ {
	121	+ global $wpdb;
	122	+
	123	+ // $table is always $wpdb->prefix . 'fs_tickets' — not user input.
	124	+ // esc_sql() is the correct escaping for SQL identifiers; $wpdb->prepare()
	125	+ // cannot quote identifiers in WP < 6.2 (no %i placeholder available).
	126	+ $table = esc_sql($table);
	127	+
	128	+ // Get existing indexes
	129	+ $existing_indexes = $wpdb->get_results("SHOW INDEX FROM `{$table}`"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	130	+ $existing_index_names = [];
	131	+
	132	+ foreach ($existing_indexes as $index) {
	133	+ $existing_index_names[] = $index->Key_name;
	134	+ }
	135	+
	136	+ // Desired indexes — keys and values (including composite column lists) are
	137	+ // all hardcoded string literals; no user input reaches these queries.
	138	+ $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`',
	154	+ ];
	155	+ // 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) {
	158	+ if (!in_array($index_name, $existing_index_names)) {
	159	+ // 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})");
57	162	}
58	163	}
59	164	}
60	165	}