mainwp / class / class-mainwp-install.php

class-mainwp-install.php in MainWP Dashboard: Self-hosted WordPress Management for Agencies 6.2.2, at class/class-mainwp-install.php

928 lines 38.4 KB

Raw Download Zip

1	<?php
2	/**
3	* MainWP Install
4	*
5	* This file handles install MainWP DB.
6	*
7	* @package MainWP/Dashboard
8	*/
9
10	namespace MainWP\Dashboard;
11
12	// Exit if accessed directly.
13	if ( ! defined( 'ABSPATH' ) ) {
14	exit;
15	}
16
17
18	/**
19	* Class MainWP_Install
20	*
21	* @package MainWP\Dashboard
22	*
23	* @uses \MainWP\Dashboard\MainWP_DB_Base
24	*/
25	class MainWP_Install extends MainWP_DB_Base { // phpcs:ignore Generic.Classes.OpeningBraceSameLine.ContentAfterBrace -- NOSONAR.
26
27	// phpcs:disable WordPress.DB.RestrictedFunctions, WordPress.DB.PreparedSQL.NotPrepared -- unprepared SQL ok, accessing the database directly to custom database functions.
28
29	/**
30	* Private variable to hold the database version info.
31	*
32	* @var string DB version info.
33	*/
34	protected $mainwp_db_version = '9.0.2.5'; // NOSONAR - no IP. 9.0.2.4 drops the stray unique index on backup progress task_id. Bumped for MWP-1566 sibling-dir chmod migration (MWP-1558 follow-up). Original 9.0.2.0 bump for MWP-1557/1558.
35
36	/**
37	* Protected variable to hold the database option name.
38	*
39	* @var string DB version info.
40	*/
41	protected $option_db_key = 'mainwp_db_version';
42
43	/**
44	* Network-scoped like mainwp_db_version, so every blog on a multisite sees the pending repair.
45	*/
46	const BACKUP_PROGRESS_INDEX_REPAIR_PENDING = 'mainwp_backup_progress_index_repair_pending';
47
48	/**
49	* Network-scoped like mainwp_db_version, so every blog on a multisite sees the pending repair.
50	*/
51	const WP_GROUP_MISSING_PRIMARY_KEY_REPAIR_PENDING = 'mainwp_wp_group_missing_primary_key_repair_pending';
52
53	/**
54	* Private static variable to hold the single instance of the class.
55	*
56	* @static
57	*
58	* @var mixed Default null
59	*/
60	private static $instance = null;
61
62	/**
63	* Method instance()
64	*
65	* Return public static instance.
66	*
67	* @static
68	* @return MainWP_DB
69	*/
70	public static function instance() {
71	if ( null === static::$instance ) {
72	static::$instance = new self();
73	}
74
75	static::$instance->test_connection();
76
77	return static::$instance;
78	}
79
80	/**
81	* Method install()
82	*
83	* Installs the new DB.
84	*
85	* @return void
86	*
87	* @uses \MainWP\Dashboard\MainWP_Utility::update_option()
88	*/
89	public function install() { // phpcs:ignore -- NOSONAR - complex function. Current complexity is the only way to achieve desired results, pull request solutions appreciated.
90	// get_site_option is multisite aware!
91	$currentVersion = get_site_option( $this->option_db_key );
92
93	if ( empty( $currentVersion ) ) {
94	update_option( 'mainwp_run_quick_setup', 'yes' );
95	MainWP_Utility::update_option( 'mainwp_enableLegacyBackupFeature', 0 );
96	} elseif ( false === get_option( 'mainwp_enableLegacyBackupFeature' ) ) {
97	MainWP_Utility::update_option( 'mainwp_enableLegacyBackupFeature', 1 );
98	}
99
100	if ( empty( $currentVersion ) \|\| version_compare( $currentVersion, '8.8', '<' ) ) {
101	MainWP_Utility::update_option( 'mainwp_selected_theme', 'default' );
102	}
103
104	$wp_table = esc_sql( $this->table_name( 'wp' ) );
105	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name escaped via esc_sql.
106	$rslt = static::instance()->query( "SHOW TABLES LIKE '{$wp_table}'" );
107	if ( empty( static::num_rows( $rslt ) ) ) {
108	$currentVersion = false;
109	}
110
111	if ( $currentVersion === $this->mainwp_db_version ) {
112	// The index repair keeps its own marker so a failed DROP is retried at
113	// most hourly, without holding the DB version back and re-running every
114	// older migration on each load.
115	$this->maybe_retry_backup_progress_index_repair();
116
117	$this->maybe_retry_repair_wp_group_primary_key();
118	return;
119	}
120
121	$this->pre_update_tables();
122
123	$charset_collate = $this->wpdb->get_charset_collate();
124
125	$sql = array();
126	$tbl = 'CREATE TABLE ' . $this->table_name( 'wp' ) . " (
127	id int(11) NOT NULL auto_increment,
128	userid int(11) NOT NULL,
129	adminname text NOT NULL,
130	name text NOT NULL,
131	url text NOT NULL,
132	pubkey text NOT NULL,
133	privkey text NOT NULL,
134	siteurl text NOT NULL,
135	ga_id text NOT NULL,
136	gas_id int(11) NOT NULL,
137	offline_checks_last int(11) NOT NULL,
138	offline_check_result int(11) NOT NULL,
139	http_response_code int(11) NOT NULL DEFAULT 0,
140	http_code_noticed tinyint(1) NOT NULL DEFAULT 1,
141	disable_health_check tinyint(1) NOT NULL DEFAULT 0,
142	health_threshold int(11) NOT NULL DEFAULT 0,
143	note text NOT NULL,
144	statsUpdate int(11) NOT NULL,
145	directories longtext NOT NULL,
146	plugin_upgrades longtext NOT NULL,
147	theme_upgrades longtext NOT NULL,
148	translation_upgrades longtext NOT NULL,
149	premium_upgrades longtext NOT NULL,
150	securityIssues longtext NOT NULL,
151	themes longtext NOT NULL,
152	ignored_themes longtext NOT NULL,
153	plugins longtext NOT NULL,
154	ignored_plugins longtext NOT NULL,
155	users longtext NOT NULL,
156	categories longtext NOT NULL,
157	pluginDir text NOT NULL,
158	automatic_update tinyint(1) NOT NULL,
159	backup_before_upgrade tinyint(1) NOT NULL DEFAULT 2,
160	mainwpdir tinyint(1) NOT NULL,
161	loadFilesBeforeZip tinyint(1) NOT NULL DEFAULT 1,
162	is_ignoreCoreUpdates tinyint(1) NOT NULL DEFAULT 0,
163	is_ignorePluginUpdates tinyint(1) NOT NULL DEFAULT 0,
164	is_ignoreThemeUpdates tinyint(1) NOT NULL DEFAULT 0,
165	verify_certificate tinyint(1) NOT NULL DEFAULT 1,
166	force_use_ipv4 tinyint(1) NOT NULL DEFAULT 0,
167	ssl_version tinyint(1) NOT NULL DEFAULT 0,
168	ip text NOT NULL DEFAULT '',
169	uniqueId text NOT NULL,
170	maximumFileDescriptorsOverride tinyint(1) NOT NULL DEFAULT 0,
171	maximumFileDescriptorsAuto tinyint(1) NOT NULL DEFAULT 1,
172	maximumFileDescriptors int(11) NOT NULL DEFAULT 150,
173	primary_backup_method varchar(64) NOT NULL DEFAULT '',
174	http_user text NOT NULL DEFAULT '',
175	http_pass text NOT NULL DEFAULT '',
176	wpe tinyint(1) NOT NULL,
177	is_staging tinyint(1) NOT NULL DEFAULT 0,
178	client_id int(11) NOT NULL DEFAULT 0,
179	`suspended` tinyint(1) NOT NULL DEFAULT 0,
180	KEY idx_wp_staging_name_id (is_staging, name(191), id),
181	KEY idx_userid (userid),
182	KEY idx_client_id (client_id),
183	KEY idx_url (url(191))";
184
185	if ( empty( $currentVersion ) ) {
186	$tbl .= ',
187	PRIMARY KEY (id) ';
188	}
189	$tbl .= ') ' . $charset_collate;
190	$sql[] = $tbl;
191
192	$tbl = 'CREATE TABLE ' . $this->table_name( 'wp_sync' ) . " (
193	sync_id int(11) NOT NULL auto_increment,
194	wpid int(11) NOT NULL,
195	version text NOT NULL DEFAULT '',
196	sync_errors longtext NOT NULL DEFAULT '',
197	uptodate longtext NOT NULL DEFAULT '',
198	dtsAutomaticSync int(11) NOT NULL DEFAULT 0,
199	dtsAutomaticSyncStart int(11) NOT NULL DEFAULT 0,
200	dtsSync int(11) NOT NULL DEFAULT 0,
201	dtsSyncStart int(11) NOT NULL DEFAULT 0,
202	totalsize int(11) NOT NULL DEFAULT 0,
203	dbsize int(11) NOT NULL DEFAULT 0,
204	extauth text NOT NULL DEFAULT '',
205	last_post_gmt int(11) NOT NULL DEFAULT 0,
206	health_value int(11) NOT NULL DEFAULT 0,
207	health_status tinyint(1) NOT NULL DEFAULT 0,
208	health_site_noticed tinyint(1) NOT NULL DEFAULT 1,
209	KEY idx_wpid (wpid)";
210
211	if ( empty( $currentVersion ) ) {
212	$tbl .= ',
213	PRIMARY KEY (sync_id)';
214	}
215
216	$tbl .= ') ' . $charset_collate;
217
218	$sql[] = $tbl;
219
220	$tbl = 'CREATE TABLE ' . $this->table_name( 'wp_options' ) . " (
221	opt_id int(11) NOT NULL auto_increment,
222	wpid int(11) NOT NULL,
223	name text NOT NULL DEFAULT '',
224	value longtext NOT NULL DEFAULT '',
225	KEY idx_options_wpid_name (wpid, name(191)),
226	KEY idx_wpid (wpid)";
227
228	if ( empty( $currentVersion ) ) {
229	$tbl .= ',
230	PRIMARY KEY (opt_id)';
231	}
232	$tbl .= ') ' . $charset_collate;
233	$sql[] = $tbl;
234
235	$tbl = 'CREATE TABLE ' . $this->table_name( 'wp_settings_backup' ) . ' (
236	set_id int(11) NOT NULL auto_increment,
237	wpid int(11) NOT NULL,
238	archiveFormat text NOT NULL,
239	KEY idx_wpid (wpid)';
240
241	if ( empty( $currentVersion ) ) {
242	$tbl .= ',
243	PRIMARY KEY (set_id)';
244	}
245	$tbl .= ') ' . $charset_collate;
246	$sql[] = $tbl;
247
248	$tbl = 'CREATE TABLE ' . $this->table_name( 'users' ) . " (
249	userid int(11) NOT NULL,
250	user_email text NOT NULL DEFAULT '',
251	ignored_plugins longtext NOT NULL DEFAULT '',
252	trusted_plugins longtext NOT NULL DEFAULT '',
253	trusted_plugins_notes longtext NOT NULL DEFAULT '',
254	ignored_themes longtext NOT NULL DEFAULT '',
255	ignored_wp_upgrades longtext NOT NULL DEFAULT '',
256	trusted_themes longtext NOT NULL DEFAULT '',
257	trusted_themes_notes longtext NOT NULL DEFAULT '',
258	site_view tinyint(1) NOT NULL DEFAULT '0',
259	pluginDir text NOT NULL DEFAULT '',
260	dismissed_plugins longtext NOT NULL DEFAULT '',
261	dismissed_themes longtext NOT NULL DEFAULT ''";
262	if ( empty( $currentVersion ) ) {
263	$tbl .= ',
264	PRIMARY KEY (userid) ';
265	}
266	$tbl .= ') ' . $charset_collate;
267	$sql[] = $tbl;
268
269	$tbl = 'CREATE TABLE ' . $this->table_name( 'wp_status' ) . ' (
270	statusid bigint(20) unsigned NOT NULL auto_increment,
271	wpid int(11) NOT NULL,
272	http_code smallint NOT NULL DEFAULT 0,
273	status tinyint(1) NOT NULL DEFAULT 0,
274	event_timestamp int(11) NOT NULL,
275	duration int(11) NOT NULL DEFAULT 0';
276	if ( empty( $currentVersion ) \|\| version_compare( $currentVersion, '8.31', '<=' ) ) {
277	$tbl .= ',
278	PRIMARY KEY (statusid) ';
279	}
280	$tbl .= ') ' . $charset_collate;
281	$sql[] = $tbl;
282
283	$tbl = 'CREATE TABLE ' . $this->table_name( 'group' ) . ' (
284	id int(11) NOT NULL auto_increment,
285	userid int(11) NOT NULL,
286	name text NOT NULL,
287	color varchar(32) NOT NULL DEFAULT ""';
288	if ( empty( $currentVersion ) ) {
289	$tbl .= ',
290	PRIMARY KEY (id) ';
291	}
292	$tbl .= ') ' . $charset_collate;
293	$sql[] = $tbl;
294
295	$tbl = 'CREATE TABLE ' . $this->table_name( 'wp_group' ) . ' (
296	wp_group_id int(11) NOT NULL auto_increment,
297	wpid int(11) NOT NULL,
298	groupid int(11) NOT NULL,
299	KEY idx_wpid (wpid),
300	KEY idx_groupid (groupid),
301	PRIMARY KEY (wp_group_id)
302	) ' . $charset_collate;
303
304	$sql[] = $tbl;
305
306	$tbl = 'CREATE TABLE ' . $this->table_name( 'lookup_item_objects' ) . ' (
307	lookup_id bigint(20) unsigned NOT NULL auto_increment,
308	item_id bigint(20) unsigned NOT NULL,
309	item_name varchar(32) NOT NULL,
310	object_id bigint(20) unsigned NOT NULL,
311	object_name varchar(32) NOT NULL,
312	KEY item_id (item_id),
313	KEY object_id (object_id)';
314
315	if ( empty( $currentVersion ) \|\| version_compare( $currentVersion, '9.0.0.5', '<' ) ) { // NOSONAR - none IP.
316	$tbl .= ',
317	PRIMARY KEY (lookup_id) ';
318	}
319	$tbl .= ') ' . $charset_collate . ';';
320	$sql[] = $tbl;
321
322	$tbl = 'CREATE TABLE ' . $this->table_name( 'wp_backup_progress' ) . " (
323	task_id int(11) NOT NULL,
324	wp_id int(11) NOT NULL,
325	dtsFetched int(11) NOT NULL DEFAULT 0,
326	fetchResult text NOT NULL DEFAULT '',
327	downloadedDB text NOT NULL DEFAULT '',
328	downloadedFULL text NOT NULL DEFAULT '',
329	downloadedDBComplete tinyint(1) NOT NULL DEFAULT 0,
330	downloadedFULLComplete tinyint(1) NOT NULL DEFAULT 0,
331	removedFiles tinyint(1) NOT NULL DEFAULT 0,
332	attempts int(11) NOT NULL DEFAULT 0,
333	last_error text NOT NULL DEFAULT '',
334	pid int(11) NOT NULL DEFAULT 0,
335	KEY idx_task_id (task_id)";
336	$tbl .= ') ' . $charset_collate;
337	$sql[] = $tbl;
338
339	$tbl = 'CREATE TABLE ' . $this->table_name( 'wp_backup' ) . ' (
340	id int(11) NOT NULL auto_increment,
341	userid int(11) NOT NULL,
342	name text NOT NULL,
343	schedule text NOT NULL,
344	type text NOT NULL,
345	exclude text NOT NULL,
346	sites text NOT NULL,
347	`groups` text NOT NULL,
348	last int(11) NOT NULL,
349	last_run int(11) NOT NULL,
350	lastStartNotificationSent int(11) NOT NULL DEFAULT 0,
351	last_run_manually int(11) NOT NULL,
352	completed_sites text NOT NULL,
353	completed int(11) NOT NULL,
354	backup_errors text NOT NULL,
355	subfolder text NOT NULL,
356	filename text NOT NULL,
357	paused tinyint(1) NOT NULL,
358	template tinyint(1) DEFAULT 0,
359	excludebackup tinyint(1) DEFAULT 0,
360	excludecache tinyint(1) DEFAULT 0,
361	excludenonwp tinyint(1) DEFAULT 0,
362	excludezip tinyint(1) DEFAULT 0,
363	archiveFormat text NOT NULL,
364	loadFilesBeforeZip tinyint(1) NOT NULL DEFAULT 1,
365	maximumFileDescriptorsOverride tinyint(1) NOT NULL DEFAULT 0,
366	maximumFileDescriptorsAuto tinyint(1) NOT NULL DEFAULT 1,
367	maximumFileDescriptors int(11) NOT NULL DEFAULT 150';
368	if ( empty( $currentVersion ) ) {
369	$tbl .= ',
370	PRIMARY KEY (id) ';
371	}
372	$tbl .= ') ' . $charset_collate;
373	$sql[] = $tbl;
374
375	$tbl = 'CREATE TABLE ' . $this->table_name( 'api_keys' ) . ' (
376	key_id bigint(20) unsigned NOT NULL auto_increment,
377	user_id bigint(20) unsigned NOT NULL,
378	description varchar(200) NULL,
379	permissions varchar(10) NOT NULL,
380	consumer_key char(64) NOT NULL,
381	consumer_secret varchar(255) NOT NULL,
382	nonces longtext NULL,
383	truncated_key char(7) NOT NULL,
384	key_pass char(64) NOT NULL DEFAULT "",
385	key_type tinyint(1) NOT NULL DEFAULT 0,
386	`enabled` tinyint(1) DEFAULT 0,
387	last_access datetime NULL default null,
388	KEY consumer_key (consumer_key)';
389	if ( empty( $currentVersion ) \|\| version_compare( $currentVersion, '9.0.0.9', '<=' ) ) { // NOSONAR - none IP.
390	$tbl .= ',
391	PRIMARY KEY (key_id) ';
392	}
393	$tbl .= ') ' . $charset_collate . ';';
394	$sql[] = $tbl;
395
396	$tbl = 'CREATE TABLE ' . $this->table_name( 'action_log' ) . " (
397	id int(11) NOT NULL auto_increment,
398	log_content mediumtext NOT NULL DEFAULT '',
399	log_type tinyint(1) DEFAULT 0,
400	log_color tinyint(1) DEFAULT 0,
401	log_user varchar(128) NOT NULL DEFAULT '',
402	log_timestamp int(11) NOT NULL DEFAULT 0";
403	if ( empty( $currentVersion ) \|\| version_compare( $currentVersion, '8.50', '<=' ) ) {
404	$tbl .= ',
405	PRIMARY KEY (id) ';
406	}
407	$tbl .= ') ' . $charset_collate . ';';
408	$sql[] = $tbl;
409
410	$tbl = 'CREATE TABLE ' . $this->table_name( 'request_log' ) . " (
411	id int(11) NOT NULL auto_increment,
412	wpid int(11) NOT NULL,
413	ip text NOT NULL DEFAULT '',
414	subnet text NOT NULL DEFAULT '',
415	micro_timestamp_stop DECIMAL( 12, 2 ) NOT NULL DEFAULT 0,
416	micro_timestamp_start DECIMAL( 12, 2 ) NOT NULL DEFAULT 0";
417	if ( empty( $currentVersion ) \|\| version_compare( $currentVersion, '5.7', '<=' ) ) {
418	$tbl .= ',
419	PRIMARY KEY (id) ';
420	}
421	$tbl .= ') ' . $charset_collate . ';';
422	$sql[] = $tbl;
423
424	$tbl = 'CREATE TABLE ' . $this->table_name( 'schedule_processes' ) . " (
425	process_id int(11) NOT NULL auto_increment,
426	item_id int(11) NOT NULL,
427	`type` varchar(32) NOT NULL,
428	`process_slug` varchar(64) NOT NULL,
429	`status` varchar(32) NOT NULL DEFAULT '',
430	dts_process_start int(11) NOT NULL DEFAULT 0,
431	dts_process_init_time int(11) NOT NULL DEFAULT 0,
432	dts_process_stop int(11) NOT NULL DEFAULT 0";
433
434	if ( empty( $currentVersion ) \|\| version_compare( $currentVersion, '9.0.0.45', '<' ) ) { //phpcs:ignore -- NOSONAR - no ip.
435	$tbl .= ',
436	PRIMARY KEY (process_id) ';
437	}
438
439	$tbl .= ') ' . $charset_collate . ';';
440	$sql[] = $tbl;
441
442	// End of tables.
443
444	$sql = apply_filters( 'mainwp_db_install_tables', $sql, $currentVersion, $charset_collate );
445
446	MainWP_DB_Uptime_Monitoring::instance()->get_db_schema( $sql, $currentVersion );
447
448	require_once ABSPATH . 'wp-admin/includes/upgrade.php'; // NOSONAR - WP compatible.
449
450	global $wpdb;
451
452	if ( MainWP_Utility::instance()->is_disabled_functions( 'error_log' ) \|\| ! function_exists( '\error_log' ) ) {
453	error_reporting(0); // phpcs:ignore -- try to disabled the error_log somewhere in WP.
454	}
455
456	$suppress = $wpdb->suppress_errors();
457	foreach ( $sql as $query ) {
458	dbDelta( $query );
459	}
460	$wpdb->suppress_errors( $suppress );
461
462	$this->post_update();
463
464	do_action( 'mainwp_db_after_update', $currentVersion, $this->mainwp_db_version ); // new version: $this->mainwp_db_version.
465
466	if ( ! is_multisite() ) {
467	MainWP_Utility::update_option( $this->option_db_key, $this->mainwp_db_version );
468	} else {
469	update_site_option( $this->option_db_key, $this->mainwp_db_version );
470	}
471	}
472
473	/**
474	* Returns the database version.
475	*
476	* @return string
477	*/
478	public function get_db_version() {
479	return get_site_option( $this->option_db_key );
480	}
481
482	/**
483	* Method post_update()
484	*
485	* Update MainWP DB.
486	*
487	* @return void
488	*/
489	public function post_update() { // phpcs:ignore -- NOSONAR - complex.
490
491	// get_site_option is multisite aware!
492	$currentVersion = get_site_option( $this->option_db_key );
493
494	if ( false === $currentVersion ) {
495	return;
496	}
497
498	$suppress = $this->wpdb->suppress_errors();
499
500	$this->post_update_81( $currentVersion );
501
502	if ( version_compare( $currentVersion, '8.984', '<' ) ) {
503	$sslColumns = array(
504	'nossl',
505	'nosslkey',
506	);
507	$wp_table = esc_sql( $this->table_name( 'wp' ) );
508	foreach ( $sslColumns as $col ) {
509	$col = esc_sql( $col );
510	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Column name escaped via esc_sql, table name escaped separately.
511	$this->wpdb->query( "ALTER TABLE {$wp_table} DROP COLUMN {$col}" );
512	}
513	}
514
515	// delete old columns.
516	if ( version_compare( $currentVersion, '8.17', '<' ) ) {
517	$rankColumns = array(
518	'pagerank',
519	'indexed',
520	'alexia',
521	'pagerank_old',
522	'indexed_old',
523	'alexia_old',
524	'last_db_backup_size',
525	);
526
527	foreach ( $rankColumns as $rankColumn ) {
528	$rankColumn = esc_sql( $rankColumn );
529	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Column name escaped via esc_sql, table name escaped separately.
530	$this->wpdb->query( "ALTER TABLE {$wp_table} DROP COLUMN {$rankColumn}" );
531	}
532
533	$syncColumns = array( 'uptodate' );
534	$wp_sync_table = esc_sql( $this->table_name( 'wp_sync' ) );
535	foreach ( $syncColumns as $column ) {
536	$column = esc_sql( $column );
537	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Column name escaped via esc_sql, table name escaped separately.
538	$this->wpdb->query( "ALTER TABLE {$wp_sync_table} DROP COLUMN {$column}" );
539	}
540	}
541
542	// delete old columns.
543	if ( version_compare( $currentVersion, '8.35', '<' ) ) {
544	$delColumns = array( 'offline_checks' );
545	foreach ( $delColumns as $column ) {
546	$column = esc_sql( $column );
547	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Column name escaped via esc_sql, table name escaped separately.
548	$this->wpdb->query( "ALTER TABLE {$wp_table} DROP COLUMN {$column}" );
549	}
550	$delColumns = array( 'heatMap' );
551	$users_table = esc_sql( $this->table_name( 'users' ) );
552	foreach ( $delColumns as $column ) {
553	$column = esc_sql( $column );
554	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Column name escaped via esc_sql, table name escaped separately.
555	$this->wpdb->query( "ALTER TABLE {$users_table} DROP COLUMN {$column}" );
556	}
557	}
558
559	// delete columns.
560	if ( version_compare( $currentVersion, '8.42', '<' ) ) {
561	$delColumns = array( 'offlineChecksOnlineNotification' );
562	foreach ( $delColumns as $column ) {
563	$column = esc_sql( $column );
564	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Column name escaped via esc_sql, table name escaped separately.
565	$this->wpdb->query( "ALTER TABLE {$users_table} DROP COLUMN {$column}" );
566	}
567	}
568
569	// fix missing PRIMARY keys.
570	if ( version_compare( $currentVersion, '8.53', '<=' ) ) {
571	$wp_options_table = esc_sql( $this->table_name( 'wp_options' ) );
572	$wp_settings_backup_table = esc_sql( $this->table_name( 'wp_settings_backup' ) );
573	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name escaped via esc_sql.
574	$this->wpdb->query( "ALTER TABLE {$wp_options_table} ADD opt_id int NOT NULL AUTO_INCREMENT PRIMARY KEY" );
575	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name escaped via esc_sql.
576	$this->wpdb->query( "ALTER TABLE {$wp_settings_backup_table} ADD set_id int NOT NULL AUTO_INCREMENT PRIMARY KEY" );
577	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name escaped via esc_sql.
578	$this->wpdb->query( "ALTER TABLE {$wp_sync_table} ADD sync_id int NOT NULL AUTO_INCREMENT PRIMARY KEY" );
579	}
580
581	$this->update_optimize_indexes_55( $currentVersion );
582
583	// dbDelta never drops an index, so the stray unique key needs an explicit migration.
584	if ( version_compare( $currentVersion, '9.0.2.4', '<' ) ) { // NOSONAR - no ip.
585	$this->repair_backup_progress_index();
586	}
587
588	if ( ! empty( $currentVersion ) && version_compare( $currentVersion, '9.0.2.5', '<' ) ) { // NOSONAR - no ip.
589	$this->repair_wp_group_primary_key();
590	}
591
592	$this->wpdb->suppress_errors( $suppress );
593	MainWP_DB_Client::instance()->check_to_updates_reports_data_861( $currentVersion );
594	}
595
596	/**
597	* Handle optimize tables indexes.
598	*
599	* @param string $current_ver Current DB version.
600	*
601	* @return void
602	*/
603	public function update_optimize_indexes_55( $current_ver ) {
604	if ( ! empty( $current_ver ) && version_compare( $current_ver, '9.0.1.1', '<' ) ) { // NOSONAR - no ip.
605	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp' ) . ' ADD INDEX idx_wp_staging_name_id (is_staging, name(191), id)' ); //phpcs:ignore -- ok.
606	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp_options' ) . ' ADD INDEX KEY idx_options_wpid_name (wpid, name(191))' ); //phpcs:ignore -- ok.
607	}
608	if ( ! empty( $current_ver ) && version_compare( $current_ver, '9.0.1.3', '<' ) ) { // NOSONAR - no ip.
609	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp' ) . ' ADD INDEX idx_userid (userid)' ); //phpcs:ignore -- ok.
610	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp' ) . ' ADD INDEX idx_client_id (client_id)' ); //phpcs:ignore -- ok.
611	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp' ) . ' ADD INDEX idx_url (url(191))' ); //phpcs:ignore -- ok.
612	}
613	// MWP-1540: widen consumer_secret so wp_hash_password output (variable length,
614	// typically 34 chars for $P$ but up to ~150 for argon2) fits without truncation,
615	// and drop the unused KEY consumer_secret index (lookups are by HMAC'd consumer_key,
616	// never by consumer_secret). dbDelta does not reliably MODIFY existing column types
617	// or DROP indexes, so both changes are applied explicitly here.
618	if ( ! empty( $current_ver ) && version_compare( $current_ver, '9.0.1.5', '<' ) ) { // NOSONAR - no ip.
619	$api_keys_table = $this->table_name( 'api_keys' );
620	// Drop the unused index FIRST so the column type change is unambiguous.
621	$existing_indexes = $this->wpdb->get_col( "SHOW INDEX FROM {$api_keys_table} WHERE Key_name = 'consumer_secret'", 2 ); // phpcs:ignore -- table name is internal.
622	if ( ! empty( $existing_indexes ) ) {
623	$this->wpdb->query( "ALTER TABLE {$api_keys_table} DROP INDEX consumer_secret" ); // phpcs:ignore -- table name is internal.
624	}
625	$this->wpdb->query( "ALTER TABLE {$api_keys_table} MODIFY COLUMN consumer_secret varchar(255) NOT NULL" ); // phpcs:ignore -- table name is internal.
626
627	// Confirm the MODIFY actually took effect before we trust this migration.
628	// WP 6.5+ produces bcrypt hashes around 60 characters, so a silent failure
629	// would leave the column at char(43) and truncate every freshly hashed
630	// secret on insert, quietly breaking auth on any newly created key. On a
631	// width mismatch we set a flag option that the dashboard surfaces as an
632	// admin notice; mainwp_notice_wp_mail_failed in class-mainwp-system.php
633	// uses the same shape.
634	$col_def = $this->wpdb->get_row( "SHOW COLUMNS FROM {$api_keys_table} LIKE 'consumer_secret'" ); // phpcs:ignore -- table name is internal.
635	if ( empty( $col_def ) \|\| false === stripos( (string) $col_def->Type, 'varchar(255)' ) ) {
636	update_option( 'mainwp_notice_consumer_secret_migration_failed', current_time( 'mysql' ) );
637	} else {
638	delete_option( 'mainwp_notice_consumer_secret_migration_failed' );
639	}
640	}
641	}
642
643	/**
644	* Retry a pending backup progress index repair, no more than once an hour.
645	*
646	* A DB user without ALTER never clears the marker, so an unthrottled retry
647	* would run two SHOW INDEX plus a failing ALTER on every single request.
648	*
649	* @return bool True when a repair attempt was made.
650	*/
651	public function maybe_retry_repair_wp_group_primary_key() {
652	$pending = (int) get_site_option( self::WP_GROUP_MISSING_PRIMARY_KEY_REPAIR_PENDING );
653	if ( empty( $pending ) \|\| time() - $pending < HOUR_IN_SECONDS ) {
654	return false;
655	}
656
657	$suppress = $this->wpdb->suppress_errors();
658	$this->repair_wp_group_primary_key();
659	$this->wpdb->suppress_errors( $suppress );
660
661	return true;
662	}
663
664	/**
665	* Repair the wp_group table primary key for legacy installations.
666	*
667	* To fix MWP-1794.
668	*
669	* @return void
670	*/
671	private function repair_wp_group_primary_key() {
672	$wp_group_table = esc_sql( $this->table_name( 'wp_group' ) );
673
674	$column_exists = $this->wpdb->get_var(
675	"SHOW COLUMNS FROM {$wp_group_table} LIKE 'wp_group_id'" // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Table name is escaped and query contains no user input.
676	);
677
678	if ( empty( $column_exists ) ) {
679	$this->wpdb->query(
680	"ALTER TABLE {$wp_group_table}
681	ADD COLUMN wp_group_id int(11) NOT NULL auto_increment PRIMARY KEY FIRST" // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Table name is escaped and query contains no user input.
682	);
683	} else {
684	$primary_key = $this->wpdb->get_var(
685	"SHOW INDEX FROM {$wp_group_table}
686	WHERE Key_name = 'PRIMARY'
687	AND Column_name = 'wp_group_id'" // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Table name is escaped and query contains no user input.
688	);
689
690	if ( empty( $primary_key ) ) {
691	$this->wpdb->query(
692	"ALTER TABLE {$wp_group_table}
693	ADD PRIMARY KEY (wp_group_id)" // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Table name is escaped and query contains no user input.
694	);
695	}
696	}
697
698	$primary_key = $this->wpdb->get_var(
699	"SHOW INDEX FROM {$wp_group_table}
700	WHERE Key_name = 'PRIMARY'
701	AND Column_name = 'wp_group_id'" // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Table name is escaped and query contains no user input.
702	);
703
704	if ( ! empty( $primary_key ) ) {
705	delete_site_option( self::WP_GROUP_MISSING_PRIMARY_KEY_REPAIR_PENDING );
706	} else {
707	// A timestamp, not a formatted date: the retry throttle compares it against time().
708	update_site_option( self::WP_GROUP_MISSING_PRIMARY_KEY_REPAIR_PENDING, time() );
709	}
710	}
711
712	/**
713	* Retry a pending backup progress index repair, no more than once an hour.
714	*
715	* A DB user without ALTER never clears the marker, so an unthrottled retry
716	* would run two SHOW INDEX plus a failing ALTER on every single request.
717	*
718	* @return bool True when a repair attempt was made.
719	*/
720	public function maybe_retry_backup_progress_index_repair() {
721	$pending = (int) get_site_option( self::BACKUP_PROGRESS_INDEX_REPAIR_PENDING );
722	if ( empty( $pending ) \|\| time() - $pending < HOUR_IN_SECONDS ) {
723	return false;
724	}
725
726	$this->repair_backup_progress_index();
727
728	return true;
729	}
730
731	/**
732	* Run the backup progress index repair and remember whether it still needs a retry.
733	*
734	* @return bool True when the repair is confirmed complete.
735	*/
736	public function repair_backup_progress_index() {
737	// The retry from install() runs outside post_update()'s suppression; a
738	// persistently failing ALTER must not log or print on every request.
739	$suppress = $this->wpdb->suppress_errors();
740	$repaired = $this->drop_backup_progress_unique_index();
741	$this->wpdb->suppress_errors( $suppress );
742	if ( $repaired ) {
743	delete_site_option( self::BACKUP_PROGRESS_INDEX_REPAIR_PENDING );
744	} else {
745	// A timestamp, not a formatted date: the retry throttle compares it against time().
746	update_site_option( self::BACKUP_PROGRESS_INDEX_REPAIR_PENDING, time() );
747	}
748	return $repaired;
749	}
750
751	/**
752	* Drop a stray unique index on the backup progress task_id column.
753	*
754	* Fresh installs made between DB 8.53 and the schema fix created
755	* wp_backup_progress with UNIQUE (task_id). Progress rows are per task and
756	* per site, so a task covering two or more sites could never insert its
757	* second row. dbDelta never drops an index, so the stray key has to go here.
758	*
759	* @return bool True when no such key remains, false when one survived a failed DROP.
760	*/
761	public function drop_backup_progress_unique_index() {
762	$table = $this->table_name( 'wp_backup_progress' );
763	$keys = $this->find_backup_progress_task_id_unique_keys();
764	if ( null === $keys ) {
765	return false;
766	}
767
768	foreach ( $keys as $key_name ) {
769	$this->wpdb->query( "ALTER TABLE {$table} DROP INDEX `{$key_name}`" ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL statement; table name is a hardcoded internal identifier, key name comes from SHOW INDEX and is allowlisted below.
770	}
771
772	// suppress_errors() hides a failed DROP, so the table is the only source of truth.
773	return array() === $this->find_backup_progress_task_id_unique_keys();
774	}
775
776	/**
777	* Names of the unique keys on the backup progress table that cover task_id alone.
778	*
779	* A composite unique key over task_id and wp_id is legitimate and is left out.
780	*
781	* @return array\|null Key names, or null when the lookup itself failed and nothing can be concluded.
782	*/
783	protected function find_backup_progress_task_id_unique_keys() {
784	$table = $this->table_name( 'wp_backup_progress' );
785
786	// suppress_errors() is on in post_update(), so a failed SHOW INDEX looks like an empty index list; last_error is the only tell.
787	$this->wpdb->last_error = '';
788	$indexes = $this->wpdb->get_results( "SHOW INDEX FROM {$table} WHERE Non_unique = 0", ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL introspection; table name is a hardcoded internal identifier, no user input involved.
789	if ( '' !== $this->wpdb->last_error ) {
790	return null;
791	}
792
793	$key_columns = array();
794	foreach ( (array) $indexes as $index ) {
795	if ( 'PRIMARY' === $index['Key_name'] ) {
796	continue;
797	}
798	$key_columns[ $index['Key_name'] ][] = $index['Column_name'];
799	}
800
801	$keys = array();
802	foreach ( $key_columns as $key_name => $columns ) {
803	if ( array( 'task_id' ) === $columns && preg_match( '/^[A-Za-z0-9_]+$/', $key_name ) ) {
804	$keys[] = $key_name;
805	}
806	}
807
808	return $keys;
809	}
810
811	/**
812	* Method pre_update_tables()
813	*
814	* Handle pre update tables.
815	*
816	* @return void
817	*/
818	public function pre_update_tables() {
819	// get_site_option is multisite aware!
820	$currentVersion = get_site_option( $this->option_db_key );
821
822	if ( false === $currentVersion ) {
823	return;
824	}
825
826	$suppress = $this->wpdb->suppress_errors();
827
828	if ( version_compare( $currentVersion, '8.98', '<=' ) ) {
829	$wp_table = esc_sql( $this->table_name( 'wp' ) );
830	$existing_columns = $this->wpdb->get_col( "SHOW COLUMNS FROM {$wp_table}", 0 ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL introspection; table name is a hardcoded internal identifier escaped via esc_sql(), no user input involved.
831	foreach ( array( 'backups', 'note_lastupdate', 'pages' ) as $column ) {
832	if ( in_array( $column, $existing_columns, true ) ) {
833	$this->wpdb->query( 'ALTER TABLE ' . $wp_table . ' DROP COLUMN ' . esc_sql( $column ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL statement; table and column names are hardcoded internal identifiers escaped via esc_sql(), no user input involved.
834	}
835	}
836	}
837
838	$this->wpdb->suppress_errors( $suppress );
839	}
840
841	/**
842	* Method post_update_81()
843	*
844	* Update MainWP DB for version 8.1.
845	*
846	* @param string $current_version Current version DB.
847	*
848	* @return void
849	*/
850	public function post_update_81( $current_version ) { //phpcs:ignore -- NOSONAR - complex.
851
852	if ( version_compare( $current_version, '8.1', '<' ) ) {
853
854	// We can't split up here!
855	$wpSyncColumns = array(
856	'version',
857	'totalsize',
858	'dbsize',
859	'extauth',
860	'last_post_gmt',
861	'sync_errors',
862	'dtsSync',
863	'dtsSyncStart',
864	'dtsAutomaticSync',
865	'dtsAutomaticSyncStart',
866	);
867	$wp_table = esc_sql( $this->table_name( 'wp' ) );
868	$wp_sync_table = esc_sql( $this->table_name( 'wp_sync' ) );
869	foreach ( $wpSyncColumns as $wpSyncColumn ) {
870	$wpSyncColumn = esc_sql( $wpSyncColumn );
871	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Column name escaped via esc_sql, table name escaped separately.
872	$rslts = $this->wpdb->get_results( "SELECT id,{$wpSyncColumn} FROM {$wp_table}", ARRAY_A );
873	if ( empty( $rslts ) ) {
874	continue;
875	}
876
877	foreach ( $rslts as $rslt ) {
878	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name escaped via esc_sql and query uses proper prepare.
879	$exists = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT wpid FROM {$wp_sync_table} WHERE wpid = %d", $rslt['id'] ), ARRAY_A );
880	if ( empty( $exists ) ) {
881	$this->wpdb->insert(
882	$this->table_name( 'wp_sync' ),
883	array(
884	'wpid' => $rslt['id'],
885	$wpSyncColumn => $rslt[ $wpSyncColumn ],
886	)
887	);
888	} else {
889	$this->wpdb->update( $this->table_name( 'wp_sync' ), array( $wpSyncColumn => $rslt[ $wpSyncColumn ] ), array( 'wpid' => $rslt['id'] ) );
890	}
891	}
892
893	$suppress = $this->wpdb->suppress_errors();
894	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Column name escaped via esc_sql, table name escaped separately.
895	$this->wpdb->query( "ALTER TABLE {$wp_table} DROP COLUMN {$wpSyncColumn}" );
896	$this->wpdb->suppress_errors( $suppress );
897	}
898
899	$optionColumns = array(
900	'last_wp_upgrades',
901	'last_plugin_upgrades',
902	'last_theme_upgrades',
903	'wp_upgrades',
904	'recent_comments',
905	'recent_posts',
906	'recent_pages',
907	);
908	foreach ( $optionColumns as $optionColumn ) {
909	$optionColumn = esc_sql( $optionColumn );
910	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Column name escaped via esc_sql, table name escaped separately.
911	$rslts = $this->wpdb->get_results( "SELECT id,{$optionColumn} FROM {$wp_table}", ARRAY_A );
912	if ( empty( $rslts ) ) {
913	continue;
914	}
915
916	foreach ( $rslts as $rslt ) {
917	static::update_website_option( (object) $rslt, $optionColumn, $rslt[ $optionColumn ] );
918	}
919
920	$suppress = $this->wpdb->suppress_errors();
921	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Column name escaped via esc_sql, table name escaped separately.
922	$this->wpdb->query( "ALTER TABLE {$wp_table} DROP COLUMN {$optionColumn}" );
923	$this->wpdb->suppress_errors( $suppress );
924	}
925	}
926	}
927	}
928