| 1 |
<?php |
| 2 |
|
| 3 |
/** |
| 4 |
* The database migration for the plugin. |
| 5 |
* |
| 6 |
* @since 1.0.0 |
| 7 |
* @package Metasync |
| 8 |
* @subpackage Metasync/database |
| 9 |
* @author Engineering Team <[email protected]> |
| 10 |
*/ |
| 11 |
// Some Plugins declare class name DBMigration to avoid conflict, renamed the class |
| 12 |
class MetaSync_DBMigration |
| 13 |
{ |
| 14 |
|
| 15 |
/** |
| 16 |
* Option flag marking the leftover wp-config.php copy cleanup as done. |
| 17 |
*/ |
| 18 |
const WPCONFIG_BACKUP_CLEANUP_OPTION = 'metasync_wpconfig_backup_cleanup_done'; |
| 19 |
|
| 20 |
/** |
| 21 |
* Age in seconds before an orphaned .metasync-tmp-* file is safe to delete. |
| 22 |
*/ |
| 23 |
const WPCONFIG_TMP_MAX_AGE = 300; |
| 24 |
|
| 25 |
/** |
| 26 |
* activation of migration. |
| 27 |
*/ |
| 28 |
public static function activation() |
| 29 |
{ |
| 30 |
self::run_migrations(); |
| 31 |
} |
| 32 |
|
| 33 |
/** |
| 34 |
* Run all database migrations |
| 35 |
*/ |
| 36 |
public static function run_migrations() |
| 37 |
{ |
| 38 |
global $wpdb; |
| 39 |
$collate = $wpdb->get_charset_collate(); |
| 40 |
|
| 41 |
require_once ABSPATH . 'wp-admin/includes/upgrade.php'; |
| 42 |
|
| 43 |
// Create 404 Monitor Table |
| 44 |
require_once dirname(__FILE__, 2) . '/404-monitor/class-metasync-404-monitor-database.php'; |
| 45 |
$tableName = esc_sql($wpdb->prefix . Metasync_Error_Monitor_Database::$table_name); |
| 46 |
|
| 47 |
if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $tableName)) != $tableName) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 48 |
// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 49 |
$table_sql = "CREATE TABLE {$tableName} ( |
| 50 |
id BIGINT(20) unsigned NOT NULL AUTO_INCREMENT, |
| 51 |
uri VARCHAR(255) NOT NULL, |
| 52 |
date_time DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', |
| 53 |
hits_count BIGINT(20) unsigned NOT NULL DEFAULT 1, |
| 54 |
user_agent VARCHAR(255) NOT NULL DEFAULT '', |
| 55 |
PRIMARY KEY id (id), |
| 56 |
KEY uri (uri(191)) |
| 57 |
) $collate;"; |
| 58 |
|
| 59 |
dbDelta($table_sql); |
| 60 |
} |
| 61 |
|
| 62 |
// Create Redirections Table |
| 63 |
require_once dirname(__FILE__, 2) . '/redirections/class-metasync-redirection-database.php'; |
| 64 |
$tableNameRedirection = esc_sql($wpdb->prefix . Metasync_Redirection_Database::$table_name); |
| 65 |
|
| 66 |
if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s ", $tableNameRedirection)) != $tableNameRedirection) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 67 |
// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 68 |
$table_sql = "CREATE TABLE {$wpdb->prefix}metasync_redirections ( |
| 69 |
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, |
| 70 |
sources_from TEXT NOT NULL, |
| 71 |
url_redirect_to TEXT NOT NULL, |
| 72 |
http_code SMALLINT(4) unsigned NOT NULL DEFAULT 301, |
| 73 |
hits_count BIGINT(20) unsigned NOT NULL DEFAULT '0', |
| 74 |
status VARCHAR(25) NOT NULL DEFAULT 'active', |
| 75 |
pattern_type ENUM('exact', 'contain', 'start', 'end', 'regex', 'wildcard') NOT NULL DEFAULT 'exact', |
| 76 |
regex_pattern TEXT NULL, |
| 77 |
description TEXT NULL, |
| 78 |
created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', |
| 79 |
updated_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', |
| 80 |
last_accessed_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', |
| 81 |
PRIMARY KEY id (id), |
| 82 |
KEY status (status), |
| 83 |
KEY pattern_type (pattern_type), |
| 84 |
KEY created_at (created_at), |
| 85 |
KEY idx_active_redirects (status, sources_from(191)) |
| 86 |
) $collate;"; |
| 87 |
|
| 88 |
dbDelta($table_sql); |
| 89 |
} else { |
| 90 |
// Check if new columns exist and add them if they don't |
| 91 |
$columns = $wpdb->get_col("DESCRIBE {$wpdb->prefix}metasync_redirections"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 92 |
|
| 93 |
if (!in_array('pattern_type', $columns)) { |
| 94 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD COLUMN pattern_type ENUM('exact', 'contain', 'start', 'end', 'regex', 'wildcard') NOT NULL DEFAULT 'exact' AFTER status"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 95 |
} |
| 96 |
|
| 97 |
if (!in_array('regex_pattern', $columns)) { |
| 98 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD COLUMN regex_pattern TEXT NULL AFTER pattern_type"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 99 |
} |
| 100 |
|
| 101 |
if (!in_array('description', $columns)) { |
| 102 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD COLUMN description TEXT NULL AFTER regex_pattern"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 103 |
} |
| 104 |
|
| 105 |
// Add indexes if they don't exist |
| 106 |
$indexes = $wpdb->get_results("SHOW INDEX FROM {$wpdb->prefix}metasync_redirections"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 107 |
$index_names = array_column($indexes, 'Key_name'); |
| 108 |
|
| 109 |
if (!in_array('pattern_type', $index_names)) { |
| 110 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD KEY pattern_type (pattern_type)"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 111 |
} |
| 112 |
|
| 113 |
if (!in_array('created_at', $index_names)) { |
| 114 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD KEY created_at (created_at)"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 115 |
} |
| 116 |
|
| 117 |
// PERFORMANCE OPTIMIZATION: Add composite index for active redirects lookup |
| 118 |
if (!in_array('idx_active_redirects', $index_names)) { |
| 119 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD KEY idx_active_redirects (status, sources_from(191))"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 120 |
} |
| 121 |
|
| 122 |
// Set default pattern_type for existing records |
| 123 |
$wpdb->query("UPDATE {$wpdb->prefix}metasync_redirections SET pattern_type = 'exact' WHERE pattern_type IS NULL OR pattern_type = ''"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 124 |
} |
| 125 |
|
| 126 |
// One-time migration: auto-enable external redirects if the site already has any |
| 127 |
if (!get_option('metasync_external_redirects_migrated')) { |
| 128 |
// Compare against every home-URL variant (http/https, www/non-www), |
| 129 |
// not just the exact home_url() string — otherwise same-site |
| 130 |
// destinations such as 'https://example.com/about' on a |
| 131 |
// www-prefixed site count as external and silently flip the setting. |
| 132 |
$home_host = wp_parse_url(home_url(), PHP_URL_HOST); |
| 133 |
$home_host = is_string($home_host) ? strtolower(preg_replace('/^www\./i', '', $home_host)) : ''; |
| 134 |
|
| 135 |
if ($home_host !== '') { |
| 136 |
// NOT LIKE per home-URL variant, preserving the original |
| 137 |
// scheme × host ordering (http/https × bare/www). |
| 138 |
$external_count = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 139 |
$wpdb->prepare( |
| 140 |
"SELECT COUNT(*) FROM {$wpdb->prefix}metasync_redirections |
| 141 |
WHERE url_redirect_to LIKE %s |
| 142 |
AND url_redirect_to NOT LIKE %s |
| 143 |
AND url_redirect_to NOT LIKE %s |
| 144 |
AND url_redirect_to NOT LIKE %s |
| 145 |
AND url_redirect_to NOT LIKE %s", |
| 146 |
'http%', |
| 147 |
$wpdb->esc_like('http://' . $home_host) . '%', |
| 148 |
$wpdb->esc_like('http://www.' . $home_host) . '%', |
| 149 |
$wpdb->esc_like('https://' . $home_host) . '%', |
| 150 |
$wpdb->esc_like('https://www.' . $home_host) . '%' |
| 151 |
) |
| 152 |
); |
| 153 |
} else { |
| 154 |
$external_count = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 155 |
$wpdb->prepare( |
| 156 |
"SELECT COUNT(*) FROM {$wpdb->prefix}metasync_redirections |
| 157 |
WHERE url_redirect_to LIKE %s |
| 158 |
AND url_redirect_to NOT LIKE %s", |
| 159 |
'http%', |
| 160 |
$wpdb->esc_like(trailingslashit(home_url())) . '%' |
| 161 |
) |
| 162 |
); |
| 163 |
} |
| 164 |
if ($external_count > 0) { |
| 165 |
update_option('metasync_allow_external_redirects', 1, true); |
| 166 |
} |
| 167 |
update_option('metasync_external_redirects_migrated', 1, true); |
| 168 |
} |
| 169 |
|
| 170 |
// Create HeartBeat Error Monitor Table |
| 171 |
require_once dirname(__FILE__, 2) . '/heartbeat-error-monitor/class-metasync-heartbeat-error-monitor-database.php'; |
| 172 |
$tableNameHeartBeatErrorMonitor = esc_sql($wpdb->prefix . Metasync_HeartBeat_Error_Monitor_Database::$table_name); |
| 173 |
|
| 174 |
if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s ", $tableNameHeartBeatErrorMonitor)) != $tableNameHeartBeatErrorMonitor) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 175 |
// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 176 |
$table_sql = "CREATE TABLE {$tableNameHeartBeatErrorMonitor} ( |
| 177 |
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, |
| 178 |
attribute_name VARCHAR(25) NOT NULL DEFAULT '', |
| 179 |
object_count VARCHAR(25) NOT NULL DEFAULT '', |
| 180 |
error_description TEXT NULL, |
| 181 |
created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', |
| 182 |
PRIMARY KEY id (id) |
| 183 |
) $collate;"; |
| 184 |
|
| 185 |
dbDelta($table_sql); |
| 186 |
} |
| 187 |
|
| 188 |
// Create Headless Refresh History Table |
| 189 |
require_once dirname(__FILE__, 2) . '/headless-refresh-history/class-metasync-headless-refresh-history-database.php'; |
| 190 |
$tableNameHeadlessRefreshHistory = esc_sql($wpdb->prefix . Metasync_Headless_Refresh_History::$table_name); |
| 191 |
if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s ", $tableNameHeadlessRefreshHistory)) != $tableNameHeadlessRefreshHistory) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 192 |
// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 193 |
$table_sql = "CREATE TABLE {$tableNameHeadlessRefreshHistory} ( |
| 194 |
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, |
| 195 |
object_type VARCHAR(20) NOT NULL DEFAULT '', |
| 196 |
object_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, |
| 197 |
url_hash CHAR(64) NOT NULL DEFAULT '', |
| 198 |
outcome VARCHAR(32) NOT NULL DEFAULT '', |
| 199 |
reason VARCHAR(64) NOT NULL DEFAULT '', |
| 200 |
duration_ms INT UNSIGNED NOT NULL DEFAULT 0, |
| 201 |
object_count SMALLINT UNSIGNED NOT NULL DEFAULT 0, |
| 202 |
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 203 |
PRIMARY KEY (id), |
| 204 |
KEY idx_created_at (created_at), |
| 205 |
KEY idx_object (object_type, object_id, created_at), |
| 206 |
KEY idx_outcome (outcome, created_at) |
| 207 |
) $collate;"; |
| 208 |
dbDelta($table_sql); |
| 209 |
} |
| 210 |
|
| 211 |
// Create Sync History Table |
| 212 |
require_once dirname(__FILE__, 2) . '/sync-history/class-metasync-sync-history-database.php'; |
| 213 |
$tableNameSyncHistory = esc_sql($wpdb->prefix . Metasync_Sync_History_Database::$table_name); |
| 214 |
|
| 215 |
if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s ", $tableNameSyncHistory)) != $tableNameSyncHistory) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 216 |
// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 217 |
$table_sql = "CREATE TABLE {$wpdb->prefix}metasync_sync_history ( |
| 218 |
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, |
| 219 |
title VARCHAR(255) NOT NULL DEFAULT '', |
| 220 |
source VARCHAR(50) NOT NULL DEFAULT '', |
| 221 |
status VARCHAR(25) NOT NULL DEFAULT 'draft', |
| 222 |
content_type VARCHAR(50) NOT NULL DEFAULT '', |
| 223 |
url TEXT NULL, |
| 224 |
meta_data TEXT NULL, |
| 225 |
created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', |
| 226 |
PRIMARY KEY id (id), |
| 227 |
KEY source (source), |
| 228 |
KEY status (status), |
| 229 |
KEY created_at (created_at), |
| 230 |
KEY idx_dedup (source, created_at), |
| 231 |
KEY idx_search (title(50), source, created_at) |
| 232 |
) $collate;"; |
| 233 |
|
| 234 |
dbDelta($table_sql); |
| 235 |
} else { |
| 236 |
// PERFORMANCE OPTIMIZATION: Add composite indexes to existing tables |
| 237 |
// Check and add indexes if they don't exist |
| 238 |
$indexes = $wpdb->get_results("SHOW INDEX FROM {$wpdb->prefix}metasync_sync_history"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 239 |
$index_names = array_column($indexes, 'Key_name'); |
| 240 |
|
| 241 |
// Add deduplication index (source, created_at) |
| 242 |
if (!in_array('idx_dedup', $index_names)) { |
| 243 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_sync_history ADD KEY idx_dedup (source, created_at)"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 244 |
} |
| 245 |
|
| 246 |
// Add search index (title(50), source, created_at) |
| 247 |
if (!in_array('idx_search', $index_names)) { |
| 248 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_sync_history ADD KEY idx_search (title(50), source, created_at)"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 249 |
} |
| 250 |
} |
| 251 |
|
| 252 |
// Create OTTO Excluded URLs Table |
| 253 |
require_once dirname(__FILE__, 2) . '/otto/class-metasync-otto-excluded-urls-database.php'; |
| 254 |
$tableNameOttoExcludedURLs = esc_sql($wpdb->prefix . Metasync_Otto_Excluded_URLs_Database::$table_name); |
| 255 |
|
| 256 |
if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s ", $tableNameOttoExcludedURLs)) != $tableNameOttoExcludedURLs) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 257 |
// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 258 |
$table_sql = "CREATE TABLE {$wpdb->prefix}metasync_otto_excluded_urls ( |
| 259 |
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, |
| 260 |
url_pattern TEXT NOT NULL, |
| 261 |
pattern_type ENUM('exact', 'contain', 'start', 'end', 'regex') NOT NULL DEFAULT 'exact', |
| 262 |
description TEXT NULL, |
| 263 |
status VARCHAR(25) NOT NULL DEFAULT 'active', |
| 264 |
is_permanent TINYINT(1) NOT NULL DEFAULT 0, |
| 265 |
auto_excluded TINYINT(1) NOT NULL DEFAULT 0, |
| 266 |
recheck_after DATETIME NULL DEFAULT NULL, |
| 267 |
created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', |
| 268 |
PRIMARY KEY id (id), |
| 269 |
KEY status (status), |
| 270 |
KEY pattern_type (pattern_type), |
| 271 |
KEY created_at (created_at), |
| 272 |
KEY is_permanent (is_permanent), |
| 273 |
KEY auto_excluded (auto_excluded), |
| 274 |
KEY recheck_after (recheck_after), |
| 275 |
UNIQUE KEY url_pattern_type_unique (url_pattern(191), pattern_type) |
| 276 |
) $collate;"; |
| 277 |
|
| 278 |
dbDelta($table_sql); |
| 279 |
} |
| 280 |
|
| 281 |
// Create Robots.txt Backups Table |
| 282 |
require_once dirname(__FILE__, 2) . '/robots-txt/class-metasync-robots-txt-database.php'; |
| 283 |
$robots_db = Metasync_Robots_Txt_Database::get_instance(); |
| 284 |
$table_name_robots = esc_sql($wpdb->prefix . 'metasync_robots_txt_backups'); |
| 285 |
if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name_robots)) != $table_name_robots) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 286 |
$robots_db->create_table(); |
| 287 |
} |
| 288 |
|
| 289 |
} |
| 290 |
|
| 291 |
/** |
| 292 |
* deactivation of migration. |
| 293 |
*/ |
| 294 |
public static function deactivation() |
| 295 |
{ |
| 296 |
global $wpdb; |
| 297 |
// require_once dirname(__FILE__, 2) . '/404-monitor/class-metasync-404-monitor-database.php'; |
| 298 |
// $tableName = esc_sql($wpdb->prefix . Metasync_Error_Monitor_Database::$table_name); |
| 299 |
|
| 300 |
/* drop wp_metasync_404_logs table */ |
| 301 |
// $sql = "DROP TABLE IF EXISTS `$tableName` "; |
| 302 |
// $wpdb->query($sql); |
| 303 |
|
| 304 |
// require_once dirname(__FILE__, 2) . '/redirections/class-metasync-redirection-database.php'; |
| 305 |
// $tableNameRedirection = esc_sql($wpdb->prefix . Metasync_Redirection_Database::$table_name); |
| 306 |
|
| 307 |
/* drop wp_metasync_redirections table */ |
| 308 |
// $sql = "DROP TABLE IF EXISTS `$tableNameRedirection` "; |
| 309 |
// $wpdb->query($sql); |
| 310 |
|
| 311 |
// Data tables are preserved on deactivation so a deactivate/reactivate cycle |
| 312 |
// does not silently destroy logged data (e.g. heartbeat error history). |
| 313 |
// Removal belongs to uninstall, not deactivation. |
| 314 |
// require_once dirname(__FILE__, 2) . '/heartbeat-error-monitor/class-metasync-heartbeat-error-monitor-database.php'; |
| 315 |
// $tableNameHeartBeatErrorMonitor = esc_sql($wpdb->prefix . Metasync_HeartBeat_Error_Monitor_Database::$table_name); |
| 316 |
/* drop wp_metasync_heartbeat_error_logs table */ |
| 317 |
// $sql = "DROP TABLE IF EXISTS `$tableNameHeartBeatErrorMonitor` "; |
| 318 |
// $wpdb->query($sql); |
| 319 |
} |
| 320 |
|
| 321 |
/** |
| 322 |
* Run version-specific migrations |
| 323 |
*/ |
| 324 |
public static function run_version_migrations($from_version, $to_version) |
| 325 |
{ |
| 326 |
// If from_version is 9.9.9, always run all migrations |
| 327 |
$force_run = ($from_version === '9.9.9'); |
| 328 |
|
| 329 |
// Migration for versions 2.5.4+ - Enhanced 404 monitor and redirections |
| 330 |
if ($force_run || version_compare($to_version, '2.5.4', '>=')) { |
| 331 |
self::migrate_enhanced_features_v2_5_4(); |
| 332 |
} |
| 333 |
|
| 334 |
// Migration for versions 2.5.6+ - Robots.txt management |
| 335 |
if ($force_run || version_compare($to_version, '2.5.6', '>=')) { |
| 336 |
self::migrate_robots_txt_v2_5_6(); |
| 337 |
} |
| 338 |
|
| 339 |
// Migration for versions 2.5.9+ - OTTO Excluded URLs |
| 340 |
if ($force_run || version_compare($to_version, '2.5.9', '>=')) { |
| 341 |
self::migrate_otto_excluded_urls_v2_5_9(); |
| 342 |
} |
| 343 |
|
| 344 |
// Migration for versions 2.5.20+ - Remove insecure wp-config.php backup copies from the web root |
| 345 |
if ($force_run || version_compare($to_version, '2.5.20', '>=')) { |
| 346 |
self::migrate_remove_wpconfig_backups_v2_5_20(); |
| 347 |
} |
| 348 |
|
| 349 |
// Add more version-specific migrations here as needed |
| 350 |
// if (version_compare($from_version, '1.1.0', '<')) { |
| 351 |
// self::migrate_something_v1_1(); |
| 352 |
// } |
| 353 |
} |
| 354 |
|
| 355 |
/** |
| 356 |
* Migrate enhanced features for version 2.5.4+ |
| 357 |
*/ |
| 358 |
private static function migrate_enhanced_features_v2_5_4() |
| 359 |
{ |
| 360 |
global $wpdb; |
| 361 |
$collate = $wpdb->get_charset_collate(); |
| 362 |
|
| 363 |
// Load WordPress upgrade functions for dbDelta |
| 364 |
require_once ABSPATH . 'wp-admin/includes/upgrade.php'; |
| 365 |
|
| 366 |
// Enhanced 404 Error Monitor Table |
| 367 |
require_once dirname(__FILE__, 2) . '/404-monitor/class-metasync-404-monitor-database.php'; |
| 368 |
$tableName404Monitor = esc_sql($wpdb->prefix . Metasync_Error_Monitor_Database::$table_name); |
| 369 |
|
| 370 |
if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s ", $tableName404Monitor)) != $tableName404Monitor) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 371 |
// Table doesn't exist, create enhanced version |
| 372 |
// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 373 |
$table_sql = "CREATE TABLE {$wpdb->prefix}metasync_404_logs ( |
| 374 |
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, |
| 375 |
uri TEXT NOT NULL, |
| 376 |
hits_count BIGINT(20) unsigned NOT NULL DEFAULT '1', |
| 377 |
date_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 378 |
user_agent TEXT NULL, |
| 379 |
referer TEXT NULL, |
| 380 |
ip_address VARCHAR(45) NULL, |
| 381 |
PRIMARY KEY id (id), |
| 382 |
KEY uri (uri(191)), |
| 383 |
KEY hits_count (hits_count), |
| 384 |
KEY date_time (date_time) |
| 385 |
) $collate;"; |
| 386 |
|
| 387 |
dbDelta($table_sql); |
| 388 |
} else { |
| 389 |
// Table exists, check for missing columns and add them |
| 390 |
$columns = $wpdb->get_col("DESCRIBE {$wpdb->prefix}metasync_404_logs"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 391 |
|
| 392 |
// Add referer column if it doesn't exist |
| 393 |
if (!in_array('referer', $columns)) { |
| 394 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_404_logs ADD COLUMN referer TEXT NULL AFTER user_agent"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 395 |
} |
| 396 |
|
| 397 |
// Add ip_address column if it doesn't exist |
| 398 |
if (!in_array('ip_address', $columns)) { |
| 399 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_404_logs ADD COLUMN ip_address VARCHAR(45) NULL AFTER referer"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 400 |
} |
| 401 |
|
| 402 |
// Update uri column to TEXT if it's VARCHAR(255) |
| 403 |
$uri_column = $wpdb->get_row("SHOW COLUMNS FROM {$wpdb->prefix}metasync_404_logs LIKE 'uri'"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 404 |
if ($uri_column && strpos($uri_column->Type, 'varchar') !== false) { |
| 405 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_404_logs MODIFY COLUMN uri TEXT NOT NULL"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 406 |
} |
| 407 |
|
| 408 |
// Update user_agent column to TEXT if it's VARCHAR(255) |
| 409 |
$ua_column = $wpdb->get_row("SHOW COLUMNS FROM {$wpdb->prefix}metasync_404_logs LIKE 'user_agent'"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 410 |
if ($ua_column && strpos($ua_column->Type, 'varchar') !== false) { |
| 411 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_404_logs MODIFY COLUMN user_agent TEXT NULL"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 412 |
} |
| 413 |
|
| 414 |
// Add missing indexes |
| 415 |
$indexes = $wpdb->get_results("SHOW INDEX FROM {$wpdb->prefix}metasync_404_logs"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 416 |
$index_names = array_column($indexes, 'Key_name'); |
| 417 |
|
| 418 |
if (!in_array('hits_count', $index_names)) { |
| 419 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_404_logs ADD KEY hits_count (hits_count)"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 420 |
} |
| 421 |
|
| 422 |
if (!in_array('date_time', $index_names)) { |
| 423 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_404_logs ADD KEY date_time (date_time)"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 424 |
} |
| 425 |
} |
| 426 |
|
| 427 |
// Enhanced Redirections Table with new columns |
| 428 |
require_once dirname(__FILE__, 2) . '/redirections/class-metasync-redirection-database.php'; |
| 429 |
$tableNameRedirection = esc_sql($wpdb->prefix . Metasync_Redirection_Database::$table_name); |
| 430 |
|
| 431 |
if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s ", $tableNameRedirection)) == $tableNameRedirection) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 432 |
// Table exists, check for new columns |
| 433 |
$columns = $wpdb->get_col("DESCRIBE {$wpdb->prefix}metasync_redirections"); |
| 434 |
|
| 435 |
// Add pattern_type column if it doesn't exist |
| 436 |
if (!in_array('pattern_type', $columns)) { |
| 437 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD COLUMN pattern_type ENUM('exact', 'contain', 'start', 'end', 'regex', 'wildcard') NOT NULL DEFAULT 'exact' AFTER status"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 438 |
} |
| 439 |
|
| 440 |
// Add regex_pattern column if it doesn't exist |
| 441 |
if (!in_array('regex_pattern', $columns)) { |
| 442 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD COLUMN regex_pattern TEXT NULL AFTER pattern_type"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 443 |
} |
| 444 |
|
| 445 |
// Add description column if it doesn't exist |
| 446 |
if (!in_array('description', $columns)) { |
| 447 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD COLUMN description TEXT NULL AFTER regex_pattern"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 448 |
} |
| 449 |
|
| 450 |
// Add timestamp columns if they don't exist |
| 451 |
if (!in_array('created_at', $columns)) { |
| 452 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD COLUMN created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER description"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 453 |
} |
| 454 |
|
| 455 |
if (!in_array('updated_at', $columns)) { |
| 456 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD COLUMN updated_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER created_at"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 457 |
} |
| 458 |
|
| 459 |
if (!in_array('last_accessed_at', $columns)) { |
| 460 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD COLUMN last_accessed_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER updated_at"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 461 |
} |
| 462 |
|
| 463 |
// Add indexes if they don't exist |
| 464 |
$indexes = $wpdb->get_results("SHOW INDEX FROM {$wpdb->prefix}metasync_redirections"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 465 |
$index_names = array_column($indexes, 'Key_name'); |
| 466 |
|
| 467 |
if (!in_array('pattern_type', $index_names)) { |
| 468 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD KEY pattern_type (pattern_type)"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 469 |
} |
| 470 |
|
| 471 |
if (!in_array('created_at', $index_names)) { |
| 472 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD KEY created_at (created_at)"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 473 |
} |
| 474 |
|
| 475 |
// Set default pattern_type for existing records |
| 476 |
$wpdb->query("UPDATE {$wpdb->prefix}metasync_redirections SET pattern_type = 'exact' WHERE pattern_type IS NULL OR pattern_type = ''"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 477 |
} |
| 478 |
} |
| 479 |
|
| 480 |
/** |
| 481 |
* Migrate robots.txt management for version 2.5.6+ |
| 482 |
*/ |
| 483 |
private static function migrate_robots_txt_v2_5_6() |
| 484 |
{ |
| 485 |
global $wpdb; |
| 486 |
|
| 487 |
// Create Robots.txt Backups Table |
| 488 |
require_once dirname(__FILE__, 2) . '/robots-txt/class-metasync-robots-txt-database.php'; |
| 489 |
$robots_db = Metasync_Robots_Txt_Database::get_instance(); |
| 490 |
$table_name = esc_sql($wpdb->prefix . 'metasync_robots_txt_backups'); |
| 491 |
|
| 492 |
// Check if table already exists |
| 493 |
if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)) != $table_name) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 494 |
// Table doesn't exist, create it |
| 495 |
$robots_db->create_table(); |
| 496 |
} |
| 497 |
} |
| 498 |
|
| 499 |
/** |
| 500 |
* Migrate OTTO Excluded URLs for version 2.5.9+ |
| 501 |
*/ |
| 502 |
private static function migrate_otto_excluded_urls_v2_5_9() |
| 503 |
{ |
| 504 |
global $wpdb; |
| 505 |
$collate = $wpdb->get_charset_collate(); |
| 506 |
|
| 507 |
// Load WordPress upgrade functions for dbDelta |
| 508 |
require_once ABSPATH . 'wp-admin/includes/upgrade.php'; |
| 509 |
|
| 510 |
// Create OTTO Excluded URLs Table |
| 511 |
require_once dirname(__FILE__, 2) . '/otto/class-metasync-otto-excluded-urls-database.php'; |
| 512 |
$tableNameOttoExcludedURLs = esc_sql($wpdb->prefix . Metasync_Otto_Excluded_URLs_Database::$table_name); |
| 513 |
|
| 514 |
// Check if table already exists |
| 515 |
if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $tableNameOttoExcludedURLs)) != $tableNameOttoExcludedURLs) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 516 |
// Table doesn't exist, create it |
| 517 |
// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 518 |
$table_sql = "CREATE TABLE {$wpdb->prefix}metasync_otto_excluded_urls ( |
| 519 |
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, |
| 520 |
url_pattern TEXT NOT NULL, |
| 521 |
pattern_type ENUM('exact', 'contain', 'start', 'end', 'regex') NOT NULL DEFAULT 'exact', |
| 522 |
description TEXT NULL, |
| 523 |
status VARCHAR(25) NOT NULL DEFAULT 'active', |
| 524 |
is_permanent TINYINT(1) NOT NULL DEFAULT 0, |
| 525 |
auto_excluded TINYINT(1) NOT NULL DEFAULT 0, |
| 526 |
recheck_after DATETIME NULL DEFAULT NULL, |
| 527 |
created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', |
| 528 |
PRIMARY KEY id (id), |
| 529 |
KEY status (status), |
| 530 |
KEY pattern_type (pattern_type), |
| 531 |
KEY created_at (created_at), |
| 532 |
KEY is_permanent (is_permanent), |
| 533 |
KEY auto_excluded (auto_excluded), |
| 534 |
KEY status_auto_excluded (status, auto_excluded), |
| 535 |
KEY recheck_after (recheck_after), |
| 536 |
UNIQUE KEY url_pattern_type_unique (url_pattern(191), pattern_type) |
| 537 |
) $collate;"; |
| 538 |
|
| 539 |
dbDelta($table_sql); |
| 540 |
|
| 541 |
// Log successful migration |
| 542 |
// error_log('MetaSync: OTTO Excluded URLs table created successfully (v2.5.9)'); |
| 543 |
} else { |
| 544 |
// Table exists, verify structure and add any missing columns if needed |
| 545 |
$columns = $wpdb->get_col("DESCRIBE {$wpdb->prefix}metasync_otto_excluded_urls"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 546 |
|
| 547 |
// Check for required columns and add if missing |
| 548 |
$missing_columns = false; |
| 549 |
|
| 550 |
if (!in_array('pattern_type', $columns)) { |
| 551 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_otto_excluded_urls ADD COLUMN pattern_type ENUM('exact', 'contain', 'start', 'end', 'regex') NOT NULL DEFAULT 'exact' AFTER url_pattern"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 552 |
$missing_columns = true; |
| 553 |
} |
| 554 |
|
| 555 |
if (!in_array('description', $columns)) { |
| 556 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_otto_excluded_urls ADD COLUMN description TEXT NULL AFTER pattern_type"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 557 |
$missing_columns = true; |
| 558 |
} |
| 559 |
|
| 560 |
if (!in_array('status', $columns)) { |
| 561 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_otto_excluded_urls ADD COLUMN status VARCHAR(25) NOT NULL DEFAULT 'active' AFTER description"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 562 |
$missing_columns = true; |
| 563 |
} |
| 564 |
|
| 565 |
if (!in_array('is_permanent', $columns)) { |
| 566 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_otto_excluded_urls ADD COLUMN is_permanent TINYINT(1) NOT NULL DEFAULT 0 AFTER status"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 567 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_otto_excluded_urls ADD KEY is_permanent (is_permanent)"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 568 |
} |
| 569 |
|
| 570 |
if (!in_array('auto_excluded', $columns)) { |
| 571 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_otto_excluded_urls ADD COLUMN auto_excluded TINYINT(1) NOT NULL DEFAULT 0 AFTER is_permanent"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 572 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_otto_excluded_urls ADD KEY auto_excluded (auto_excluded)"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 573 |
// Backfill: mark existing 404 exclusions as auto_excluded |
| 574 |
$wpdb->query("UPDATE {$wpdb->prefix}metasync_otto_excluded_urls SET auto_excluded = 1 WHERE description = 'Auto-excluded: 404'"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 575 |
} |
| 576 |
|
| 577 |
if (!in_array('recheck_after', $columns)) { |
| 578 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_otto_excluded_urls ADD COLUMN recheck_after DATETIME NULL DEFAULT NULL AFTER auto_excluded"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 579 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_otto_excluded_urls ADD KEY recheck_after (recheck_after)"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 580 |
// Backfill: set recheck_after = created_at + 7 days for auto-excluded URLs |
| 581 |
$wpdb->query("UPDATE {$wpdb->prefix}metasync_otto_excluded_urls SET recheck_after = DATE_ADD(created_at, INTERVAL 7 DAY) WHERE auto_excluded = 1 AND (recheck_after IS NULL OR recheck_after = '0000-00-00 00:00:00')"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 582 |
} |
| 583 |
|
| 584 |
// Check and add indexes if they don't exist |
| 585 |
$indexes = $wpdb->get_results("SHOW INDEX FROM {$wpdb->prefix}metasync_otto_excluded_urls"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 586 |
$index_names = array_column($indexes, 'Key_name'); |
| 587 |
|
| 588 |
if (!in_array('status', $index_names)) { |
| 589 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_otto_excluded_urls ADD KEY status (status)"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 590 |
} |
| 591 |
|
| 592 |
if (!in_array('pattern_type', $index_names)) { |
| 593 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_otto_excluded_urls ADD KEY pattern_type (pattern_type)"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 594 |
} |
| 595 |
|
| 596 |
if (!in_array('created_at', $index_names)) { |
| 597 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_otto_excluded_urls ADD KEY created_at (created_at)"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 598 |
} |
| 599 |
|
| 600 |
// Add unique index on url_pattern + pattern_type to prevent duplicates at database level |
| 601 |
// Note: TEXT columns need a prefix length for indexing (767 is max for UTF8) |
| 602 |
if (!in_array('url_pattern_type_unique', $index_names)) { |
| 603 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_otto_excluded_urls ADD UNIQUE KEY url_pattern_type_unique (url_pattern(191), pattern_type)"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 604 |
} |
| 605 |
|
| 606 |
// Composite index for the cache-miss path in metasync_is_otto_url_manually_excluded() |
| 607 |
if (!in_array('status_auto_excluded', $index_names)) { |
| 608 |
$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_otto_excluded_urls ADD KEY status_auto_excluded (status, auto_excluded)"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 609 |
} |
| 610 |
|
| 611 |
// if ($missing_columns) { |
| 612 |
// error_log('MetaSync: OTTO Excluded URLs table structure updated (v2.5.9)'); |
| 613 |
// } |
| 614 |
} |
| 615 |
} |
| 616 |
|
| 617 |
/** |
| 618 |
* One-time cleanup of canonical meta corrupted to the literal |
| 619 |
* "Array" (and its esc_url'd forms "http://Array" / "https://Array"). |
| 620 |
* |
| 621 |
* Deletions are exact-match only — a legitimate URL can never match. |
| 622 |
* Also repairs legacy rows still stored as (possibly nested) serialized |
| 623 |
* arrays, and clears the mirrored corruption from Yoast / RankMath / |
| 624 |
* AIOSEO storage that plugin-sync propagated, so cleaned sites are not |
| 625 |
* re-polluted by stale third-party caches. Idempotent by construction. |
| 626 |
*/ |
| 627 |
public static function cleanup_corrupted_canonicals() |
| 628 |
{ |
| 629 |
global $wpdb; |
| 630 |
|
| 631 |
$meta_keys = array('meta_canonical', '_metasync_canonical_url', '_yoast_wpseo_canonical', 'rank_math_canonical_url'); |
| 632 |
$bad_values = array('array', 'http://array', 'https://array'); |
| 633 |
|
| 634 |
// 1. Post meta + term meta: delete exact-match corrupted rows. |
| 635 |
// Batched and deleted by primary key so huge postmeta tables aren't |
| 636 |
// range-locked in one statement, with per-object meta-cache |
| 637 |
// invalidation — raw SQL alone would leave persistent object caches |
| 638 |
// (Redis/Memcached) serving the deleted value to Yoast/RankMath |
| 639 |
// readers indefinitely. The two targets are written out explicitly |
| 640 |
// so every table/column identifier is a literal in the statement. |
| 641 |
// |
| 642 |
// Normalized comparison: trailing slashes stripped in SQL so |
| 643 |
// "http://Array/" and "http://Array//" both match the literals. |
| 644 |
|
| 645 |
// 1a. Post meta. |
| 646 |
for ($batch = 0; $batch < 50; $batch++) { |
| 647 |
$rows = $wpdb->get_results($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 648 |
'SELECT meta_id, post_id AS object_id FROM ' . $wpdb->postmeta |
| 649 |
. ' WHERE meta_key IN (' . implode(',', array_fill(0, count($meta_keys), '%s')) . ')' |
| 650 |
. " AND LOWER(TRIM(TRAILING '/' FROM TRIM(meta_value)))" |
| 651 |
. ' IN (' . implode(',', array_fill(0, count($bad_values), '%s')) . ')' |
| 652 |
. ' ORDER BY meta_id LIMIT 500', |
| 653 |
array_merge($meta_keys, $bad_values) |
| 654 |
)); |
| 655 |
if (empty($rows)) { |
| 656 |
break; |
| 657 |
} |
| 658 |
$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 659 |
$wpdb->prepare( |
| 660 |
'DELETE FROM ' . $wpdb->postmeta |
| 661 |
. ' WHERE meta_id IN (' . implode(',', array_fill(0, count($rows), '%d')) . ')', |
| 662 |
wp_list_pluck($rows, 'meta_id') |
| 663 |
) |
| 664 |
); |
| 665 |
foreach ($rows as $row) { |
| 666 |
wp_cache_delete((int) $row->object_id, 'post_meta'); |
| 667 |
} |
| 668 |
if (count($rows) < 500) { |
| 669 |
break; |
| 670 |
} |
| 671 |
} |
| 672 |
|
| 673 |
// 1b. Term meta. |
| 674 |
for ($batch = 0; $batch < 50; $batch++) { |
| 675 |
$rows = $wpdb->get_results($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 676 |
'SELECT meta_id, term_id AS object_id FROM ' . $wpdb->termmeta |
| 677 |
. ' WHERE meta_key IN (' . implode(',', array_fill(0, count($meta_keys), '%s')) . ')' |
| 678 |
. " AND LOWER(TRIM(TRAILING '/' FROM TRIM(meta_value)))" |
| 679 |
. ' IN (' . implode(',', array_fill(0, count($bad_values), '%s')) . ')' |
| 680 |
. ' ORDER BY meta_id LIMIT 500', |
| 681 |
array_merge($meta_keys, $bad_values) |
| 682 |
)); |
| 683 |
if (empty($rows)) { |
| 684 |
break; |
| 685 |
} |
| 686 |
$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 687 |
$wpdb->prepare( |
| 688 |
'DELETE FROM ' . $wpdb->termmeta |
| 689 |
. ' WHERE meta_id IN (' . implode(',', array_fill(0, count($rows), '%d')) . ')', |
| 690 |
wp_list_pluck($rows, 'meta_id') |
| 691 |
) |
| 692 |
); |
| 693 |
foreach ($rows as $row) { |
| 694 |
wp_cache_delete((int) $row->object_id, 'term_meta'); |
| 695 |
} |
| 696 |
if (count($rows) < 500) { |
| 697 |
break; |
| 698 |
} |
| 699 |
} |
| 700 |
|
| 701 |
// 2. Rows still stored as serialized arrays (the raw material the |
| 702 |
// "Array" casts came from): repair MetaSync's own keys to the first |
| 703 |
// usable URL inside; third-party keys are delete-only (never invent |
| 704 |
// a value inside another plugin's storage). Written with direct SQL |
| 705 |
// by meta_id so the updated_post_meta plugin-sync cascade, Yoast |
| 706 |
// indexable rebuilds, and sitemap cache busts don't fire once per |
| 707 |
// row; loops until exhausted (repaired rows stop matching LIKE). |
| 708 |
if (class_exists('Metasync_Canonical_Sanitizer')) { |
| 709 |
$own_keys = array('meta_canonical', '_metasync_canonical_url'); |
| 710 |
for ($batch = 0; $batch < 50; $batch++) { |
| 711 |
$rows = $wpdb->get_results($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 712 |
'SELECT meta_id, post_id, meta_key, meta_value FROM ' . $wpdb->postmeta |
| 713 |
. ' WHERE meta_key IN (' . implode(',', array_fill(0, count($meta_keys), '%s')) . ')' |
| 714 |
. " AND meta_value LIKE %s" |
| 715 |
. ' ORDER BY meta_id LIMIT 500', |
| 716 |
array_merge($meta_keys, array('a:%')) |
| 717 |
)); |
| 718 |
if (empty($rows)) { |
| 719 |
break; |
| 720 |
} |
| 721 |
foreach ($rows as $row) { |
| 722 |
$repaired = ''; |
| 723 |
if (in_array($row->meta_key, $own_keys, true)) { |
| 724 |
$repaired = Metasync_Canonical_Sanitizer::sanitize(maybe_unserialize($row->meta_value)); |
| 725 |
} |
| 726 |
if ($repaired !== '') { |
| 727 |
$wpdb->update($wpdb->postmeta, array('meta_value' => $repaired), array('meta_id' => (int) $row->meta_id)); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 728 |
} else { |
| 729 |
$wpdb->delete($wpdb->postmeta, array('meta_id' => (int) $row->meta_id)); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 730 |
} |
| 731 |
wp_cache_delete((int) $row->post_id, 'post_meta'); |
| 732 |
} |
| 733 |
if (count($rows) < 500) { |
| 734 |
break; |
| 735 |
} |
| 736 |
} |
| 737 |
} |
| 738 |
|
| 739 |
// 3. Yoast indexable cache: null corrupted canonical columns so the |
| 740 |
// frontend and sitemaps stop serving the bad value immediately. |
| 741 |
$indexable_table = $wpdb->prefix . 'yoast_indexable'; |
| 742 |
if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($indexable_table))) === $indexable_table) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 743 |
$wpdb->query($wpdb->prepare( |
| 744 |
'UPDATE ' . $wpdb->prefix . 'yoast_indexable SET canonical = NULL' |
| 745 |
. " WHERE LOWER(TRIM(TRAILING '/' FROM TRIM(canonical)))" |
| 746 |
. ' IN (' . implode(',', array_fill(0, count($bad_values), '%s')) . ')', |
| 747 |
$bad_values |
| 748 |
)); |
| 749 |
} |
| 750 |
|
| 751 |
// 4. AIOSEO custom tables: null corrupted canonical_url columns. |
| 752 |
// Both table names are fixed literals, written out explicitly. |
| 753 |
$aioseo_posts = $wpdb->prefix . 'aioseo_posts'; |
| 754 |
if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($aioseo_posts))) === $aioseo_posts) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 755 |
$wpdb->query($wpdb->prepare( |
| 756 |
'UPDATE ' . $wpdb->prefix . 'aioseo_posts SET canonical_url = NULL' |
| 757 |
. " WHERE LOWER(TRIM(TRAILING '/' FROM TRIM(canonical_url)))" |
| 758 |
. ' IN (' . implode(',', array_fill(0, count($bad_values), '%s')) . ')', |
| 759 |
$bad_values |
| 760 |
)); |
| 761 |
} |
| 762 |
$aioseo_terms = $wpdb->prefix . 'aioseo_terms'; |
| 763 |
if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($aioseo_terms))) === $aioseo_terms) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- activation/upgrade migration routine — schema changes are gated to activation and version bumps, never ordinary requests |
| 764 |
$wpdb->query($wpdb->prepare( |
| 765 |
'UPDATE ' . $wpdb->prefix . 'aioseo_terms SET canonical_url = NULL' |
| 766 |
. " WHERE LOWER(TRIM(TRAILING '/' FROM TRIM(canonical_url)))" |
| 767 |
. ' IN (' . implode(',', array_fill(0, count($bad_values), '%s')) . ')', |
| 768 |
$bad_values |
| 769 |
)); |
| 770 |
} |
| 771 |
|
| 772 |
// 5. Yoast stores term canonicals in the wpseo_taxonomy_meta option, |
| 773 |
// not termmeta. Strip only exact corruption literals. |
| 774 |
if (class_exists('Metasync_Canonical_Sanitizer')) { |
| 775 |
$tax_meta = get_option('wpseo_taxonomy_meta'); |
| 776 |
if (is_array($tax_meta)) { |
| 777 |
$changed = false; |
| 778 |
foreach ($tax_meta as $taxonomy => $terms) { |
| 779 |
if (!is_array($terms)) { |
| 780 |
continue; |
| 781 |
} |
| 782 |
foreach ($terms as $term_id => $fields) { |
| 783 |
if (is_array($fields) && isset($fields['wpseo_canonical']) |
| 784 |
&& Metasync_Canonical_Sanitizer::is_corrupted($fields['wpseo_canonical'])) { |
| 785 |
unset($tax_meta[$taxonomy][$term_id]['wpseo_canonical']); |
| 786 |
$changed = true; |
| 787 |
} |
| 788 |
} |
| 789 |
} |
| 790 |
if ($changed) { |
| 791 |
update_option('wpseo_taxonomy_meta', $tax_meta); |
| 792 |
} |
| 793 |
} |
| 794 |
} |
| 795 |
} |
| 796 |
|
| 797 |
/** |
| 798 |
* One-time repair of Local Business logo values corrupted to |
| 799 |
* "http://<attachment-id>" (and the https:// / trailing-slash variants). |
| 800 |
* |
| 801 |
* The legacy save path ran the logo through |
| 802 |
* sanitize_url() (= esc_url_raw()), which prepends a scheme to any value |
| 803 |
* that has none — so a stored attachment ID "45589" became "http://45589", |
| 804 |
* the admin preview rendered a broken image, and the front-end JSON-LD |
| 805 |
* published "logo": "http://45589" as structured data. |
| 806 |
* |
| 807 |
* The corrupted shape is unambiguous (a scheme followed by digits only — no |
| 808 |
* dot, no path, so it can never be a resolvable host) and encodes the |
| 809 |
* original ID exactly, so restoring the digits is lossless and needs no |
| 810 |
* rollback path. Idempotent by construction: repaired values no longer |
| 811 |
* match, so a second run writes nothing. |
| 812 |
* |
| 813 |
* Uses the same shared helper as the admin preview and the schema output |
| 814 |
* (metasync_repair_scheme_prefixed_media_id()), so the three can never |
| 815 |
* disagree about what "corrupted" means. |
| 816 |
* |
| 817 |
* @see metasync_repair_scheme_prefixed_media_id() |
| 818 |
*/ |
| 819 |
public static function repair_corrupted_local_seo_logo() |
| 820 |
{ |
| 821 |
$options = get_option('metasync_options'); |
| 822 |
|
| 823 |
if (!is_array($options) || !isset($options['localseo']['local_seo_logo'])) { |
| 824 |
return; |
| 825 |
} |
| 826 |
|
| 827 |
$stored = $options['localseo']['local_seo_logo']; |
| 828 |
$repaired = metasync_repair_scheme_prefixed_media_id($stored); |
| 829 |
|
| 830 |
if ($repaired === $stored) { |
| 831 |
return; // already clean: URL, plain attachment ID, or empty |
| 832 |
} |
| 833 |
|
| 834 |
$options['localseo']['local_seo_logo'] = $repaired; |
| 835 |
update_option('metasync_options', $options, true); |
| 836 |
} |
| 837 |
|
| 838 |
|
| 839 |
/** |
| 840 |
* Remove insecure wp-config.php backup copies left in the web root by prior versions. |
| 841 |
* |
| 842 |
* Older versions wrote a full copy of wp-config.php (containing DB credentials and |
| 843 |
* auth salts) to `wp-config.php.metasync-backup-<time()>` in the same directory as |
| 844 |
* wp-config.php before each debug-mode write. This cleanup deletes any such leftover |
| 845 |
* copies, plus any `.metasync-tmp-*` file orphaned by an interrupted atomic save. |
| 846 |
* |
| 847 |
* Claimed via an option so it runs once rather than on every later version bump, and |
| 848 |
* left unclaimed if anything could not be deleted so a later upgrade retries. |
| 849 |
* |
| 850 |
* @return void |
| 851 |
*/ |
| 852 |
private static function migrate_remove_wpconfig_backups_v2_5_20() |
| 853 |
{ |
| 854 |
if (get_option(self::WPCONFIG_BACKUP_CLEANUP_OPTION)) { |
| 855 |
return; |
| 856 |
} |
| 857 |
|
| 858 |
// This runs from `init` at priority 1, so a wp_dlct_config_file_manager_path filter |
| 859 |
// another plugin registers at the default priority is not added yet and the filtered |
| 860 |
// value can still be the default. Sweep every candidate directory rather than |
| 861 |
// trusting one resolution, and only claim the cleanup once wp-config.php was |
| 862 |
// actually found in one of them - otherwise a later upgrade retries. |
| 863 |
$candidates = [ |
| 864 |
ABSPATH . 'wp-config.php', |
| 865 |
dirname(ABSPATH) . '/wp-config.php', |
| 866 |
apply_filters('wp_dlct_config_file_manager_path', ABSPATH . 'wp-config.php'), |
| 867 |
]; |
| 868 |
|
| 869 |
$sweptDirs = []; |
| 870 |
$located = false; |
| 871 |
$failed = 0; |
| 872 |
$scan_failed = 0; |
| 873 |
|
| 874 |
foreach ($candidates as $config_file) { |
| 875 |
if (!is_string($config_file) || '' === $config_file) { |
| 876 |
continue; |
| 877 |
} |
| 878 |
|
| 879 |
$config_dir = dirname($config_file); |
| 880 |
if (isset($sweptDirs[$config_dir])) { |
| 881 |
continue; |
| 882 |
} |
| 883 |
$sweptDirs[$config_dir] = true; |
| 884 |
|
| 885 |
if (@file_exists($config_file)) { |
| 886 |
$located = true; |
| 887 |
} |
| 888 |
|
| 889 |
$result = self::remove_wpconfig_backups($config_dir); |
| 890 |
$failed += $result['failed']; |
| 891 |
$scan_failed += $result['scan_failed']; |
| 892 |
} |
| 893 |
|
| 894 |
if ($scan_failed) { |
| 895 |
error_log(sprintf( // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- genuine failure path, bounded, no secrets |
| 896 |
'MetaSync: could not inspect %d wp-config.php backup directory scan(s); cleanup will be retried.', |
| 897 |
$scan_failed |
| 898 |
)); |
| 899 |
return; |
| 900 |
} |
| 901 |
|
| 902 |
if ($failed) { |
| 903 |
// A copy left behind is exactly the exposure this cleanup exists to remove, |
| 904 |
// so surface it rather than failing silently. |
| 905 |
error_log(sprintf( // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- genuine failure path, bounded, no secrets |
| 906 |
'MetaSync: could not delete %d leftover wp-config.php copy/copies in %s - remove them manually.', |
| 907 |
$failed, |
| 908 |
implode(', ', array_keys($sweptDirs)) |
| 909 |
)); |
| 910 |
return; |
| 911 |
} |
| 912 |
|
| 913 |
if (!$located) { |
| 914 |
// wp-config.php was not in any directory we swept, so it is relocated somewhere |
| 915 |
// we could not resolve. Leave the flag unset so a later upgrade tries again. |
| 916 |
error_log('MetaSync: wp-config.php was not found while cleaning up legacy backup copies in ' // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- genuine failure path, bounded, no secrets |
| 917 |
. implode(', ', array_keys($sweptDirs)) . ' - cleanup will be retried.'); |
| 918 |
return; |
| 919 |
} |
| 920 |
|
| 921 |
update_option(self::WPCONFIG_BACKUP_CLEANUP_OPTION, 1, false); |
| 922 |
} |
| 923 |
|
| 924 |
/** |
| 925 |
* Delete leftover full copies of wp-config.php from a directory. |
| 926 |
* |
| 927 |
* Covers the legacy `wp-config.php.metasync-backup-*` copies and `.metasync-tmp-*` |
| 928 |
* files orphaned by an interrupted atomic save. Temp files are only removed once |
| 929 |
* they are older than WPCONFIG_TMP_MAX_AGE, so a save running concurrently in |
| 930 |
* another request never has its temp file pulled out from under it. |
| 931 |
* |
| 932 |
* @param string $config_dir Directory that may contain leftover copies. |
| 933 |
* |
| 934 |
* @return array{removed:int,failed:int,scan_failed:int} Cleanup and scan-failure counts. |
| 935 |
*/ |
| 936 |
public static function remove_wpconfig_backups($config_dir) |
| 937 |
{ |
| 938 |
$dir = rtrim($config_dir, '/'); |
| 939 |
$removed = 0; |
| 940 |
$failed = 0; |
| 941 |
$scan_failed = 0; |
| 942 |
|
| 943 |
$backups = glob($dir . '/wp-config.php.metasync-backup-*'); |
| 944 |
if (false === $backups) { |
| 945 |
$scan_failed++; |
| 946 |
$backups = []; |
| 947 |
} |
| 948 |
|
| 949 |
foreach ($backups as $backup) { |
| 950 |
if (!is_file($backup)) { |
| 951 |
continue; |
| 952 |
} |
| 953 |
|
| 954 |
if (@unlink($backup)) { // phpcs:ignore WordPress.WP.AlternativeFunctions.unlink_unlink -- boolean return value is consumed |
| 955 |
$removed++; |
| 956 |
} else { |
| 957 |
$failed++; |
| 958 |
} |
| 959 |
} |
| 960 |
|
| 961 |
$temps = glob($dir . '/.metasync-tmp-*'); |
| 962 |
if (false === $temps) { |
| 963 |
$scan_failed++; |
| 964 |
$temps = []; |
| 965 |
} |
| 966 |
|
| 967 |
foreach ($temps as $temp) { |
| 968 |
if (!is_file($temp)) { |
| 969 |
continue; |
| 970 |
} |
| 971 |
|
| 972 |
// abs() so a future mtime (clock skew, NFS) still ages out instead of being |
| 973 |
// skipped forever. |
| 974 |
$mtime = @filemtime($temp); |
| 975 |
if (false === $mtime || abs(time() - $mtime) < self::WPCONFIG_TMP_MAX_AGE) { |
| 976 |
continue; |
| 977 |
} |
| 978 |
|
| 979 |
if (@unlink($temp)) { // phpcs:ignore WordPress.WP.AlternativeFunctions.unlink_unlink -- boolean return value is consumed |
| 980 |
$removed++; |
| 981 |
} else { |
| 982 |
$failed++; |
| 983 |
} |
| 984 |
} |
| 985 |
|
| 986 |
return ['removed' => $removed, 'failed' => $failed, 'scan_failed' => $scan_failed]; |
| 987 |
} |
| 988 |
|
| 989 |
|
| 990 |
} |
| 991 |
|
