<?php
/**
* Database Schema Comparison Tool
*
* This script is designed to compare two database tables and generate the required MySQL CREATE or ALTER scripts
* to synchronize them. It's intended for developers and database administrators who need to keep database environments
* aligned, such as development, staging, and production. The script automates the generation of SQL scripts necessary
* for updating table structures, including adding missing tables, columns, and adjusting column definitions to match
* between two specified databases.
*
* Features:
* - Generates SQL scripts for synchronizing database schemas.
* - Supports CREATE and ALTER TABLE commands based on comparison results.
* - Automates the identification of discrepancies between two database tables.
*
* Usage:
* Modify the `$connections` array with the database connection details and specify the databases to compare.
* Execute the script in a PHP-supported command line or web server environment to get the SQL commands for synchronization.
*
* Note:
* This script stops execution immediately to prevent unauthorized access. Remove the `header` and `die` function calls
* to enable its functionality in a secure environment.
*
* Author: RapidMod.io
* Website: https://rapidmod.io/
*
* Please ensure proper security measures are in place when using this script to prevent unauthorized database access.
*/
header("File not found",1,404); die(404);
//ini_set('display_errors', 1);
//ini_set('display_startup_errors', 1);
//error_reporting(E_ALL);
function executeQ($connections) {
if(!empty($connections)){
foreach ($connections as $conn){
}
}
$conn = array(
"name" => "",
"type" => "",
"user" => "",
"password" => '',
"host" => "",
"port"=>""
);
$data = [];
$dbNames = ["table1","table2"];
/** @var PDO $pdo */
$pdo = new PDO(
"mysql:host={$conn['host']}; dbname={$conn['name']}",
$conn['user'],
$conn['password']
);
foreach ($dbNames as $dbName){
$data["additional_tables"][$dbName] = [];
$data["additional_columns"][$dbName] = [];
$data["changes"][$dbName] = [];
$data["schema"][$dbName] = [];
$data[$dbName] = [ "tables" => [], "columns" => [] ];
$params = [];
$schema = fetchQ($pdo,"SHOW TABLES in {$dbName}",$params);
if(!empty($schema)){
foreach ($schema as $i => $x){
$table = trim($x["Tables_in_{$dbName}"]);
$data[$dbName]["tables"][$table] = $table;
$data[$dbName]["columns"][$table] = [];
$schemaA = fetchQ($pdo,"DESCRIBE `{$dbName}`.`{$table}` ",$params);
if(!empty($schemaA)){
$formattedSchema = array();
foreach ($schemaA as $colInfo){
$data[$dbName]["columns"][$table][$colInfo["Field"]] = $colInfo["Type"];
$formattedSchema[$colInfo["Field"]] = $colInfo;
}
$data["schema"][$dbName][$table] = $formattedSchema;
}
}
}
}
// die("<pre>".print_r($data,1));
foreach ($data as $dbName => $info){
if($dbName === $dbNames[0]){$check = $dbNames[1];}
else{$check = $dbNames[0];}
$tables = $data[$dbName]["tables"];
if(!$tables){
continue;
die(":nothing #1125");
}
$dbChanges[$dbName] = [];
foreach ($tables as $table){
if(!isset($data[$check]["tables"][trim($table)])){
$data["additional_tables"][$dbName][] = trim($table);
$x = fetchQ($pdo,"show create table {$dbName}.{$table}",[]);
if(!empty($x[0]["Create Table"])){
$data["changes"][$dbName][] = $x[0]["Create Table"].";";
}else{
die(":nothing #1124");
}
}
if($data[$dbName]["columns"][$table]){
foreach ($data[$dbName]["columns"][$table] as $col => $type){
if(isset($data[$check]["columns"][$table])){
if(!isset($data[$check]["columns"][$table][$col])){
$data["additional_columns"][$dbName][$table][] = $col;
$data["changes"][$dbName][] = getAlterTableStatement($table,$col,$data["schema"][$dbName][$table][$col]);
}
}
}
}
}
}
$changes = $data["changes"]["table1"];
if(!empty($changes)){
$changes = implode(PHP_EOL.PHP_EOL,$changes);
echo str_replace(PHP_EOL,PHP_EOL."<br>","SET FOREIGN_KEY_CHECKS=0;".PHP_EOL.PHP_EOL.$changes.PHP_EOL.PHP_EOL."SET FOREIGN_KEY_CHECKS=1;");
die("");
}
die(":nothing #1123");
}
/**
*
* Name getAlterTableStatement
*
* @param $table
* @param $column
* @param $info
*
* @return string
*
* @author RapidMod.io
* @author 813.330.0522
* @created 1/25/20
*/
function getAlterTableStatement($table,$column,$info){
$alter = "";
$stmnt = "ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$info["Type"]}";
if($info["Key"]){
switch (strtolower($info["Key"])){
case "pri" : return $stmnt. " PRIMARY KEY AUTO_INCREMENT ;"; break;
case "mul" : $alter = PHP_EOL."ALTER TABLE `$table` ADD INDEX {$column} ({$column}); "; break;
case "uni" : $alter = PHP_EOL."ALTER TABLE `$table` ADD UNIQUE INDEX {$column} ({$column}); ";break;
default : break;
}
}
if($info["Null"] === "YES"){
$stmnt .= " NULL";
}else{
$stmnt .= " NOT NULL";
}
if($info["Default"]){
if(in_array(strtolower($info["Type"]),["datetime","timestamp"]) && $info["Default"] !== "CURRENT_TIMESTAMP"){
$var = "0000-00-00 00:00:00";
}else if(is_numeric($info["Default"]) || $info["Default"] === "CURRENT_TIMESTAMP"){
$var = $info["Default"];
}else{
$var = "'{$info["Default"]}'";
}
$stmnt .= " DEFAULT {$var}";
}
return "{$stmnt};{$alter}";
}
/**
*
* Name fetchQ
*
* @param PDO $pdo
* @param string $query
* @param array $params
*
* @return array
*
* @author RapidMod.io
* @author 813.330.0522
* @created 1/24/20
*/
function fetchQ(PDO $pdo, string $query,array $params = []){
$stmnt = $pdo->prepare($query,[PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true]);
$x = array();
if(isset($params[0]) && is_array($params[0])){$params = $params[0];}
if(empty($params)){$params=null;}
$success = $stmnt->execute($params);
//\Rapidmod\Dev::printVar($stmnt);
if($success){
while($row = $stmnt->fetch(PDO::FETCH_ASSOC)){
$x[] = $row;
}
}
return $x;
}
executeQ([]);
?>
Comments