Unterschied zwischen zwei Datenbanken exportieren

Um mit Selenium Testcases für TYPO3 Extensions erzeugen zu können stellte sich mir die Frage, wie ich möglichst einfach ein umfangreiches Datenbank-Testinventar erstellen kann, ohne alle Tabelleneinträge von Hand anlegen zu müssen. Die Lösung ist dabei recht einfach:

  1. Datenbank kopieren
  2. Änderungen am Original durchführen
  3. Datenbanken vergleichen und Änderungen exportieren

Doch wie kommt man an die Änderungen zwischen beiden Datenbanken? Wenn beide Datenbanken (nennen wir sie actualDb und oldDb) auf dem gleichen Datenbankserver und für einen gemeinsamen User zugänglich sind, kann man dies für eine einzige Tabelle wie folgt erledigen.

SELECT actualDb.tableName.*
FROM actualDb.tableName
LEFT JOIN oldDb.tableName
    ON actualDb.tableName.uid=oldDb.tableName.uid
WHERE oldDb.tableName.uid IS NULL

Hierbei werden alle Zeilen ausgegeben, welche in der Tabelle actualDb.tableName vorhanden sind, nicht jedoch in der Tabelle oldDb.tableName. Das Ergebnis kann man sich dann beispielsweise mittels der OUTFILE-Syntax in eine CSV Datei exportieren, was dann insgesamt so aussieht:

SELECT actualDb.tableName.*
INTO OUTFILE 'C:/tableName.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM actualDb.tableName
LEFT JOIN oldDb.tableName
    ON actualDb.tableName.uid=oldDb.tableName.uid
WHERE oldDb.tableName.uid IS NULL

Bei diesem einfachen Beispiel würde die Differenz beider Tabellen auf dem Laufwerk C: in die Datei tableName.csv geschrieben werden. Die einzelnen Werte wären kommaspariert und ggf. durch doppelte Anführungszeichen eingeschlossen. Zeilen werden mit einem einfache Zeilenumbruch (‘\n’) umgebrochen.

Führt man dies für alle Tabellen der Datenbank durch, so erhält man ein Verzeichniss mit CSV Dateien, welche die Änderungen der gesamten DB seit dem letzten Kopieren der Datenbank enthalten.

Das ganze resultiert dann in der folgenden PHP-Klasse:

/**
 * Class for exporting and importing database differences
 */
class DbDiff {
 
