Compare two databases schema and generate SQL scripts

diff_db.php

<?php
#
# diff_db() function for MySQL/MariaDB
# This function compare two databases schema and generate SQL scripts
# to update the second database.
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# by Daif Alazmi (http://daif.net)
# [email protected]
# 2020-10-27
#
    error_reporting(E_ALL);
    ini_set('display_errors', true);

    // Database connection 2
    $dsn  = 'mysql:host=localhost';
    $user = 'root';
    $pass = '';
    $conn1 = new PDO($dsn, $user, $pass);
    $conn1->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Database connection 2
    $dsn  = 'mysql:host=localhost';
    $user = 'root';
    $pass = '';
    $conn2 = new PDO($dsn, $user, $pass);
    $conn2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // databases
    $db1 = 'db_test1';
    $db2 = 'db_test2';

    $diff_db = diff_db($db1, $db2, $conn1, $conn2);


function diff_db($db1 , $db2, $conn1, $conn2)
{
    $dbs = [
        $db1 => ['tables'=>[],'keys'=>[],'primary'=>[],'create'=>[]],
        $db2 => ['tables'=>[],'keys'=>[],'primary'=>[],'create'=>[]],
    ];
    foreach ($dbs as $db => $info)
    {
        $conn = ($db == $db1)?$conn1:$conn2;
        $sth = $conn->prepare('SHOW TABLES FROM '.$db);
        $sth->execute();
        $tables = $sth->fetchAll(PDO::FETCH_COLUMN);
        foreach ($tables as $table)
        {
            $sth = $conn->prepare('SHOW CREATE TABLE '.$db.'.'.$table);
            $sth->execute();
            $dbs[$db]['create'][$table] = $sth->fetch(PDO::FETCH_ASSOC)['Create Table'];
            // find fields
            preg_match_all('#  `(.+)` (.+)\n#Uis', $dbs[$db]['create'][$table], $fields);
            $dbs[$db]['tables'][$table] = array_combine($fields[1], $fields[2]);
            $dbs[$db]['keys'][$table]   = [];
            // find UNIQUE keys
            if(preg_match_all('#  UNIQUE KEY `(.+)`.+\n#Uis', $dbs[$db]['create'][$table], $keys))
            {
                $dbs[$db]['keys'][$table] = array_combine($keys[1], $keys[0]);
            }
            // find keys
            if(preg_match_all('#  KEY `(.+)`.+\n#Uis', $dbs[$db]['create'][$table], $keys))
            {
                $dbs[$db]['keys'][$table] += array_combine($keys[1], $keys[0]);
            }            // find keys
            if(preg_match_all('#  FULLTEXT KEY `(.+)`.+\n#Uis', $dbs[$db]['create'][$table], $keys))
            {
                $dbs[$db]['keys'][$table] += array_combine($keys[1], $keys[0]);
            }
            // find primary key
            if(preg_match('#  PRIMARY KEY \(`(.+)`\)#Uis', $dbs[$db]['create'][$table], $primary))
            {
                $dbs[$db]['primary'][$table]= $primary[1];
            }
        }
    }
    $diff_db  = [];
    $diff_sql = [];
    foreach ($dbs[$db1]['tables'] as $table => $columns)
    {
        if(empty($dbs[$db2]['tables'][$table]))
        {
            $diff_db[$db2]['tables'][$table] = $dbs[$db1]['create'][$table];
            $diff_sql[] = ['type'=>'insert','sql'=>$diff_db[$db2]['tables'][$table]];
            continue;
        }
        foreach ($columns as $column => $sql)
        {
            if(empty($dbs[$db2]['tables'][$table][$column]))
            {
                $diff_db[$db2]['tables'][$table][$column] = 'ALTER TABLE '.$db2.'.`'.$table.'` ADD `'.$column.'` '.rtrim(trim($dbs[$db1]['tables'][$table][$column]),',');
                if(!empty($prev_column))
                {
                    $diff_db[$db2]['tables'][$table][$column] .= ' AFTER `'.$prev_column.'`';
                }
                $diff_sql[] = ['type'=>'insert','sql'=>$diff_db[$db2]['tables'][$table][$column]];
            }
            elseif($dbs[$db1]['tables'][$table][$column] != $dbs[$db2]['tables'][$table][$column])
            {
                $diff_db[$db2]['tables'][$table][$column] = 'ALTER TABLE '.$db2.'.`'.$table.'` MODIFY `'.$column.'` '.rtrim(trim($dbs[$db1]['tables'][$table][$column]),',');
                $diff_sql[] = ['type'=>'modify','sql'=>$diff_db[$db2]['tables'][$table][$column]];
            }
            $prev_column = $column;
        }
    }
    foreach ($dbs[$db1]['keys'] as $table => $keys)
    {
        if(empty($dbs[$db2]['tables'][$table]))
        {
            continue;
        }
        foreach ($keys as $key => $sql)
        {
            if(empty($dbs[$db2]['keys'][$table][$key]))
            {
                $diff_db[$db2]['keys'][$table][$key] = 'ALTER TABLE '.$db2.'.`'.$table.'` ADD '.rtrim(trim($dbs[$db1]['keys'][$table][$key]),',');
                $diff_sql[] = ['type'=>'insert','sql'=>$diff_db[$db2]['keys'][$table][$key]];
            }
            elseif($dbs[$db1]['keys'][$table][$key] != $dbs[$db2]['keys'][$table][$key])
            {
                $diff_db[$db2]['tables'][$table][$key] = 'ALTER TABLE '.$db2.'.`'.$table.'` DROP INDEX `'.$key.'`, ADD '.rtrim(trim($dbs[$db1]['keys'][$table][$key]),',');
                $diff_sql[] = ['type'=>'modify','sql'=>$diff_db[$db2]['tables'][$table][$key]];
            }
        }
    }
    foreach ($dbs[$db1]['primary'] as $table => $keys)
    {
        if(empty($dbs[$db2]['tables'][$table]))
        {
            continue;
        }
        if(empty($dbs[$db2]['primary'][$table]))
        {
            $diff_db[$db2]['primary'][$table] = 'ALTER TABLE '.$db2.'.`'.$table.'` ADD PRIMARY KEY(`'.$dbs[$db1]['primary'][$table].'`)';
            $diff_sql[] = ['type'=>'modify','sql'=>$diff_db[$db2]['primary'][$table]];
        }
        elseif($dbs[$db1]['primary'][$table] != $dbs[$db2]['primary'][$table])
        {
            $diff_db[$db2]['primary'][$table] = 'ALTER TABLE '.$db2.'.`'.$table.'` DROP PRIMARY KEY, ADD PRIMARY KEY(`'.$dbs[$db1]['primary'][$table].'`)';
            $diff_sql[] = ['type'=>'modify','sql'=>$diff_db[$db2]['primary'][$table]];
        }
    }
    return ['diff_db'=>$diff_db,'diff_sql'=>$diff_sql];
}

