fluent-community / database / Migrations / NotificationUserMigrator.php

NotificationUserMigrator.php in FluentCommunity – Ultra-Fast High-Performance Social Network, Community, LMS & Online Courses 2.10.01, at database/Migrations/NotificationUserMigrator.php

107 lines 4.1 KB

Raw Download Zip

1	<?php
2	// phpcs:disable
3
4	namespace FluentCommunity\Database\Migrations;
5
6	class NotificationUserMigrator
7	{
8	/**
9	* Migrate the table.
10	*
11	* @return void
12	*/
13	public static function migrate()
14	{
15	global $wpdb;
16
17	$charsetCollate = $wpdb->get_charset_collate();
18
19	$table = $wpdb->prefix . 'fcom_notification_users';
20	$indexPrefix = $wpdb->prefix . 'fcom_nu_';
21
22	if ($wpdb->get_var("SHOW TABLES LIKE '$table'") != $table) {
23	$sql = "CREATE TABLE $table (
24	`id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
25	`object_type` VARCHAR(50) NULL DEFAULT 'notification',
26	`notification_type` VARCHAR(50) NULL DEFAULT 'web',
27	`object_id` BIGINT UNSIGNED NULL,
28	`user_id` BIGINT UNSIGNED NULL,
29	`is_read` TINYINT(1) UNSIGNED NULL DEFAULT 0,
30	`created_at` TIMESTAMP NULL,
31	`updated_at` TIMESTAMP NULL,
32	INDEX `{$indexPrefix}_uiou` (`user_id`, `is_read`, `object_type`, `updated_at`),
33	INDEX `{$indexPrefix}_mto_id_oion` (`object_id`, `is_read`, `object_type`, `notification_type`),
34	INDEX `{$indexPrefix}_created_uid` (`created_at`, `user_id`)
35	) $charsetCollate;";
36	dbDelta($sql);
37	} else {
38	self::maybeAlterDBColumns();
39	}
40	}
41
42	private static function maybeAlterDBColumns()
43	{
44	global $wpdb;
45	$table = $wpdb->prefix . 'fcom_notification_users';
46	// get column names
47	$column_names = $wpdb->get_col("DESC " . $table, 0);
48
49	if(in_array('notification_id', $column_names)) {
50	$wpdb->query("ALTER TABLE $table CHANGE `notification_id` `object_id` bigint unsigned NULL");
51	}
52
53	$newItems = ['object_type', 'notification_type'];
54	$newItems = array_diff($newItems, $column_names);
55	if($newItems) {
56	if(in_array('object_type', $newItems)) {
57	$wpdb->query("ALTER TABLE $table ADD object_type VARCHAR(50) NULL DEFAULT 'notification' AFTER id");
58	}
59	if(in_array('notification_type', $newItems)) {
60	$wpdb->query("ALTER TABLE $table ADD notification_type VARCHAR(50) NULL DEFAULT 'web' AFTER object_type");
61	}
62	}
63
64	$allIndexes = $wpdb->get_col("SHOW INDEX FROM $table", 2);
65	$previousIndexName = $wpdb->prefix . 'fcom_nu__mto_id_idx';
66	if(in_array($previousIndexName, $allIndexes)) {
67	// remove this index
68	$wpdb->query("ALTER TABLE $table DROP INDEX `{$previousIndexName}`");
69	}
70
71	$newIndex1 = $wpdb->prefix . 'fcom_nu__mto_id_uio';
72
73	if(in_array($newIndex1, $allIndexes)) {
74	// remove this index
75	$wpdb->query("ALTER TABLE $table DROP INDEX `{$newIndex1}`");
76	}
77
78	$index2 = $wpdb->prefix . 'fcom_nu__mto_id_oion';
79
80	if(!in_array($index2, $allIndexes)) {
81	// add this index
82	$wpdb->query("ALTER TABLE $table ADD INDEX `{$index2}` (`object_id`, `is_read`, `object_type`, `notification_type`)");
83	}
84
85	$createdIndex = $wpdb->prefix . 'fcom_nu__created_uid';
86
87	if(!in_array($createdIndex, $allIndexes)) {
88	$wpdb->query("ALTER TABLE $table ADD INDEX `{$createdIndex}` (`created_at`, `user_id`)");
89	}
90
91	/*
92	* Every ticker poll counts a member's unread rows, and the live-notification
93	* toast reads the newest of them. Without a user_id-leading index MySQL falls
94	* back to the single-column is_read index - on a table where nearly every row
95	* is unread that means scanning half the table, per poll, per member.
96	*
97	* user_id + is_read + object_type answer both queries as a covering index, and
98	* the trailing updated_at removes the toast query's filesort as well.
99	*/
100	$unreadIndex = $wpdb->prefix . 'fcom_nu__uiou';
101
102	if(!in_array($unreadIndex, $allIndexes)) {
103	$wpdb->query("ALTER TABLE $table ADD INDEX `{$unreadIndex}` (`user_id`, `is_read`, `object_type`, `updated_at`)");
104	}
105	}
106	}
107