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             $tables_rs = $GLOBALS['dbi']->query(
 488                 'SHOW TABLE STATUS FROM ' . PMA_Util::backquote($foreign_db),
 489                 null,
 490                 PMA_DatabaseInterface::QUERY_STORE
 491             );
 492             while ($row = $GLOBALS['dbi']->fetchRow($tables_rs)) {
 493                 if (isset($row[1])
 494                     && strtoupper($row[1]) == $tbl_storage_engine
 495                 ) {
 496                     $tables[] = $row[0];
 497                 }
 498             }
 499         }
 500         $html_output .= PMA_generateRelationalDropdown(
 501             'destination_foreign_table[' . $myfield_md5 . ']',
 502             $tables,
 503             $foreign_table,
 504             __('Table')
 505         );
 506         // end of foreign table dropdown
 507
 508         // foreign column dropdown
 509         $columns = array();
 510         if ($foreign_db && $foreign_table) {
 511             if (isset($existrel_foreign[$myfield])) {
 512                 $foreign_column = $existrel_foreign[$myfield]['foreign_field'];
 513             }
 514             $table_obj = new PMA_Table($foreign_table, $foreign_db);
 515             $columns = $table_obj->getUniqueColumns(false, false);
 516         }
 517         $html_output .= PMA_generateRelationalDropdown(
 518             'destination_foreign_column[' . $myfield_md5 . ']',
 519             $columns,
 520             $foreign_column,
 521             __('Column')
 522         );
 523         $html_output .= '</span>';
 524         // end of foreign column dropdown
 525
 526         // For constraint name
 527         $html_output .= '<span class="formelement clearfloat">';
 528         $constraint_name = isset($existrel_foreign[$myfield]['constraint'])
 529             ? $existrel_foreign[$myfield]['constraint'] : '';
 530         $html_output .= __('Constraint name');
 531         $html_output .= '<input type="text" name="constraint_name['
 532             . $myfield_md5 . ']"'
 533             . ' value="' . $constraint_name . '"/>';
 534         $html_output .= '</span>' . "\n";
 535
 536         $html_output .= '<span class="formelement clearfloat">';
 537         // For ON DELETE and ON UPDATE, the default action
 538         // is RESTRICT as per MySQL doc; however, a SHOW CREATE TABLE
 539         // won't display the clause if it's set as RESTRICT.
 540         $on_delete = isset($existrel_foreign[$myfield]['on_delete'])
 541             ? $existrel_foreign[$myfield]['on_delete'] : 'RESTRICT';
 542         $html_output .= PMA_generateDropdown(
 543             'ON DELETE',
 544             'on_delete[' . $myfield_md5 . ']',
 545             $options_array,
 546             $on_delete
 547         );
 548         $html_output .= '</span>' . "\n";
 549
 550         $html_output .= '<span class="formelement clearfloat">' . "\n";
 551         $on_update = isset($existrel_foreign[$myfield]['on_update'])
 552             ? $existrel_foreign[$myfield]['on_update'] : 'RESTRICT';
 553         $html_output .= PMA_generateDropdown(
 554             'ON UPDATE',
 555             'on_update[' . $myfield_md5 . ']',
 556             $options_array,
 557             $on_update
 558         );
 559         $html_output .= '</span>' . "\n";
 560     } else {
 561         $html_output .= __('No index defined! Create one below');
 562     } // end if (a key exists)
 563     $html_output .= '</td>';
 564
 565     return $html_output;
 566 }
 567
 568 /**
 569  * Function to send html for table or column dropdown list
 570  *
 571  * @return void
 572  */
 573 function PMA_sendHtmlForTableOrColumnDropdownList()
 574 {
 575     if (isset($_REQUEST['foreignTable'])) { // if both db and table are selected
 576         PMA_sendHtmlForColumnDropdownList();
 577     } else { // if only the db is selected
 578         PMA_sendHtmlForTableDropdownList();
 579     }
 580     exit;
 581 }
 582
 583 /**
 584  * Function to send html for column dropdown list
 585  *
 586  * @return void
 587  */
 588 function PMA_sendHtmlForColumnDropdownList()
 589 {
 590     $response = PMA_Response::getInstance();
 591
 592     $foreignTable = $_REQUEST['foreignTable'];
 593     $table_obj = new PMA_Table($foreignTable, $_REQUEST['foreignDb']);
 594     $columns = array();
 595     foreach ($table_obj->getUniqueColumns(false, false) as $column) {
 596         $columns[] = htmlspecialchars($column);
 597     }
 598     $response->addJSON('columns', $columns);
 599 }
 600
 601 /**
 602  * Function to send html for table dropdown list
 603  *
 604  * @return void
 605  */
 606 function PMA_sendHtmlForTableDropdownList()
 607 {
 608     $response = PMA_Response::getInstance();
 609
 610     $foreign = isset($_REQUEST['foreign']) && $_REQUEST['foreign'] === 'true';
 611     if ($foreign) {
 612         $query = 'SHOW TABLE STATUS FROM '
 613             . PMA_Util::backquote($_REQUEST['foreignDb']);
 614         $tbl_storage_engine = strtoupper(
 615             PMA_Table::sGetStatusInfo(
 616                 $_REQUEST['db'],
 617                 $_REQUEST['table'],
 618                 'Engine'
 619             )
 620         );
 621     } else {
 622         $query = 'SHOW TABLES FROM '
 623             . PMA_Util::backquote($_REQUEST['foreignDb']);
 624     }
 625     $tables_rs = $GLOBALS['dbi']->query(
 626         $query,
 627         null,
 628         PMA_DatabaseInterface::QUERY_STORE
 629     );
 630     $tables = array();
 631     while ($row = $GLOBALS['dbi']->fetchArray($tables_rs)) {
 632         if ($foreign) {
 633             if (isset($row['Engine'])
 634                 && strtoupper($row['Engine']) == $tbl_storage_engine
 635             ) {
 636                 $tables[] = htmlspecialchars($row['Name']);
 637             }
 638         } else {
 639             $tables[] = htmlspecialchars($row[0]);
 640         }
 641     }
 642     $response->addJSON('tables', $tables);
 643 }
 644
 645 /**
 646  * Function to handle update for display field
 647  *
 648  * @param string $disp          field name
 649  * @param string $display_field display field
 650  * @param string $db            current database
 651  * @param string $table         current table
 652  * @param array  $cfgRelation   configuration relation
 653  *
 654  * @return void
 655  */
 656 function PMA_handleUpdateForDisplayField($disp, $display_field, $db, $table,
 657     $cfgRelation
 658 ) {
 659     $upd_query = PMA_getQueryForDisplayUpdate(
 660         $disp, $display_field, $db, $table, $cfgRelation
 661     );
 662     if ($upd_query) {
 663         PMA_queryAsControlUser($upd_query);
 664     }
 665 }
 666
 667 /**
 668  * Function to get display query for handlingdisplay update
 669  *
 670  * @param string $disp          field name
 671  * @param string $display_field display field
 672  * @param string $db            current database
 673  * @param string $table         current table
 674  * @param array  $cfgRelation   configuration relation
 675  *
 676  * @return string
 677  */
 678 function PMA_getQueryForDisplayUpdate($disp, $display_field, $db, $table,
 679     $cfgRelation
 680 ) {
 681     $upd_query = false;
 682     if ($disp) {
 683         if ($display_field != '') {
 684             $upd_query = 'UPDATE '
 685                 . PMA_Util::backquote($GLOBALS['cfgRelation']['db'])
 686                 . '.' . PMA_Util::backquote($cfgRelation['table_info'])
 687                 . ' SET display_field = \''
 688                 . PMA_Util::sqlAddSlashes($display_field) . '\''
 689                 . ' WHERE db_name  = \'' . PMA_Util::sqlAddSlashes($db) . '\''
 690                 . ' AND table_name = \'' . PMA_Util::sqlAddSlashes($table) . '\'';
 691         } else {
 692             $upd_query = 'DELETE FROM '
 693                 . PMA_Util::backquote($GLOBALS['cfgRelation']['db'])
 694                 . '.' . PMA_Util::backquote($cfgRelation['table_info'])
 695                 . ' WHERE db_name  = \'' . PMA_Util::sqlAddSlashes($db) . '\''
 696                 . ' AND table_name = \'' . PMA_Util::sqlAddSlashes($table) . '\'';
 697         }
 698     } elseif ($display_field != '') {
 699         $upd_query = 'INSERT INTO '
 700             . PMA_Util::backquote($GLOBALS['cfgRelation']['db'])
 701             . '.' . PMA_Util::backquote($cfgRelation['table_info'])
 702             . '(db_name, table_name, display_field) VALUES('
 703             . '\'' . PMA_Util::sqlAddSlashes($db) . '\','
 704             . '\'' . PMA_Util::sqlAddSlashes($table) . '\','
 705             . '\'' . PMA_Util::sqlAddSlashes($display_field) . '\')';
 706     }
 707
 708     return $upd_query;
 709 }
 710
 711 /**
 712  * Function to handle updates for internal relations
 713  *
 714  * @param string $destination_db          destination database
 715  * @param string $multi_edit_columns_name multi edit column name
 716  * @param string $destination_table       destination table
 717  * @param string $destination_column      destination column
 718  * @param array  $cfgRelation             configuration relation
 719  * @param string $db                      current database
 720  * @param string $table                   current table
 721  * @param array  $existrel                db, table, column
 722  *
 723  * @return void
 724  */
 725 function PMA_handleUpdatesForInternalRelations($destination_db,
 726     $multi_edit_columns_name, $destination_table, $destination_column, $cfgRelation,
 727     $db, $table, $existrel
 728 ) {
 729     foreach ($destination_db as $master_field_md5 => $foreign_db) {
 730         $upd_query = PMA_getQueryForInternalRelationUpdate(
 731             $multi_edit_columns_name,
 732             $master_field_md5, $foreign_db, $destination_table, $destination_column,
 733             $cfgRelation, $db, $table, isset($existrel) ? $existrel : null
 734         );
 735         if ($upd_query) {
 736             PMA_queryAsControlUser($upd_query);
 737         }
 738     }
 739 }
 740
 741 /**
 742  * Function to get update query for updating internal relations
 743  *
 744  * @param string $multi_edit_columns_name multi edit column names
 745  * @param string $master_field_md5        master field md5
 746  * @param string $foreign_db              foreign database
 747  * @param string $destination_table       destination table
 748  * @param string $destination_column      destination column
 749  * @param array  $cfgRelation             configuration relation
 750  * @param string $db                      current database
 751  * @param string $table                   current table
 752  * @param array  $existrel                db, table, column
 753  *
 754  * @return string
 755  */
 756 function PMA_getQueryForInternalRelationUpdate($multi_edit_columns_name,
 757     $master_field_md5, $foreign_db, $destination_table, $destination_column,
 758     $cfgRelation, $db, $table, $existrel
 759 ) {
 760     $upd_query = false;
 761
 762     // Map the fieldname's md5 back to its real name
 763     $master_field = $multi_edit_columns_name[$master_field_md5];
 764
 765     $foreign_table = $destination_table[$master_field_md5];
 766     $foreign_field = $destination_column[$master_field_md5];
 767     if (! empty($foreign_db)
 768         && ! empty($foreign_table)
 769         && ! empty($foreign_field)
 770     ) {
 771         if (! isset($existrel[$master_field])) {
 772             $upd_query  = 'INSERT INTO '
 773                 . PMA_Util::backquote($GLOBALS['cfgRelation']['db'])
 774                 . '.' . PMA_Util::backquote($cfgRelation['relation'])
 775                 . '(master_db, master_table, master_field, foreign_db,'
 776                 . ' foreign_table, foreign_field)'
 777                 . ' values('
 778                 . '\'' . PMA_Util::sqlAddSlashes($db) . '\', '
 779                 . '\'' . PMA_Util::sqlAddSlashes($table) . '\', '
 780                 . '\'' . PMA_Util::sqlAddSlashes($master_field) . '\', '
 781                 . '\'' . PMA_Util::sqlAddSlashes($foreign_db) . '\', '
 782                 . '\'' . PMA_Util::sqlAddSlashes($foreign_table) . '\','
 783                 . '\'' . PMA_Util::sqlAddSlashes($foreign_field) . '\')';
 784
 785         } elseif ($existrel[$master_field]['foreign_db'] != $foreign_db
 786             || $existrel[$master_field]['foreign_table'] != $foreign_table
 787             || $existrel[$master_field]['foreign_field'] != $foreign_field
 788         ) {
 789             $upd_query  = 'UPDATE '
 790                 . PMA_Util::backquote($GLOBALS['cfgRelation']['db'])
 791                 . '.' . PMA_Util::backquote($cfgRelation['relation']) . ' SET'
 792                 . ' foreign_db       = \''
 793                 . PMA_Util::sqlAddSlashes($foreign_db) . '\', '
 794                 . ' foreign_table    = \''
 795                 . PMA_Util::sqlAddSlashes($foreign_table) . '\', '
 796                 . ' foreign_field    = \''
 797                 . PMA_Util::sqlAddSlashes($foreign_field) . '\' '
 798                 . ' WHERE master_db  = \''
 799                 . PMA_Util::sqlAddSlashes($db) . '\''
 800                 . ' AND master_table = \''
 801                 . PMA_Util::sqlAddSlashes($table) . '\''
 802                 . ' AND master_field = \''
 803                 . PMA_Util::sqlAddSlashes($master_field) . '\'';
 804         } // end if... else....
 805     } elseif (isset($existrel[$master_field])) {
 806         $upd_query = 'DELETE FROM '
 807             . PMA_Util::backquote($GLOBALS['cfgRelation']['db'])
 808             . '.' . PMA_Util::backquote($cfgRelation['relation'])
 809             . ' WHERE master_db  = \'' . PMA_Util::sqlAddSlashes($db) . '\''
 810             . ' AND master_table = \'' . PMA_Util::sqlAddSlashes($table) . '\''
 811             . ' AND master_field = \'' . PMA_Util::sqlAddSlashes($master_field)
 812             . '\'';
 813     } // end if... else....
 814
 815     return $upd_query;
 816 }
 817
 818 /**
 819  * Function to handle foreign key updates
 820  *
 821  * @param string $destination_foreign_db     destination foreign database
 822  * @param string $multi_edit_columns_name    multi edit column names
 823  * @param string $destination_foreign_table  destination foreign table
 824  * @param string $destination_foreign_column destination foreign column
 825  * @param array  $options_array              options array
 826  * @param string $table                      current table
 827  * @param array  $existrel_foreign           db, table, column
 828  *
 829  * @return string
 830  */
 831 function PMA_handleUpdatesForForeignKeys($destination_foreign_db,
 832     $multi_edit_columns_name, $destination_foreign_table,
 833     $destination_foreign_column, $options_array, $table, $existrel_foreign
 834 ) {
 835     $html_output = '';
 836     $display_query = '';
 837     $seen_error = false;
 838     foreach ($destination_foreign_db as $master_field_md5 => $foreign_db) {
 839         $html_output .= PMA_handleUpdateForForeignKey(
 840             $multi_edit_columns_name, $master_field_md5,
 841             $destination_foreign_table, $destination_foreign_column, $options_array,
 842             $existrel_foreign, $table, $seen_error, $display_query, $foreign_db
 843         );
 844     } // end foreach
 845     if (! empty($display_query) && ! $seen_error) {
 846         $GLOBALS['display_query'] = $display_query;
 847         $html_output = PMA_Util::getMessage(
 848             __('Your SQL query has been executed successfully'),
 849             null, 'success'
 850         );
 851     }
 852
 853     return $html_output;
 854 }
 855
 856 /**
 857  * Function to handle update for a foreign key
 858  *
 859  * @param array  $multi_edit_columns_name    multu edit columns name
 860  * @param string $master_field_md5           master field md5
 861  * @param string $destination_foreign_table  destination foreign table
 862  * @param string $destination_foreign_column destination foreign column
 863  * @param array  $options_array              options array
 864  * @param array  $existrel_foreign           db, table, column
 865  * @param string $table                      current table
 866  * @param bool   &$seen_error                whether seen error
 867  * @param string &$display_query             display query
 868  * @param string $foreign_db                 foreign database
 869  *
 870  * @return string
 871  */
 872 function PMA_handleUpdateForForeignKey($multi_edit_columns_name, $master_field_md5,
 873     $destination_foreign_table, $destination_foreign_column, $options_array,
 874     $existrel_foreign, $table, &$seen_error, &$display_query, $foreign_db
 875 ) {
 876     $html_output = '';
 877     $create = false;
 878     $drop = false;
 879
 880     // Map the fieldname's md5 back to its real name
 881     $master_field = $multi_edit_columns_name[$master_field_md5];
 882
 883     $foreign_table = $destination_foreign_table[$master_field_md5];
 884     $foreign_field = $destination_foreign_column[$master_field_md5];
 885     if (! empty($foreign_db)
 886         && ! empty($foreign_table)
 887         && ! empty($foreign_field)
 888     ) {
 889         if ( isset($existrel_foreign[$master_field])) {
 890             $constraint_name = $existrel_foreign[$master_field]['constraint'];
 891             $on_delete = ! empty(
 892                         $existrel_foreign[$master_field]['on_delete'])
 893                         ? $existrel_foreign[$master_field]['on_delete'] : 'RESTRICT';
 894             $on_update = ! empty(
 895                         $existrel_foreign[$master_field]['on_update'])
 896                         ? $existrel_foreign[$master_field]['on_update'] : 'RESTRICT';
 897         }
 898         if (! isset($existrel_foreign[$master_field])) {
 899             // no key defined for this field
 900             $create = true;
 901         } elseif ($existrel_foreign[$master_field]['foreign_db'] != $foreign_db
 902             || $existrel_foreign[$master_field]['foreign_table'] != $foreign_table
 903             || $existrel_foreign[$master_field]['foreign_field'] != $foreign_field
 904             || $_REQUEST['constraint_name'][$master_field_md5] != $constraint_name
 905             || ($_REQUEST['on_delete'][$master_field_md5] != $on_delete)
 906             || ($_REQUEST['on_update'][$master_field_md5] != $on_update)
 907         ) {
 908             // another foreign key is already defined for this field
 909             // or an option has been changed for ON DELETE or ON UPDATE
 910             $drop = true;
 911             $create = true;
 912         } // end if... else....
 913     } elseif (isset($existrel_foreign[$master_field])) {
 914         $drop = true;
 915     } // end if... else....
 916
 917     $tmp_error_drop = false;
 918     if ($drop) {
 919         $drop_query = PMA_getSQLToDropForeignKey(
 920             $table, $existrel_foreign[$master_field]['constraint']
 921         );
 922         $display_query .= $drop_query . "\n";
 923         $GLOBALS['dbi']->tryQuery($drop_query);
 924         $tmp_error_drop = $GLOBALS['dbi']->getError();
 925
 926         if (! empty($tmp_error_drop)) {
 927             $seen_error = true;
 928             $html_output .= PMA_Util::mysqlDie(
 929                 $tmp_error_drop, $drop_query, false, '', false
 930             );
 931             return $html_output;
 932         }
 933     }
 934     $tmp_error_create = false;
 935     if ($create) {
 936         $create_query = PMA_getSQLToCreateForeignKey(
 937             $table, $master_field, $foreign_db, $foreign_table, $foreign_field,
 938             $_REQUEST['constraint_name'][$master_field_md5],
 939             $options_array[$_REQUEST['on_delete'][$master_field_md5]],
 940             $options_array[$_REQUEST['on_update'][$master_field_md5]]
 941         );
 942
 943         $display_query .= $create_query . "\n";
 944         $GLOBALS['dbi']->tryQuery($create_query);
 945         $tmp_error_create = $GLOBALS['dbi']->getError();
 946         if (! empty($tmp_error_create)) {
 947             $seen_error = true;
 948
 949             if (substr($tmp_error_create, 1, 4) == '1005') {
 950                 $message = PMA_Message::error(
 951                     __('Error creating foreign key on %1$s (check data types)')
 952                 );
 953                 $message->addParam($master_field);
 954                 $message->display();
 955             } else {
 956                 $html_output .= PMA_Util::mysqlDie(
 957                     $tmp_error_create, $create_query, false, '', false
 958                 );
 959             }
 960             $html_output .= PMA_Util::showMySQLDocu(
 961                 'InnoDB_foreign_key_constraints'
 962             ) . "\n";
 963         }
 964
 965         // this is an alteration and the old constraint has been dropped
 966         // without creation of a new one
 967         if ($drop && $create && empty($tmp_error_drop)
 968             && ! empty($tmp_error_create)
 969         ) {
 970             // a rollback may be better here
 971             $sql_query_recreate = '# Restoring the dropped constraint...' . "\n";
 972             $sql_query_recreate .= PMA_getSQLToCreateForeignKey(
 973                 $table,
 974                 $master_field,
 975                 $existrel_foreign[$master_field]['foreign_db'],
 976                 $existrel_foreign[$master_field]['foreign_table'],
 977                 $existrel_foreign[$master_field]['foreign_field'],
 978                 $existrel_foreign[$master_field]['constraint'],
 979                 $options_array[$existrel_foreign[$master_field]['on_delete']],
 980                 $options_array[$existrel_foreign[$master_field]['on_update']]
 981             );
 982             $display_query .= $sql_query_recreate . "\n";
 983             $GLOBALS['dbi']->tryQuery($sql_query_recreate);
 984         }
 985     }
 986
 987     return $html_output;
 988 }
 989 ?>