2
/* vim: set expandtab sw=4 ts=4 sts=4: */
5
* PMA_getMatchingTables places matching tables in source
6
* and target databases in $matching_tables array whereas
7
* $uncommon_source_tables array gets the tables present in
8
* source database but are absent from target database.
9
* Criterion for matching tables is just comparing their names.
11
* @param $trg_tables array of target database table names,
12
* @param $src_tables array of source database table names,
14
* @param &$matching_tables empty array passed by reference to save names of matching tables,
15
* @param &$uncommon_source_tables empty array passed by reference to save names of tables present in
16
* source database but absent from target database
19
function PMA_getMatchingTables($trg_tables, $src_tables, &$matching_tables, &$uncommon_source_tables)
21
for($k=0; $k< sizeof($src_tables); $k++) {
22
$present_in_target = false;
23
for($l=0; $l < sizeof($trg_tables); $l++) {
24
if ($src_tables[$k] === $trg_tables[$l]) {
25
$present_in_target = true;
26
$matching_tables[] = $src_tables[$k];
29
if ($present_in_target === false) {
30
$uncommon_source_tables[] = $src_tables[$k];
36
* PMA_getNonMatchingTargetTables() places tables present
37
* in target database but are absent from source database
39
* @param $trg_tables array of target database table names,
41
* @param $matching_tables $matching tables array containing names of matching tables,
42
* @param &$uncommon_target_tables empty array passed by reference to save names of tables presnet in
43
* target database but absent from source database
46
function PMA_getNonMatchingTargetTables($trg_tables, $matching_tables, &$uncommon_target_tables)
48
for($c=0; $c<sizeof($trg_tables) ;$c++) {
50
for($d=0; $d < sizeof($matching_tables); $d++)
52
if ($trg_tables[$c] === $matching_tables[$d]) {
56
if ($match === false) {
57
$uncommon_target_tables[] = $trg_tables[$c];
63
* PMA_dataDiffInTables() finds the difference in source and target matching tables by
64
* first comparing source table's primary key entries with target table enteries.
65
* It gets the field names for the matching table also for comparisons.
66
* If the entry is found in target table also then it is checked for the remaining
67
* field values also, in order to check whether update is required or not.
68
* If update is required, it is placed in $update_array
69
* Otherwise that entry is placed in the $insert_array.
71
* @uses PMA_DBI_get_fields()
72
* @uses PMA_DBI_get_column_values()
73
* @uses PMA_DBI_fetch_result()
75
* @param $src_db name of source database
76
* @param $trg_db name of target database
77
* @param $src_link connection established with source server
78
* @param $trg_link connection established with target server
79
* @param $index Index of a table from $matching_table array
81
* @param $update_array A three dimensional array passed by reference to
82
* contain updates required for each matching table
83
* @param $insert_array A three dimensional array passed by reference to
84
* contain inserts required for each matching table
85
* @param $fields_num A two dimensional array passed by reference to
86
* contain number of fields for each matching table
87
* @param $matching_table array containing matching table names
89
* @param $matching_tables_fields A two dimensional array passed by reference to contain names of fields for each matching table
91
* @param $matching_tables_keys A two dimensional array passed by reference to contain names of keys for each matching table
93
function PMA_dataDiffInTables($src_db, $trg_db, $src_link, $trg_link, &$matching_table, &$matching_tables_fields,
94
&$update_array, &$insert_array, &$delete_array, &$fields_num, $matching_table_index, &$matching_tables_keys)
96
if (isset($matching_table[$matching_table_index])) {
98
$fld_results = PMA_DBI_get_fields($src_db, $matching_table[$matching_table_index], $src_link);
100
if (isset($fld_results)) {
101
foreach ($fld_results as $each_field) {
102
$field_name = $each_field['Field'];
103
if ($each_field['Key'] == 'PRI') {
104
$is_key[] = $field_name;
106
$fld[] = $field_name;
109
$matching_tables_fields[$matching_table_index] = $fld;
110
$fields_num[$matching_table_index] = sizeof($fld);
111
$matching_tables_keys[$matching_table_index] = $is_key;
113
$source_result_set = PMA_DBI_get_column_values($src_db, $matching_table[$matching_table_index], $is_key, $src_link);
114
$source_size = sizeof($source_result_set);
116
$trg_fld_results = PMA_DBI_get_fields($trg_db, $matching_table[$matching_table_index], $trg_link);
117
$all_keys_match = true;
120
if (isset($trg_fld_results)) {
121
foreach ($trg_fld_results as $each_field) {
122
if ($each_field['Key'] == 'PRI') {
123
$trg_keys[] = $each_field['Field'];
124
if (! (in_array($each_field['Field'], $is_key))) {
125
$all_keys_match = false;
136
for ($j = 0; $j < $source_size; $j++) {
140
if (isset($source_result_set[$j]) && ($all_keys_match)) {
142
// Query the target server to see which rows already exist
143
$trg_select_query = "SELECT * FROM " . PMA_backquote($trg_db) . "."
144
. PMA_backquote($matching_table[$matching_table_index]) . " WHERE ";
146
if (sizeof($is_key) == 1) {
147
$trg_select_query .= $is_key[0]. "='" . $source_result_set[$j] . "'";
148
} elseif (sizeof($is_key) > 1){
149
for ($k=0; $k < sizeof($is_key); $k++) {
150
$trg_select_query .= $is_key[$k] . "='" . $source_result_set[$j][$is_key[$k]] . "'";
151
if ($k < (sizeof($is_key)-1)){
152
$trg_select_query .= " AND ";
157
$target_result_set = PMA_DBI_fetch_result($trg_select_query, null, null, $trg_link);
158
if ($target_result_set) {
160
// Fetch the row from the source server to do a comparison
161
$src_select_query = "SELECT * FROM " . PMA_backquote($src_db) . "."
162
. PMA_backquote($matching_table[$matching_table_index]) . " WHERE ";
164
if (sizeof($is_key) == 1) {
165
$src_select_query .= $is_key[0] . "='" . $source_result_set[$j] . "'";
166
} else if(sizeof($is_key) > 1){
167
for ($k=0; $k< sizeof($is_key); $k++) {
168
$src_select_query .= $is_key[$k] . "='" . $source_result_set[$j][$is_key[$k]] . "'";
169
if ($k < (sizeof($is_key) - 1)){
170
$src_select_query .= " AND ";
175
$src_result_set = PMA_DBI_fetch_result($src_select_query, null, null, $src_link);
178
* Comparing each corresponding field of the source and target matching rows.
179
* Placing the primary key, value of primary key, field to be updated, and the
180
* new value of field to be updated in each row of the update array.
182
for ($m = 0; ($m < $fields_num[$matching_table_index]) && ($starting_index == 0) ; $m++) {
183
if (isset($src_result_set[0][$fld[$m]])) {
184
if (isset($target_result_set[0][$fld[$m]])) {
185
if (($src_result_set[0][$fld[$m]] != $target_result_set[0][$fld[$m]]) && (! (in_array($fld[$m], $is_key)))) {
186
if (sizeof($is_key) == 1) {
187
if ($source_result_set[$j]) {
188
$update_array[$matching_table_index][$update_row][$is_key[0]] = $source_result_set[$j];
190
} elseif (sizeof($is_key) > 1) {
191
for ($n=0; $n < sizeof($is_key); $n++) {
192
if (isset($src_result_set[0][$is_key[$n]])) {
193
$update_array[$matching_table_index][$update_row][$is_key[$n]] = $src_result_set[0][$is_key[$n]];
198
$update_array[$matching_table_index][$update_row][$update_field] = $fld[$m];
201
if (isset($src_result_set[0][$fld[$m]])) {
202
$update_array[$matching_table_index][$update_row][$update_field] = $src_result_set[0][$fld[$m]];
205
$starting_index = $m;
209
if (sizeof($is_key) == 1) {
210
if ($source_result_set[$j]) {
211
$update_array[$matching_table_index][$update_row][$is_key[0]] = $source_result_set[$j];
214
} elseif (sizeof($is_key) > 1) {
215
for ($n = 0; $n < sizeof($is_key); $n++) {
216
if (isset($src_result_set[0][$is_key[$n]])) {
217
$update_array[$matching_table_index][$update_row][$is_key[$n]] = $src_result_set[0][$is_key[$n]];
222
$update_array[$matching_table_index][$update_row][$update_field] = $fld[$m];
225
if (isset($src_result_set[0][$fld[$m]])) {
226
$update_array[$matching_table_index][$update_row][$update_field] = $src_result_set[0][$fld[$m]];
229
$starting_index = $m;
234
for ($m = $starting_index + 1; $m < $fields_num[$matching_table_index] ; $m++)
236
if (isset($src_result_set[0][$fld[$m]])) {
237
if (isset($target_result_set[0][$fld[$m]])) {
238
if (($src_result_set[0][$fld[$m]] != $target_result_set[0][$fld[$m]]) && (!(in_array($fld[$m], $is_key)))) {
240
$update_array[$matching_table_index][$update_row][$update_field] = $fld[$m];
242
if ($src_result_set[0][$fld[$m]]) {
243
$update_array[$matching_table_index][$update_row][$update_field] = $src_result_set[0][$fld[$m]];
250
$update_array[$matching_table_index][$update_row][$update_field] = $fld[$m];
252
if ($src_result_set[0][$fld[$m]]) {
253
$update_array[$matching_table_index][$update_row][$update_field] = $src_result_set[0][$fld[$m]];
262
* Placing the primary key, and the value of primary key of the row that is to be inserted in the target table
264
if (sizeof($is_key) == 1) {
265
if (isset($source_result_set[$j])) {
266
$insert_array[$matching_table_index][$insert_row][$is_key[0]] = $source_result_set[$j];
268
} elseif (sizeof($is_key) > 1) {
269
for($l = 0; $l < sizeof($is_key); $l++) {
270
if (isset($source_result_set[$j][$matching_tables_fields[$matching_table_index][$l]])) {
271
$insert_array[$matching_table_index][$insert_row][$is_key[$l]] = $source_result_set[$j][$matching_tables_fields[$matching_table_index][$l]];
279
* Placing the primary key, and the value of primary key of the row that is to be inserted in the target table
280
* This condition is met when there is an additional column in the source table
282
if (sizeof($is_key) == 1) {
283
if (isset($source_result_set[$j])) {
284
$insert_array[$matching_table_index][$insert_row][$is_key[0]] = $source_result_set[$j];
286
} elseif (sizeof($is_key) > 1) {
287
for ($l = 0; $l < sizeof($is_key); $l++) {
288
if (isset($source_result_set[$j][$matching_tables_fields[$matching_table_index][$l]])) {
289
$insert_array[$matching_table_index][$insert_row][$is_key[$l]] = $source_result_set[$j][$matching_tables_fields[$matching_table_index][$l]];
299
* PMA_findDeleteRowsFromTargetTables finds the rows which are to be deleted from target table.
301
* @uses PMA_DBI_get_column_values()
304
* @param $delete_array array containing rows that are to be deleted
305
* @param $matching_table array containing matching table names
306
* @param $matching_table_index index of a table from $matching_table array
307
* @param $trg_keys array of target table keys
308
* @param $src_keys array of source table keys
309
* @param $trg_db name of target database
310
* @param $trg_link connection established with target server
311
* @param $src_db name of source database
312
* @param $src_link connection established with source server
315
function PMA_findDeleteRowsFromTargetTables(&$delete_array, $matching_table, $matching_table_index, $trg_keys, $src_keys, $trg_db, $trg_link,$src_db, $src_link)
317
if (isset($trg_keys[$matching_table_index])) {
318
$target_key_values = PMA_DBI_get_column_values($trg_db, $matching_table[$matching_table_index], $trg_keys[$matching_table_index], $trg_link);
319
$target_row_size = sizeof($target_key_values);
321
if (isset($src_keys[$matching_table_index])) {
322
$source_key_values = PMA_DBI_get_column_values($src_db, $matching_table[$matching_table_index], $src_keys[$matching_table_index], $src_link);
323
$source_size = sizeof($source_key_values);
326
for ($a = 0; $a < sizeof($trg_keys[$matching_table_index]); $a++) {
327
if (isset($trg_keys[$matching_table_index][$a])) {
328
if (! (in_array($trg_keys[$matching_table_index][$a], $src_keys[$matching_table_index]))) {
333
if (! ($all_keys_match)) {
334
if (isset($target_key_values)) {
335
$delete_array[$matching_table_index] = $target_key_values;
338
if (isset($trg_keys[$matching_table_index])) {
339
if ((sizeof($trg_keys[$matching_table_index]) == 1) && $all_keys_match) {
341
if (isset($target_key_values)) {
342
for ($i = 0; $i < sizeof($target_key_values); $i++) {
343
if (! (in_array($target_key_values[$i], $source_key_values))) {
344
$delete_array[$matching_table_index][$row] = $target_key_values[$i];
349
} elseif ((sizeof($trg_keys[$matching_table_index]) > 1) && $all_keys_match) {
351
if (isset($target_key_values)) {
352
for ($i = 0; $i < sizeof($target_key_values); $i++) {
354
for ($j = 0; $j < sizeof($source_key_values) && ($is_present == false) ; $j++) {
356
for ($k = 0; $k < sizeof($trg_keys[$matching_table_index]); $k++) {
357
if ($target_key_values[$i][$trg_keys[$matching_table_index][$k]] != $source_key_values[$j][$trg_keys[$matching_table_index][$k]]) {
365
if (! ($is_present)) {
366
for ($l = 0; $l < sizeof($trg_keys[$matching_table_index]); $l++) {
367
$delete_array[$matching_table_index][$row][$trg_keys[$matching_table_index][$l]] = $target_key_values[$i][$trg_keys[$matching_table_index][$l]];
378
* PMA_dataDiffInUncommonTables() finds the data difference in $source_tables_uncommon
379
* @uses PMA_DBI_fetch_result()
381
* @param $source_tables_uncommon array of table names; containing table names that are in source db and not in target db
382
* @param $src_db name of source database
383
* @param $src_link connection established with source server
384
* @param $index index of a table from $matching_table array
385
* @param $row_count number of rows
388
function PMA_dataDiffInUncommonTables($source_tables_uncommon, $src_db, $src_link, $index, &$row_count)
390
$query = "SELECT COUNT(*) FROM " . PMA_backquote($src_db) . "." . PMA_backquote($source_tables_uncommon[$index]);
391
$rows = PMA_DBI_fetch_result($query, null, null, $src_link);
392
$row_count[$index] = $rows[0];
396
* PMA_updateTargetTables() sets the updated field values to target table rows using $update_array[$matching_table_index]
398
* @uses PMA_DBI_fetch_result()
399
* @uses PMA_backquote()
401
* @param $table Array containing matching tables' names
402
* @param $update_array A three dimensional array containing field
403
* value updates required for each matching table
404
* @param $src_db Name of source database
405
* @param $trg_db Name of target database
406
* @param $trg_link Connection established with target server
407
* @param $matching_table_index index of matching table in matching_table_array
408
* @param $display true/false value
411
function PMA_updateTargetTables($table, $update_array, $src_db, $trg_db, $trg_link, $matching_table_index, $matching_table_keys, $display)
413
if (isset($update_array[$matching_table_index])) {
414
if (sizeof($update_array[$matching_table_index])) {
416
for ($update_row = 0; $update_row < sizeof($update_array[$matching_table_index]); $update_row++) {
418
if (isset($update_array[$matching_table_index][$update_row])) {
419
$update_fields_num = sizeof($update_array[$matching_table_index][$update_row])-sizeof($matching_table_keys[$matching_table_index]);
420
if ($update_fields_num > 0) {
421
$query = "UPDATE " . PMA_backquote($trg_db) . "." .PMA_backquote($table[$matching_table_index]) . " SET ";
423
for ($update_field = 0; $update_field < $update_fields_num; $update_field = $update_field+2) {
424
if (isset($update_array[$matching_table_index][$update_row][$update_field]) && isset($update_array[$matching_table_index][$update_row][$update_field+1])) {
425
$query .= $update_array[$matching_table_index][$update_row][$update_field] . "='" . $update_array[$matching_table_index][$update_row][$update_field+1] . "'";
427
if ($update_field < ($update_fields_num - 2)) {
432
if (isset($matching_table_keys[$matching_table_index])) {
433
for ($key = 0; $key < sizeof($matching_table_keys[$matching_table_index]); $key++)
435
if (isset($matching_table_keys[$matching_table_index][$key])) {
437
$query .= $matching_table_keys[$matching_table_index][$key] . "='" . $update_array[$matching_table_index][$update_row][$matching_table_keys[$matching_table_index][$key]] . "'";
439
if ($key < (sizeof($matching_table_keys[$matching_table_index]) - 1)) {
444
if ($display == true) {
445
echo "<p>" . $query . "</p>";
447
PMA_DBI_try_query($query, $trg_link, 0);
455
* PMA_insertIntoTargetTable() inserts missing rows in the target table using $array_insert[$matching_table_index]
457
* @uses PMA_DBI_fetch_result()
458
* @uses PMA_backquote()
461
* @param $matching_table array containing matching table names
462
* @param $src_db name of source database
463
* @param $trg_db name of target database
464
* @param $src_link connection established with source server
465
* @param $trg_link connection established with target server
466
* @param $table_fields array containing field names of a table
467
* @param $array_insert
468
* @param $matching_table_index index of matching table in matching_table_array
469
* @param $matching_tables_keys array containing field names that are keys in the matching table
470
* @param $source_columns array containing source column information
471
* @param $add_column_array array containing column names that are to be added in target table
472
* @param $criteria array containing criterias like type, null, collation, default etc
473
* @param $target_tables_keys array containing field names that are keys in the target table
474
* @param $uncommon_tables array containing table names that are present in source db but not in targt db
475
* @param $uncommon_tables_fields array containing field names of the uncommon tables
476
* @param $uncommon_cols column names that are present in target table and not in source table
477
* @param $alter_str_array array containing column names that are to be altered
478
* @param $source_indexes column names on which indexes are made in source table
479
* @param $target_indexes column names on which indexes are made in target table
480
* @param $add_indexes_array array containing column names on which index is to be added in target table
481
* @param $alter_indexes_array array containing column names whose indexes are to be altered. Only index name and uniqueness of an index can be changed
482
* @param $delete_array array containing rows that are to be deleted
483
* @param $update_array array containing rows that are to be updated in target
484
* @param $display true/false value
487
function PMA_insertIntoTargetTable($matching_table, $src_db, $trg_db, $src_link, $trg_link, $table_fields, &$array_insert, $matching_table_index,
488
$matching_tables_keys, $source_columns, &$add_column_array, $criteria, $target_tables_keys, $uncommon_tables, &$uncommon_tables_fields,$uncommon_cols,
489
&$alter_str_array,&$source_indexes, &$target_indexes, &$add_indexes_array, &$alter_indexes_array, &$delete_array, &$update_array, $display)
491
if(isset($array_insert[$matching_table_index])) {
492
if (sizeof($array_insert[$matching_table_index])) {
493
for ($insert_row = 0; $insert_row< sizeof($array_insert[$matching_table_index]); $insert_row++) {
494
if (isset($array_insert[$matching_table_index][$insert_row][$matching_tables_keys[$matching_table_index][0]])) {
496
$select_query = "SELECT * FROM " . PMA_backquote($src_db) . "." . PMA_backquote($matching_table[$matching_table_index]) . " WHERE ";
497
for ($i = 0; $i < sizeof($matching_tables_keys[$matching_table_index]); $i++) {
498
$select_query .= $matching_tables_keys[$matching_table_index][$i] . "='";
499
$select_query .= $array_insert[$matching_table_index][$insert_row][$matching_tables_keys[$matching_table_index][$i]] . "'" ;
501
if ($i < (sizeof($matching_tables_keys[$matching_table_index]) - 1)) {
502
$select_query.= " AND ";
505
$select_query .= "; ";
506
$result = PMA_DBI_fetch_result ($select_query, null, null, $src_link);
507
$insert_query = "INSERT INTO " . PMA_backquote($trg_db) . "." . PMA_backquote($matching_table[$matching_table_index]) ." (";
509
for ($field_index = 0; $field_index < sizeof($table_fields[$matching_table_index]); $field_index++)
511
$insert_query .= $table_fields[$matching_table_index][$field_index];
513
$is_fk_query = "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '" . $trg_db ."'
514
AND TABLE_NAME = '" . $matching_table[$matching_table_index]. "'AND COLUMN_NAME = '" .
515
$table_fields[$matching_table_index][$field_index] . "' AND TABLE_NAME <> REFERENCED_TABLE_NAME;" ;
517
$is_fk_result = PMA_DBI_fetch_result($is_fk_query, null, null, $trg_link);
518
if (sizeof($is_fk_result) > 0) {
519
for ($j = 0; $j < sizeof($is_fk_result); $j++)
521
$table_index = array_keys($matching_table, $is_fk_result[$j]['REFERENCED_TABLE_NAME']);
523
if (isset($alter_str_array[$table_index[0]])) {
524
PMA_alterTargetTableStructure($trg_db, $trg_link, $matching_tables, $source_columns, $alter_str_array, $matching_tables_fields,
525
$criteria, $matching_tables_keys, $target_tables_keys, $table_index[0], $display);
526
unset($alter_str_array[$table_index[0]]);
528
if (isset($uncommon_columns[$table_index[0]])) {
529
PMA_removeColumnsFromTargetTable($trg_db, $trg_link, $matching_tables, $uncommon_columns, $table_index[0], $display);
530
unset($uncommon_columns[$table_index[0]]);
532
if (isset($add_column_array[$table_index[0]])) {
533
PMA_findDeleteRowsFromTargetTables($delete_array, $matching_tables, $table_index[0], $target_tables_keys, $matching_tables_keys,
534
$trg_db, $trg_link, $src_db, $src_link);
536
if (isset($delete_array[$table_index[0]])) {
537
PMA_deleteFromTargetTable($trg_db, $trg_link, $matching_tables, $table_index[0], $target_tables_keys, $delete_array, $display);
538
unset($delete_array[$table_index[0]]);
540
PMA_addColumnsInTargetTable($src_db, $trg_db, $src_link, $trg_link, $matching_tables, $source_columns, $add_column_array,
541
$matching_tables_fields, $criteria, $matching_tables_keys, $target_tables_keys, $uncommon_tables,$uncommon_tables_fields,
542
$table_index[0], $uncommon_cols, $display);
543
unset($add_column_array[$table_index[0]]);
545
if (isset($add_indexes_array[$table_index[0]]) || isset($remove_indexes_array[$table_index[0]])
546
|| isset($alter_indexes_array[$table_index[0]])) {
547
PMA_applyIndexesDiff ($trg_db, $trg_link, $matching_tables, $source_indexes, $target_indexes, $add_indexes_array, $alter_indexes_array,
548
$remove_indexes_array, $table_index[0], $display);
550
unset($add_indexes_array[$table_index[0]]);
551
unset($alter_indexes_array[$table_index[0]]);
552
unset($remove_indexes_array[$table_index[0]]);
554
if (isset($update_array[$table_index[0]])) {
555
PMA_updateTargetTables($matching_tables, $update_array, $src_db, $trg_db, $trg_link, $table_index[0], $matching_table_keys,
557
unset($update_array[$table_index[0]]);
559
if (isset($array_insert[$table_index[0]])) {
560
PMA_insertIntoTargetTable($matching_table, $src_db, $trg_db, $src_link, $trg_link, $table_fields, $array_insert,
561
$table_index[0], $matching_tables_keys, $source_columns, $add_column_array, $criteria, $target_tables_keys, $uncommon_tables,
562
$uncommon_tables_fields, $uncommon_cols, $alter_str_array, $source_indexes, $target_indexes, $add_indexes_array,
563
$alter_indexes_array, $delete_array, $update_array, $display);
564
unset($array_insert[$table_index[0]]);
568
if ($field_index < sizeof($table_fields[$matching_table_index])-1) {
569
$insert_query .= ", ";
572
$insert_query .= ") VALUES(";
573
if (sizeof($table_fields[$matching_table_index]) == 1) {
574
$insert_query .= "'" . $result[0] . "'";
576
for ($field_index = 0; $field_index < sizeof($table_fields[$matching_table_index]); $field_index++) {
577
if (isset($result[0][$table_fields[$matching_table_index][$field_index]])) {
578
$insert_query .= "'" . $result[0][$table_fields[$matching_table_index][$field_index]] . "'";
580
$insert_query .= "'NULL'";
582
if ($field_index < (sizeof($table_fields[$matching_table_index])) - 1) {
583
$insert_query .= " ," ;
587
$insert_query .= ");";
588
if ($display == true) {
589
PMA_displayQuery($insert_query);
591
PMA_DBI_try_query($insert_query, $trg_link, 0);
598
* PMA_createTargetTables() Create the missing table $uncommon_table in target database
600
* @uses PMA_DBI_get_fields()
601
* @uses PMA_backquote()
602
* @uses PMA_DBI_fetch_result()
604
* @param $src_db name of source database
605
* @param $trg_db name of target database
606
* @param $trg_link connection established with target server
607
* @param $src_link connection established with source server
608
* @param $uncommon_table name of table present in source but not in target
609
* @param $table_index index of table in matching_table_array
610
* @param $uncommon_tables_fields field names of the uncommon table
611
* @param $display true/false value
613
function PMA_createTargetTables($src_db, $trg_db, $src_link, $trg_link, &$uncommon_tables, $table_index, &$uncommon_tables_fields, $display)
615
if (isset($uncommon_tables[$table_index])) {
616
$fields_result = PMA_DBI_get_fields($src_db, $uncommon_tables[$table_index], $src_link);
618
foreach ($fields_result as $each_field) {
619
$field_name = $each_field['Field'];
620
$fields[] = $field_name;
622
$uncommon_tables_fields[$table_index] = $fields;
624
$Create_Query = PMA_DBI_fetch_value("SHOW CREATE TABLE " . PMA_backquote($src_db) . '.' . PMA_backquote($uncommon_tables[$table_index]), 0, 1, $src_link);
626
// Replace the src table name with a `dbname`.`tablename`
627
$Create_Table_Query = preg_replace('/' . PMA_backquote($uncommon_tables[$table_index]) . '/',
628
PMA_backquote($trg_db) . '.' .PMA_backquote($uncommon_tables[$table_index]),
633
$is_fk_query = "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '" . $src_db . "'
634
AND TABLE_NAME = '" . $uncommon_tables[$table_index] . "' AND TABLE_NAME <> REFERENCED_TABLE_NAME;" ;
636
$is_fk_result = PMA_DBI_fetch_result($is_fk_query, null, null, $src_link);
637
if (sizeof($is_fk_result) > 0) {
638
for ($j = 0; $j < sizeof($is_fk_result); $j++)
640
if (in_array($is_fk_result[$j]['REFERENCED_TABLE_NAME'], $uncommon_tables)) {
641
$table_index = array_keys($uncommon_tables, $is_fk_result[$j]['REFERENCED_TABLE_NAME']);
642
PMA_createTargetTables($src_db, $trg_db, $trg_link, $src_link, $uncommon_tables, $table_index[0], $uncommon_tables_fields, $display);
643
unset($uncommon_tables[$table_index[0]]);
647
if ($display == true) {
648
echo '<p>' . $Create_Table_Query . '</p>';
650
PMA_DBI_try_query($Create_Table_Query, $trg_link, 0);
654
* PMA_populateTargetTables() inserts data into uncommon tables after they have been created
655
* @uses PMA_DBI_fetch_result()
656
* @uses PMA_backquote()
658
* @uses PMA_DBI_try_query()
660
* @param $src_db name of source database
661
* @param $trg_db name of target database
662
* @param $src_link connection established with source server
663
* @param $trg_link connection established with target server
664
* @param $uncommon_tables array containing uncommon table names (table names that are present in source but not in target db)
665
* @param $table_index index of table in matching_table_array
666
* @param $uncommon_tables_fields field names of the uncommon table
667
* @param $display true/false value
669
* FIXME: This turns NULL values into '' (empty string)
671
function PMA_populateTargetTables($src_db, $trg_db, $src_link, $trg_link, $uncommon_tables, $table_index, $uncommon_tables_fields, $display)
673
$display = false; // todo: maybe display some of the queries if they are not too numerous
675
$unbuffered_result = PMA_DBI_try_query('SELECT * FROM ' . PMA_backquote($src_db) . '.' . PMA_backquote($uncommon_tables[$table_index]), $src_link, PMA_DBI_QUERY_UNBUFFERED);
676
if (false !== $unbuffered_result) {
677
while ($one_row = PMA_DBI_fetch_row($unbuffered_result)) {
678
$insert_query = 'INSERT INTO ' . PMA_backquote($trg_db) . '.' .PMA_backquote($uncommon_tables[$table_index]) . ' VALUES(';
679
$key_of_last_value = count($one_row) - 1;
680
foreach($one_row as $key => $value) {
681
$insert_query .= "'" . PMA_sqlAddslashes($value) . "'";
682
if ($key < $key_of_last_value) {
683
$insert_query .= ",";
686
$insert_query .= ');';
687
if ($display == true) {
688
PMA_displayQuery($insert_query);
690
PMA_DBI_try_query($insert_query, $trg_link, 0);
695
* PMA_deleteFromTargetTable() delete rows from target table
697
* @uses PMA_backquote()
698
* @uses PMA_DBI_try_query()
701
* @param $trg_db name of target database
702
* @param $trg_link connection established with target server
703
* @param $matching_tables array containing matching table names
704
* @param $table_index index of table in matching_table_array
705
* @param $target_table_keys primary key names of the target tables
706
* @param $delete array array containing the key values of rows that are to be deleted
707
* @param $display true/false value
709
function PMA_deleteFromTargetTable($trg_db, $trg_link, $matching_tables, $table_index, $target_tables_keys, $delete_array, $display)
711
for($i = 0; $i < sizeof($delete_array[$table_index]); $i++) {
712
if (isset($target_tables_keys[$table_index])) {
713
$delete_query = 'DELETE FROM ' . PMA_backquote($trg_db) . '.' .PMA_backquote($matching_tables[$table_index]) . ' WHERE ';
714
for($y = 0; $y < sizeof($target_tables_keys[$table_index]); $y++) {
715
$delete_query .= $target_tables_keys[$table_index][$y] . " = '";
717
if (sizeof($target_tables_keys[$table_index]) == 1) {
718
$delete_query .= $delete_array[$table_index][$i] . "'";
719
} elseif (sizeof($target_tables_keys[$table_index]) > 1) {
720
$delete_query .= $delete_array[$table_index][$i][$target_tables_keys[$table_index][$y]] . "'";
722
if ($y < (sizeof($target_tables_keys[$table_index]) - 1)) {
723
$delete_query .= ' AND ';
725
$pk_query = "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = '" . $trg_db . "'
726
AND REFERENCED_TABLE_NAME = '" . $matching_tables[$table_index]."' AND REFERENCED_COLUMN_NAME = '"
727
. $target_tables_keys[$table_index][$y] . "' AND TABLE_NAME <> REFERENCED_TABLE_NAME;";
729
$pk_query_result = PMA_DBI_fetch_result($pk_query, null, null, $trg_link);
730
$result_size = sizeof($pk_query_result);
732
if ($result_size > 0) {
733
for ($b = 0; $b < $result_size; $b++) {
734
$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] . ";";
735
PMA_DBI_try_query($drop_pk_query, $trg_link, 0);
740
if ($display == true) {
741
echo '<p>' . $delete_query . '</p>';
743
PMA_DBI_try_query($delete_query, $trg_link, 0);
747
* PMA_structureDiffInTables() Gets all the column information for source and target table.
748
* Compare columns on their names.
749
* If column exists in target then compare Type, Null, Collation, Key, Default and Comment for that column.
750
* If column does not exist in target table then it is placed in $add_column_array.
751
* If column exists in target table but criteria is different then it is palced in $alter_str_array.
752
* If column does not exist in source table but is present in target table then it is placed in $uncommon_columns.
753
* Keys for all the source tables that have a corresponding target table are placed in $matching_tables_keys.
754
* Keys for all the target tables that have a corresponding source table are placed in $target_tables_keys.
756
* @uses PMA_DBI_get_columns_full()
759
* @param $src_db name of source database
760
* @param $trg_db name of target database
761
* @param $src_link connection established with source server
762
* @param $trg_link connection established with target server
763
* @param $matching_tables array containing names of matching tables
764
* @param $source_columns array containing columns information of the source tables
765
* @param $target_columns array containing columns information of the target tables
766
* @param $alter_str_array three dimensional associative array first index being the matching table index, second index being column name for which target
767
* column have some criteria different and third index containing the criteria which is different.
768
* @param $add_column_array two dimensional associative array, first index of the array contain the matching table number and second index contain the
769
* column name which is to be added in the target table
770
* @param $uncommon_columns array containing the columns that are present in the target table but not in the source table
771
* @param $criteria array containing the criterias which are to be checked for field that is present in source table and target table
772
* @param $target_tables_keys array containing the field names which is key in the target table
773
* @param $matching_table_index integer number of the matching table
776
function PMA_structureDiffInTables($src_db, $trg_db, $src_link, $trg_link, $matching_tables, &$source_columns, &$target_columns, &$alter_str_array,
777
&$add_column_array, &$uncommon_columns, $criteria, &$target_tables_keys, $matching_table_index)
779
//Gets column information for source and target table
780
$source_columns[$matching_table_index] = PMA_DBI_get_columns_full($src_db, $matching_tables[$matching_table_index], null, $src_link);
781
$target_columns[$matching_table_index] = PMA_DBI_get_columns_full($trg_db, $matching_tables[$matching_table_index], null, $trg_link);
782
foreach ($source_columns[$matching_table_index] as $column_name => $each_column) {
783
if (isset($target_columns[$matching_table_index][$column_name]['Field'])) {
784
//If column exists in target table then matches criterias like type, null, collation, key, default, comment of the column
785
for ($i = 0; $i < sizeof($criteria); $i++) {
786
if ($source_columns[$matching_table_index][$column_name][$criteria[$i]] != $target_columns[$matching_table_index][$column_name][$criteria[$i]]) {
787
if (($criteria[$i] == 'Default') && ($source_columns[$matching_table_index][$column_name][$criteria[$i]] == '' )) {
788
$alter_str_array[$matching_table_index][$column_name][$criteria[$i]] = 'None';
790
if (! (($criteria[$i] == 'Key') && (($source_columns[$matching_table_index][$column_name][$criteria[$i]] == 'MUL')
791
|| ($target_columns[$matching_table_index][$column_name][$criteria[$i]] == 'MUL')
792
|| ($source_columns[$matching_table_index][$column_name][$criteria[$i]] == 'UNI')
793
|| ($target_columns[$matching_table_index][$column_name][$criteria[$i]] == 'UNI')))) {
794
$alter_str_array[$matching_table_index][$column_name][$criteria[$i]] = $source_columns[$matching_table_index][$column_name][$criteria[$i]];
800
$add_column_array[$matching_table_index][$column_name]= $column_name;
803
//Finds column names that are present in target table but not in source table
804
foreach ($target_columns[$matching_table_index] as $fld_name => $each_column) {
805
if (! (isset($source_columns[$matching_table_index][$fld_name]['Field']))) {
806
$fields_uncommon[] = $fld_name;
808
if ($target_columns[$matching_table_index][$fld_name]['Key'] == 'PRI') {
812
if (isset($fields_uncommon)) {
813
$uncommon_columns[$matching_table_index] = $fields_uncommon;
816
$target_tables_keys[$matching_table_index] = $keys;
820
* PMA_addColumnsInTargetTable() adds column that are present in source table but not in target table
824
* @uses PMA_checkForeignKeys()
825
* @uses PMA_createTargetTables()
826
* @uses PMA_DBI_try_query()
827
* @uses PMA_DBI_fetch_result()
829
* @param $src_db name of source database
830
* @param $trg_db name of target database
831
* @param $src_link connection established with source server
832
* @param $trg_link connection established with target server
833
* @param $matching_tables array containing names of matching tables
834
* @param $source_columns array containing columns information of the source tables
835
* @param $add_column_array array containing the names of the column(field) that are to be added in the target
836
* @param $matching_tables_fields
837
* @param $criteria array containing the criterias
838
* @param $matching_tables_keys array containing the field names which is key in the source table
839
* @param $target_tables_keys array containing the field names which is key in the target table
840
* @param $uncommon_tables array containing the table names that are present in source db and not in target db
841
* @param $uncommon_tables_fields array containing the names of the fields of the uncommon tables
842
* @param $table_counter integer number of the matching table
843
* @param $uncommon_cols
844
* @param $display true/false value
846
function PMA_addColumnsInTargetTable($src_db, $trg_db, $src_link, $trg_link, $matching_tables, $source_columns, &$add_column_array, $matching_tables_fields,
847
$criteria, $matching_tables_keys, $target_tables_keys, $uncommon_tables, &$uncommon_tables_fields, $table_counter, $uncommon_cols, $display)
849
for ($i = 0; $i < sizeof($matching_tables_fields[$table_counter]); $i++) {
850
if (isset($add_column_array[$table_counter][$matching_tables_fields[$table_counter][$i]])) {
851
$query = "ALTER TABLE " . PMA_backquote($trg_db) . '.' . PMA_backquote($matching_tables[$table_counter]). " ADD COLUMN " .
852
$add_column_array[$table_counter][$matching_tables_fields[$table_counter][$i]] . " " . $source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Type'];
854
if($source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Null'] == 'NO') {
855
$query .= ' Not Null ';
856
} elseif ($source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Null'] == 'YES') {
859
if ($source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Collation'] != '') {
860
$query .= ' COLLATE ' . $source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Collation'];
862
if ($source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Default'] != '') {
863
$query .= " DEFAULT " . $source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Default'];
865
if ($source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Comment'] != '') {
866
$query .= " COMMENT " . $source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Comment'];
868
if ($source_columns[$table_counter][$matching_tables_fields[$table_counter][$i]]['Key'] == 'PRI' ) {
869
$trg_key_size = sizeof($target_tables_keys[$table_counter]);
872
for ($a = 0; ($a < $trg_key_size) && ($check); $a++) {
873
if (! (in_array($target_tables_keys[$table_counter], $uncommon_cols))) {
878
$query .= " ,DROP PRIMARY KEY " ;
881
$query .= " , ADD PRIMARY KEY (";
882
for ($t = 0; $t < sizeof($matching_tables_keys[$table_counter]); $t++) {
883
$query .= $matching_tables_keys[$table_counter][$t];
884
if ($t < (sizeof($matching_tables_keys[$table_counter]) - 1)) {
892
if ($display == true) {
893
echo '<p>' . $query . '</p>';
895
PMA_DBI_try_query($query, $trg_link, 0);
897
//Checks if column to be added is a foreign key or not
898
$is_fk_query = "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '" . $trg_db . "' AND TABLE_NAME = '"
899
. $matching_tables[$table_counter] . "' AND COLUMN_NAME ='" . $add_column_array[$table_counter][$matching_tables_fields[$table_counter][$i]] .
900
"' AND TABLE_NAME <> REFERENCED_TABLE_NAME;";
902
$is_fk_result = PMA_DBI_fetch_result($is_fk_query, null, null, $src_link);
904
//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
905
//it is created first.
906
if (isset($is_fk_result)) {
907
if (in_array($is_fk_result[0]['REFERENCED_TABLE_NAME'], $uncommon_tables)) {
908
$table_index = array_keys($uncommon_tables, $is_fk_result[0]['REFERENCED_TABLE_NAME']);
909
PMA_checkForeignKeys($src_db, $src_link, $trg_db, $trg_link, $is_fk_result[0]['REFERENCED_TABLE_NAME'], $uncommon_tables, $uncommon_tables_fields);
910
PMA_createTargetTables($src_db, $trg_db, $trg_link, $src_link, $uncommon_tables, $table_index[0], $uncommon_tables_fields);
911
unset($uncommon_tables[$table_index[0]]);
913
$fk_query = "ALTER TABLE " . PMA_backquote($trg_db) . '.' . PMA_backquote($matching_tables[$table_counter]) .
914
"ADD CONSTRAINT FOREIGN KEY " . $add_column_array[$table_counter][$matching_tables_fields[$table_counter][$i]] . "
915
(" . $add_column_array[$table_counter][$matching_tables_fields[$table_counter][$i]] . ") REFERENCES " . PMA_backquote($trg_db) .
916
'.' . PMA_backquote($is_fk_result[0]['REFERENCED_TABLE_NAME']) . " (" . $is_fk_result[0]['REFERENCED_COLUMN_NAME'] . ");";
918
PMA_DBI_try_query($fk_query, $trg_link, null);
924
* PMA_checkForeignKeys() checks if the referenced table have foreign keys.
928
* @uses PMA_checkForeignKeys()
929
* uses PMA_createTargetTables()
931
* @param $src_db name of source database
932
* @param $src_link connection established with source server
933
* @param $trg_db name of target database
934
* @param $trg_link connection established with target server
935
* @param $referenced_table table whose column is a foreign key in another table
936
* @param $uncommon_tables array containing names that are uncommon
937
* @param $uncommon_tables_fields field names of the uncommon table
938
* @param $display true/false value
940
function PMA_checkForeignKeys($src_db, $src_link, $trg_db, $trg_link ,$referenced_table, &$uncommon_tables, &$uncommon_tables_fields, $display)
942
$is_fk_query = "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '" . $src_db . "'
943
AND TABLE_NAME = '" . $referenced_table . "' AND TABLE_NAME <> REFERENCED_TABLE_NAME;";
945
$is_fk_result = PMA_DBI_fetch_result($is_fk_query, null, null, $src_link);
946
if (sizeof($is_fk_result) > 0) {
947
for ($j = 0; $j < sizeof($is_fk_result); $j++) {
948
if (in_array($is_fk_result[$j]['REFERENCED_TABLE_NAME'], $uncommon_tables)) {
949
$table_index = array_keys($uncommon_tables, $is_fk_result[$j]['REFERENCED_TABLE_NAME']);
950
PMA_checkForeignKeys($src_db, $src_link, $trg_db, $trg_link, $is_fk_result[$j]['REFERENCED_TABLE_NAME'], $uncommon_tables,
951
$uncommon_tables_fields, $display);
952
PMA_createTargetTables($src_db, $trg_db, $trg_link, $src_link, $uncommon_tables, $table_index[0], $uncommon_tables_fields, $display);
953
unset($uncommon_tables[$table_index[0]]);
959
* PMA_alterTargetTableStructure() alters structure of the target table using $alter_str_array
961
* @uses PMA_DBI_fetch_result()
964
* @uses PMA_DBI_try_query()
967
* @param $trg_db name of target database
968
* @param $trg_link connection established with target server
969
* @param $matching_tables array containing names of matching tables
970
* @param $source_columns array containing columns information of the source table
971
* @param $alter_str_array array containing the column name and criteria which is to be altered for the targert table
972
* @param $matching_tables_fields array containing the name of the fields for the matching table
973
* @param $criteria array containing the criterias
974
* @param $matching_tables_keys array containing the field names which is key in the source table
975
* @param $target_tables_keys array containing the field names which is key in the target table
976
* @param $matching_table_index integer number of the matching table
977
* @param $display true/false value
979
function PMA_alterTargetTableStructure($trg_db, $trg_link, $matching_tables, &$source_columns, &$alter_str_array, $matching_tables_fields, $criteria,
980
&$matching_tables_keys, &$target_tables_keys, $matching_table_index, $display)
986
//Checks if the criteria to be altered is primary key
987
for ($v = 0; $v < sizeof($matching_tables_fields[$matching_table_index]); $v++) {
988
if (isset($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$v]]['Key'])) {
989
if ($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$v]]['Key'] == 'PRI' ) {
996
$pri_query = "ALTER TABLE " . PMA_backquote($trg_db) . '.' . PMA_backquote($matching_tables[$matching_table_index]);
997
if (sizeof($target_tables_keys[$matching_table_index]) > 0) {
998
$pri_query .= " DROP PRIMARY KEY ," ;
1000
$pri_query .= " ADD PRIMARY KEY (";
1001
for ($z = 0; $z < sizeof($matching_tables_keys[$matching_table_index]); $z++) {
1002
$pri_query .= $matching_tables_keys[$matching_table_index][$z];
1003
if ($z < (sizeof($matching_tables_keys[$matching_table_index]) - 1)) {
1004
$pri_query .= " , " ;
1010
if (isset($pri_query)) {
1011
if ($display == true) {
1012
echo '<p>' . $pri_query . '</p>';
1014
PMA_DBI_try_query($pri_query, $trg_link, 0);
1016
for ($t = 0; $t < sizeof($matching_tables_fields[$matching_table_index]); $t++) {
1017
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)) {
1018
$sql_query = 'ALTER TABLE ' . PMA_backquote($trg_db) . '.' . PMA_backquote($matching_tables[$matching_table_index]) . ' MODIFY ' .
1019
$matching_tables_fields[$matching_table_index][$t] . ' ' . $source_columns[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]]['Type'];
1021
for ($i = 0; $i < sizeof($criteria); $i++)
1023
if (isset($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]]) && $criteria[$i] != 'Key') {
1025
if (($criteria[$i] == 'Type') && (! isset($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i+1]]))) {
1026
if ($source_columns[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i + 1]] == 'NO') {
1027
$sql_query .= " Not Null" ;
1028
} elseif ($source_columns[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i + 1]] == 'YES') {
1029
$sql_query .= " Null" ;
1032
if (($criteria[$i] == 'Null') && ( $alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]] == 'NO')) {
1033
$sql_query .= " Not Null " ;
1034
} elseif (($criteria[$i] == 'Null') && ($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]] == 'YES')) {
1035
$sql_query .= " Null " ;
1037
if ($criteria[$i] == 'Collation') {
1038
if( !(isset($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[2]]))) {
1039
$sql_query .= " Not Null " ;
1041
$sql_query .= " COLLATE " . $alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]] ;
1043
if (($criteria[$i] == 'Default') && ($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]] == 'None')) {
1044
if( !(isset($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[2]]))) {
1045
$sql_query .= " Not Null " ;
1047
} elseif($criteria[$i] == 'Default') {
1048
if(! (isset($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[2]]))) {
1049
$sql_query .= " Not Null " ;
1051
if (is_string($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]])) {
1052
if ($source_columns[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]]['Type'] != 'timestamp') {
1053
$sql_query .= " DEFAULT '" . $alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]] . "'";
1054
} elseif($source_columns[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]]['Type'] == 'timestamp') {
1055
$sql_query .= " DEFAULT " . $alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]];
1057
} elseif (is_numeric($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]])) {
1058
$sql_query .= " DEFAULT " . $alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]];
1061
if ($criteria[$i] == 'Comment') {
1062
if( !(isset($alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[2]]))) {
1063
$sql_query .= " Not Null " ;
1065
$sql_query .= " COMMENT '" . $alter_str_array[$matching_table_index][$matching_tables_fields[$matching_table_index][$t]][$criteria[$i]] . "'" ;
1072
if ($display == true) {
1073
echo '<p>' . $sql_query . '</p>';
1075
PMA_DBI_try_query($sql_query, $trg_link, 0);
1079
$query = "ALTER TABLE " . PMA_backquote($trg_db) . '.' . PMA_backquote($matching_tables[$matching_table_index]);
1080
for($p = 0; $p < sizeof($matching_tables_keys[$matching_table_index]); $p++) {
1081
if ((isset($alter_str_array[$matching_table_index][$matching_tables_keys[$matching_table_index][$p]]['Key']))) {
1083
$query .= ' MODIFY ' . $matching_tables_keys[$matching_table_index][$p] . ' '
1084
. $source_columns[$matching_table_index][$matching_tables_fields[$matching_table_index][$p]]['Type'] . ' Not Null ';
1085
if ($p < (sizeof($matching_tables_keys[$matching_table_index]) - 1)) {
1091
if ($display == true) {
1092
echo '<p>' . $query . '</p>';
1094
PMA_DBI_try_query($query, $trg_link, 0);
1099
* PMA_removeColumnsFromTargetTable() removes the columns which are present in target table but not in source table.
1101
* @uses PMA_DBI_try_query()
1102
* @uses PMA_DBI_fetch_result()
1104
* @param $trg_db name of target database
1105
* @param $trg_link connection established with target server
1106
* @param $matching_tables array containing names of matching tables
1107
* @param $uncommon_columns array containing the names of the column which are to be dropped from the target table
1108
* @param $table_counter index of the matching table as in $matchiing_tables array
1109
* @param $display true/false value
1111
function PMA_removeColumnsFromTargetTable($trg_db, $trg_link, $matching_tables, $uncommon_columns, $table_counter, $display)
1113
if (isset($uncommon_columns[$table_counter])) {
1114
$drop_query = "ALTER TABLE " . PMA_backquote($trg_db) . "." . PMA_backquote($matching_tables[$table_counter]);
1115
for ($a = 0; $a < sizeof($uncommon_columns[$table_counter]); $a++) {
1116
//Checks if column to be removed is a foreign key in any table
1117
$pk_query = "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = '" . $trg_db . "'
1118
AND REFERENCED_TABLE_NAME = '" . $matching_tables[$table_counter]."' AND REFERENCED_COLUMN_NAME = '"
1119
. $uncommon_columns[$table_counter][$a] . "' AND TABLE_NAME <> REFERENCED_TABLE_NAME;";
1121
$pk_query_result = PMA_DBI_fetch_result($pk_query, null, null, $trg_link);
1122
$result_size = sizeof($pk_query_result);
1124
if ($result_size > 0) {
1125
for ($b = 0; $b < $result_size; $b++) {
1126
$drop_pk_query = "ALTER TABLE " . PMA_backquote($pk_query_result[$b]['TABLE_SCHEMA']) . "." . PMA_backquote($pk_query_result[$b]['TABLE_NAME']) . "
1127
DROP FOREIGN KEY " . $pk_query_result[$b]['CONSTRAINT_NAME'] . ", DROP COLUMN " . $pk_query_result[$b]['COLUMN_NAME'] . ";";
1128
PMA_DBI_try_query($drop_pk_query, $trg_link, 0);
1131
$query = "SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '" . $trg_db . "' AND TABLE_NAME = '"
1132
. $matching_tables[$table_counter]. "' AND COLUMN_NAME = '" . $uncommon_columns[$table_counter][$a] . "'
1133
AND TABLE_NAME <> REFERENCED_TABLE_NAME;";
1135
$result = PMA_DBI_fetch_result($query, null, null, $trg_link);
1137
if (sizeof($result) > 0) {
1138
$drop_query .= " DROP FOREIGN KEY " . $result[0]['CONSTRAINT_NAME'] . ",";
1140
$drop_query .= " DROP COLUMN " . $uncommon_columns[$table_counter][$a];
1141
if ($a < (sizeof($uncommon_columns[$table_counter]) - 1)) {
1142
$drop_query .= " , " ;
1145
$drop_query .= ";" ;
1147
if ($display == true) {
1148
echo '<p>' . $drop_query . '</p>';
1150
PMA_DBI_try_query($drop_query, $trg_link, 0);
1154
* 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
1155
* indexes to be altered in $alter_indexes_array and indexes to be removed from target table in $remove_indexes_array.
1156
* Only keyname and uniqueness characteristic of the indexes are altered.
1158
* @uses PMA_DBI_get_table_indexes()
1160
* @param $src_db name of source database
1161
* @param $trg_db name of target database
1162
* @param $src_link connection established with source server
1163
* @param $trg_link connection established with target server
1164
* @param $matching_tables array containing the matching tables name
1165
* @param $source_indexes array containing the indexes of the source table
1166
* @param $target_indexes array containing the indexes of the target table
1167
* @param $add_indexes_array array containing the name of the column on which the index is to be added in the target table
1168
* @param $alter_indexes_array array containing the key name which needs to be altered
1169
* @param $remove_indexes_array array containing the key name of the index which is to be removed from the target table
1170
* @param $table_counter number of the matching table
1172
function PMA_indexesDiffInTables($src_db, $trg_db, $src_link, $trg_link, $matching_tables, &$source_indexes, &$target_indexes, &$add_indexes_array,
1173
&$alter_indexes_array, &$remove_indexes_array, $table_counter)
1175
//Gets indexes information for source and target table
1176
$source_indexes[$table_counter] = PMA_DBI_get_table_indexes($src_db, $matching_tables[$table_counter],$src_link);
1177
$target_indexes[$table_counter] = PMA_DBI_get_table_indexes($trg_db, $matching_tables[$table_counter],$trg_link);
1178
for ($a = 0; $a < sizeof($source_indexes[$table_counter]); $a++) {
1181
//Compares key name and non_unique characteristic of source indexes with target indexes
1183
* @todo compare the length of each sub part
1185
while (($z <= sizeof($target_indexes[$table_counter])) && ($found == false))
1187
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']) {
1189
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'])) {
1190
if (! (($source_indexes[$table_counter][$a]['Key_name'] == "PRIMARY") || ($target_indexes[$table_counter][$z]['Key_name'] == 'PRIMARY'))) {
1191
$alter_indexes_array[$table_counter][] = $source_indexes[$table_counter][$a]['Key_name'];
1197
if ($found === false) {
1198
if(! ($source_indexes[$table_counter][$a]['Key_name'] == 'PRIMARY')) {
1199
$add_indexes_array [$table_counter][] = $source_indexes[$table_counter][$a]['Column_name'];
1204
//Finds indexes that exist on target table but not on source table
1205
for ($b = 0; $b < sizeof($target_indexes[$table_counter]); $b++) {
1208
while (($c <= sizeof($source_indexes[$table_counter])) && ($found == false))
1210
if ($target_indexes[$table_counter][$b]['Column_name'] == $source_indexes[$table_counter][$c]['Column_name']) {
1215
if ($found === false) {
1216
$remove_indexes_array[$table_counter][] = $target_indexes[$table_counter][$b]['Key_name'];
1222
* PMA_applyIndexesDiff() create indexes, alters indexes and remove indexes.
1224
* @uses PMA_DBI_try_query()
1226
* @param $trg_db name of target database
1227
* @param $trg_link connection established with target server
1228
* @param $matching_tables array containing the matching tables name
1229
* @param $source_indexes array containing the indexes of the source table
1230
* @param $target_indexes array containing the indexes of the target table
1231
* @param $add_indexes_array array containing the column names on which indexes are to be created in target table
1232
* @param $alter_indexes_array array containing the column names for which indexes are to be altered
1233
* @param $remove_indexes_array array containing the key name of the indexes which are to be removed from the target table
1234
* @param $table_counter number of the matching table
1235
* @param $display true/false value
1237
function PMA_applyIndexesDiff ($trg_db, $trg_link, $matching_tables, $source_indexes, $target_indexes, $add_indexes_array, $alter_indexes_array,
1238
$remove_indexes_array, $table_counter, $display)
1240
//Adds indexes on target table
1241
if (isset($add_indexes_array[$table_counter])) {
1242
$sql = "ALTER TABLE " . PMA_backquote($trg_db) . "." . PMA_backquote($matching_tables[$table_counter]) . " ADD" ;
1243
for ($a = 0; $a < sizeof($source_indexes[$table_counter]); $a++) {
1244
if (isset($add_indexes_array[$table_counter][$a])) {
1245
for ($b = 0; $b < sizeof($source_indexes[$table_counter]); $b++) {
1246
if ($source_indexes[$table_counter][$b]['Column_name'] == $add_indexes_array[$table_counter][$a]) {
1247
if ($source_indexes[$table_counter][$b]['Non_unique'] == '0') {
1250
$sql .= " INDEX " . $source_indexes[$table_counter][$b]['Key_name'] . " (" . $add_indexes_array[$table_counter][$a] . " );";
1251
if ($display == true) {
1252
echo '<p>' . $sql . '</p>';
1254
PMA_DBI_try_query($sql, $trg_link, 0);
1260
//Alter indexes of target table
1262
if (isset($alter_indexes_array[$table_counter])) {
1263
$query = "ALTER TABLE " . PMA_backquote($trg_db) . "." . PMA_backquote($matching_tables[$table_counter]);
1264
for ($a = 0; $a < sizeof($alter_indexes_array[$table_counter]); $a++) {
1265
if (isset($alter_indexes_array[$table_counter][$a])) {
1266
$query .= ' DROP INDEX ' . PMA_backquote($alter_indexes_array[$table_counter][$a]) . " , ADD ";
1267
$got_first_index_column = false;
1268
for ($z = 0; $z < sizeof($source_indexes[$table_counter]); $z++) {
1269
if ($source_indexes[$table_counter][$z]['Key_name'] == $alter_indexes_array[$table_counter][$a]) {
1270
if (! $got_first_index_column) {
1271
if ($source_indexes[$table_counter][$z]['Non_unique'] == '0') {
1272
$query .= " UNIQUE ";
1274
$query .= " INDEX " . PMA_backquote($source_indexes[$table_counter][$z]['Key_name']) . " (" . PMA_backquote($source_indexes[$table_counter][$z]['Column_name']);
1275
$got_first_index_column = true;
1277
// another column for this index
1278
$query .= ', ' . PMA_backquote($source_indexes[$table_counter][$z]['Column_name']);
1285
if ($display == true) {
1286
echo '<p>' . $query . '</p>';
1288
PMA_DBI_try_query($query, $trg_link, 0);
1290
//Removes indexes from target table
1291
if (isset($remove_indexes_array[$table_counter])) {
1292
$drop_index_query = "ALTER TABLE " . PMA_backquote($trg_db) . "." . PMA_backquote($matching_tables[$table_counter]);
1293
for ($a = 0; $a < sizeof($target_indexes[$table_counter]); $a++) {
1294
if (isset($remove_indexes_array[$table_counter][$a])) {
1295
$drop_index_query .= " DROP INDEX " . $remove_indexes_array[$table_counter][$a];
1297
if ($a < (sizeof($remove_indexes_array[$table_counter]) - 1)) {
1298
$drop_index_query .= " , " ;
1301
$drop_index_query .= " ; " ;
1302
if ($display == true) {
1303
echo '<p>' . $drop_index_query . '</p>';
1305
PMA_DBI_try_query($drop_index_query, $trg_link, 0);
1310
* PMA_displayQuery() displays a query, taking the maximum display size
1312
* @uses $GLOBALS['cfg']['MaxCharactersInDisplayedSQL']
1314
* @param $query the query to display
1316
function PMA_displayQuery($query) {
1317
if (strlen($query) > $GLOBALS['cfg']['MaxCharactersInDisplayedSQL']) {
1318
$query = substr($query, 0, $GLOBALS['cfg']['MaxCharactersInDisplayedSQL']) . '[...]';
1320
echo '<p>' . htmlspecialchars($query) . '</p>';
1324
* PMA_syncDisplayHeaderSource() shows the header for source database
1325
* @uses $GLOBALS['strDatabase_src']
1326
* @uses $GLOBALS['strDifference']
1327
* @uses $GLOBALS['strCurrentServer']
1328
* @uses $GLOBALS['strRemoteServer']
1329
* @uses $_SESSION['src_type']
1330
* @uses $_SESSION['src_server']['host']
1332
* @param string $src_db source db name
1334
function PMA_syncDisplayHeaderSource($src_db) {
1335
echo '<div id="serverstatus" style = "overflow: auto; width: 1020px; height: 220px; border-left: 1px gray solid; border-bottom: 1px gray solid; padding:0px; margin-bottom: 1em "> ';
1337
echo '<table id="serverstatustraffic" class="data" width="55%">';
1339
echo '<th>' . $GLOBALS['strDatabase_src'] . ': ' . $src_db . '<br />(';
1340
if ('cur' == $_SESSION['src_type']) {
1341
echo $GLOBALS['strCurrentServer'];
1343
echo $GLOBALS['strRemoteServer'] . ' ' . $_SESSION['src_server']['host'];
1346
echo '<th>' . $GLOBALS['strDifference'] . '</th>';
1351
* PMA_syncDisplayHeaderTargetAndMatchingTables() shows the header for target database and the matching tables
1352
* @uses $GLOBALS['strDatabase_trg']
1353
* @uses $GLOBALS['strCurrentServer']
1354
* @uses $GLOBALS['strRemoteServer']
1355
* @uses $_SESSION['trg_type']
1356
* @uses $_SESSION['trg_server']['host']
1358
* @param string $trg_db target db name
1359
* @param array $matching_tables
1360
* @return boolean $odd_row current value of this toggle
1362
function PMA_syncDisplayHeaderTargetAndMatchingTables($trg_db, $matching_tables) {
1363
echo '<table id="serverstatusconnections" class="data" width="43%">';
1365
echo '<th>' . $GLOBALS['strDatabase_trg'] . ': '. $trg_db . '<br />(';
1366
if ('cur' == $_SESSION['trg_type']) {
1367
echo $GLOBALS['strCurrentServer'];
1369
echo $GLOBALS['strRemoteServer'] . ' ' . $_SESSION['trg_server']['host'];
1374
foreach ($matching_tables as $tbl_name) {
1375
$odd_row = PMA_syncDisplayBeginTableRow($odd_row);
1376
echo '<td> ' . htmlspecialchars($tbl_name) . '</td>';
1383
* PMA_syncDisplayBeginTableRow() displays the TR tag for alternating colors
1385
* @param boolean $odd_row current status of the toggle
1386
* @return boolean $odd_row final status of the toggle
1388
function PMA_syncDisplayBeginTableRow($odd_row) {
1389
$odd_row = ! $odd_row;
1390
echo '<tr height="32" class=" ';
1391
echo $odd_row ? 'odd' : 'even';
added
removed
libraries/server_synchronize.lib.php
