← All changes | database/Migrations/PersonsMigrator.php +73 -15 1.4.5 → 2.4.0 View file →

		@@ -8,14 +8,13 @@
8	8
9	9	public static function migrate()
10	10	{
11	11	global $wpdb;
12		-
13	12	$charsetCollate = $wpdb->get_charset_collate();
14	13
15	14	$table = $wpdb->prefix . static::$tableName;
16	15
17		- if ($wpdb->get_var("SHOW TABLES LIKE '$table'") != $table) {
	16	+ if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table)) != $table) {
18	17	$sql = "CREATE TABLE $table (
19	18	`id` BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
20	19	`first_name` VARCHAR(192) NULL,
21	20	`last_name` VARCHAR(192) NULL,
		@@ -38,24 +37,83 @@
38	37	`description` MEDIUMTEXT NULL,
39	38	`remote_uid` BIGINT(20) UNSIGNED NULL,
40	39	`last_response_at` TIMESTAMP NULL,
41	40	`created_at` TIMESTAMP NULL,
42		- `updated_at` TIMESTAMP NULL
	41	+ `updated_at` TIMESTAMP NULL,
	42	+ INDEX `idx_email` (`email`),
	43	+ INDEX `idx_user_id` (`user_id`),
	44	+ INDEX `idx_ip_address` (`ip_address`)
43	45	) $charsetCollate;";
44		- dbDelta($sql);
	46	+ $created = dbDelta($sql);
	47	+ return $created;
45	48	} else {
46		- // @todo: We will remove this on final release
47		- // This is only for beta users
48		- $existing_columns = $wpdb->get_col("DESC {$table}", 0);
49		- if(!in_array('title', $existing_columns)) {
50		- $query = 'ALTER TABLE '.$table.' ADD `title` VARCHAR(192) NULL AFTER `email`';
51		- $wpdb->query($query);
52		- }
	49	+ static::alterTable($table);
	50	+ }
53	51
54		- if(!in_array('description', $existing_columns)) {
55		- $query = 'ALTER TABLE '.$table.' ADD `description` MEDIUMTEXT NULL AFTER `user_id`';
56		- $wpdb->query($query);
	52	+ return false;
	53	+ }
	54	+
	55	+ public static function alterTable($table)
	56	+ {
	57	+ static::addMissingColumns($table);
	58	+ static::addMissingIndexes($table);
	59	+ }
	60	+
	61	+ public static function addMissingColumns($table)
	62	+ {
	63	+ global $wpdb;
	64	+
	65	+ // $table is always $wpdb->prefix . 'fs_persons' — not user input.
	66	+ // esc_sql() is the correct escaping for SQL identifiers; $wpdb->prepare()
	67	+ // cannot quote identifiers in WP < 6.2 (no %i placeholder available).
	68	+ $table = esc_sql($table);
	69	+
	70	+ // Get existing columns
	71	+ $existing_columns = $wpdb->get_col("DESC `{$table}`", 0); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	72	+
	73	+ // @todo: We will remove this on final release
	74	+ // This is only for beta users
	75	+ if (!in_array('title', $existing_columns)) {
	76	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
	77	+ $wpdb->query("ALTER TABLE `{$table}` ADD `title` VARCHAR(192) NULL AFTER `email`");
	78	+ }
	79	+
	80	+ if (!in_array('description', $existing_columns)) {
	81	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
	82	+ $wpdb->query("ALTER TABLE `{$table}` ADD `description` MEDIUMTEXT NULL AFTER `user_id`");
	83	+ }
	84	+ }
	85	+
	86	+ public static function addMissingIndexes($table)
	87	+ {
	88	+ global $wpdb;
	89	+
	90	+ // $table is always $wpdb->prefix . 'fs_persons' — not user input.
	91	+ // esc_sql() is the correct escaping for SQL identifiers; $wpdb->prepare()
	92	+ // cannot quote identifiers in WP < 6.2 (no %i placeholder available).
	93	+ $table = esc_sql($table);
	94	+
	95	+ // Get existing indexes
	96	+ $existing_indexes = $wpdb->get_results("SHOW INDEX FROM `{$table}`"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	97	+ $existing_index_names = [];
	98	+
	99	+ foreach ($existing_indexes as $index) {
	100	+ $existing_index_names[] = $index->Key_name;
	101	+ }
	102	+
	103	+ // Desired indexes — keys and values are all hardcoded string literals.
	104	+ $indexes = [
	105	+ 'idx_email' => 'email',
	106	+ 'idx_user_id' => 'user_id',
	107	+ 'idx_ip_address' => 'ip_address',
	108	+ ];
	109	+
	110	+ // Add missing indexes. $table is esc_sql()'d above; $index_name and
	111	+ // $column_name are hardcoded array literals — no user input reaches this query.
	112	+ foreach ($indexes as $index_name => $column_name) {
	113	+ if (!in_array($index_name, $existing_index_names)) {
	114	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- all identifiers are either esc_sql()'d or hardcoded literals.
	115	+ $wpdb->query("ALTER TABLE `{$table}` ADD INDEX `{$index_name}` (`{$column_name}`)");
57	116	}
58		-
59	117	}
60	118	}
61	119	}