fluent-support / database / Migrations / AttachmentsMigrator.php

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

96 lines 3.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("SHOW TABLES LIKE '$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	// @todo: We will remove this on final release
54	// This is only for beta users
55	$existing_columns = $wpdb->get_col("DESC {$table}", 0);
56	if(!in_array('status', $existing_columns)) {
57	$query = "ALTER TABLE {$table} ADD `status` VARCHAR(100) NULL DEFAULT 'active' AFTER `driver`";
58	$wpdb->query($query);
59	}
60	static::addMissingIndexes($table);
61	}
62
63	public static function addMissingIndexes($table)
64	{
65	global $wpdb;
66
67	// Escape table name
68	$table = esc_sql($table);
69
70	// Get existing indexes
71	$existing_indexes = $wpdb->get_results("SHOW INDEX FROM `$table`");
72	$existing_index_names = [];
73
74	foreach ($existing_indexes as $index) {
75	$existing_index_names[] = $index->Key_name;
76	}
77
78	// Desired indexes
79	$indexes = [
80	'idx_ticket_id' => 'ticket_id',
81	'idx_person_id' => 'person_id',
82	'idx_conversation_id' => 'conversation_id',
83	'idx_status' => 'status',
84	'idx_created_at' => 'created_at',
85	];
86
87	// Add missing indexes
88	foreach ($indexes as $index_name => $column_name) {
89	if (!in_array($index_name, $existing_index_names)) {
90	$sql = "ALTER TABLE `$table` ADD INDEX `$index_name` (`$column_name`)";
91	$wpdb->query($sql);
92	}
93	}
94	}
95	}
96