Translation update done using Pootle.
[phpmyadmin.git] / libraries / server_synchronize.lib.php
blobffe0cc01ac82413288923fc0ef4194bcad0f3768
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], null, 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], null, 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 .= PMA_backquote($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 .= PMA_backquote($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 .= PMA_backquote($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 .= PMA_backquote($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 * @return nothing
374 function PMA_dataDiffInUncommonTables($source_tables_uncommon, $src_db, $src_link, $index, &$row_count)
376 $query = "SELECT COUNT(*) FROM " . PMA_backquote($src_db) . "." . PMA_backquote($source_tables_uncommon[$index]);
377 $rows = PMA_DBI_fetch_result($query, null, null, $src_link);
378 $row_count[$index] = $rows[0];
382 * PMA_updateTargetTables() sets the updated field values to target table rows using $update_array[$matching_table_index]
384 * @param array $table Matching tables' names
385 * @param array $update_array A three dimensional array containing field
386 * value updates required for each matching table
387 * @param string $src_db Name of source database
388 * @param string $trg_db Name of target database
389 * @param mixed $trg_link Connection established with target server
390 * @param int $matching_table_index index of matching table in matching_table_array
391 * @param array $matching_table_keys
392 * @param boolean $display
394 function PMA_updateTargetTables($table, $update_array, $src_db, $trg_db, $trg_link, $matching_table_index, $matching_table_keys, $display)
396 if (isset($update_array[$matching_table_index])) {
397 if (sizeof($update_array[$matching_table_index])) {
399 for ($update_row = 0; $update_row < sizeof($update_array[$matching_table_index]); $update_row++) {
401 if (isset($update_array[$matching_table_index][$update_row])) {
402 $update_fields_num = sizeof($update_array[$matching_table_index][$update_row])-sizeof($matching_table_keys[$matching_table_index]);
403 if ($update_fields_num > 0) {
404 $query = "UPDATE " . PMA_backquote($trg_db) . "." .PMA_backquote($table[$matching_table_index]) . " SET ";
406 for ($update_field = 0; $update_field < $update_fields_num; $update_field = $update_field+2) {
407 if (isset($update_array[$matching_table_index][$update_row][$update_field]) && isset($update_array[$matching_table_index][$update_row][$update_field+1])) {
408 $query .= PMA_backquote($update_array[$matching_table_index][$update_row][$update_field]) . "='" . $update_array[$matching_table_index][$update_row][$update_field+1] . "'";
410 if ($update_field < ($update_fields_num - 2)) {
411 $query .= ", ";
414 $query .= " WHERE ";
415 if (isset($matching_table_keys[$matching_table_index])) {
416 for ($key = 0; $key < sizeof($matching_table_keys[$matching_table_index]); $key++) {
417 if (isset($matching_table_keys[$matching_table_index][$key])) {
418 $query .= PMA_backquote($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++) {
491 $insert_query .= PMA_backquote($table_fields[$matching_table_index][$field_index]);
493 $is_fk_query = "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '" . $trg_db ."'
494 AND TABLE_NAME = '" . $matching_table[$matching_table_index]. "'AND COLUMN_NAME = '" .
495 $table_fields[$matching_table_index][$field_index] . "' AND TABLE_NAME <> REFERENCED_TABLE_NAME;" ;
497 $is_fk_result = PMA_DBI_fetch_result($is_fk_query, null, null, $trg_link);
498 if (sizeof($is_fk_result) > 0) {
499 for ($j = 0; $j < sizeof($is_fk_result); $j++) {
500 $table_index = array_keys($matching_table, $is_fk_result[$j]['REFERENCED_TABLE_NAME']);
502 if (isset($alter_str_array[$table_index[0]])) {
503 PMA_alterTargetTableStructure(
504 $trg_db, $trg_link, $matching_tables, $source_columns, $alter_str_array, $matching_tables_fields,
505 $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(
515 $delete_array, $matching_tables, $table_index[0], $target_tables_keys,
516 $matching_tables_keys, $trg_db, $trg_link, $src_db, $src_link
519 if (isset($delete_array[$table_index[0]])) {
520 PMA_deleteFromTargetTable($trg_db, $trg_link, $matching_tables, $table_index[0], $target_tables_keys, $delete_array, $display);
521 unset($delete_array[$table_index[0]]);
523 PMA_addColumnsInTargetTable(
524 $src_db, $trg_db, $src_link, $trg_link, $matching_tables, $source_columns, $add_column_array,
525 $matching_tables_fields, $criteria, $matching_tables_keys, $target_tables_keys, $uncommon_tables,
526 $uncommon_tables_fields, $table_index[0], $uncommon_cols, $display
528 unset($add_column_array[$table_index[0]]);
530 if (isset($add_indexes_array[$table_index[0]])
531 || isset($remove_indexes_array[$table_index[0]])
532 || isset($alter_indexes_array[$table_index[0]])
534 PMA_applyIndexesDiff(
535 $trg_db, $trg_link, $matching_tables, $source_indexes, $target_indexes, $add_indexes_array,
536 $alter_indexes_array, $remove_indexes_array, $table_index[0], $display
539 unset($add_indexes_array[$table_index[0]]);
540 unset($alter_indexes_array[$table_index[0]]);
541 unset($remove_indexes_array[$table_index[0]]);
543 if (isset($update_array[$table_index[0]])) {
544 PMA_updateTargetTables(
545 $matching_tables, $update_array, $src_db, $trg_db, $trg_link,
546 $table_index[0], $matching_table_keys, $display
548 unset($update_array[$table_index[0]]);
550 if (isset($array_insert[$table_index[0]])) {
551 PMA_insertIntoTargetTable(
552 $matching_table, $src_db, $trg_db, $src_link, $trg_link, $table_fields, $array_insert, $table_index[0],
553 $matching_tables_keys, $source_columns, $add_column_array, $criteria, $target_tables_keys, $uncommon_tables,
554 $uncommon_tables_fields, $uncommon_cols, $alter_str_array, $source_indexes, $target_indexes, $add_indexes_array,
555 $alter_indexes_array, $delete_array, $update_array, $display
557 unset($array_insert[$table_index[0]]);
561 if ($field_index < sizeof($table_fields[$matching_table_index])-1) {
562 $insert_query .= ", ";
565 $insert_query .= ") VALUES(";
566 if (sizeof($table_fields[$matching_table_index]) == 1) {
567 $insert_query .= "'" . PMA_sqlAddSlashes($result[0]) . "'";
568 } else {
569 for ($field_index = 0; $field_index < sizeof($table_fields[$matching_table_index]); $field_index++) {
570 if (isset($result[0][$table_fields[$matching_table_index][$field_index]])) {
571 $insert_query .= "'" . PMA_sqlAddSlashes($result[0][$table_fields[$matching_table_index][$field_index]]) . "'";
572 } else {
573 $insert_query .= "'NULL'";
575 if ($field_index < (sizeof($table_fields[$matching_table_index])) - 1) {
576 $insert_query .= " ," ;
580 $insert_query .= ");";
581 if ($display == true) {
582 PMA_displayQuery($insert_query);
584 PMA_DBI_try_query($insert_query, $trg_link, 0);
592 * PMA_createTargetTables() Create the missing table $uncommon_table in target database
594 * @param string $src_db name of source database
595 * @param string $trg_db name of target database
596 * @param mixed $src_link connection established with source server
597 * @param mixed $trg_link connection established with target server
598 * @param array &$uncommon_tables names of tables present in source but not in target
599 * @param int $table_index index of table in $uncommon_tables array
600 * @param array &$uncommon_tables_fields field names of the uncommon table
601 * @param bool $display
603 function PMA_createTargetTables($src_db, $trg_db, $src_link, $trg_link, &$uncommon_tables, $table_index, &$uncommon_tables_fields, $display)
605 if (isset($uncommon_tables[$table_index])) {
606 $fields_result = PMA_DBI_get_columns($src_db, $uncommon_tables[$table_index], null, true, $src_link);
607 $fields = array();
608 foreach ($fields_result as $each_field) {
609 $field_name = $each_field['Field'];
610 $fields[] = $field_name;
612 $uncommon_tables_fields[$table_index] = $fields;
614 $Create_Query = PMA_DBI_fetch_value("SHOW CREATE TABLE " . PMA_backquote($src_db) . '.' . PMA_backquote($uncommon_tables[$table_index]), 0, 1, $src_link);
616 // Replace the src table name with a `dbname`.`tablename`
617 $Create_Table_Query = preg_replace('/' . preg_quote(PMA_backquote($uncommon_tables[$table_index]), '/') . '/',
618 PMA_backquote($trg_db) . '.' .PMA_backquote($uncommon_tables[$table_index]),
619 $Create_Query,
620 $limit = 1
623 $is_fk_query = "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '" . $src_db . "'
624 AND TABLE_NAME = '" . $uncommon_tables[$table_index] . "' AND TABLE_NAME <> REFERENCED_TABLE_NAME;" ;
626 $is_fk_result = PMA_DBI_fetch_result($is_fk_query, null, null, $src_link);
627 if (sizeof($is_fk_result) > 0) {
628 for ($j = 0; $j < sizeof($is_fk_result); $j++) {
629 if (in_array($is_fk_result[$j]['REFERENCED_TABLE_NAME'], $uncommon_tables)) {
630 $table_index = array_keys($uncommon_tables, $is_fk_result[$j]['REFERENCED_TABLE_NAME']);
631 PMA_createTargetTables($src_db, $trg_db, $trg_link, $src_link, $uncommon_tables, $table_index[0], $uncommon_tables_fields, $display);
632 unset($uncommon_tables[$table_index[0]]);
636 $Create_Table_Query .= ';';
637 if ($display == true) {
638 echo '<p>' . $Create_Table_Query . '</p>';
640 PMA_DBI_try_query($Create_Table_Query, $trg_link, 0);
644 * PMA_populateTargetTables() inserts data into uncommon tables after they have been created
646 * @param string $src_db name of source database
647 * @param string $trg_db name of target database
648 * @param mixed $src_link connection established with source server
649 * @param mixed $trg_link connection established with target server
650 * @param array $uncommon_tables uncommon table names (table names that are present in source but not in target db)
651 * @param int $table_index index of table in matching_table_array
652 * @param array $uncommon_tables_fields field names of the uncommon table
653 * @param bool $display
655 * @todo This turns NULL values into '' (empty string)
657 function PMA_populateTargetTables($src_db, $trg_db, $src_link, $trg_link, $uncommon_tables, $table_index, $uncommon_tables_fields, $display)
659 $display = false; // todo: maybe display some of the queries if they are not too numerous
660 $unbuffered_result = PMA_DBI_try_query('SELECT * FROM ' . PMA_backquote($src_db) . '.' . PMA_backquote($uncommon_tables[$table_index]), $src_link, PMA_DBI_QUERY_UNBUFFERED);
661 if (false !== $unbuffered_result) {
662 $insert_query = 'INSERT INTO ' . PMA_backquote($trg_db) . '.' .PMA_backquote($uncommon_tables[$table_index]) . ' VALUES';
663 while ($one_row = PMA_DBI_fetch_row($unbuffered_result)) {
664 $insert_query .= '(';
665 $key_of_last_value = count($one_row) - 1;
666 foreach ($one_row as $key => $value) {
667 $insert_query .= "'" . PMA_sqlAddSlashes($value) . "'";
668 if ($key < $key_of_last_value) {
669 $insert_query .= ",";
672 $insert_query .= '),';
674 $insert_query = substr($insert_query, 0, -1);
675 $insert_query .= ';';
676 if ($display == true) {
677 PMA_displayQuery($insert_query);
679 PMA_DBI_try_query($insert_query, $trg_link, 0);
684 * PMA_deleteFromTargetTable() delete rows from target table
686 * @param string $trg_db name of target database
687 * @param mixed $trg_link connection established with target server
688 * @param array $matching_tables matching table names
689 * @param int $table_index index of table in matching_table_array
690 * @param array $target_tables_keys primary key names of the target tables
691 * @param array $delete_array key values of rows that are to be deleted
692 * @param bool $display
694 function PMA_deleteFromTargetTable($trg_db, $trg_link, $matching_tables, $table_index, $target_tables_keys, $delete_array, $display)
696 for ($i = 0; $i < sizeof($delete_array[$table_index]); $i++) {
697 if (isset($target_tables_keys[$table_index])) {
698 $delete_query = 'DELETE FROM ' . PMA_backquote($trg_db) . '.' .PMA_backquote($matching_tables[$table_index]) . ' WHERE ';
699 for ($y = 0; $y < sizeof($target_tables_keys[$table_index]); $y++) {
700 $delete_query .= PMA_backquote($target_tables_keys[$table_index][$y]) . " = '";
702 if (sizeof($target_tables_keys[$table_index]) == 1) {
703 $delete_query .= $delete_array[$table_index][$i] . "'";
704 } elseif (sizeof($target_tables_keys[$table_index]) > 1) {
705 $delete_query .= $delete_array[$table_index][$i][$target_tables_keys[$table_index][$y]] . "'";
707 if ($y < (sizeof($target_tables_keys[$table_index]) - 1)) {
708 $delete_query .= ' AND ';
710 $pk_query = "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = '" . $trg_db . "'
711 AND REFERENCED_TABLE_NAME = '" . $matching_tables[$table_index]."' AND REFERENCED_COLUMN_NAME = '"
712 . $target_tables_keys[$table_index][$y] . "' AND TABLE_NAME <> REFERENCED_TABLE_NAME;";
714 $pk_query_result = PMA_DBI_fetch_result($pk_query, null, null, $trg_link);
715 $result_size = sizeof($pk_query_result);
717 if ($result_size > 0) {
718 for ($b = 0; $b < $result_size; $b++) {
719 $drop_pk_query = "DELETE FROM " . PMA_backquote($pk_query_result[$b]['TABLE_SCHEMA']) . "." . PMA_backquote($pk_query_result[$b]['TABLE_NAME']) . " WHERE " . PMA_backquote($pk_query_result[$b]['COLUMN_NAME']) . " = " . $target_tables_keys[$table_index][$y] . ";";
720 PMA_DBI_try_query($drop_pk_query, $trg_link, 0);
725 if ($display == true) {
726 echo '<p>' . $delete_query . '</p>';
728 PMA_DBI_try_query($delete_query, $trg_link, 0);
733 * PMA_structureDiffInTables() Gets all the column information for source and target table.
734 * Compare columns on their names.
735 * If column exists in target then compare Type, Null, Collation, Key, Default and Comment for that column.
736 * If column does not exist in target table then it is placed in $add_column_array.
737 * If column exists in target table but criteria is different then it is palced in $alter_str_array.
738 * If column does not exist in source table but is present in target table then it is placed in $uncommon_columns.
739 * Keys for all the source tables that have a corresponding target table are placed in $matching_tables_keys.
740 * Keys for all the target tables that have a corresponding source table are placed in $target_tables_keys.
742 * @param string $src_db name of source database
743 * @param string $trg_db name of target database
744 * @param mixed $src_link connection established with source server
745 * @param mixed $trg_link connection established with target server
746 * @param array $matching_tables names of matching tables
747 * @param array &$source_columns columns information of the source tables
748 * @param array &$target_columns columns information of the target tables
749 * @param array &$alter_str_array three dimensional associative array first index being the matching table index, second index being column name for which target
750 * column have some criteria different and third index containing the criteria which is different.
751 * @param array &$add_column_array two dimensional associative array, first index of the array contain the matching table number and second index contain the
752 * column name which is to be added in the target table
753 * @param array &$uncommon_columns columns that are present in the target table but not in the source table
754 * @param array $criteria criteria which are to be checked for field that is present in source table and target table
755 * @param array &$target_tables_keys field names which is key in the target table
756 * @param int $matching_table_index number of the matching table
758 function PMA_structureDiffInTables($src_db, $trg_db, $src_link, $trg_link, $matching_tables, &$source_columns, &$target_columns, &$alter_str_array,
759 &$add_column_array, &$uncommon_columns, $criteria, &$target_tables_keys, $matching_table_index)
761 //Gets column information for source and target table
762 $source_columns[$matching_table_index] = PMA_DBI_get_columns_full($src_db, $matching_tables[$matching_table_index], null, $src_link);
763 $target_columns[$matching_table_index] = PMA_DBI_get_columns_full($trg_db, $matching_tables[$matching_table_index], null, $trg_link);
764 foreach ($source_columns[$matching_table_index] as $column_name => $each_column) {
765 if (isset($target_columns[$matching_table_index][$column_name]['Field'])) {
766 //If column exists in target table then matches criteria like type, null, collation, key, default, comment of the column
767 for ($i = 0; $i < sizeof($criteria); $i++) {
768 if ($source_columns[$matching_table_index][$column_name][$criteria[$i]] != $target_columns[$matching_table_index][$column_name][$criteria[$i]]) {
769 if (($criteria[$i] == 'Default') && ($source_columns[$matching_table_index][$column_name][$criteria[$i]] == '' )) {
770 $alter_str_array[$matching_table_index][$column_name][$criteria[$i]] = 'None';
771 } else {
772 if (! (($criteria[$i] == 'Key') && (($source_columns[$matching_table_index][$column_name][$criteria[$i]] == 'MUL')
773 || ($target_columns[$matching_table_index][$column_name][$criteria[$i]] == 'MUL')
774 || ($source_columns[$matching_table_index][$column_name][$criteria[$i]] == 'UNI')
775 || ($target_columns[$matching_table_index][$column_name][$criteria[$i]] == 'UNI')))
777 $alter_str_array[$matching_table_index][$column_name][$criteria[$i]] = $source_columns[$matching_table_index][$column_name][$criteria[$i]];
782 } else {
783 $add_column_array[$matching_table_index][$column_name]= $column_name;
786 //Finds column names that are present in target table but not in source table
787 foreach ($target_columns[$matching_table_index] as $fld_name => $each_column) {
788 if (! (isset($source_columns[$matching_table_index][$fld_name]['Field']))) {
789 $fields_uncommon[] = $fld_name;
791 if ($target_columns[$matching_table_index][$fld_name]['Key'] == 'PRI') {
792 $keys[] = $fld_name;
795 if (isset($fields_uncommon)) {
796 $uncommon_columns[$matching_table_index] = $fields_uncommon;
798 if (isset($keys)) {
799 $target_tables_keys[$matching_table_index] = $keys;
803 * PMA_addColumnsInTargetTable() adds column that are present in source table but not in target table
805 * @param string $src_db name of source database
806 * @param string $trg_db name of target database
807 * @param mixed $src_link connection established with source server
808 * @param mixed $trg_link connection established with target server
809 * @param array $matching_tables names of matching tables
810 * @param array $source_columns columns information of the source tables
811 * @param array &$add_column_array the names of the column(field) that are to be added in the target
812 * @param array $matching_tables_fields
813 * @param array $criteria criteria
814 * @param array $matching_tables_keys field names which is key in the source table
815 * @param array $target_tables_keys field names which is key in the target table
816 * @param array $uncommon_tables table names that are present in source db and not in target db
817 * @param array &$uncommon_tables_fields names of the fields of the uncommon tables
818 * @param int $table_counter number of the matching table
819 * @param array $uncommon_cols
820 * @param bool $display
822 function PMA_addColumnsInTargetTable($src_db, $trg_db, $src_link, $trg_link, $matching_tables, $source_columns, &$add_column_array, $matching_tables_fields,
823 $criteria, $matching_tables_keys, $target_tables_keys, $uncommon_tables, &$uncommon_tables_fields, $table_counter, $uncommon_cols, $display)
825 for ($i = 0; $i < sizeof($matching_tables_fields[$table_counter]); $i++) {
826 if (isset($add_column_array[$table_counter][$matching_tables_fields[$table_counter][$i]])) {
827 $query = "ALTER TABLE " . PMA_backquote($trg_db) . '.' . PMA_backquote($matching_tables[$table_counter]). " ADD COLUMN " .
828 PMA_backquote($add_column_array[$table_counter][$matching_tables_fields[$table_counter][$i]]) . " " . $source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Type'];
830 if ($source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Null'] == 'NO') {
831 $query .= ' Not Null ';
832 } elseif ($source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Null'] == 'YES') {
833 $query .= ' Null ';
835 if ($source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Collation'] != '') {
836 $query .= ' COLLATE ' . $source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Collation'];
838 if ($source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Default'] != '') {
839 $query .= " DEFAULT " . $source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Default'];
841 if ($source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Comment'] != '') {
842 $query .= " COMMENT " . $source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Comment'];
844 if ($source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Key'] == 'PRI' ) {
845 $trg_key_size = sizeof($target_tables_keys[$table_counter]);
846 if ($trg_key_size) {
847 $check = true;
848 for ($a = 0; ($a < $trg_key_size) && ($check); $a++) {
849 if (! (in_array($target_tables_keys[$table_counter], $uncommon_cols))) {
850 $check = false;
853 if (! $check) {
854 $query .= " ,DROP PRIMARY KEY " ;
857 $query .= " , ADD PRIMARY KEY (";
858 for ($t = 0; $t < sizeof($matching_tables_keys[$table_counter]); $t++) {
859 $query .= PMA_backquote($matching_tables_keys[$table_counter][$t]);
860 if ($t < (sizeof($matching_tables_keys[$table_counter]) - 1)) {
861 $query .= " , " ;
864 $query .= ")";
867 $query .= ";";
868 if ($display == true) {
869 echo '<p>' . $query . '</p>';
871 PMA_DBI_try_query($query, $trg_link, 0);
873 //Checks if column to be added is a foreign key or not
874 $is_fk_query = "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '" . $trg_db . "' AND TABLE_NAME = '"
875 . $matching_tables[$table_counter] . "' AND COLUMN_NAME ='" . $add_column_array[$table_counter][$matching_tables_fields[$table_counter][$i]] .
876 "' AND TABLE_NAME <> REFERENCED_TABLE_NAME;";
878 $is_fk_result = PMA_DBI_fetch_result($is_fk_query, null, null, $src_link);
880 //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
881 //it is created first.
882 if (isset($is_fk_result)) {
883 if (in_array($is_fk_result[0]['REFERENCED_TABLE_NAME'], $uncommon_tables)) {
884 $table_index = array_keys($uncommon_tables, $is_fk_result[0]['REFERENCED_TABLE_NAME']);
885 PMA_checkForeignKeys($src_db, $src_link, $trg_db, $trg_link, $is_fk_result[0]['REFERENCED_TABLE_NAME'], $uncommon_tables, $uncommon_tables_fields, $display);
886 PMA_createTargetTables($src_db, $trg_db, $trg_link, $src_link, $uncommon_tables, $table_index[0], $uncommon_tables_fields, $display);
887 unset($uncommon_tables[$table_index[0]]);
889 $fk_query = "ALTER TABLE " . PMA_backquote($trg_db) . '.' . PMA_backquote($matching_tables[$table_counter]) .
890 "ADD CONSTRAINT FOREIGN KEY " . PMA_backquote($add_column_array[$table_counter][$matching_tables_fields[$table_counter][$i]]) . "
891 (" . $add_column_array[$table_counter][$matching_tables_fields[$table_counter][$i]] . ") REFERENCES " . PMA_backquote($trg_db) .
892 '.' . PMA_backquote($is_fk_result[0]['REFERENCED_TABLE_NAME']) . " (" . $is_fk_result[0]['REFERENCED_COLUMN_NAME'] . ");";
894 PMA_DBI_try_query($fk_query, $trg_link, null);
900 * PMA_checkForeignKeys() checks if the referenced table have foreign keys.
901 * uses PMA_createTargetTables()
903 * @param string $src_db name of source database
904 * @param mixed $src_link connection established with source server
905 * @param string $trg_db name of target database
906 * @param mixed $trg_link connection established with target server
907 * @param string $referenced_table table whose column is a foreign key in another table
908 * @param array &$uncommon_tables names that are uncommon
909 * @param array &$uncommon_tables_fields field names of the uncommon table
910 * @param bool $display
912 function PMA_checkForeignKeys($src_db, $src_link, $trg_db, $trg_link, $referenced_table, &$uncommon_tables, &$uncommon_tables_fields, $display)
914 $is_fk_query = "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '" . $src_db . "'
915 AND TABLE_NAME = '" . $referenced_table . "' AND TABLE_NAME <> REFERENCED_TABLE_NAME;";
917 $is_fk_result = PMA_DBI_fetch_result($is_fk_query, null, null, $src_link);
918 if (sizeof($is_fk_result) > 0) {
919 for ($j = 0; $j < sizeof($is_fk_result); $j++) {
920 if (in_array($is_fk_result[$j]['REFERENCED_TABLE_NAME'], $uncommon_tables)) {
921 $table_index = array_keys($uncommon_tables, $is_fk_result[$j]['REFERENCED_TABLE_NAME']);
922 PMA_checkForeignKeys(
923 $src_db, $src_link, $trg_db, $trg_link, $is_fk_result[$j]['REFERENCED_TABLE_NAME'],
924 $uncommon_tables, $uncommon_tables_fields, $display
926 PMA_createTargetTables($src_db, $trg_db, $trg_link, $src_link, $uncommon_tables, $table_index[0], $uncommon_tables_fields, $display);
927 unset($uncommon_tables[$table_index[0]]);
933 * PMA_alterTargetTableStructure() alters structure of the target table using $alter_str_array
935 * @param string $trg_db name of target database
936 * @param mixed $trg_link connection established with target server
937 * @param array $matching_tables names of matching tables
938 * @param array &$source_columns columns information of the source table
939 * @param array &$alter_str_array column name and criteria which is to be altered for the targert table
940 * @param array $matching_tables_fields name of the fields for the matching table
941 * @param array $criteria criteria
942 * @param array &$matching_tables_keys field names which is key in the source table
943 * @param array &$target_tables_keys field names which is key in the target table
944 * @param int $matching_table_index number of the matching table
945 * @param bool $display
947 function PMA_alterTargetTableStructure($trg_db, $trg_link, $matching_tables, &$source_columns, &$alter_str_array, $matching_tables_fields, $criteria,
948 &$matching_tables_keys, &$target_tables_keys, $matching_table_index, $display)
950 $check = true;
951 $sql_query = '';
952 $found = false;
954 //Checks if the criteria to be altered is primary key
955 for ($v = 0; $v < sizeof($matching_tables_fields[$matching_table_index]); $v++) {
956 if (isset($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$v]]['Key'])) {
957 if ($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$v]]['Key'] == 'PRI' ) {
958 $check = false;
963 $pri_query = null;
964 if (! $check) {
965 $pri_query = "ALTER TABLE " . PMA_backquote($trg_db) . '.' . PMA_backquote($matching_tables[$matching_table_index]);
966 if (sizeof($target_tables_keys[$matching_table_index]) > 0) {
967 $pri_query .= " DROP PRIMARY KEY ," ;
969 $pri_query .= " ADD PRIMARY KEY (";
970 for ($z = 0; $z < sizeof($matching_tables_keys[$matching_table_index]); $z++) {
971 $pri_query .= PMA_backquote($matching_tables_keys[$matching_table_index][$z]);
972 if ($z < (sizeof($matching_tables_keys[$matching_table_index]) - 1)) {
973 $pri_query .= " , " ;
976 $pri_query .= ");";
979 if (isset($pri_query)) {
980 if ($display == true) {
981 echo '<p>' . $pri_query . '</p>';
983 PMA_DBI_try_query($pri_query, $trg_link, 0);
985 for ($t = 0; $t < sizeof($matching_tables_fields[$matching_table_index]); $t++) {
986 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)) {
987 $sql_query = 'ALTER TABLE ' . PMA_backquote($trg_db) . '.' . PMA_backquote($matching_tables[$matching_table_index]) . ' MODIFY ' .
988 PMA_backquote($matching_tables_fields[$matching_table_index][$t]) . ' ' . $source_columns[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]]['Type'];
989 $found = false;
990 for ($i = 0; $i < sizeof($criteria); $i++) {
991 if (isset($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]]) && $criteria[$i] != 'Key') {
992 $found = true;
993 if (($criteria[$i] == 'Type') && (! isset($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i+1]]))) {
994 if ($source_columns[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i + 1]] == 'NO') {
995 $sql_query .= " Not Null" ;
996 } elseif ($source_columns[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i + 1]] == 'YES') {
997 $sql_query .= " Null" ;
1000 if (($criteria[$i] == 'Null') && ( $alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]] == 'NO')) {
1001 $sql_query .= " Not Null " ;
1002 } elseif (($criteria[$i] == 'Null') && ($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]] == 'YES')) {
1003 $sql_query .= " Null " ;
1005 if ($criteria[$i] == 'Collation') {
1006 if ( !(isset($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[2]]))) {
1007 $sql_query .= " Not Null " ;
1009 $sql_query .= " COLLATE " . $alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]] ;
1011 if (($criteria[$i] == 'Default') && ($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]] == 'None')) {
1012 if ( !(isset($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[2]]))) {
1013 $sql_query .= " Not Null " ;
1015 } elseif ($criteria[$i] == 'Default') {
1016 if (! (isset($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[2]]))) {
1017 $sql_query .= " Not Null " ;
1019 if (is_string($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]])) {
1020 if ($source_columns[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]]['Type'] != 'timestamp') {
1021 $sql_query .= " DEFAULT '" . $alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]] . "'";
1022 } elseif ($source_columns[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]]['Type'] == 'timestamp') {
1023 $sql_query .= " DEFAULT " . $alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]];
1025 } elseif (is_numeric($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]])) {
1026 $sql_query .= " DEFAULT " . $alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]];
1029 if ($criteria[$i] == 'Comment') {
1030 if ( !(isset($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[2]]))) {
1031 $sql_query .= " Not Null " ;
1033 $sql_query .= " COMMENT '" . $alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]] . "'" ;
1038 $sql_query .= ";";
1039 if ($found) {
1040 if ($display == true) {
1041 echo '<p>' . $sql_query . '</p>';
1043 PMA_DBI_try_query($sql_query, $trg_link, 0);
1046 $check = false;
1047 $query = "ALTER TABLE " . PMA_backquote($trg_db) . '.' . PMA_backquote($matching_tables[$matching_table_index]);
1048 for ($p = 0; $p < sizeof($matching_tables_keys[$matching_table_index]); $p++) {
1049 if ((isset($alter_str_array[$matching_table_index][$matching_tables_keys[$matching_table_index][$p]]['Key']))) {
1050 $check = true;
1051 $query .= ' MODIFY ' . PMA_backquote($matching_tables_keys[$matching_table_index][$p]) . ' '
1052 . $source_columns[$matching_table_index][$matching_tables_fields[$matching_table_index][$p]]['Type'] . ' Not Null ';
1053 if ($p < (sizeof($matching_tables_keys[$matching_table_index]) - 1)) {
1054 $query .= ', ';
1058 $query .= ';';
1059 if ($check) {
1060 if ($display == true) {
1061 echo '<p>' . $query . '</p>';
1063 PMA_DBI_try_query($query, $trg_link, 0);
1068 * PMA_removeColumnsFromTargetTable() removes the columns which are present in target table but not in source table.
1070 * @param string $trg_db name of target database
1071 * @param mixed $trg_link connection established with target server
1072 * @param array $matching_tables names of matching tables
1073 * @param array $uncommon_columns array containing the names of the column which are to be dropped from the target table
1074 * @param int $table_counter index of the matching table as in $matchiing_tables array
1075 * @param bool $display
1077 function PMA_removeColumnsFromTargetTable($trg_db, $trg_link, $matching_tables, $uncommon_columns, $table_counter, $display)
1079 if (isset($uncommon_columns[$table_counter])) {
1080 $drop_query = "ALTER TABLE " . PMA_backquote($trg_db) . "." . PMA_backquote($matching_tables[$table_counter]);
1081 for ($a = 0; $a < sizeof($uncommon_columns[$table_counter]); $a++) {
1082 //Checks if column to be removed is a foreign key in any table
1083 $pk_query = "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = '" . $trg_db . "'
1084 AND REFERENCED_TABLE_NAME = '" . $matching_tables[$table_counter]."' AND REFERENCED_COLUMN_NAME = '"
1085 . $uncommon_columns[$table_counter][$a] . "' AND TABLE_NAME <> REFERENCED_TABLE_NAME;";
1087 $pk_query_result = PMA_DBI_fetch_result($pk_query, null, null, $trg_link);
1088 $result_size = sizeof($pk_query_result);
1090 if ($result_size > 0) {
1091 for ($b = 0; $b < $result_size; $b++) {
1092 $drop_pk_query = "ALTER TABLE " . PMA_backquote($pk_query_result[$b]['TABLE_SCHEMA']) . "." . PMA_backquote($pk_query_result[$b]['TABLE_NAME']) . "
1093 DROP FOREIGN KEY " . PMA_backquote($pk_query_result[$b]['CONSTRAINT_NAME']) . ", DROP COLUMN " . PMA_backquote($pk_query_result[$b]['COLUMN_NAME']) . ";";
1094 PMA_DBI_try_query($drop_pk_query, $trg_link, 0);
1097 $query = "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '" . $trg_db . "' AND TABLE_NAME = '"
1098 . $matching_tables[$table_counter]. "' AND COLUMN_NAME = '" . $uncommon_columns[$table_counter][$a] . "'
1099 AND TABLE_NAME <> REFERENCED_TABLE_NAME;";
1101 $result = PMA_DBI_fetch_result($query, null, null, $trg_link);
1103 if (sizeof($result) > 0) {
1104 $drop_query .= " DROP FOREIGN KEY " . PMA_backquote($result[0]['CONSTRAINT_NAME']) . ",";
1106 $drop_query .= " DROP COLUMN " . PMA_backquote($uncommon_columns[$table_counter][$a]);
1107 if ($a < (sizeof($uncommon_columns[$table_counter]) - 1)) {
1108 $drop_query .= " , " ;
1111 $drop_query .= ";" ;
1113 if ($display == true) {
1114 echo '<p>' . $drop_query . '</p>';
1116 PMA_DBI_try_query($drop_query, $trg_link, 0);
1121 * 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
1122 * indexes to be altered in $alter_indexes_array and indexes to be removed from target table in $remove_indexes_array.
1123 * Only keyname and uniqueness characteristic of the indexes are altered.
1125 * @param string $src_db name of source database
1126 * @param string $trg_db name of target database
1127 * @param mixed $src_link connection established with source server
1128 * @param mixed $trg_link connection established with target server
1129 * @param array $matching_tables matching tables name
1130 * @param array &$source_indexes indexes of the source table
1131 * @param array &$target_indexes indexes of the target table
1132 * @param array &$add_indexes_array name of the column on which the index is to be added in the target table
1133 * @param array &$alter_indexes_array key name which needs to be altered
1134 * @param array &$remove_indexes_array key name of the index which is to be removed from the target table
1135 * @param int $table_counter number of the matching table
1137 function PMA_indexesDiffInTables($src_db, $trg_db, $src_link, $trg_link, $matching_tables, &$source_indexes, &$target_indexes, &$add_indexes_array,
1138 &$alter_indexes_array, &$remove_indexes_array, $table_counter)
1140 //Gets indexes information for source and target table
1141 $source_indexes[$table_counter] = PMA_DBI_get_table_indexes($src_db, $matching_tables[$table_counter], $src_link);
1142 $target_indexes[$table_counter] = PMA_DBI_get_table_indexes($trg_db, $matching_tables[$table_counter], $trg_link);
1143 for ($a = 0; $a < sizeof($source_indexes[$table_counter]); $a++) {
1144 $found = false;
1145 $z = 0;
1146 //Compares key name and non_unique characteristic of source indexes with target indexes
1148 * @todo compare the length of each sub part
1150 while (($z <= sizeof($target_indexes[$table_counter])) && ($found == false)) {
1151 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']) {
1152 $found = true;
1153 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'])) {
1154 if (! (($source_indexes[$table_counter][$a]['Key_name'] == "PRIMARY") || ($target_indexes[$table_counter][$z]['Key_name'] == 'PRIMARY'))) {
1155 $alter_indexes_array[$table_counter][] = $source_indexes[$table_counter][$a]['Key_name'];
1159 $z++;
1161 if ($found === false) {
1162 if (! ($source_indexes[$table_counter][$a]['Key_name'] == 'PRIMARY')) {
1163 $add_indexes_array [$table_counter][] = $source_indexes[$table_counter][$a]['Column_name'];
1168 //Finds indexes that exist on target table but not on source table
1169 for ($b = 0; $b < sizeof($target_indexes[$table_counter]); $b++) {
1170 $found = false;
1171 $c = 0;
1172 while (($c <= sizeof($source_indexes[$table_counter])) && ($found == false)) {
1173 if ($target_indexes[$table_counter][$b]['Column_name'] == $source_indexes[$table_counter][$c]['Column_name']) {
1174 $found = true;
1176 $c++;
1178 if ($found === false) {
1179 $remove_indexes_array[$table_counter][] = $target_indexes[$table_counter][$b]['Key_name'];
1185 * PMA_applyIndexesDiff() create indexes, alters indexes and remove indexes.
1187 * @param string $trg_db name of target database
1188 * @param mixed $trg_link connection established with target server
1189 * @param array $matching_tables matching tables name
1190 * @param array $source_indexes indexes of the source table
1191 * @param array $target_indexes indexes of the target table
1192 * @param array $add_indexes_array column names on which indexes are to be created in target table
1193 * @param array $alter_indexes_array column names for which indexes are to be altered
1194 * @param array $remove_indexes_array key name of the indexes which are to be removed from the target table
1195 * @param int $table_counter number of the matching table
1196 * @param $display
1198 function PMA_applyIndexesDiff ($trg_db, $trg_link, $matching_tables, $source_indexes, $target_indexes, $add_indexes_array, $alter_indexes_array,
1199 $remove_indexes_array, $table_counter, $display)
1201 //Adds indexes on target table
1202 if (isset($add_indexes_array[$table_counter])) {
1203 $sql = "ALTER TABLE " . PMA_backquote($trg_db) . "." . PMA_backquote($matching_tables[$table_counter]) . " ADD" ;
1204 for ($a = 0; $a < sizeof($source_indexes[$table_counter]); $a++) {
1205 if (isset($add_indexes_array[$table_counter][$a])) {
1206 for ($b = 0; $b < sizeof($source_indexes[$table_counter]); $b++) {
1207 if ($source_indexes[$table_counter][$b]['Column_name'] == $add_indexes_array[$table_counter][$a]) {
1208 if ($source_indexes[$table_counter][$b]['Non_unique'] == '0') {
1209 $sql .= " UNIQUE ";
1211 $sql .= " INDEX " . PMA_backquote($source_indexes[$table_counter][$b]['Key_name']) . " (" . $add_indexes_array[$table_counter][$a] . " );";
1212 if ($display == true) {
1213 echo '<p>' . $sql . '</p>';
1215 PMA_DBI_try_query($sql, $trg_link, 0);
1221 //Alter indexes of target table
1223 if (isset($alter_indexes_array[$table_counter])) {
1224 $query = "ALTER TABLE " . PMA_backquote($trg_db) . "." . PMA_backquote($matching_tables[$table_counter]);
1225 for ($a = 0; $a < sizeof($alter_indexes_array[$table_counter]); $a++) {
1226 if (isset($alter_indexes_array[$table_counter][$a])) {
1227 $query .= ' DROP INDEX ' . PMA_backquote($alter_indexes_array[$table_counter][$a]) . " , ADD ";
1228 $got_first_index_column = false;
1229 for ($z = 0; $z < sizeof($source_indexes[$table_counter]); $z++) {
1230 if ($source_indexes[$table_counter][$z]['Key_name'] == $alter_indexes_array[$table_counter][$a]) {
1231 if (! $got_first_index_column) {
1232 if ($source_indexes[$table_counter][$z]['Non_unique'] == '0') {
1233 $query .= " UNIQUE ";
1235 $query .= " INDEX " . PMA_backquote($source_indexes[$table_counter][$z]['Key_name']) . " (" . PMA_backquote($source_indexes[$table_counter][$z]['Column_name']);
1236 $got_first_index_column = true;
1237 } else {
1238 // another column for this index
1239 $query .= ', ' . PMA_backquote($source_indexes[$table_counter][$z]['Column_name']);
1243 $query .= " )";
1246 $query .= ';';
1247 if ($display == true) {
1248 echo '<p>' . $query . '</p>';
1250 PMA_DBI_try_query($query, $trg_link, 0);
1252 //Removes indexes from target table
1253 if (isset($remove_indexes_array[$table_counter])) {
1254 $drop_index_query = "ALTER TABLE " . PMA_backquote($trg_db) . "." . PMA_backquote($matching_tables[$table_counter]);
1255 for ($a = 0; $a < sizeof($target_indexes[$table_counter]); $a++) {
1256 if (isset($remove_indexes_array[$table_counter][$a])) {
1257 $drop_index_query .= " DROP INDEX " . PMA_backquote($remove_indexes_array[$table_counter][$a]);
1259 if ($a < (sizeof($remove_indexes_array[$table_counter]) - 1)) {
1260 $drop_index_query .= " , " ;
1263 $drop_index_query .= " ; " ;
1264 if ($display == true) {
1265 echo '<p>' . $drop_index_query . '</p>';
1267 PMA_DBI_try_query($drop_index_query, $trg_link, 0);
1272 * PMA_displayQuery() displays a query, taking the maximum display size
1273 * into account
1275 * @param string $query the query to display
1277 * @return nothing
1279 function PMA_displayQuery($query)
1281 if (strlen($query) > $GLOBALS['cfg']['MaxCharactersInDisplayedSQL']) {
1282 $query = substr($query, 0, $GLOBALS['cfg']['MaxCharactersInDisplayedSQL']) . '[...]';
1284 echo '<p>' . htmlspecialchars($query) . '</p>';
1288 * PMA_syncDisplayHeaderCompare() shows the header for source database
1290 * @param string $src_db source db name
1291 * @param string $trg_db target db name
1293 * @return nothing
1295 function PMA_syncDisplayHeaderCompare($src_db, $trg_db)
1297 echo '<fieldset style="padding:0"><div style="padding:1.5em; overflow:auto; height:220px">';
1299 echo '<table class="data">';
1300 echo '<tr>';
1301 echo '<th>' . __('Source database') . ': ' . htmlspecialchars($src_db) . '<br />(';
1302 if ('cur' == $_SESSION['src_type']) {
1303 echo __('Current server');
1304 } else {
1305 echo __('Remote server') . ' ' . htmlspecialchars($_SESSION['src_server']['host']);
1307 echo ')</th>';
1308 echo '<th>' . __('Difference') . '</th>';
1309 echo '<th>' . __('Target database') . ': '. htmlspecialchars($trg_db) . '<br />(';
1310 if ('cur' == $_SESSION['trg_type']) {
1311 echo __('Current server');
1312 } else {
1313 echo __('Remote server') . ' ' . htmlspecialchars($_SESSION['trg_server']['host']);
1315 echo ')</th>';
1316 echo '</tr>';
1320 * Prints table row
1322 * $rows contains following keys:
1323 * - src_table_name - source server table name
1324 * - dst_table_name - target server table name
1325 * - btn_type - 'M' or 'U'
1326 * - btn_structure - null or arguments for showDetails in server_synchronize.js (without img_obj and table_name):
1327 * i, update_size, insert_size, remove_size, insert_index, remove_index
1329 * @param array $rows
1331 function PMA_syncDisplayDataCompare($rows)
1333 global $pmaThemeImage;
1335 $odd_row = true;
1336 foreach ($rows as $row) {
1337 echo '<tr class=" ' . ($odd_row ? 'odd' : 'even') . '">';
1338 echo '<td>' . htmlspecialchars($row['src_table_name']) . '</td><td style="text-align:center">';
1339 if (isset($row['btn_structure']) && $row['btn_structure']) {
1340 // parameters: i, update_size, insert_size, remove_size, insert_index, remove_index
1341 $p = $row['btn_structure'];
1342 $p[0] = $row['btn_type'] . 'S' . $p[0];
1343 echo '<img class="icon struct_img" src="' . $pmaThemeImage . 'new_struct.png" width="16" height="16"
1344 alt="Structure" title="' . __('Click to select') . '" style="cursor:pointer" onclick="showDetails('
1345 . "'" . implode($p, "','") . "'"
1346 . ', this, ' . "'" . PMA_escapeJsString(htmlspecialchars($row['src_table_name'])) . "'" . ')" /> ';
1348 if (isset($row['btn_data']) && $row['btn_data']) {
1349 // parameters: i, update_size, insert_size, remove_size, insert_index, remove_index
1350 $p = $row['btn_data'];
1351 $p[0] = $row['btn_type'] . 'D' . $p[0];
1352 echo '<img class="icon data_img" src="' . $pmaThemeImage . 'new_data.png" width="16" height="16"
1353 alt="Data" title="' . __('Click to select') . '" style="cursor:pointer" onclick="showDetails('
1354 . "'" . implode($p, "','") . "'"
1355 . ', this, ' . "'" . PMA_escapeJsString(htmlspecialchars($row['src_table_name'])) . "'" . ')" />';
1357 echo '</td><td>' . htmlspecialchars($row['dst_table_name']) . '</td></tr>';
1358 $odd_row = !$odd_row;
1363 * array PMA_get_column_values (string $database, string $table, string $column , mysql db link $link = null)
1365 * @param string $database name of database
1366 * @param string $table name of table to retrieve columns from
1367 * @param string $column name of the column to retrieve data from
1368 * @param mixed $link mysql link resource
1370 * @return array $field_values
1372 function PMA_get_column_values($database, $table, $column, $link = null)
1374 $query = 'SELECT ';
1375 for ($i=0; $i< sizeof($column); $i++) {
1376 $query.= PMA_backquote($column[$i]);
1377 if ($i < (sizeof($column)-1)) {
1378 $query.= ', ';
1381 $query.= ' FROM ' . PMA_backquote($database) . '.' . PMA_backquote($table);
1382 $field_values = PMA_DBI_fetch_result($query, null, null, $link);
1384 if (! is_array($field_values) || count($field_values) < 1) {
1385 return false;
1387 return $field_values;