fluent-support / database / Migrations / AttachmentsMigrator.php

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

104 lines 4.1 KB

Raw Download Zip

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