fluent-support / database / Migrations / AttachmentsMigrator.php

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

106 lines 4.2 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_file_hash` (`file_hash`),
35	INDEX `idx_ticket_id` (`ticket_id`),
36	INDEX `idx_person_id` (`person_id`),
37	INDEX `idx_conversation_id` (`conversation_id`),
38	INDEX `idx_status` (`status`),
39	INDEX `idx_created_at` (`created_at`)
40	) $charsetCollate;";
41	$created = dbDelta($sql);
42	return $created;
43	} else {
44	static::alterTable($table);
45	}
46
47	return false;
48	}
49
50	public static function alterTable($table)
51	{
52	global $wpdb;
53
54	// $table is always $wpdb->prefix . 'fs_attachments' — not user input.
55	// esc_sql() is the correct escaping for SQL identifiers; $wpdb->prepare()
56	// cannot quote identifiers in WP < 6.2 (no %i placeholder available).
57	$table = esc_sql($table);
58
59	// @todo: We will remove this on final release
60	// This is only for beta users
61	$existing_columns = $wpdb->get_col("DESC `{$table}`", 0); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
62	if (!in_array('status', $existing_columns)) {
63	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
64	$wpdb->query("ALTER TABLE `{$table}` ADD `status` VARCHAR(100) NULL DEFAULT 'active' AFTER `driver`");
65	}
66
67	static::addMissingIndexes($table);
68	}
69
70	public static function addMissingIndexes($table)
71	{
72	global $wpdb;
73
74	// $table is already esc_sql()'d by alterTable(); sanitize again defensively
75	// in case addMissingIndexes() is ever called directly.
76	$table = esc_sql($table);
77
78	// Get existing indexes
79	$existing_indexes = $wpdb->get_results("SHOW INDEX FROM `{$table}`"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
80	$existing_index_names = [];
81
82	foreach ($existing_indexes as $index) {
83	$existing_index_names[] = $index->Key_name;
84	}
85
86	// Desired indexes — keys and values are all hardcoded string literals.
87	$indexes = [
88	'idx_ticket_id' => 'ticket_id',
89	'idx_person_id' => 'person_id',
90	'idx_conversation_id' => 'conversation_id',
91	'idx_status' => 'status',
92	'idx_created_at' => 'created_at',
93	'idx_file_hash' => 'file_hash',
94	];
95
96	// Add missing indexes. $table is esc_sql()'d above; $index_name and
97	// $column_name are hardcoded array literals — no user input reaches this query.
98	foreach ($indexes as $index_name => $column_name) {
99	if (!in_array($index_name, $existing_index_names)) {
100	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- all identifiers are either esc_sql()'d or hardcoded literals.
101	$wpdb->query("ALTER TABLE `{$table}` ADD INDEX `{$index_name}` (`{$column_name}`)");
102	}
103	}
104	}
105	}
106