    /**
    * @var PDO $pdo The PHP Data Object for the database connection
    */
    protected $pdo;
 
 
    /**
     * Constructor
     *
     * @param string $dbHost Database host
     * @param integer $dbPort Database port
     * @param string $dbUser Database user
     * @param string $dbUserPassword Database user password
     * @return void
     */
    public function __construct($dbHost='localhost', $dbPort=3306, $dbUser='root', $dbUserPassword='') {
        $this->pdo = new PDO(
            'mysql:host=' . $dbHost . ';port=' . $dbPort . ';',
            $dbUser,
            $dbUserPassword,
            array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")
        );
    }
 
 
    /**
     * Export database differences to CSV files
     *
     * @param string $actualDb The name of the up-to-date database
     * @param string $oldDb The name of the out-of-date database
     * @param string $directory Directory where the CSV files will be saved
     * @return void
     */
    public function exportToCsv($actualDb, $oldDb, $directory) {
        $sqlQuery = 'SHOW TABLES FROM ' . $actualDb;
        $sqlTables = $this->pdo->prepare($sqlQuery);
        $sqlTables->execute();
        while ($row = $sqlTables->fetch()) {
            $table = $row['Tables_in_' . $actualDb];
 
            $filename = $directory . $table . '.csv';
            if (file_exists($filename)) {
                unlink($filename);
            }
 
            $sqlQuery =
                'SELECT ' . $actualDb . '.' . $table . '.*
                INTO OUTFILE \'' . $filename . '\'
                FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\'
                LINES TERMINATED BY \'\n\'
                FROM ' . $actualDb . '.' . $table . '
                LEFT JOIN ' . $oldDb . '.' . $table . ' ON ' . $actualDb . '.' . $table . '.uid=' . $oldDb . '.' . $table . '.uid
                WHERE ' . $oldDb . '.' . $table . '.uid is NULL';
            $sqlStatement = $this->pdo->prepare($sqlQuery);
            $sqlStatement->execute();
 
            // Delete empty files
            if (file_exists($filename)) {
                if (filesize($filename) === 0) {
                    unlink($filename);
                } else {
                    echo  'Created: ' . $table . '.csv<br>';
                }
            }
        }
    }
 
 
    /**
     * Import data from CSVs
     * CSVs must be in one directory. Every CSV in this directory will be used as a data source.
     *
     * @param string $targetDb The name target database
     * @param string $directory Directory containing the CSV files to be imported
     * @return void
     */
    public function importFromCsv($targetDb, $directory) {
        foreach (glob($directory . '*.csv') as $filename) {
            $table = substr($filename, strrpos($filename, '/') + 1);
            $table = substr($table, 0, strrpos($table, '.csv'));
            echo 'Importing: ' . $filename . ' INTO ' . $table . '<br>';
 
            $sqlQuery = '
                LOAD DATA INFILE \'' . $filename . '\' INTO TABLE ' . $targetDb . '.' . $table . '
                FIELDS TERMINATED BY \',\'
                OPTIONALLY ENCLOSED BY \'"\'
                LINES TERMINATED BY \'\n\'';
            $sqlStatement = $this->pdo->prepare($sqlQuery);
            $sqlStatement->execute();
        }
    }
}
 

Die Klasse DbDiff besitzt (neben dem Konstruktor) zwei Methoden. Die Methode exportToCsv, um die Unterschiede zwischen den zwei Datenbanken in die CSV-Dateien zu schreiben, und die Methode importFromCsv, um die Dateien einzulesen und zu einer Datenbank hinzuzufügen. CSV Dateien, welche leer sind, werden abschließend gelöscht. Sie entstehen durch die MySQL Anweisung OUTFILE, welche auch bei einem leeren Ergebnis eine Datei erzeugt.

Hier noch die Klasse zum Herunterladen (ohne die Status Ausgaben mittels echo):

Abschließend noch ein Beispiel zur Verwendung:

// Usage:
$mydiff = new DbDiff();
 
$actualDb = 'myDb';
$oldDb = 'myDb_orig';
$directory = 'c:/dbdump/';
$mydiff->exportToCsv($actualDb, $oldDb, $directory);
 
$targetDb = 'myDb_orig';
$mydiff->importFromCsv($targetDb, $directory);

Für den Anwendungsfall muss natürlich erst die Datenbank myDb in die Datenbank myDb_orig kopiert werden. Anschließend führt man wie eingangs erwähnt die gewünschten Änderungen durch. Das Skript erstellt dann im ersten Schritt die CSV Dateien, und wendet diese dann auf die Datenbank myDb_orig an. Dies führt dann dazu, dass beide Datenbanken wieder synchron sind.

Die Standardeinstellungen für die Datenbank nehmen an, dass es sich um eine lokale MySQL Datenbank mit Root als User ohne Passwort handelt (was für eine lokale Testdatenbenk nicht unüblich ist). Diese müssen ggf. natürlich angepasst werden, und können mittels der Konstruktorparameter übergeben werden.

Mit dieser Klasse ist es im Übrigen nicht möglich zwei Datenbanken auf verschiedenen Servern oder mit verschiedenen Usern direkt miteinander zu vergleichen. Daher auch der Schritt des Kopierens der Datenbank. Auch darf sich das Tabellen Schema nicht ändern. Es dient lediglich dem Auffinden inhaltlicher Änderungen.