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