<?php

$DB_HOST = 'host';        // Host
$DB_USER = 'user';        // User
$DB_PASS = 'pass';    // Password

$DB_NAME_1 = 'ctdb1';  // First database
$DB_NAME_2 = 'ctdb2';  // Second database

$EOL = "\n";                // End of line separator
$BASEDIR = 'sql';         // Directory for results
$PAGE = 1000;               // Range of rows to select per query
$KEY_SEPARATOR = '=>';

// List of tables to skip...
$SKIP_TABLES = array(
    'creature_respawn',
    'gameobject_respawn',
    'locales_quest' // Too large table to select even with $PAGE = 100 and 128Mb of memory for PHP
);

$SPECIFIC_TABLES = array(); // Fill in this array to process specific tables only.

// Customize page size for various tables if necessary.
$TABLE_PAGE = array(

);

set_time_limit(1800);

function array_compare($array1, $array2) {
    $diff = false;
    // Left-to-right
    if (isset($array1)) {
        foreach ($array1 as $key => $value) {
            if (!isset($array2) || !array_key_exists($key, $array2)) {
                $diff[1][$key] = $value;
            } elseif (is_array($value)) {
                if (!is_array($array2[$key])) {
                    $diff[0][$key] = array($value, $array2[$key]);
                } else {
                    $new = array_compare($value, $array2[$key]);
                    if ($new !== false) {
                        if (isset($new[0])) {
                            $diff[0][$key] = $new[0];
                        } elseif (isset($new[1])) {
                            $diff[1][$key] = $new[1];
                        } elseif (isset($new[2])) {
                            $diff[2][$key] = $new[2];
                        }
                    }
                }
            } elseif ($array2[$key] !== $value) {
                $diff[0][$key] = array($value, $array2[$key]);
            }
        }
    }
    // Right-to-left
    if (isset($array2)) {
        foreach ($array2 as $key => $value) {
            if (!isset($array1) || !array_key_exists($key, $array1)) {
                $diff[2][$key] = $value;
            }
            // No direct comparsion because matching keys were compared in the
            // left-to-right loop earlier, recursively.
        }
    }
    return $diff;
};

// Removes file if it exists.
function deleteFile($file) {
    if (file_exists($file)) {
        unlink($file);
    }
}

// Appends $new string to $s.
// If $s is not empty, appends $sep before $new.
function append(&$s, $new, $sep) {
    if ($s) {
        $s .= $sep;
    }
    $s .= $new;
}

// Performs $sql query to specified $db and fetches result
// using $keyNames fields as key for associative array
function do_query($sql, $db, $keyNames, $use_quotes) {
    $rs = do_query_rs($sql, $db);
    return fetch_result($rs, $keyNames, $use_quotes);
}

// Performs $sql query to specified $db and returns result set.
function do_query_rs($sql, $db) {
    global $LOG, $EOL;
    mysql_select_db($db);
    if (!$rs = mysql_query($sql)) {
        $err = 'Error: '.mysql_error();
        fwrite($LOG, $sql.$EOL.$err);
        die($sql.'<BR>'.$err);
    }
    return $rs;
}

// Fetches result from $rs result set using $keyNames fields as key for associative array.
function fetch_result($rs, $keyNames, $use_quotes) {
    $res = null;
    if (mysql_num_rows($rs) > 0) {
        $res = array();
        while ($row = mysql_fetch_assoc($rs)) {
            $res[getResultKey($row, $keyNames, $use_quotes)] = $row;
        }
    }
    mysql_free_result($rs);
    return $res;
}

// Formats key from values using $keyNames fields.
function getResultKey($row, $keyNames, $use_quotes) {
    global $KEY_SEPARATOR;
    $key = '';
    foreach ($keyNames as $keyName) {
        append($key, getValue($row[$keyName], $use_quotes), $KEY_SEPARATOR);
    }
    return $key;
}

// Detects which columns represent primary key of the table.
function getPrimaryKey($fields) {
    $keys = array();
    foreach ($fields as $key => $field) {
        if ($field['Key'] == 'PRI') {
            $keys[] = $key;
        }
    }
    return $keys;
}

