mainwp / class / class-mainwp-install.php

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

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