, column_order: list, indexes: array, constraints: array} */ function parseCreateTable(string $createSql): array { $bodyStart = strpos($createSql, "(\n"); $bodyEnd = strrpos($createSql, "\n)"); if ($bodyStart === false || $bodyEnd === false || $bodyEnd <= $bodyStart) { throw new RuntimeException('Unsupported SHOW CREATE TABLE format'); } $body = substr($createSql, $bodyStart + 2, $bodyEnd - ($bodyStart + 2)); $items = preg_split('/,\n(?= )/', $body) ?: []; $columns = []; $columnOrder = []; $indexes = []; $constraints = []; foreach ($items as $item) { $definition = trim($item); if (preg_match('/^`((?:``|[^`])+)`\s+/', $definition, $match)) { $name = str_replace('``', '`', $match[1]); $columns[$name] = $definition; $columnOrder[] = $name; continue; } if (preg_match('/^PRIMARY KEY\b/i', $definition)) { $indexes['PRIMARY'] = $definition; continue; } if (preg_match('/^(?:UNIQUE |FULLTEXT |SPATIAL )?KEY\s+`((?:``|[^`])+)`/i', $definition, $match)) { $name = str_replace('``', '`', $match[1]); $indexes[$name] = $definition; continue; } if (preg_match('/^CONSTRAINT\s+`((?:``|[^`])+)`/i', $definition, $match)) { $name = str_replace('``', '`', $match[1]); $constraints[$name] = $definition; continue; } throw new RuntimeException("Unsupported table definition item: {$definition}"); } return [ 'columns' => $columns, 'column_order' => $columnOrder, 'indexes' => $indexes, 'constraints' => $constraints, ]; } /** @return array> */ function fetchTables(PDO $pdo, string $schema): array { $sql = <<<'SQL' SELECT t.TABLE_NAME, t.ENGINE, t.TABLE_COLLATION, c.CHARACTER_SET_NAME AS TABLE_CHARSET, t.ROW_FORMAT, t.TABLE_COMMENT, t.CREATE_OPTIONS, t.TABLE_ROWS FROM information_schema.TABLES t LEFT JOIN information_schema.COLLATIONS c ON c.COLLATION_NAME = t.TABLE_COLLATION WHERE t.TABLE_SCHEMA = :schema AND t.TABLE_TYPE = 'BASE TABLE' ORDER BY t.TABLE_NAME SQL; $statement = $pdo->prepare($sql); $statement->execute(['schema' => $schema]); $tables = []; foreach ($statement->fetchAll(PDO::FETCH_ASSOC) as $row) { $name = (string)$row['TABLE_NAME']; $show = $pdo->query('SHOW CREATE TABLE ' . quoteIdent($schema) . '.' . quoteIdent($name)); if ($show === false) { throw new RuntimeException("SHOW CREATE TABLE failed for {$schema}.{$name}"); } $createRow = $show->fetch(PDO::FETCH_ASSOC); $createSql = $createRow['Create Table'] ?? null; if (!is_string($createSql)) { throw new RuntimeException("Missing CREATE TABLE text for {$schema}.{$name}"); } $row['create_sql'] = $createSql; $row['parsed'] = parseCreateTable($createSql); $tables[$name] = $row; } return $tables; } /** @return array{charset:string,collation:string} */ function fetchSchemaOptions(PDO $pdo, string $schema): array { $statement = $pdo->prepare( 'SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME ' . 'FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = :schema' ); $statement->execute(['schema' => $schema]); $row = $statement->fetch(PDO::FETCH_ASSOC); if (!is_array($row)) { throw new RuntimeException("Schema does not exist or is not visible: {$schema}"); } return [ 'charset' => (string)$row['DEFAULT_CHARACTER_SET_NAME'], 'collation' => (string)$row['DEFAULT_COLLATION_NAME'], ]; } /** @param list $clauses */ function alterStatement(string $table, array $clauses): string { return 'ALTER TABLE ' . quoteIdent($table) . "\n " . implode(",\n ", $clauses) . ';'; } function columnPositionClause(int $index, array $sourceOrder): string { return $index === 0 ? ' FIRST' : ' AFTER ' . quoteIdent($sourceOrder[$index - 1]); } function tableOptionsDiffer(array $source, array $target): bool { foreach (['ENGINE', 'ROW_FORMAT', 'TABLE_COMMENT', 'CREATE_OPTIONS'] as $key) { if ((string)$source[$key] !== (string)$target[$key]) { return true; } } return false; } function sourceTableOptions(PDO $pdo, array $source, array $target): array { $clauses = []; if ((string)$source['ENGINE'] !== (string)$target['ENGINE']) { $clauses[] = 'ENGINE=' . $source['ENGINE']; } if ((string)$source['CREATE_OPTIONS'] !== (string)$target['CREATE_OPTIONS']) { if (preg_match('/(?:^|\s)row_format=([^\s]+)/i', (string)$source['CREATE_OPTIONS'], $match)) { $clauses[] = 'ROW_FORMAT=' . strtoupper($match[1]); } elseif (preg_match('/(?:^|\s)row_format=/i', (string)$target['CREATE_OPTIONS'])) { $clauses[] = 'ROW_FORMAT=DEFAULT'; } } if ((string)$source['TABLE_COMMENT'] !== (string)$target['TABLE_COMMENT']) { $clauses[] = 'COMMENT=' . $pdo->quote((string)$source['TABLE_COMMENT']); } return $clauses; } function isCharacterColumnDefinition(string $definition): bool { return preg_match('/^`(?:``|[^`])+`\s+(?:char|varchar|tinytext|text|mediumtext|longtext|enum|set)\b/i', $definition) === 1; } $host = envRequired('DB_HOST'); $port = getenv('DB_PORT') ?: '3306'; $user = envRequired('DB_USER'); $password = envRequired('DB_PASS'); $sourceSchema = envRequired('SOURCE_SCHEMA'); $targetSchema = envRequired('TARGET_SCHEMA'); $output = envRequired('OUTPUT_SQL'); $manifestPath = envRequired('OUTPUT_MANIFEST'); $pdo = new PDO( "mysql:host={$host};port={$port};charset=utf8mb4", $user, $password, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, ] ); $version = (string)$pdo->query('SELECT VERSION()')->fetchColumn(); $sourceSchemaOptions = fetchSchemaOptions($pdo, $sourceSchema); $targetSchemaOptions = fetchSchemaOptions($pdo, $targetSchema); $sourceTables = fetchTables($pdo, $sourceSchema); $targetTables = fetchTables($pdo, $targetSchema); $sourceNames = array_keys($sourceTables); $targetNames = array_keys($targetTables); $missingTables = array_values(array_diff($sourceNames, $targetNames)); $extraTables = array_values(array_diff($targetNames, $sourceNames)); $sharedTables = array_values(array_intersect($sourceNames, $targetNames)); sort($missingTables); sort($extraTables); sort($sharedTables); $sections = [ 'database_options' => [], 'drop_constraints' => [], 'create_tables' => [], 'drop_indexes' => [], 'charset_conversion' => [], 'data_backfills' => [], 'alter_columns' => [], 'add_indexes' => [], 'table_options' => [], 'add_constraints' => [], 'drop_extra_tables' => [], ]; $manifest = [ 'server_version' => $version, 'source_schema' => $sourceSchema, 'target_schema' => $targetSchema, 'source_schema_options' => $sourceSchemaOptions, 'target_schema_options' => $targetSchemaOptions, 'source_table_count' => count($sourceTables), 'target_table_count' => count($targetTables), 'missing_tables' => $missingTables, 'extra_tables' => $extraTables, 'tables' => [], ]; if ($sourceSchemaOptions !== $targetSchemaOptions) { $sections['database_options'][] = 'ALTER DATABASE ' . quoteIdent($targetSchema) . ' DEFAULT CHARACTER SET ' . $sourceSchemaOptions['charset'] . ' COLLATE ' . $sourceSchemaOptions['collation'] . ';'; } foreach ($missingTables as $table) { $create = stripAutoIncrementCounter((string)$sourceTables[$table]['create_sql']); $sections['create_tables'][] = $create . ';'; $manifest['tables'][$table] = ['action' => 'create']; } foreach ($sharedTables as $table) { $source = $sourceTables[$table]; $target = $targetTables[$table]; $sourceParsed = $source['parsed']; $targetParsed = $target['parsed']; $needsCharsetConversion = (string)$source['TABLE_CHARSET'] !== (string)$target['TABLE_CHARSET'] || (string)$source['TABLE_COLLATION'] !== (string)$target['TABLE_COLLATION']; $tableManifest = [ 'drop_constraints' => [], 'add_constraints' => [], 'drop_indexes' => [], 'charset_conversion' => false, 'data_backfills' => [], 'add_indexes' => [], 'add_columns' => [], 'modify_columns' => [], 'drop_columns' => [], 'table_options' => false, ]; $dropConstraints = []; foreach ($targetParsed['constraints'] as $name => $definition) { $sourceDefinition = $sourceParsed['constraints'][$name] ?? null; if ($sourceDefinition === null || normalizeSql($sourceDefinition) !== normalizeSql($definition)) { $dropConstraints[] = 'DROP FOREIGN KEY ' . quoteIdent($name); $tableManifest['drop_constraints'][] = $name; } } if ($dropConstraints !== []) { $sections['drop_constraints'][] = alterStatement($table, $dropConstraints); } $dropIndexes = []; foreach ($targetParsed['indexes'] as $name => $definition) { $sourceDefinition = $sourceParsed['indexes'][$name] ?? null; if ($sourceDefinition === null || normalizeSql($sourceDefinition) !== normalizeSql($definition)) { // Keep a changed primary key in place until its replacement is added. // MySQL 5.6 rejects an intermediate state where AUTO_INCREMENT is not indexed. if ($name === 'PRIMARY' && $sourceDefinition !== null) { $tableManifest['drop_indexes'][] = $name; continue; } $dropIndexes[] = $name === 'PRIMARY' ? 'DROP PRIMARY KEY' : 'DROP INDEX ' . quoteIdent($name); $tableManifest['drop_indexes'][] = $name; } } if ($dropIndexes !== []) { $sections['drop_indexes'][] = alterStatement($table, $dropIndexes); } if ($needsCharsetConversion) { $sections['charset_conversion'][] = alterStatement($table, [ 'CONVERT TO CHARACTER SET ' . $source['TABLE_CHARSET'] . ' COLLATE ' . $source['TABLE_COLLATION'], 'DEFAULT CHARACTER SET ' . $source['TABLE_CHARSET'] . ' COLLATE ' . $source['TABLE_COLLATION'], ]); $tableManifest['charset_conversion'] = true; } $columnClauses = []; $currentOrder = $targetParsed['column_order']; foreach ($sourceParsed['column_order'] as $index => $column) { $sourceDefinition = $sourceParsed['columns'][$column]; $targetDefinition = $targetParsed['columns'][$column] ?? null; if ($targetDefinition !== null && preg_match('/\sNOT NULL\b/i', $sourceDefinition) === 1 && preg_match('/\sNOT NULL\b/i', $targetDefinition) !== 1) { if (!isCharacterColumnDefinition($sourceDefinition)) { throw new RuntimeException("Explicit backfill required before making {$table}.{$column} NOT NULL"); } $sections['data_backfills'][] = 'UPDATE ' . quoteIdent($table) . ' SET ' . quoteIdent($column) . " = '' WHERE " . quoteIdent($column) . ' IS NULL;'; $tableManifest['data_backfills'][] = $column; } $currentIndex = array_search($column, $currentOrder, true); $definitionDiffers = $targetDefinition === null || normalizeSql($sourceDefinition) !== normalizeSql($targetDefinition) || ($needsCharsetConversion && isCharacterColumnDefinition($sourceDefinition)); $positionDiffers = $currentIndex === false || $currentIndex !== $index; if ($targetDefinition === null) { $columnClauses[] = 'ADD COLUMN ' . $sourceDefinition . columnPositionClause($index, $sourceParsed['column_order']); $tableManifest['add_columns'][] = $column; } elseif ($definitionDiffers || $positionDiffers) { $columnClauses[] = 'MODIFY COLUMN ' . $sourceDefinition . columnPositionClause($index, $sourceParsed['column_order']); $tableManifest['modify_columns'][] = $column; } if ($currentIndex !== false) { array_splice($currentOrder, $currentIndex, 1); } array_splice($currentOrder, $index, 0, [$column]); } foreach ($targetParsed['column_order'] as $column) { if (!isset($sourceParsed['columns'][$column])) { $columnClauses[] = 'DROP COLUMN ' . quoteIdent($column); $tableManifest['drop_columns'][] = $column; } } if ($columnClauses !== []) { $sections['alter_columns'][] = alterStatement($table, $columnClauses); } $addIndexes = []; foreach ($sourceParsed['indexes'] as $name => $definition) { $targetDefinition = $targetParsed['indexes'][$name] ?? null; if ($targetDefinition === null || normalizeSql($targetDefinition) !== normalizeSql($definition)) { if ($name === 'PRIMARY' && $targetDefinition !== null) { $addIndexes[] = 'DROP PRIMARY KEY'; } $addIndexes[] = 'ADD ' . $definition; $tableManifest['add_indexes'][] = $name; } } if ($addIndexes !== []) { $sections['add_indexes'][] = alterStatement($table, $addIndexes); } if (tableOptionsDiffer($source, $target)) { $optionClauses = sourceTableOptions($pdo, $source, $target); if ($optionClauses !== []) { $sections['table_options'][] = alterStatement($table, $optionClauses); $tableManifest['table_options'] = true; } } $addConstraints = []; foreach ($sourceParsed['constraints'] as $name => $definition) { $targetDefinition = $targetParsed['constraints'][$name] ?? null; if ($targetDefinition === null || normalizeSql($targetDefinition) !== normalizeSql($definition)) { $addConstraints[] = 'ADD ' . $definition; $tableManifest['add_constraints'][] = $name; } } if ($addConstraints !== []) { $sections['add_constraints'][] = alterStatement($table, $addConstraints); } if (array_filter($tableManifest, static fn($value) => $value !== [] && $value !== false) !== []) { $manifest['tables'][$table] = $tableManifest; } } foreach ($extraTables as $table) { $sections['drop_extra_tables'][] = 'DROP TABLE ' . quoteIdent($table) . ';'; $manifest['tables'][$table] = ['action' => 'drop']; } $generatedAt = date('Y-m-d H:i:s O'); $header = [ '-- zhct_wxbcgz schema convergence SQL', '-- Generated: ' . $generatedAt, '-- Source schema: ' . $sourceSchema, '-- Target schema: ' . $targetSchema, '-- MySQL server: ' . $version, '-- IMPORTANT: This script contains DDL and auto-commits on MySQL 5.6.', '-- IMPORTANT: DROP COLUMN / DROP TABLE statements permanently remove target data.', '-- Regenerate from current metadata after any partial execution; do not blindly rerun the full file.', '', 'SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS;', 'SET @OLD_UNIQUE_CHECKS = @@UNIQUE_CHECKS;', 'SET FOREIGN_KEY_CHECKS = 0;', 'SET UNIQUE_CHECKS = 0;', 'USE ' . quoteIdent($targetSchema) . ';', ]; $labels = [ 'database_options' => 'Phase 1: align database default charset and collation', 'drop_constraints' => 'Phase 2: drop changed or obsolete foreign keys', 'create_tables' => 'Phase 3: create tables missing from the target', 'drop_indexes' => 'Phase 4: drop changed or obsolete indexes', 'charset_conversion' => 'Phase 5: convert existing text columns to the source table charset and collation', 'data_backfills' => 'Phase 6: backfill rows required by stricter source nullability', 'alter_columns' => 'Phase 7: align columns and column order, including explicit column collations', 'add_indexes' => 'Phase 8: add missing or changed indexes', 'table_options' => 'Phase 9: align engine, row format and comments', 'add_constraints' => 'Phase 10: add missing or changed foreign keys', 'drop_extra_tables' => 'Phase 11: drop tables absent from the source schema', ]; $sqlParts = [implode("\n", $header)]; foreach ($sections as $key => $statements) { $sqlParts[] = ''; $sqlParts[] = '-- ' . $labels[$key]; if ($statements === []) { $sqlParts[] = '-- No changes.'; } else { $sqlParts[] = implode("\n\n", $statements); } } $sqlParts[] = ''; $sqlParts[] = 'SET UNIQUE_CHECKS = @OLD_UNIQUE_CHECKS;'; $sqlParts[] = 'SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS;'; $sqlParts[] = ''; $sql = implode("\n", $sqlParts); $manifest['generated_at'] = $generatedAt; $manifest['sql_sha256'] = hash('sha256', $sql); $manifest['statement_counts'] = array_map('count', $sections); if (file_put_contents($output, $sql) === false) { throw new RuntimeException("Unable to write SQL output: {$output}"); } if (file_put_contents($manifestPath, json_encode($manifest, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE) . "\n") === false) { throw new RuntimeException("Unable to write manifest output: {$manifestPath}"); } fwrite(STDOUT, json_encode([ 'source_tables' => count($sourceTables), 'target_tables' => count($targetTables), 'missing_tables' => count($missingTables), 'extra_tables' => count($extraTables), 'changed_shared_tables' => count($manifest['tables']) - count($missingTables) - count($extraTables), 'statement_counts' => $manifest['statement_counts'], 'sql_sha256' => $manifest['sql_sha256'], ], JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE) . "\n");