← All changes | database/Migrations/AttachmentsMigrator.php +68 -9 1.4.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	`ticket_id` BIGINT(20) UNSIGNED NULL,
21	21	`person_id` BIGINT(20) UNSIGNED NULL,
		@@ -29,18 +29,77 @@
29	29	`driver` VARCHAR(100) DEFAULT 'local',
30	30	`status` VARCHAR(100) NULL DEFAULT 'active',
31	31	`file_size` VARCHAR(100) NULL,
32	32	`created_at` TIMESTAMP NULL,
33		- `updated_at` TIMESTAMP NULL
	33	+ `updated_at` TIMESTAMP NULL,
	34	+ INDEX `idx_file_hash` (`file_hash`),
	35	+ INDEX `idx_ticket_id` (`ticket_id`),
	36	+ INDEX `idx_person_id` (`person_id`),
	37	+ INDEX `idx_conversation_id` (`conversation_id`),
	38	+ INDEX `idx_status` (`status`),
	39	+ INDEX `idx_created_at` (`created_at`)
34	40	) $charsetCollate;";
35		- dbDelta($sql);
	41	+ $created = dbDelta($sql);
	42	+ return $created;
36	43	} else {
37		- // @todo: We will remove this on final release
38		- // This is only for beta users
39		- $existing_columns = $wpdb->get_col("DESC {$table}", 0);
40		- if(!in_array('status', $existing_columns)) {
41		- $query = "ALTER TABLE {$table} ADD `status` VARCHAR(100) NULL DEFAULT 'active' AFTER `driver`";
42		- $wpdb->query($query);
	44	+ static::alterTable($table);
	45	+ }
	46	+
	47	+ return false;
	48	+ }
	49	+
	50	+ public static function alterTable($table)
	51	+ {
	52	+ global $wpdb;
	53	+
	54	+ // $table is always $wpdb->prefix . 'fs_attachments' — not user input.
	55	+ // esc_sql() is the correct escaping for SQL identifiers; $wpdb->prepare()
	56	+ // cannot quote identifiers in WP < 6.2 (no %i placeholder available).
	57	+ $table = esc_sql($table);
	58	+
	59	+ // @todo: We will remove this on final release
	60	+ // This is only for beta users
	61	+ $existing_columns = $wpdb->get_col("DESC `{$table}`", 0); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	62	+ if (!in_array('status', $existing_columns)) {
	63	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
	64	+ $wpdb->query("ALTER TABLE `{$table}` ADD `status` VARCHAR(100) NULL DEFAULT 'active' AFTER `driver`");
	65	+ }
	66	+
	67	+ static::addMissingIndexes($table);
	68	+ }
	69	+
	70	+ public static function addMissingIndexes($table)
	71	+ {
	72	+ global $wpdb;
	73	+
	74	+ // $table is already esc_sql()'d by alterTable(); sanitize again defensively
	75	+ // in case addMissingIndexes() is ever called directly.
	76	+ $table = esc_sql($table);
	77	+
	78	+ // Get existing indexes
	79	+ $existing_indexes = $wpdb->get_results("SHOW INDEX FROM `{$table}`"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	80	+ $existing_index_names = [];
	81	+
	82	+ foreach ($existing_indexes as $index) {
	83	+ $existing_index_names[] = $index->Key_name;
	84	+ }
	85	+
	86	+ // Desired indexes — keys and values are all hardcoded string literals.
	87	+ $indexes = [
	88	+ 'idx_ticket_id' => 'ticket_id',
	89	+ 'idx_person_id' => 'person_id',
	90	+ 'idx_conversation_id' => 'conversation_id',
	91	+ 'idx_status' => 'status',
	92	+ 'idx_created_at' => 'created_at',
	93	+ 'idx_file_hash' => 'file_hash',
	94	+ ];
	95	+
	96	+ // Add missing indexes. $table is esc_sql()'d above; $index_name and
	97	+ // $column_name are hardcoded array literals — no user input reaches this query.
	98	+ foreach ($indexes as $index_name => $column_name) {
	99	+ if (!in_array($index_name, $existing_index_names)) {
	100	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- all identifiers are either esc_sql()'d or hardcoded literals.
	101	+ $wpdb->query("ALTER TABLE `{$table}` ADD INDEX `{$index_name}` (`{$column_name}`)");
43	102	}
44	103	}
45	104	}
46	105	}