'ewg.dedicated.co.za',
'user' => 'elegaysv_Code2',
'pass' => 'EWG2Cod!@#',
'name' => 'elegaysv_systems',
];
$secondaryDbConfig = [
'host' => 'ewg.dedicated.co.za',
'user' => 'elegaysv_Code2',
'pass' => 'EWG2Cod!@#',
'name' => 'elegaysv_ewg',
];
function connectToDb($config) {
$conn = new mysqli($config['host'], $config['user'], $config['pass'], $config['name']);
if ($conn->connect_error) {
die("Connection failed to {$config['name']}: " . $conn->connect_error);
}
return $conn;
}
function getTables($conn, $dbName) {
$tables = [];
$result = $conn->query("SHOW TABLES");
while ($row = $result->fetch_array()) {
$tables[] = $row[0];
}
return $tables;
}
function getColumns($conn, $dbName, $table) {
$columns = [];
$sql = "SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '$dbName' AND TABLE_NAME = '$table'";
$result = $conn->query($sql);
while ($row = $result->fetch_assoc()) {
$columns[$row['COLUMN_NAME']] = $row;
}
return $columns;
}
function buildColumnSQL($col) {
$type = $col['COLUMN_TYPE'];
$nullable = $col['IS_NULLABLE'] === 'YES' ? 'NULL' : 'NOT NULL';
$default = '';
$extra = $col['EXTRA'];
if ($col['COLUMN_DEFAULT'] !== null) {
$default = "DEFAULT '" . $col['COLUMN_DEFAULT'] . "'";
} else {
if ($nullable === 'NOT NULL') {
if (preg_match('/int|decimal|float|double/', $type)) {
$default = "DEFAULT 0";
} elseif (preg_match('/char|text|enum|set/', $type)) {
$default = "DEFAULT ''";
} elseif (preg_match('/date|time|timestamp/', $type)) {
$default = '';
}
}
}
return "$type $nullable $default $extra";
}
$mainConn = connectToDb($mainDbConfig);
$secondaryConn = connectToDb($secondaryDbConfig);
$mainTables = getTables($mainConn, $mainDbConfig['name']);
$secondaryTables = getTables($secondaryConn, $secondaryDbConfig['name']);
$report = "";
$report .= "Database Schema Sync Report
";
foreach ($mainTables as $table) {
$report .= "Table: $table
| Type | Column | Main DB | Secondary DB | Action |
|---|
";
if (!in_array($table, $secondaryTables)) {
$createSQL = $mainConn->query("SHOW CREATE TABLE `$table`")->fetch_assoc()['Create Table'];
if ($secondaryConn->query($createSQL)) {
$report .= "| Table | Created table '$table' in secondary DB |
";
} else {
$report .= "| Error | Failed to create table '$table': {$secondaryConn->error} |
";
}
$report .= "
";
continue;
}
$mainCols = getColumns($mainConn, $mainDbConfig['name'], $table);
$secCols = getColumns($secondaryConn, $secondaryDbConfig['name'], $table);
foreach ($mainCols as $colName => $mainCol) {
if (!isset($secCols[$colName])) {
$colDef = buildColumnSQL($mainCol);
$alterSQL = "ALTER TABLE `$table` ADD COLUMN `$colName` $colDef";
$success = $secondaryConn->query($alterSQL);
$report .= "| Missing | $colName | {$mainCol['COLUMN_TYPE']} | -- | " . ($success ? "Added" : "Failed: {$secondaryConn->error}") . " |
";
} else {
$secCol = $secCols[$colName];
$mismatch = false;
$details = [];
foreach (['COLUMN_TYPE', 'IS_NULLABLE', 'COLUMN_DEFAULT', 'EXTRA'] as $attr) {
if (strtolower($mainCol[$attr]) !== strtolower($secCol[$attr])) {
$mismatch = true;
$details[] = "$attr differs";
}
}
if ($mismatch) {
$colDef = buildColumnSQL($mainCol);
$alterSQL = "ALTER TABLE `$table` MODIFY COLUMN `$colName` $colDef";
$success = $secondaryConn->query($alterSQL);
$report .= "| Mismatch | $colName | {$mainCol['COLUMN_TYPE']} | {$secCol['COLUMN_TYPE']} | " . ($success ? "Modified" : "Failed: {$secondaryConn->error}") . " |
";
}
}
}
$report .= "";
}
$mainConn->close();
$secondaryConn->close();
echo $report;
?>