| 1 |
<?php |
| 2 |
|
| 3 |
|
| 4 |
if (!defined('ABSPATH')) { |
| 5 |
exit; |
| 6 |
} |
| 7 |
|
| 8 |
require_once __DIR__ . '/DatabaseUpgradesEtcTrait_NGram.php'; |
| 9 |
require_once __DIR__ . '/DatabaseUpgradesEtcTrait_Maintenance.php'; |
| 10 |
require_once __DIR__ . '/DatabaseUpgradesEtcTrait_PluginUpdate.php'; |
| 11 |
require_once __DIR__ . '/DatabaseUpgradesEtcTrait_TableRepair.php'; |
| 12 |
|
| 13 |
/* Functions in this class should all reference one of the following variables or support functions that do. |
| 14 |
* $wpdb, $_GET, $_POST, $_SERVER, $_.* |
| 15 |
* everything $wpdb related. |
| 16 |
* everything $_GET, $_POST, (etc) related. |
| 17 |
* Read the database, Store to the database, |
| 18 |
*/ |
| 19 |
|
| 20 |
class ABJ_404_Solution_DatabaseUpgradesEtc { |
| 21 |
|
| 22 |
/** @var self|null */ |
| 23 |
private static $instance = null; |
| 24 |
|
| 25 |
/** @var string|null */ |
| 26 |
private static $uniqID = null; |
| 27 |
|
| 28 |
/** |
| 29 |
* Per-request dedup flag for scheduleLogsv2CanonicalUrlBackfill(). |
| 30 |
* Mirrors DataAccess::$hitsTableRebuildScheduled — ensures the shutdown |
| 31 |
* hook is registered at most once per request even if the schedule |
| 32 |
* function is called from multiple paths (Captured-404s tab render + |
| 33 |
* Stats panel + EmailDigest, etc.). Reset to false naturally when the |
| 34 |
* PHP process ends; persistent SAPIs (PHP-FPM, mod_php) reset it |
| 35 |
* implicitly between requests because static is process-local. |
| 36 |
* |
| 37 |
* @var bool |
| 38 |
*/ |
| 39 |
private static $logsv2CanonicalBackfillScheduled = false; |
| 40 |
|
| 41 |
/** @var ABJ_404_Solution_DataAccess */ |
| 42 |
private $dao; |
| 43 |
|
| 44 |
/** @var ABJ_404_Solution_Logging */ |
| 45 |
private $logger; |
| 46 |
|
| 47 |
/** @var ABJ_404_Solution_Functions */ |
| 48 |
private $f; |
| 49 |
|
| 50 |
/** @var ABJ_404_Solution_PermalinkCache */ |
| 51 |
private $permalinkCache; |
| 52 |
|
| 53 |
/** @var ABJ_404_Solution_SynchronizationUtils */ |
| 54 |
private $syncUtils; |
| 55 |
|
| 56 |
/** @var ABJ_404_Solution_PluginLogic */ |
| 57 |
private $logic; |
| 58 |
|
| 59 |
/** @var ABJ_404_Solution_NGramFilter */ |
| 60 |
private $ngramFilter; |
| 61 |
|
| 62 |
use ABJ_404_Solution_DatabaseUpgradesEtc_NGramTrait; |
| 63 |
use ABJ_404_Solution_DatabaseUpgradesEtc_MaintenanceTrait; |
| 64 |
use ABJ_404_Solution_DatabaseUpgradesEtc_PluginUpdateTrait; |
| 65 |
use ABJ_404_Solution_DatabaseUpgradesEtc_TableRepairTrait; |
| 66 |
use ABJ_404_Solution_DatabaseUpgradesEtc_IndexesTrait; |
| 67 |
|
| 68 |
/** |
| 69 |
* Constructor with dependency injection. |
| 70 |
* |
| 71 |
* @param ABJ_404_Solution_DataAccess|null $dataAccess Data access layer |
| 72 |
* @param ABJ_404_Solution_Logging|null $logging Logging service |
| 73 |
* @param ABJ_404_Solution_Functions|null $functions String utilities |
| 74 |
* @param ABJ_404_Solution_PermalinkCache|null $permalinkCache Permalink cache service |
| 75 |
* @param ABJ_404_Solution_SynchronizationUtils|null $syncUtils Sync utilities |
| 76 |
* @param ABJ_404_Solution_PluginLogic|null $pluginLogic Business logic service |
| 77 |
* @param ABJ_404_Solution_NGramFilter|null $ngramFilter N-gram filter service |
| 78 |
*/ |
| 79 |
public function __construct($dataAccess = null, $logging = null, $functions = null, $permalinkCache = null, $syncUtils = null, $pluginLogic = null, $ngramFilter = null) { |
| 80 |
// Use injected dependencies or fall back to getInstance() for backward compatibility |
| 81 |
$this->dao = $dataAccess !== null ? $dataAccess : abj_service('data_access'); |
| 82 |
$this->logger = $logging !== null ? $logging : abj_service('logging'); |
| 83 |
$this->f = $functions !== null ? $functions : abj_service('functions'); |
| 84 |
$this->permalinkCache = $permalinkCache !== null ? $permalinkCache : abj_service('permalink_cache'); |
| 85 |
$this->syncUtils = $syncUtils !== null ? $syncUtils : abj_service('sync_utils'); |
| 86 |
$this->logic = $pluginLogic !== null ? $pluginLogic : abj_service('plugin_logic'); |
| 87 |
$this->ngramFilter = $ngramFilter !== null ? $ngramFilter : abj_service('ngram_filter'); |
| 88 |
} |
| 89 |
|
| 90 |
/** @return self */ |
| 91 |
public static function getInstance() { |
| 92 |
if (self::$instance == null) { |
| 93 |
self::$instance = new ABJ_404_Solution_DatabaseUpgradesEtc(); |
| 94 |
self::$uniqID = uniqid("", true); |
| 95 |
} |
| 96 |
|
| 97 |
return self::$instance; |
| 98 |
} |
| 99 |
|
| 100 |
/** Create the tables when the plugin is first activated. |
| 101 |
* @param bool $updatingToNewVersion |
| 102 |
* @return void |
| 103 |
*/ |
| 104 |
function createDatabaseTables($updatingToNewVersion = false, bool $force = false) { |
| 105 |
|
| 106 |
$synchronizedKeyFromUser = "create_db_tables"; |
| 107 |
$uniqueID = null; |
| 108 |
|
| 109 |
if (!$force) { |
| 110 |
$uniqueID = $this->syncUtils->synchronizerAcquireLockTry($synchronizedKeyFromUser); |
| 111 |
|
| 112 |
if ($uniqueID == '' || $uniqueID == null) { |
| 113 |
$this->logger->debugMessage("Avoiding multiple calls for creating database tables."); |
| 114 |
return; |
| 115 |
} |
| 116 |
} |
| 117 |
|
| 118 |
// Fixed: Use finally block to ensure lock is ALWAYS released, even on fatal errors |
| 119 |
try { |
| 120 |
$this->reallyCreateDatabaseTables($updatingToNewVersion); |
| 121 |
|
| 122 |
} catch (\Exception $e) { |
| 123 |
$this->logger->errorMessage("Error creating database tables. ", $e); |
| 124 |
throw $e; // Re-throw to propagate the error |
| 125 |
} finally { |
| 126 |
// Release the lock only if one was acquired (non-forced path). |
| 127 |
if ($uniqueID !== null && $uniqueID !== '') { |
| 128 |
$this->syncUtils->synchronizerReleaseLock($uniqueID, $synchronizedKeyFromUser); |
| 129 |
} |
| 130 |
} |
| 131 |
} |
| 132 |
|
| 133 |
/** |
| 134 |
* @param bool $updatingToNewVersion |
| 135 |
* @return void |
| 136 |
*/ |
| 137 |
private function reallyCreateDatabaseTables($updatingToNewVersion = false) { |
| 138 |
if ($updatingToNewVersion) { |
| 139 |
$this->correctIssuesBefore(); |
| 140 |
} |
| 141 |
|
| 142 |
// MULTISITE: Process current site immediately, schedule background task for remaining sites |
| 143 |
if ($this->isNetworkActivated() && !$updatingToNewVersion) { |
| 144 |
// Activation path: create tables for current site + schedule background for others. |
| 145 |
$currentBlogId = get_current_blog_id(); |
| 146 |
$this->runInitialCreateTables(); |
| 147 |
$this->correctCollations(); |
| 148 |
$this->updateTableEngineToInnoDB(); |
| 149 |
$this->createIndexes(); |
| 150 |
|
| 151 |
// First chunk of the canonical_url backfill runs in-band so newly |
| 152 |
// upgraded small sites finish in one shot. Larger sites converge |
| 153 |
// over subsequent daily-maintenance cron ticks (same method). |
| 154 |
$this->backfillRedirectsCanonicalUrl(); |
| 155 |
|
| 156 |
$this->logger->infoMessage(sprintf( |
| 157 |
"Network activation: Created tables for current site (ID %d). Scheduling background task for remaining sites.", |
| 158 |
$currentBlogId |
| 159 |
)); |
| 160 |
|
| 161 |
$this->scheduleBackgroundMultisiteActivation($currentBlogId); |
| 162 |
|
| 163 |
} else if ($this->isNetworkActivated() && $updatingToNewVersion) { |
| 164 |
// Upgrade path on a network install: update tables for current site + schedule |
| 165 |
// background upgrade for other sites (so sub-site tables are also updated). |
| 166 |
$currentBlogId = get_current_blog_id(); |
| 167 |
$this->runInitialCreateTables(); |
| 168 |
$this->correctCollations(); |
| 169 |
$this->updateTableEngineToInnoDB(); |
| 170 |
$this->createIndexes(); |
| 171 |
|
| 172 |
// First chunk of the canonical_url backfill runs in-band so newly |
| 173 |
// upgraded small sites finish in one shot. Larger sites converge |
| 174 |
// over subsequent daily-maintenance cron ticks (same method). |
| 175 |
$this->backfillRedirectsCanonicalUrl(); |
| 176 |
|
| 177 |
$this->logger->infoMessage(sprintf( |
| 178 |
"Network upgrade: Updated tables for current site (ID %d). Scheduling background upgrade for remaining sites.", |
| 179 |
$currentBlogId |
| 180 |
)); |
| 181 |
|
| 182 |
$this->scheduleBackgroundMultisiteUpgrade($currentBlogId); |
| 183 |
|
| 184 |
} else { |
| 185 |
// Single site (or non-network-activated): create/update tables for current site only. |
| 186 |
$this->runInitialCreateTables(); |
| 187 |
$this->correctCollations(); |
| 188 |
$this->updateTableEngineToInnoDB(); |
| 189 |
$this->createIndexes(); |
| 190 |
|
| 191 |
// First chunk of the canonical_url backfill runs in-band so newly |
| 192 |
// upgraded small sites finish in one shot. Larger sites converge |
| 193 |
// over subsequent daily-maintenance cron ticks (same method). |
| 194 |
$this->backfillRedirectsCanonicalUrl(); |
| 195 |
} |
| 196 |
|
| 197 |
// Adopt orphaned tables AFTER target tables exist (rename handles prefix mismatches). |
| 198 |
$this->renameAbj404TablesToLowerCase(); |
| 199 |
|
| 200 |
// we could do this only when a table is created or when the "meta" column is created |
| 201 |
// but it doesn't take long anyway so we do it every night. |
| 202 |
$this->permalinkCache->updatePermalinkCache(1); |
| 203 |
|
| 204 |
// One-time N-gram cache initialization (async via WP-Cron to prevent blocking) |
| 205 |
// MULTISITE: Use network-aware option getter to check initialization status |
| 206 |
if ($this->getNetworkAwareOption('abj404_ngram_cache_initialized') !== '1') { |
| 207 |
$this->logger->debugMessage("N-gram cache not initialized. Scheduling background build..."); |
| 208 |
|
| 209 |
// Schedule async rebuild via WP-Cron instead of blocking activation |
| 210 |
$this->scheduleNGramCacheRebuild(); |
| 211 |
|
| 212 |
// Show admin notice that build is scheduled |
| 213 |
if ($updatingToNewVersion && function_exists('add_settings_error')) { |
| 214 |
$context = is_multisite() && $this->isNetworkActivated() ? ' across all sites in the network' : ''; |
| 215 |
$message = sprintf( |
| 216 |
__('404 Solution: N-gram spell check cache is being built in the background%s to optimize performance. This may take a few minutes on large sites.', '404-solution'), |
| 217 |
$context |
| 218 |
); |
| 219 |
add_settings_error('abj404_settings', 'ngram_cache_scheduled', $message, 'updated'); |
| 220 |
} |
| 221 |
|
| 222 |
$this->logger->infoMessage("N-gram cache rebuild scheduled via WP-Cron."); |
| 223 |
} else { |
| 224 |
$this->logger->debugMessage("N-gram cache already initialized. Skipping rebuild."); |
| 225 |
} |
| 226 |
|
| 227 |
// Run one-time migration to relative paths (Issue #24) |
| 228 |
if (get_option('abj404_migrated_to_relative_paths') !== '1') { |
| 229 |
$migrationResults = $this->migrateURLsToRelativePaths(); |
| 230 |
|
| 231 |
// Show admin notice if migration occurred |
| 232 |
if ($updatingToNewVersion && !empty($migrationResults['redirects_updated'])) { |
| 233 |
$rawRedirectsUpdated = $migrationResults['redirects_updated']; |
| 234 |
$redirectsUpdated = is_scalar($rawRedirectsUpdated) ? (int)$rawRedirectsUpdated : 0; |
| 235 |
$message = sprintf( |
| 236 |
_n( |
| 237 |
'404 Solution: Migrated %d redirect to subdirectory-independent format.', |
| 238 |
'404 Solution: Migrated %d redirects to subdirectory-independent format.', |
| 239 |
$redirectsUpdated, |
| 240 |
'404-solution' |
| 241 |
), |
| 242 |
$redirectsUpdated |
| 243 |
); |
| 244 |
if (function_exists('add_settings_error')) { |
| 245 |
add_settings_error('abj404_settings', 'migration_success', $message, 'updated'); |
| 246 |
} |
| 247 |
} |
| 248 |
} |
| 249 |
|
| 250 |
if ($updatingToNewVersion) { |
| 251 |
$this->correctIssuesAfter(); |
| 252 |
} |
| 253 |
} |
| 254 |
|
| 255 |
/** |
| 256 |
* Makes all plugin table names lowercase, in case someone thought it was funny to use |
| 257 |
* the lower_case_table_names=0 setting. Also detects and adopts orphaned plugin tables |
| 258 |
* under old prefixes (from site migrations or the rename bug in v2.35.16–v3.x). |
| 259 |
* @return void |
| 260 |
*/ |
| 261 |
function renameAbj404TablesToLowerCase() { |
| 262 |
global $wpdb; |
| 263 |
|
| 264 |
// On case-insensitive MySQL (lower_case_table_names >= 1), table names |
| 265 |
// are already treated as lowercase internally. Renaming is pointless and |
| 266 |
// can cause issues on some hosting setups. |
| 267 |
// DAO-bypass-approved: Schema-bootstrap inside renameAbj404TablesToLowerCase() — runs before plugin DAO is fully wired during DB upgrades |
| 268 |
$lctnResult = $wpdb->get_row("SHOW VARIABLES LIKE 'lower_case_table_names'", ARRAY_A); |
| 269 |
if (is_array($lctnResult)) { |
| 270 |
$lctnValue = null; |
| 271 |
foreach ($lctnResult as $key => $value) { |
| 272 |
if (strtolower((string)$key) === 'value') { |
| 273 |
$lctnValue = $value; |
| 274 |
break; |
| 275 |
} |
| 276 |
} |
| 277 |
if ($lctnValue !== null && (int)$lctnValue >= 1) { |
| 278 |
// MySQL already handles table names case-insensitively. |
| 279 |
// Still run adoption check in case of prefix mismatch. |
| 280 |
$this->adoptOrphanedTables(); |
| 281 |
return; |
| 282 |
} |
| 283 |
} |
| 284 |
|
| 285 |
// Fetch all tables containing "abj404", case-insensitive |
| 286 |
$dbNameRaw = $wpdb->dbname ?? ''; |
| 287 |
if ($dbNameRaw === '') { |
| 288 |
$this->logger->warn("Could not determine database name for lowercase rename."); |
| 289 |
return; |
| 290 |
} |
| 291 |
$dbNameEscaped = esc_sql($dbNameRaw); |
| 292 |
$dbName = is_array($dbNameEscaped) ? '' : $dbNameEscaped; |
| 293 |
$query = "SELECT table_name |
| 294 |
FROM information_schema.tables |
| 295 |
WHERE table_schema = '{$dbName}' |
| 296 |
AND LOWER(table_name) LIKE '%abj404%'"; |
| 297 |
$results = $this->dao->queryAndGetResults($query); |
| 298 |
|
| 299 |
if (!is_array($results['rows'])) { |
| 300 |
$this->logger->warn("Could not query information_schema tables for lowercase rename."); |
| 301 |
return; |
| 302 |
} |
| 303 |
|
| 304 |
foreach ($results['rows'] as $row) { |
| 305 |
// Case-insensitive key lookup: MySQL drivers return information_schema |
| 306 |
// column names in varying cases (table_name, TABLE_NAME, Table_Name). |
| 307 |
$tableName = null; |
| 308 |
foreach ($row as $key => $value) { |
| 309 |
if (strtolower((string)$key) === 'table_name') { |
| 310 |
$tableName = $value; |
| 311 |
break; |
| 312 |
} |
| 313 |
} |
| 314 |
|
| 315 |
if (!empty($tableName)) { |
| 316 |
$lowercaseName = strtolower($tableName); |
| 317 |
|
| 318 |
// Check if the table name is already lowercase, skip if it is |
| 319 |
if ($tableName !== $lowercaseName) { |
| 320 |
// Rename the table to lowercase |
| 321 |
$renameQuery = "RENAME TABLE `{$tableName}` TO `{$lowercaseName}`"; |
| 322 |
$this->dao->queryAndGetResults($renameQuery, |
| 323 |
['ignore_errors' => ["already exists"]]); |
| 324 |
$this->logger->infoMessage("Renamed table {$tableName} to {$lowercaseName}\n"); |
| 325 |
} |
| 326 |
} else { |
| 327 |
$this->logger->warn("I didn't find a table name in the results of this row: " . |
| 328 |
print_r($row, true)); |
| 329 |
} |
| 330 |
} |
| 331 |
|
| 332 |
// After renaming, check for orphaned tables under old prefixes. |
| 333 |
$this->adoptOrphanedTables(); |
| 334 |
} |
| 335 |
|
| 336 |
/** |
| 337 |
* Number of rows updated per chunk by backfillRedirectsCanonicalUrl(). |
| 338 |
* Sized so a single chunk completes well under the standard 60s query |
| 339 |
* timeout even on slow disks; the chunk loop will keep going until the |
| 340 |
* per-invocation budget is exhausted. |
| 341 |
* |
| 342 |
* Defined here (not on the trait) because trait constants require PHP 8.2+ |
| 343 |
* and the plugin supports PHP 7.4. The trait references this via self:: |
| 344 |
* which resolves to the using class at compile time. |
| 345 |
*/ |
| 346 |
const CANONICAL_URL_BACKFILL_CHUNK_SIZE = 5000; |
| 347 |
|
| 348 |
/** |
| 349 |
* Per-invocation wall-clock budget (seconds) for backfillRedirectsCanonicalUrl(). |
| 350 |
* Bounds how long the daily cron / activation handler will spend on this |
| 351 |
* task in one call so a 350K-row site finishes over a few cron ticks |
| 352 |
* instead of all in one request that risks PHP max_execution_time. |
| 353 |
*/ |
| 354 |
const CANONICAL_URL_BACKFILL_TIME_BUDGET_SEC = 25; |
| 355 |
|
| 356 |
/** |
| 357 |
* Per-invocation wall-clock budget (seconds) for backfillLogsv2CanonicalUrl(). |
| 358 |
* Tighter than the redirects-side budget because logsv2 backfill can also |
| 359 |
* be triggered from the Captured-404s admin-tab shutdown hook, which |
| 360 |
* holds a PHP-FPM worker for the duration. 15s caps worker-hold to a |
| 361 |
* window short enough that concurrent visitors are unlikely to notice |
| 362 |
* worker-pool pressure on shared hosts. Daily cron uses the same budget |
| 363 |
* so convergence math (~25K-75K rows per invocation) is consistent. |
| 364 |
*/ |
| 365 |
const LOGSV2_CANONICAL_URL_BACKFILL_TIME_BUDGET_SEC = 15; |
| 366 |
|
| 367 |
/** |
| 368 |
* wp_options key that flips to '1' once backfillLogsv2CanonicalUrl() |
| 369 |
* confirms zero NULL rows remain on logsv2.canonical_url. Once set, the |
| 370 |
* read-side query can drop the COALESCE fallback and use the no-COALESCE |
| 371 |
* form ("logsv2.canonical_url = redirects.canonical_url"); the planner |
| 372 |
* picks the smaller side as driver and skips the Filter step (~17,000x |
| 373 |
* cost reduction vs the COALESCE form per the redirects-temp-table-perf |
| 374 |
* writeup). |
| 375 |
* |
| 376 |
* Stored as autoload=false so the option doesn't bloat the autoloaded |
| 377 |
* options blob on every request — read on the captured-404s render path |
| 378 |
* only, which already triggers wp_cache lookups for related options. |
| 379 |
*/ |
| 380 |
const LOGSV2_CANONICAL_URL_BACKFILL_COMPLETE_OPTION = 'abj404_logsv2_canonical_url_backfill_complete'; |
| 381 |
|
| 382 |
/** |
| 383 |
* Known plugin table suffixes for adoption. |
| 384 |
* @var array<int, string> |
| 385 |
*/ |
| 386 |
private const PLUGIN_TABLE_SUFFIXES = [ |
| 387 |
'abj404_redirects', |
| 388 |
'abj404_logsv2', |
| 389 |
'abj404_spelling_cache', |
| 390 |
'abj404_permalink_cache', |
| 391 |
'abj404_lookup', |
| 392 |
'abj404_ngram_cache', |
| 393 |
'abj404_logs_hits', |
| 394 |
'abj404_redirect_conditions', |
| 395 |
'abj404_engine_profiles', |
| 396 |
'abj404_view_cache', |
| 397 |
]; |
| 398 |
|
| 399 |
/** |
| 400 |
* Detect orphaned plugin tables under old prefixes and adopt their data |
| 401 |
* into the current-prefix tables. Uses slug verification against the logs |
| 402 |
* table to confirm ownership before adopting. |
| 403 |
* |
| 404 |
* @return void |
| 405 |
*/ |
| 406 |
private function adoptOrphanedTables(): void { |
| 407 |
global $wpdb; |
| 408 |
|
| 409 |
$dbNameRaw = $wpdb->dbname ?? ''; |
| 410 |
if ($dbNameRaw === '') { |
| 411 |
return; |
| 412 |
} |
| 413 |
// @utf8-audit: opt-out — $wpdb->dbname is set by WordPress at |
| 414 |
// bootstrap from wp-config.php; never user input. |
| 415 |
$dbNameEscaped = esc_sql($dbNameRaw); |
| 416 |
$dbName = is_array($dbNameEscaped) ? '' : $dbNameEscaped; |
| 417 |
|
| 418 |
// Find all abj404 tables in the database, grouped by prefix. |
| 419 |
$query = "SELECT table_name |
| 420 |
FROM information_schema.tables |
| 421 |
WHERE table_schema = '{$dbName}' |
| 422 |
AND LOWER(table_name) LIKE '%abj404\\_%'"; |
| 423 |
$results = $this->dao->queryAndGetResults($query); |
| 424 |
|
| 425 |
if (!is_array($results['rows']) || empty($results['rows'])) { |
| 426 |
return; |
| 427 |
} |
| 428 |
|
| 429 |
$currentPrefix = $this->dao->getLowercasePrefix(); |
| 430 |
|
| 431 |
// Group tables by their prefix (everything before 'abj404_'). |
| 432 |
/** @var array<string, array<string>> prefix => [table_name, ...] */ |
| 433 |
$tablesByPrefix = []; |
| 434 |
foreach ($results['rows'] as $row) { |
| 435 |
$tableName = null; |
| 436 |
foreach ($row as $key => $value) { |
| 437 |
if (strtolower((string)$key) === 'table_name') { |
| 438 |
$tableName = strtolower((string)$value); |
| 439 |
break; |
| 440 |
} |
| 441 |
} |
| 442 |
if ($tableName === null) { |
| 443 |
continue; |
| 444 |
} |
| 445 |
|
| 446 |
$abj404Pos = strpos($tableName, 'abj404_'); |
| 447 |
if ($abj404Pos === false) { |
| 448 |
continue; |
| 449 |
} |
| 450 |
|
| 451 |
$prefix = substr($tableName, 0, $abj404Pos); |
| 452 |
$tablesByPrefix[$prefix][] = $tableName; |
| 453 |
} |
| 454 |
|
| 455 |
// Skip prefixes we've already adopted. |
| 456 |
$adoptedPrefixes = get_option('abj404_adopted_prefixes', array()); |
| 457 |
if (!is_array($adoptedPrefixes)) { |
| 458 |
$adoptedPrefixes = array(); |
| 459 |
} |
| 460 |
|
| 461 |
// Process each OLD prefix (not the current one). |
| 462 |
foreach ($tablesByPrefix as $oldPrefix => $tables) { |
| 463 |
if ($oldPrefix === $currentPrefix) { |
| 464 |
continue; |
| 465 |
} |
| 466 |
if (in_array($oldPrefix, $adoptedPrefixes, true)) { |
| 467 |
continue; |
| 468 |
} |
| 469 |
|
| 470 |
$this->logger->infoMessage( |
| 471 |
"Found orphaned plugin tables under prefix '{$oldPrefix}' " |
| 472 |
. "(current prefix is '{$currentPrefix}'): " . implode(', ', $tables) |
| 473 |
); |
| 474 |
|
| 475 |
// Check if old tables have any data at all. |
| 476 |
$totalRows = $this->countOldPrefixRows($oldPrefix, $tables); |
| 477 |
if ($totalRows === 0) { |
| 478 |
$this->logger->infoMessage( |
| 479 |
"Orphaned tables under prefix '{$oldPrefix}' are all empty. Skipping adoption." |
| 480 |
); |
| 481 |
continue; |
| 482 |
} |
| 483 |
|
| 484 |
// Verify ownership via logs dest_url slug matching. |
| 485 |
$matchResult = $this->verifyOwnershipViaLogs($oldPrefix); |
| 486 |
|
| 487 |
if ($matchResult === null) { |
| 488 |
// Logs verification returned no data — fall back to redirects post-ID check. |
| 489 |
$matchResult = $this->verifyOwnershipViaRedirects($oldPrefix); |
| 490 |
} |
| 491 |
|
| 492 |
if ($matchResult !== true) { |
| 493 |
// false = data doesn't match this site; null = insufficient data to verify. |
| 494 |
// Either way, do not adopt — absence of veto is not permission. |
| 495 |
$reason = ($matchResult === false) |
| 496 |
? "Data does not appear to belong to this site." |
| 497 |
: "Insufficient data in logs and redirects to verify ownership."; |
| 498 |
$this->logger->infoMessage( |
| 499 |
"Orphaned tables under prefix '{$oldPrefix}' — skipping adoption. {$reason}" |
| 500 |
); |
| 501 |
continue; |
| 502 |
} |
| 503 |
|
| 504 |
// Ownership positively verified — adopt the data. |
| 505 |
$this->adoptDataFromPrefix($oldPrefix, $currentPrefix, $tables); |
| 506 |
} |
| 507 |
} |
| 508 |
|
| 509 |
/** |
| 510 |
* Count total rows across all known plugin tables for a given prefix. |
| 511 |
* |
| 512 |
* @param string $oldPrefix |
| 513 |
* @param array<int, string> $knownTables Table names actually found in information_schema. |
| 514 |
* @return int |
| 515 |
*/ |
| 516 |
private function countOldPrefixRows(string $oldPrefix, array $knownTables): int { |
| 517 |
$total = 0; |
| 518 |
foreach (self::PLUGIN_TABLE_SUFFIXES as $suffix) { |
| 519 |
$tableName = $oldPrefix . $suffix; |
| 520 |
if (!in_array($tableName, $knownTables, true)) { |
| 521 |
continue; |
| 522 |
} |
| 523 |
$result = $this->dao->queryAndGetResults( |
| 524 |
"SELECT COUNT(*) AS cnt FROM `{$tableName}`", |
| 525 |
['ignore_errors' => ["doesn't exist", "not found"]] |
| 526 |
); |
| 527 |
if (is_array($result['rows']) && !empty($result['rows'])) { |
| 528 |
$row = $result['rows'][0]; |
| 529 |
$cnt = is_array($row) ? (int)($row['cnt'] ?? $row['CNT'] ?? 0) : 0; |
| 530 |
$total += $cnt; |
| 531 |
} |
| 532 |
} |
| 533 |
return $total; |
| 534 |
} |
| 535 |
|
| 536 |
/** |
| 537 |
* Verify ownership of orphaned tables by matching logs dest_url against |
| 538 |
* current site's published post slugs. |
| 539 |
* |
| 540 |
* @param string $oldPrefix The old table prefix. |
| 541 |
* @return bool|null true = verified, false = failed, null = no data to verify. |
| 542 |
*/ |
| 543 |
private function verifyOwnershipViaLogs(string $oldPrefix): ?bool { |
| 544 |
global $wpdb; |
| 545 |
$logsTable = $oldPrefix . 'abj404_logsv2'; |
| 546 |
// WordPress core posts table uses the original $wpdb->prefix (possibly mixed-case), |
| 547 |
// NOT our lowercased prefix. Only plugin tables were renamed to lowercase. |
| 548 |
$postsTable = ($wpdb->prefix ?? 'wp_') . 'posts'; |
| 549 |
|
| 550 |
// Check distinct internal dest_urls against published post slugs. |
| 551 |
$query = "SELECT COUNT(*) AS total, |
| 552 |
SUM(CASE WHEN matched = 1 THEN 1 ELSE 0 END) AS matches |
| 553 |
FROM ( |
| 554 |
SELECT DISTINCT dest_url, |
| 555 |
EXISTS(SELECT 1 FROM `{$postsTable}` p |
| 556 |
WHERE p.post_status = 'publish' |
| 557 |
AND LENGTH(p.post_name) >= 3 |
| 558 |
AND LOCATE(p.post_name, dest_url) > 0) AS matched |
| 559 |
FROM `{$logsTable}` l |
| 560 |
WHERE dest_url IS NOT NULL |
| 561 |
AND dest_url != '' |
| 562 |
AND dest_url != '404' |
| 563 |
AND dest_url NOT LIKE 'http://%' |
| 564 |
AND dest_url NOT LIKE 'https://%' |
| 565 |
LIMIT 500 |
| 566 |
) sub"; |
| 567 |
|
| 568 |
$result = $this->dao->queryAndGetResults($query, |
| 569 |
['ignore_errors' => ["doesn't exist", "not found"]]); |
| 570 |
|
| 571 |
if (!is_array($result['rows']) || empty($result['rows'])) { |
| 572 |
return null; |
| 573 |
} |
| 574 |
|
| 575 |
$row = $result['rows'][0]; |
| 576 |
$total = 0; |
| 577 |
$matches = 0; |
| 578 |
foreach ($row as $key => $value) { |
| 579 |
$lk = strtolower((string)$key); |
| 580 |
if ($lk === 'total') { $total = (int)$value; } |
| 581 |
if ($lk === 'matches') { $matches = (int)$value; } |
| 582 |
} |
| 583 |
|
| 584 |
if ($total === 0) { |
| 585 |
return null; // No internal dest_urls to verify. |
| 586 |
} |
| 587 |
|
| 588 |
$matchPct = ($matches / max(1, $total)) * 100; |
| 589 |
$this->logger->infoMessage( |
| 590 |
"Logs ownership verification for prefix '{$oldPrefix}': " |
| 591 |
. "{$matches}/{$total} distinct internal dest_urls match published post slugs " |
| 592 |
. "({$matchPct}%)" |
| 593 |
); |
| 594 |
|
| 595 |
return $matchPct >= 80; |
| 596 |
} |
| 597 |
|
| 598 |
/** |
| 599 |
* Fallback ownership verification using redirects table post-ID existence. |
| 600 |
* Weaker than slug matching but useful when logs have no internal dest_urls. |
| 601 |
* |
| 602 |
* @param string $oldPrefix |
| 603 |
* @return bool|null true = verified, false = failed, null = no data. |
| 604 |
*/ |
| 605 |
private function verifyOwnershipViaRedirects(string $oldPrefix): ?bool { |
| 606 |
global $wpdb; |
| 607 |
$redirectsTable = $oldPrefix . 'abj404_redirects'; |
| 608 |
$postsTable = ($wpdb->prefix ?? 'wp_') . 'posts'; |
| 609 |
|
| 610 |
$query = "SELECT COUNT(*) AS total, |
| 611 |
SUM(CASE WHEN p.ID IS NOT NULL THEN 1 ELSE 0 END) AS matches |
| 612 |
FROM `{$redirectsTable}` r |
| 613 |
LEFT JOIN `{$postsTable}` p |
| 614 |
ON p.ID = CAST(r.final_dest AS UNSIGNED) |
| 615 |
AND p.post_status IN ('publish', 'draft', 'private') |
| 616 |
WHERE r.type IN (1, 2, 3)"; |
| 617 |
|
| 618 |
$result = $this->dao->queryAndGetResults($query, |
| 619 |
['ignore_errors' => ["doesn't exist", "not found"]]); |
| 620 |
|
| 621 |
if (!is_array($result['rows']) || empty($result['rows'])) { |
| 622 |
return null; |
| 623 |
} |
| 624 |
|
| 625 |
$row = $result['rows'][0]; |
| 626 |
$total = 0; |
| 627 |
$matches = 0; |
| 628 |
foreach ($row as $key => $value) { |
| 629 |
$lk = strtolower((string)$key); |
| 630 |
if ($lk === 'total') { $total = (int)$value; } |
| 631 |
if ($lk === 'matches') { $matches = (int)$value; } |
| 632 |
} |
| 633 |
|
| 634 |
if ($total === 0) { |
| 635 |
return null; |
| 636 |
} |
| 637 |
|
| 638 |
$matchPct = ($matches / max(1, $total)) * 100; |
| 639 |
$this->logger->infoMessage( |
| 640 |
"Redirects fallback ownership verification for prefix '{$oldPrefix}': " |
| 641 |
. "{$matches}/{$total} type 1/2/3 redirects point to existing posts ({$matchPct}%)" |
| 642 |
); |
| 643 |
|
| 644 |
return $matchPct >= 80; |
| 645 |
} |
| 646 |
|
| 647 |
/** |
| 648 |
* Adopt data from orphaned tables under an old prefix into current-prefix tables. |
| 649 |
* Uses INSERT IGNORE to avoid duplicate key conflicts. |
| 650 |
* |
| 651 |
* @param string $oldPrefix |
| 652 |
* @param string $currentPrefix |
| 653 |
* @param array<string> $knownTables |
| 654 |
* @return void |
| 655 |
*/ |
| 656 |
private function adoptDataFromPrefix(string $oldPrefix, string $currentPrefix, array $knownTables): void { |
| 657 |
$this->logger->infoMessage( |
| 658 |
"Beginning adoption of data from prefix '{$oldPrefix}' to '{$currentPrefix}'" |
| 659 |
); |
| 660 |
|
| 661 |
$totalAdopted = 0; |
| 662 |
|
| 663 |
foreach (self::PLUGIN_TABLE_SUFFIXES as $suffix) { |
| 664 |
$oldTable = $oldPrefix . $suffix; |
| 665 |
if (!in_array($oldTable, $knownTables, true)) { |
| 666 |
continue; |
| 667 |
} |
| 668 |
$newTable = $currentPrefix . $suffix; |
| 669 |
|
| 670 |
// Check if old table exists and has rows. |
| 671 |
$countResult = $this->dao->queryAndGetResults( |
| 672 |
"SELECT COUNT(*) AS cnt FROM `{$oldTable}`", |
| 673 |
['ignore_errors' => ["doesn't exist", "not found"]] |
| 674 |
); |
| 675 |
if (!is_array($countResult['rows']) || empty($countResult['rows'])) { |
| 676 |
continue; |
| 677 |
} |
| 678 |
$row = $countResult['rows'][0]; |
| 679 |
$oldCount = is_array($row) ? (int)($row['cnt'] ?? $row['CNT'] ?? 0) : 0; |
| 680 |
if ($oldCount === 0) { |
| 681 |
continue; |
| 682 |
} |
| 683 |
|
| 684 |
// Check if new table exists (it should — auto-repair creates them). |
| 685 |
$newExists = $this->dao->queryAndGetResults( |
| 686 |
"SELECT 1 FROM `{$newTable}` LIMIT 1", |
| 687 |
['ignore_errors' => ["doesn't exist", "not found"]] |
| 688 |
); |
| 689 |
if (!empty($newExists['last_error'])) { |
| 690 |
$this->logger->infoMessage( |
| 691 |
"Target table '{$newTable}' does not exist yet. Skipping adoption for '{$suffix}'." |
| 692 |
); |
| 693 |
continue; |
| 694 |
} |
| 695 |
|
| 696 |
// Build a column-matched INSERT to handle schema drift between old and new tables. |
| 697 |
// Old tables from older plugin versions may have fewer or different columns. |
| 698 |
$commonColumns = $this->getCommonColumns($oldTable, $newTable); |
| 699 |
if (empty($commonColumns)) { |
| 700 |
$this->logger->infoMessage( |
| 701 |
"No common columns found between '{$oldTable}' and '{$newTable}'. Skipping." |
| 702 |
); |
| 703 |
continue; |
| 704 |
} |
| 705 |
|
| 706 |
$columnList = implode('`, `', $commonColumns); |
| 707 |
$insertQuery = "INSERT IGNORE INTO `{$newTable}` (`{$columnList}`) " |
| 708 |
. "SELECT `{$columnList}` FROM `{$oldTable}`"; |
| 709 |
$insertResult = $this->dao->queryAndGetResults($insertQuery, |
| 710 |
['ignore_errors' => ["doesn't exist", "not found", "Duplicate"]]); |
| 711 |
|
| 712 |
$affectedRows = 0; |
| 713 |
if (is_array($insertResult) && isset($insertResult['rows_affected'])) { |
| 714 |
$rawAffected = $insertResult['rows_affected']; |
| 715 |
$affectedRows = is_numeric($rawAffected) ? (int)$rawAffected : 0; |
| 716 |
} |
| 717 |
|
| 718 |
if ($affectedRows > 0) { |
| 719 |
$totalAdopted += $affectedRows; |
| 720 |
$this->logger->infoMessage( |
| 721 |
"Adopted {$affectedRows} rows from '{$oldTable}' into '{$newTable}'" |
| 722 |
); |
| 723 |
} |
| 724 |
} |
| 725 |
|
| 726 |
$this->logger->infoMessage( |
| 727 |
"Adoption complete: {$totalAdopted} total rows adopted from prefix '{$oldPrefix}' to '{$currentPrefix}'" |
| 728 |
); |
| 729 |
|
| 730 |
// Record this prefix as adopted so we don't re-detect it on every page load. |
| 731 |
$adoptedPrefixes = get_option('abj404_adopted_prefixes', array()); |
| 732 |
if (!is_array($adoptedPrefixes)) { |
| 733 |
$adoptedPrefixes = array(); |
| 734 |
} |
| 735 |
if (!in_array($oldPrefix, $adoptedPrefixes, true)) { |
| 736 |
$adoptedPrefixes[] = $oldPrefix; |
| 737 |
update_option('abj404_adopted_prefixes', $adoptedPrefixes, false); |
| 738 |
} |
| 739 |
} |
| 740 |
|
| 741 |
/** |
| 742 |
* Get the list of column names that exist in both tables. |
| 743 |
* Used by adoptDataFromPrefix() to build column-matched INSERTs |
| 744 |
* that survive schema drift between plugin versions. |
| 745 |
* |
| 746 |
* @param string $tableA |
| 747 |
* @param string $tableB |
| 748 |
* @return array<int, string> Column names present in both tables (lowercase). |
| 749 |
*/ |
| 750 |
private function getCommonColumns(string $tableA, string $tableB): array { |
| 751 |
$colsA = $this->getTableColumns($tableA); |
| 752 |
$colsB = $this->getTableColumns($tableB); |
| 753 |
|
| 754 |
if (empty($colsA) || empty($colsB)) { |
| 755 |
return []; |
| 756 |
} |
| 757 |
|
| 758 |
return array_values(array_intersect($colsA, $colsB)); |
| 759 |
} |
| 760 |
|
| 761 |
/** |
| 762 |
* Get column names for a table via SHOW COLUMNS. |
| 763 |
* |
| 764 |
* @param string $tableName |
| 765 |
* @return array<int, string> Column names (lowercase). |
| 766 |
*/ |
| 767 |
private function getTableColumns(string $tableName): array { |
| 768 |
$result = $this->dao->queryAndGetResults( |
| 769 |
"SHOW COLUMNS FROM `{$tableName}`", |
| 770 |
['ignore_errors' => ["doesn't exist", "not found"]] |
| 771 |
); |
| 772 |
|
| 773 |
if (!is_array($result['rows']) || empty($result['rows'])) { |
| 774 |
return []; |
| 775 |
} |
| 776 |
|
| 777 |
$columns = []; |
| 778 |
foreach ($result['rows'] as $row) { |
| 779 |
// SHOW COLUMNS returns 'Field' key — case-insensitive lookup. |
| 780 |
$colName = null; |
| 781 |
foreach ($row as $key => $value) { |
| 782 |
if (strtolower((string)$key) === 'field') { |
| 783 |
$colName = strtolower((string)$value); |
| 784 |
break; |
| 785 |
} |
| 786 |
} |
| 787 |
if ($colName !== null) { |
| 788 |
$columns[] = $colName; |
| 789 |
} |
| 790 |
} |
| 791 |
|
| 792 |
return $columns; |
| 793 |
} |
| 794 |
|
| 795 |
/** When certain columns are created we have to populate data. |
| 796 |
* @param string $tableName |
| 797 |
* @param string $colName |
| 798 |
* @return void |
| 799 |
*/ |
| 800 |
function handleSpecificCases($tableName, $colName) { |
| 801 |
if (empty($tableName) || !is_string($tableName)) { |
| 802 |
return; |
| 803 |
} |
| 804 |
|
| 805 |
if (strpos($tableName, 'abj404_logsv2') !== false && $colName == 'min_log_id') { |
| 806 |
global $wpdb; |
| 807 |
$query = ABJ_404_Solution_Functions::readFileContents(__DIR__ . "/sql/logsSetMinLogID.sql"); |
| 808 |
$this->dao->queryAndGetResults($query); |
| 809 |
// Ensure composite index exists after backfilling min_log_id. |
| 810 |
$this->ensureLogsCompositeIndex($tableName); |
| 811 |
} |
| 812 |
if (strpos($tableName, 'abj404_permalink_cache') !== false && $colName == 'url_length') { |
| 813 |
// clear the permalink cache so that the url length column will be populated. |
| 814 |
// this could be more efficient but I'll assume that's not necessary. |
| 815 |
$this->dao->truncatePermalinkCacheTable(); |
| 816 |
} |
| 817 |
} |
| 818 |
|
| 819 |
/** |
| 820 |
* Discover all permanent (non-Temp) DDL files and extract table metadata. |
| 821 |
* |
| 822 |
* @return array<int, array{placeholder: string, bareTableName: string, ddlContent: string}> |
| 823 |
*/ |
| 824 |
function discoverPermanentDDLFiles(): array { |
| 825 |
$sqlDir = __DIR__ . '/sql'; |
| 826 |
$files = glob($sqlDir . '/create*Table.sql'); |
| 827 |
if (!is_array($files)) { |
| 828 |
$files = []; |
| 829 |
} |
| 830 |
sort($files); |
| 831 |
|
| 832 |
$result = []; |
| 833 |
foreach ($files as $file) { |
| 834 |
if (stripos(basename($file), 'Temp') !== false) { |
| 835 |
continue; |
| 836 |
} |
| 837 |
$ddlContent = ABJ_404_Solution_Functions::readFileContents($file); |
| 838 |
if (!is_string($ddlContent) || trim($ddlContent) === '') { |
| 839 |
continue; |
| 840 |
} |
| 841 |
if (!preg_match('/\{(wp_(abj404_\w+))\}/', $ddlContent, $m)) { |
| 842 |
continue; |
| 843 |
} |
| 844 |
// Transient staged-build tables (view_build, view_done, view_deleteme) |
| 845 |
// are owned by ABJ_404_Solution_DataAccess_ViewQueriesStagedTrait. |
| 846 |
// stageCreateBuildTable() creates view_build on demand, stageRenameSwap() |
| 847 |
// renames it to view_done, and view_deleteme is the ephemeral previous- |
| 848 |
// generation served table that gets dropped right after the swap. None |
| 849 |
// of them should participate in the permanent-DDL bootstrap, repair, or |
| 850 |
// missing-table check loops. Their absence between builds is normal, |
| 851 |
// not a corruption signal. |
| 852 |
if (in_array($m[2], array('abj404_view_build', 'abj404_view_done', 'abj404_view_deleteme'), true)) { |
| 853 |
continue; |
| 854 |
} |
| 855 |
$result[] = [ |
| 856 |
'placeholder' => '{' . $m[1] . '}', |
| 857 |
'bareTableName' => $m[2], |
| 858 |
'ddlContent' => $ddlContent, |
| 859 |
]; |
| 860 |
} |
| 861 |
return $result; |
| 862 |
} |
| 863 |
|
| 864 |
/** @return void */ |
| 865 |
function runInitialCreateTables() { |
| 866 |
// Re-add a stripped `id` PRIMARY KEY (via ALTER) BEFORE any CREATE TABLE |
| 867 |
// IF NOT EXISTS runs. Without this step, an existing-but-broken table |
| 868 |
// (missing the file's `id` PRIMARY KEY) would survive the IF NOT EXISTS |
| 869 |
// check and verifyColumns would only ALTER ADD the missing non-PK |
| 870 |
// columns, leaving the table without its primary key. Lives here (not |
| 871 |
// just in correctIssuesBefore) so cron callers of createDatabaseTables() |
| 872 |
// — which don't pass the $updatingToNewVersion flag — also repair |
| 873 |
// stripped tables instead of propagating the broken state. |
| 874 |
$this->repairStrippedViewCacheTable(); |
| 875 |
|
| 876 |
foreach ($this->discoverPermanentDDLFiles() as $ddlEntry) { |
| 877 |
$query = $this->applyPluginTableCharsetCollate($ddlEntry['ddlContent']); |
| 878 |
$this->dao->queryAndGetResults($query); |
| 879 |
|
| 880 |
$tableName = $this->dao->doTableNameReplacements($ddlEntry['placeholder']); |
| 881 |
|
| 882 |
// Per-table post-CREATE verification: confirm the table actually |
| 883 |
// exists on disk. queryAndGetResults logs SQL errors generically, |
| 884 |
// but a silently-failing CREATE (concurrent DROP, swallowed parse |
| 885 |
// error, prefix drift, or insufficient privileges) is invisible |
| 886 |
// without an explicit existence check. Log per-table so the debug |
| 887 |
// log identifies which DDL didn't materialize and why downstream |
| 888 |
// auto-repair attempts will keep failing. |
| 889 |
if (!$this->verifyTableMaterialized($tableName, $ddlEntry['placeholder'])) { |
| 890 |
// Don't abort the loop — other tables can still get created. |
| 891 |
continue; |
| 892 |
} |
| 893 |
|
| 894 |
// Targeted online-DDL column add(s) before the generic verifyColumns() |
| 895 |
// flow runs a bare ALTER. On large logsv2 tables (multi-GB on |
| 896 |
// busy sites) bare ADD COLUMN can block the table for tens of |
| 897 |
// seconds; the targeted helper uses ALGORITHM=INPLACE, LOCK=NONE |
| 898 |
// so InnoDB ≥ 5.6 picks the lockless online-DDL path. If the |
| 899 |
// engine doesn't support it the helper falls back silently and |
| 900 |
// verifyColumns() picks up the column add as a safety net. |
| 901 |
if ($ddlEntry['bareTableName'] === 'abj404_logsv2') { |
| 902 |
$this->ensureLogsv2CanonicalUrlColumn($tableName); |
| 903 |
} |
| 904 |
// Same logic for the redirects side. canonical_url is required by |
| 905 |
// setupRedirect() and was added in 4.1.11; on a small fraction of |
| 906 |
// sites dbDelta silently fails to add it, so every captured 404 |
| 907 |
// emits "Unknown column 'canonical_url' in 'field list'" until |
| 908 |
// verifyColumns eventually retries. Eagerly running the targeted |
| 909 |
// add closes that window. |
| 910 |
if ($ddlEntry['bareTableName'] === 'abj404_redirects') { |
| 911 |
$this->ensureRedirectsCanonicalUrlColumn($tableName); |
| 912 |
} |
| 913 |
|
| 914 |
$this->verifyColumns($tableName, $query); |
| 915 |
} |
| 916 |
|
| 917 |
// Table-specific post-creation steps. |
| 918 |
$logsTable = $this->dao->doTableNameReplacements("{wp_abj404_logsv2}"); |
| 919 |
$this->ensureLogsCompositeIndex($logsTable); |
| 920 |
|
| 921 |
// Mark view cache table as ensured so ensureViewSnapshotTableExists() skips redundant DDL. |
| 922 |
ABJ_404_Solution_DataAccess::setViewSnapshotTableEnsured(true); |
| 923 |
} |
| 924 |
|
| 925 |
/** |
| 926 |
* Verify that a CREATE TABLE actually materialized the named table on disk. |
| 927 |
* Returns true if the table exists, false (and logs a per-table error) if not. |
| 928 |
* |
| 929 |
* Distinguishes silently-failing CREATEs from generic SQL errors so the |
| 930 |
* debug log identifies which specific DDL didn't materialize. Common causes: |
| 931 |
* concurrent DROP from a parallel cron, SQL parse error swallowed by |
| 932 |
* queryAndGetResults, prefix drift between request and table_prefix in |
| 933 |
* wp-config, or missing CREATE TABLE privileges on the DB user. |
| 934 |
* |
| 935 |
* @param string $tableName Fully-qualified table name (with prefix). |
| 936 |
* @param string $placeholder Original placeholder (e.g. "{wp_abj404_redirects}") for diagnostic context. |
| 937 |
* @return bool True if table exists post-CREATE, false otherwise. |
| 938 |
*/ |
| 939 |
private function verifyTableMaterialized(string $tableName, string $placeholder): bool { |
| 940 |
global $wpdb; |
| 941 |
if (!isset($wpdb)) { |
| 942 |
return false; |
| 943 |
} |
| 944 |
// @utf8-audit: opt-out — $tableName is fully-qualified plugin table |
| 945 |
// name from doTableNameReplacements / $wpdb->prefix; never user input. |
| 946 |
// DAO-bypass-approved: Schema-bootstrap inside verifyTableMaterialized() — verifies CREATE TABLE actually materialized; DAO timeout wrapper is irrelevant for DDL existence probe |
| 947 |
$found = $wpdb->get_var("SHOW TABLES LIKE '" . esc_sql($tableName) . "'"); |
| 948 |
if ($found === $tableName) { |
| 949 |
return true; |
| 950 |
} |
| 951 |
$this->logger->errorMessage( |
| 952 |
"CREATE TABLE did not materialize '" . $tableName . "' " |
| 953 |
. "(placeholder " . $placeholder . "). " |
| 954 |
. "Table is still missing on disk after CREATE TABLE IF NOT EXISTS ran. " |
| 955 |
. "Likely causes: concurrent DROP from a parallel request, " |
| 956 |
. "SQL parse error suppressed by queryAndGetResults, " |
| 957 |
. "prefix mismatch between request and wp-config table_prefix, " |
| 958 |
. "or insufficient CREATE TABLE privileges on the DB user." |
| 959 |
); |
| 960 |
return false; |
| 961 |
} |
| 962 |
|
| 963 |
/** |
| 964 |
* @param string $createTableSql |
| 965 |
* @return string |
| 966 |
*/ |
| 967 |
function applyPluginTableCharsetCollate($createTableSql) { |
| 968 |
global $wpdb; |
| 969 |
if (!is_string($createTableSql) || $createTableSql === '') { |
| 970 |
return $createTableSql; |
| 971 |
} |
| 972 |
// If the statement already specifies charset/collation, don't override. |
| 973 |
if (preg_match('/\b(?:default\s+)?(?:character\s+set|charset|collate)\b/i', $createTableSql)) { |
| 974 |
return $createTableSql; |
| 975 |
} |
| 976 |
|
| 977 |
// Always prefer utf8mb4 for plugin tables, regardless of site defaults. |
| 978 |
$collate = 'utf8mb4_unicode_ci'; |
| 979 |
if (!empty($wpdb->collate) && stripos($wpdb->collate, 'utf8mb4') !== false) { |
| 980 |
$collate = $wpdb->collate; |
| 981 |
} |
| 982 |
|
| 983 |
return rtrim($createTableSql) . " DEFAULT CHARACTER SET utf8mb4 COLLATE {$collate}"; |
| 984 |
} |
| 985 |
|
| 986 |
/** |
| 987 |
* Schedule a background multisite batch operation. |
| 988 |
* |
| 989 |
* @param string $optionPrefix e.g. 'abj404_activation' or 'abj404_upgrade' |
| 990 |
* @param string $hookName e.g. 'abj404_network_activation_background' |
| 991 |
* @param string $label Human-readable label for log messages, e.g. 'activation' |
| 992 |
* @param int $alreadyProcessedBlogId Blog ID already processed on this request. |
| 993 |
* @return void |
| 994 |
*/ |
| 995 |
private function scheduleBackgroundMultisiteBatch(string $optionPrefix, string $hookName, string $label, int $alreadyProcessedBlogId): void { |
| 996 |
update_site_option($optionPrefix . '_processed_blogs', array($alreadyProcessedBlogId)); |
| 997 |
update_site_option($optionPrefix . '_in_progress', true); |
| 998 |
|
| 999 |
if (wp_next_scheduled($hookName)) { |
| 1000 |
$this->logger->debugMessage("Background multisite $label already scheduled."); |
| 1001 |
return; |
| 1002 |
} |
| 1003 |
|
| 1004 |
$scheduled = wp_schedule_single_event(time() + 30, $hookName); |
| 1005 |
|
| 1006 |
if ($scheduled === false) { |
| 1007 |
$this->logger->errorMessage("Failed to schedule background multisite $label. Remaining sites will not be processed automatically."); |
| 1008 |
} else { |
| 1009 |
$this->logger->infoMessage("Background multisite $label scheduled successfully."); |
| 1010 |
} |
| 1011 |
} |
| 1012 |
|
| 1013 |
/** |
| 1014 |
* Process a batch of multisite sites with the given per-site action. |
| 1015 |
* |
| 1016 |
* @param string $optionPrefix e.g. 'abj404_activation' or 'abj404_upgrade' |
| 1017 |
* @param string $hookName e.g. 'abj404_network_activation_background' |
| 1018 |
* @param string $label Human-readable label for log messages, e.g. 'activation' |
| 1019 |
* @param callable $perSiteAction Called for each site (receives int $siteId). |
| 1020 |
* @return bool True if all sites are done, false if more batches needed. |
| 1021 |
*/ |
| 1022 |
public function processMultisiteBatch(string $optionPrefix, string $hookName, string $label, callable $perSiteAction): bool { |
| 1023 |
$processedBlogs = get_site_option($optionPrefix . '_processed_blogs', array()); |
| 1024 |
if (!is_array($processedBlogs)) { |
| 1025 |
$processedBlogs = array(); |
| 1026 |
} |
| 1027 |
|
| 1028 |
$allSites = get_sites(array('fields' => 'ids', 'number' => 0)); |
| 1029 |
$remainingSites = array_diff($allSites, $processedBlogs); |
| 1030 |
|
| 1031 |
if (empty($remainingSites)) { |
| 1032 |
delete_site_option($optionPrefix . '_processed_blogs'); |
| 1033 |
delete_site_option($optionPrefix . '_in_progress'); |
| 1034 |
$this->logger->infoMessage("Background multisite $label complete. All sites processed."); |
| 1035 |
return true; |
| 1036 |
} |
| 1037 |
|
| 1038 |
$batchSize = 10; |
| 1039 |
$sitesToProcess = array_slice($remainingSites, 0, $batchSize); |
| 1040 |
|
| 1041 |
$this->logger->infoMessage(sprintf( |
| 1042 |
"Processing multisite $label batch: %d sites (of %d remaining)", |
| 1043 |
count($sitesToProcess), |
| 1044 |
count($remainingSites) |
| 1045 |
)); |
| 1046 |
|
| 1047 |
foreach ($sitesToProcess as $siteId) { |
| 1048 |
try { |
| 1049 |
switch_to_blog($siteId); |
| 1050 |
$this->logger->debugMessage(sprintf("Processing $label for site ID %d...", $siteId)); |
| 1051 |
|
| 1052 |
$perSiteAction((int)$siteId); |
| 1053 |
|
| 1054 |
$processedBlogs[] = $siteId; |
| 1055 |
update_site_option($optionPrefix . '_processed_blogs', $processedBlogs); |
| 1056 |
|
| 1057 |
$this->logger->debugMessage(sprintf("Successfully processed $label for site ID %d", $siteId)); |
| 1058 |
} catch (Throwable $e) { |
| 1059 |
$this->logger->errorMessage(sprintf( |
| 1060 |
"Failed to process $label for site ID %d: %s", |
| 1061 |
$siteId, |
| 1062 |
$e->getMessage() |
| 1063 |
)); |
| 1064 |
$processedBlogs[] = $siteId; |
| 1065 |
update_site_option($optionPrefix . '_processed_blogs', $processedBlogs); |
| 1066 |
} finally { |
| 1067 |
restore_current_blog(); |
| 1068 |
} |
| 1069 |
} |
| 1070 |
|
| 1071 |
$stillRemaining = count($remainingSites) - count($sitesToProcess); |
| 1072 |
if ($stillRemaining > 0) { |
| 1073 |
$this->logger->infoMessage(sprintf( |
| 1074 |
"Batch complete. Rescheduling for %d remaining sites.", |
| 1075 |
$stillRemaining |
| 1076 |
)); |
| 1077 |
wp_schedule_single_event(time() + 30, $hookName); |
| 1078 |
return false; |
| 1079 |
} else { |
| 1080 |
delete_site_option($optionPrefix . '_processed_blogs'); |
| 1081 |
delete_site_option($optionPrefix . '_in_progress'); |
| 1082 |
$this->logger->infoMessage("Background multisite $label complete. All sites processed."); |
| 1083 |
return true; |
| 1084 |
} |
| 1085 |
} |
| 1086 |
|
| 1087 |
/** |
| 1088 |
* Schedule a background activation for all network sites except the one that |
| 1089 |
* was just activated synchronously. |
| 1090 |
* |
| 1091 |
* @param int $alreadyProcessedBlogId Blog ID of the site already activated. |
| 1092 |
* @return void |
| 1093 |
*/ |
| 1094 |
private function scheduleBackgroundMultisiteActivation(int $alreadyProcessedBlogId): void { |
| 1095 |
$this->scheduleBackgroundMultisiteBatch( |
| 1096 |
'abj404_activation', 'abj404_network_activation_background', 'activation', $alreadyProcessedBlogId |
| 1097 |
); |
| 1098 |
} |
| 1099 |
|
| 1100 |
/** |
| 1101 |
* Process multisite activation in batches (called by WP-Cron). |
| 1102 |
* |
| 1103 |
* Processes remaining sites that weren't handled during initial activation. |
| 1104 |
* Processes up to 10 sites per run to avoid timeouts, then reschedules itself |
| 1105 |
* if more sites remain. |
| 1106 |
* |
| 1107 |
* @return bool True if all sites processed, false if more remain |
| 1108 |
*/ |
| 1109 |
public function processMultisiteActivationBatch(): bool { |
| 1110 |
return $this->processMultisiteBatch( |
| 1111 |
'abj404_activation', |
| 1112 |
'abj404_network_activation_background', |
| 1113 |
'activation', |
| 1114 |
function (int $siteId): void { |
| 1115 |
add_option('abj404_settings', '', '', false); |
| 1116 |
|
| 1117 |
$this->runInitialCreateTables(); |
| 1118 |
$this->correctCollations(); |
| 1119 |
$this->updateTableEngineToInnoDB(); |
| 1120 |
$this->createIndexes(); |
| 1121 |
$this->backfillRedirectsCanonicalUrl(); |
| 1122 |
$this->renameAbj404TablesToLowerCase(); |
| 1123 |
|
| 1124 |
// Canonical self-heal prologue runs after schema creation so |
| 1125 |
// SelfHealingPrologueReachabilityTest sees per-subsite activation |
| 1126 |
// reach the same recovery primitives as the daily cron. |
| 1127 |
$this->runSelfHealPrologue(); |
| 1128 |
|
| 1129 |
ABJ_404_Solution_PluginLogic::doRegisterCrons(); |
| 1130 |
|
| 1131 |
$logic = abj_service('plugin_logic'); |
| 1132 |
$logic->doUpdateDBVersionOption(); |
| 1133 |
} |
| 1134 |
); |
| 1135 |
} |
| 1136 |
|
| 1137 |
/** |
| 1138 |
* Schedule a background upgrade for all network sites except the one that |
| 1139 |
* was just upgraded synchronously. |
| 1140 |
* |
| 1141 |
* @param int $alreadyProcessedBlogId Blog ID of the site already upgraded. |
| 1142 |
* @return void |
| 1143 |
*/ |
| 1144 |
private function scheduleBackgroundMultisiteUpgrade(int $alreadyProcessedBlogId): void { |
| 1145 |
$this->scheduleBackgroundMultisiteBatch( |
| 1146 |
'abj404_upgrade', 'abj404_network_upgrade_background', 'upgrade', $alreadyProcessedBlogId |
| 1147 |
); |
| 1148 |
} |
| 1149 |
|
| 1150 |
/** |
| 1151 |
* Process multisite plugin upgrade in batches (called by WP-Cron). |
| 1152 |
* |
| 1153 |
* Upgrades remaining sites that weren't handled during the initial upgrade. |
| 1154 |
* Processes up to 10 sites per run to avoid timeouts, then reschedules itself |
| 1155 |
* if more sites remain. |
| 1156 |
* |
| 1157 |
* @return bool True if all sites processed, false if more remain. |
| 1158 |
*/ |
| 1159 |
public function processMultisiteUpgradeBatch(): bool { |
| 1160 |
return $this->processMultisiteBatch( |
| 1161 |
'abj404_upgrade', |
| 1162 |
'abj404_network_upgrade_background', |
| 1163 |
'upgrade', |
| 1164 |
function (int $siteId): void { |
| 1165 |
// Run the full upgrade sequence for this site without going through |
| 1166 |
// createDatabaseTables() — that would re-schedule more background tasks. |
| 1167 |
$this->correctIssuesBefore(); |
| 1168 |
$this->runInitialCreateTables(); |
| 1169 |
$this->correctCollations(); |
| 1170 |
$this->updateTableEngineToInnoDB(); |
| 1171 |
$this->createIndexes(); |
| 1172 |
$this->backfillRedirectsCanonicalUrl(); |
| 1173 |
$this->renameAbj404TablesToLowerCase(); |
| 1174 |
$this->correctIssuesAfter(); |
| 1175 |
|
| 1176 |
// Canonical self-heal prologue closes the per-subsite upgrade |
| 1177 |
// batch so SelfHealingPrologueReachabilityTest can prove the |
| 1178 |
// multisite upgrade path reaches the same recovery primitives |
| 1179 |
// as the daily cron tick. |
| 1180 |
$this->runSelfHealPrologue(); |
| 1181 |
|
| 1182 |
$logic = abj_service('plugin_logic'); |
| 1183 |
$logic->doUpdateDBVersionOption(); |
| 1184 |
} |
| 1185 |
); |
| 1186 |
} |
| 1187 |
|
| 1188 |
/** |
| 1189 |
* Create tables for all sites in a multisite network. |
| 1190 |
* |
| 1191 |
* This function iterates through all sites in the network and creates |
| 1192 |
* the plugin's database tables for each site. This ensures that when |
| 1193 |
* the plugin is network-activated, all sites have the necessary tables. |
| 1194 |
* |
| 1195 |
* @since 3.0.1 |
| 1196 |
*/ |
| 1197 |
/** |
| 1198 |
* @return void |
| 1199 |
* @phpstan-ignore-next-line method.unused |
| 1200 |
*/ |
| 1201 |
private function createTablesForAllSites() { |
| 1202 |
global $wpdb; |
| 1203 |
|
| 1204 |
// Get all sites in the network |
| 1205 |
$sites = get_sites(array('fields' => 'ids', 'number' => 0)); |
| 1206 |
$totalSites = count($sites); |
| 1207 |
$successCount = 0; |
| 1208 |
$failureCount = 0; |
| 1209 |
|
| 1210 |
$this->logger->infoMessage(sprintf( |
| 1211 |
"Starting network-wide table creation for %d sites.", |
| 1212 |
$totalSites |
| 1213 |
)); |
| 1214 |
|
| 1215 |
foreach ($sites as $siteId) { |
| 1216 |
try { |
| 1217 |
// Switch to the site |
| 1218 |
switch_to_blog($siteId); |
| 1219 |
|
| 1220 |
$currentPrefix = $wpdb->prefix; |
| 1221 |
$this->logger->debugMessage(sprintf( |
| 1222 |
"Creating tables for site ID %d (prefix: %s)...", |
| 1223 |
$siteId, |
| 1224 |
$currentPrefix |
| 1225 |
)); |
| 1226 |
|
| 1227 |
// Create tables for this site |
| 1228 |
$this->runInitialCreateTables(); |
| 1229 |
$this->correctCollations(); |
| 1230 |
$this->updateTableEngineToInnoDB(); |
| 1231 |
$this->createIndexes(); |
| 1232 |
$this->backfillRedirectsCanonicalUrl(); |
| 1233 |
|
| 1234 |
$successCount++; |
| 1235 |
$this->logger->debugMessage(sprintf( |
| 1236 |
"Successfully created tables for site ID %d (prefix: %s)", |
| 1237 |
$siteId, |
| 1238 |
$currentPrefix |
| 1239 |
)); |
| 1240 |
|
| 1241 |
} catch (Throwable $e) { |
| 1242 |
$failureCount++; |
| 1243 |
$this->logger->errorMessage(sprintf( |
| 1244 |
"Failed to create tables for site ID %d (prefix: %s): %s", |
| 1245 |
$siteId, |
| 1246 |
$wpdb->prefix, |
| 1247 |
$e->getMessage() |
| 1248 |
)); |
| 1249 |
} finally { |
| 1250 |
// Always restore blog context |
| 1251 |
restore_current_blog(); |
| 1252 |
} |
| 1253 |
} |
| 1254 |
|
| 1255 |
// Log summary |
| 1256 |
$this->logger->infoMessage(sprintf( |
| 1257 |
"Network-wide table creation complete: %d successful, %d failed out of %d total sites.", |
| 1258 |
$successCount, |
| 1259 |
$failureCount, |
| 1260 |
$totalSites |
| 1261 |
)); |
| 1262 |
|
| 1263 |
if ($failureCount > 0) { |
| 1264 |
$this->logger->errorMessage(sprintf( |
| 1265 |
"Warning: Table creation failed for %d sites. Check error logs for details.", |
| 1266 |
$failureCount |
| 1267 |
)); |
| 1268 |
} |
| 1269 |
} |
| 1270 |
|
| 1271 |
|
| 1272 |
/** |
| 1273 |
* @param string $tableName |
| 1274 |
* @param string $createTableStatementGoal |
| 1275 |
* @return void |
| 1276 |
*/ |
| 1277 |
function verifyColumns($tableName, $createTableStatementGoal) { |
| 1278 |
$updatesWereNeeded = false; |
| 1279 |
|
| 1280 |
// find the differences |
| 1281 |
$tableDifferences = $this->getTableDifferences($tableName, $createTableStatementGoal); |
| 1282 |
$updateCols = is_array($tableDifferences['updateTheseColumns']) ? $tableDifferences['updateTheseColumns'] : []; |
| 1283 |
$createCols = is_array($tableDifferences['createTheseColumns']) ? $tableDifferences['createTheseColumns'] : []; |
| 1284 |
if (count($updateCols) > 0 || |
| 1285 |
count($createCols) > 0) { |
| 1286 |
$updatesWereNeeded = true; |
| 1287 |
} |
| 1288 |
// make the changes |
| 1289 |
$this->updateATableBasedOnDifferences($tableName, $tableDifferences); |
| 1290 |
|
| 1291 |
// verify that there are now no changes that need to be made. |
| 1292 |
$tableDifferences = $this->getTableDifferences($tableName, $createTableStatementGoal); |
| 1293 |
$updateCols = is_array($tableDifferences['updateTheseColumns']) ? $tableDifferences['updateTheseColumns'] : []; |
| 1294 |
$createCols = is_array($tableDifferences['createTheseColumns']) ? $tableDifferences['createTheseColumns'] : []; |
| 1295 |
|
| 1296 |
if (count($updateCols) > 0 || |
| 1297 |
count($createCols) > 0) { |
| 1298 |
|
| 1299 |
// Persistent post-update diff is usually a benign DDL-normalizer mismatch |
| 1300 |
// (parser misreads a comment, column landed in a slightly-different form). |
| 1301 |
// Plugin keeps functioning, so log at warn (stays in debug log without |
| 1302 |
// crossing the email-threshold reporter). Defensive coding philosophy #8. |
| 1303 |
$this->logger->warn("There are still differences after updating the " . |
| 1304 |
$tableName . " table. " . print_r($tableDifferences, true)); |
| 1305 |
|
| 1306 |
} else if ($updatesWereNeeded) { |
| 1307 |
$this->logger->infoMessage("No more differences found after updating the " . |
| 1308 |
$tableName . " table columns. All is well."); |
| 1309 |
} |
| 1310 |
} |
| 1311 |
|
| 1312 |
/** |
| 1313 |
* @param string $tableName |
| 1314 |
* @param string $createTableStatementGoal |
| 1315 |
* @return array<string, mixed> |
| 1316 |
*/ |
| 1317 |
function getTableDifferences($tableName, $createTableStatementGoal) { |
| 1318 |
|
| 1319 |
// get the current create table statement |
| 1320 |
$existingTableSQL = $this->dao->getCreateTableDDL($tableName); |
| 1321 |
|
| 1322 |
$existingTableSQL = strtolower($this->removeCommentsFromColumns($existingTableSQL)); |
| 1323 |
$createTableStatementGoal = strtolower( |
| 1324 |
$this->removeCommentsFromColumns($createTableStatementGoal)); |
| 1325 |
|
| 1326 |
// remove the "COLLATE xxx" from the columns. |
| 1327 |
$removeCollatePattern = '/collate[= ]\w+ ?/'; |
| 1328 |
$existingTableSQL = preg_replace($removeCollatePattern, "", $existingTableSQL) ?? ''; |
| 1329 |
$createTableStatementGoal = preg_replace($removeCollatePattern, "", $createTableStatementGoal) ?? ''; |
| 1330 |
|
| 1331 |
// remove the int size format from columns because it doesn't matter. |
| 1332 |
$removeIntSizePattern = '/( \w*?int)(\(\d+\))/m'; |
| 1333 |
$existingTableSQL = preg_replace($removeIntSizePattern, "$1", $existingTableSQL) ?? ''; |
| 1334 |
$createTableStatementGoal = preg_replace($removeIntSizePattern, "$1", $createTableStatementGoal) ?? ''; |
| 1335 |
|
| 1336 |
// MySQL's SHOW CREATE TABLE omits "DEFAULT NULL" for TEXT/BLOB columns |
| 1337 |
// (it's implicit). Normalize both sides so this doesn't flag as a mismatch. |
| 1338 |
$removeTextDefaultNull = '/(text|blob|mediumtext|longtext|tinytext|mediumblob|longblob|tinyblob)\s+default\s+null/'; |
| 1339 |
$existingTableSQL = preg_replace($removeTextDefaultNull, "$1", $existingTableSQL) ?? $existingTableSQL; |
| 1340 |
$createTableStatementGoal = preg_replace($removeTextDefaultNull, "$1", $createTableStatementGoal) ?? $createTableStatementGoal; |
| 1341 |
|
| 1342 |
// get column names and types pattern (backticks are optional — accept both styles); |
| 1343 |
// (?!key\b) guards against accidentally matching PRIMARY KEY / UNIQUE KEY lines. |
| 1344 |
$colNamesAndTypesPattern = "/\s+?(`?(\w+?)`? (?!key\b)(\w.+)\s?),/"; |
| 1345 |
$existingTableMatches = null; |
| 1346 |
$goalTableMatches = null; |
| 1347 |
// match the existing table. use preg_match_all because I couldn't find an |
| 1348 |
// "_all" option when using mb_ereg. |
| 1349 |
preg_match_all($colNamesAndTypesPattern, $existingTableSQL, $existingTableMatches); |
| 1350 |
preg_match_all($colNamesAndTypesPattern, $createTableStatementGoal, $goalTableMatches); |
| 1351 |
|
| 1352 |
// get the matches. |
| 1353 |
$goalTableMatchesColumnNames = $goalTableMatches[2]; |
| 1354 |
$existingTableMatchesColumnNames = $existingTableMatches[2]; |
| 1355 |
|
| 1356 |
// remove any spaces |
| 1357 |
$goalTableMatchesColumnNames = array_map('trim', $goalTableMatchesColumnNames); |
| 1358 |
$existingTableMatchesColumnNames = array_map('trim', $existingTableMatchesColumnNames); |
| 1359 |
|
| 1360 |
// Safety guard: if the goal DDL produced zero column names the regex failed |
| 1361 |
// to parse it (e.g. malformed or unparseable DDL). In that case never drop |
| 1362 |
// any existing columns — an empty goal list would otherwise flag every real |
| 1363 |
// column as "extra" and wipe the table. |
| 1364 |
if (empty($goalTableMatchesColumnNames) && !empty($existingTableMatchesColumnNames)) { |
| 1365 |
$this->logger->errorMessage("Goal DDL for " . $tableName . |
| 1366 |
" produced no column matches -- the DDL may be malformed or unparseable. " . |
| 1367 |
"Skipping column comparison to prevent data loss."); |
| 1368 |
$dropTheseColumns = []; |
| 1369 |
$createTheseColumns = []; |
| 1370 |
return array("updateTheseColumns" => [], |
| 1371 |
"dropTheseColumns" => [], |
| 1372 |
"createTheseColumns" => [], |
| 1373 |
"goalTableMatchesColumnDDL" => [], |
| 1374 |
"existingTableMatchesColumnDDL" => [], |
| 1375 |
"goalTableMatches" => $goalTableMatches, |
| 1376 |
"goalTableMatchesColumnNames" => [] |
| 1377 |
); |
| 1378 |
} |
| 1379 |
|
| 1380 |
// see if some columns need to be created. |
| 1381 |
$dropTheseColumns = array_diff($existingTableMatchesColumnNames, |
| 1382 |
$goalTableMatchesColumnNames); |
| 1383 |
$createTheseColumns = array_diff($goalTableMatchesColumnNames, |
| 1384 |
$existingTableMatchesColumnNames); |
| 1385 |
|
| 1386 |
// get the ddl for each column |
| 1387 |
$goalTableMatchesColumnDDL = $goalTableMatches[1]; |
| 1388 |
$existingTableMatchesColumnDDL = $existingTableMatches[1]; |
| 1389 |
|
| 1390 |
// remove any spaces |
| 1391 |
$goalTableMatchesColumnDDL = array_map('trim', $goalTableMatchesColumnDDL); |
| 1392 |
$existingTableMatchesColumnDDL = array_map('trim', $existingTableMatchesColumnDDL); |
| 1393 |
|
| 1394 |
// normalize minor differences between mysql versions (strip backticks so DDL |
| 1395 |
// files using either quoting style compare equal to SHOW CREATE TABLE output) |
| 1396 |
$goalTableMatchesColumnDDL = array_map([$this, 'normalizeColumnDDL'], $goalTableMatchesColumnDDL); |
| 1397 |
$existingTableMatchesColumnDDL = array_map([$this, 'normalizeColumnDDL'], $existingTableMatchesColumnDDL); |
| 1398 |
|
| 1399 |
// see if anything needs to be updated or created. |
| 1400 |
$updateTheseColumns = array_diff($goalTableMatchesColumnDDL, |
| 1401 |
$existingTableMatchesColumnDDL); |
| 1402 |
|
| 1403 |
// wrap the results |
| 1404 |
$results = array("updateTheseColumns" => $updateTheseColumns, |
| 1405 |
"dropTheseColumns" => $dropTheseColumns, |
| 1406 |
"createTheseColumns" => $createTheseColumns, |
| 1407 |
"goalTableMatchesColumnDDL" => $goalTableMatchesColumnDDL, |
| 1408 |
"existingTableMatchesColumnDDL" => $existingTableMatchesColumnDDL, |
| 1409 |
"goalTableMatches" => $goalTableMatches, |
| 1410 |
"goalTableMatchesColumnNames" => $goalTableMatchesColumnNames |
| 1411 |
); |
| 1412 |
return $results; |
| 1413 |
} |
| 1414 |
|
| 1415 |
/** |
| 1416 |
* @param string $tableName |
| 1417 |
* @param array<string, mixed> $tableDifferences |
| 1418 |
* @return void |
| 1419 |
*/ |
| 1420 |
function updateATableBasedOnDifferences($tableName, $tableDifferences) { |
| 1421 |
|
| 1422 |
/** @var array<int|string, mixed> $dropTheseColumns */ |
| 1423 |
$dropTheseColumns = is_array($tableDifferences['dropTheseColumns']) ? $tableDifferences['dropTheseColumns'] : []; |
| 1424 |
/** @var array<int|string, mixed> $updateTheseColumns */ |
| 1425 |
$updateTheseColumns = is_array($tableDifferences['updateTheseColumns']) ? $tableDifferences['updateTheseColumns'] : []; |
| 1426 |
/** @var array<int|string, mixed> $createTheseColumns */ |
| 1427 |
$createTheseColumns = is_array($tableDifferences['createTheseColumns']) ? $tableDifferences['createTheseColumns'] : []; |
| 1428 |
$goalTableMatchesColumnDDL = is_array($tableDifferences['goalTableMatchesColumnDDL']) ? $tableDifferences['goalTableMatchesColumnDDL'] : []; |
| 1429 |
$existingTableMatchesColumnDDL = is_array($tableDifferences['existingTableMatchesColumnDDL']) ? $tableDifferences['existingTableMatchesColumnDDL'] : []; |
| 1430 |
/** @var array<int, array<int, mixed>> $goalTableMatches */ |
| 1431 |
$goalTableMatches = is_array($tableDifferences['goalTableMatches']) ? $tableDifferences['goalTableMatches'] : []; |
| 1432 |
/** @var array<int|string, mixed> $goalTableMatchesColumnNames */ |
| 1433 |
$goalTableMatchesColumnNames = is_array($tableDifferences['goalTableMatchesColumnNames']) ? $tableDifferences['goalTableMatchesColumnNames'] : []; |
| 1434 |
|
| 1435 |
// drop unnecessary columns — but never drop ALL columns (MySQL error: |
| 1436 |
// "You can't delete all columns with ALTER TABLE; use DROP TABLE instead"). |
| 1437 |
// This happens when a table is completely restructured and every existing |
| 1438 |
// column name differs from the goal schema. |
| 1439 |
$existingColumnCount = count($existingTableMatchesColumnDDL); |
| 1440 |
if (count($dropTheseColumns) > 0 && count($dropTheseColumns) >= $existingColumnCount) { |
| 1441 |
$this->logger->warn("Skipping column drops on " . $tableName . |
| 1442 |
" because it would remove all " . $existingColumnCount . |
| 1443 |
" existing columns. Drops requested: " . implode(', ', $dropTheseColumns)); |
| 1444 |
} else { |
| 1445 |
foreach ($dropTheseColumns as $colName) { |
| 1446 |
$query = "alter table " . $tableName . " drop " . $colName; |
| 1447 |
$this->dao->queryAndGetResults($query); |
| 1448 |
$this->logger->infoMessage("I dropped a column (1): " . $query); |
| 1449 |
} |
| 1450 |
} |
| 1451 |
|
| 1452 |
// say why we're doing what we're doing. |
| 1453 |
if (count($updateTheseColumns) > 0) { |
| 1454 |
$this->logger->infoMessage(self::$uniqID . ": On " . $tableName . |
| 1455 |
" I'm updating various columns because we want: \n`" . |
| 1456 |
print_r($goalTableMatchesColumnDDL, true) . "\n but we have: \n" . |
| 1457 |
print_r($existingTableMatchesColumnDDL, true)); |
| 1458 |
} |
| 1459 |
|
| 1460 |
// create missing columns |
| 1461 |
// Normalize $goalMatchesSub using the same normalizeColumnDDL() that |
| 1462 |
// getTableDifferences() uses, so array_search() can find the right index. |
| 1463 |
$goalMatchesSub = is_array($goalTableMatches[1] ?? null) ? $goalTableMatches[1] : []; |
| 1464 |
$goalMatchesSub = array_map([$this, 'normalizeColumnDDL'], $goalMatchesSub); |
| 1465 |
foreach ($updateTheseColumns as $colDDL) { |
| 1466 |
// find the colum name. |
| 1467 |
$matchIndex = array_search($colDDL, $goalMatchesSub); |
| 1468 |
if ($matchIndex === false) { |
| 1469 |
$this->logger->warn("Could not match column DDL to goal schema, skipping: " . $colDDL); |
| 1470 |
continue; |
| 1471 |
} |
| 1472 |
$colName = is_string($goalTableMatchesColumnNames[$matchIndex] ?? null) ? $goalTableMatchesColumnNames[$matchIndex] : ''; |
| 1473 |
|
| 1474 |
// if the column exists then update it. otherwise create it. |
| 1475 |
if (!in_array($colName, $createTheseColumns)) { |
| 1476 |
// update the existing column. |
| 1477 |
// ALTER TABLE `mywp_abj404_redirects` CHANGE `status` `status` BIGINT(19) NOT NULL; |
| 1478 |
$updateColStatement = "alter table " . $tableName . " change " . $colName . |
| 1479 |
" " . $colDDL; |
| 1480 |
$this->dao->queryAndGetResults($updateColStatement); |
| 1481 |
$this->logger->infoMessage("I updated a column: " . $updateColStatement); |
| 1482 |
|
| 1483 |
} else { |
| 1484 |
// create the column. |
| 1485 |
$createColStatement = "alter table " . $tableName . " add " . $colDDL; |
| 1486 |
$this->dao->queryAndGetResults($createColStatement); |
| 1487 |
$this->logger->infoMessage("I added a column: " . $createColStatement); |
| 1488 |
} |
| 1489 |
|
| 1490 |
$this->handleSpecificCases($tableName, $colName); |
| 1491 |
} |
| 1492 |
} |
| 1493 |
|
| 1494 |
/** Create table DDL is returned without SQL comments of any kind. |
| 1495 |
* Strips block comments (slash-star ... star-slash), line comments (-- ...), |
| 1496 |
* and inline COMMENT 'text' column clauses so the column-name regex in |
| 1497 |
* getTableDifferences() cannot mistake comment text for column definitions. |
| 1498 |
* @param string|null $createTableDDL |
| 1499 |
* @return string |
| 1500 |
*/ |
| 1501 |
function removeCommentsFromColumns($createTableDDL) { |
| 1502 |
if ($createTableDDL === null) { |
| 1503 |
return ''; |
| 1504 |
} |
| 1505 |
$ddl = (string) $createTableDDL; |
| 1506 |
// Strip block comments (slash-star ... star-slash), including multi-line. |
| 1507 |
$ddl = preg_replace('/\/\*.*?\*\//s', '', $ddl) ?? $ddl; |
| 1508 |
// Strip line comments (-- ...). |
| 1509 |
$ddl = preg_replace('/--[^\r\n]*/', '', $ddl) ?? $ddl; |
| 1510 |
// Strip inline COMMENT 'text', clauses from column definitions. |
| 1511 |
return preg_replace('/ (?:COMMENT.+?,[\r\n])/', ",\n", $ddl) ?? $ddl; |
| 1512 |
} |
| 1513 |
/** |
| 1514 |
* Normalize a single column DDL fragment for comparison. |
| 1515 |
* |
| 1516 |
* Strips backticks and unquotes integer defaults so that DDL from |
| 1517 |
* SHOW CREATE TABLE (e.g. default '1') matches the goal DDL file |
| 1518 |
* (e.g. default 1). Used by both getTableDifferences() and |
| 1519 |
* updateATableBasedOnDifferences() — a single source of truth |
| 1520 |
* prevents the two normalization sites from drifting out of sync. |
| 1521 |
* |
| 1522 |
* @param mixed $ddl A column DDL string (or non-string from regex match) |
| 1523 |
* @return string |
| 1524 |
*/ |
| 1525 |
function normalizeColumnDDL($ddl): string { |
| 1526 |
$ddlStr = is_string($ddl) ? $ddl : ''; |
| 1527 |
$normalized = strtolower(str_replace('`', '', trim($ddlStr))); |
| 1528 |
$normalized = preg_replace("/default '(\d+)'/", 'default $1', $normalized) ?? $normalized; |
| 1529 |
// MySQL omits DEFAULT NULL for nullable columns — strip it so DDL file |
| 1530 |
// and SHOW CREATE TABLE produce identical normalized strings. |
| 1531 |
$normalized = preg_replace('/\s+default\s+null\b/', '', $normalized) ?? $normalized; |
| 1532 |
return $normalized; |
| 1533 |
} |
| 1534 |
|
| 1535 |
/** |
| 1536 |
* @param string $tableName |
| 1537 |
* @return void |
| 1538 |
*/ |
| 1539 |
function deleteIndexes($tableName) { |
| 1540 |
|
| 1541 |
// get the indexes list. |
| 1542 |
$results = $this->dao->queryAndGetResults("show index from " . $tableName . |
| 1543 |
" where key_name != 'PRIMARY'"); |
| 1544 |
/** @var array<int, array<string, mixed>> $rows */ |
| 1545 |
$rows = isset($results['rows']) && is_array($results['rows']) ? $results['rows'] : []; |
| 1546 |
|
| 1547 |
if (empty($rows)) { |
| 1548 |
return; |
| 1549 |
} |
| 1550 |
|
| 1551 |
// find the key_name column because the case can be different on different systems. |
| 1552 |
$keyNameColumn = 'key_name'; |
| 1553 |
$aRow = $rows[0]; |
| 1554 |
foreach (array_keys($aRow) as $someKey) { |
| 1555 |
if ($this->f->strtolower((string)$someKey) == 'key_name') { |
| 1556 |
$keyNameColumn = (string)$someKey; |
| 1557 |
break; |
| 1558 |
} |
| 1559 |
} |
| 1560 |
|
| 1561 |
foreach ($rows as $row) { |
| 1562 |
// delete them |
| 1563 |
$indexName = $row[$keyNameColumn] ?? ''; |
| 1564 |
if (!is_string($indexName) || $indexName === '') { |
| 1565 |
continue; |
| 1566 |
} |
| 1567 |
$query = "alter table " . $tableName . " drop index " . $indexName; |
| 1568 |
$this->dao->queryAndGetResults($query); |
| 1569 |
} |
| 1570 |
} |
| 1571 |
} |
| 1572 |
|
