fluent-support / database / Migrations / PersonsMigrator.php

PersonsMigrator.php in Fluent Support – Helpdesk & Customer Support Ticket System 2.4.5, at database/Migrations/PersonsMigrator.php

110 lines 4.2 KB

Raw Download Zip

1	<?php
2
3	namespace FluentSupport\Database\Migrations;
4
5	class PersonsMigrator
6	{
7	static $tableName = 'fs_persons';
8
9	public static function migrate()
10	{
11	global $wpdb;
12	$charsetCollate = $wpdb->get_charset_collate();
13
14	$table = $wpdb->prefix . static::$tableName;
15
16	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table)) != $table) {
17	$sql = "CREATE TABLE $table (
18	`id` BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
19	`first_name` VARCHAR(192) NULL,
20	`last_name` VARCHAR(192) NULL,
21	`email` VARCHAR(192) NULL,
22	`title` VARCHAR(192) NULL,
23	`avatar` VARCHAR(192) NULL,
24	`person_type` VARCHAR(192) DEFAULT 'customer',
25	`status` VARCHAR(192) DEFAULT 'active',
26	`ip_address` VARCHAR(20) NULL,
27	`last_ip_address` VARCHAR(20) NULL,
28	`address_line_1` VARCHAR(192) NULL,
29	`address_line_2` VARCHAR(192) NULL,
30	`city` VARCHAR(192) NULL,
31	`zip` VARCHAR(192) NULL,
32	`state` VARCHAR(192) NULL,
33	`country` VARCHAR(192) NULL,
34	`note` LONGTEXT NULL,
35	`hash` VARCHAR(192) NULL,
36	`user_id` BIGINT(20) UNSIGNED NULL,
37	`description` MEDIUMTEXT NULL,
38	`remote_uid` BIGINT(20) UNSIGNED NULL,
39	`last_response_at` TIMESTAMP NULL,
40	`created_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`)
45	) $charsetCollate;";
46	$created = dbDelta($sql);
47	return $created;
48	} else {
49	static::alterTable($table);
50	}
51
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}`)");
106	}
107	}
108	}
109	}
110