← All changes | database/Migrations/PersonsMigrator.php +63 -15 1.4.0 → 2.4.5 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,73 @@
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	+ // Our own table name, not user input. esc_sql() because %i needs WP 6.2+.
	66	+ $table = esc_sql($table);
	67	+
	68	+ $existing_columns = $wpdb->get_col("DESC `{$table}`", 0); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	69	+
	70	+ // @todo: Beta-only migration, remove on final release.
	71	+ if (!in_array('title', $existing_columns)) {
	72	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
	73	+ $wpdb->query("ALTER TABLE `{$table}` ADD `title` VARCHAR(192) NULL AFTER `email`");
	74	+ }
	75	+
	76	+ if (!in_array('description', $existing_columns)) {
	77	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
	78	+ $wpdb->query("ALTER TABLE `{$table}` ADD `description` MEDIUMTEXT NULL AFTER `user_id`");
	79	+ }
	80	+ }
	81	+
	82	+ public static function addMissingIndexes($table)
	83	+ {
	84	+ global $wpdb;
	85	+
	86	+ // Our own table name, not user input. esc_sql() because %i needs WP 6.2+.
	87	+ $table = esc_sql($table);
	88	+
	89	+ $existing_indexes = $wpdb->get_results("SHOW INDEX FROM `{$table}`"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	90	+ $existing_index_names = [];
	91	+
	92	+ foreach ($existing_indexes as $index) {
	93	+ $existing_index_names[] = $index->Key_name;
	94	+ }
	95	+
	96	+ $indexes = [
	97	+ 'idx_email' => 'email',
	98	+ 'idx_user_id' => 'user_id',
	99	+ 'idx_ip_address' => 'ip_address',
	100	+ ];
	101	+
	102	+ foreach ($indexes as $index_name => $column_name) {
	103	+ if (!in_array($index_name, $existing_index_names)) {
	104	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- all identifiers are either esc_sql()'d or hardcoded literals.
	105	+ $wpdb->query("ALTER TABLE `{$table}` ADD INDEX `{$index_name}` (`{$column_name}`)");
57	106	}
58		-
59	107	}
60	108	}
61	109	}