fluent-support / database / Migrations / PersonsMigrator.php

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

120 lines 4.8 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	// $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}`)");
116	}
117	}
118	}
119	}
120