libraries/tbl_relation.lib.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Functions for the table relation page
   5  *
   6  * @package PhpMyAdmin
   7  */
   8
   9 /**
  10  * Generate dropdown choices
  11  *
  12  * @param string $dropdown_question Message to display
  13  * @param string $select_name       Name of the <select> field
  14  * @param array  $choices           Choices for dropdown
  15  * @param string $selected_value    Selected value
  16  *
  17  * @return string The html code for existing value (for selected)
  18  *
  19  * @access public
  20  */
  21 function PMA_generateDropdown(
  22     $dropdown_question, $select_name, $choices, $selected_value
  23 ) {
  24     $html_output = htmlspecialchars($dropdown_question) . '&nbsp;&nbsp;'
  25         . '<select name="' . htmlspecialchars($select_name) . '">' . "\n";
  26
  27     foreach ($choices as $one_value => $one_label) {
  28         $html_output .= '<option value="' . htmlspecialchars($one_value) . '"';
  29         if ($selected_value == $one_value) {
  30             $html_output .= ' selected="selected" ';
  31         }
  32         $html_output .= '>' . htmlspecialchars($one_label) . '</option>' . "\n";
  33     }
  34     $html_output .= '</select>' . "\n";
  35
  36     return $html_output;
  37 }
  38
  39 /**
  40  * Split a string on backquote pairs
  41  *
  42  * @param string $text original string
  43  *
  44  * @return array containing the elements (and their surrounding backquotes)
  45  *
  46  * @access public
  47  */
  48 function PMA_backquoteSplit($text)
  49 {
  50     $elements = array();
  51     $final_pos = strlen($text) - 1;
  52     $pos = 0;
  53     while ($pos <= $final_pos) {
  54         $first_backquote = strpos($text, '`', $pos);
  55         $second_backquote = strpos($text, '`', $first_backquote + 1);
  56         // after the second one, there might be another one which means
  57         // this is an escaped backquote
  58         if ($second_backquote < $final_pos && '`' == $text[$second_backquote + 1]) {
  59             $second_backquote = strpos($text, '`', $second_backquote + 2);
  60         }
  61         if (false === $first_backquote || false === $second_backquote) {
  62             break;
  63         }
  64         $elements[] = substr(
  65             $text, $first_backquote, $second_backquote - $first_backquote + 1
  66         );
  67         $pos = $second_backquote + 1;
  68     }
  69     return($elements);
  70 }
  71
  72 /**
  73  * Returns the DROP query for a foreign key constraint
  74  *
  75  * @param string $table table of the foreign key
  76  * @param string $fk    foreign key name
  77  *
  78  * @return string DROP query for the foreign key constraint
  79  */
  80 function PMA_getSQLToDropForeignKey($table, $fk)
  81 {
  82     return 'ALTER TABLE ' . PMA_Util::backquote($table)
  83         . ' DROP FOREIGN KEY ' . PMA_Util::backquote($fk) . ';';
  84 }
  85
  86 /**
  87  * Returns the SQL query for foreign key constraint creation
  88  *
  89  * @param string $table        table name
  90  * @param string $field        field name
  91  * @param string $foreignDb    foreign database name
  92  * @param string $foreignTable foreign table name
  93  * @param string $foreignField foreign field name
  94  * @param string $name         name of the constraint
  95  * @param string $onDelete     on delete action
  96  * @param string $onUpdate     on update action
  97  *
  98  * @return string SQL query for foreign key constraint creation
  99  */
 100 function PMA_getSQLToCreateForeignKey($table, $field, $foreignDb, $foreignTable,
 101     $foreignField, $name = null, $onDelete = null, $onUpdate = null
 102 ) {
 103     $sql_query  = 'ALTER TABLE ' . PMA_Util::backquote($table) . ' ADD ';
 104     // if user entered a constraint name
 105     if (! empty($name)) {
 106         $sql_query .= ' CONSTRAINT ' . PMA_Util::backquote($name);
 107     }
 108
 109     $sql_query .= ' FOREIGN KEY (' . PMA_Util::backquote($field) . ')'
 110         . ' REFERENCES ' . PMA_Util::backquote($foreignDb)
 111         . '.' . PMA_Util::backquote($foreignTable)
 112         . '(' . PMA_Util::backquote($foreignField) . ')';
 113
 114     if (! empty($onDelete)) {
 115         $sql_query .= ' ON DELETE ' . $onDelete;
 116     }
 117     if (! empty($onUpdate)) {
 118         $sql_query .= ' ON UPDATE ' . $onUpdate;
 119     }
 120     $sql_query .= ';';
 121
 122     return $sql_query;
 123 }
 124
 125 /**
 126  * Creates and populates dropdowns to select foreign db/table/column
 127  *
 128  * @param string $name    name of the dropdowns
 129  * @param array  $values  dropdown values
 130  * @param string $foreign value of the item to be selected
 131  * @param string $title   title to show on hovering the dropdown
 132  *
 133  * @return string HTML for the dropdown
 134  */
 135 function PMA_generateRelationalDropdown(
 136     $name, $values = array(), $foreign = false, $title = ''
 137 ) {
 138     $html_output = '<select name="' . $name . '" title="' . $title . '">';
 139     $html_output .= '<option value=""></option>';
 140
 141     $seen_key = false;
 142     foreach ($values as $value) {
 143         $html_output .= '<option value="' . htmlspecialchars($value) . '"';
 144         if ($foreign && $value == $foreign) {
 145             $html_output .= ' selected="selected"';
 146             $seen_key = true;
 147         }
 148         $html_output .= '>' . htmlspecialchars($value) . '</option>';
 149     }
 150
 151     if ($foreign && ! $seen_key) {
 152         $html_output .= '<option value="' . htmlspecialchars($foreign) . '"'
 153             . ' selected="selected">' . htmlspecialchars($foreign) . '</option>';
 154     }
 155     $html_output .= '</select>';
 156     return $html_output;
 157 }
 158
 159 /**
 160  * Function to get html for the common form
 161  *
 162  * @param string $db                 current database
 163  * @param string $table              current table
 164  * @param array  $columns            columns
 165  * @param array  $cfgRelation        configuration relation
 166  * @param string $tbl_storage_engine table storage engine
 167  * @param array  $existrel           db, table, column
 168  * @param array  $existrel_foreign   db, table, column
 169  * @param array  $options_array      options array
 170  *
 171  * @return string
 172  */
 173 function PMA_getHtmlForCommonForm($db, $table, $columns, $cfgRelation,
 174     $tbl_storage_engine, $existrel, $existrel_foreign, $options_array
 175 ) {
 176     $html_output = PMA_getHtmlForCommonFormHeader($db, $table);
 177
 178     if (count($columns) > 0) {
 179         $html_output .= PMA_getHtmlForCommonFormRows(
 180             $columns, $cfgRelation, $tbl_storage_engine,
 181             $existrel, $existrel_foreign, $options_array, $db, $table
 182         );
 183     } // end if (we have columns in this table)
 184
 185     $html_output .= PMA_getHtmlForCommonFormFooter();
 186
 187     return $html_output;
 188 }
 189
 190 /**
 191  * Function to get html for the common form rows
 192  *
 193  * @param array  $columns            columns
 194  * @param array  $cfgRelation        configuration relation
 195  * @param string $tbl_storage_engine table storage engine
 196  * @param array  $existrel           existed relations
 197  * @param array  $existrel_foreign   existed relations for foreign keys
 198  * @param array  $options_array      options array
 199  * @param string $db                 current database
 200  * @param string $table              current table
 201  *
 202  * @return string
 203  */
 204 function PMA_getHtmlForCommonFormRows($columns, $cfgRelation, $tbl_storage_engine,
 205     $existrel, $existrel_foreign, $options_array, $db, $table
 206 ) {
 207     foreach ($columns as $row) {
 208         $save_row[] = $row;
 209     }
 210
 211     $saved_row_cnt  = count($save_row);
 212
 213     $html_output = '<fieldset>'
 214         . '<legend>' . __('Relations'). '</legend>'
 215         . '<table id="relationalTable">';
 216
 217     $html_output .= PMA_getHtmlForCommonFormTableHeaders(
 218         $cfgRelation, $tbl_storage_engine
 219     );
 220
 221     $odd_row = true;
 222     for ($i = 0; $i < $saved_row_cnt; $i++) {
 223         $html_output .= PMA_getHtmlForRow(
 224             $save_row, $i, $odd_row, $cfgRelation, $existrel, $db,
 225             $tbl_storage_engine, $existrel_foreign, $options_array
 226         );
 227         $odd_row = ! $odd_row;
 228     } // end for
 229
 230     $html_output .= '</table>' . "\n"
 231         . '</fieldset>' . "\n";
 232
 233     if ($cfgRelation['displaywork']) {
 234         $html_output .= PMA_getHtmlForDisplayFieldInfos($db, $table, $save_row);
 235     }
 236
 237     return $html_output;
 238 }
 239
 240 /**
 241  * Function to get html for an entire row in common form
 242  *
 243  * @param array  $save_row           save row
 244  * @param int    $i                  counter
 245  * @param bool   $odd_row            whether odd row or not
 246  * @param array  $cfgRelation        configuration relation
 247  * @param array  $existrel           db, table, column
 248  * @param string $db                 current db
 249  * @param string $tbl_storage_engine table storage engine
 250  * @param array  $existrel_foreign   db, table, column
 251  * @param array  $options_array      options array
 252  *
 253  * @return string
 254  */
 255 function PMA_getHtmlForRow($save_row, $i, $odd_row, $cfgRelation, $existrel, $db,
 256     $tbl_storage_engine, $existrel_foreign, $options_array
 257 ) {
 258     $myfield = $save_row[$i]['Field'];
 259     // Use an md5 as array index to avoid having special characters
 260     // in the name attribute (see bug #1746964 )
 261     $myfield_md5 = md5($myfield);
 262     $myfield_html = htmlspecialchars($myfield);
 263
 264     $html_output = '<tr class="' . ($odd_row ? 'odd' : 'even') . '">'
 265         . '<td class="center">'
 266         . '<strong>' . $myfield_html . '</strong>'
 267         . '<input type="hidden" name="fields_name[' . $myfield_md5 . ']"'
 268         . ' value="' . $myfield_html . '"/>'
 269         . '</td>';
 270
 271     if ($cfgRelation['relwork']) {
 272         $html_output .= '<td>';
 273
 274         $foreign_db = false;
 275         $foreign_table = false;
 276         $foreign_column = false;
 277
 278         // database dropdown
 279         if (isset($existrel[$myfield])) {
 280             $foreign_db = $existrel[$myfield]['foreign_db'];
 281         } else {
 282             $foreign_db = $db;
 283         }
 284         $html_output .= PMA_generateRelationalDropdown(
 285             'destination_db[' . $myfield_md5 . ']',
 286             $GLOBALS['pma']->databases,
 287             $foreign_db,
 288             __('Database')
 289         );
 290         // end of database dropdown
 291
 292         // table dropdown
 293         $tables = array();
 294         if ($foreign_db) {
 295             if (isset($existrel[$myfield])) {
 296                 $foreign_table = $existrel[$myfield]['foreign_table'];
 297             }
 298             $tables_rs = $GLOBALS['dbi']->query(
 299                 'SHOW TABLES FROM ' . PMA_Util::backquote($foreign_db),
 300                 null,
 301                 PMA_DatabaseInterface::QUERY_STORE
 302             );
 303             while ($row = $GLOBALS['dbi']->fetchRow($tables_rs)) {
 304                 $tables[] = $row[0];
 305             }
 306         }
 307         $html_output .= PMA_generateRelationalDropdown(
 308             'destination_table[' . $myfield_md5 . ']',
 309             $tables,
 310             $foreign_table,
 311             __('Table')
 312         );
 313         // end of table dropdown
 314
 315         // column dropdown
 316         $columns = array();
 317         if ($foreign_db && $foreign_table) {
 318             if (isset($existrel[$myfield])) {
 319                 $foreign_column = $existrel[$myfield]['foreign_field'];
 320             }
 321             $table_obj = new PMA_Table($foreign_table, $foreign_db);
 322             $columns = $table_obj->getUniqueColumns(false, false);
 323         }
 324         $html_output .= PMA_generateRelationalDropdown(
 325             'destination_column[' . $myfield_md5 . ']',
 326             $columns,
 327             $foreign_column,
 328             __('Column')
 329         );
 330         // end of column dropdown
 331
 332         $html_output .= '</td>';
 333     } // end if (internal relations)
 334
 335     if (PMA_Util::isForeignKeySupported($tbl_storage_engine)) {
 336         $html_output .= PMA_getHtmlForForeignKey(
 337             $save_row, $i, $existrel_foreign, $myfield, $db,
 338             $myfield_md5, $tbl_storage_engine, $options_array
 339         );
 340     } // end if (InnoDB)
 341     $html_output .= '</tr>';
 342
 343     return $html_output;
 344 }
 345
 346 /**
 347  * Function to get html for the common form header
 348  *
 349  * @param string $db    current database
 350  * @param string $table current table
 351  *
 352  * @return string
 353  */
 354 function PMA_getHtmlForCommonFormHeader($db, $table)
 355 {
 356     return '<form method="post" action="tbl_relation.php">' . "\n"
 357     . PMA_URL_getHiddenInputs($db, $table);
 358 }
 359
 360 /**
 361  * Function to get html for the common form footer
 362  *
 363  * @return string
 364  */
 365 function PMA_getHtmlForCommonFormFooter()
 366 {
 367     return '<fieldset class="tblFooters">'
 368         . '<input type="submit" value="' . __('Save') . '" />'
 369         . '</fieldset>'
 370         . '</form>';
 371 }
 372
 373 /**
 374  * Function to get html for display field infos
 375  *
 376  * @param string $db       current database
 377  * @param string $table    current table
 378  * @param array  $save_row save row
 379  *
 380  * @return string
 381  */
 382 function PMA_getHtmlForDisplayFieldInfos($db, $table, $save_row)
 383 {
 384     $disp = PMA_getDisplayField($db, $table);
 385     $html_output = '<fieldset>'
 386         . '<label>' . __('Choose column to display:') . '</label>'
 387         . '<select name="display_field">'
 388         . '<option value="">---</option>';
 389
 390     foreach ($save_row as $row) {
 391         $html_output .= '<option value="'
 392             . htmlspecialchars($row['Field']) . '"';
 393         if (isset($disp) && $row['Field'] == $disp) {
 394             $html_output .= ' selected="selected"';
 395         }
 396         $html_output .= '>' . htmlspecialchars($row['Field'])
 397             . '</option>'. "\n";
 398     } // end while
 399
 400     $html_output .= '</select>'
 401         . '</fieldset>';
 402
 403     return $html_output;
 404 }
 405
 406 /**
 407  * Function to get html for the common form title headers
 408  *
 409  * @param array  $cfgRelation        configuration relation
 410  * @param string $tbl_storage_engine table storage engine
 411  *
 412  * @return string
 413  */
 414 function PMA_getHtmlForCommonFormTableHeaders($cfgRelation, $tbl_storage_engine)
 415 {
 416     $html_output = '<tr><th>' . __('Column') . '</th>';
 417
 418     if ($cfgRelation['relwork']) {
 419         $html_output .= '<th>' . __('Internal relation');
 420         if (PMA_Util::isForeignKeySupported($tbl_storage_engine)) {
 421             $html_output .= PMA_Util::showHint(
 422                 __(
 423                     'An internal relation is not necessary when a corresponding'
 424                     . ' FOREIGN KEY relation exists.'
 425                 )
 426             );
 427         }
 428         $html_output .= '</th>';
 429     }
 430
 431     if (PMA_Util::isForeignKeySupported($tbl_storage_engine)) {
 432         // this does not have to be translated, it's part of the MySQL syntax
 433         $html_output .= '<th colspan="2">' . __('Foreign key constraint')
 434             . ' (' . $tbl_storage_engine . ')';
 435         $html_output .= '</th>';
 436     }
 437     $html_output .= '</tr>';
 438
 439     return $html_output;
 440 }
 441
 442 /**
 443  * Function to get html for the foreign key
 444  *
 445  * @param array  $save_row           save row
 446  * @param int    $i                  counter
 447  * @param array  $existrel_foreign   db, table, columns
 448  * @param string $myfield            my field
 449  * @param string $db                 current database
 450  * @param string $myfield_md5        my field md5
 451  * @param string $tbl_storage_engine table storage engine
 452  * @param array  $options_array      options array
 453  *
 454  * @return string
 455  */
 456 function PMA_getHtmlForForeignKey($save_row, $i, $existrel_foreign, $myfield, $db,
 457     $myfield_md5, $tbl_storage_engine, $options_array
 458 ) {
 459     $html_output = '<td>';
 460     if (! empty($save_row[$i]['Key'])) {
 461
 462         $foreign_db = false;
 463         $foreign_table = false;
 464         $foreign_column = false;
 465
 466         // foreign database dropdown
 467         if (isset($existrel_foreign[$myfield])) {
 468             $foreign_db = $existrel_foreign[$myfield]['foreign_db'];
 469         } else {
 470             $foreign_db = $db;
 471         }
 472         $html_output .= '<span class="formelement clearfloat">';
 473         $html_output .= PMA_generateRelationalDropdown(
 474             'destination_foreign_db[' . $myfield_md5 . ']',
 475             $GLOBALS['pma']->databases,
 476             $foreign_db,
 477             __('Database')
 478         );
 479         // end of foreign database dropdown
 480
 481         // foreign table dropdown
 482         $tables = array();
 483         if ($foreign_db) {
 484             if (isset($existrel_foreign[$myfield])) {
 485                 $foreign_table = $existrel_foreign[$myfield]['foreign_table'];
 486             }
 487             // In Drizzle, 'SHOW TABLE STATUS' will show status only for the tables
 488             // which are currently in the table cache. Hence we have to use
 489             // 'SHOW TABLES' and manully retrieve table engine values.
 490             if (PMA_DRIZZLE) {
 491                 $tables_rs = $GLOBALS['dbi']->query(
 492                     'SHOW TABLES FROM ' . PMA_Util::backquote($foreign_db),
 493                     null,
 494                     PMA_DatabaseInterface::QUERY_STORE
 495                 );
 496                 while ($row = $GLOBALS['dbi']->fetchArray($tables_rs)) {
 497                     $engine = PMA_Table::sGetStatusInfo(
 498                         $foreign_db,
 499                         $row[0],
 500                         'Engine'
 501                     );
 502                     if (isset($engine)
 503                         && strtoupper($engine) == $tbl_storage_engine
 504                     ) {
 505                         $tables[] = $row[0];
 506                     }
 507                 }
 508             } else {
 509                 $tables_rs = $GLOBALS['dbi']->query(
 510                     'SHOW TABLE STATUS FROM ' . PMA_Util::backquote($foreign_db),
 511                     null,
 512                     PMA_DatabaseInterface::QUERY_STORE
 513                 );
 514                 while ($row = $GLOBALS['dbi']->fetchRow($tables_rs)) {
 515                     if (isset($row[1])
 516                         && strtoupper($row[1]) == $tbl_storage_engine
 517                     ) {
 518                         $tables[] = $row[0];
 519                     }
 520                 }
 521             }
 522         }
 523         $html_output .= PMA_generateRelationalDropdown(
 524             'destination_foreign_table[' . $myfield_md5 . ']',
 525             $tables,
 526             $foreign_table,
 527             __('Table')
 528         );
 529         // end of foreign table dropdown
 530
 531         // foreign column dropdown
 532         $columns = array();
 533         if ($foreign_db && $foreign_table) {
 534             if (isset($existrel_foreign[$myfield])) {
 535                 $foreign_column = $existrel_foreign[$myfield]['foreign_field'];
 536             }
 537             $table_obj = new PMA_Table($foreign_table, $foreign_db);
 538             $columns = $table_obj->getUniqueColumns(false, false);
 539         }
 540         $html_output .= PMA_generateRelationalDropdown(
 541             'destination_foreign_column[' . $myfield_md5 . ']',
 542             $columns,
 543             $foreign_column,
 544             __('Column')
 545         );
 546         $html_output .= '</span>';
 547         // end of foreign column dropdown
 548
 549         // For constraint name
 550         $html_output .= '<span class="formelement clearfloat">';
 551         $constraint_name = isset($existrel_foreign[$myfield]['constraint'])
 552             ? $existrel_foreign[$myfield]['constraint'] : '';
 553         $html_output .= __('Constraint name');
 554         $html_output .= '<input type="text" name="constraint_name['
 555             . $myfield_md5 . ']"'
 556             . ' value="' . $constraint_name . '"/>';
 557         $html_output .= '</span>' . "\n";
 558
 559         $html_output .= '<span class="formelement clearfloat">';
 560         // For ON DELETE and ON UPDATE, the default action
 561         // is RESTRICT as per MySQL doc; however, a SHOW CREATE TABLE
 562         // won't display the clause if it's set as RESTRICT.
 563         $on_delete = isset($existrel_foreign[$myfield]['on_delete'])
 564             ? $existrel_foreign[$myfield]['on_delete'] : 'RESTRICT';
 565         $html_output .= PMA_generateDropdown(
 566             'ON DELETE',
 567             'on_delete[' . $myfield_md5 . ']',
 568             $options_array,
 569             $on_delete
 570         );
 571         $html_output .= '</span>' . "\n";
 572
 573         $html_output .= '<span class="formelement clearfloat">' . "\n";
 574         $on_update = isset($existrel_foreign[$myfield]['on_update'])
 575             ? $existrel_foreign[$myfield]['on_update'] : 'RESTRICT';
 576         $html_output .= PMA_generateDropdown(
 577             'ON UPDATE',
 578             'on_update[' . $myfield_md5 . ']',
 579             $options_array,
 580             $on_update
 581         );
 582         $html_output .= '</span>' . "\n";
 583     } else {
 584         $html_output .= __('No index defined! Create one below');
 585     } // end if (a key exists)
 586     $html_output .= '</td>';
 587
 588     return $html_output;
 589 }
 590
 591 /**
 592  * Function to send html for table or column dropdown list
 593  *
 594  * @return void
 595  */
 596 function PMA_sendHtmlForTableOrColumnDropdownList()
 597 {
 598     if (isset($_REQUEST['foreignTable'])) { // if both db and table are selected
 599         PMA_sendHtmlForColumnDropdownList();
 600     } else { // if only the db is selected
 601         PMA_sendHtmlForTableDropdownList();
 602     }
 603     exit;
 604 }
 605
 606 /**
 607  * Function to send html for column dropdown list
 608  *
 609  * @return void
 610  */
 611 function PMA_sendHtmlForColumnDropdownList()
 612 {
 613     $response = PMA_Response::getInstance();
 614
 615     $foreignTable = $_REQUEST['foreignTable'];
 616     $table_obj = new PMA_Table($foreignTable, $_REQUEST['foreignDb']);
 617     $columns = array();
 618     foreach ($table_obj->getUniqueColumns(false, false) as $column) {
 619         $columns[] = htmlspecialchars($column);
 620     }
 621     $response->addJSON('columns', $columns);
 622 }
 623
 624 /**
 625  * Function to send html for table dropdown list
 626  *
 627  * @return void
 628  */
 629 function PMA_sendHtmlForTableDropdownList()
 630 {
 631     $response = PMA_Response::getInstance();
 632     $tables = array();
 633
 634     $foreign = isset($_REQUEST['foreign']) && $_REQUEST['foreign'] === 'true';
 635     if ($foreign) {
 636         $tbl_storage_engine = strtoupper(
 637             PMA_Table::sGetStatusInfo(
 638                 $_REQUEST['db'],
 639                 $_REQUEST['table'],
 640                 'Engine'
 641             )
 642         );
 643     }
 644
 645     // In Drizzle, 'SHOW TABLE STATUS' will show status only for the tables
 646     // which are currently in the table cache. Hence we have to use 'SHOW TABLES'
 647     // and manully retrieve table engine values.
 648     if ($foreign && ! PMA_DRIZZLE) {
 649         $query = 'SHOW TABLE STATUS FROM '
 650             . PMA_Util::backquote($_REQUEST['foreignDb']);
 651         $tables_rs = $GLOBALS['dbi']->query(
 652             $query,
 653             null,
 654             PMA_DatabaseInterface::QUERY_STORE
 655         );
 656
 657         while ($row = $GLOBALS['dbi']->fetchArray($tables_rs)) {
 658             if (isset($row['Engine'])
 659                 && strtoupper($row['Engine']) == $tbl_storage_engine
 660             ) {
 661                 $tables[] = htmlspecialchars($row['Name']);
 662             }
 663         }
 664     } else {
 665         $query = 'SHOW TABLES FROM '
 666             . PMA_Util::backquote($_REQUEST['foreignDb']);
 667         $tables_rs = $GLOBALS['dbi']->query(
 668             $query,
 669             null,
 670             PMA_DatabaseInterface::QUERY_STORE
 671         );
 672         while ($row = $GLOBALS['dbi']->fetchArray($tables_rs)) {
 673             if ($foreign && PMA_DRIZZLE) {
 674                 $engine = strtoupper(
 675                     PMA_Table::sGetStatusInfo(
 676                         $_REQUEST['foreignDb'],
 677                         $row[0],
 678                         'Engine'
 679                     )
 680                 );
 681                 if (isset($engine) && $engine == $tbl_storage_engine) {
 682                     $tables[] = htmlspecialchars($row[0]);
 683                 }
 684             } else {
 685                 $tables[] = htmlspecialchars($row[0]);
 686             }
 687         }
 688     }
 689     $response->addJSON('tables', $tables);
 690 }
 691
 692 /**
 693  * Function to handle update for display field
 694  *
 695  * @param string $disp          field name
 696  * @param string $display_field display field
 697  * @param string $db            current database
 698  * @param string $table         current table
 699  * @param array  $cfgRelation   configuration relation
 700  *
 701  * @return void
 702  */
 703 function PMA_handleUpdateForDisplayField($disp, $display_field, $db, $table,
 704     $cfgRelation
 705 ) {
 706     $upd_query = PMA_getQueryForDisplayUpdate(
 707         $disp, $display_field, $db, $table, $cfgRelation
 708     );
 709     if ($upd_query) {
 710         PMA_queryAsControlUser($upd_query);
 711     }
 712 }
 713
 714 /**
 715  * Function to get display query for handlingdisplay update
 716  *
 717  * @param string $disp          field name
 718  * @param string $display_field display field
 719  * @param string $db            current database
 720  * @param string $table         current table
 721  * @param array  $cfgRelation   configuration relation
 722  *
 723  * @return string
 724  */
 725 function PMA_getQueryForDisplayUpdate($disp, $display_field, $db, $table,
 726     $cfgRelation
 727 ) {
 728     $upd_query = false;
 729     if ($disp) {
 730         if ($display_field != '') {
 731             $upd_query = 'UPDATE '
 732                 . PMA_Util::backquote($GLOBALS['cfgRelation']['db'])
 733                 . '.' . PMA_Util::backquote($cfgRelation['table_info'])
 734                 . ' SET display_field = \''
 735                 . PMA_Util::sqlAddSlashes($display_field) . '\''
 736                 . ' WHERE db_name  = \'' . PMA_Util::sqlAddSlashes($db) . '\''
 737                 . ' AND table_name = \'' . PMA_Util::sqlAddSlashes($table) . '\'';
 738         } else {
 739             $upd_query = 'DELETE FROM '
 740                 . PMA_Util::backquote($GLOBALS['cfgRelation']['db'])
 741                 . '.' . PMA_Util::backquote($cfgRelation['table_info'])
 742                 . ' WHERE db_name  = \'' . PMA_Util::sqlAddSlashes($db) . '\''
 743                 . ' AND table_name = \'' . PMA_Util::sqlAddSlashes($table) . '\'';
 744         }
 745     } elseif ($display_field != '') {
 746         $upd_query = 'INSERT INTO '
 747             . PMA_Util::backquote($GLOBALS['cfgRelation']['db'])
 748             . '.' . PMA_Util::backquote($cfgRelation['table_info'])
 749             . '(db_name, table_name, display_field) VALUES('
 750             . '\'' . PMA_Util::sqlAddSlashes($db) . '\','
 751             . '\'' . PMA_Util::sqlAddSlashes($table) . '\','
 752             . '\'' . PMA_Util::sqlAddSlashes($display_field) . '\')';
 753     }
 754
 755     return $upd_query;
 756 }
 757
 758 /**
 759  * Function to handle updates for internal relations
 760  *
 761  * @param string $destination_db          destination database
 762  * @param string $multi_edit_columns_name multi edit column name
 763  * @param string $destination_table       destination table
 764  * @param string $destination_column      destination column
 765  * @param array  $cfgRelation             configuration relation
 766  * @param string $db                      current database
 767  * @param string $table                   current table
 768  * @param array  $existrel                db, table, column
 769  *
 770  * @return void
 771  */
 772 function PMA_handleUpdatesForInternalRelations($destination_db,
 773     $multi_edit_columns_name, $destination_table, $destination_column, $cfgRelation,
 774     $db, $table, $existrel
 775 ) {
 776     foreach ($destination_db as $master_field_md5 => $foreign_db) {
 777         $upd_query = PMA_getQueryForInternalRelationUpdate(
 778             $multi_edit_columns_name,
 779             $master_field_md5, $foreign_db, $destination_table, $destination_column,
 780             $cfgRelation, $db, $table, isset($existrel) ? $existrel : null
 781         );
 782         if ($upd_query) {
 783             PMA_queryAsControlUser($upd_query);
 784         }
 785     }
 786 }
 787
 788 /**
 789  * Function to get update query for updating internal relations
 790  *
 791  * @param string $multi_edit_columns_name multi edit column names
 792  * @param string $master_field_md5        master field md5
 793  * @param string $foreign_db              foreign database
 794  * @param string $destination_table       destination table
 795  * @param string $destination_column      destination column
 796  * @param array  $cfgRelation             configuration relation
 797  * @param string $db                      current database
 798  * @param string $table                   current table
 799  * @param array  $existrel                db, table, column
 800  *
 801  * @return string
 802  */
 803 function PMA_getQueryForInternalRelationUpdate($multi_edit_columns_name,
 804     $master_field_md5, $foreign_db, $destination_table, $destination_column,
 805     $cfgRelation, $db, $table, $existrel
 806 ) {
 807     $upd_query = false;
 808
 809     // Map the fieldname's md5 back to its real name
 810     $master_field = $multi_edit_columns_name[$master_field_md5];
 811
 812     $foreign_table = $destination_table[$master_field_md5];
 813     $foreign_field = $destination_column[$master_field_md5];
 814     if (! empty($foreign_db)
 815         && ! empty($foreign_table)
 816         && ! empty($foreign_field)
 817     ) {
 818         if (! isset($existrel[$master_field])) {
 819             $upd_query  = 'INSERT INTO '
 820                 . PMA_Util::backquote($GLOBALS['cfgRelation']['db'])
 821                 . '.' . PMA_Util::backquote($cfgRelation['relation'])
 822                 . '(master_db, master_table, master_field, foreign_db,'
 823                 . ' foreign_table, foreign_field)'
 824                 . ' values('
 825                 . '\'' . PMA_Util::sqlAddSlashes($db) . '\', '
 826                 . '\'' . PMA_Util::sqlAddSlashes($table) . '\', '
 827                 . '\'' . PMA_Util::sqlAddSlashes($master_field) . '\', '
 828                 . '\'' . PMA_Util::sqlAddSlashes($foreign_db) . '\', '
 829                 . '\'' . PMA_Util::sqlAddSlashes($foreign_table) . '\','
 830                 . '\'' . PMA_Util::sqlAddSlashes($foreign_field) . '\')';
 831
 832         } elseif ($existrel[$master_field]['foreign_db'] != $foreign_db
 833             || $existrel[$master_field]['foreign_table'] != $foreign_table
 834             || $existrel[$master_field]['foreign_field'] != $foreign_field
 835         ) {
 836             $upd_query  = 'UPDATE '
 837                 . PMA_Util::backquote($GLOBALS['cfgRelation']['db'])
 838                 . '.' . PMA_Util::backquote($cfgRelation['relation']) . ' SET'
 839                 . ' foreign_db       = \''
 840                 . PMA_Util::sqlAddSlashes($foreign_db) . '\', '
 841                 . ' foreign_table    = \''
 842                 . PMA_Util::sqlAddSlashes($foreign_table) . '\', '
 843                 . ' foreign_field    = \''
 844                 . PMA_Util::sqlAddSlashes($foreign_field) . '\' '
 845                 . ' WHERE master_db  = \''
 846                 . PMA_Util::sqlAddSlashes($db) . '\''
 847                 . ' AND master_table = \''
 848                 . PMA_Util::sqlAddSlashes($table) . '\''
 849                 . ' AND master_field = \''
 850                 . PMA_Util::sqlAddSlashes($master_field) . '\'';
 851         } // end if... else....
 852     } elseif (isset($existrel[$master_field])) {
 853         $upd_query = 'DELETE FROM '
 854             . PMA_Util::backquote($GLOBALS['cfgRelation']['db'])
 855             . '.' . PMA_Util::backquote($cfgRelation['relation'])
 856             . ' WHERE master_db  = \'' . PMA_Util::sqlAddSlashes($db) . '\''
 857             . ' AND master_table = \'' . PMA_Util::sqlAddSlashes($table) . '\''
 858             . ' AND master_field = \'' . PMA_Util::sqlAddSlashes($master_field)
 859             . '\'';
 860     } // end if... else....
 861
 862     return $upd_query;
 863 }
 864
 865 /**
 866  * Function to handle foreign key updates
 867  *
 868  * @param string $destination_foreign_db     destination foreign database
 869  * @param string $multi_edit_columns_name    multi edit column names
 870  * @param string $destination_foreign_table  destination foreign table
 871  * @param string $destination_foreign_column destination foreign column
 872  * @param array  $options_array              options array
 873  * @param string $table                      current table
 874  * @param array  $existrel_foreign           db, table, column
 875  *
 876  * @return string
 877  */
 878 function PMA_handleUpdatesForForeignKeys($destination_foreign_db,
 879     $multi_edit_columns_name, $destination_foreign_table,
 880     $destination_foreign_column, $options_array, $table, $existrel_foreign
 881 ) {
 882     $html_output = '';
 883     $display_query = '';
 884     $seen_error = false;
 885     foreach ($destination_foreign_db as $master_field_md5 => $foreign_db) {
 886         $html_output .= PMA_handleUpdateForForeignKey(
 887             $multi_edit_columns_name, $master_field_md5,
 888             $destination_foreign_table, $destination_foreign_column, $options_array,
 889             $existrel_foreign, $table, $seen_error, $display_query, $foreign_db
 890         );
 891     } // end foreach
 892     if (! empty($display_query) && ! $seen_error) {
 893         $GLOBALS['display_query'] = $display_query;
 894         $html_output = PMA_Util::getMessage(
 895             __('Your SQL query has been executed successfully'),
 896             null, 'success'
 897         );
 898     }
 899
 900     return $html_output;
 901 }
 902
 903 /**
 904  * Function to handle update for a foreign key
 905  *
 906  * @param array  $multi_edit_columns_name    multu edit columns name
 907  * @param string $master_field_md5           master field md5
 908  * @param string $destination_foreign_table  destination foreign table
 909  * @param string $destination_foreign_column destination foreign column
 910  * @param array  $options_array              options array
 911  * @param array  $existrel_foreign           db, table, column
 912  * @param string $table                      current table
 913  * @param bool   &$seen_error                whether seen error
 914  * @param string &$display_query             display query
 915  * @param string $foreign_db                 foreign database
 916  *
 917  * @return string
 918  */
 919 function PMA_handleUpdateForForeignKey($multi_edit_columns_name, $master_field_md5,
 920     $destination_foreign_table, $destination_foreign_column, $options_array,
 921     $existrel_foreign, $table, &$seen_error, &$display_query, $foreign_db
 922 ) {
 923     $html_output = '';
 924     $create = false;
 925     $drop = false;
 926
 927     // Map the fieldname's md5 back to its real name
 928     $master_field = $multi_edit_columns_name[$master_field_md5];
 929
 930     $foreign_table = $destination_foreign_table[$master_field_md5];
 931     $foreign_field = $destination_foreign_column[$master_field_md5];
 932     if (! empty($foreign_db)
 933         && ! empty($foreign_table)
 934         && ! empty($foreign_field)
 935     ) {
 936         if ( isset($existrel_foreign[$master_field])) {
 937             $constraint_name = $existrel_foreign[$master_field]['constraint'];
 938             $on_delete = ! empty(
 939                         $existrel_foreign[$master_field]['on_delete'])
 940                         ? $existrel_foreign[$master_field]['on_delete'] : 'RESTRICT';
 941             $on_update = ! empty(
 942                         $existrel_foreign[$master_field]['on_update'])
 943                         ? $existrel_foreign[$master_field]['on_update'] : 'RESTRICT';
 944         }
 945         if (! isset($existrel_foreign[$master_field])) {
 946             // no key defined for this field
 947             $create = true;
 948         } elseif ($existrel_foreign[$master_field]['foreign_db'] != $foreign_db
 949             || $existrel_foreign[$master_field]['foreign_table'] != $foreign_table
 950             || $existrel_foreign[$master_field]['foreign_field'] != $foreign_field
 951             || $_REQUEST['constraint_name'][$master_field_md5] != $constraint_name
 952             || ($_REQUEST['on_delete'][$master_field_md5] != $on_delete)
 953             || ($_REQUEST['on_update'][$master_field_md5] != $on_update)
 954         ) {
 955             // another foreign key is already defined for this field
 956             // or an option has been changed for ON DELETE or ON UPDATE
 957             $drop = true;
 958             $create = true;
 959         } // end if... else....
 960     } elseif (isset($existrel_foreign[$master_field])) {
 961         $drop = true;
 962     } // end if... else....
 963
 964     $tmp_error_drop = false;
 965     if ($drop) {
 966         $drop_query = PMA_getSQLToDropForeignKey(
 967             $table, $existrel_foreign[$master_field]['constraint']
 968         );
 969         $display_query .= $drop_query . "\n";
 970         $GLOBALS['dbi']->tryQuery($drop_query);
 971         $tmp_error_drop = $GLOBALS['dbi']->getError();
 972
 973         if (! empty($tmp_error_drop)) {
 974             $seen_error = true;
 975             $html_output .= PMA_Util::mysqlDie(
 976                 $tmp_error_drop, $drop_query, false, '', false
 977             );
 978             return $html_output;
 979         }
 980     }
 981     $tmp_error_create = false;
 982     if ($create) {
 983         $create_query = PMA_getSQLToCreateForeignKey(
 984             $table, $master_field, $foreign_db, $foreign_table, $foreign_field,
 985             $_REQUEST['constraint_name'][$master_field_md5],
 986             $options_array[$_REQUEST['on_delete'][$master_field_md5]],
 987             $options_array[$_REQUEST['on_update'][$master_field_md5]]
 988         );
 989
 990         $display_query .= $create_query . "\n";
 991         $GLOBALS['dbi']->tryQuery($create_query);
 992         $tmp_error_create = $GLOBALS['dbi']->getError();
 993         if (! empty($tmp_error_create)) {
 994             $seen_error = true;
 995
 996             if (substr($tmp_error_create, 1, 4) == '1005') {
 997                 $message = PMA_Message::error(
 998                     __('Error creating foreign key on %1$s (check data types)')
 999                 );
1000                 $message->addParam($master_field);
1001                 $html_output .= $message->getDisplay();
1002             } else {
1003                 $html_output .= PMA_Util::mysqlDie(
1004                     $tmp_error_create, $create_query, false, '', false
1005                 );
1006             }
1007             $html_output .= PMA_Util::showMySQLDocu(
1008                 'InnoDB_foreign_key_constraints'
1009             ) . "\n";
1010         }
1011
1012         // this is an alteration and the old constraint has been dropped
1013         // without creation of a new one
1014         if ($drop && $create && empty($tmp_error_drop)
1015             && ! empty($tmp_error_create)
1016         ) {
1017             // a rollback may be better here
1018             $sql_query_recreate = '# Restoring the dropped constraint...' . "\n";
1019             $sql_query_recreate .= PMA_getSQLToCreateForeignKey(
1020                 $table,
1021                 $master_field,
1022                 $existrel_foreign[$master_field]['foreign_db'],
1023                 $existrel_foreign[$master_field]['foreign_table'],
1024                 $existrel_foreign[$master_field]['foreign_field'],
1025                 $existrel_foreign[$master_field]['constraint'],
1026                 $options_array[$existrel_foreign[$master_field]['on_delete']],
1027                 $options_array[$existrel_foreign[$master_field]['on_update']]
1028             );
1029             $display_query .= $sql_query_recreate . "\n";
1030             $GLOBALS['dbi']->tryQuery($sql_query_recreate);
1031         }
1032     }
1033
1034     return $html_output;
1035 }
1036 ?>