← All changes | database/Migrations/DataMetrixMigrator.php +50 -3 1.5.5 → 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	`stat_date` DATE NOT NULL,
21	21	`data_type` VARCHAR(100) DEFAULT 'agent_stat',
		@@ -26,10 +26,57 @@
26	26	`new_tickets` INT(11) UNSIGNED NULL DEFAULT 0, /* all new status ticket count */
27	27	`unassigned_tickets` INT(11) UNSIGNED NULL DEFAULT 0, /* For Global use case only */
28	28	`close_to_average` INT(11) UNSIGNED NULL DEFAULT 0, /* average close time of the tickets */
29	29	`created_at` TIMESTAMP NULL,
30		- `updated_at` TIMESTAMP NULL
	30	+ `updated_at` TIMESTAMP NULL,
	31	+ INDEX `idx_stat_date` (`stat_date`),
	32	+ INDEX `idx_agent_id` (`agent_id`),
	33	+ INDEX `idx_data_type` (`data_type`)
31	34	) $charsetCollate;";
32		- dbDelta($sql);
	35	+ $created = dbDelta($sql);
	36	+ return $created;
	37	+ } else {
	38	+ static::alterTable($table);
	39	+ }
	40	+
	41	+ return false;
	42	+ }
	43	+
	44	+ public static function alterTable($table)
	45	+ {
	46	+ static::addMissingIndexes($table);
	47	+ }
	48	+
	49	+ public static function addMissingIndexes($table)
	50	+ {
	51	+ global $wpdb;
	52	+
	53	+ // $table is always $wpdb->prefix . 'fs_data_metrix' — not user input.
	54	+ // esc_sql() is the correct escaping for SQL identifiers; $wpdb->prepare()
	55	+ // cannot quote identifiers in WP < 6.2 (no %i placeholder available).
	56	+ $table = esc_sql($table);
	57	+
	58	+ // Get existing indexes
	59	+ $existing_indexes = $wpdb->get_results("SHOW INDEX FROM `{$table}`"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	60	+ $existing_index_names = [];
	61	+
	62	+ foreach ($existing_indexes as $index) {
	63	+ $existing_index_names[] = $index->Key_name;
	64	+ }
	65	+
	66	+ // Desired indexes — keys and values are all hardcoded string literals.
	67	+ $indexes = [
	68	+ 'idx_stat_date' => 'stat_date',
	69	+ 'idx_agent_id' => 'agent_id',
	70	+ 'idx_data_type' => 'data_type',
	71	+ ];
	72	+
	73	+ // Add missing indexes. $table is esc_sql()'d above; $index_name and
	74	+ // $column_name are hardcoded array literals — no user input reaches this query.
	75	+ foreach ($indexes as $index_name => $column_name) {
	76	+ if (!in_array($index_name, $existing_index_names)) {
	77	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- all identifiers are either esc_sql()'d or hardcoded literals.
	78	+ $wpdb->query("ALTER TABLE `{$table}` ADD INDEX `{$index_name}` (`{$column_name}`)");
	79	+ }
33	80	}
34	81	}
35	82	}