· 9 years ago · May 19, 2017, 10:54 AM
1<?php
2
3$DB_HOST = 'host'; // Host
4$DB_USER = 'user'; // User
5$DB_PASS = 'pass'; // Password
6
7$DB_NAME_1 = 'ctdb1'; // First database
8$DB_NAME_2 = 'ctdb2'; // Second database
9
10$EOL = "\n"; // End of line separator
11$BASEDIR = 'sql'; // Directory for results
12$PAGE = 1000; // Range of rows to select per query
13$KEY_SEPARATOR = '=>';
14
15// List of tables to skip...
16$SKIP_TABLES = array(
17 'creature_respawn',
18 'gameobject_respawn',
19 'locales_quest' // Too large table to select even with $PAGE = 100 and 128Mb of memory for PHP
20);
21
22$SPECIFIC_TABLES = array(); // Fill in this array to process specific tables only.
23
24// Customize page size for various tables if necessary.
25$TABLE_PAGE = array(
26
27);
28
29set_time_limit(1800);
30
31function array_compare($array1, $array2) {
32 $diff = false;
33 // Left-to-right
34 if (isset($array1)) {
35 foreach ($array1 as $key => $value) {
36 if (!isset($array2) || !array_key_exists($key, $array2)) {
37 $diff[1][$key] = $value;
38 } elseif (is_array($value)) {
39 if (!is_array($array2[$key])) {
40 $diff[0][$key] = array($value, $array2[$key]);
41 } else {
42 $new = array_compare($value, $array2[$key]);
43 if ($new !== false) {
44 if (isset($new[0])) {
45 $diff[0][$key] = $new[0];
46 } elseif (isset($new[1])) {
47 $diff[1][$key] = $new[1];
48 } elseif (isset($new[2])) {
49 $diff[2][$key] = $new[2];
50 }
51 }
52 }
53 } elseif ($array2[$key] !== $value) {
54 $diff[0][$key] = array($value, $array2[$key]);
55 }
56 }
57 }
58 // Right-to-left
59 if (isset($array2)) {
60 foreach ($array2 as $key => $value) {
61 if (!isset($array1) || !array_key_exists($key, $array1)) {
62 $diff[2][$key] = $value;
63 }
64 // No direct comparsion because matching keys were compared in the
65 // left-to-right loop earlier, recursively.
66 }
67 }
68 return $diff;
69};
70
71// Removes file if it exists.
72function deleteFile($file) {
73 if (file_exists($file)) {
74 unlink($file);
75 }
76}
77
78// Appends $new string to $s.
79// If $s is not empty, appends $sep before $new.
80function append(&$s, $new, $sep) {
81 if ($s) {
82 $s .= $sep;
83 }
84 $s .= $new;
85}
86
87// Performs $sql query to specified $db and fetches result
88// using $keyNames fields as key for associative array
89function do_query($sql, $db, $keyNames, $use_quotes) {
90 $rs = do_query_rs($sql, $db);
91 return fetch_result($rs, $keyNames, $use_quotes);
92}
93
94// Performs $sql query to specified $db and returns result set.
95function do_query_rs($sql, $db) {
96 global $LOG, $EOL;
97 mysql_select_db($db);
98 if (!$rs = mysql_query($sql)) {
99 $err = 'Error: '.mysql_error();
100 fwrite($LOG, $sql.$EOL.$err);
101 die($sql.'<BR>'.$err);
102 }
103 return $rs;
104}
105
106// Fetches result from $rs result set using $keyNames fields as key for associative array.
107function fetch_result($rs, $keyNames, $use_quotes) {
108 $res = null;
109 if (mysql_num_rows($rs) > 0) {
110 $res = array();
111 while ($row = mysql_fetch_assoc($rs)) {
112 $res[getResultKey($row, $keyNames, $use_quotes)] = $row;
113 }
114 }
115 mysql_free_result($rs);
116 return $res;
117}
118
119// Formats key from values using $keyNames fields.
120function getResultKey($row, $keyNames, $use_quotes) {
121 global $KEY_SEPARATOR;
122 $key = '';
123 foreach ($keyNames as $keyName) {
124 append($key, getValue($row[$keyName], $use_quotes), $KEY_SEPARATOR);
125 }
126 return $key;
127}
128
129// Detects which columns represent primary key of the table.
130function getPrimaryKey($fields) {
131 $keys = array();
132 foreach ($fields as $key => $field) {
133 if ($field['Key'] == 'PRI') {
134 $keys[] = $key;
135 }
136 }
137 return $keys;
138}
139
140function getValue($value, $use_quotes) {
141 if ($value === null) {
142 $res = 'NULL';
143 } else {
144 $res = mysql_real_escape_string($value);
145 if ($use_quotes) {
146 $res = "'".$res."'";
147 }
148 }
149 return $res;
150}
151
152function printInsert($filename, $table, $arr) {
153 global $EOL;
154
155 if (count($arr) != 0) {
156 $fields = '';
157 $rows = '';
158 $do_fields = true;
159 foreach ($arr as $key => $row) {
160 $values = '';
161 foreach ($row as $field => $value) {
162 if ($do_fields) {
163 append($fields, $field, ', ');
164 }
165 append($values, getValue($value, true), ', ');
166 }
167 append($rows, '('.$values.')', ','.$EOL);
168 $do_fields = false;
169 }
170
171 // Write to file.
172 $f = fopen($filename, a);
173 fwrite($f, 'INSERT INTO '.$table.' ('.$fields.') VALUES '.$EOL.$rows.';'.$EOL.$EOL);
174 fclose($f);
175 }
176}
177
178function printUpdate($filename, $table, $arr, $keys) {
179 global $EOL, $KEY_SEPARATOR;
180
181 if (count($arr) != 0) {
182 $f = fopen($filename, a);
183 foreach ($arr as $key => $row) {
184 $values = '';
185 foreach ($row as $field => $value) {
186 append($values,
187 $field." = ".getValue($value[1], true)." -- was ".getValue($value[0], true).$EOL,
188 ', ');
189 }
190 $keyWhere = '';
191 $keyValues = explode($KEY_SEPARATOR, $key);
192 $i = 0;
193 foreach ($keys as $keyName) {
194 $kv = $keyValues[$i++];
195 $kv = ($kv == 'NULL') ? ' IS NULL' : ' = '.$kv;
196 append($keyWhere, $keyName.$kv, ' AND ');
197 }
198 fwrite($f, 'UPDATE '.$table.' SET '.$values.' WHERE '.$keyWhere.';'.$EOL.$EOL);
199 }
200 fclose($f);
201 }
202}
203
204// Create necessary directories...
205if (!file_exists($BASEDIR)) {
206 mkdir($BASEDIR);
207}
208if (!file_exists($BASEDIR.'/'.$DB_NAME_1)) {
209 mkdir($BASEDIR.'/'.$DB_NAME_1);
210}
211if (!file_exists($BASEDIR.'/'.$DB_NAME_2)) {
212 mkdir($BASEDIR.'/'.$DB_NAME_2);
213}
214
215$conn = mysql_connect($DB_HOST, $DB_USER, $DB_PASS) or die(mysql_error());
216if (count($SPECIFIC_TABLES) != 0) {
217 $tables = $SPECIFIC_TABLES;
218} else {
219 // Make a list of tables, present in both databases.
220 $sql = "SHOW TABLES";
221 $tables1 = array_keys(do_query($sql, $DB_NAME_1, array('Tables_in_'.$DB_NAME_1), false));
222 $tables2 = array_keys(do_query($sql, $DB_NAME_2, array('Tables_in_'.$DB_NAME_2), false));
223 $tables = array_intersect($tables1, $tables2);
224}
225
226// Start logging.
227$time = date('Ymd_His');
228$LOG = fopen("compare_{$DB_NAME_1}_{$DB_NAME_2}_{$time}.log", w);
229// Tables to process.
230foreach ($tables as $table) {
231 echo $table;
232 if (in_array($table, $SKIP_TABLES)) {
233 echo ' - skipped<br>';
234 continue;
235 }
236 echo '<br>';
237
238 // Detect columns, present in both tables.
239 $sql = "SHOW COLUMNS FROM $table";
240 $keys = array('Field');
241 $res1 = do_query($sql, $DB_NAME_1, $keys, false);
242 $res2 = do_query($sql, $DB_NAME_2, $keys, false);
243
244 $diff = array_compare($res1, $res2);
245 fwrite($LOG, '--- '.$table.' ---'.$EOL);
246 fwrite($LOG, 'Fields: ');
247 $fields = '';
248 foreach ($res1 as $key => $value) {
249 if (!isset($diff[1]) || !array_key_exists($key, $diff[1])) {
250 append($fields, '`'.$key.'`', ', ');
251 }
252 }
253 fwrite($LOG, $fields.$EOL);
254
255 // Detect primary key columns.
256 // If no primary key is present, then use 'id' or 'entry' column if present.
257 // Otherwise, skip table.
258 $pr = getPrimaryKey($res1);
259 if (count($pr) == 0) {
260 if (array_key_exists('id', $res1)) {
261 $pr = array('id');
262 } else if (array_key_exists('entry', $res1)) {
263 $pr = array('entry');
264 } else {
265 fwrite($LOG, 'Skipping table. Primary key missing...'.$EOL.$EOL);
266 continue;
267 }
268 }
269
270 // Detect minimum and maximum values in both tables to paginate selection (for numeric columns only).
271 // It's made to prevent total consumption of memory.
272 $fkey = $pr[0];
273 $sql = "SELECT MAX($fkey), MIN($fkey) FROM $table";
274 $rs = do_query_rs($sql, $DB_NAME_1);
275 $res = mysql_fetch_array($rs);
276 $max1 = $res[0];
277 $min1 = $res[1];
278 mysql_free_result($rs);
279 $rs = do_query_rs($sql, $DB_NAME_2);
280 $res = mysql_fetch_array($rs);
281 $max2 = $res[0];
282 $min2 = $res[1];
283 mysql_free_result($rs);
284 if (is_numeric($max1)) {
285 $max_value = max($max1, $max2);
286 $min_value = min($min1, $min2);
287 fwrite($LOG, 'First primary column: '.$fkey.', min value: '.$min_value.', max value: '.$max_value.$EOL);
288 } else {
289 $max_value = 0;
290 $min_value = 0;
291 }
292 $page_value = isset($TABLE_PAGE[$table]) ? $TABLE_PAGE[$table] : $PAGE;
293 $min = $min_value;
294 $max = $min_value + $page_value - 1;
295
296 // Format file names.
297 $file0 = $BASEDIR.'/'.$table.'.sql'; // Changed rows.
298 $file1 = $BASEDIR.'/'.$DB_NAME_1.'/'.$table.'.sql'; // Unique rows for first database.
299 $file2 = $BASEDIR.'/'.$DB_NAME_2.'/'.$table.'.sql'; // Unique rows for second database.
300 // Remove previous files.
301 deleteFile($file0);
302 deleteFile($file1);
303 deleteFile($file2);
304 // Init counters.
305 $rec1 = 0;
306 $rec2 = 0;
307 $rec3 = 0;
308 while (true) {
309 if ($max_value == 0) {
310 // Select all rows ror non-numeric primary keys.
311 $sql = "SELECT $fields FROM $table ORDER BY ".implode(', ', $pr);
312 } else {
313 // Select range of rows.
314 $sql = "SELECT $fields FROM $table WHERE $fkey BETWEEN $min AND $max ORDER BY ".implode(', ', $pr);
315 }
316 $values1 = do_query($sql, $DB_NAME_1, $pr, true);
317 $values2 = do_query($sql, $DB_NAME_2, $pr, true);
318
319 $diff = array_compare($values1, $values2);
320 if (count($diff[0])) {
321 $rec1 += count($diff[0]);
322 printUpdate($file0, $table, $diff[0], $pr);
323 }
324 if (count($diff[1])) {
325 $rec2 += count($diff[1]);
326 printInsert($file1, $table, $diff[1]);
327 }
328 if (count($diff[2])) {
329 $rec3 += count($diff[2]);
330 printInsert($file2, $table, $diff[2]);
331 }
332
333 unset($values1);
334 unset($values2);
335 unset($diff);
336
337 $min += $page_value;
338 $max += $page_value;
339 if (($max_value == 0) || ($min >= $max_value)) {
340 break;
341 }
342 }
343 // Output statistics to log file.
344 if ($rec2) {
345 fwrite($LOG, 'Unique old: '.$rec2.$EOL);
346 }
347 if ($rec3) {
348 fwrite($LOG, 'Unique new: '.$rec3.$EOL);
349 }
350 if ($rec1) {
351 fwrite($LOG, 'Changed: '.$rec1.$EOL);
352 }
353 fwrite($LOG, 'DONE!'.$EOL.$EOL);
354}
355fclose($LOG);
356echo 'DONE!';
357?>