?><!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Diff DBs</title>
<style type="text/css">
body{ 
    margin: 0.5em; 
    font-family: Arial, Tahoma, sans-serif; 
    color: #222;
}
table{
    border-collapse: collapse; 
    border-spacing: 0; 
    background-color: white;
    width: 100%;
    border: 1px solid #c3c9cc;
}
caption{
    text-align: left;
    padding: 8px 0px;
}
th{
    background-color: #f5f5f5;
    white-space: nowrap; 
}
.container{
    overflow: auto;
    -webkit-overflow-scrolling: touch;
}
.diff th, td{
    vertical-align    : top;
    white-space       : pre;
    white-space       : pre-wrap;
    font-family       : monospace;
    border            : 1px solid #c3c9cc;
    padding           : 0.2em;
    text-align        : left;
}
.diffModify {
    background-color: #8fb5ff;
}
.diffInsert {
    background-color:  #e0ffe0;
}
</style>
</head>
<body>
    <div class="container">
        <table class="diff">
            <caption>Compare <b><?php echo $db1?></b> - <b><?php echo $db2?></b></caption>
            <thead>
            <tr>
                <th scope="col" style="width: 30px">#</th>
                <th scope="col">SQL scripts:</th>
            </tr>
            </thead>
            <tbody>
                <?php foreach ($diff_db['diff_sql'] as $key => $diff) { ?>
                <tr class="row">
                    <td><?php echo $key?></td>
                    <?php if($diff['type'] == 'insert') { ?>
                        <td class="diffInsert"><?php echo $diff['sql']?>;</td>
                    <?php } ?>
                    <?php if($diff['type'] == 'modify') { ?>
                        <td class="diffModify"><?php echo $diff['sql']?>;</td>
                    <?php } ?>
                </tr>
                <?php } ?>
            </tbody>
        </table>
    </div>
</body>
</html>