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    the query to execute
  16  * @param boolean   whether to display SQL error messages or not
  17  *
  18  * @return  integer   the result set, or false if no result set
  19  *
  20  * @access  public
  21  *
  22  */
  23 function PMA_query_as_controluser($sql, $show_error = true, $options = 0)
  24 {
  25     // Avoid caching of the number of rows affected; for example, this function
  26     // is called for tracking purposes but we want to display the correct number
  27     // of rows affected by the original query, not by the query generated for
  28     // tracking.
  29     $cache_affected_rows = false;
  30
  31     if ($show_error) {
  32         $result = PMA_DBI_query($sql, $GLOBALS['controllink'], $options, $cache_affected_rows);
  33     } else {
  34         $result = @PMA_DBI_try_query($sql, $GLOBALS['controllink'], $options, $cache_affected_rows);
  35     } // end if... else...
  36
  37     if ($result) {
  38         return $result;
  39     } else {
  40         return false;
  41     }
  42 } // end of the "PMA_query_as_controluser()" function
  43
  44 /**
  45  * @param bool    $verbose    whether to print diagnostic info
  46  * @return  array   $cfgRelation
  47  */
  48 function PMA_getRelationsParam($verbose = false)
  49 {
  50     if (empty($_SESSION['relation'][$GLOBALS['server']])) {
  51         $_SESSION['relation'][$GLOBALS['server']] = PMA__getRelationsParam();
  52     }
  53
  54     // just for BC but needs to be before PMA_printRelationsParamDiagnostic()
  55     // which uses it
  56     $GLOBALS['cfgRelation'] = $_SESSION['relation'][$GLOBALS['server']];
  57
  58     if ($verbose) {
  59         PMA_printRelationsParamDiagnostic($_SESSION['relation'][$GLOBALS['server']]);
  60     }
  61
  62     return $_SESSION['relation'][$GLOBALS['server']];
  63 }
  64
  65 /**
  66  * prints out diagnostic info for pma relation feature
  67  *
  68  * @param array   $cfgRelation
  69  */
  70 function PMA_printRelationsParamDiagnostic($cfgRelation)
  71 {
  72     $messages['error'] = '<font color="red"><strong>' . __('not OK')
  73                    . '</strong></font> [ <a href="Documentation.html#%s" target="documentation">'
  74                    . __('Documentation') . '</a> ]';
  75
  76     $messages['ok'] = '<font color="green"><strong>' . __('OK') . '</strong></font>';
  77     $messages['enabled']  = '<font color="green">' . __('Enabled') . '</font>';
  78     $messages['disabled'] = '<font color="red">'   . __('Disabled') . '</font>';
  79
  80     if (false === $GLOBALS['cfg']['Server']['pmadb']) {
  81         echo 'PMA Database ... '
  82              . sprintf($messages['error'], 'pmadb')
  83              . '<br />' . "\n"
  84              . __('General relation features')
  85              . ' <font color="green">' . __('Disabled')
  86              . '</font>' . "\n";
  87         return;
  88     }
  89
  90     echo '<table>' . "\n";
  91
  92     PMA_printDiagMessageForParameter('pmadb', $GLOBALS['cfg']['Server']['pmadb'], $messages, 'pmadb');
  93
  94     PMA_printDiagMessageForParameter('relation', isset($cfgRelation['relation']), $messages, 'relation');
  95
  96     PMA_printDiagMessageForFeature(__('General relation features'), 'relwork', $messages);
  97
  98     PMA_printDiagMessageForParameter('table_info', isset($cfgRelation['table_info']), $messages, 'table_info');
  99
 100     PMA_printDiagMessageForFeature(__('Display Features'), 'displaywork', $messages);
 101
 102     PMA_printDiagMessageForParameter('table_coords', isset($cfgRelation['table_coords']), $messages, 'table_coords');
 103
 104     PMA_printDiagMessageForParameter('pdf_pages', isset($cfgRelation['pdf_pages']), $messages, 'table_coords');
 105
 106     PMA_printDiagMessageForFeature(__('Creation of PDFs'), 'pdfwork', $messages);
 107
 108     PMA_printDiagMessageForParameter('column_info', isset($cfgRelation['column_info']), $messages, 'col_com');
 109
 110     PMA_printDiagMessageForFeature(__('Displaying Column Comments'), 'commwork', $messages, false);
 111
 112     PMA_printDiagMessageForFeature(__('Browser transformation'), 'mimework', $messages);
 113
 114     if ($cfgRelation['commwork'] && ! $cfgRelation['mimework']) {
 115         echo '<tr><td colspan=2 align="left">' . __('Please see the documentation on how to update your column_comments table') . '</td></tr>' . "\n";
 116     }
 117
 118     PMA_printDiagMessageForParameter('bookmarktable', isset($cfgRelation['bookmark']), $messages, 'bookmark');
 119
 120     PMA_printDiagMessageForFeature(__('Bookmarked SQL query'), 'bookmarkwork', $messages);
 121
 122     PMA_printDiagMessageForParameter('history', isset($cfgRelation['history']), $messages, 'history');
 123
 124     PMA_printDiagMessageForFeature(__('SQL history'), 'historywork', $messages);
 125
 126     PMA_printDiagMessageForParameter('designer_coords', isset($cfgRelation['designer_coords']), $messages, 'designer_coords');
 127
 128     PMA_printDiagMessageForFeature(__('Designer'), 'designerwork', $messages);
 129
 130     PMA_printDiagMessageForParameter('recent', isset($cfgRelation['recent']), $messages, 'recent');
 131
 132     PMA_printDiagMessageForFeature(__('Persistent recently used tables'), 'recentwork', $messages);
 133
 134     PMA_printDiagMessageForParameter('table_uiprefs', isset($cfgRelation['table_uiprefs']), $messages, 'table_uiprefs');
 135
 136     PMA_printDiagMessageForFeature(__('Persistent tables\' UI preferences'), 'uiprefswork', $messages);
 137
 138     PMA_printDiagMessageForParameter('tracking', isset($cfgRelation['tracking']), $messages, 'tracking');
 139
 140     PMA_printDiagMessageForFeature(__('Tracking'), 'trackingwork', $messages);
 141
 142     PMA_printDiagMessageForParameter('userconfig', isset($cfgRelation['userconfig']), $messages, 'userconfig');
 143
 144     PMA_printDiagMessageForFeature(__('User preferences'), 'userconfigwork', $messages);
 145
 146     echo '</table>' . "\n";
 147
 148     echo '<p>' . __('Quick steps to setup advanced features:') . '</p>';
 149     echo '<ul>';
 150     echo '<li>' . __('Create the needed tables with the <code>script/create_tables.sql</code>.') . ' ' . PMA_showDocu('linked-tables') . '</li>';
 151     echo '<li>' . __('Create a pma user and give access to these tables.') . ' ' . PMA_showDocu('pmausr') . '</li>';
 152     echo '<li>' . __('Enable advanced features in configuration file (<code>config.inc.php</code>), for example by starting from <code>config.sample.inc.php</code>.') . ' ' . PMA_showDocu('quick_install') . '</li>';
 153     echo '<li>' . __('Re-login to phpMyAdmin to load the updated configuration file.') . '</li>';
 154     echo '</ul>';
 155 }
 156
 157 /**
 158  * prints out one diagnostic message for a feature
 159  *
 160  * @param string  feature name in a message string
 161  * @param string  the $GLOBALS['cfgRelation'] parameter to check
 162  * @param array   utility messages
 163  * @param boolean whether to skip a line after the message
 164  */
 165 function PMA_printDiagMessageForFeature($feature_name, $relation_parameter, $messages, $skip_line=true)
 166 {
 167     echo '    <tr><td colspan=2 align="right">' . $feature_name . ': '
 168          . ($GLOBALS['cfgRelation'][$relation_parameter] ? $messages['enabled'] : $messages['disabled'])
 169          . '</td></tr>' . "\n";
 170     if ($skip_line) {
 171         echo '    <tr><td>&nbsp;</td></tr>' . "\n";
 172     }
 173 }
 174
 175 /**
 176  * prints out one diagnostic message for a configuration parameter
 177  *
 178  * @param string  config parameter name to display
 179  * @param boolean whether this parameter is set
 180  * @param array   utility messages
 181  * @param string  anchor in Documentation.html
 182  */
 183 function PMA_printDiagMessageForParameter($parameter, $relation_parameter_set, $messages, $doc_anchor)
 184 {
 185     echo '    <tr><th align="left">';
 186     echo '$cfg[\'Servers\'][$i][\'' . $parameter . '\']  ... </th><td align="right">';
 187     echo ($relation_parameter_set ? $messages['ok'] : sprintf($messages['error'], $doc_anchor)) . '</td></tr>' . "\n";
 188 }
 189
 190
 191 /**
 192  * Defines the relation parameters for the current user
 193  * just a copy of the functions used for relations ;-)
 194  * but added some stuff to check what will work
 195  *
 196  * @access  protected
 197  * @return  array    the relation parameters for the current user
 198  */
 199 function PMA__getRelationsParam()
 200 {
 201     $cfgRelation                = array();
 202     $cfgRelation['relwork']     = false;
 203     $cfgRelation['displaywork'] = false;
 204     $cfgRelation['bookmarkwork']= false;
 205     $cfgRelation['pdfwork']     = false;
 206     $cfgRelation['commwork']    = false;
 207     $cfgRelation['mimework']    = false;
 208     $cfgRelation['historywork'] = false;
 209     $cfgRelation['recentwork']  = false;
 210     $cfgRelation['uiprefswork'] = false;
 211     $cfgRelation['trackingwork'] = false;
 212     $cfgRelation['designerwork'] = false;
 213     $cfgRelation['userconfigwork'] = false;
 214     $cfgRelation['allworks']    = false;
 215     $cfgRelation['user']        = null;
 216     $cfgRelation['db']          = null;
 217
 218     if ($GLOBALS['server'] == 0 || empty($GLOBALS['cfg']['Server']['pmadb'])
 219      || ! PMA_DBI_select_db($GLOBALS['cfg']['Server']['pmadb'], $GLOBALS['controllink'])) {
 220         // No server selected -> no bookmark table
 221         // we return the array with the falses in it,
 222         // to avoid some 'Unitialized string offset' errors later
 223         $GLOBALS['cfg']['Server']['pmadb'] = false;
 224         return $cfgRelation;
 225     }
 226
 227
 228     $cfgRelation['user']  = $GLOBALS['cfg']['Server']['user'];
 229     $cfgRelation['db']    = $GLOBALS['cfg']['Server']['pmadb'];
 230
 231     //  Now I just check if all tables that i need are present so I can for
 232     //  example enable relations but not pdf...
 233     //  I was thinking of checking if they have all required columns but I
 234     //  fear it might be too slow
 235
 236     $tab_query = 'SHOW TABLES FROM ' . PMA_backquote($GLOBALS['cfg']['Server']['pmadb']);
 237     $tab_rs    = PMA_query_as_controluser($tab_query, false, PMA_DBI_QUERY_STORE);
 238
 239     if (! $tab_rs) {
 240         // query failed ... ?
 241         //$GLOBALS['cfg']['Server']['pmadb'] = false;
 242         return $cfgRelation;
 243     }
 244
 245     while ($curr_table = @PMA_DBI_fetch_row($tab_rs)) {
 246         if ($curr_table[0] == $GLOBALS['cfg']['Server']['bookmarktable']) {
 247             $cfgRelation['bookmark']        = $curr_table[0];
 248         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['relation']) {
 249             $cfgRelation['relation']        = $curr_table[0];
 250         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['table_info']) {
 251             $cfgRelation['table_info']      = $curr_table[0];
 252         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['table_coords']) {
 253             $cfgRelation['table_coords']    = $curr_table[0];
 254         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['designer_coords']) {
 255             $cfgRelation['designer_coords']    = $curr_table[0];
 256         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['column_info']) {
 257             $cfgRelation['column_info'] = $curr_table[0];
 258         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['pdf_pages']) {
 259             $cfgRelation['pdf_pages']       = $curr_table[0];
 260         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['history']) {
 261             $cfgRelation['history'] = $curr_table[0];
 262         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['recent']) {
 263             $cfgRelation['recent'] = $curr_table[0];
 264         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['table_uiprefs']) {
 265             $cfgRelation['table_uiprefs'] = $curr_table[0];
 266         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['tracking']) {
 267             $cfgRelation['tracking'] = $curr_table[0];
 268         } elseif ($curr_table[0] == $GLOBALS['cfg']['Server']['userconfig']) {
 269             $cfgRelation['userconfig'] = $curr_table[0];
 270         }
 271     } // end while
 272     PMA_DBI_free_result($tab_rs);
 273
 274     if (isset($cfgRelation['relation'])) {
 275         $cfgRelation['relwork']         = true;
 276         if (isset($cfgRelation['table_info'])) {
 277                 $cfgRelation['displaywork'] = true;
 278         }
 279     }
 280
 281     if (isset($cfgRelation['table_coords']) && isset($cfgRelation['pdf_pages'])) {
 282         $cfgRelation['pdfwork']     = true;
 283     }
 284
 285     if (isset($cfgRelation['column_info'])) {
 286         $cfgRelation['commwork']    = true;
 287
 288         if ($GLOBALS['cfg']['Server']['verbose_check']) {
 289             $mime_query  = PMA_DBI_get_columns_sql($cfgRelation['db'], $cfgRelation['column_info']);
 290             $mime_rs     = PMA_query_as_controluser($mime_query, false);
 291
 292             $mime_field_mimetype                = false;
 293             $mime_field_transformation          = false;
 294             $mime_field_transformation_options  = false;
 295             while ($curr_mime_field = @PMA_DBI_fetch_row($mime_rs)) {
 296                 if ($curr_mime_field[0] == 'mimetype') {
 297                     $mime_field_mimetype               = true;
 298                 } elseif ($curr_mime_field[0] == 'transformation') {
 299                     $mime_field_transformation         = true;
 300                 } elseif ($curr_mime_field[0] == 'transformation_options') {
 301                     $mime_field_transformation_options = true;
 302                 }
 303             }
 304             PMA_DBI_free_result($mime_rs);
 305
 306             if ($mime_field_mimetype
 307              && $mime_field_transformation
 308              && $mime_field_transformation_options) {
 309                 $cfgRelation['mimework'] = true;
 310             }
 311         } else {
 312             $cfgRelation['mimework'] = true;
 313         }
 314     }
 315
 316     if (isset($cfgRelation['history'])) {
 317         $cfgRelation['historywork']     = true;
 318     }
 319
 320     if (isset($cfgRelation['recent'])) {
 321         $cfgRelation['recentwork']      = true;
 322     }
 323
 324     if (isset($cfgRelation['table_uiprefs'])) {
 325         $cfgRelation['uiprefswork']     = true;
 326     }
 327
 328     if (isset($cfgRelation['tracking'])) {
 329         $cfgRelation['trackingwork']     = true;
 330     }
 331
 332     if (isset($cfgRelation['userconfig'])) {
 333         $cfgRelation['userconfigwork']   = true;
 334     }
 335
 336     // we do not absolutely need that the internal relations or the PDF
 337     // schema feature be activated
 338     if (isset($cfgRelation['designer_coords'])) {
 339         $cfgRelation['designerwork']     = true;
 340     }
 341
 342     if (isset($cfgRelation['bookmark'])) {
 343         $cfgRelation['bookmarkwork']     = true;
 344     }
 345
 346     if ($cfgRelation['relwork'] && $cfgRelation['displaywork']
 347      && $cfgRelation['pdfwork'] && $cfgRelation['commwork']
 348      && $cfgRelation['mimework'] && $cfgRelation['historywork']
 349      && $cfgRelation['recentwork'] && $cfgRelation['uiprefswork']
 350      && $cfgRelation['trackingwork'] && $cfgRelation['userconfigwork']
 351      && $cfgRelation['bookmarkwork'] && $cfgRelation['designerwork']) {
 352         $cfgRelation['allworks'] = true;
 353     }
 354
 355     return $cfgRelation;
 356 } // end of the 'PMA_getRelationsParam()' function
 357
 358 /**
 359  * Gets all Relations to foreign tables for a given table or
 360  * optionally a given column in a table
 361  *
 362  * @access  public
 363  * @param string   $db        the name of the db to check for
 364  * @param string   $table     the name of the table to check for
 365  * @param string   $column    the name of the column to check for
 366  * @param string   $source    the source for foreign key information
 367  * @return  array    db,table,column
 368  */
 369 function PMA_getForeigners($db, $table, $column = '', $source = 'both')
 370 {
 371     $cfgRelation = PMA_getRelationsParam();
 372     $foreign = array();
 373
 374     if ($cfgRelation['relwork'] && ($source == 'both' || $source == 'internal')) {
 375         $rel_query = '
 376              SELECT `master_field`,
 377                     `foreign_db`,
 378                     `foreign_table`,
 379                     `foreign_field`
 380                FROM ' . PMA_backquote($cfgRelation['db']) . '.' . PMA_backquote($cfgRelation['relation']) . '
 381               WHERE `master_db`    = \'' . PMA_sqlAddSlashes($db) . '\'
 382                 AND `master_table` = \'' . PMA_sqlAddSlashes($table) . '\' ';
 383         if (strlen($column)) {
 384             $rel_query .= ' AND `master_field` = \'' . PMA_sqlAddSlashes($column) . '\'';
 385         }
 386         $foreign = PMA_DBI_fetch_result($rel_query, 'master_field', null, $GLOBALS['controllink']);
 387     }
 388
 389     if (($source == 'both' || $source == 'foreign') && strlen($table)) {
 390         $show_create_table_query = 'SHOW CREATE TABLE '
 391             . PMA_backquote($db) . '.' . PMA_backquote($table);
 392         $show_create_table = PMA_DBI_fetch_value($show_create_table_query, 0, 1);
 393         $analyzed_sql = PMA_SQP_analyze(PMA_SQP_parse($show_create_table));
 394
 395         foreach ($analyzed_sql[0]['foreign_keys'] as $one_key) {
 396             // The analyzer may return more than one column name in the
 397             // index list or the ref_index_list; if this happens,
 398             // the current logic just discards the whole index; having
 399             // more than one index field is currently unsupported (see FAQ 3.6)
 400             if (count($one_key['index_list']) == 1) {
 401                 foreach ($one_key['index_list'] as $i => $field) {
 402                     // If a foreign key is defined in the 'internal' source (pmadb)
 403                     // and as a native foreign key, we won't get it twice
 404                     // if $source='both' because we use $field as key
 405
 406                     // The parser looks for a CONSTRAINT clause just before
 407                     // the FOREIGN KEY clause. It finds it (as output from
 408                     // SHOW CREATE TABLE) in MySQL 4.0.13, but not in older
 409                     // versions like 3.23.58.
 410                     // In those cases, the FOREIGN KEY parsing will put numbers
 411                     // like -1, 0, 1... instead of the constraint number.
 412
 413                     if (isset($one_key['constraint'])) {
 414                         $foreign[$field]['constraint'] = $one_key['constraint'];
 415                     }
 416
 417                     if (isset($one_key['ref_db_name'])) {
 418                         $foreign[$field]['foreign_db'] = $one_key['ref_db_name'];
 419                     } else {
 420                         $foreign[$field]['foreign_db'] = $db;
 421                     }
 422                     $foreign[$field]['foreign_table'] = $one_key['ref_table_name'];
 423                     $foreign[$field]['foreign_field'] = $one_key['ref_index_list'][$i];
 424                     if (isset($one_key['on_delete'])) {
 425                         $foreign[$field]['on_delete'] = $one_key['on_delete'];
 426                     }
 427                     if (isset($one_key['on_update'])) {
 428                         $foreign[$field]['on_update'] = $one_key['on_update'];
 429                     }
 430                 }
 431             }
 432         }
 433     }
 434
 435     /**
 436      * Emulating relations for some information_schema and data_dictionary tables
 437      */
 438     $is_information_schema = strtolower($db) == 'information_schema';
 439     $is_data_dictionary = PMA_DRIZZLE && strtolower($db) == 'data_dictionary';
 440     if (($is_information_schema || $is_data_dictionary) && ($source == 'internal' || $source == 'both')) {
 441         if ($is_information_schema) {
 442             $relations_key = 'information_schema_relations';
 443             require_once './libraries/information_schema_relations.lib.php';
 444         } else {
 445             $relations_key = 'data_dictionary_relations';
 446             require_once './libraries/data_dictionary_relations.lib.php';
 447         }
 448         if (isset($GLOBALS[$relations_key][$table])) {
 449             foreach ($GLOBALS[$relations_key][$table] as $field => $relations) {
 450                 if ((! strlen($column) || $column == $field)
 451                  && (! isset($foreign[$field]) || ! strlen($foreign[$field]))) {
 452                     $foreign[$field] = $relations;
 453                 }
 454             }
 455         }
 456     }
 457
 458     return $foreign;
 459 } // end of the 'PMA_getForeigners()' function
 460
 461 /**
 462  * Gets the display field of a table
 463  *
 464  * @access  public
 465  * @param string   $db    the name of the db to check for
 466  * @param string   $table the name of the table to check for
 467  * @return  string   field name
 468  */
 469 function PMA_getDisplayField($db, $table)
 470 {
 471     $cfgRelation = PMA_getRelationsParam();
 472
 473     /**
 474      * Try to fetch the display field from DB.
 475      */
 476     if ($cfgRelation['displaywork']) {
 477         $disp_query = '
 478              SELECT `display_field`
 479                FROM ' . PMA_backquote($cfgRelation['db']) . '.' . PMA_backquote($cfgRelation['table_info']) . '
 480               WHERE `db_name`    = \'' . PMA_sqlAddSlashes($db) . '\'
 481                 AND `table_name` = \'' . PMA_sqlAddSlashes($table) . '\'';
 482
 483         $row = PMA_DBI_fetch_single_row($disp_query, 'ASSOC', $GLOBALS['controllink']);
 484         if (isset($row['display_field'])) {
 485             return $row['display_field'];
 486         }
 487     }
 488
 489     /**
 490      * Emulating the display field for some information_schema tables.
 491      */
 492     if ($db == 'information_schema') {
 493         switch ($table) {
 494             case 'CHARACTER_SETS': return 'DESCRIPTION';
 495             case 'TABLES':         return 'TABLE_COMMENT';
 496         }
 497     }
 498
 499     /**
 500      * No Luck...
 501      */
 502     return false;
 503
 504 } // end of the 'PMA_getDisplayField()' function
 505
 506 /**
 507  * Gets the comments for all rows of a table or the db itself
 508  *
 509  * @access  public
 510  * @param string   the name of the db to check for
 511  * @param string   the name of the table to check for
 512  * @return  array    [field_name] = comment
 513  */
 514 function PMA_getComments($db, $table = '')
 515 {
 516     $comments = array();
 517
 518     if ($table != '') {
 519         // MySQL native column comments
 520         $fields = PMA_DBI_get_columns($db, $table, null, true);
 521         if ($fields) {
 522             foreach ($fields as $field) {
 523                 if (! empty($field['Comment'])) {
 524                     $comments[$field['Field']] = $field['Comment'];
 525                 }
 526             }
 527         }
 528     } else {
 529         $comments[] = PMA_getDbComment($db);
 530     }
 531
 532     return $comments;
 533 } // end of the 'PMA_getComments()' function
 534
 535 /**
 536  * Gets the comment for a db
 537  *
 538  * @access  public
 539  * @param string   the name of the db to check for
 540  * @return  string   comment
 541  */
 542 function PMA_getDbComment($db)
 543 {
 544     $cfgRelation = PMA_getRelationsParam();
 545     $comment = '';
 546
 547     if ($cfgRelation['commwork']) {
 548         // pmadb internal db comment
 549         $com_qry = "
 550              SELECT `comment`
 551                FROM " . PMA_backquote($cfgRelation['db']) . "." . PMA_backquote($cfgRelation['column_info']) . "
 552               WHERE db_name     = '" . PMA_sqlAddSlashes($db) . "'
 553                 AND table_name  = ''
 554                 AND column_name = '(db_comment)'";
 555         $com_rs = PMA_query_as_controluser($com_qry, true, PMA_DBI_QUERY_STORE);
 556
 557         if ($com_rs && PMA_DBI_num_rows($com_rs) > 0) {
 558             $row = PMA_DBI_fetch_assoc($com_rs);
 559             $comment = $row['comment'];
 560         }
 561         PMA_DBI_free_result($com_rs);
 562     }
 563
 564     return $comment;
 565 } // end of the 'PMA_getDbComment()' function
 566
 567 /**
 568  * Gets the comment for a db
 569  *
 570  * @access  public
 571  * @param string   the name of the db to check for
 572  * @return  string   comment
 573  */
 574 function PMA_getDbComments()
 575 {
 576     $cfgRelation = PMA_getRelationsParam();
 577     $comments = array();
 578
 579     if ($cfgRelation['commwork']) {
 580         // pmadb internal db comment
 581         $com_qry = "
 582              SELECT `db_name`, `comment`
 583                FROM " . PMA_backquote($cfgRelation['db']) . "." . PMA_backquote($cfgRelation['column_info']) . "
 584               WHERE `column_name` = '(db_comment)'";
 585         $com_rs = PMA_query_as_controluser($com_qry, true, PMA_DBI_QUERY_STORE);
 586
 587         if ($com_rs && PMA_DBI_num_rows($com_rs) > 0) {
 588             while ($row = PMA_DBI_fetch_assoc($com_rs)) {
 589                 $comments[$row['db_name']] = $row['comment'];
 590             }
 591         }
 592         PMA_DBI_free_result($com_rs);
 593     }
 594
 595     return $comments;
 596 } // end of the 'PMA_getDbComments()' function
 597
 598 /**
 599  * Set a database comment to a certain value.
 600  *
 601  * @access  public
 602  * @param string   $db        the name of the db
 603  * @param string   $comment   the value of the column
 604  * @return  boolean  true, if comment-query was made.
 605  */
 606 function PMA_setDbComment($db, $comment = '')
 607 {
 608     $cfgRelation = PMA_getRelationsParam();
 609
 610     if (! $cfgRelation['commwork']) {
 611         return false;
 612     }
 613
 614     if (strlen($comment)) {
 615         $upd_query = "
 616              INSERT INTO
 617                     " . PMA_backquote($cfgRelation['db']) . "." . PMA_backquote($cfgRelation['column_info']) . "
 618                     (`db_name`, `table_name`, `column_name`, `comment`)
 619              VALUES (
 620                    '" . PMA_sqlAddSlashes($db) . "',
 621                    '',
 622                    '(db_comment)',
 623                    '" . PMA_sqlAddSlashes($comment) . "')
 624              ON DUPLICATE KEY UPDATE
 625                 `comment` = '" . PMA_sqlAddSlashes($comment) . "'";
 626     } else {
 627         $upd_query = '
 628              DELETE FROM
 629                     ' . PMA_backquote($cfgRelation['db']) . '.' . PMA_backquote($cfgRelation['column_info']) . '
 630               WHERE `db_name`     = \'' . PMA_sqlAddSlashes($db) . '\'
 631                 AND `table_name`  = \'\'
 632                 AND `column_name` = \'(db_comment)\'';
 633     }
 634
 635     if (isset($upd_query)) {
 636         return PMA_query_as_controluser($upd_query);
 637     }
 638
 639     return false;
 640 } // end of 'PMA_setDbComment()' function
 641
 642 /**
 643  * Set a SQL history entry
 644  *
 645  * @param string   $db        the name of the db
 646  * @param string   $table     the name of the table
 647  * @param string   $username  the username
 648  * @param string   $sqlquery  the sql query
 649  * @access  public
 650  */
 651 function PMA_setHistory($db, $table, $username, $sqlquery)
 652 {
 653     if (strlen($sqlquery) > $GLOBALS['cfg']['MaxCharactersInDisplayedSQL']) {
 654         return;
 655     }
 656
 657     $cfgRelation = PMA_getRelationsParam();
 658
 659     if (! isset($_SESSION['sql_history'])) {
 660         $_SESSION['sql_history'] = array();
 661     }
 662
 663     $key = md5($sqlquery . $db . $table);
 664
 665     if (isset($_SESSION['sql_history'][$key])) {
 666         unset($_SESSION['sql_history'][$key]);
 667     }
 668
 669     $_SESSION['sql_history'][$key] = array(
 670         'db' => $db,
 671         'table' => $table,
 672         'sqlquery' => $sqlquery,
 673     );
 674
 675     if (count($_SESSION['sql_history']) > $GLOBALS['cfg']['QueryHistoryMax']) {
 676         // history should not exceed a maximum count
 677         array_shift($_SESSION['sql_history']);
 678     }
 679
 680     if (! $cfgRelation['historywork'] || ! $GLOBALS['cfg']['QueryHistoryDB']) {
 681         return;
 682     }
 683
 684     PMA_query_as_controluser('
 685          INSERT INTO
 686                 ' . PMA_backquote($cfgRelation['db']) . '.' . PMA_backquote($cfgRelation['history']) . '
 687               (`username`,
 688                 `db`,
 689                 `table`,
 690                 `timevalue`,
 691                 `sqlquery`)
 692          VALUES
 693               (\'' . PMA_sqlAddSlashes($username) . '\',
 694                \'' . PMA_sqlAddSlashes($db) . '\',
 695                \'' . PMA_sqlAddSlashes($table) . '\',
 696                NOW(),
 697                \'' . PMA_sqlAddSlashes($sqlquery) . '\')');
 698 } // end of 'PMA_setHistory()' function
 699
 700 /**
 701  * Gets a SQL history entry
 702  *
 703  * @param string   $username  the username
 704  * @return  array    list of history items
 705  * @access  public
 706  */
 707 function PMA_getHistory($username)
 708 {
 709     $cfgRelation = PMA_getRelationsParam();
 710
 711     if (! $cfgRelation['historywork']) {
 712         return false;
 713     }
 714
 715     $hist_query = '
 716          SELECT `db`,
 717                 `table`,
 718                 `sqlquery`
 719            FROM ' . PMA_backquote($cfgRelation['db']) . '.' . PMA_backquote($cfgRelation['history']) . '
 720           WHERE `username` = \'' . PMA_sqlAddSlashes($username) . '\'
 721        ORDER BY `id` DESC';
 722
 723     return PMA_DBI_fetch_result($hist_query, null, null, $GLOBALS['controllink']);
 724 } // end of 'PMA_getHistory()' function
 725
 726 /**
 727  * purges SQL history
 728  *
 729  * deletes entries that exceeds $cfg['QueryHistoryMax'], oldest first, for the
 730  * given user
 731  *
 732  * @param string   $username  the username
 733  * @access  public
 734  */
 735 function PMA_purgeHistory($username)
 736 {
 737     $cfgRelation = PMA_getRelationsParam();
 738     if (! $GLOBALS['cfg']['QueryHistoryDB'] || ! $cfgRelation['historywork']) {
 739         return;
 740     }
 741
 742     if (! $cfgRelation['historywork']) {
 743         return;
 744     }
 745
 746     $search_query = '
 747          SELECT `timevalue`
 748            FROM ' . PMA_backquote($cfgRelation['db']) . '.' . PMA_backquote($cfgRelation['history']) . '
 749           WHERE `username` = \'' . PMA_sqlAddSlashes($username) . '\'
 750        ORDER BY `timevalue` DESC
 751           LIMIT ' . $GLOBALS['cfg']['QueryHistoryMax'] . ', 1';
 752
 753     if ($max_time = PMA_DBI_fetch_value($search_query, 0, 0, $GLOBALS['controllink'])) {
 754         PMA_query_as_controluser('
 755              DELETE FROM
 756                     ' . PMA_backquote($cfgRelation['db']) . '.' . PMA_backquote($cfgRelation['history']) . '
 757               WHERE `username` = \'' . PMA_sqlAddSlashes($username) . '\'
 758                 AND `timevalue` <= \'' . $max_time . '\'');
 759     }
 760 } // end of 'PMA_purgeHistory()' function
 761
 762 /**
 763  * Prepares the dropdown for one mode
 764  *
 765  * @param array    $foreign   the keys and values for foreigns
 766  * @param string   $data      the current data of the dropdown
 767  * @param string   $mode      the needed mode
 768  *
 769  * @return  array   the <option value=""><option>s
 770  *
 771  * @access  protected
 772  */
 773 function PMA__foreignDropdownBuild($foreign, $data, $mode)
 774 {
 775     $reloptions = array();
 776
 777     // id-only is a special mode used when no foreign display column
 778     // is available
 779     if ($mode == 'id-content' || $mode == 'id-only') {
 780         // sort for id-content
 781         if ($GLOBALS['cfg']['NaturalOrder']) {
 782             uksort($foreign, 'strnatcasecmp');
 783         } else {
 784             ksort($foreign);
 785         }
 786     } elseif ($mode == 'content-id') {
 787         // sort for content-id
 788         if ($GLOBALS['cfg']['NaturalOrder']) {
 789             natcasesort($foreign);
 790         } else {
 791             asort($foreign);
 792         }
 793     }
 794
 795     foreach ($foreign as $key => $value) {
 796         if (PMA_strlen($value) <= $GLOBALS['cfg']['LimitChars']) {
 797             $vtitle = '';
 798             $value  = htmlspecialchars($value);
 799         } else {
 800             $vtitle  = htmlspecialchars($value);
 801             $value  = htmlspecialchars(substr($value, 0, $GLOBALS['cfg']['LimitChars']) . '...');
 802         }
 803
 804         $reloption = '<option value="' . htmlspecialchars($key) . '"';
 805         if ($vtitle != '') {
 806             $reloption .= ' title="' . $vtitle . '"';
 807         }
 808
 809         if ((string) $key == (string) $data) {
 810            $reloption .= ' selected="selected"';
 811         }
 812
 813         if ($mode == 'content-id') {
 814             $reloptions[] = $reloption . '>' . $value . '&nbsp;-&nbsp;' . htmlspecialchars($key) .  '</option>';
 815         } elseif ($mode == 'id-content') {
 816             $reloptions[] = $reloption . '>' . htmlspecialchars($key) .  '&nbsp;-&nbsp;' . $value . '</option>';
 817         } elseif ($mode == 'id-only') {
 818             $reloptions[] = $reloption . '>' . htmlspecialchars($key) . '</option>';
 819         }
 820     } // end foreach
 821
 822     return $reloptions;
 823 } // end of 'PMA__foreignDropdownBuild' function
 824
 825 /**
 826  * Outputs dropdown with values of foreign fields
 827  *
 828  * @param array    array of the displayed row
 829  * @param string   the foreign field
 830  * @param string   the foreign field to display
 831  * @param string   the current data of the dropdown (field in row)
 832  * @return  string   the <option value=""><option>s
 833  * @access  public
 834  */
 835 function PMA_foreignDropdown($disp_row, $foreign_field, $foreign_display, $data,
 836     $max = null)
 837 {
 838     if (null === $max) {
 839         $max = $GLOBALS['cfg']['ForeignKeyMaxLimit'];
 840     }
 841
 842     $foreign = array();
 843
 844     // collect the data
 845     foreach ($disp_row as $relrow) {
 846         $key   = $relrow[$foreign_field];
 847
 848         // if the display field has been defined for this foreign table
 849         if ($foreign_display) {
 850             $value  = $relrow[$foreign_display];
 851         } else {
 852             $value = '';
 853         } // end if ($foreign_display)
 854
 855         $foreign[$key] = $value;
 856     } // end foreach
 857
 858     // put the dropdown sections in correct order
 859     $top = array();
 860     $bottom = array();
 861     if ($foreign_display) {
 862         if (PMA_isValid($GLOBALS['cfg']['ForeignKeyDropdownOrder'], 'array')) {
 863             if (PMA_isValid($GLOBALS['cfg']['ForeignKeyDropdownOrder'][0])) {
 864                 $top = PMA__foreignDropdownBuild($foreign, $data,
 865                     $GLOBALS['cfg']['ForeignKeyDropdownOrder'][0]);
 866             }
 867             if (PMA_isValid($GLOBALS['cfg']['ForeignKeyDropdownOrder'][1])) {
 868                 $bottom = PMA__foreignDropdownBuild($foreign, $data,
 869                     $GLOBALS['cfg']['ForeignKeyDropdownOrder'][1]);
 870             }
 871         } else {
 872             $top = PMA__foreignDropdownBuild($foreign, $data, 'id-content');
 873             $bottom = PMA__foreignDropdownBuild($foreign, $data, 'content-id');
 874         }
 875     } else {
 876         $top = PMA__foreignDropdownBuild($foreign, $data, 'id-only');
 877     }
 878
 879     // beginning of dropdown
 880     $ret = '<option value="">&nbsp;</option>';
 881     $top_count = count($top);
 882     if ($max == -1 || $top_count < $max) {
 883         $ret .= implode('', $top);
 884         if ($foreign_display && $top_count > 0) {
 885             // this empty option is to visually mark the beginning of the
 886             // second series of values (bottom)
 887             $ret .= '<option value="">&nbsp;</option>';
 888         }
 889     }
 890     if ($foreign_display) {
 891         $ret .= implode('', $bottom);
 892     }
 893
 894     return $ret;
 895 } // end of 'PMA_foreignDropdown()' function
 896
 897 /**
 898  * Gets foreign keys in preparation for a drop-down selector
 899  *
 900  * @param array    array of the foreign keys
 901  * @param string   the foreign field name
 902  * @param bool     whether to override the total
 903  * @param string   a possible filter
 904  * @param string   a possible LIMIT clause
 905  * @return  array    data about the foreign keys
 906  * @access  public
 907  */
 908
 909 function PMA_getForeignData($foreigners, $field, $override_total, $foreign_filter, $foreign_limit)
 910 {
 911     // we always show the foreign field in the drop-down; if a display
 912     // field is defined, we show it besides the foreign field
 913     $foreign_link = false;
 914     if ($foreigners && isset($foreigners[$field])) {
 915         $foreigner       = $foreigners[$field];
 916         $foreign_db      = $foreigner['foreign_db'];
 917         $foreign_table   = $foreigner['foreign_table'];
 918         $foreign_field   = $foreigner['foreign_field'];
 919
 920         // Count number of rows in the foreign table. Currently we do
 921         // not use a drop-down if more than 200 rows in the foreign table,
 922         // for speed reasons and because we need a better interface for this.
 923         //
 924         // We could also do the SELECT anyway, with a LIMIT, and ensure that
 925         // the current value of the field is one of the choices.
 926
 927         $the_total   = PMA_Table::countRecords($foreign_db, $foreign_table);
 928
 929         if ($override_total == true || $the_total < $GLOBALS['cfg']['ForeignKeyMaxLimit']) {
 930             // foreign_display can be false if no display field defined:
 931             $foreign_display = PMA_getDisplayField($foreign_db, $foreign_table);
 932
 933             $f_query_main = 'SELECT ' . PMA_backquote($foreign_field)
 934                         . (($foreign_display == false) ? '' : ', ' . PMA_backquote($foreign_display));
 935             $f_query_from = ' FROM ' . PMA_backquote($foreign_db) . '.' . PMA_backquote($foreign_table);
 936             $f_query_filter = empty($foreign_filter) ? '' : ' WHERE ' . PMA_backquote($foreign_field)
 937                             . ' LIKE "%' . PMA_sqlAddSlashes($foreign_filter, true) . '%"'
 938                             . (($foreign_display == false) ? '' : ' OR ' . PMA_backquote($foreign_display)
 939                                 . ' LIKE "%' . PMA_sqlAddSlashes($foreign_filter, true) . '%"'
 940                                 );
 941             $f_query_order = ($foreign_display == false) ? '' :' ORDER BY ' . PMA_backquote($foreign_table) . '.' . PMA_backquote($foreign_display);
 942             $f_query_limit = isset($foreign_limit) ? $foreign_limit : '';
 943
 944             if (!empty($foreign_filter)) {
 945                 $res = PMA_DBI_query('SELECT COUNT(*)' . $f_query_from . $f_query_filter);
 946                 if ($res) {
 947                     $the_total = PMA_DBI_fetch_value($res);
 948                     @PMA_DBI_free_result($res);
 949                 } else {
 950                     $the_total = 0;
 951                 }
 952             }
 953
 954             $disp  = PMA_DBI_query($f_query_main . $f_query_from . $f_query_filter . $f_query_order . $f_query_limit);
 955             if ($disp && PMA_DBI_num_rows($disp) > 0) {
 956                 // If a resultset has been created, pre-cache it in the $disp_row array
 957                 // This helps us from not needing to use mysql_data_seek by accessing a pre-cached
 958                 // PHP array. Usually those resultsets are not that big, so a performance hit should
 959                 // not be expected.
 960                 $disp_row = array();
 961                 while ($single_disp_row = @PMA_DBI_fetch_assoc($disp)) {
 962                     $disp_row[] = $single_disp_row;
 963                 }
 964                 @PMA_DBI_free_result($disp);
 965             }
 966         } else {
 967             $disp_row = null;
 968             $foreign_link = true;
 969         }
 970     }  // end if $foreigners
 971
 972     $foreignData['foreign_link'] = $foreign_link;
 973     $foreignData['the_total'] = isset($the_total) ? $the_total : null;
 974     $foreignData['foreign_display'] = isset($foreign_display) ? $foreign_display : null;
 975     $foreignData['disp_row'] = isset($disp_row) ? $disp_row : null;
 976     $foreignData['foreign_field'] = isset($foreign_field) ? $foreign_field : null;
 977     return $foreignData;
 978 } // end of 'PMA_getForeignData()' function
 979
 980 /**
 981  * Finds all related tables
 982  *
 983  * @param string   whether to go from master to foreign or vice versa
 984  * @return  boolean  always true
 985  * @global  array    $tab_left the list of tables that we still couldn't connect
 986  * @global  array    $tab_know the list of allready connected tables
 987  * @global  string   $fromclause
 988  *
 989  * @access  private
 990  */
 991 function PMA_getRelatives($from)
 992 {
 993     global $tab_left, $tab_know, $fromclause;
 994
 995     if ($from == 'master') {
 996         $to    = 'foreign';
 997     } else {
 998         $to    = 'master';
 999     }
1000     $in_know = '(\'' . implode('\', \'', $tab_know) . '\')';
1001     $in_left = '(\'' . implode('\', \'', $tab_left) . '\')';
1002
1003     $rel_query = 'SELECT *'
1004                . '  FROM ' . PMA_backquote($GLOBALS['cfgRelation']['db'])
1005                .       '.' . PMA_backquote($GLOBALS['cfgRelation']['relation'])
1006                . ' WHERE ' . $from . '_db = \'' . PMA_sqlAddSlashes($GLOBALS['db']) . '\''
1007                . '   AND ' . $to   . '_db = \'' . PMA_sqlAddSlashes($GLOBALS['db']) . '\''
1008                . '   AND ' . $from . '_table IN ' . $in_know
1009                . '   AND ' . $to   . '_table IN ' . $in_left;
1010     $relations = @PMA_DBI_query($rel_query, $GLOBALS['controllink']);
1011     while ($row = PMA_DBI_fetch_assoc($relations)) {
1012         $found_table                = $row[$to . '_table'];
1013         if (isset($tab_left[$found_table])) {
1014             $fromclause
1015                 .= "\n" . ' LEFT JOIN '
1016                 . PMA_backquote($GLOBALS['db']) . '.' . PMA_backquote($row[$to . '_table']) . ' ON '
1017                 . PMA_backquote($row[$from . '_table']) . '.'
1018                 . PMA_backquote($row[$from . '_field']) . ' = '
1019                 . PMA_backquote($row[$to . '_table']) . '.'
1020                 . PMA_backquote($row[$to . '_field']) . ' ';
1021             $tab_know[$found_table] = $found_table;
1022             unset($tab_left[$found_table]);
1023         }
1024     } // end while
1025
1026     return true;
1027 } // end of the "PMA_getRelatives()" function
1028
1029 /**
1030  * Rename a field in relation tables
1031  *
1032  * usually called after a field in a table was renamed in tbl_alter.php
1033  *
1034  * @param string $db
1035  * @param string $table
1036  * @param string $field
1037  * @param string $new_name
1038  */
1039 function PMA_REL_renameField($db, $table, $field, $new_name)
1040 {
1041     $cfgRelation = PMA_getRelationsParam();
1042
1043     if ($cfgRelation['displaywork']) {
1044         $table_query = 'UPDATE ' . PMA_backquote($cfgRelation['db']) . '.' . PMA_backquote($cfgRelation['table_info'])
1045                       . '   SET display_field = \'' . PMA_sqlAddSlashes($new_name) . '\''
1046                       . ' WHERE db_name       = \'' . PMA_sqlAddSlashes($db) . '\''
1047                       . '   AND table_name    = \'' . PMA_sqlAddSlashes($table) . '\''
1048                       . '   AND display_field = \'' . PMA_sqlAddSlashes($field) . '\'';
1049         PMA_query_as_controluser($table_query);
1050     }
1051
1052     if ($cfgRelation['relwork']) {
1053         $table_query = 'UPDATE ' . PMA_backquote($cfgRelation['db']) . '.' . PMA_backquote($cfgRelation['relation'])
1054                       . '   SET master_field = \'' . PMA_sqlAddSlashes($new_name) . '\''
1055                       . ' WHERE master_db    = \'' . PMA_sqlAddSlashes($db) . '\''
1056                       . '   AND master_table = \'' . PMA_sqlAddSlashes($table) . '\''
1057                       . '   AND master_field = \'' . PMA_sqlAddSlashes($field) . '\'';
1058         PMA_query_as_controluser($table_query);
1059
1060         $table_query = 'UPDATE ' . PMA_backquote($cfgRelation['db']) . '.' . PMA_backquote($cfgRelation['relation'])
1061                       . '   SET foreign_field = \'' . PMA_sqlAddSlashes($new_name) . '\''
1062                       . ' WHERE foreign_db    = \'' . PMA_sqlAddSlashes($db) . '\''
1063                       . '   AND foreign_table = \'' . PMA_sqlAddSlashes($table) . '\''
1064                       . '   AND foreign_field = \'' . PMA_sqlAddSlashes($field) . '\'';
1065         PMA_query_as_controluser($table_query);
1066     } // end if relwork
1067 }
1068
1069 /**
1070  * Create a PDF page
1071  *
1072  * @param string    $newpage
1073  * @param array     $cfgRelation
1074  * @param string    $db
1075  * @return string   $pdf_page_number
1076  */
1077 function PMA_REL_create_page($newpage, $cfgRelation, $db)
1078 {
1079     if (! isset($newpage) || $newpage == '') {
1080         $newpage = __('no description');
1081     }
1082     $ins_query   = 'INSERT INTO ' . PMA_backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_backquote($cfgRelation['pdf_pages'])
1083                  . ' (db_name, page_descr)'
1084                  . ' VALUES (\'' . PMA_sqlAddSlashes($db) . '\', \'' . PMA_sqlAddSlashes($newpage) . '\')';
1085     PMA_query_as_controluser($ins_query, false);
1086     return PMA_DBI_insert_id(isset($GLOBALS['controllink']) ? $GLOBALS['controllink'] : '');
1087 }
1088 ?>