# 404-solution/4.2.0/includes/DatabaseUpgradesEtcTrait_SchemaDiff.php 404 Solution, version 4.2.0. 309 lines. - Page: https://pluginprobe.com/plugins/404-solution/4.2.0/code/includes/DatabaseUpgradesEtcTrait_SchemaDiff.php - Raw: https://pluginprobe.com/plugins/404-solution/4.2.0/raw/includes/DatabaseUpgradesEtcTrait_SchemaDiff.php - Modified: 2026-05-24T08:07:28+00:00 Line numbers below start at 1. Link to a line or a range by appending a fragment to the page URL, for example `https://pluginprobe.com/plugins/404-solution/4.2.0/code/includes/DatabaseUpgradesEtcTrait_SchemaDiff.php#L10-L20`. ```php getTableDifferences($tableName, $createTableStatementGoal); $updateCols = is_array($tableDifferences['updateTheseColumns']) ? $tableDifferences['updateTheseColumns'] : []; $createCols = is_array($tableDifferences['createTheseColumns']) ? $tableDifferences['createTheseColumns'] : []; if (count($updateCols) > 0 || count($createCols) > 0) { $updatesWereNeeded = true; } // make the changes $this->updateATableBasedOnDifferences($tableName, $tableDifferences); // verify that there are now no changes that need to be made. $tableDifferences = $this->getTableDifferences($tableName, $createTableStatementGoal); $updateCols = is_array($tableDifferences['updateTheseColumns']) ? $tableDifferences['updateTheseColumns'] : []; $createCols = is_array($tableDifferences['createTheseColumns']) ? $tableDifferences['createTheseColumns'] : []; if (count($updateCols) > 0 || count($createCols) > 0) { // Persistent post-update diff is usually a benign DDL-normalizer mismatch // (parser misreads a comment, column landed in a slightly-different form). // Plugin keeps functioning, so log at warn (stays in debug log without // crossing the email-threshold reporter). Defensive coding philosophy #8. $this->logger->warn("There are still differences after updating the " . $tableName . " table. " . print_r($tableDifferences, true)); } else if ($updatesWereNeeded) { $this->logger->infoMessage("No more differences found after updating the " . $tableName . " table columns. All is well."); } } /** * @param string $tableName * @param string $createTableStatementGoal * @return array */ function getTableDifferences($tableName, $createTableStatementGoal) { // get the current create table statement $existingTableSQL = $this->dbCore->getCreateTableDDL($tableName); $existingTableSQL = strtolower($this->removeCommentsFromColumns($existingTableSQL)); $createTableStatementGoal = strtolower( $this->removeCommentsFromColumns($createTableStatementGoal)); // remove the "COLLATE xxx" from the columns. $removeCollatePattern = '/collate[= ]\w+ ?/'; $existingTableSQL = preg_replace($removeCollatePattern, "", $existingTableSQL) ?? ''; $createTableStatementGoal = preg_replace($removeCollatePattern, "", $createTableStatementGoal) ?? ''; // remove the int size format from columns because it doesn't matter. $removeIntSizePattern = '/( \w*?int)(\(\d+\))/m'; $existingTableSQL = preg_replace($removeIntSizePattern, "$1", $existingTableSQL) ?? ''; $createTableStatementGoal = preg_replace($removeIntSizePattern, "$1", $createTableStatementGoal) ?? ''; // MySQL's SHOW CREATE TABLE omits "DEFAULT NULL" for TEXT/BLOB columns // (it's implicit). Normalize both sides so this doesn't flag as a mismatch. $removeTextDefaultNull = '/(text|blob|mediumtext|longtext|tinytext|mediumblob|longblob|tinyblob)\s+default\s+null/'; $existingTableSQL = preg_replace($removeTextDefaultNull, "$1", $existingTableSQL) ?? $existingTableSQL; $createTableStatementGoal = preg_replace($removeTextDefaultNull, "$1", $createTableStatementGoal) ?? $createTableStatementGoal; // get column names and types pattern (backticks are optional — accept both styles); // (?!key\b) guards against accidentally matching PRIMARY KEY / UNIQUE KEY lines. $colNamesAndTypesPattern = "/\s+?(`?(\w+?)`? (?!key\b)(\w.+)\s?),/"; $existingTableMatches = null; $goalTableMatches = null; // match the existing table. use preg_match_all because I couldn't find an // "_all" option when using mb_ereg. preg_match_all($colNamesAndTypesPattern, $existingTableSQL, $existingTableMatches); preg_match_all($colNamesAndTypesPattern, $createTableStatementGoal, $goalTableMatches); // get the matches. $goalTableMatchesColumnNames = $goalTableMatches[2]; $existingTableMatchesColumnNames = $existingTableMatches[2]; // remove any spaces $goalTableMatchesColumnNames = array_map('trim', $goalTableMatchesColumnNames); $existingTableMatchesColumnNames = array_map('trim', $existingTableMatchesColumnNames); // Safety guard: if the goal DDL produced zero column names the regex failed // to parse it (e.g. malformed or unparseable DDL). In that case never drop // any existing columns — an empty goal list would otherwise flag every real // column as "extra" and wipe the table. if (empty($goalTableMatchesColumnNames) && !empty($existingTableMatchesColumnNames)) { $this->logger->errorMessage("Goal DDL for " . $tableName . " produced no column matches -- the DDL may be malformed or unparseable. " . "Skipping column comparison to prevent data loss."); $dropTheseColumns = []; $createTheseColumns = []; return array("updateTheseColumns" => [], "dropTheseColumns" => [], "createTheseColumns" => [], "goalTableMatchesColumnDDL" => [], "existingTableMatchesColumnDDL" => [], "goalTableMatches" => $goalTableMatches, "goalTableMatchesColumnNames" => [] ); } // see if some columns need to be created. $dropTheseColumns = array_diff($existingTableMatchesColumnNames, $goalTableMatchesColumnNames); $createTheseColumns = array_diff($goalTableMatchesColumnNames, $existingTableMatchesColumnNames); // get the ddl for each column $goalTableMatchesColumnDDL = $goalTableMatches[1]; $existingTableMatchesColumnDDL = $existingTableMatches[1]; // remove any spaces $goalTableMatchesColumnDDL = array_map('trim', $goalTableMatchesColumnDDL); $existingTableMatchesColumnDDL = array_map('trim', $existingTableMatchesColumnDDL); // normalize minor differences between mysql versions (strip backticks so DDL // files using either quoting style compare equal to SHOW CREATE TABLE output) $goalTableMatchesColumnDDL = array_map([$this, 'normalizeColumnDDL'], $goalTableMatchesColumnDDL); $existingTableMatchesColumnDDL = array_map([$this, 'normalizeColumnDDL'], $existingTableMatchesColumnDDL); // see if anything needs to be updated or created. $updateTheseColumns = array_diff($goalTableMatchesColumnDDL, $existingTableMatchesColumnDDL); // wrap the results $results = array("updateTheseColumns" => $updateTheseColumns, "dropTheseColumns" => $dropTheseColumns, "createTheseColumns" => $createTheseColumns, "goalTableMatchesColumnDDL" => $goalTableMatchesColumnDDL, "existingTableMatchesColumnDDL" => $existingTableMatchesColumnDDL, "goalTableMatches" => $goalTableMatches, "goalTableMatchesColumnNames" => $goalTableMatchesColumnNames ); return $results; } /** * @param string $tableName * @param array $tableDifferences * @return void */ function updateATableBasedOnDifferences($tableName, $tableDifferences) { /** @var array $dropTheseColumns */ $dropTheseColumns = is_array($tableDifferences['dropTheseColumns']) ? $tableDifferences['dropTheseColumns'] : []; /** @var array $updateTheseColumns */ $updateTheseColumns = is_array($tableDifferences['updateTheseColumns']) ? $tableDifferences['updateTheseColumns'] : []; /** @var array $createTheseColumns */ $createTheseColumns = is_array($tableDifferences['createTheseColumns']) ? $tableDifferences['createTheseColumns'] : []; $goalTableMatchesColumnDDL = is_array($tableDifferences['goalTableMatchesColumnDDL']) ? $tableDifferences['goalTableMatchesColumnDDL'] : []; $existingTableMatchesColumnDDL = is_array($tableDifferences['existingTableMatchesColumnDDL']) ? $tableDifferences['existingTableMatchesColumnDDL'] : []; /** @var array> $goalTableMatches */ $goalTableMatches = is_array($tableDifferences['goalTableMatches']) ? $tableDifferences['goalTableMatches'] : []; /** @var array $goalTableMatchesColumnNames */ $goalTableMatchesColumnNames = is_array($tableDifferences['goalTableMatchesColumnNames']) ? $tableDifferences['goalTableMatchesColumnNames'] : []; // drop unnecessary columns — but never drop ALL columns (MySQL error: // "You can't delete all columns with ALTER TABLE; use DROP TABLE instead"). // This happens when a table is completely restructured and every existing // column name differs from the goal schema. $existingColumnCount = count($existingTableMatchesColumnDDL); if (count($dropTheseColumns) > 0 && count($dropTheseColumns) >= $existingColumnCount) { $this->logger->warn("Skipping column drops on " . $tableName . " because it would remove all " . $existingColumnCount . " existing columns. Drops requested: " . implode(', ', $dropTheseColumns)); } else { foreach ($dropTheseColumns as $colName) { $query = "alter table " . $tableName . " drop " . $colName; $this->dbCore->queryAndGetResults($query); $this->logger->infoMessage("I dropped a column (1): " . $query); } } // say why we're doing what we're doing. if (count($updateTheseColumns) > 0) { $this->logger->infoMessage(self::$uniqID . ": On " . $tableName . " I'm updating various columns because we want: \n`" . print_r($goalTableMatchesColumnDDL, true) . "\n but we have: \n" . print_r($existingTableMatchesColumnDDL, true)); } // create missing columns // Normalize $goalMatchesSub using the same normalizeColumnDDL() that // getTableDifferences() uses, so array_search() can find the right index. $goalMatchesSub = is_array($goalTableMatches[1] ?? null) ? $goalTableMatches[1] : []; $goalMatchesSub = array_map([$this, 'normalizeColumnDDL'], $goalMatchesSub); foreach ($updateTheseColumns as $colDDL) { // find the colum name. $matchIndex = array_search($colDDL, $goalMatchesSub); if ($matchIndex === false) { $this->logger->warn("Could not match column DDL to goal schema, skipping: " . $colDDL); continue; } $colName = is_string($goalTableMatchesColumnNames[$matchIndex] ?? null) ? $goalTableMatchesColumnNames[$matchIndex] : ''; // if the column exists then update it. otherwise create it. if (!in_array($colName, $createTheseColumns)) { // update the existing column. // ALTER TABLE `mywp_abj404_redirects` CHANGE `status` `status` BIGINT(19) NOT NULL; $updateColStatement = "alter table " . $tableName . " change " . $colName . " " . $colDDL; $this->dbCore->queryAndGetResults($updateColStatement); $this->logger->infoMessage("I updated a column: " . $updateColStatement); } else { // create the column. $createColStatement = "alter table " . $tableName . " add " . $colDDL; $this->dbCore->queryAndGetResults($createColStatement); $this->logger->infoMessage("I added a column: " . $createColStatement); } $this->handleSpecificCases($tableName, $colName); } } /** Create table DDL is returned without SQL comments of any kind. * Strips block comments (slash-star ... star-slash), line comments (-- ...), * and inline COMMENT 'text' column clauses so the column-name regex in * getTableDifferences() cannot mistake comment text for column definitions. * @param string|null $createTableDDL * @return string */ function removeCommentsFromColumns($createTableDDL) { if ($createTableDDL === null) { return ''; } $ddl = (string) $createTableDDL; // Strip block comments (slash-star ... star-slash), including multi-line. $ddl = preg_replace('/\/\*.*?\*\//s', '', $ddl) ?? $ddl; // Strip line comments (-- ...). $ddl = preg_replace('/--[^\r\n]*/', '', $ddl) ?? $ddl; // Strip inline COMMENT 'text', clauses from column definitions. return preg_replace('/ (?:COMMENT.+?,[\r\n])/', ",\n", $ddl) ?? $ddl; } /** * Normalize a single column DDL fragment for comparison. * * Strips backticks and unquotes integer defaults so that DDL from * SHOW CREATE TABLE (e.g. default '1') matches the goal DDL file * (e.g. default 1). Used by both getTableDifferences() and * updateATableBasedOnDifferences() — a single source of truth * prevents the two normalization sites from drifting out of sync. * * @param mixed $ddl A column DDL string (or non-string from regex match) * @return string */ function normalizeColumnDDL($ddl): string { $ddlStr = is_string($ddl) ? $ddl : ''; $normalized = strtolower(str_replace('`', '', trim($ddlStr))); $normalized = preg_replace("/default '(\d+)'/", 'default $1', $normalized) ?? $normalized; // MySQL omits DEFAULT NULL for nullable columns — strip it so DDL file // and SHOW CREATE TABLE produce identical normalized strings. $normalized = preg_replace('/\s+default\s+null\b/', '', $normalized) ?? $normalized; return $normalized; } /** * @param string $tableName * @return void */ function deleteIndexes($tableName) { // get the indexes list. $results = $this->dbCore->queryAndGetResults("show index from " . $tableName . " where key_name != 'PRIMARY'"); /** @var array> $rows */ $rows = isset($results['rows']) && is_array($results['rows']) ? $results['rows'] : []; if (empty($rows)) { return; } // find the key_name column because the case can be different on different systems. $keyNameColumn = 'key_name'; $aRow = $rows[0]; foreach (array_keys($aRow) as $someKey) { if ($this->f->strtolower((string)$someKey) == 'key_name') { $keyNameColumn = (string)$someKey; break; } } foreach ($rows as $row) { // delete them $indexName = $row[$keyNameColumn] ?? ''; if (!is_string($indexName) || $indexName === '') { continue; } $query = "alter table " . $tableName . " drop index " . $indexName; $this->dbCore->queryAndGetResults($query); } } } ```