function getValue($value, $use_quotes) {
    if ($value === null) {
        $res = 'NULL';
    } else {
        $res = mysql_real_escape_string($value);
        if ($use_quotes) {
            $res = "'".$res."'";
        }
    }
    return $res;
}

function printInsert($filename, $table, $arr) {
    global $EOL;

    if (count($arr) != 0) {
        $fields = '';
        $rows = '';
        $do_fields = true;
        foreach ($arr as $key => $row) {
            $values = '';
            foreach ($row as $field => $value) {
                if ($do_fields) {
                    append($fields, $field, ', ');
                }
                append($values, getValue($value, true), ', ');
            }
            append($rows, '('.$values.')', ','.$EOL);
            $do_fields = false;
        }

        // Write to file.
        $f = fopen($filename, a);
        fwrite($f, 'INSERT INTO '.$table.' ('.$fields.') VALUES '.$EOL.$rows.';'.$EOL.$EOL);
        fclose($f);
    }
}

function printUpdate($filename, $table, $arr, $keys) {
    global $EOL, $KEY_SEPARATOR;

    if (count($arr) != 0) {
        $f = fopen($filename, a);
        foreach ($arr as $key => $row) {
            $values = '';
            foreach ($row as $field => $value) {
                append($values,
                    $field." = ".getValue($value[1], true)." -- was ".getValue($value[0], true).$EOL,
                    ', ');
            }
            $keyWhere = '';
            $keyValues = explode($KEY_SEPARATOR, $key);
            $i = 0;
            foreach ($keys as $keyName) {
                $kv = $keyValues[$i++];
                $kv = ($kv == 'NULL') ? ' IS NULL' : ' = '.$kv;
                append($keyWhere, $keyName.$kv, ' AND ');
            }
            fwrite($f, 'UPDATE '.$table.' SET '.$values.' WHERE '.$keyWhere.';'.$EOL.$EOL);
        }
        fclose($f);
    }
}

// Create necessary directories...
if (!file_exists($BASEDIR)) {
    mkdir($BASEDIR);
}
if (!file_exists($BASEDIR.'/'.$DB_NAME_1)) {
    mkdir($BASEDIR.'/'.$DB_NAME_1);
}
if (!file_exists($BASEDIR.'/'.$DB_NAME_2)) {
    mkdir($BASEDIR.'/'.$DB_NAME_2);
}

$conn = mysql_connect($DB_HOST, $DB_USER, $DB_PASS) or die(mysql_error());
if (count($SPECIFIC_TABLES) != 0) {
    $tables = $SPECIFIC_TABLES;
} else {
    // Make a list of tables, present in both databases.
    $sql = "SHOW TABLES";
    $tables1 = array_keys(do_query($sql, $DB_NAME_1, array('Tables_in_'.$DB_NAME_1), false));
    $tables2 = array_keys(do_query($sql, $DB_NAME_2, array('Tables_in_'.$DB_NAME_2), false));
    $tables = array_intersect($tables1, $tables2);
}

