libraries/relation.lib.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Set of functions used with the relation and pdf feature
   5  *
   6  * @package PhpMyAdmin
   7  */
   8 if (! defined('PHPMYADMIN')) {
   9     exit;
  10 }
  11
  12 /**
  13  * Executes a query as controluser if possible, otherwise as normal user
  14  *
  15  * @param string  $sql        the query to execute
  16  * @param boolean $show_error whether to display SQL error messages or not
  17  * @param int     $options    query options
  18  *
  19  * @return integer   the result set, or false if no result set
  20  *
  21  * @access  public
  22  *
  23  */
  24 function PMA_queryAsControlUser($sql, $show_error = true, $options = 0)
  25 {
  26     // Avoid caching of the number of rows affected; for example, this function
  27     // is called for tracking purposes but we want to display the correct number
  28     // of rows affected by the original query, not by the query generated for
  29     // tracking.
  30     $cache_affected_rows = false;
  31
  32     if ($show_error) {
  33         $result = $GLOBALS['dbi']->query(
  34             $sql,
  35             $GLOBALS['controllink'],
  36             $options,
  37             $cache_affected_rows
  38         );
  39     } else {
  40         $result = @$GLOBALS['dbi']->tryQuery(
  41             $sql,
  42             $GLOBALS['controllink'],
  43             $options,
  44             $cache_affected_rows
  45         );
  46     } // end if... else...
  47
  48     if ($result) {
  49         return $result;
  50     } else {
  51         return false;
  52     }
  53 } // end of the "PMA_queryAsControlUser()" function
  54
  55 /**
  56  * Returns current relation parameters
  57  *
  58  * @return array   $cfgRelation
  59  */
  60 function PMA_getRelationsParam()
  61 {
  62     // avoid breakage if pmadb got unconfigured after login
  63     if (! defined('TESTSUITE') && empty($GLOBALS['cfg']['Server']['pmadb'])) {
  64         unset($_SESSION['relation'][$GLOBALS['server']]);
  65     }
  66     if (empty($_SESSION['relation'][$GLOBALS['server']])) {
  67         $_SESSION['relation'][$GLOBALS['server']] = PMA_checkRelationsParam();
  68     }
  69
  70     // just for BC but needs to be before PMA_getRelationsParamDiagnostic()
  71     // which uses it
  72     $GLOBALS['cfgRelation'] = $_SESSION['relation'][$GLOBALS['server']];
  73
  74     return $_SESSION['relation'][$GLOBALS['server']];
  75 }
  76
  77 /**
  78  * prints out diagnostic info for pma relation feature
  79  *
  80  * @param array $cfgRelation Relation configuration
  81  *
  82  * @return string
  83  */
  84 function PMA_getRelationsParamDiagnostic($cfgRelation)
  85 {
  86     $retval = '';
  87
  88     $messages = array();
  89     $messages['error'] = '<font color="red"><strong>'
  90         . __('not OK')
  91         . '</strong></font>';
  92
  93     $messages['ok'] = '<font color="green"><strong>'
  94         .  _pgettext('Correctly working', 'OK')
  95         . '</strong></font>';
  96
  97     $messages['enabled']  = '<font color="green">' . __('Enabled') . '</font>';
  98     $messages['disabled'] = '<font color="red">'   . __('Disabled') . '</font>';
  99
 100     if (false === $GLOBALS['cfg']['Server']['pmadb']) {
 101         $retval .= __('Configuration of pmadb… ')
 102              . $messages['error']
 103              . PMA_Util::showDocu('setup', 'linked-tables')
 104              . '<br />' . "\n"
 105              . __('General relation features')
 106              . ' <font color="green">' . __('Disabled')
 107              . '</font>' . "\n";
 108         if (! empty($GLOBALS['db']) && $GLOBALS['cfg']['ZeroConf']) {
 109             $retval .= PMA_getHtmlFixPMATables();
 110         }
 111     } else {
 112         $retval .= '<table>' . "\n";
 113         $retval .= PMA_getDiagMessageForParameter(
 114             'pmadb',
 115             $GLOBALS['cfg']['Server']['pmadb'],
 116             $messages,
 117             'pmadb'
 118         );
 119         $retval .= PMA_getDiagMessageForParameter(
 120             'relation',
 121             isset($cfgRelation['relation']),
 122             $messages,
 123             'relation'
 124         );
 125         $retval .= PMA_getDiagMessageForFeature(
 126             __('General relation features'),
 127             'relwork',
 128             $messages
 129         );
 130         $retval .= PMA_getDiagMessageForParameter(
 131             'table_info',
 132             isset($cfgRelation['table_info']),
 133             $messages,
 134             'table_info'
 135         );
 136         $retval .= PMA_getDiagMessageForFeature(
 137             __('Display Features'),
 138             'displaywork',
 139             $messages
 140         );
 141         $retval .= PMA_getDiagMessageForParameter(
 142             'table_coords',
 143             isset($cfgRelation['table_coords']),
 144             $messages,
 145             'table_coords'
 146         );
 147         $retval .= PMA_getDiagMessageForParameter(
 148             'pdf_pages',
 149             isset($cfgRelation['pdf_pages']),
 150             $messages,
 151             'pdf_pages'
 152         );
 153         $retval .= PMA_getDiagMessageForFeature(
 154             __('Designer and creation of PDFs'),
 155             'pdfwork',
 156             $messages
 157         );
 158         $retval .= PMA_getDiagMessageForParameter(
 159             'column_info',
 160             isset($cfgRelation['column_info']),
 161             $messages,
 162             'column_info'
 163         );
 164         $retval .= PMA_getDiagMessageForFeature(
 165             __('Displaying Column Comments'),
 166             'commwork',
 167             $messages,
 168             false
 169         );
 170         $retval .= PMA_getDiagMessageForFeature(
 171             __('Browser transformation'),
 172             'mimework',
 173             $messages
 174         );
 175         if ($cfgRelation['commwork'] && ! $cfgRelation['mimework']) {
 176             $retval .= '<tr><td colspan=2 class="left error">';
 177             $retval .=  __(
 178                 'Please see the documentation on how to'
 179                 . ' update your column_info table. '
 180             );
 181             $retval .= PMA_Util::showDocu('config', 'cfg_Servers_column_info');
 182             $retval .= '</td></tr>';
 183         }
 184         $retval .= PMA_getDiagMessageForParameter(
 185             'bookmarktable',
 186             isset($cfgRelation['bookmark']),
 187             $messages,
 188             'bookmark'
 189         );
 190         $retval .= PMA_getDiagMessageForFeature(
 191             __('Bookmarked SQL query'),
 192             'bookmarkwork',
 193             $messages
 194         );
 195         $retval .= PMA_getDiagMessageForParameter(
 196             'history',
 197             isset($cfgRelation['history']),
 198             $messages,
 199             'history'
 200         );
 201         $retval .= PMA_getDiagMessageForFeature(
 202             __('SQL history'),
 203             'historywork',
 204             $messages
 205         );
 206         $retval .= PMA_getDiagMessageForParameter(
 207             'recent',
 208             isset($cfgRelation['recent']),
 209             $messages,
 210             'recent'
 211         );
 212         $retval .= PMA_getDiagMessageForFeature(
 213             __('Persistent recently used tables'),
 214             'recentwork',
 215             $messages
 216         );
 217         $retval .= PMA_getDiagMessageForParameter(
 218             'table_uiprefs',
 219             isset($cfgRelation['table_uiprefs']),
 220             $messages,
 221             'table_uiprefs'
 222         );
 223         $retval .= PMA_getDiagMessageForFeature(
 224             __('Persistent tables\' UI preferences'),
 225             'uiprefswork',
 226             $messages
 227         );
 228         $retval .= PMA_getDiagMessageForParameter(
 229             'tracking',
 230             isset($cfgRelation['tracking']),
 231             $messages,
 232             'tracking'
 233         );
 234         $retval .= PMA_getDiagMessageForFeature(
 235             __('Tracking'),
 236             'trackingwork',
 237             $messages
 238         );
 239         $retval .= PMA_getDiagMessageForParameter(
 240             'userconfig',
 241             isset($cfgRelation['userconfig']),
 242             $messages,
 243             'userconfig'
 244         );
 245         $retval .= PMA_getDiagMessageForFeature(
 246             __('User preferences'),
 247             'userconfigwork',
 248             $messages
 249         );
 250         $retval .= PMA_getDiagMessageForParameter(
 251             'users',
 252             isset($cfgRelation['users']),
 253             $messages,
 254             'users'
 255         );
 256         $retval .= PMA_getDiagMessageForParameter(
 257             'usergroups',
 258             isset($cfgRelation['usergroups']),
 259             $messages,
 260             'usergroups'
 261         );
 262         $retval .= PMA_getDiagMessageForFeature(
 263             __('Configurable menus'),
 264             'menuswork',
 265             $messages
 266         );
 267         $retval .= PMA_getDiagMessageForParameter(
 268             'navigationhiding',
 269             isset($cfgRelation['navigationhiding']),
 270             $messages,
 271             'navigationhiding'
 272         );
 273         $retval .= PMA_getDiagMessageForFeature(
 274             __('Hide/show navigation items'),
 275             'navwork',
 276             $messages
 277         );
 278         $retval .= PMA_getDiagMessageForParameter(
 279             'savedsearches',
 280             isset($cfgRelation['savedsearches']),
 281             $messages,
 282             'savedsearches'
 283         );
 284         $retval .= PMA_getDiagMessageForFeature(
 285             __('Saving Query-By-Example searches'),
 286             'savedsearcheswork',
 287             $messages
 288         );
 289         $retval .= PMA_getDiagMessageForParameter(
 290             'central_columns',
 291             isset($cfgRelation['central_columns']),
 292             $messages,
 293             'central_columns'
 294         );
 295         $retval .= PMA_getDiagMessageForFeature(
 296             __('Managing Central list of columns'),
 297             'central_columnswork',
 298             $messages
 299         );
 300         $retval .= '</table>' . "\n";
 301
 302         if (! $cfgRelation['allworks']) {
 303
 304             $retval .= '<p>' . __('Quick steps to setup advanced features:')
 305                 . '</p>';
 306             $retval .= '<ul>';
 307             $retval .= '<li>';
 308             $retval .= __(
 309                 'Create the needed tables with the '
 310                 . '<code>examples/create_tables.sql</code>.'
 311             );
 312             $retval .= ' ' . PMA_Util::showDocu('setup', 'linked-tables');
 313             $retval .= '</li>';
 314             $retval .= '<li>';
 315             $retval .= __('Create a pma user and give access to these tables.');
 316             $retval .= ' ' . PMA_Util::showDocu('config', 'cfg_Servers_controluser');
 317             $retval .= '</li>';
 318             $retval .= '<li>';
 319             $retval .= __(
 320                 'Enable advanced features in configuration file '
 321                 . '(<code>config.inc.php</code>), for example by '
 322                 . 'starting from <code>config.sample.inc.php</code>.'
 323             );
 324             $retval .= ' ' . PMA_Util::showDocu('setup', 'quick-install');
 325             $retval .= '</li>';
 326             $retval .= '<li>';
 327             $retval .= __(
 328                 'Re-login to phpMyAdmin to load the updated configuration file.'
 329             );
 330             $retval .= '</li>';
 331             $retval .= '</ul>';
 332         }
 333     }
 334
 335     return $retval;
 336 }
 337
 338 /**
 339  * prints out one diagnostic message for a feature
 340  *
 341  * @param string  $feature_name       feature name in a message string
 342  * @param string  $relation_parameter the $GLOBALS['cfgRelation'] parameter to check
 343  * @param array   $messages           utility messages
 344  * @param boolean $skip_line          whether to skip a line after the message
 345  *
 346  * @return string
 347  */
 348 function PMA_getDiagMessageForFeature($feature_name,
 349     $relation_parameter, $messages, $skip_line = true
 350 ) {
 351     $retval = '    <tr><td colspan=2 class="right">' . $feature_name . ': ';
 352     if ($GLOBALS['cfgRelation'][$relation_parameter]) {
 353         $retval .= $messages['enabled'];
 354     } else {
 355         $retval .= $messages['disabled'];
 356     }
 357     $retval .= '</td></tr>';
 358     if ($skip_line) {
 359         $retval .= '<tr><td>&nbsp;</td></tr>';
 360     }
 361     return $retval;
 362 }
 363
 364 /**
 365  * prints out one diagnostic message for a configuration parameter
 366  *
 367  * @param string  $parameter            config parameter name to display
 368  * @param boolean $relationParameterSet whether this parameter is set
 369  * @param array   $messages             utility messages
 370  * @param string  $docAnchor            anchor in documentation
 371  *
 372  * @return string
 373  */
 374 function PMA_getDiagMessageForParameter($parameter,
 375     $relationParameterSet, $messages, $docAnchor
 376 ) {
 377     $retval = '<tr><th class="left">';
 378     $retval .= '$cfg[\'Servers\'][$i][\'' . $parameter . '\']  ... ';
 379     $retval .= '</th><td class="right">';
 380     if ($relationParameterSet) {
 381         $retval .= $messages['ok'];
 382     } else {
 383         $retval .= sprintf(
 384             $messages['error'],
 385             PMA_Util::getDocuLink('config', 'cfg_Servers_' . $docAnchor)
 386         );
 387     }
 388     $retval .= '</td></tr>' . "\n";
 389     return $retval;
 390 }
 391
 392
 393 /**
 394  * Defines the relation parameters for the current user
 395  * just a copy of the functions used for relations ;-)
 396  * but added some stuff to check what will work
 397  *
 398  * @access  protected
 399  * @return array    the relation parameters for the current user
 400  */
 401 function PMA_checkRelationsParam()
 402 {
 403     $cfgRelation                   = array();
 404     $cfgRelation['relwork']        = false;
 405     $cfgRelation['displaywork']    = false;
 406     $cfgRelation['bookmarkwork']   = false;
 407     $cfgRelation['pdfwork']        = false;
 408     $cfgRelation['commwork']       = false;
 409     $cfgRelation['mimework']       = false;
 410     $cfgRelation['historywork']    = false;
 411     $cfgRelation['recentwork']     = false;
 412     $cfgRelation['uiprefswork']    = false;
 413     $cfgRelation['trackingwork']   = false;
 414     $cfgRelation['userconfigwork'] = false;
 415     $cfgRelation['menuswork']      = false;
 416     $cfgRelation['navwork']        = false;
 417     $cfgRelation['allworks']       = false;
 418     $cfgRelation['savedsearcheswork'] = false;
 419     $cfgRelation['central_columnswork'] = false;
 420     $cfgRelation['user']           = null;
 421     $cfgRelation['db']             = null;
 422
 423     if ($GLOBALS['server'] == 0
 424         || empty($GLOBALS['cfg']['Server']['pmadb'])
 425         || ! $GLOBALS['dbi']->selectDb(
 426             $GLOBALS['cfg']['Server']['pmadb'], $GLOBALS['controllink']
 427         )
 428     ) {
 429         // No server selected -> no bookmark table
 430         // we return the array with the falses in it,
 431         // to avoid some 'Unitialized string offset' errors later
 432         $GLOBALS['cfg']['Server']['pmadb'] = false;
 433         return $cfgRelation;
 434     }
 435
 436     $cfgRelation['user']  = $GLOBALS['cfg']['Server']['user'];
 437     $cfgRelation['db']    = $GLOBALS['cfg']['Server']['pmadb'];
 438
 439     //  Now I just check if all tables that i need are present so I can for
 440     //  example enable relations but not pdf...
 441     //  I was thinking of checking if they have all required columns but I
 442     //  fear it might be too slow
 443
 444     $tab_query = 'SHOW TABLES FROM '
 445         . PMA_Util::backquote(
 446             $GLOBALS['cfg']['Server']['pmadb']
 447         );
 448     $tab_rs    = PMA_queryAsControlUser(
 449         $tab_query, false, PMA_DatabaseInterface::QUERY_STORE
 450     );
 451
 452     if (! $tab_rs) {
 453         // query failed ... ?
 454         //$GLOBALS['cfg']['Server']['pmadb'] = false;
 455         return $cfgRelation;
 456     }
 457
 458     while ($curr_table = @$GLOBALS['dbi']->fetchRow($tab_rs)) {
 459         if ($curr_table[0] == $GLOBALS['cfg']['Server']['bookmarktable']) {
 460             $cfgRelation['bookmark']        = $curr_table[0];
 461         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['relation']) {
 462             $cfgRelation['relation']        = $curr_table[0];
 463         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['table_info']) {
 464             $cfgRelation['table_info']      = $curr_table[0];
 465         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['table_coords']) {
 466             $cfgRelation['table_coords']    = $curr_table[0];
 467         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['column_info']) {
 468             $cfgRelation['column_info']     = $curr_table[0];
 469         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['pdf_pages']) {
 470             $cfgRelation['pdf_pages']       = $curr_table[0];
 471         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['history']) {
 472             $cfgRelation['history']         = $curr_table[0];
 473         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['recent']) {
 474             $cfgRelation['recent']          = $curr_table[0];
 475         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['table_uiprefs']) {
 476             $cfgRelation['table_uiprefs']   = $curr_table[0];
 477         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['tracking']) {
 478             $cfgRelation['tracking']        = $curr_table[0];
 479         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['userconfig']) {
 480             $cfgRelation['userconfig']      = $curr_table[0];
 481         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['users']) {
 482             $cfgRelation['users']           = $curr_table[0];
 483         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['usergroups']) {
 484             $cfgRelation['usergroups']      = $curr_table[0];
 485         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['navigationhiding']) {
 486             $cfgRelation['navigationhiding']      = $curr_table[0];
 487         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['savedsearches']) {
 488             $cfgRelation['savedsearches']    = $curr_table[0];
 489         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['central_columns']) {
 490             $cfgRelation['central_columns']    = $curr_table[0];
 491         }
 492     } // end while
 493     $GLOBALS['dbi']->freeResult($tab_rs);
 494
 495     if (isset($cfgRelation['relation'])) {
 496         $cfgRelation['relwork']         = true;
 497         if (isset($cfgRelation['table_info'])) {
 498             $cfgRelation['displaywork'] = true;
 499         }
 500     }
 501
 502     if (isset($cfgRelation['table_coords']) && isset($cfgRelation['pdf_pages'])) {
 503         $cfgRelation['pdfwork']     = true;
 504     }
 505
 506     if (isset($cfgRelation['column_info'])) {
 507         $cfgRelation['commwork']    = true;
 508         // phpMyAdmin 4.3+
 509         // Check for input transformations upgrade.
 510         $cfgRelation['mimework'] = PMA_tryUpgradeTransformations();
 511     }
 512
 513     if (isset($cfgRelation['history'])) {
 514         $cfgRelation['historywork']     = true;
 515     }
 516
 517     if (isset($cfgRelation['recent'])) {
 518         $cfgRelation['recentwork']      = true;
 519     }
 520
 521     if (isset($cfgRelation['table_uiprefs'])) {
 522         $cfgRelation['uiprefswork']     = true;
 523     }
 524
 525     if (isset($cfgRelation['tracking'])) {
 526         $cfgRelation['trackingwork']     = true;
 527     }
 528
 529     if (isset($cfgRelation['userconfig'])) {
 530         $cfgRelation['userconfigwork']   = true;
 531     }
 532
 533     if (isset($cfgRelation['bookmark'])) {
 534         $cfgRelation['bookmarkwork']     = true;
 535     }
 536
 537     if (isset($cfgRelation['users']) && isset($cfgRelation['usergroups'])) {
 538         $cfgRelation['menuswork']        = true;
 539     }
 540
 541     if (isset($cfgRelation['navigationhiding'])) {
 542         $cfgRelation['navwork']          = true;
 543     }
 544
 545     if (isset($cfgRelation['savedsearches'])) {
 546         $cfgRelation['savedsearcheswork']      = true;
 547     }
 548
 549     if (isset($cfgRelation['central_columns'])) {
 550         $cfgRelation['central_columnswork']      = true;
 551     }
 552
 553     if ($cfgRelation['relwork'] && $cfgRelation['displaywork']
 554         && $cfgRelation['pdfwork'] && $cfgRelation['commwork']
 555         && $cfgRelation['mimework'] && $cfgRelation['historywork']
 556         && $cfgRelation['recentwork'] && $cfgRelation['uiprefswork']
 557         && $cfgRelation['trackingwork'] && $cfgRelation['userconfigwork']
 558         && $cfgRelation['bookmarkwork'] && $cfgRelation['central_columnswork']
 559         && $cfgRelation['menuswork'] && $cfgRelation['navwork']
 560         && $cfgRelation['savedsearcheswork']
 561     ) {
 562         $cfgRelation['allworks'] = true;
 563     }
 564
 565     return $cfgRelation;
 566 } // end of the 'PMA_getRelationsParam()' function
 567
 568 /**
 569  * Check whether column_info table input transformation
 570  * upgrade is required and try to upgrade silently
 571  *
 572  * @return bool false if upgrade failed
 573  *
 574  * @access  public
 575  */
 576 function PMA_tryUpgradeTransformations()
 577 {
 578     // From 4.3, new input oriented transformation feature was introduced.
 579     // Check whether column_info table has input transformation columns
 580     $new_cols = array(
 581         "input_transformation",
 582         "input_transformation_options"
 583     );
 584     $query = 'SHOW COLUMNS FROM '
 585         . PMA_Util::backquote($GLOBALS['cfg']['Server']['pmadb'])
 586         . '.' . PMA_Util::backquote($GLOBALS['cfg']['Server']['column_info'])
 587         . ' WHERE Field IN (\'' . implode('\', \'', $new_cols) . '\')';
 588     $result = PMA_queryAsControlUser(
 589         $query, false, PMA_DatabaseInterface::QUERY_STORE
 590     );
 591     if ($result) {
 592         $rows = $GLOBALS['dbi']->numRows($result);
 593         $GLOBALS['dbi']->freeResult($result);
 594         // input transformations are present
 595         // no need to upgrade
 596         if ($rows === 2) {
 597             return true;
 598             // try silent upgrade without disturbing the user
 599         } else {
 600             // read upgrade query file
 601             $query = @file_get_contents('examples/upgrade_column_info_4_3_0+.sql');
 602             // replace database name from query to with set in config.inc.php
 603             $query = str_replace(
 604                 '`phpmyadmin`',
 605                 PMA_Util::backquote($GLOBALS['cfg']['Server']['pmadb']),
 606                 $query
 607             );
 608             // replace pma__column_info table name from query
 609             // to with set in config.inc.php
 610             $query = str_replace(
 611                 '`pma__column_info`',
 612                 PMA_Util::backquote($GLOBALS['cfg']['Server']['column_info']),
 613                 $query
 614             );
 615             $GLOBALS['dbi']->tryMultiQuery($query, $GLOBALS['controllink']);
 616             // skips result sets of query as we are not interested in it
 617             while ($GLOBALS['dbi']->moreResults($GLOBALS['controllink'])
 618                 && $GLOBALS['dbi']->nextResult($GLOBALS['controllink'])
 619             ) {
 620             }
 621             $error = $GLOBALS['dbi']->getError($GLOBALS['controllink']);
 622             // return true if no error exists otherwise false
 623             return empty($error);
 624         }
 625     }
 626     // some failure, either in upgrading or something else
 627     // make some noise, time to wake up user.
 628     return false;
 629 }
 630
 631 /**
 632  * Gets all Relations to foreign tables for a given table or
 633  * optionally a given column in a table
 634  *
 635  * @param string $db     the name of the db to check for
 636  * @param string $table  the name of the table to check for
 637  * @param string $column the name of the column to check for
 638  * @param string $source the source for foreign key information
 639  *
 640  * @return array    db,table,column
 641  *
 642  * @access  public
 643  */
 644 function PMA_getForeigners($db, $table, $column = '', $source = 'both')
 645 {
 646     $cfgRelation = PMA_getRelationsParam();
 647     $foreign = array();
 648
 649     if ($cfgRelation['relwork'] && ($source == 'both' || $source == 'internal')) {
 650         $rel_query = '
 651              SELECT `master_field`,
 652                     `foreign_db`,
 653                     `foreign_table`,
 654                     `foreign_field`
 655                FROM ' . PMA_Util::backquote($cfgRelation['db'])
 656                 . '.' . PMA_Util::backquote($cfgRelation['relation']) . '
 657               WHERE `master_db`    = \'' . PMA_Util::sqlAddSlashes($db) . '\'
 658                 AND `master_table` = \'' . PMA_Util::sqlAddSlashes($table) . '\' ';
 659         if (strlen($column)) {
 660             $rel_query .= ' AND `master_field` = '
 661                 . '\'' . PMA_Util::sqlAddSlashes($column) . '\'';
 662         }
 663         $foreign = $GLOBALS['dbi']->fetchResult(
 664             $rel_query, 'master_field', null, $GLOBALS['controllink']
 665         );
 666     }
 667
 668     if (($source == 'both' || $source == 'foreign') && strlen($table)) {
 669
 670         $showCreateTableQuery = 'SHOW CREATE TABLE '
 671             . PMA_Util::backquote($db) . '.' . PMA_Util::backquote($table);
 672         $show_create_table = $GLOBALS['dbi']->fetchValue(
 673             $showCreateTableQuery, 0, 1
 674         );
 675         $analyzed_sql = PMA_SQP_analyze(PMA_SQP_parse($show_create_table));
 676
 677         $foreign['foreign_keys_data'] = $analyzed_sql[0]['foreign_keys'];
 678     }
 679
 680     /**
 681      * Emulating relations for some information_schema and data_dictionary tables
 682      */
 683     $isInformationSchema = strtolower($db) == 'information_schema';
 684     $is_data_dictionary = PMA_DRIZZLE && strtolower($db) == 'data_dictionary';
 685     if (($isInformationSchema || $is_data_dictionary)
 686         && ($source == 'internal' || $source == 'both')
 687     ) {
 688         if ($isInformationSchema) {
 689             $relations_key = 'information_schema_relations';
 690             include_once './libraries/information_schema_relations.lib.php';
 691         } else {
 692             $relations_key = 'data_dictionary_relations';
 693             include_once './libraries/data_dictionary_relations.lib.php';
 694         }
 695         if (isset($GLOBALS[$relations_key][$table])) {
 696             foreach ($GLOBALS[$relations_key][$table] as $field => $relations) {
 697                 if ((! strlen($column) || $column == $field)
 698                     && (! isset($foreign[$field]) || ! strlen($foreign[$field]))
 699                 ) {
 700                     $foreign[$field] = $relations;
 701                 }
 702             }
 703         }
 704     }
 705
 706     return $foreign;
 707 } // end of the 'PMA_getForeigners()' function
 708
 709 /**
 710  * Gets the display field of a table
 711  *
 712  * @param string $db    the name of the db to check for
 713  * @param string $table the name of the table to check for
 714  *
 715  * @return string   field name
 716  *
 717  * @access  public
 718  */
 719 function PMA_getDisplayField($db, $table)
 720 {
 721     $cfgRelation = PMA_getRelationsParam();
 722
 723     /**
 724      * Try to fetch the display field from DB.
 725      */
 726     if ($cfgRelation['displaywork']) {
 727         $disp_query = '
 728              SELECT `display_field`
 729                FROM ' . PMA_Util::backquote($cfgRelation['db'])
 730                 . '.' . PMA_Util::backquote($cfgRelation['table_info']) . '
 731               WHERE `db_name`    = \'' . PMA_Util::sqlAddSlashes($db) . '\'
 732                 AND `table_name` = \'' . PMA_Util::sqlAddSlashes($table) . '\'';
 733
 734         $row = $GLOBALS['dbi']->fetchSingleRow(
 735             $disp_query, 'ASSOC', $GLOBALS['controllink']
 736         );
 737         if (isset($row['display_field'])) {
 738             return $row['display_field'];
 739         }
 740     }
 741
 742     /**
 743      * Emulating the display field for some information_schema tables.
 744      */
 745     if ($db == 'information_schema') {
 746         switch ($table) {
 747         case 'CHARACTER_SETS':
 748             return 'DESCRIPTION';
 749         case 'TABLES':
 750             return 'TABLE_COMMENT';
 751         }
 752     }
 753
 754     /**
 755      * No Luck...
 756      */
 757     return false;
 758
 759 } // end of the 'PMA_getDisplayField()' function
 760
 761 /**
 762  * Gets the comments for all columns of a table or the db itself
 763  *
 764  * @param string $db    the name of the db to check for
 765  * @param string $table the name of the table to check for
 766  *
 767  * @return array    [column_name] = comment
 768  *
 769  * @access  public
 770  */
 771 function PMA_getComments($db, $table = '')
 772 {
 773     $comments = array();
 774
 775     if ($table != '') {
 776         // MySQL native column comments
 777         $columns = $GLOBALS['dbi']->getColumns($db, $table, null, true);
 778         if ($columns) {
 779             foreach ($columns as $column) {
 780                 if (! empty($column['Comment'])) {
 781                     $comments[$column['Field']] = $column['Comment'];
 782                 }
 783             }
 784         }
 785     } else {
 786         $comments[] = PMA_getDbComment($db);
 787     }
 788
 789     return $comments;
 790 } // end of the 'PMA_getComments()' function
 791
 792 /**
 793  * Gets the comment for a db
 794  *
 795  * @param string $db the name of the db to check for
 796  *
 797  * @return string   comment
 798  *
 799  * @access  public
 800  */
 801 function PMA_getDbComment($db)
 802 {
 803     $cfgRelation = PMA_getRelationsParam();
 804     $comment = '';
 805
 806     if ($cfgRelation['commwork']) {
 807         // pmadb internal db comment
 808         $com_qry = "
 809              SELECT `comment`
 810                FROM " . PMA_Util::backquote($cfgRelation['db'])
 811                 . "." . PMA_Util::backquote($cfgRelation['column_info']) . "
 812               WHERE db_name     = '" . PMA_Util::sqlAddSlashes($db) . "'
 813                 AND table_name  = ''
 814                 AND column_name = '(db_comment)'";
 815         $com_rs = PMA_queryAsControlUser(
 816             $com_qry, true, PMA_DatabaseInterface::QUERY_STORE
 817         );
 818
 819         if ($com_rs && $GLOBALS['dbi']->numRows($com_rs) > 0) {
 820             $row = $GLOBALS['dbi']->fetchAssoc($com_rs);
 821             $comment = $row['comment'];
 822         }
 823         $GLOBALS['dbi']->freeResult($com_rs);
 824     }
 825
 826     return $comment;
 827 } // end of the 'PMA_getDbComment()' function
 828
 829 /**
 830  * Gets the comment for a db
 831  *
 832  * @access  public
 833  *
 834  * @return string   comment
 835  */
 836 function PMA_getDbComments()
 837 {
 838     $cfgRelation = PMA_getRelationsParam();
 839     $comments = array();
 840
 841     if ($cfgRelation['commwork']) {
 842         // pmadb internal db comment
 843         $com_qry = "
 844              SELECT `db_name`, `comment`
 845                FROM " . PMA_Util::backquote($cfgRelation['db'])
 846                 . "." . PMA_Util::backquote($cfgRelation['column_info']) . "
 847               WHERE `column_name` = '(db_comment)'";
 848         $com_rs = PMA_queryAsControlUser(
 849             $com_qry, true, PMA_DatabaseInterface::QUERY_STORE
 850         );
 851
 852         if ($com_rs && $GLOBALS['dbi']->numRows($com_rs) > 0) {
 853             while ($row = $GLOBALS['dbi']->fetchAssoc($com_rs)) {
 854                 $comments[$row['db_name']] = $row['comment'];
 855             }
 856         }
 857         $GLOBALS['dbi']->freeResult($com_rs);
 858     }
 859
 860     return $comments;
 861 } // end of the 'PMA_getDbComments()' function
 862
 863 /**
 864  * Set a database comment to a certain value.
 865  *
 866  * @param string $db      the name of the db
 867  * @param string $comment the value of the column
 868  *
 869  * @return boolean  true, if comment-query was made.
 870  *
 871  * @access  public
 872  */
 873 function PMA_setDbComment($db, $comment = '')
 874 {
 875     $cfgRelation = PMA_getRelationsParam();
 876
 877     if (! $cfgRelation['commwork']) {
 878         return false;
 879     }
 880
 881     if (strlen($comment)) {
 882         $upd_query = 'INSERT INTO '
 883             . PMA_Util::backquote($cfgRelation['db']) . '.'
 884             . PMA_Util::backquote($cfgRelation['column_info'])
 885             . ' (`db_name`, `table_name`, `column_name`, `comment`)'
 886             . ' VALUES (\''
 887             . PMA_Util::sqlAddSlashes($db)
 888             . "', '', '(db_comment)', '"
 889             . PMA_Util::sqlAddSlashes($comment)
 890             . "') "
 891             . ' ON DUPLICATE KEY UPDATE '
 892             . "`comment` = '" . PMA_Util::sqlAddSlashes($comment) . "'";
 893     } else {
 894         $upd_query = 'DELETE FROM '
 895             . PMA_Util::backquote($cfgRelation['db']) . '.'
 896             . PMA_Util::backquote($cfgRelation['column_info'])
 897             . ' WHERE `db_name`     = \'' . PMA_Util::sqlAddSlashes($db) . '\'
 898                 AND `table_name`  = \'\'
 899                 AND `column_name` = \'(db_comment)\'';
 900     }
 901
 902     if (isset($upd_query)) {
 903         return PMA_queryAsControlUser($upd_query);
 904     }
 905
 906     return false;
 907 } // end of 'PMA_setDbComment()' function
 908
 909 /**
 910  * Set a SQL history entry
 911  *
 912  * @param string $db       the name of the db
 913  * @param string $table    the name of the table
 914  * @param string $username the username
 915  * @param string $sqlquery the sql query
 916  *
 917  * @return void
 918  *
 919  * @access  public
 920  */
 921 function PMA_setHistory($db, $table, $username, $sqlquery)
 922 {
 923     // Prevent to run this automatically on Footer class destroying in testsuite
 924     if (defined('TESTSUITE')
 925         || strlen($sqlquery) > $GLOBALS['cfg']['MaxCharactersInDisplayedSQL']
 926     ) {
 927         return;
 928     }
 929
 930     $cfgRelation = PMA_getRelationsParam();
 931
 932     if (! isset($_SESSION['sql_history'])) {
 933         $_SESSION['sql_history'] = array();
 934     }
 935
 936     $_SESSION['sql_history'][] = array(
 937         'db' => $db,
 938         'table' => $table,
 939         'sqlquery' => $sqlquery,
 940     );
 941
 942     if (count($_SESSION['sql_history']) > $GLOBALS['cfg']['QueryHistoryMax']) {
 943         // history should not exceed a maximum count
 944         array_shift($_SESSION['sql_history']);
 945     }
 946
 947     if (! $cfgRelation['historywork'] || ! $GLOBALS['cfg']['QueryHistoryDB']) {
 948         return;
 949     }
 950
 951     PMA_queryAsControlUser(
 952         'INSERT INTO '
 953         . PMA_Util::backquote($cfgRelation['db']) . '.'
 954         . PMA_Util::backquote($cfgRelation['history']) . '
 955               (`username`,
 956                 `db`,
 957                 `table`,
 958                 `timevalue`,
 959                 `sqlquery`)
 960         VALUES
 961               (\'' . PMA_Util::sqlAddSlashes($username) . '\',
 962                \'' . PMA_Util::sqlAddSlashes($db) . '\',
 963                \'' . PMA_Util::sqlAddSlashes($table) . '\',
 964                NOW(),
 965                \'' . PMA_Util::sqlAddSlashes($sqlquery) . '\')'
 966     );
 967 } // end of 'PMA_setHistory()' function
 968
 969 /**
 970  * Gets a SQL history entry
 971  *
 972  * @param string $username the username
 973  *
 974  * @return array    list of history items
 975  *
 976  * @access  public
 977  */
 978 function PMA_getHistory($username)
 979 {
 980     $cfgRelation = PMA_getRelationsParam();
 981
 982     if (! $cfgRelation['historywork']) {
 983         return false;
 984     }
 985
 986     /**
 987      * if db-based history is disabled but there exists a session-based
 988      * history, use it
 989      */
 990     if (! $GLOBALS['cfg']['QueryHistoryDB']) {
 991         if (isset($_SESSION['sql_history'])) {
 992             return array_reverse($_SESSION['sql_history']);
 993         }
 994         return false;
 995     }
 996
 997     $hist_query = '
 998          SELECT `db`,
 999                 `table`,
1000                 `sqlquery`,
1001                 `timevalue`
1002            FROM ' . PMA_Util::backquote($cfgRelation['db'])
1003             . '.' . PMA_Util::backquote($cfgRelation['history']) . '
1004           WHERE `username` = \'' . PMA_Util::sqlAddSlashes($username) . '\'
1005        ORDER BY `id` DESC';
1006
1007     return $GLOBALS['dbi']->fetchResult(
1008         $hist_query, null, null, $GLOBALS['controllink']
1009     );
1010 } // end of 'PMA_getHistory()' function
1011
1012 /**
1013  * purges SQL history
1014  *
1015  * deletes entries that exceeds $cfg['QueryHistoryMax'], oldest first, for the
1016  * given user
1017  *
1018  * @param string $username the username
1019  *
1020  * @return void
1021  *
1022  * @access  public
1023  */
1024 function PMA_purgeHistory($username)
1025 {
1026     $cfgRelation = PMA_getRelationsParam();
1027     if (! $GLOBALS['cfg']['QueryHistoryDB'] || ! $cfgRelation['historywork']) {
1028         return;
1029     }
1030
1031     if (! $cfgRelation['historywork']) {
1032         return;
1033     }
1034
1035     $search_query = '
1036          SELECT `timevalue`
1037            FROM ' . PMA_Util::backquote($cfgRelation['db'])
1038             . '.' . PMA_Util::backquote($cfgRelation['history']) . '
1039           WHERE `username` = \'' . PMA_Util::sqlAddSlashes($username) . '\'
1040        ORDER BY `timevalue` DESC
1041           LIMIT ' . $GLOBALS['cfg']['QueryHistoryMax'] . ', 1';
1042
1043     if ($max_time = $GLOBALS['dbi']->fetchValue(
1044         $search_query, 0, 0, $GLOBALS['controllink']
1045     )) {
1046         PMA_queryAsControlUser(
1047             'DELETE FROM '
1048             . PMA_Util::backquote($cfgRelation['db']) . '.'
1049             . PMA_Util::backquote($cfgRelation['history']) . '
1050               WHERE `username` = \'' . PMA_Util::sqlAddSlashes($username) . '\'
1051                 AND `timevalue` <= \'' . $max_time . '\''
1052         );
1053     }
1054 } // end of 'PMA_purgeHistory()' function
1055
1056 /**
1057  * Prepares the dropdown for one mode
1058  *
1059  * @param array  $foreign the keys and values for foreigns
1060  * @param string $data    the current data of the dropdown
1061  * @param string $mode    the needed mode
1062  *
1063  * @return array   the <option value=""><option>s
1064  *
1065  * @access  protected
1066  */
1067 function PMA_buildForeignDropdown($foreign, $data, $mode)
1068 {
1069     $reloptions = array();
1070
1071     // id-only is a special mode used when no foreign display column
1072     // is available
1073     if ($mode == 'id-content' || $mode == 'id-only') {
1074         // sort for id-content
1075         if ($GLOBALS['cfg']['NaturalOrder']) {
1076             uksort($foreign, 'strnatcasecmp');
1077         } else {
1078             ksort($foreign);
1079         }
1080     } elseif ($mode == 'content-id') {
1081         // sort for content-id
1082         if ($GLOBALS['cfg']['NaturalOrder']) {
1083             natcasesort($foreign);
1084         } else {
1085             asort($foreign);
1086         }
1087     }
1088
1089     foreach ($foreign as $key => $value) {
1090         if ($GLOBALS['PMA_String']->strlen($value) <= $GLOBALS['cfg']['LimitChars']
1091         ) {
1092             $vtitle = '';
1093             $value  = htmlspecialchars($value);
1094         } else {
1095             $vtitle  = htmlspecialchars($value);
1096             $value  = htmlspecialchars(
1097                 substr($value, 0, $GLOBALS['cfg']['LimitChars']) . '...'
1098             );
1099         }
1100
1101         $reloption = '<option value="' . htmlspecialchars($key) . '"';
1102         if ($vtitle != '') {
1103             $reloption .= ' title="' . $vtitle . '"';
1104         }
1105
1106         if ((string) $key == (string) $data) {
1107             $reloption .= ' selected="selected"';
1108         }
1109
1110         if ($mode == 'content-id') {
1111             $reloptions[] = $reloption . '>'
1112                 . $value . '&nbsp;-&nbsp;' . htmlspecialchars($key) .  '</option>';
1113         } elseif ($mode == 'id-content') {
1114             $reloptions[] = $reloption . '>'
1115                 . htmlspecialchars($key) .  '&nbsp;-&nbsp;' . $value . '</option>';
1116         } elseif ($mode == 'id-only') {
1117             $reloptions[] = $reloption . '>'
1118                 . htmlspecialchars($key) . '</option>';
1119         }
1120     } // end foreach
1121
1122     return $reloptions;
1123 } // end of 'PMA_buildForeignDropdown' function
1124
1125 /**
1126  * Outputs dropdown with values of foreign fields
1127  *
1128  * @param array  $disp_row        array of the displayed row
1129  * @param string $foreign_field   the foreign field
1130  * @param string $foreign_display the foreign field to display
1131  * @param string $data            the current data of the dropdown (field in row)
1132  * @param int    $max             maximum number of items in the dropdown
1133  *
1134  * @return string   the <option value=""><option>s
1135  *
1136  * @access  public
1137  */
1138 function PMA_foreignDropdown($disp_row, $foreign_field, $foreign_display, $data,
1139     $max = null
1140 ) {
1141     if (null === $max) {
1142         $max = $GLOBALS['cfg']['ForeignKeyMaxLimit'];
1143     }
1144
1145     $foreign = array();
1146
1147     // collect the data
1148     foreach ($disp_row as $relrow) {
1149         $key   = $relrow[$foreign_field];
1150
1151         // if the display field has been defined for this foreign table
1152         if ($foreign_display) {
1153             $value  = $relrow[$foreign_display];
1154         } else {
1155             $value = '';
1156         } // end if ($foreign_display)
1157
1158         $foreign[$key] = $value;
1159     } // end foreach
1160
1161     // put the dropdown sections in correct order
1162     $top = array();
1163     $bottom = array();
1164     if ($foreign_display) {
1165         if (PMA_isValid($GLOBALS['cfg']['ForeignKeyDropdownOrder'], 'array')) {
1166             if (PMA_isValid($GLOBALS['cfg']['ForeignKeyDropdownOrder'][0])) {
1167                 $top = PMA_buildForeignDropdown(
1168                     $foreign,
1169                     $data,
1170                     $GLOBALS['cfg']['ForeignKeyDropdownOrder'][0]
1171                 );
1172             }
1173             if (PMA_isValid($GLOBALS['cfg']['ForeignKeyDropdownOrder'][1])) {
1174                 $bottom = PMA_buildForeignDropdown(
1175                     $foreign,
1176                     $data,
1177                     $GLOBALS['cfg']['ForeignKeyDropdownOrder'][1]
1178                 );
1179             }
1180         } else {
1181             $top = PMA_buildForeignDropdown($foreign, $data, 'id-content');
1182             $bottom = PMA_buildForeignDropdown($foreign, $data, 'content-id');
1183         }
1184     } else {
1185         $top = PMA_buildForeignDropdown($foreign, $data, 'id-only');
1186     }
1187
1188     // beginning of dropdown
1189     $ret = '<option value="">&nbsp;</option>';
1190     $top_count = count($top);
1191     if ($max == -1 || $top_count < $max) {
1192         $ret .= implode('', $top);
1193         if ($foreign_display && $top_count > 0) {
1194             // this empty option is to visually mark the beginning of the
1195             // second series of values (bottom)
1196             $ret .= '<option value="">&nbsp;</option>';
1197         }
1198     }
1199     if ($foreign_display) {
1200         $ret .= implode('', $bottom);
1201     }
1202
1203     return $ret;
1204 } // end of 'PMA_foreignDropdown()' function
1205
1206 /**
1207  * Gets foreign keys in preparation for a drop-down selector
1208  *
1209  * @param array  $foreigners     array of the foreign keys
1210  * @param string $field          the foreign field name
1211  * @param bool   $override_total whether to override the total
1212  * @param string $foreign_filter a possible filter
1213  * @param string $foreign_limit  a possible LIMIT clause
1214  *
1215  * @return array    data about the foreign keys
1216  *
1217  * @access  public
1218  */
1219
1220 function PMA_getForeignData(
1221     $foreigners, $field, $override_total, $foreign_filter, $foreign_limit
1222 ) {
1223     // we always show the foreign field in the drop-down; if a display
1224     // field is defined, we show it besides the foreign field
1225     $foreign_link = false;
1226     do {
1227         if (! $foreigners) {
1228             break;
1229         }
1230         $foreigner = PMA_searchColumnInForeigners($foreigners, $field);
1231         if ($foreigner != false) {
1232             $foreign_db      = $foreigner['foreign_db'];
1233             $foreign_table   = $foreigner['foreign_table'];
1234             $foreign_field   = $foreigner['foreign_field'];
1235         } else {
1236             break;
1237         }
1238
1239         // Count number of rows in the foreign table. Currently we do
1240         // not use a drop-down if more than ForeignKeyMaxLimit rows in the
1241         // foreign table,
1242         // for speed reasons and because we need a better interface for this.
1243         //
1244         // We could also do the SELECT anyway, with a LIMIT, and ensure that
1245         // the current value of the field is one of the choices.
1246
1247         $the_total   = PMA_Table::countRecords($foreign_db, $foreign_table, true);
1248
1249         if ($override_total == true
1250             || $the_total < $GLOBALS['cfg']['ForeignKeyMaxLimit']
1251         ) {
1252             // foreign_display can be false if no display field defined:
1253             $foreign_display = PMA_getDisplayField($foreign_db, $foreign_table);
1254
1255             $f_query_main = 'SELECT ' . PMA_Util::backquote($foreign_field)
1256                 . (
1257                     ($foreign_display == false)
1258                         ? ''
1259                         : ', ' . PMA_Util::backquote($foreign_display)
1260                 );
1261             $f_query_from = ' FROM ' . PMA_Util::backquote($foreign_db)
1262                 . '.' . PMA_Util::backquote($foreign_table);
1263             $f_query_filter = empty($foreign_filter) ? '' : ' WHERE '
1264                 . PMA_Util::backquote($foreign_field)
1265                 . ' LIKE "%' . PMA_Util::sqlAddSlashes($foreign_filter, true) . '%"'
1266                 . (
1267                     ($foreign_display == false)
1268                     ? ''
1269                     : ' OR ' . PMA_Util::backquote($foreign_display)
1270                         . ' LIKE "%' . PMA_Util::sqlAddSlashes($foreign_filter, true)
1271                         . '%"'
1272                 );
1273             $f_query_order = ($foreign_display == false) ? '' :' ORDER BY '
1274                 . PMA_Util::backquote($foreign_table) . '.'
1275                 . PMA_Util::backquote($foreign_display);
1276             $f_query_limit = isset($foreign_limit) ? $foreign_limit : '';
1277
1278             if (!empty($foreign_filter)) {
1279                 $the_total = $GLOBALS['dbi']->fetchValue(
1280                     'SELECT COUNT(*)' . $f_query_from . $f_query_filter
1281                 );
1282                 if ($the_total === false) {
1283                     $the_total = 0;
1284                 }
1285             }
1286
1287             $disp  = $GLOBALS['dbi']->query(
1288                 $f_query_main . $f_query_from . $f_query_filter
1289                 . $f_query_order . $f_query_limit
1290             );
1291             if ($disp && $GLOBALS['dbi']->numRows($disp) > 0) {
1292                 // If a resultset has been created, pre-cache it in the $disp_row
1293                 // array. This helps us from not needing to use mysql_data_seek by
1294                 // accessing a pre-cached PHP array. Usually those resultsets are
1295                 // not that big, so a performance hit should not be expected.
1296                 $disp_row = array();
1297                 while ($single_disp_row = @$GLOBALS['dbi']->fetchAssoc($disp)) {
1298                     $disp_row[] = $single_disp_row;
1299                 }
1300                 @$GLOBALS['dbi']->freeResult($disp);
1301             }
1302         } else {
1303             $disp_row = null;
1304             $foreign_link = true;
1305         }
1306     } while (false);
1307
1308     $foreignData = array();
1309     $foreignData['foreign_link'] = $foreign_link;
1310     $foreignData['the_total'] = isset($the_total) ? $the_total : null;
1311     $foreignData['foreign_display'] = (
1312         isset($foreign_display) ? $foreign_display : null
1313     );
1314     $foreignData['disp_row'] = isset($disp_row) ? $disp_row : null;
1315     $foreignData['foreign_field'] = isset($foreign_field) ? $foreign_field : null;
1316     return $foreignData;
1317 } // end of 'PMA_getForeignData()' function
1318
1319 /**
1320  * Finds all related tables
1321  *
1322  * @param array  $all_tables All the involved tables
1323  * @param string $master     The master table to form the LEFT JOIN clause
1324  *
1325  * @return string LEFT JOIN
1326  * @access  private
1327  */
1328 function PMA_getRelatives($all_tables, $master)
1329 {
1330     $fromclause = '';
1331     $left_joins = array();
1332     $emerg = '';
1333     $ignore_internal_relations = false;
1334
1335     // The list of tables that we still couldn't connect
1336     $remaining_tables = $all_tables;
1337     unset($remaining_tables[$master]);
1338     // The list of allready connected tables
1339     $known_tables = array();
1340     $known_tables[$master] = $master;
1341     $run = 0;
1342     while ($GLOBALS['cfgRelation']['relwork'] && count($remaining_tables) > 0) {
1343         // Whether to go from master to foreign or vice versa
1344         if ($run % 2 == 0) {
1345             $from = 'master';
1346             $to    = 'foreign';
1347         } else {
1348             $from = 'foreign';
1349             $to    = 'master';
1350         }
1351         $in_know = '(\'' . implode('\', \'', $known_tables) . '\')';
1352         $in_left = '(\'' . implode('\', \'', $remaining_tables) . '\')';
1353         $rel_query = 'SELECT *'
1354             . '  FROM ' . PMA_Util::backquote($GLOBALS['cfgRelation']['db'])
1355             .       '.' . PMA_Util::backquote($GLOBALS['cfgRelation']['relation'])
1356             . ' WHERE ' . $from . '_db = \''
1357             . PMA_Util::sqlAddSlashes($GLOBALS['db']) . '\''
1358             . '   AND ' . $to   . '_db = \''
1359             . PMA_Util::sqlAddSlashes($GLOBALS['db']) . '\''
1360             . '   AND ' . $from . '_table IN ' . $in_know
1361             . '   AND ' . $to   . '_table IN ' . $in_left;
1362         $relations = @$GLOBALS['dbi']->query($rel_query, $GLOBALS['controllink']);
1363         while ($row = $GLOBALS['dbi']->fetchAssoc($relations)) {
1364             $found_table                = $row[$to . '_table'];
1365             if (isset($remaining_tables[$found_table])) {
1366                 $left_join_with = PMA_Util::backquote($GLOBALS['db']) . '.'
1367                         . PMA_Util::backquote($row[$to . '_table']);
1368                 $on_condition = PMA_Util::backquote($row[$from . '_table']) . '.'
1369                     . PMA_Util::backquote($row[$from . '_field']) . ' = '
1370                     . PMA_Util::backquote($row[$to . '_table']) . '.'
1371                     . PMA_Util::backquote($row[$to . '_field']);
1372
1373                 $left_joins[$left_join_with] = array(
1374                     'left_join_with' => $left_join_with,
1375                     'on_condition' => array($on_condition)
1376                 );
1377
1378                 $known_tables[$found_table] = $found_table;
1379                 unset($remaining_tables[$found_table]);
1380             }
1381         } // end while
1382         $run++;
1383         if ($run > 5) {
1384             foreach ($remaining_tables as $table) {
1385                 $emerg .= ', ' . PMA_Util::backquote($table);
1386                 unset($remaining_tables[$table]);
1387             }
1388         }
1389     } // end while
1390
1391     // Generate 'LEFT JOIN's for InnoDB foreign keys.
1392     $remaining_tables = $all_tables;
1393     foreach ($remaining_tables as $one_table) {
1394         $foreigners = PMA_getForeigners($GLOBALS['db'], $one_table, '', 'foreign');
1395         foreach ($foreigners['foreign_keys_data'] as $one_key) {
1396             if (in_array($one_key['ref_table_name'], $all_tables)
1397                 && ! isset($one_key['ref_db_name'])
1398                 && ($one_key['ref_table_name'] == $master
1399                 || $one_key['ref_table_name'] == $one_table)
1400             ) {
1401                 $left_join_with = PMA_Util::backquote($GLOBALS['db']) . '.'
1402                     . PMA_Util::backquote($one_table);
1403                 if (! isset($left_joins[$left_join_with])) {
1404                     $left_joins[$left_join_with] = array(
1405                         'left_join_with' =>$left_join_with,
1406                         'on_condition' => array()
1407                     );
1408                 }
1409
1410                 foreach ($one_key['ref_index_list'] as $key => $one_column) {
1411                     $on_condition = PMA_Util::backquote($one_key['ref_table_name'])
1412                     . '.' . PMA_Util::backquote($one_column) . ' = '
1413                     . PMA_Util::backquote($one_table) . '.'
1414                     . PMA_Util::backquote($one_key['index_list'][$key]);
1415
1416                     if (! in_array($on_condition, $left_joins[$left_join_with]['on_condition'])) {
1417                         $left_joins[$left_join_with]['on_condition'][] = $on_condition;
1418                     }
1419                 }
1420                 $ignore_internal_relations = true;
1421                 unset($remaining_tables[$one_table]);
1422             }
1423         }
1424     }
1425
1426     if ($ignore_internal_relations) {
1427         $emerg = '';
1428         $remaining_tables = array();
1429     }
1430
1431     // Build the 'FROM' clause.
1432     foreach ($left_joins as $one_join) {
1433         $fromclause .= "\n" . ' LEFT JOIN '
1434             . $one_join['left_join_with']
1435             . ' ON '
1436             . implode(' AND ', $one_join['on_condition']);
1437     }
1438
1439     $fromclause = $emerg . $fromclause;
1440     return $fromclause;
1441 } // end of the "PMA_getRelatives()" function
1442
1443 /**
1444  * Rename a field in relation tables
1445  *
1446  * usually called after a column in a table was renamed
1447  *
1448  * @param string $db       database name
1449  * @param string $table    table name
1450  * @param string $field    old field name
1451  * @param string $new_name new field name
1452  *
1453  * @return void
1454  */
1455 function PMA_REL_renameField($db, $table, $field, $new_name)
1456 {
1457     $cfgRelation = PMA_getRelationsParam();
1458
1459     if ($cfgRelation['displaywork']) {
1460         $table_query = 'UPDATE '
1461             . PMA_Util::backquote($cfgRelation['db']) . '.'
1462             . PMA_Util::backquote($cfgRelation['table_info'])
1463             . '   SET display_field = \'' . PMA_Util::sqlAddSlashes($new_name) . '\''
1464             . ' WHERE db_name       = \'' . PMA_Util::sqlAddSlashes($db) . '\''
1465             . '   AND table_name    = \'' . PMA_Util::sqlAddSlashes($table) . '\''
1466             . '   AND display_field = \'' . PMA_Util::sqlAddSlashes($field) . '\'';
1467         PMA_queryAsControlUser($table_query);
1468     }
1469
1470     if ($cfgRelation['relwork']) {
1471         $table_query = 'UPDATE '
1472             . PMA_Util::backquote($cfgRelation['db']) . '.'
1473             . PMA_Util::backquote($cfgRelation['relation'])
1474             . '   SET master_field = \'' . PMA_Util::sqlAddSlashes($new_name) . '\''
1475             . ' WHERE master_db    = \'' . PMA_Util::sqlAddSlashes($db) . '\''
1476             . '   AND master_table = \'' . PMA_Util::sqlAddSlashes($table) . '\''
1477             . '   AND master_field = \'' . PMA_Util::sqlAddSlashes($field) . '\'';
1478         PMA_queryAsControlUser($table_query);
1479
1480         $table_query = 'UPDATE '
1481             . PMA_Util::backquote($cfgRelation['db']) . '.'
1482             . PMA_Util::backquote($cfgRelation['relation'])
1483             . '   SET foreign_field = \'' . PMA_Util::sqlAddSlashes($new_name) . '\''
1484             . ' WHERE foreign_db    = \'' . PMA_Util::sqlAddSlashes($db) . '\''
1485             . '   AND foreign_table = \'' . PMA_Util::sqlAddSlashes($table) . '\''
1486             . '   AND foreign_field = \'' . PMA_Util::sqlAddSlashes($field) . '\'';
1487         PMA_queryAsControlUser($table_query);
1488
1489     } // end if relwork
1490 }
1491
1492
1493 /**
1494  * Performs SQL query used for renaming table.
1495  *
1496  * @param string $table        Relation table to use
1497  * @param string $source_db    Source database name
1498  * @param string $target_db    Target database name
1499  * @param string $source_table Source table name
1500  * @param string $target_table Target table name
1501  * @param string $db_field     Name of database field
1502  * @param string $table_field  Name of table field
1503  *
1504  * @return void
1505  */
1506 function PMA_REL_renameSingleTable($table,
1507     $source_db, $target_db,
1508     $source_table, $target_table,
1509     $db_field, $table_field
1510 ) {
1511     $query = 'UPDATE '
1512         . PMA_Util::backquote($GLOBALS['cfgRelation']['db']) . '.'
1513         . PMA_Util::backquote($GLOBALS['cfgRelation'][$table])
1514         . ' SET '
1515         . $db_field . ' = \'' . PMA_Util::sqlAddSlashes($target_db) . '\', '
1516         . $table_field . ' = \'' . PMA_Util::sqlAddSlashes($target_table) . '\''
1517         . ' WHERE '
1518         . $db_field . '  = \'' . PMA_Util::sqlAddSlashes($source_db) . '\''
1519         . ' AND '
1520         . $table_field . ' = \'' . PMA_Util::sqlAddSlashes($source_table) . '\'';
1521     PMA_queryAsControlUser($query);
1522 }
1523
1524
1525 /**
1526  * Rename a table in relation tables
1527  *
1528  * usually called after table has been moved
1529  *
1530  * @param string $source_db    Source database name
1531  * @param string $target_db    Target database name
1532  * @param string $source_table Source table name
1533  * @param string $target_table Target table name
1534  *
1535  * @return void
1536  */
1537 function PMA_REL_renameTable($source_db, $target_db, $source_table, $target_table)
1538 {
1539     // Move old entries from PMA-DBs to new table
1540     if ($GLOBALS['cfgRelation']['commwork']) {
1541         PMA_REL_renameSingleTable(
1542             'column_info',
1543             $source_db, $target_db,
1544             $source_table, $target_table,
1545             'db_name', 'table_name'
1546         );
1547     }
1548
1549     // updating bookmarks is not possible since only a single table is
1550     // moved, and not the whole DB.
1551
1552     if ($GLOBALS['cfgRelation']['displaywork']) {
1553         PMA_REL_renameSingleTable(
1554             'table_info',
1555             $source_db, $target_db,
1556             $source_table, $target_table,
1557             'db_name', 'table_name'
1558         );
1559     }
1560
1561     if ($GLOBALS['cfgRelation']['relwork']) {
1562         PMA_REL_renameSingleTable(
1563             'relation',
1564             $source_db, $target_db,
1565             $source_table, $target_table,
1566             'foreign_db', 'foreign_table'
1567         );
1568
1569         PMA_REL_renameSingleTable(
1570             'relation',
1571             $source_db, $target_db,
1572             $source_table, $target_table,
1573             'master_db', 'master_table'
1574         );
1575     }
1576
1577     /**
1578      * @todo Can't get moving PDFs the right way. The page numbers
1579      * always get screwed up independently from duplication because the
1580      * numbers do not seem to be stored on a per-database basis. Would
1581      * the author of pdf support please have a look at it?
1582      */
1583
1584     if ($GLOBALS['cfgRelation']['pdfwork']) {
1585         PMA_REL_renameSingleTable(
1586             'table_coords',
1587             $source_db, $target_db,
1588             $source_table, $target_table,
1589             'db_name', 'table_name'
1590         );
1591     }
1592 }
1593
1594 /**
1595  * Create a PDF page
1596  *
1597  * @param string $newpage     name of the new PDF page
1598  * @param array  $cfgRelation Relation configuration
1599  * @param string $db          database name
1600  *
1601  * @return string   $pdf_page_number
1602  */
1603 function PMA_REL_createPage($newpage, $cfgRelation, $db)
1604 {
1605     if (! isset($newpage) || $newpage == '') {
1606         $newpage = __('no description');
1607     }
1608     $ins_query   = 'INSERT INTO '
1609         . PMA_Util::backquote($GLOBALS['cfgRelation']['db']) . '.'
1610         . PMA_Util::backquote($cfgRelation['pdf_pages'])
1611         . ' (db_name, page_descr)'
1612         . ' VALUES (\''
1613         . PMA_Util::sqlAddSlashes($db) . '\', \''
1614         . PMA_Util::sqlAddSlashes($newpage) . '\')';
1615     PMA_queryAsControlUser($ins_query, false);
1616
1617     return $GLOBALS['dbi']->insertId(
1618         isset($GLOBALS['controllink']) ? $GLOBALS['controllink'] : ''
1619     );
1620 }
1621
1622 /**
1623  * Get child table references for a table column.
1624  *
1625  * @param string $db     name of master table db.
1626  * @param string $table  name of master table.
1627  * @param string $column name of master table column.
1628  *
1629  * @return array $child_references
1630  */
1631 function PMA_getChildReferences($db, $table, $column)
1632 {
1633     $child_references = array();
1634     $i=0;
1635     $rel_query = 'SELECT `column_name`,'
1636                 . ' `table_name`,'
1637                 . '`table_schema`'
1638                 . ' FROM `information_schema`.`key_column_usage`'
1639                 . ' WHERE `referenced_column_name` = \''
1640                 . PMA_Util::sqlAddSlashes($column) . '\''
1641                 . ' AND `referenced_table_name` = \''
1642                 . PMA_Util::sqlAddSlashes($table) . '\''
1643                 . ' AND `referenced_table_schema` = \''
1644                 . PMA_Util::sqlAddSlashes($db) . '\'';
1645
1646     $result = $GLOBALS['dbi']->tryQuery($rel_query, $GLOBALS['controllink']);
1647     if ($result == true) {
1648         while (($row = $GLOBALS['dbi']->fetchAssoc($result))) {
1649             $child_references[$i++] = $row;
1650         }
1651     }
1652     return $child_references;
1653 }
1654
1655 /**
1656  * Check child table references and foreign key for a table column.
1657  *
1658  * @param string $db     name of master table db.
1659  * @param string $table  name of master table.
1660  * @param string $column name of master table column.
1661  *
1662  * @return array $column_status telling about references if foreign key.
1663  */
1664 function PMA_checkChildForeignReferences($db, $table, $column)
1665 {
1666     $column_status = array();
1667     $column_status['isEditable'] = false;
1668     $column_status['isReferenced'] = false;
1669     $column_status['isForeignKey'] = false;
1670     $column_status['references'] = array();
1671     $foreigners = PMA_getForeigners($db, $table, $column);
1672     $foreigner = PMA_searchColumnInForeigners($foreigners, $column);
1673     $child_references = PMA_getChildReferences($db, $table, $column);
1674
1675     if (sizeof($child_references, 0) > 0
1676         || $foreigner
1677     ) {
1678         if (sizeof($child_references, 0) > 0) {
1679             $column_status['isReferenced'] = true;
1680             foreach ($child_references as $row => $columns) {
1681                 array_push(
1682                     $column_status['references'],
1683                     PMA_Util::backquote($columns['table_schema'])
1684                     . '.' . PMA_Util::backquote($columns['table_name'])
1685                 );
1686             }
1687         }
1688
1689         if ($foreigner) {
1690             $column_status['isForeignKey'] = true;
1691         }
1692     } else {
1693         $column_status['isEditable'] = true;
1694     }
1695
1696     return $column_status;
1697 }
1698
1699 /**
1700  * Search a table column in foreign data.
1701  *
1702  * @param array  $foreigners Table Foreign data
1703  * @param string $column     Column name
1704  *
1705  * @return bool|array
1706  */
1707 function PMA_searchColumnInForeigners($foreigners, $column)
1708 {
1709     if (isset($foreigners[$column])) {
1710         return $foreigners[$column];
1711     } else {
1712         $foreigner = array();
1713         foreach ($foreigners['foreign_keys_data'] as $key => $one_key) {
1714             $column_index = array_search($column, $one_key['index_list']);
1715             if ($column_index !== false) {
1716                 $foreigner['foreign_field']
1717                     = $one_key['ref_index_list'][$column_index];
1718                 $foreigner['foreign_db'] = isset($one_key['ref_db_name'])
1719                     ? $one_key['ref_db_name']
1720                     : $GLOBALS['db'];
1721                 $foreigner['foreign_table'] = $one_key['ref_table_name'];
1722                 $foreigner['constraint'] = $one_key['constraint'];
1723
1724                 return $foreigner;
1725             }
1726         }
1727     }
1728
1729     return false;
1730 }
1731
1732 /**
1733  * Searches a DB for the existence of PMA tables.
1734  *
1735  * @param string $db     Database
1736  * @param array  $tables Default table names
1737  *
1738  * @return bool
1739  */
1740 function PMA_searchPMATablesInDb($db, $tables)
1741 {
1742     $tab_rs = $GLOBALS['dbi']->getTables($db);
1743
1744     if ($tab_rs === false) {
1745         return false;
1746     }
1747
1748     foreach ($tab_rs as $curr_table) {
1749         if (in_array($curr_table, $tables)) {
1750             $tables = array_diff($tables, array($curr_table));
1751         }
1752     }
1753
1754     if (count($tables) != 0) {
1755         return false;
1756     }
1757
1758     return true;
1759 }
1760
1761 /**
1762  * Returns default PMA table names and their create queries.
1763  *
1764  * @return array table name, create query
1765  */
1766 function PMA_getDefaultPMATableNames()
1767 {
1768     $pma_tables = array();
1769     if (PMA_DRIZZLE) {
1770         $create_tables_file = file_get_contents(
1771             'examples/create_tables_drizzle.sql'
1772         );
1773     } else {
1774         $create_tables_file = file_get_contents(
1775             'examples/create_tables.sql'
1776         );
1777     }
1778
1779     $queries = explode(';', $create_tables_file);
1780
1781     foreach ($queries as $query) {
1782         if (preg_match(
1783             '/CREATE TABLE IF NOT EXISTS `(.*)` \(/',
1784             $query,
1785             $table
1786         )
1787         ) {
1788             $pma_tables[$table[1]] = $query . ';';
1789         }
1790     }
1791
1792     return $pma_tables;
1793 }
1794
1795 /**
1796  * Creates PMA tables in the given db, updates if already exists.
1797  *
1798  * @param string $db Database
1799  *
1800  * @return void
1801  */
1802 function PMA_fixPMATables($db)
1803 {
1804     $default_tables = PMA_getDefaultPMATableNames();
1805     $GLOBALS['dbi']->selectDb($db);
1806
1807     foreach ($default_tables as $table => $create_query) {
1808         $GLOBALS['dbi']->tryQuery($create_query);
1809
1810         if ($error = $GLOBALS['dbi']->getError()) {
1811             $GLOBALS['message'] = $error;
1812             break;
1813         }
1814
1815         if ($table == 'pma__bookmark') {
1816             $GLOBALS['cfg']['Server']['bookmarktable']           = $table;
1817         } elseif ($table == 'pma__relation') {
1818             $GLOBALS['cfg']['Server']['relation']           = $table;
1819         } elseif ($table == 'pma__table_info') {
1820             $GLOBALS['cfg']['Server']['table_info']         = $table;
1821         } elseif ($table == 'pma__table_coords') {
1822             $GLOBALS['cfg']['Server']['table_coords']       = $table;
1823         } elseif ($table == 'pma__column_info') {
1824             $GLOBALS['cfg']['Server']['column_info']        = $table;
1825         } elseif ($table == 'pma__pdf_pages') {
1826             $GLOBALS['cfg']['Server']['pdf_pages']          = $table;
1827         } elseif ($table == 'pma__history') {
1828             $GLOBALS['cfg']['Server']['history']            = $table;
1829         } elseif ($table == 'pma__recent') {
1830             $GLOBALS['cfg']['Server']['recent']             = $table;
1831         } elseif ($table == 'pma__table_uiprefs') {
1832             $GLOBALS['cfg']['Server']['table_uiprefs']      = $table;
1833         } elseif ($table == 'pma__tracking') {
1834             $GLOBALS['cfg']['Server']['tracking']           = $table;
1835         } elseif ($table == 'pma__userconfig') {
1836             $GLOBALS['cfg']['Server']['userconfig']         = $table;
1837         } elseif ($table == 'pma__users') {
1838             $GLOBALS['cfg']['Server']['users']              = $table;
1839         } elseif ($table == 'pma__usergroups') {
1840             $GLOBALS['cfg']['Server']['usergroups']         = $table;
1841         } elseif ($table == 'pma__navigationhiding') {
1842             $GLOBALS['cfg']['Server']['navigationhiding']   = $table;
1843         } elseif ($table == 'pma__savedsearches') {
1844             $GLOBALS['cfg']['Server']['savedsearches']      = $table;
1845         } elseif ($table == 'pma__central_columns') {
1846             $GLOBALS['cfg']['Server']['central_columns']    = $table;
1847         } else if ($table == 'pma__designer_coords') {
1848             $GLOBALS['cfg']['Server']['designer_coords']    = $table;
1849         }
1850     }
1851     $GLOBALS['cfg']['Server']['pmadb'] = $db;
1852     $_SESSION['relation'][$GLOBALS['server']] = PMA_checkRelationsParam();
1853 }
1854
1855 /**
1856  * Get Html for PMA tables fixing anchor.
1857  *
1858  * @return string Html
1859  */
1860 function PMA_getHtmlFixPMATables()
1861 {
1862     $retval = '';
1863
1864     $url_query = PMA_URL_getCommon($GLOBALS['db']);
1865     $url_query .= '&amp;goto=db_operations.php&amp;fix_pmadb=1';
1866     $message = PMA_Message::notice(
1867         __(
1868             '%sCreate%s the phpMyAdmin configuration storage in the '
1869             . 'current database.'
1870         )
1871     );
1872     $message->addParam(
1873         '<a href="' . $GLOBALS['cfg']['PmaAbsoluteUri']
1874         . 'chk_rel.php?' . $url_query . '">',
1875         false
1876     );
1877     $message->addParam('</a>', false);
1878
1879     $retval .= $message->getDisplay();
1880
1881     return $retval;
1882 }
1883 ?>