fluent-support / database / Migrations / ProductsMigrator.php

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

78 lines 2.2 KB

Raw Download Zip

1	<?php
2
3	namespace FluentSupport\Database\Migrations;
4
5	class ProductsMigrator
6	{
7	static $tableName = 'fs_products';
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	`source_uid` BIGINT(20) UNSIGNED NULL,
21	`mailbox_id` BIGINT(20) UNSIGNED NULL,
22	`title` VARCHAR(192) NULL,
23	`description` TEXT NULL,
24	`settings` LONGTEXT NULL,
25	`source` VARCHAR(100) DEFAULT 'local',
26	`created_by` BIGINT(20) UNSIGNED NULL,
27	`created_at` TIMESTAMP NULL,
28	`updated_at` TIMESTAMP NULL,
29	INDEX `idx_mailbox_id` (`mailbox_id`),
30	INDEX `idx_created_at` (`created_at`),
31	INDEX `idx_source` (`source`)
32	) $charsetCollate;";
33	$created = dbDelta($sql);
34	return $created;
35	} else {
36	static::alterTable($table);
37	}
38
39	return false;
40	}
41
42	public static function alterTable($table)
43	{
44	static::addMissingIndexes($table);
45	}
46
47	public static function addMissingIndexes($table)
48	{
49	global $wpdb;
50
51	// Escape table name
52	$table = esc_sql($table);
53
54	// Get existing indexes
55	$existing_indexes = $wpdb->get_results("SHOW INDEX FROM `$table`");
56	$existing_index_names = [];
57
58	foreach ($existing_indexes as $index) {
59	$existing_index_names[] = $index->Key_name;
60	}
61
62	// Desired indexes
63	$indexes = [
64	'idx_mailbox_id' => 'mailbox_id',
65	'idx_created_at' => 'created_at',
66	'idx_source' => 'source',
67	];
68
69	// Add missing indexes
70	foreach ($indexes as $index_name => $column_name) {
71	if (!in_array($index_name, $existing_index_names)) {
72	$sql = "ALTER TABLE `$table` ADD INDEX `$index_name` (`$column_name`)";
73	$wpdb->query($sql);
74	}
75	}
76	}
77	}
78