// Start logging.
$time = date('Ymd_His');
$LOG = fopen("compare_{$DB_NAME_1}_{$DB_NAME_2}_{$time}.log", w);
// Tables to process.
foreach ($tables as $table) {
    echo $table;
    if (in_array($table, $SKIP_TABLES)) {
        echo ' - skipped<br>';
        continue;
    }
    echo '<br>';

    // Detect columns, present in both tables.
    $sql = "SHOW COLUMNS FROM $table";
    $keys = array('Field');
    $res1 = do_query($sql, $DB_NAME_1, $keys, false);
    $res2 = do_query($sql, $DB_NAME_2, $keys, false);

    $diff = array_compare($res1, $res2);
    fwrite($LOG, '--- '.$table.' ---'.$EOL);
    fwrite($LOG, 'Fields: ');
    $fields = '';
    foreach ($res1 as $key => $value) {
        if (!isset($diff[1]) || !array_key_exists($key, $diff[1])) {
            append($fields, '`'.$key.'`', ', ');
        }
    }
    fwrite($LOG, $fields.$EOL);

    // Detect primary key columns.
    // If no primary key is present, then use 'id' or 'entry' column if present.
    // Otherwise, skip table.
    $pr = getPrimaryKey($res1);
    if (count($pr) == 0) {
        if (array_key_exists('id', $res1)) {
            $pr = array('id');
        } else if (array_key_exists('entry', $res1)) {
            $pr = array('entry');
        } else {
            fwrite($LOG, 'Skipping table. Primary key missing...'.$EOL.$EOL);
            continue;
        }
    }

    // Detect minimum and maximum values in both tables to paginate selection (for numeric columns only).
    // It's made to prevent total consumption of memory.
    $fkey = $pr[0];
    $sql = "SELECT MAX($fkey), MIN($fkey) FROM $table";
    $rs = do_query_rs($sql, $DB_NAME_1);
    $res = mysql_fetch_array($rs);
    $max1 = $res[0];
    $min1 = $res[1];
    mysql_free_result($rs);
    $rs = do_query_rs($sql, $DB_NAME_2);
    $res = mysql_fetch_array($rs);
    $max2 = $res[0];
    $min2 = $res[1];
    mysql_free_result($rs);
    if (is_numeric($max1)) {
        $max_value = max($max1, $max2);
        $min_value = min($min1, $min2);
        fwrite($LOG, 'First primary column: '.$fkey.', min value: '.$min_value.', max value: '.$max_value.$EOL);
    } else {
        $max_value = 0;
        $min_value = 0;
    }
    $page_value = isset($TABLE_PAGE[$table]) ? $TABLE_PAGE[$table] : $PAGE;
    $min = $min_value;
    $max = $min_value + $page_value - 1;

    // Format file names.
    $file0 = $BASEDIR.'/'.$table.'.sql';                    // Changed rows.
    $file1 = $BASEDIR.'/'.$DB_NAME_1.'/'.$table.'.sql';     // Unique rows for first database.
    $file2 = $BASEDIR.'/'.$DB_NAME_2.'/'.$table.'.sql';     // Unique rows for second database.
    // Remove previous files.
    deleteFile($file0);
    deleteFile($file1);
    deleteFile($file2);
    // Init counters.
    $rec1 = 0;
    $rec2 = 0;
    $rec3 = 0;
    while (true) {
        if ($max_value == 0) {
            // Select all rows ror non-numeric primary keys.
            $sql = "SELECT $fields FROM $table ORDER BY ".implode(', ', $pr);
        } else {
            // Select range of rows.
            $sql = "SELECT $fields FROM $table WHERE $fkey BETWEEN $min AND $max ORDER BY ".implode(', ', $pr);
        }
        $values1 = do_query($sql, $DB_NAME_1, $pr, true);
        $values2 = do_query($sql, $DB_NAME_2, $pr, true);

        $diff = array_compare($values1, $values2);
        if (count($diff[0])) {
            $rec1 += count($diff[0]);
            printUpdate($file0, $table, $diff[0], $pr);
        }
        if (count($diff[1])) {
            $rec2 += count($diff[1]);
            printInsert($file1, $table, $diff[1]);
        }
        if (count($diff[2])) {
            $rec3 += count($diff[2]);
            printInsert($file2, $table, $diff[2]);
        }

        unset($values1);
        unset($values2);
        unset($diff);

        $min += $page_value;
        $max += $page_value;
        if (($max_value == 0) || ($min >= $max_value)) {
            break;
        }
    }
    // Output statistics to log file.
    if ($rec2) {
        fwrite($LOG, 'Unique old: '.$rec2.$EOL);
    }
    if ($rec3) {
        fwrite($LOG, 'Unique new: '.$rec3.$EOL);
    }
    if ($rec1) {
        fwrite($LOG, 'Changed: '.$rec1.$EOL);
    }
    fwrite($LOG, 'DONE!'.$EOL.$EOL);
}
fclose($LOG);
echo 'DONE!';
?>