Bug: Live query chart always zero
[phpmyadmin/tyronm.git] / libraries / server_synchronize.lib.php
blobeacae70645a7bda5324cfec83ddfb6fbede6bb80
1 <?php
2 /* vim: set expandtab sw=4 ts=4 sts=4: */
3 /**
4 * Server synchronisation functions.
6 * @package phpMyAdmin
7 */
9 /**
10 * Places matching tables in source and target databases in $matching_tables
11 * array whereas $uncommon_source_tables array gets the tables present in
12 * source database but are absent from target database. Criterion for
13 * matching tables is just comparing their names.
15 * @param array $trg_tables array of target database table names,
16 * @param array $src_tables array of source database table names,
17 * @param array &$matching_tables empty array passed by reference to save
18 * names of matching tables,
19 * @param array &$uncommon_source_tables empty array passed by reference to save
20 * names of tables present in source database
21 * but absent from target database
23 function PMA_getMatchingTables($trg_tables, $src_tables, &$matching_tables, &$uncommon_source_tables)
25 for ($k=0; $k< sizeof($src_tables); $k++) {
26 $present_in_target = false;
27 for ($l=0; $l < sizeof($trg_tables); $l++) {
28 if ($src_tables[$k] === $trg_tables[$l]) {
29 $present_in_target = true;
30 $matching_tables[] = $src_tables[$k];
33 if ($present_in_target === false) {
34 $uncommon_source_tables[] = $src_tables[$k];
39 /**
40 * Places tables present in target database but are absent from source database
42 * @param array $trg_tables array of target database table names,
43 * @param array $matching_tables matching tables array containing names
44 * of matching tables,
45 * @param array &$uncommon_target_tables empty array passed by reference to save
46 * names of tables presnet in target database
47 * but absent from source database
49 function PMA_getNonMatchingTargetTables($trg_tables, $matching_tables, &$uncommon_target_tables)
51 for ($c=0; $c<sizeof($trg_tables); $c++) {
52 $match = false;
53 for ($d=0; $d < sizeof($matching_tables); $d++) {
54 if ($trg_tables[$c] === $matching_tables[$d]) {
55 $match=true;
58 if ($match === false) {
59 $uncommon_target_tables[] = $trg_tables[$c];
64 /**
65 * Finds the difference in source and target matching tables by
66 * first comparing source table's primary key entries with target table enteries.
67 * It gets the field names for the matching table also for comparisons.
68 * If the entry is found in target table also then it is checked for the remaining
69 * field values also, in order to check whether update is required or not.
70 * If update is required, it is placed in $update_array
71 * Otherwise that entry is placed in the $insert_array.
73 * @param string $src_db name of source database
74 * @param string $trg_db name of target database
75 * @param db_link $src_link connection established with source server
76 * @param db_link $trg_link connection established with target server
77 * @param array &$matching_table array containing matching table names
78 * @param array &$matching_tables_fields A two dimensional array passed by reference to contain names of fields for each matching table
79 * @param array &$update_array A three dimensional array passed by reference to
80 * contain updates required for each matching table
81 * @param array &$insert_array A three dimensional array passed by reference to
82 * contain inserts required for each matching table
83 * @param array &$delete_array Unused
84 * @param array &$fields_num A two dimensional array passed by reference to
85 * contain number of fields for each matching table
86 * @param int $matching_table_index Index of a table from $matching_table array
87 * @param array &$matching_tables_keys A two dimensional array passed by reference to contain names of keys for each matching table
89 function PMA_dataDiffInTables($src_db, $trg_db, $src_link, $trg_link, &$matching_table, &$matching_tables_fields,
90 &$update_array, &$insert_array, &$delete_array, &$fields_num, $matching_table_index, &$matching_tables_keys)
92 if (isset($matching_table[$matching_table_index])) {
93 $fld = array();
94 $fld_results = PMA_DBI_get_columns($src_db, $matching_table[$matching_table_index], true, $src_link);
95 $is_key = array();
96 if (isset($fld_results)) {
97 foreach ($fld_results as $each_field) {
98 $field_name = $each_field['Field'];
99 if ($each_field['Key'] == 'PRI') {
100 $is_key[] = $field_name;
102 $fld[] = $field_name;
105 $matching_tables_fields[$matching_table_index] = $fld;
106 $fields_num[$matching_table_index] = sizeof($fld);
107 $matching_tables_keys[$matching_table_index] = $is_key;
109 $source_result_set = PMA_get_column_values($src_db, $matching_table[$matching_table_index], $is_key, $src_link);
110 $source_size = sizeof($source_result_set);
112 $trg_fld_results = PMA_DBI_get_columns($trg_db, $matching_table[$matching_table_index], true, $trg_link);
113 $all_keys_match = true;
114 $trg_keys = array();
116 if (isset($trg_fld_results)) {
117 foreach ($trg_fld_results as $each_field) {
118 if ($each_field['Key'] == 'PRI') {
119 $trg_keys[] = $each_field['Field'];
120 if (! (in_array($each_field['Field'], $is_key))) {
121 $all_keys_match = false;
126 $update_row = 0;
127 $insert_row = 0;
129 for ($j = 0; $j < $source_size; $j++) {
130 $starting_index = 0;
131 $update_field = 0;
133 if (isset($source_result_set[$j]) && ($all_keys_match)) {
135 // Query the target server to see which rows already exist
136 $trg_select_query = "SELECT * FROM " . PMA_backquote($trg_db) . "."
137 . PMA_backquote($matching_table[$matching_table_index]) . " WHERE ";
139 if (sizeof($is_key) == 1) {
140 $trg_select_query .= $is_key[0]. "='" . $source_result_set[$j] . "'";
141 } elseif (sizeof($is_key) > 1) {
142 for ($k=0; $k < sizeof($is_key); $k++) {
143 $trg_select_query .= $is_key[$k] . "='" . $source_result_set[$j][$is_key[$k]] . "'";
144 if ($k < (sizeof($is_key)-1)) {
145 $trg_select_query .= " AND ";
150 $target_result_set = PMA_DBI_fetch_result($trg_select_query, null, null, $trg_link);
151 if ($target_result_set) {
153 // Fetch the row from the source server to do a comparison
154 $src_select_query = "SELECT * FROM " . PMA_backquote($src_db) . "."
155 . PMA_backquote($matching_table[$matching_table_index]) . " WHERE ";
157 if (sizeof($is_key) == 1) {
158 $src_select_query .= $is_key[0] . "='" . $source_result_set[$j] . "'";
159 } elseif (sizeof($is_key) > 1) {
160 for ($k=0; $k< sizeof($is_key); $k++) {
161 $src_select_query .= $is_key[$k] . "='" . $source_result_set[$j][$is_key[$k]] . "'";
162 if ($k < (sizeof($is_key) - 1)) {
163 $src_select_query .= " AND ";
168 $src_result_set = PMA_DBI_fetch_result($src_select_query, null, null, $src_link);
171 * Comparing each corresponding field of the source and target matching rows.
172 * Placing the primary key, value of primary key, field to be updated, and the
173 * new value of field to be updated in each row of the update array.
175 for ($m = 0; ($m < $fields_num[$matching_table_index]) && ($starting_index == 0) ; $m++) {
176 if (isset($src_result_set[0][$fld[$m]])) {
177 if (isset($target_result_set[0][$fld[$m]])) {
178 if (($src_result_set[0][$fld[$m]] != $target_result_set[0][$fld[$m]]) && (! (in_array($fld[$m], $is_key)))) {
179 if (sizeof($is_key) == 1) {
180 if ($source_result_set[$j]) {
181 $update_array[$matching_table_index][$update_row][$is_key[0]] = $source_result_set[$j];
183 } elseif (sizeof($is_key) > 1) {
184 for ($n=0; $n < sizeof($is_key); $n++) {
185 if (isset($src_result_set[0][$is_key[$n]])) {
186 $update_array[$matching_table_index][$update_row][$is_key[$n]] = $src_result_set[0][$is_key[$n]];
191 $update_array[$matching_table_index][$update_row][$update_field] = $fld[$m];
193 $update_field++;
194 if (isset($src_result_set[0][$fld[$m]])) {
195 $update_array[$matching_table_index][$update_row][$update_field] = $src_result_set[0][$fld[$m]];
196 $update_field++;
198 $starting_index = $m;
199 $update_row++;
201 } else {
202 if (sizeof($is_key) == 1) {
203 if ($source_result_set[$j]) {
204 $update_array[$matching_table_index][$update_row][$is_key[0]] = $source_result_set[$j];
206 } elseif (sizeof($is_key) > 1) {
207 for ($n = 0; $n < sizeof($is_key); $n++) {
208 if (isset($src_result_set[0][$is_key[$n]])) {
209 $update_array[$matching_table_index][$update_row][$is_key[$n]] = $src_result_set[0][$is_key[$n]];
214 $update_array[$matching_table_index][$update_row][$update_field] = $fld[$m];
216 $update_field++;
217 if (isset($src_result_set[0][$fld[$m]])) {
218 $update_array[$matching_table_index][$update_row][$update_field] = $src_result_set[0][$fld[$m]];
219 $update_field++;
221 $starting_index = $m;
222 $update_row++;
226 for ($m = $starting_index + 1; $m < $fields_num[$matching_table_index] ; $m++) {
227 if (isset($src_result_set[0][$fld[$m]])) {
228 if (isset($target_result_set[0][$fld[$m]])) {
229 if (($src_result_set[0][$fld[$m]] != $target_result_set[0][$fld[$m]]) && (!(in_array($fld[$m], $is_key)))) {
230 $update_row--;
231 $update_array[$matching_table_index][$update_row][$update_field] = $fld[$m];
232 $update_field++;
233 if ($src_result_set[0][$fld[$m]]) {
234 $update_array[$matching_table_index][$update_row][$update_field] = $src_result_set[0][$fld[$m]];
235 $update_field++;
237 $update_row++;
239 } else {
240 $update_row--;
241 $update_array[$matching_table_index][$update_row][$update_field] = $fld[$m];
242 $update_field++;
243 if ($src_result_set[0][$fld[$m]]) {
244 $update_array[$matching_table_index][$update_row][$update_field] = $src_result_set[0][$fld[$m]];
245 $update_field++;
247 $update_row++;
251 } else {
253 * Placing the primary key, and the value of primary key of the row that is to be inserted in the target table
255 if (sizeof($is_key) == 1) {
256 if (isset($source_result_set[$j])) {
257 $insert_array[$matching_table_index][$insert_row][$is_key[0]] = $source_result_set[$j];
259 } elseif (sizeof($is_key) > 1) {
260 for ($l = 0; $l < sizeof($is_key); $l++) {
261 if (isset($source_result_set[$j][$matching_tables_fields[$matching_table_index][$l]])) {
262 $insert_array[$matching_table_index][$insert_row][$is_key[$l]] = $source_result_set[$j][$matching_tables_fields[$matching_table_index][$l]];
266 $insert_row++;
268 } else {
270 * Placing the primary key, and the value of primary key of the row that is to be inserted in the target table
271 * This condition is met when there is an additional column in the source table
273 if (sizeof($is_key) == 1) {
274 if (isset($source_result_set[$j])) {
275 $insert_array[$matching_table_index][$insert_row][$is_key[0]] = $source_result_set[$j];
277 } elseif (sizeof($is_key) > 1) {
278 for ($l = 0; $l < sizeof($is_key); $l++) {
279 if (isset($source_result_set[$j][$matching_tables_fields[$matching_table_index][$l]])) {
280 $insert_array[$matching_table_index][$insert_row][$is_key[$l]] = $source_result_set[$j][$matching_tables_fields[$matching_table_index][$l]];
284 $insert_row++;
286 } // for loop ends
291 * Finds the rows which are to be deleted from target table.
293 * @param array &$delete_array array containing rows that are to be deleted
294 * @param array $matching_table array containing matching table names
295 * @param int $matching_table_index index of a table from $matching_table array
296 * @param array $trg_keys array of target table keys
297 * @param array $src_keys array of source table keys
298 * @param string $trg_db name of target database
299 * @param db_link $trg_link connection established with target server
300 * @param string $src_db name of source database
301 * @param db_link $src_link connection established with source server
303 function PMA_findDeleteRowsFromTargetTables(&$delete_array, $matching_table, $matching_table_index, $trg_keys, $src_keys, $trg_db, $trg_link, $src_db, $src_link)
305 if (isset($trg_keys[$matching_table_index])) {
306 $target_key_values = PMA_get_column_values($trg_db, $matching_table[$matching_table_index], $trg_keys[$matching_table_index], $trg_link);
308 if (isset($src_keys[$matching_table_index])) {
309 $source_key_values = PMA_get_column_values($src_db, $matching_table[$matching_table_index], $src_keys[$matching_table_index], $src_link);
311 $all_keys_match = 1;
312 for ($a = 0; $a < sizeof($trg_keys[$matching_table_index]); $a++) {
313 if (isset($trg_keys[$matching_table_index][$a])) {
314 if (! (in_array($trg_keys[$matching_table_index][$a], $src_keys[$matching_table_index]))) {
315 $all_keys_match = 0;
319 if (! ($all_keys_match)) {
320 if (isset($target_key_values)) {
321 $delete_array[$matching_table_index] = $target_key_values;
324 if (isset($trg_keys[$matching_table_index])) {
325 if ((sizeof($trg_keys[$matching_table_index]) == 1) && $all_keys_match) {
326 $row = 0;
327 if (isset($target_key_values)) {
328 for ($i = 0; $i < sizeof($target_key_values); $i++) {
329 if (! (in_array($target_key_values[$i], $source_key_values))) {
330 $delete_array[$matching_table_index][$row] = $target_key_values[$i];
331 $row++;
335 } elseif ((sizeof($trg_keys[$matching_table_index]) > 1) && $all_keys_match) {
336 $row = 0;
337 if (isset($target_key_values)) {
338 for ($i = 0; $i < sizeof($target_key_values); $i++) {
339 $is_present = false;
340 for ($j = 0; $j < sizeof($source_key_values) && ($is_present == false) ; $j++) {
341 $check = true;
342 for ($k = 0; $k < sizeof($trg_keys[$matching_table_index]); $k++) {
343 if ($target_key_values[$i][$trg_keys[$matching_table_index][$k]] != $source_key_values[$j][$trg_keys[$matching_table_index][$k]]) {
344 $check = false;
347 if ($check) {
348 $is_present = true;
351 if (! ($is_present)) {
352 for ($l = 0; $l < sizeof($trg_keys[$matching_table_index]); $l++) {
353 $delete_array[$matching_table_index][$row][$trg_keys[$matching_table_index][$l]] = $target_key_values[$i][$trg_keys[$matching_table_index][$l]];
355 $row++;
364 * PMA_dataDiffInUncommonTables() finds the data difference in $source_tables_uncommon
366 * @param array $source_tables_uncommon table names that are in source db and not in target db
367 * @param string $src_db name of source database
368 * @param mixed $src_link connection established with source server
369 * @param int $index index of a table from $matching_table array
370 * @param array $row_count number of rows
372 function PMA_dataDiffInUncommonTables($source_tables_uncommon, $src_db, $src_link, $index, &$row_count)
374 $query = "SELECT COUNT(*) FROM " . PMA_backquote($src_db) . "." . PMA_backquote($source_tables_uncommon[$index]);
375 $rows = PMA_DBI_fetch_result($query, null, null, $src_link);
376 $row_count[$index] = $rows[0];
380 * PMA_updateTargetTables() sets the updated field values to target table rows using $update_array[$matching_table_index]
383 * @param array $table Matching tables' names
384 * @param array $update_array A three dimensional array containing field
385 * value updates required for each matching table
386 * @param string $src_db Name of source database
387 * @param string $trg_db Name of target database
388 * @param mixed $trg_link Connection established with target server
389 * @param int $matching_table_index index of matching table in matching_table_array
390 * @param array $matching_table_keys
391 * @param boolean $display
393 function PMA_updateTargetTables($table, $update_array, $src_db, $trg_db, $trg_link, $matching_table_index, $matching_table_keys, $display)
395 if (isset($update_array[$matching_table_index])) {
396 if (sizeof($update_array[$matching_table_index])) {
398 for ($update_row = 0; $update_row < sizeof($update_array[$matching_table_index]); $update_row++) {
400 if (isset($update_array[$matching_table_index][$update_row])) {
401 $update_fields_num = sizeof($update_array[$matching_table_index][$update_row])-sizeof($matching_table_keys[$matching_table_index]);
402 if ($update_fields_num > 0) {
403 $query = "UPDATE " . PMA_backquote($trg_db) . "." .PMA_backquote($table[$matching_table_index]) . " SET ";
405 for ($update_field = 0; $update_field < $update_fields_num; $update_field = $update_field+2) {
406 if (isset($update_array[$matching_table_index][$update_row][$update_field]) && isset($update_array[$matching_table_index][$update_row][$update_field+1])) {
407 $query .= $update_array[$matching_table_index][$update_row][$update_field] . "='" . $update_array[$matching_table_index][$update_row][$update_field+1] . "'";
409 if ($update_field < ($update_fields_num - 2)) {
410 $query .= ", ";
413 $query .= " WHERE ";
414 if (isset($matching_table_keys[$matching_table_index])) {
415 for ($key = 0; $key < sizeof($matching_table_keys[$matching_table_index]); $key++)
417 if (isset($matching_table_keys[$matching_table_index][$key])) {
418 $query .= $matching_table_keys[$matching_table_index][$key] . "='" . $update_array[$matching_table_index][$update_row][$matching_table_keys[$matching_table_index][$key]] . "'";
420 if ($key < (sizeof($matching_table_keys[$matching_table_index]) - 1)) {
421 $query .= " AND ";
425 $query .= ';';
426 if ($display == true) {
427 echo "<p>" . $query . "</p>";
429 PMA_DBI_try_query($query, $trg_link, 0);
438 * PMA_insertIntoTargetTable() inserts missing rows in the target table using $array_insert[$matching_table_index]
440 * @todo this function uses undefined variables and is possibly broken: $matching_tables,
441 * $matching_tables_fields, $remove_indexes_array, $matching_table_keys
443 * @param array $matching_table matching table names
444 * @param string $src_db name of source database
445 * @param string $trg_db name of target database
446 * @param mixed $src_link connection established with source server
447 * @param mixed $trg_link connection established with target server
448 * @param array $table_fields field names of a table
449 * @param array &$array_insert
450 * @param int $matching_table_index index of matching table in matching_table_array
451 * @param array $matching_tables_keys field names that are keys in the matching table
452 * @param array $source_columns source column information
453 * @param array &$add_column_array column names that are to be added in target table
454 * @param array $criteria criteria like type, null, collation, default etc
455 * @param array $target_tables_keys field names that are keys in the target table
456 * @param array $uncommon_tables table names that are present in source db but not in targt db
457 * @param array &$uncommon_tables_fields field names of the uncommon tables
458 * @param array $uncommon_cols column names that are present in target table and not in source table
459 * @param array &$alter_str_array column names that are to be altered
460 * @param array &$source_indexes column names on which indexes are made in source table
461 * @param array &$target_indexes column names on which indexes are made in target table
462 * @param array &$add_indexes_array column names on which index is to be added in target table
463 * @param array &$alter_indexes_array column names whose indexes are to be altered. Only index name and uniqueness of an index can be changed
464 * @param array &$delete_array rows that are to be deleted
465 * @param array &$update_array rows that are to be updated in target
466 * @param bool $display
468 function PMA_insertIntoTargetTable($matching_table, $src_db, $trg_db, $src_link, $trg_link, $table_fields, &$array_insert, $matching_table_index,
469 $matching_tables_keys, $source_columns, &$add_column_array, $criteria, $target_tables_keys, $uncommon_tables, &$uncommon_tables_fields, $uncommon_cols,
470 &$alter_str_array,&$source_indexes, &$target_indexes, &$add_indexes_array, &$alter_indexes_array, &$delete_array, &$update_array, $display)
472 if (isset($array_insert[$matching_table_index])) {
473 if (sizeof($array_insert[$matching_table_index])) {
474 for ($insert_row = 0; $insert_row< sizeof($array_insert[$matching_table_index]); $insert_row++) {
475 if (isset($array_insert[$matching_table_index][$insert_row][$matching_tables_keys[$matching_table_index][0]])) {
477 $select_query = "SELECT * FROM " . PMA_backquote($src_db) . "." . PMA_backquote($matching_table[$matching_table_index]) . " WHERE ";
478 for ($i = 0; $i < sizeof($matching_tables_keys[$matching_table_index]); $i++) {
479 $select_query .= $matching_tables_keys[$matching_table_index][$i] . "='";
480 $select_query .= $array_insert[$matching_table_index][$insert_row][$matching_tables_keys[$matching_table_index][$i]] . "'" ;
482 if ($i < (sizeof($matching_tables_keys[$matching_table_index]) - 1)) {
483 $select_query.= " AND ";
486 $select_query .= "; ";
487 $result = PMA_DBI_fetch_result ($select_query, null, null, $src_link);
488 $insert_query = "INSERT INTO " . PMA_backquote($trg_db) . "." . PMA_backquote($matching_table[$matching_table_index]) ." (";
490 for ($field_index = 0; $field_index < sizeof($table_fields[$matching_table_index]); $field_index++)
492 $insert_query .= $table_fields[$matching_table_index][$field_index];
494 $is_fk_query = "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '" . $trg_db ."'
495 AND TABLE_NAME = '" . $matching_table[$matching_table_index]. "'AND COLUMN_NAME = '" .
496 $table_fields[$matching_table_index][$field_index] . "' AND TABLE_NAME <> REFERENCED_TABLE_NAME;" ;
498 $is_fk_result = PMA_DBI_fetch_result($is_fk_query, null, null, $trg_link);
499 if (sizeof($is_fk_result) > 0) {
500 for ($j = 0; $j < sizeof($is_fk_result); $j++)
502 $table_index = array_keys($matching_table, $is_fk_result[$j]['REFERENCED_TABLE_NAME']);
504 if (isset($alter_str_array[$table_index[0]])) {
505 PMA_alterTargetTableStructure($trg_db, $trg_link, $matching_tables, $source_columns, $alter_str_array, $matching_tables_fields,
506 $criteria, $matching_tables_keys, $target_tables_keys, $table_index[0], $display);
507 unset($alter_str_array[$table_index[0]]);
509 if (isset($uncommon_columns[$table_index[0]])) {
510 PMA_removeColumnsFromTargetTable($trg_db, $trg_link, $matching_tables, $uncommon_columns, $table_index[0], $display);
511 unset($uncommon_columns[$table_index[0]]);
513 if (isset($add_column_array[$table_index[0]])) {
514 PMA_findDeleteRowsFromTargetTables($delete_array, $matching_tables, $table_index[0], $target_tables_keys, $matching_tables_keys,
515 $trg_db, $trg_link, $src_db, $src_link);
517 if (isset($delete_array[$table_index[0]])) {
518 PMA_deleteFromTargetTable($trg_db, $trg_link, $matching_tables, $table_index[0], $target_tables_keys, $delete_array, $display);
519 unset($delete_array[$table_index[0]]);
521 PMA_addColumnsInTargetTable($src_db, $trg_db, $src_link, $trg_link, $matching_tables, $source_columns, $add_column_array,
522 $matching_tables_fields, $criteria, $matching_tables_keys, $target_tables_keys, $uncommon_tables,$uncommon_tables_fields,
523 $table_index[0], $uncommon_cols, $display);
524 unset($add_column_array[$table_index[0]]);
526 if (isset($add_indexes_array[$table_index[0]]) || isset($remove_indexes_array[$table_index[0]])
527 || isset($alter_indexes_array[$table_index[0]])) {
528 PMA_applyIndexesDiff ($trg_db, $trg_link, $matching_tables, $source_indexes, $target_indexes, $add_indexes_array, $alter_indexes_array,
529 $remove_indexes_array, $table_index[0], $display);
531 unset($add_indexes_array[$table_index[0]]);
532 unset($alter_indexes_array[$table_index[0]]);
533 unset($remove_indexes_array[$table_index[0]]);
535 if (isset($update_array[$table_index[0]])) {
536 PMA_updateTargetTables($matching_tables, $update_array, $src_db, $trg_db, $trg_link, $table_index[0], $matching_table_keys,
537 $display);
538 unset($update_array[$table_index[0]]);
540 if (isset($array_insert[$table_index[0]])) {
541 PMA_insertIntoTargetTable($matching_table, $src_db, $trg_db, $src_link, $trg_link, $table_fields, $array_insert,
542 $table_index[0], $matching_tables_keys, $source_columns, $add_column_array, $criteria, $target_tables_keys, $uncommon_tables,
543 $uncommon_tables_fields, $uncommon_cols, $alter_str_array, $source_indexes, $target_indexes, $add_indexes_array,
544 $alter_indexes_array, $delete_array, $update_array, $display);
545 unset($array_insert[$table_index[0]]);
549 if ($field_index < sizeof($table_fields[$matching_table_index])-1) {
550 $insert_query .= ", ";
553 $insert_query .= ") VALUES(";
554 if (sizeof($table_fields[$matching_table_index]) == 1) {
555 $insert_query .= "'" . PMA_sqlAddSlashes($result[0]) . "'";
556 } else {
557 for ($field_index = 0; $field_index < sizeof($table_fields[$matching_table_index]); $field_index++) {
558 if (isset($result[0][$table_fields[$matching_table_index][$field_index]])) {
559 $insert_query .= "'" . PMA_sqlAddSlashes($result[0][$table_fields[$matching_table_index][$field_index]]) . "'";
560 } else {
561 $insert_query .= "'NULL'";
563 if ($field_index < (sizeof($table_fields[$matching_table_index])) - 1) {
564 $insert_query .= " ," ;
568 $insert_query .= ");";
569 if ($display == true) {
570 PMA_displayQuery($insert_query);
572 PMA_DBI_try_query($insert_query, $trg_link, 0);
580 * PMA_createTargetTables() Create the missing table $uncommon_table in target database
582 * @param string $src_db name of source database
583 * @param string $trg_db name of target database
584 * @param mixed $src_link connection established with source server
585 * @param mixed $trg_link connection established with target server
586 * @param array &$uncommon_tables names of tables present in source but not in target
587 * @param int $table_index index of table in $uncommon_tables array
588 * @param array &$uncommon_tables_fields field names of the uncommon table
589 * @param bool $display
591 function PMA_createTargetTables($src_db, $trg_db, $src_link, $trg_link, &$uncommon_tables, $table_index, &$uncommon_tables_fields, $display)
593 if (isset($uncommon_tables[$table_index])) {
594 $fields_result = PMA_DBI_get_columns($src_db, $uncommon_tables[$table_index], true, $src_link);
595 $fields = array();
596 foreach ($fields_result as $each_field) {
597 $field_name = $each_field['Field'];
598 $fields[] = $field_name;
600 $uncommon_tables_fields[$table_index] = $fields;
602 $Create_Query = PMA_DBI_fetch_value("SHOW CREATE TABLE " . PMA_backquote($src_db) . '.' . PMA_backquote($uncommon_tables[$table_index]), 0, 1, $src_link);
604 // Replace the src table name with a `dbname`.`tablename`
605 $Create_Table_Query = preg_replace('/' . preg_quote(PMA_backquote($uncommon_tables[$table_index]), '/') . '/',
606 PMA_backquote($trg_db) . '.' .PMA_backquote($uncommon_tables[$table_index]),
607 $Create_Query,
608 $limit = 1
611 $is_fk_query = "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '" . $src_db . "'
612 AND TABLE_NAME = '" . $uncommon_tables[$table_index] . "' AND TABLE_NAME <> REFERENCED_TABLE_NAME;" ;
614 $is_fk_result = PMA_DBI_fetch_result($is_fk_query, null, null, $src_link);
615 if (sizeof($is_fk_result) > 0) {
616 for ($j = 0; $j < sizeof($is_fk_result); $j++)
618 if (in_array($is_fk_result[$j]['REFERENCED_TABLE_NAME'], $uncommon_tables)) {
619 $table_index = array_keys($uncommon_tables, $is_fk_result[$j]['REFERENCED_TABLE_NAME']);
620 PMA_createTargetTables($src_db, $trg_db, $trg_link, $src_link, $uncommon_tables, $table_index[0], $uncommon_tables_fields, $display);
621 unset($uncommon_tables[$table_index[0]]);
625 $Create_Table_Query .= ';';
626 if ($display == true) {
627 echo '<p>' . $Create_Table_Query . '</p>';
629 PMA_DBI_try_query($Create_Table_Query, $trg_link, 0);
633 * PMA_populateTargetTables() inserts data into uncommon tables after they have been created
635 * @param string $src_db name of source database
636 * @param string $trg_db name of target database
637 * @param mixed $src_link connection established with source server
638 * @param mixed $trg_link connection established with target server
639 * @param array $uncommon_tables uncommon table names (table names that are present in source but not in target db)
640 * @param int $table_index index of table in matching_table_array
641 * @param array $uncommon_tables_fields field names of the uncommon table
642 * @param bool $display
644 * @todo This turns NULL values into '' (empty string)
646 function PMA_populateTargetTables($src_db, $trg_db, $src_link, $trg_link, $uncommon_tables, $table_index, $uncommon_tables_fields, $display)
648 $display = false; // todo: maybe display some of the queries if they are not too numerous
649 $unbuffered_result = PMA_DBI_try_query('SELECT * FROM ' . PMA_backquote($src_db) . '.' . PMA_backquote($uncommon_tables[$table_index]), $src_link, PMA_DBI_QUERY_UNBUFFERED);
650 if (false !== $unbuffered_result) {
651 $insert_query = 'INSERT INTO ' . PMA_backquote($trg_db) . '.' .PMA_backquote($uncommon_tables[$table_index]) . ' VALUES';
652 while ($one_row = PMA_DBI_fetch_row($unbuffered_result)) {
653 $insert_query .= '(';
654 $key_of_last_value = count($one_row) - 1;
655 foreach ($one_row as $key => $value) {
656 $insert_query .= "'" . PMA_sqlAddSlashes($value) . "'";
657 if ($key < $key_of_last_value) {
658 $insert_query .= ",";
661 $insert_query .= '),';
663 $insert_query = substr($insert_query, 0, -1);
664 $insert_query .= ';';
665 if ($display == true) {
666 PMA_displayQuery($insert_query);
668 PMA_DBI_try_query($insert_query, $trg_link, 0);
673 * PMA_deleteFromTargetTable() delete rows from target table
675 * @param string $trg_db name of target database
676 * @param mixed $trg_link connection established with target server
677 * @param array $matching_tables matching table names
678 * @param int $table_index index of table in matching_table_array
679 * @param array $target_tables_keys primary key names of the target tables
680 * @param array $delete_array key values of rows that are to be deleted
681 * @param bool $display
683 function PMA_deleteFromTargetTable($trg_db, $trg_link, $matching_tables, $table_index, $target_tables_keys, $delete_array, $display)
685 for ($i = 0; $i < sizeof($delete_array[$table_index]); $i++) {
686 if (isset($target_tables_keys[$table_index])) {
687 $delete_query = 'DELETE FROM ' . PMA_backquote($trg_db) . '.' .PMA_backquote($matching_tables[$table_index]) . ' WHERE ';
688 for ($y = 0; $y < sizeof($target_tables_keys[$table_index]); $y++) {
689 $delete_query .= $target_tables_keys[$table_index][$y] . " = '";
691 if (sizeof($target_tables_keys[$table_index]) == 1) {
692 $delete_query .= $delete_array[$table_index][$i] . "'";
693 } elseif (sizeof($target_tables_keys[$table_index]) > 1) {
694 $delete_query .= $delete_array[$table_index][$i][$target_tables_keys[$table_index][$y]] . "'";
696 if ($y < (sizeof($target_tables_keys[$table_index]) - 1)) {
697 $delete_query .= ' AND ';
699 $pk_query = "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = '" . $trg_db . "'
700 AND REFERENCED_TABLE_NAME = '" . $matching_tables[$table_index]."' AND REFERENCED_COLUMN_NAME = '"
701 . $target_tables_keys[$table_index][$y] . "' AND TABLE_NAME <> REFERENCED_TABLE_NAME;";
703 $pk_query_result = PMA_DBI_fetch_result($pk_query, null, null, $trg_link);
704 $result_size = sizeof($pk_query_result);
706 if ($result_size > 0) {
707 for ($b = 0; $b < $result_size; $b++) {
708 $drop_pk_query = "DELETE FROM " . PMA_backquote($pk_query_result[$b]['TABLE_SCHEMA']) . "." . PMA_backquote($pk_query_result[$b]['TABLE_NAME']) . " WHERE " . $pk_query_result[$b]['COLUMN_NAME'] . " = " . $target_tables_keys[$table_index][$y] . ";";
709 PMA_DBI_try_query($drop_pk_query, $trg_link, 0);
714 if ($display == true) {
715 echo '<p>' . $delete_query . '</p>';
717 PMA_DBI_try_query($delete_query, $trg_link, 0);
722 * PMA_structureDiffInTables() Gets all the column information for source and target table.
723 * Compare columns on their names.
724 * If column exists in target then compare Type, Null, Collation, Key, Default and Comment for that column.
725 * If column does not exist in target table then it is placed in $add_column_array.
726 * If column exists in target table but criteria is different then it is palced in $alter_str_array.
727 * If column does not exist in source table but is present in target table then it is placed in $uncommon_columns.
728 * Keys for all the source tables that have a corresponding target table are placed in $matching_tables_keys.
729 * Keys for all the target tables that have a corresponding source table are placed in $target_tables_keys.
731 * @param string $src_db name of source database
732 * @param string $trg_db name of target database
733 * @param mixed $src_link connection established with source server
734 * @param mixed $trg_link connection established with target server
735 * @param array $matching_tables names of matching tables
736 * @param array &$source_columns columns information of the source tables
737 * @param array &$target_columns columns information of the target tables
738 * @param array &$alter_str_array three dimensional associative array first index being the matching table index, second index being column name for which target
739 * column have some criteria different and third index containing the criteria which is different.
740 * @param array &$add_column_array two dimensional associative array, first index of the array contain the matching table number and second index contain the
741 * column name which is to be added in the target table
742 * @param array &$uncommon_columns columns that are present in the target table but not in the source table
743 * @param array $criteria criteria which are to be checked for field that is present in source table and target table
744 * @param array &$target_tables_keys field names which is key in the target table
745 * @param int $matching_table_index number of the matching table
747 function PMA_structureDiffInTables($src_db, $trg_db, $src_link, $trg_link, $matching_tables, &$source_columns, &$target_columns, &$alter_str_array,
748 &$add_column_array, &$uncommon_columns, $criteria, &$target_tables_keys, $matching_table_index)
750 //Gets column information for source and target table
751 $source_columns[$matching_table_index] = PMA_DBI_get_columns_full($src_db, $matching_tables[$matching_table_index], null, $src_link);
752 $target_columns[$matching_table_index] = PMA_DBI_get_columns_full($trg_db, $matching_tables[$matching_table_index], null, $trg_link);
753 foreach ($source_columns[$matching_table_index] as $column_name => $each_column) {
754 if (isset($target_columns[$matching_table_index][$column_name]['Field'])) {
755 //If column exists in target table then matches criteria like type, null, collation, key, default, comment of the column
756 for ($i = 0; $i < sizeof($criteria); $i++) {
757 if ($source_columns[$matching_table_index][$column_name][$criteria[$i]] != $target_columns[$matching_table_index][$column_name][$criteria[$i]]) {
758 if (($criteria[$i] == 'Default') && ($source_columns[$matching_table_index][$column_name][$criteria[$i]] == '' )) {
759 $alter_str_array[$matching_table_index][$column_name][$criteria[$i]] = 'None';
760 } else {
761 if (! (($criteria[$i] == 'Key') && (($source_columns[$matching_table_index][$column_name][$criteria[$i]] == 'MUL')
762 || ($target_columns[$matching_table_index][$column_name][$criteria[$i]] == 'MUL')
763 || ($source_columns[$matching_table_index][$column_name][$criteria[$i]] == 'UNI')
764 || ($target_columns[$matching_table_index][$column_name][$criteria[$i]] == 'UNI')))) {
765 $alter_str_array[$matching_table_index][$column_name][$criteria[$i]] = $source_columns[$matching_table_index][$column_name][$criteria[$i]];
770 } else {
771 $add_column_array[$matching_table_index][$column_name]= $column_name;
774 //Finds column names that are present in target table but not in source table
775 foreach ($target_columns[$matching_table_index] as $fld_name => $each_column) {
776 if (! (isset($source_columns[$matching_table_index][$fld_name]['Field']))) {
777 $fields_uncommon[] = $fld_name;
779 if ($target_columns[$matching_table_index][$fld_name]['Key'] == 'PRI') {
780 $keys[] = $fld_name;
783 if (isset($fields_uncommon)) {
784 $uncommon_columns[$matching_table_index] = $fields_uncommon;
786 if (isset($keys)) {
787 $target_tables_keys[$matching_table_index] = $keys;
791 * PMA_addColumnsInTargetTable() adds column that are present in source table but not in target table
793 * @param string $src_db name of source database
794 * @param string $trg_db name of target database
795 * @param mixed $src_link connection established with source server
796 * @param mixed $trg_link connection established with target server
797 * @param array $matching_tables names of matching tables
798 * @param array $source_columns columns information of the source tables
799 * @param array &$add_column_array the names of the column(field) that are to be added in the target
800 * @param array $matching_tables_fields
801 * @param array $criteria criteria
802 * @param array $matching_tables_keys field names which is key in the source table
803 * @param array $target_tables_keys field names which is key in the target table
804 * @param array $uncommon_tables table names that are present in source db and not in target db
805 * @param array &$uncommon_tables_fields names of the fields of the uncommon tables
806 * @param int $table_counter number of the matching table
807 * @param array $uncommon_cols
808 * @param bool $display
810 function PMA_addColumnsInTargetTable($src_db, $trg_db, $src_link, $trg_link, $matching_tables, $source_columns, &$add_column_array, $matching_tables_fields,
811 $criteria, $matching_tables_keys, $target_tables_keys, $uncommon_tables, &$uncommon_tables_fields, $table_counter, $uncommon_cols, $display)
813 for ($i = 0; $i < sizeof($matching_tables_fields[$table_counter]); $i++) {
814 if (isset($add_column_array[$table_counter][$matching_tables_fields[$table_counter][$i]])) {
815 $query = "ALTER TABLE " . PMA_backquote($trg_db) . '.' . PMA_backquote($matching_tables[$table_counter]). " ADD COLUMN " .
816 $add_column_array[$table_counter][$matching_tables_fields[$table_counter][$i]] . " " . $source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Type'];
818 if ($source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Null'] == 'NO') {
819 $query .= ' Not Null ';
820 } elseif ($source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Null'] == 'YES') {
821 $query .= ' Null ';
823 if ($source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Collation'] != '') {
824 $query .= ' COLLATE ' . $source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Collation'];
826 if ($source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Default'] != '') {
827 $query .= " DEFAULT " . $source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Default'];
829 if ($source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Comment'] != '') {
830 $query .= " COMMENT " . $source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Comment'];
832 if ($source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Key'] == 'PRI' ) {
833 $trg_key_size = sizeof($target_tables_keys[$table_counter]);
834 if ($trg_key_size) {
835 $check = true;
836 for ($a = 0; ($a < $trg_key_size) && ($check); $a++) {
837 if (! (in_array($target_tables_keys[$table_counter], $uncommon_cols))) {
838 $check = false;
841 if (! $check) {
842 $query .= " ,DROP PRIMARY KEY " ;
845 $query .= " , ADD PRIMARY KEY (";
846 for ($t = 0; $t < sizeof($matching_tables_keys[$table_counter]); $t++) {
847 $query .= $matching_tables_keys[$table_counter][$t];
848 if ($t < (sizeof($matching_tables_keys[$table_counter]) - 1)) {
849 $query .= " , " ;
852 $query .= ")";
855 $query .= ";";
856 if ($display == true) {
857 echo '<p>' . $query . '</p>';
859 PMA_DBI_try_query($query, $trg_link, 0);
861 //Checks if column to be added is a foreign key or not
862 $is_fk_query = "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '" . $trg_db . "' AND TABLE_NAME = '"
863 . $matching_tables[$table_counter] . "' AND COLUMN_NAME ='" . $add_column_array[$table_counter][$matching_tables_fields[$table_counter][$i]] .
864 "' AND TABLE_NAME <> REFERENCED_TABLE_NAME;";
866 $is_fk_result = PMA_DBI_fetch_result($is_fk_query, null, null, $src_link);
868 //If column is a foreign key then it is checked that referenced table exist in target db. If referenced table does not exist in target db then
869 //it is created first.
870 if (isset($is_fk_result)) {
871 if (in_array($is_fk_result[0]['REFERENCED_TABLE_NAME'], $uncommon_tables)) {
872 $table_index = array_keys($uncommon_tables, $is_fk_result[0]['REFERENCED_TABLE_NAME']);
873 PMA_checkForeignKeys($src_db, $src_link, $trg_db, $trg_link, $is_fk_result[0]['REFERENCED_TABLE_NAME'], $uncommon_tables, $uncommon_tables_fields, $display);
874 PMA_createTargetTables($src_db, $trg_db, $trg_link, $src_link, $uncommon_tables, $table_index[0], $uncommon_tables_fields, $display);
875 unset($uncommon_tables[$table_index[0]]);
877 $fk_query = "ALTER TABLE " . PMA_backquote($trg_db) . '.' . PMA_backquote($matching_tables[$table_counter]) .
878 "ADD CONSTRAINT FOREIGN KEY " . $add_column_array[$table_counter][$matching_tables_fields[$table_counter][$i]] . "
879 (" . $add_column_array[$table_counter][$matching_tables_fields[$table_counter][$i]] . ") REFERENCES " . PMA_backquote($trg_db) .
880 '.' . PMA_backquote($is_fk_result[0]['REFERENCED_TABLE_NAME']) . " (" . $is_fk_result[0]['REFERENCED_COLUMN_NAME'] . ");";
882 PMA_DBI_try_query($fk_query, $trg_link, null);
888 * PMA_checkForeignKeys() checks if the referenced table have foreign keys.
889 * uses PMA_createTargetTables()
891 * @param string $src_db name of source database
892 * @param mixed $src_link connection established with source server
893 * @param string $trg_db name of target database
894 * @param mixed $trg_link connection established with target server
895 * @param string $referenced_table table whose column is a foreign key in another table
896 * @param array &$uncommon_tables names that are uncommon
897 * @param array &$uncommon_tables_fields field names of the uncommon table
898 * @param bool $display
900 function PMA_checkForeignKeys($src_db, $src_link, $trg_db, $trg_link, $referenced_table, &$uncommon_tables, &$uncommon_tables_fields, $display)
902 $is_fk_query = "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '" . $src_db . "'
903 AND TABLE_NAME = '" . $referenced_table . "' AND TABLE_NAME <> REFERENCED_TABLE_NAME;";
905 $is_fk_result = PMA_DBI_fetch_result($is_fk_query, null, null, $src_link);
906 if (sizeof($is_fk_result) > 0) {
907 for ($j = 0; $j < sizeof($is_fk_result); $j++) {
908 if (in_array($is_fk_result[$j]['REFERENCED_TABLE_NAME'], $uncommon_tables)) {
909 $table_index = array_keys($uncommon_tables, $is_fk_result[$j]['REFERENCED_TABLE_NAME']);
910 PMA_checkForeignKeys($src_db, $src_link, $trg_db, $trg_link, $is_fk_result[$j]['REFERENCED_TABLE_NAME'], $uncommon_tables,
911 $uncommon_tables_fields, $display);
912 PMA_createTargetTables($src_db, $trg_db, $trg_link, $src_link, $uncommon_tables, $table_index[0], $uncommon_tables_fields, $display);
913 unset($uncommon_tables[$table_index[0]]);
919 * PMA_alterTargetTableStructure() alters structure of the target table using $alter_str_array
921 * @param string $trg_db name of target database
922 * @param mixed $trg_link connection established with target server
923 * @param array $matching_tables names of matching tables
924 * @param array &$source_columns columns information of the source table
925 * @param array &$alter_str_array column name and criteria which is to be altered for the targert table
926 * @param array $matching_tables_fields name of the fields for the matching table
927 * @param array $criteria criteria
928 * @param array &$matching_tables_keys field names which is key in the source table
929 * @param array &$target_tables_keys field names which is key in the target table
930 * @param int $matching_table_index number of the matching table
931 * @param bool $display
933 function PMA_alterTargetTableStructure($trg_db, $trg_link, $matching_tables, &$source_columns, &$alter_str_array, $matching_tables_fields, $criteria,
934 &$matching_tables_keys, &$target_tables_keys, $matching_table_index, $display)
936 $check = true;
937 $sql_query = '';
938 $found = false;
940 //Checks if the criteria to be altered is primary key
941 for ($v = 0; $v < sizeof($matching_tables_fields[$matching_table_index]); $v++) {
942 if (isset($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$v]]['Key'])) {
943 if ($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$v]]['Key'] == 'PRI' ) {
944 $check = false;
949 $pri_query = null;
950 if (! $check) {
951 $pri_query = "ALTER TABLE " . PMA_backquote($trg_db) . '.' . PMA_backquote($matching_tables[$matching_table_index]);
952 if (sizeof($target_tables_keys[$matching_table_index]) > 0) {
953 $pri_query .= " DROP PRIMARY KEY ," ;
955 $pri_query .= " ADD PRIMARY KEY (";
956 for ($z = 0; $z < sizeof($matching_tables_keys[$matching_table_index]); $z++) {
957 $pri_query .= $matching_tables_keys[$matching_table_index][$z];
958 if ($z < (sizeof($matching_tables_keys[$matching_table_index]) - 1)) {
959 $pri_query .= " , " ;
962 $pri_query .= ");";
965 if (isset($pri_query)) {
966 if ($display == true) {
967 echo '<p>' . $pri_query . '</p>';
969 PMA_DBI_try_query($pri_query, $trg_link, 0);
971 for ($t = 0; $t < sizeof($matching_tables_fields[$matching_table_index]); $t++) {
972 if ((isset($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]])) && (sizeof($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]]) > 0)) {
973 $sql_query = 'ALTER TABLE ' . PMA_backquote($trg_db) . '.' . PMA_backquote($matching_tables[$matching_table_index]) . ' MODIFY ' .
974 $matching_tables_fields[$matching_table_index][$t] . ' ' . $source_columns[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]]['Type'];
975 $found = false;
976 for ($i = 0; $i < sizeof($criteria); $i++)
978 if (isset($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]]) && $criteria[$i] != 'Key') {
979 $found = true;
980 if (($criteria[$i] == 'Type') && (! isset($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i+1]]))) {
981 if ($source_columns[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i + 1]] == 'NO') {
982 $sql_query .= " Not Null" ;
983 } elseif ($source_columns[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i + 1]] == 'YES') {
984 $sql_query .= " Null" ;
987 if (($criteria[$i] == 'Null') && ( $alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]] == 'NO')) {
988 $sql_query .= " Not Null " ;
989 } elseif (($criteria[$i] == 'Null') && ($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]] == 'YES')) {
990 $sql_query .= " Null " ;
992 if ($criteria[$i] == 'Collation') {
993 if ( !(isset($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[2]]))) {
994 $sql_query .= " Not Null " ;
996 $sql_query .= " COLLATE " . $alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]] ;
998 if (($criteria[$i] == 'Default') && ($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]] == 'None')) {
999 if ( !(isset($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[2]]))) {
1000 $sql_query .= " Not Null " ;
1002 } elseif ($criteria[$i] == 'Default') {
1003 if (! (isset($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[2]]))) {
1004 $sql_query .= " Not Null " ;
1006 if (is_string($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]])) {
1007 if ($source_columns[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]]['Type'] != 'timestamp') {
1008 $sql_query .= " DEFAULT '" . $alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]] . "'";
1009 } elseif ($source_columns[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]]['Type'] == 'timestamp') {
1010 $sql_query .= " DEFAULT " . $alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]];
1012 } elseif (is_numeric($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]])) {
1013 $sql_query .= " DEFAULT " . $alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]];
1016 if ($criteria[$i] == 'Comment') {
1017 if ( !(isset($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[2]]))) {
1018 $sql_query .= " Not Null " ;
1020 $sql_query .= " COMMENT '" . $alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]] . "'" ;
1025 $sql_query .= ";";
1026 if ($found) {
1027 if ($display == true) {
1028 echo '<p>' . $sql_query . '</p>';
1030 PMA_DBI_try_query($sql_query, $trg_link, 0);
1033 $check = false;
1034 $query = "ALTER TABLE " . PMA_backquote($trg_db) . '.' . PMA_backquote($matching_tables[$matching_table_index]);
1035 for ($p = 0; $p < sizeof($matching_tables_keys[$matching_table_index]); $p++) {
1036 if ((isset($alter_str_array[$matching_table_index][$matching_tables_keys[$matching_table_index][$p]]['Key']))) {
1037 $check = true;
1038 $query .= ' MODIFY ' . $matching_tables_keys[$matching_table_index][$p] . ' '
1039 . $source_columns[$matching_table_index][$matching_tables_fields[$matching_table_index][$p]]['Type'] . ' Not Null ';
1040 if ($p < (sizeof($matching_tables_keys[$matching_table_index]) - 1)) {
1041 $query .= ', ';
1045 $query .= ';';
1046 if ($check) {
1047 if ($display == true) {
1048 echo '<p>' . $query . '</p>';
1050 PMA_DBI_try_query($query, $trg_link, 0);
1055 * PMA_removeColumnsFromTargetTable() removes the columns which are present in target table but not in source table.
1057 * @param string $trg_db name of target database
1058 * @param mixed $trg_link connection established with target server
1059 * @param array $matching_tables names of matching tables
1060 * @param array $uncommon_columns array containing the names of the column which are to be dropped from the target table
1061 * @param int $table_counter index of the matching table as in $matchiing_tables array
1062 * @param bool $display
1064 function PMA_removeColumnsFromTargetTable($trg_db, $trg_link, $matching_tables, $uncommon_columns, $table_counter, $display)
1066 if (isset($uncommon_columns[$table_counter])) {
1067 $drop_query = "ALTER TABLE " . PMA_backquote($trg_db) . "." . PMA_backquote($matching_tables[$table_counter]);
1068 for ($a = 0; $a < sizeof($uncommon_columns[$table_counter]); $a++) {
1069 //Checks if column to be removed is a foreign key in any table
1070 $pk_query = "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = '" . $trg_db . "'
1071 AND REFERENCED_TABLE_NAME = '" . $matching_tables[$table_counter]."' AND REFERENCED_COLUMN_NAME = '"
1072 . $uncommon_columns[$table_counter][$a] . "' AND TABLE_NAME <> REFERENCED_TABLE_NAME;";
1074 $pk_query_result = PMA_DBI_fetch_result($pk_query, null, null, $trg_link);
1075 $result_size = sizeof($pk_query_result);
1077 if ($result_size > 0) {
1078 for ($b = 0; $b < $result_size; $b++) {
1079 $drop_pk_query = "ALTER TABLE " . PMA_backquote($pk_query_result[$b]['TABLE_SCHEMA']) . "." . PMA_backquote($pk_query_result[$b]['TABLE_NAME']) . "
1080 DROP FOREIGN KEY " . $pk_query_result[$b]['CONSTRAINT_NAME'] . ", DROP COLUMN " . $pk_query_result[$b]['COLUMN_NAME'] . ";";
1081 PMA_DBI_try_query($drop_pk_query, $trg_link, 0);
1084 $query = "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '" . $trg_db . "' AND TABLE_NAME = '"
1085 . $matching_tables[$table_counter]. "' AND COLUMN_NAME = '" . $uncommon_columns[$table_counter][$a] . "'
1086 AND TABLE_NAME <> REFERENCED_TABLE_NAME;";
1088 $result = PMA_DBI_fetch_result($query, null, null, $trg_link);
1090 if (sizeof($result) > 0) {
1091 $drop_query .= " DROP FOREIGN KEY " . $result[0]['CONSTRAINT_NAME'] . ",";
1093 $drop_query .= " DROP COLUMN " . $uncommon_columns[$table_counter][$a];
1094 if ($a < (sizeof($uncommon_columns[$table_counter]) - 1)) {
1095 $drop_query .= " , " ;
1098 $drop_query .= ";" ;
1100 if ($display == true) {
1101 echo '<p>' . $drop_query . '</p>';
1103 PMA_DBI_try_query($drop_query, $trg_link, 0);
1108 * PMA_indexesDiffInTables() compares the source table indexes with target table indexes and keep the indexes to be added in target table in $add_indexes_array
1109 * indexes to be altered in $alter_indexes_array and indexes to be removed from target table in $remove_indexes_array.
1110 * Only keyname and uniqueness characteristic of the indexes are altered.
1112 * @param string $src_db name of source database
1113 * @param string $trg_db name of target database
1114 * @param mixed $src_link connection established with source server
1115 * @param mixed $trg_link connection established with target server
1116 * @param array $matching_tables matching tables name
1117 * @param array &$source_indexes indexes of the source table
1118 * @param array &$target_indexes indexes of the target table
1119 * @param array &$add_indexes_array name of the column on which the index is to be added in the target table
1120 * @param array &$alter_indexes_array key name which needs to be altered
1121 * @param array &$remove_indexes_array key name of the index which is to be removed from the target table
1122 * @param int $table_counter number of the matching table
1124 function PMA_indexesDiffInTables($src_db, $trg_db, $src_link, $trg_link, $matching_tables, &$source_indexes, &$target_indexes, &$add_indexes_array,
1125 &$alter_indexes_array, &$remove_indexes_array, $table_counter)
1127 //Gets indexes information for source and target table
1128 $source_indexes[$table_counter] = PMA_get_table_indexes($src_db, $matching_tables[$table_counter],$src_link);
1129 $target_indexes[$table_counter] = PMA_get_table_indexes($trg_db, $matching_tables[$table_counter],$trg_link);
1130 for ($a = 0; $a < sizeof($source_indexes[$table_counter]); $a++) {
1131 $found = false;
1132 $z = 0;
1133 //Compares key name and non_unique characteristic of source indexes with target indexes
1135 * @todo compare the length of each sub part
1137 while (($z <= sizeof($target_indexes[$table_counter])) && ($found == false))
1139 if (isset($source_indexes[$table_counter][$a]) && isset($target_indexes[$table_counter][$z]) && $source_indexes[$table_counter][$a]['Key_name'] == $target_indexes[$table_counter][$z]['Key_name']) {
1140 $found = true;
1141 if (($source_indexes[$table_counter][$a]['Column_name'] != $target_indexes[$table_counter][$z]['Column_name']) || ($source_indexes[$table_counter][$a]['Non_unique'] != $target_indexes[$table_counter][$z]['Non_unique'])) {
1142 if (! (($source_indexes[$table_counter][$a]['Key_name'] == "PRIMARY") || ($target_indexes[$table_counter][$z]['Key_name'] == 'PRIMARY'))) {
1143 $alter_indexes_array[$table_counter][] = $source_indexes[$table_counter][$a]['Key_name'];
1147 $z++;
1149 if ($found === false) {
1150 if (! ($source_indexes[$table_counter][$a]['Key_name'] == 'PRIMARY')) {
1151 $add_indexes_array [$table_counter][] = $source_indexes[$table_counter][$a]['Column_name'];
1156 //Finds indexes that exist on target table but not on source table
1157 for ($b = 0; $b < sizeof($target_indexes[$table_counter]); $b++) {
1158 $found = false;
1159 $c = 0;
1160 while (($c <= sizeof($source_indexes[$table_counter])) && ($found == false))
1162 if ($target_indexes[$table_counter][$b]['Column_name'] == $source_indexes[$table_counter][$c]['Column_name']) {
1163 $found = true;
1165 $c++;
1167 if ($found === false) {
1168 $remove_indexes_array[$table_counter][] = $target_indexes[$table_counter][$b]['Key_name'];
1174 * PMA_applyIndexesDiff() create indexes, alters indexes and remove indexes.
1176 * @param string $trg_db name of target database
1177 * @param mixed $trg_link connection established with target server
1178 * @param array $matching_tables matching tables name
1179 * @param array $source_indexes indexes of the source table
1180 * @param array $target_indexes indexes of the target table
1181 * @param array $add_indexes_array column names on which indexes are to be created in target table
1182 * @param array $alter_indexes_array column names for which indexes are to be altered
1183 * @param array $remove_indexes_array key name of the indexes which are to be removed from the target table
1184 * @param int $table_counter number of the matching table
1185 * @param $display
1187 function PMA_applyIndexesDiff ($trg_db, $trg_link, $matching_tables, $source_indexes, $target_indexes, $add_indexes_array, $alter_indexes_array,
1188 $remove_indexes_array, $table_counter, $display)
1190 //Adds indexes on target table
1191 if (isset($add_indexes_array[$table_counter])) {
1192 $sql = "ALTER TABLE " . PMA_backquote($trg_db) . "." . PMA_backquote($matching_tables[$table_counter]) . " ADD" ;
1193 for ($a = 0; $a < sizeof($source_indexes[$table_counter]); $a++) {
1194 if (isset($add_indexes_array[$table_counter][$a])) {
1195 for ($b = 0; $b < sizeof($source_indexes[$table_counter]); $b++) {
1196 if ($source_indexes[$table_counter][$b]['Column_name'] == $add_indexes_array[$table_counter][$a]) {
1197 if ($source_indexes[$table_counter][$b]['Non_unique'] == '0') {
1198 $sql .= " UNIQUE ";
1200 $sql .= " INDEX " . $source_indexes[$table_counter][$b]['Key_name'] . " (" . $add_indexes_array[$table_counter][$a] . " );";
1201 if ($display == true) {
1202 echo '<p>' . $sql . '</p>';
1204 PMA_DBI_try_query($sql, $trg_link, 0);
1210 //Alter indexes of target table
1212 if (isset($alter_indexes_array[$table_counter])) {
1213 $query = "ALTER TABLE " . PMA_backquote($trg_db) . "." . PMA_backquote($matching_tables[$table_counter]);
1214 for ($a = 0; $a < sizeof($alter_indexes_array[$table_counter]); $a++) {
1215 if (isset($alter_indexes_array[$table_counter][$a])) {
1216 $query .= ' DROP INDEX ' . PMA_backquote($alter_indexes_array[$table_counter][$a]) . " , ADD ";
1217 $got_first_index_column = false;
1218 for ($z = 0; $z < sizeof($source_indexes[$table_counter]); $z++) {
1219 if ($source_indexes[$table_counter][$z]['Key_name'] == $alter_indexes_array[$table_counter][$a]) {
1220 if (! $got_first_index_column) {
1221 if ($source_indexes[$table_counter][$z]['Non_unique'] == '0') {
1222 $query .= " UNIQUE ";
1224 $query .= " INDEX " . PMA_backquote($source_indexes[$table_counter][$z]['Key_name']) . " (" . PMA_backquote($source_indexes[$table_counter][$z]['Column_name']);
1225 $got_first_index_column = true;
1226 } else {
1227 // another column for this index
1228 $query .= ', ' . PMA_backquote($source_indexes[$table_counter][$z]['Column_name']);
1232 $query .= " )";
1235 $query .= ';';
1236 if ($display == true) {
1237 echo '<p>' . $query . '</p>';
1239 PMA_DBI_try_query($query, $trg_link, 0);
1241 //Removes indexes from target table
1242 if (isset($remove_indexes_array[$table_counter])) {
1243 $drop_index_query = "ALTER TABLE " . PMA_backquote($trg_db) . "." . PMA_backquote($matching_tables[$table_counter]);
1244 for ($a = 0; $a < sizeof($target_indexes[$table_counter]); $a++) {
1245 if (isset($remove_indexes_array[$table_counter][$a])) {
1246 $drop_index_query .= " DROP INDEX " . $remove_indexes_array[$table_counter][$a];
1248 if ($a < (sizeof($remove_indexes_array[$table_counter]) - 1)) {
1249 $drop_index_query .= " , " ;
1252 $drop_index_query .= " ; " ;
1253 if ($display == true) {
1254 echo '<p>' . $drop_index_query . '</p>';
1256 PMA_DBI_try_query($drop_index_query, $trg_link, 0);
1261 * PMA_displayQuery() displays a query, taking the maximum display size
1262 * into account
1264 * @param string $query the query to display
1266 function PMA_displayQuery($query)
1268 if (strlen($query) > $GLOBALS['cfg']['MaxCharactersInDisplayedSQL']) {
1269 $query = substr($query, 0, $GLOBALS['cfg']['MaxCharactersInDisplayedSQL']) . '[...]';
1271 echo '<p>' . htmlspecialchars($query) . '</p>';
1275 * PMA_syncDisplayHeaderCompare() shows the header for source database
1277 * @param string $src_db source db name
1278 * @param string $trg_db target db name
1280 function PMA_syncDisplayHeaderCompare($src_db, $trg_db)
1282 echo '<fieldset style="padding:0"><div style="padding:1.5em; overflow:auto; height:220px">';
1284 echo '<table class="data">';
1285 echo '<tr>';
1286 echo '<th>' . __('Source database') . ': ' . htmlspecialchars($src_db) . '<br />(';
1287 if ('cur' == $_SESSION['src_type']) {
1288 echo __('Current server');
1289 } else {
1290 echo __('Remote server') . ' ' . htmlspecialchars($_SESSION['src_server']['host']);
1292 echo ')</th>';
1293 echo '<th>' . __('Difference') . '</th>';
1294 echo '<th>' . __('Target database') . ': '. htmlspecialchars($trg_db) . '<br />(';
1295 if ('cur' == $_SESSION['trg_type']) {
1296 echo __('Current server');
1297 } else {
1298 echo __('Remote server') . ' ' . htmlspecialchars($_SESSION['trg_server']['host']);
1300 echo ')</th>';
1301 echo '</tr>';
1305 * Prints table row
1307 * $rows contains following keys:
1308 * - src_table_name - source server table name
1309 * - dst_table_name - target server table name
1310 * - btn_type - 'M' or 'U'
1311 * - btn_structure - null or arguments for showDetails in server_synchronize.js (without img_obj and table_name):
1312 * i, update_size, insert_size, remove_size, insert_index, remove_index
1314 * @param array $rows
1316 function PMA_syncDisplayDataCompare($rows)
1318 global $pmaThemeImage;
1320 $odd_row = true;
1321 foreach ($rows as $row) {
1322 echo '<tr class=" ' . ($odd_row ? 'odd' : 'even') . '">';
1323 echo '<td>' . htmlspecialchars($row['src_table_name']) . '</td><td style="text-align:center">';
1324 if (isset($row['btn_structure']) && $row['btn_structure']) {
1325 // parameters: i, update_size, insert_size, remove_size, insert_index, remove_index
1326 $p = $row['btn_structure'];
1327 $p[0] = $row['btn_type'] . 'S' . $p[0];
1328 echo '<img class="icon struct_img" src="' . $pmaThemeImage . 'new_struct.png" width="16" height="16"
1329 alt="Structure" title="' . __('Click to select') . '" style="cursor:pointer" onclick="showDetails('
1330 . "'" . implode($p, "','") . "'"
1331 . ', this, ' . "'" . PMA_escapeJsString(htmlspecialchars($row['src_table_name'])) . "'" . ')" /> ';
1333 if (isset($row['btn_data']) && $row['btn_data']) {
1334 // parameters: i, update_size, insert_size, remove_size, insert_index, remove_index
1335 $p = $row['btn_data'];
1336 $p[0] = $row['btn_type'] . 'D' . $p[0];
1337 echo '<img class="icon data_img" src="' . $pmaThemeImage . 'new_data.png" width="16" height="16"
1338 alt="Data" title="' . __('Click to select') . '" style="cursor:pointer" onclick="showDetails('
1339 . "'" . implode($p, "','") . "'"
1340 . ', this, ' . "'" . PMA_escapeJsString(htmlspecialchars($row['src_table_name'])) . "'" . ')" />';
1342 echo '</td><td>' . htmlspecialchars($row['dst_table_name']) . '</td></tr>';
1343 $odd_row = !$odd_row;
1348 * array PMA_get_column_values (string $database, string $table, string $column , mysql db link $link = null)
1350 * @param string $database name of database
1351 * @param string $table name of table to retrieve columns from
1352 * @param string $column name of the column to retrieve data from
1353 * @param mixed $link mysql link resource
1354 * @return array $field_values
1356 function PMA_get_column_values($database, $table, $column, $link = null)
1358 $query = 'SELECT ';
1359 for ($i=0; $i< sizeof($column); $i++)
1361 $query.= PMA_backquote($column[$i]);
1362 if ($i < (sizeof($column)-1))
1364 $query.= ', ';
1367 $query.= ' FROM ' . PMA_backquote($database) . '.' . PMA_backquote($table);
1368 $field_values = PMA_DBI_fetch_result($query, null, null, $link);
1370 if (! is_array($field_values) || count($field_values) < 1) {
1371 return false;
1373 return $field_values;
1377 * array PMA_get_table_indexes($database, $table, $link = null)
1379 * @param string $database name of database
1380 * @param string $table name of the table whose indexes are to be retreived
1381 * @param mixed $link mysql link resource
1382 * @return array $indexes
1384 function PMA_get_table_indexes($database, $table, $link = null)
1386 $indexes = PMA_DBI_fetch_result(
1387 'SHOW INDEXES FROM ' .PMA_backquote($database) . '.' . PMA_backquote($table),
1388 null, null, $link);
1390 if (!is_array($indexes) || count($indexes) == 0) {
1391 return null;
1393 return $indexes;