libraries/pmd_common.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Common functions for Designer
   5  *
   6  * @package PhpMyAdmin-Designer
   7  */
   8 use PMA\libraries\Table;
   9
  10 /**
  11  * Block attempts to directly run this script
  12  */
  13 if (! defined('PHPMYADMIN')) {
  14     exit;
  15 }
  16
  17 $GLOBALS['PMD']['STYLE']          = 'default';
  18
  19 $cfgRelation = PMA_getRelationsParam();
  20
  21 /**
  22  * Retrieves table info and stores it in $GLOBALS['PMD']
  23  *
  24  * @return array with table info
  25  */
  26 function PMA_getTablesInfo()
  27 {
  28     $retval = array();
  29
  30     $GLOBALS['PMD']['TABLE_NAME'] = array();// that foreach no error
  31     $GLOBALS['PMD']['OWNER'] = array();
  32     $GLOBALS['PMD']['TABLE_NAME_SMALL'] = array();
  33
  34     $tables = $GLOBALS['dbi']->getTablesFull($GLOBALS['db']);
  35     // seems to be needed later
  36     $GLOBALS['dbi']->selectDb($GLOBALS['db']);
  37     $i = 0;
  38     foreach ($tables as $one_table) {
  39         $GLOBALS['PMD']['TABLE_NAME'][$i]
  40             = $GLOBALS['db'] . "." . $one_table['TABLE_NAME'];
  41         $GLOBALS['PMD']['OWNER'][$i] = $GLOBALS['db'];
  42         $GLOBALS['PMD']['TABLE_NAME_SMALL'][$i] = htmlspecialchars(
  43             $one_table['TABLE_NAME'], ENT_QUOTES
  44         );
  45
  46         $GLOBALS['PMD_URL']['TABLE_NAME'][$i]
  47             = $GLOBALS['db'] . "." . $one_table['TABLE_NAME'];
  48         $GLOBALS['PMD_URL']['OWNER'][$i] = $GLOBALS['db'];
  49         $GLOBALS['PMD_URL']['TABLE_NAME_SMALL'][$i]
  50             = $one_table['TABLE_NAME'];
  51
  52         $GLOBALS['PMD_OUT']['TABLE_NAME'][$i] = htmlspecialchars(
  53             $GLOBALS['db'] . "." . $one_table['TABLE_NAME'], ENT_QUOTES
  54         );
  55         $GLOBALS['PMD_OUT']['OWNER'][$i] = htmlspecialchars(
  56             $GLOBALS['db'], ENT_QUOTES
  57         );
  58         $GLOBALS['PMD_OUT']['TABLE_NAME_SMALL'][$i] = htmlspecialchars(
  59             $one_table['TABLE_NAME'], ENT_QUOTES
  60         );
  61
  62         $GLOBALS['PMD']['TABLE_TYPE'][$i] = mb_strtoupper(
  63             $one_table['ENGINE']
  64         );
  65
  66         $DF = PMA_getDisplayField($GLOBALS['db'], $one_table['TABLE_NAME']);
  67         if ($DF != '') {
  68             $retval[$GLOBALS['PMD_URL']["TABLE_NAME_SMALL"][$i]] = $DF;
  69         }
  70
  71         $i++;
  72     }
  73
  74     return $retval;
  75 }
  76
  77 /**
  78  * Retrieves table column info
  79  *
  80  * @return array   table column nfo
  81  */
  82 function PMA_getColumnsInfo()
  83 {
  84     $GLOBALS['dbi']->selectDb($GLOBALS['db']);
  85     $tab_column = array();
  86     for ($i = 0, $cnt = count($GLOBALS['PMD']["TABLE_NAME"]); $i < $cnt; $i++) {
  87         $fields_rs = $GLOBALS['dbi']->query(
  88             $GLOBALS['dbi']->getColumnsSql(
  89                 $GLOBALS['db'],
  90                 $GLOBALS['PMD']["TABLE_NAME_SMALL"][$i],
  91                 null,
  92                 true
  93             ),
  94             null,
  95             PMA\libraries\DatabaseInterface::QUERY_STORE
  96         );
  97         $tbl_name_i = $GLOBALS['PMD']['TABLE_NAME'][$i];
  98         $j = 0;
  99         while ($row = $GLOBALS['dbi']->fetchAssoc($fields_rs)) {
 100             $tab_column[$tbl_name_i]['COLUMN_ID'][$j]   = $j;
 101             $tab_column[$tbl_name_i]['COLUMN_NAME'][$j] = $row['Field'];
 102             $tab_column[$tbl_name_i]['TYPE'][$j]        = $row['Type'];
 103             $tab_column[$tbl_name_i]['NULLABLE'][$j]    = $row['Null'];
 104             $j++;
 105         }
 106     }
 107     return $tab_column;
 108 }
 109
 110 /**
 111  * Returns JavaScript code for initializing vars
 112  *
 113  * @return string   JavaScript code
 114  */
 115 function PMA_getScriptContr()
 116 {
 117     $GLOBALS['dbi']->selectDb($GLOBALS['db']);
 118     $con = array();
 119     $con["C_NAME"] = array();
 120     $i = 0;
 121     $alltab_rs = $GLOBALS['dbi']->query(
 122         'SHOW TABLES FROM ' . PMA\libraries\Util::backquote($GLOBALS['db']),
 123         null,
 124         PMA\libraries\DatabaseInterface::QUERY_STORE
 125     );
 126     while ($val = @$GLOBALS['dbi']->fetchRow($alltab_rs)) {
 127         $row = PMA_getForeigners($GLOBALS['db'], $val[0], '', 'internal');
 128
 129         if ($row !== false) {
 130             foreach ($row as $field => $value) {
 131                 $con['C_NAME'][$i] = '';
 132                 $con['DTN'][$i]    = urlencode($GLOBALS['db'] . "." . $val[0]);
 133                 $con['DCN'][$i]    = urlencode($field);
 134                 $con['STN'][$i]    = urlencode(
 135                     $value['foreign_db'] . "." . $value['foreign_table']
 136                 );
 137                 $con['SCN'][$i]    = urlencode($value['foreign_field']);
 138                 $i++;
 139             }
 140         }
 141         $row = PMA_getForeigners($GLOBALS['db'], $val[0], '', 'foreign');
 142
 143         if ($row !== false) {
 144             foreach ($row['foreign_keys_data'] as $one_key) {
 145                 foreach ($one_key['index_list'] as $index => $one_field) {
 146                     $con['C_NAME'][$i] = $one_key['constraint'];
 147                     $con['DTN'][$i]    = urlencode($GLOBALS['db'] . "." . $val[0]);
 148                     $con['DCN'][$i]    = urlencode($one_field);
 149                     $con['STN'][$i]    = urlencode(
 150                         (isset($one_key['ref_db_name']) ?
 151                             $one_key['ref_db_name'] : $GLOBALS['db'])
 152                         . "." . $one_key['ref_table_name']
 153                     );
 154                     $con['SCN'][$i] = urlencode($one_key['ref_index_list'][$index]);
 155                     $i++;
 156                 }
 157             }
 158         }
 159     }
 160
 161     $ti = 0;
 162     $retval = array();
 163     for ($i = 0, $cnt = count($con["C_NAME"]); $i < $cnt; $i++) {
 164         $c_name_i = $con['C_NAME'][$i];
 165         $dtn_i = $con['DTN'][$i];
 166         $retval[$ti] = array();
 167         $retval[$ti][$c_name_i] = array();
 168         if (in_array($dtn_i, $GLOBALS['PMD_URL']["TABLE_NAME"])
 169             && in_array($con['STN'][$i], $GLOBALS['PMD_URL']["TABLE_NAME"])
 170         ) {
 171             $retval[$ti][$c_name_i][$dtn_i] = array();
 172             $retval[$ti][$c_name_i][$dtn_i][$con['DCN'][$i]] = array(
 173                 0 => $con['STN'][$i],
 174                 1 => $con['SCN'][$i]
 175             );
 176         }
 177         $ti++;
 178     }
 179     return $retval;
 180 }
 181
 182 /**
 183  * Returns UNIQUE and PRIMARY indices
 184  *
 185  * @return array unique or primary indices
 186  */
 187 function PMA_getPKOrUniqueKeys()
 188 {
 189     return PMA_getAllKeys(true);
 190 }
 191
 192 /**
 193  * Returns all indices
 194  *
 195  * @param bool $unique_only whether to include only unique ones
 196  *
 197  * @return array indices
 198  */
 199 function PMA_getAllKeys($unique_only = false)
 200 {
 201     $keys = array();
 202
 203     foreach ($GLOBALS['PMD']['TABLE_NAME_SMALL'] as $I => $table) {
 204         $schema = $GLOBALS['PMD']['OWNER'][$I];
 205         // for now, take into account only the first index segment
 206         foreach (PMA\libraries\Index::getFromTable($table, $schema) as $index) {
 207             if ($unique_only && ! $index->isUnique()) {
 208                 continue;
 209             }
 210             $columns = $index->getColumns();
 211             foreach ($columns as $column_name => $dummy) {
 212                 $keys[$schema . '.' . $table . '.' . $column_name] = 1;
 213             }
 214         }
 215     }
 216     return $keys;
 217 }
 218
 219 /**
 220  * Return script to create j_tab and h_tab arrays
 221  *
 222  * @return string
 223  */
 224 function PMA_getScriptTabs()
 225 {
 226     $retval = array(
 227         'j_tabs' => array(),
 228         'h_tabs' => array()
 229     );
 230
 231     for ($i = 0, $cnt = count($GLOBALS['PMD']['TABLE_NAME']); $i < $cnt; $i++) {
 232         $j = 0;
 233         if (PMA\libraries\Util::isForeignKeySupported($GLOBALS['PMD']['TABLE_TYPE'][$i])) {
 234             $j = 1;
 235         }
 236         $retval['j_tabs'][$GLOBALS['PMD_URL']['TABLE_NAME'][$i]] = $j;
 237         $retval['h_tabs'][$GLOBALS['PMD_URL']['TABLE_NAME'][$i]] = 1;
 238     }
 239     return $retval;
 240 }
 241
 242 /**
 243  * Returns table positions of a given pdf page
 244  *
 245  * @param int $pg pdf page id
 246  *
 247  * @return array of table positions
 248  */
 249 function PMA_getTablePositions($pg)
 250 {
 251     $cfgRelation = PMA_getRelationsParam();
 252     if (! $cfgRelation['pdfwork']) {
 253         return null;
 254     }
 255
 256     $query = "
 257         SELECT CONCAT_WS('.', `db_name`, `table_name`) AS `name`,
 258             `x` AS `X`,
 259             `y` AS `Y`,
 260             1 AS `V`,
 261             1 AS `H`
 262         FROM " . PMA\libraries\Util::backquote($cfgRelation['db'])
 263             . "." . PMA\libraries\Util::backquote($cfgRelation['table_coords']) . "
 264         WHERE pdf_page_number = " . intval($pg);
 265
 266     $tab_pos = $GLOBALS['dbi']->fetchResult(
 267         $query,
 268         'name',
 269         null,
 270         $GLOBALS['controllink'],
 271         PMA\libraries\DatabaseInterface::QUERY_STORE
 272     );
 273     return $tab_pos;
 274 }
 275
 276 /**
 277  * Returns page name of a given pdf page
 278  *
 279  * @param int $pg pdf page id
 280  *
 281  * @return String table name
 282  */
 283 function PMA_getPageName($pg)
 284 {
 285     $cfgRelation = PMA_getRelationsParam();
 286     if (! $cfgRelation['pdfwork']) {
 287         return null;
 288     }
 289
 290     $query = "SELECT `page_descr`"
 291         . " FROM " . PMA\libraries\Util::backquote($cfgRelation['db'])
 292         . "." . PMA\libraries\Util::backquote($cfgRelation['pdf_pages'])
 293         . " WHERE " . PMA\libraries\Util::backquote('page_nr') . " = " . intval($pg);
 294     $page_name = $GLOBALS['dbi']->fetchResult(
 295         $query,
 296         null,
 297         null,
 298         $GLOBALS['controllink'],
 299         PMA\libraries\DatabaseInterface::QUERY_STORE
 300     );
 301     return count($page_name) ? $page_name[0] : null;
 302 }
 303
 304 /**
 305  * Deletes a given pdf page and its corresponding coordinates
 306  *
 307  * @param int $pg page id
 308  *
 309  * @return boolean success/failure
 310  */
 311 function PMA_deletePage($pg)
 312 {
 313     $cfgRelation = PMA_getRelationsParam();
 314     if (! $cfgRelation['pdfwork']) {
 315         return false;
 316     }
 317
 318     $query = "DELETE FROM " . PMA\libraries\Util::backquote($cfgRelation['db'])
 319         . "." . PMA\libraries\Util::backquote($cfgRelation['table_coords'])
 320         . " WHERE " . PMA\libraries\Util::backquote('pdf_page_number') . " = " . intval($pg);
 321     $success = PMA_queryAsControlUser(
 322         $query, true, PMA\libraries\DatabaseInterface::QUERY_STORE
 323     );
 324
 325     if ($success) {
 326         $query = "DELETE FROM " . PMA\libraries\Util::backquote($cfgRelation['db'])
 327             . "." . PMA\libraries\Util::backquote($cfgRelation['pdf_pages'])
 328             . " WHERE " . PMA\libraries\Util::backquote('page_nr') . " = " . intval($pg);
 329         $success = PMA_queryAsControlUser(
 330             $query, true, PMA\libraries\DatabaseInterface::QUERY_STORE
 331         );
 332     }
 333
 334     return (boolean) $success;
 335 }
 336
 337 /**
 338  * Returns the id of the default pdf page of the database.
 339  * Default page is the one which has the same name as the database.
 340  *
 341  * @param string $db database
 342  *
 343  * @return int id of the default pdf page for the database
 344  */
 345 function PMA_getDefaultPage($db)
 346 {
 347     $cfgRelation = PMA_getRelationsParam();
 348     if (! $cfgRelation['pdfwork']) {
 349         return null;
 350     }
 351
 352     $query = "SELECT `page_nr`"
 353         . " FROM " . PMA\libraries\Util::backquote($cfgRelation['db'])
 354         . "." . PMA\libraries\Util::backquote($cfgRelation['pdf_pages'])
 355         . " WHERE `db_name` = '" . $GLOBALS['dbi']->escapeString($db) . "'"
 356         . " AND `page_descr` = '" .  $GLOBALS['dbi']->escapeString($db) . "'";
 357
 358     $default_page_no = $GLOBALS['dbi']->fetchResult(
 359         $query,
 360         null,
 361         null,
 362         $GLOBALS['controllink'],
 363         PMA\libraries\DatabaseInterface::QUERY_STORE
 364     );
 365
 366     if (count($default_page_no)) {
 367         return intval($default_page_no[0]);
 368     }
 369     return -1;
 370 }
 371
 372 /**
 373  * Get the id of the page to load. If a default page exists it will be returned.
 374  * If no such exists, returns the id of the first page of the database.
 375  *
 376  * @param string $db database
 377  *
 378  * @return int id of the page to load
 379  */
 380 function PMA_getLoadingPage($db)
 381 {
 382     $cfgRelation = PMA_getRelationsParam();
 383     if (! $cfgRelation['pdfwork']) {
 384         return null;
 385     }
 386
 387     $page_no = -1;
 388
 389     $default_page_no = PMA_getDefaultPage($db);
 390     if ($default_page_no != -1) {
 391         $page_no = $default_page_no;
 392     } else {
 393         $query = "SELECT MIN(`page_nr`)"
 394             . " FROM " . PMA\libraries\Util::backquote($cfgRelation['db'])
 395             . "." . PMA\libraries\Util::backquote($cfgRelation['pdf_pages'])
 396             . " WHERE `db_name` = '" . $GLOBALS['dbi']->escapeString($db) . "'";
 397
 398         $min_page_no = $GLOBALS['dbi']->fetchResult(
 399             $query,
 400             null,
 401             null,
 402             $GLOBALS['controllink'],
 403             PMA\libraries\DatabaseInterface::QUERY_STORE
 404         );
 405         if (count($min_page_no[0])) {
 406             $page_no = $min_page_no[0];
 407         }
 408     }
 409     return intval($page_no);
 410 }
 411
 412 /**
 413  * Creates a new page and returns its auto-incrementing id
 414  *
 415  * @param string $pageName name of the page
 416  * @param string $db       name of the database
 417  *
 418  * @return int|null
 419  */
 420 function PMA_createNewPage($pageName, $db)
 421 {
 422     $cfgRelation = PMA_getRelationsParam();
 423     if ($cfgRelation['pdfwork']) {
 424         $pageNumber = PMA_REL_createPage(
 425             $pageName,
 426             $cfgRelation,
 427             $db
 428         );
 429         return $pageNumber;
 430     }
 431     return null;
 432 }
 433
 434 /**
 435  * Saves positions of table(s) of a given pdf page
 436  *
 437  * @param int $pg pdf page id
 438  *
 439  * @return boolean success/failure
 440  */
 441 function PMA_saveTablePositions($pg)
 442 {
 443     $cfgRelation = PMA_getRelationsParam();
 444     if (! $cfgRelation['pdfwork']) {
 445         return false;
 446     }
 447
 448     $query =  "DELETE FROM "
 449         . PMA\libraries\Util::backquote($GLOBALS['cfgRelation']['db'])
 450         . "." . PMA\libraries\Util::backquote(
 451             $GLOBALS['cfgRelation']['table_coords']
 452         )
 453         . " WHERE `db_name` = '" . $GLOBALS['dbi']->escapeString($_REQUEST['db'])
 454         . "'"
 455         . " AND `pdf_page_number` = '" . $GLOBALS['dbi']->escapeString($pg)
 456         . "'";
 457
 458     $res = PMA_queryAsControlUser(
 459         $query,
 460         true,
 461         PMA\libraries\DatabaseInterface::QUERY_STORE
 462     );
 463
 464     if (!$res) {
 465         return (boolean)$res;
 466     }
 467
 468     foreach ($_REQUEST['t_h'] as $key => $value) {
 469         list($DB, $TAB) = explode(".", $key);
 470         if (!$value) {
 471             continue;
 472         }
 473
 474         $query = "INSERT INTO "
 475             . PMA\libraries\Util::backquote($GLOBALS['cfgRelation']['db']) . "."
 476             . PMA\libraries\Util::backquote($GLOBALS['cfgRelation']['table_coords'])
 477             . " (`db_name`, `table_name`, `pdf_page_number`, `x`, `y`)"
 478             . " VALUES ("
 479             . "'" . $GLOBALS['dbi']->escapeString($DB) . "', "
 480             . "'" . $GLOBALS['dbi']->escapeString($TAB) . "', "
 481             . "'" . $GLOBALS['dbi']->escapeString($pg) . "', "
 482             . "'" . $GLOBALS['dbi']->escapeString($_REQUEST['t_x'][$key]) . "', "
 483             . "'" . $GLOBALS['dbi']->escapeString($_REQUEST['t_y'][$key]) . "')";
 484
 485         $res = PMA_queryAsControlUser(
 486             $query,  true, PMA\libraries\DatabaseInterface::QUERY_STORE
 487         );
 488     }
 489
 490     return (boolean) $res;
 491 }
 492
 493 /**
 494  * Saves the display field for a table.
 495  *
 496  * @param string $db    database name
 497  * @param string $table table name
 498  * @param string $field display field name
 499  *
 500  * @return boolean
 501  */
 502 function PMA_saveDisplayField($db, $table, $field)
 503 {
 504     $cfgRelation = PMA_getRelationsParam();
 505     if (!$cfgRelation['displaywork']) {
 506         return false;
 507     }
 508
 509     $disp = PMA_getDisplayField($db, $table);
 510     if ($disp && $disp === $field) {
 511         $field = '';
 512     }
 513
 514     $upd_query = new Table($table, $db, $GLOBALS['dbi']);
 515     $upd_query->updateDisplayField($disp, $field, $cfgRelation);
 516
 517     return true;
 518 }
 519
 520 /**
 521  * Adds a new foreign relation
 522  *
 523  * @param string $db        database name
 524  * @param string $T1        foreign table
 525  * @param string $F1        foreign field
 526  * @param string $T2        master table
 527  * @param string $F2        master field
 528  * @param string $on_delete on delete action
 529  * @param string $on_update on update action
 530  *
 531  * @return array array of success/failure and message
 532  */
 533 function PMA_addNewRelation($db, $T1, $F1, $T2, $F2, $on_delete, $on_update)
 534 {
 535     $tables = $GLOBALS['dbi']->getTablesFull($db, $T1);
 536     $type_T1 = mb_strtoupper($tables[$T1]['ENGINE']);
 537     $tables = $GLOBALS['dbi']->getTablesFull($db, $T2);
 538     $type_T2 = mb_strtoupper($tables[$T2]['ENGINE']);
 539
 540     // native foreign key
 541     if (PMA\libraries\Util::isForeignKeySupported($type_T1)
 542         && PMA\libraries\Util::isForeignKeySupported($type_T2)
 543         && $type_T1 == $type_T2
 544     ) {
 545         // relation exists?
 546         $existrel_foreign = PMA_getForeigners($db, $T2, '', 'foreign');
 547         $foreigner = PMA_searchColumnInForeigners($existrel_foreign, $F2);
 548         if ($foreigner
 549             && isset($foreigner['constraint'])
 550         ) {
 551             return array(false, __('Error: relationship already exists.'));
 552         }
 553         // note: in InnoDB, the index does not requires to be on a PRIMARY
 554         // or UNIQUE key
 555         // improve: check all other requirements for InnoDB relations
 556         $result = $GLOBALS['dbi']->query(
 557             'SHOW INDEX FROM ' . PMA\libraries\Util::backquote($db)
 558             . '.' . PMA\libraries\Util::backquote($T1) . ';'
 559         );
 560
 561         // will be use to emphasis prim. keys in the table view
 562         $index_array1 = array();
 563         while ($row = $GLOBALS['dbi']->fetchAssoc($result)) {
 564             $index_array1[$row['Column_name']] = 1;
 565         }
 566         $GLOBALS['dbi']->freeResult($result);
 567
 568         $result = $GLOBALS['dbi']->query(
 569             'SHOW INDEX FROM ' . PMA\libraries\Util::backquote($db)
 570             . '.' . PMA\libraries\Util::backquote($T2) . ';'
 571         );
 572         // will be used to emphasis prim. keys in the table view
 573         $index_array2 = array();
 574         while ($row = $GLOBALS['dbi']->fetchAssoc($result)) {
 575             $index_array2[$row['Column_name']] = 1;
 576         }
 577         $GLOBALS['dbi']->freeResult($result);
 578
 579         if (! empty($index_array1[$F1]) && ! empty($index_array2[$F2])) {
 580             $upd_query  = 'ALTER TABLE ' . PMA\libraries\Util::backquote($db)
 581                 . '.' . PMA\libraries\Util::backquote($T2)
 582                 . ' ADD FOREIGN KEY ('
 583                 . PMA\libraries\Util::backquote($F2) . ')'
 584                 . ' REFERENCES '
 585                 . PMA\libraries\Util::backquote($db) . '.'
 586                 . PMA\libraries\Util::backquote($T1) . '('
 587                 . PMA\libraries\Util::backquote($F1) . ')';
 588
 589             if ($on_delete != 'nix') {
 590                 $upd_query   .= ' ON DELETE ' . $on_delete;
 591             }
 592             if ($on_update != 'nix') {
 593                 $upd_query   .= ' ON UPDATE ' . $on_update;
 594             }
 595             $upd_query .= ';';
 596             if ($GLOBALS['dbi']->tryQuery($upd_query)) {
 597                 return array(true, __('FOREIGN KEY relationship has been added.'));
 598             }
 599
 600             $error = $GLOBALS['dbi']->getError();
 601             return array(
 602                 false,
 603                 __('Error: FOREIGN KEY relationship could not be added!')
 604                 . "<br/>" . $error
 605             );
 606         }
 607
 608         return array(false, __('Error: Missing index on column(s).'));
 609     }
 610
 611     // internal (pmadb) relation
 612     if ($GLOBALS['cfgRelation']['relwork'] == false) {
 613         return array(false, __('Error: Relational features are disabled!'));
 614     }
 615
 616     // no need to recheck if the keys are primary or unique at this point,
 617     // this was checked on the interface part
 618
 619     $q  = "INSERT INTO "
 620         . PMA\libraries\Util::backquote($GLOBALS['cfgRelation']['db'])
 621         . "."
 622         . PMA\libraries\Util::backquote($GLOBALS['cfgRelation']['relation'])
 623         . "(master_db, master_table, master_field, "
 624         . "foreign_db, foreign_table, foreign_field)"
 625         . " values("
 626         . "'" . $GLOBALS['dbi']->escapeString($db) . "', "
 627         . "'" . $GLOBALS['dbi']->escapeString($T2) . "', "
 628         . "'" . $GLOBALS['dbi']->escapeString($F2) . "', "
 629         . "'" . $GLOBALS['dbi']->escapeString($db) . "', "
 630         . "'" . $GLOBALS['dbi']->escapeString($T1) . "', "
 631         . "'" . $GLOBALS['dbi']->escapeString($F1) . "')";
 632
 633     if (PMA_queryAsControlUser($q, false, PMA\libraries\DatabaseInterface::QUERY_STORE)
 634     ) {
 635         return array(true, __('Internal relationship has been added.'));
 636     }
 637
 638     $error = $GLOBALS['dbi']->getError($GLOBALS['controllink']);
 639     return array(
 640         false,
 641         __('Error: Internal relationship could not be added!')
 642         . "<br/>" . $error
 643     );
 644 }
 645
 646 /**
 647  * Removes a foreign relation
 648  *
 649  * @param string $T1 foreign db.table
 650  * @param string $F1 foreign field
 651  * @param string $T2 master db.table
 652  * @param string $F2 master field
 653  *
 654  * @return array array of success/failure and message
 655  */
 656 function PMA_removeRelation($T1, $F1, $T2, $F2)
 657 {
 658     list($DB1, $T1) = explode(".", $T1);
 659     list($DB2, $T2) = explode(".", $T2);
 660
 661     $tables = $GLOBALS['dbi']->getTablesFull($DB1, $T1);
 662     $type_T1 = mb_strtoupper($tables[$T1]['ENGINE']);
 663     $tables = $GLOBALS['dbi']->getTablesFull($DB2, $T2);
 664     $type_T2 = mb_strtoupper($tables[$T2]['ENGINE']);
 665
 666     if (PMA\libraries\Util::isForeignKeySupported($type_T1)
 667         && PMA\libraries\Util::isForeignKeySupported($type_T2)
 668         && $type_T1 == $type_T2
 669     ) {
 670         // InnoDB
 671         $existrel_foreign = PMA_getForeigners($DB2, $T2, '', 'foreign');
 672         $foreigner = PMA_searchColumnInForeigners($existrel_foreign, $F2);
 673
 674         if (isset($foreigner['constraint'])) {
 675             $upd_query = 'ALTER TABLE ' . PMA\libraries\Util::backquote($DB2)
 676                 . '.' . PMA\libraries\Util::backquote($T2) . ' DROP FOREIGN KEY '
 677                 . PMA\libraries\Util::backquote($foreigner['constraint']) . ';';
 678             if ($GLOBALS['dbi']->query($upd_query)) {
 679                 return array(true, __('FOREIGN KEY relationship has been removed.'));
 680             }
 681
 682             $error = $GLOBALS['dbi']->getError();
 683             return array(
 684                 false,
 685                 __('Error: FOREIGN KEY relationship could not be removed!')
 686                 . "<br/>" . $error
 687             );
 688         }
 689     }
 690
 691     // internal relations
 692     $delete_query = "DELETE FROM "
 693         . PMA\libraries\Util::backquote($GLOBALS['cfgRelation']['db']) . "."
 694         . $GLOBALS['cfgRelation']['relation'] . " WHERE "
 695         . "master_db = '" . $GLOBALS['dbi']->escapeString($DB2) . "'"
 696         . " AND master_table = '" . $GLOBALS['dbi']->escapeString($T2) . "'"
 697         . " AND master_field = '" . $GLOBALS['dbi']->escapeString($F2) . "'"
 698         . " AND foreign_db = '" . $GLOBALS['dbi']->escapeString($DB1) . "'"
 699         . " AND foreign_table = '" . $GLOBALS['dbi']->escapeString($T1) . "'"
 700         . " AND foreign_field = '" . $GLOBALS['dbi']->escapeString($F1) . "'";
 701
 702     $result = PMA_queryAsControlUser(
 703         $delete_query,
 704         false,
 705         PMA\libraries\DatabaseInterface::QUERY_STORE
 706     );
 707
 708     if (!$result) {
 709         $error = $GLOBALS['dbi']->getError($GLOBALS['controllink']);
 710         return array(
 711             false,
 712             __('Error: Internal relationship could not be removed!') . "<br/>" . $error
 713         );
 714     }
 715
 716     return array(true, __('Internal relationship has been removed.'));
 717 }
 718
 719 /**
 720  * Save value for a designer setting
 721  *
 722  * @param string $index setting
 723  * @param string $value value
 724  *
 725  * @return bool whether the operation succeeded
 726  */
 727 function PMA_saveDesignerSetting($index, $value)
 728 {
 729     $cfgRelation = PMA_getRelationsParam();
 730     $cfgDesigner = array(
 731         'user'  => $GLOBALS['cfg']['Server']['user'],
 732         'db'    => $cfgRelation['db'],
 733         'table' => $cfgRelation['designer_settings']
 734     );
 735
 736     $success = true;
 737     if ($GLOBALS['cfgRelation']['designersettingswork']) {
 738
 739         $orig_data_query = "SELECT settings_data"
 740             . " FROM " . PMA\libraries\Util::backquote($cfgDesigner['db'])
 741             . "." . PMA\libraries\Util::backquote($cfgDesigner['table'])
 742             . " WHERE username = '"
 743             . $GLOBALS['dbi']->escapeString($cfgDesigner['user']) . "';";
 744
 745         $orig_data = $GLOBALS['dbi']->fetchSingleRow(
 746             $orig_data_query, 'ASSOC', $GLOBALS['controllink']
 747         );
 748
 749         if (! empty($orig_data)) {
 750             $orig_data = json_decode($orig_data['settings_data'], true);
 751             $orig_data[$index] = $value;
 752             $orig_data = json_encode($orig_data);
 753
 754             $save_query = "UPDATE "
 755                 . PMA\libraries\Util::backquote($cfgDesigner['db'])
 756                 . "." . PMA\libraries\Util::backquote($cfgDesigner['table'])
 757                 . " SET settings_data = '" . $orig_data . "'"
 758                 . " WHERE username = '"
 759                 . $GLOBALS['dbi']->escapeString($cfgDesigner['user']) . "';";
 760
 761             $success = PMA_queryAsControlUser($save_query);
 762         } else {
 763             $save_data = array($index => $value);
 764
 765             $query = "INSERT INTO "
 766                 . PMA\libraries\Util::backquote($cfgDesigner['db'])
 767                 . "." . PMA\libraries\Util::backquote($cfgDesigner['table'])
 768                 . " (username, settings_data)"
 769                 . " VALUES('" . $cfgDesigner['user'] . "',"
 770                 . " '" . json_encode($save_data) . "');";
 771
 772             $success = PMA_queryAsControlUser($query);
 773         }
 774     }
 775
 776     return (bool) $success;
 777 }