libraries/relation.lib.php

   1 <?php
   2 /* $Id$ */
   3 // vim: expandtab sw=4 ts=4 sts=4:
   4
   5 /**
   6  * Set of functions used with the relation and pdf feature
   7  */
   8
   9 /**
  10  * Executes a query as controluser if possible, otherwise as normal user
  11  *
  12  * @param   string    the query to execute
  13  * @param   boolean   whether to display SQL error messages or not
  14  *
  15  * @return  integer   the result id
  16  *
  17  * @global  string    the URL of the page to show in case of error
  18  * @global  string    the name of db to come back to
  19  * @global  resource  the resource id of DB connect as controluser
  20  * @global  array     configuration infos about the relations stuff
  21  *
  22  * @access  public
  23  *
  24  * @author  Mike Beck <mikebeck@users.sourceforge.net>
  25  */
  26  function PMA_query_as_cu($sql, $show_error = TRUE, $options = 0) {
  27     global $err_url_0, $db, $dbh, $cfgRelation;
  28
  29     if ($dbh == $GLOBALS['userlink']) {
  30         PMA_DBI_select_db($cfgRelation['db'], $dbh);
  31     }
  32     if ($show_error) {
  33         $result = PMA_DBI_query($sql, $dbh, $options);
  34     } else {
  35         $result = @PMA_DBI_try_query($sql, $dbh, $options);
  36     } // end if... else...
  37     // It makes no sense to restore database on control user
  38     if ($dbh == $GLOBALS['userlink']) {
  39         PMA_DBI_select_db($db, $dbh);
  40     }
  41
  42     if ($result) {
  43         return $result;
  44     } else {
  45         return FALSE;
  46     }
  47  } // end of the "PMA_query_as_cu()" function
  48
  49
  50 /**
  51  * Defines the relation parameters for the current user
  52  * just a copy of the functions used for relations ;-)
  53  * but added some stuff to check what will work
  54  *
  55  * @param   boolean  whether to check validity of settings or not
  56  *
  57  * @return  array    the relation parameters for the current user
  58  *
  59  * @global  array    the list of settings for servers
  60  * @global  integer  the id of the current server
  61  * @global  string   the URL of the page to show in case of error
  62  * @global  string   the name of the current db
  63  * @global  string   the name of the current table
  64  * @global  array    configuration infos about the relations stuff
  65  *
  66  * @access  public
  67  *
  68  * @author  Mike Beck <mikebeck@users.sourceforge.net>
  69  */
  70 function PMA_getRelationsParam($verbose = FALSE)
  71 {
  72     global $cfg, $server, $err_url_0, $db, $table, $dbh;
  73     global $cfgRelation;
  74
  75     $cfgRelation                = array();
  76     $cfgRelation['relwork']     = FALSE;
  77     $cfgRelation['displaywork'] = FALSE;
  78     $cfgRelation['bookmarkwork']= FALSE;
  79     $cfgRelation['pdfwork']     = FALSE;
  80     $cfgRelation['commwork']    = FALSE;
  81     $cfgRelation['mimework']    = FALSE;
  82     $cfgRelation['historywork'] = FALSE;
  83     $cfgRelation['allworks']    = FALSE;
  84
  85     // No server selected -> no bookmark table
  86     // we return the array with the FALSEs in it,
  87     // to avoid some 'Unitialized string offset' errors later
  88     if ($server == 0
  89        || empty($cfg['Server'])
  90        || empty($cfg['Server']['pmadb'])) {
  91         if ($verbose == TRUE) {
  92             echo 'PMA Database ... '
  93                  . '<font color="red"><b>' . $GLOBALS['strNotOK'] . '</b></font>'
  94                  . '[ <a href="Documentation.html#pmadb">' . $GLOBALS['strDocu'] . '</a> ]<br />' . "\n"
  95                  . $GLOBALS['strGeneralRelationFeat']
  96                  . ' <font color="green">' . $GLOBALS['strDisabled'] . '</font>' . "\n";
  97         }
  98         return $cfgRelation;
  99     }
 100
 101     $cfgRelation['user']  = $cfg['Server']['user'];
 102     $cfgRelation['db']    = $cfg['Server']['pmadb'];
 103
 104     //  Now I just check if all tables that i need are present so I can for
 105     //  example enable relations but not pdf...
 106     //  I was thinking of checking if they have all required columns but I
 107     //  fear it might be too slow
 108
 109     PMA_DBI_select_db($cfgRelation['db'], $dbh);
 110     $tab_query = 'SHOW TABLES FROM ' . PMA_backquote($cfgRelation['db']);
 111     $tab_rs    = PMA_query_as_cu($tab_query, FALSE, PMA_DBI_QUERY_STORE);
 112
 113     if ($tab_rs) {
 114         while ($curr_table = @PMA_DBI_fetch_row($tab_rs)) {
 115             if ($curr_table[0] == $cfg['Server']['bookmarktable']) {
 116                 $cfgRelation['bookmark']        = $curr_table[0];
 117             } else if ($curr_table[0] == $cfg['Server']['relation']) {
 118                 $cfgRelation['relation']        = $curr_table[0];
 119             } else if ($curr_table[0] == $cfg['Server']['table_info']) {
 120                 $cfgRelation['table_info']      = $curr_table[0];
 121             } else if ($curr_table[0] == $cfg['Server']['table_coords']) {
 122                 $cfgRelation['table_coords']    = $curr_table[0];
 123             } else if ($curr_table[0] == $cfg['Server']['column_info']) {
 124                 $cfgRelation['column_info'] = $curr_table[0];
 125             } else if ($curr_table[0] == $cfg['Server']['pdf_pages']) {
 126                 $cfgRelation['pdf_pages']       = $curr_table[0];
 127             } else if ($curr_table[0] == $cfg['Server']['history']) {
 128                 $cfgRelation['history'] = $curr_table[0];
 129             }
 130         } // end while
 131     }
 132
 133     if (isset($cfgRelation['relation'])) {
 134         $cfgRelation['relwork']         = TRUE;
 135         if (isset($cfgRelation['table_info'])) {
 136                 $cfgRelation['displaywork'] = TRUE;
 137         }
 138     }
 139     if (isset($cfgRelation['table_coords']) && isset($cfgRelation['pdf_pages'])) {
 140         $cfgRelation['pdfwork']     = TRUE;
 141     }
 142     if (isset($cfgRelation['column_info'])) {
 143         $cfgRelation['commwork']    = TRUE;
 144
 145         if ($cfg['Server']['verbose_check']) {
 146             $mime_query  = 'SHOW FIELDS FROM ' . PMA_backquote($cfgRelation['db']) . '.' . PMA_backquote($cfgRelation['column_info']);
 147             $mime_rs     = PMA_query_as_cu($mime_query, FALSE);
 148
 149             $mime_field_mimetype                = FALSE;
 150             $mime_field_transformation          = FALSE;
 151             $mime_field_transformation_options  = FALSE;
 152             while ($curr_mime_field = @PMA_DBI_fetch_row($mime_rs)) {
 153                 if ($curr_mime_field[0] == 'mimetype') {
 154                     $mime_field_mimetype               = TRUE;
 155                 } else if ($curr_mime_field[0] == 'transformation') {
 156                     $mime_field_transformation         = TRUE;
 157                 } else if ($curr_mime_field[0] == 'transformation_options') {
 158                     $mime_field_transformation_options = TRUE;
 159                 }
 160             }
 161             PMA_DBI_free_result($mime_rs);
 162
 163             if ($mime_field_mimetype == TRUE
 164                 && $mime_field_transformation == TRUE
 165                 && $mime_field_transformation_options == TRUE) {
 166                 $cfgRelation['mimework'] = TRUE;
 167             }
 168         } else {
 169             $cfgRelation['mimework'] = TRUE;
 170         }
 171     }
 172
 173     if (isset($cfgRelation['history'])) {
 174         $cfgRelation['historywork']     = TRUE;
 175     }
 176
 177     if (isset($cfgRelation['bookmark'])) {
 178         $cfgRelation['bookmarkwork']     = TRUE;
 179     }
 180
 181     if ($cfgRelation['relwork'] == TRUE && $cfgRelation['displaywork'] == TRUE
 182         && $cfgRelation['pdfwork'] == TRUE && $cfgRelation['commwork'] == TRUE
 183         && $cfgRelation['mimework'] == TRUE && $cfgRelation['historywork'] == TRUE
 184         && $cfgRelation['bookmarkwork'] == TRUE) {
 185         $cfgRelation['allworks'] = TRUE;
 186     }
 187     if ($tab_rs) {
 188         PMA_DBI_free_result($tab_rs);
 189     } else {
 190         $cfg['Server']['pmadb'] = FALSE;
 191     }
 192
 193     if ($verbose == TRUE) {
 194         $shit     = '<font color="red"><b>' . $GLOBALS['strNotOK'] . '</b></font> [ <a href="Documentation.html#%s">' . $GLOBALS['strDocu'] . '</a> ]';
 195         $hit      = '<font color="green"><b>' . $GLOBALS['strOK'] . '</b></font>';
 196         $enabled  = '<font color="green">' . $GLOBALS['strEnabled'] . '</font>';
 197         $disabled = '<font color="red">'   . $GLOBALS['strDisabled'] . '</font>';
 198
 199         echo '<table>' . "\n";
 200         echo '    <tr><th align="left">$cfg[\'Servers\'][$i][\'pmadb\'] ... </th><td align="right">'
 201              . (($cfg['Server']['pmadb'] == FALSE) ? sprintf($shit, 'pmadb') : $hit)
 202              . '</td></tr>' . "\n";
 203         echo '    <tr><td>&nbsp;</td></tr>' . "\n";
 204
 205         echo '    <tr><th align="left">$cfg[\'Servers\'][$i][\'relation\'] ... </th><td align="right">'
 206              . ((isset($cfgRelation['relation'])) ? $hit : sprintf($shit, 'relation'))
 207              . '</td></tr>' . "\n";
 208         echo '    <tr><td colspan=2 align="center">'. $GLOBALS['strGeneralRelationFeat'] . ': '
 209              . (($cfgRelation['relwork'] == TRUE) ? $enabled :  $disabled)
 210              . '</td></tr>' . "\n";
 211         echo '    <tr><td>&nbsp;</td></tr>' . "\n";
 212
 213         echo '    <tr><th align="left">$cfg[\'Servers\'][$i][\'table_info\']   ... </th><td align="right">'
 214              . (($cfgRelation['displaywork'] == FALSE) ? sprintf($shit, 'table_info') : $hit)
 215              . '</td></tr>' . "\n";
 216         echo '    <tr><td colspan=2 align="center">' . $GLOBALS['strDisplayFeat'] . ': '
 217              . (($cfgRelation['displaywork'] == TRUE) ? $enabled : $disabled)
 218              . '</td></tr>' . "\n";
 219         echo '    <tr><td>&nbsp;</td></tr>' . "\n";
 220
 221         echo '    <tr><th align="left">$cfg[\'Servers\'][$i][\'table_coords\'] ... </th><td align="right">'
 222              . ((isset($cfgRelation['table_coords'])) ? $hit : sprintf($shit, 'table_coords'))
 223              . '</td></tr>' . "\n";
 224         echo '    <tr><th align="left">$cfg[\'Servers\'][$i][\'pdf_pages\'] ... </th><td align="right">'
 225              . ((isset($cfgRelation['pdf_pages'])) ? $hit : sprintf($shit, 'table_coords'))
 226              . '</td></tr>' . "\n";
 227         echo '    <tr><td colspan=2 align="center">' . $GLOBALS['strCreatePdfFeat'] . ': '
 228              . (($cfgRelation['pdfwork'] == TRUE) ? $enabled : $disabled)
 229              . '</td></tr>' . "\n";
 230         echo '    <tr><td>&nbsp;</td></tr>' . "\n";
 231
 232         echo '    <tr><th align="left">$cfg[\'Servers\'][$i][\'column_info\'] ... </th><td align="right">'
 233              . ((isset($cfgRelation['column_info'])) ? $hit : sprintf($shit, 'col_com'))
 234              . '</td></tr>' . "\n";
 235         echo '    <tr><td colspan=2 align="center">' . $GLOBALS['strColComFeat'] . ': '
 236              . (($cfgRelation['commwork'] == TRUE) ? $enabled : $disabled)
 237              . '</td></tr>' . "\n";
 238         echo '    <tr><td colspan=2 align="center">' . $GLOBALS['strBookmarkQuery'] . ': '
 239              . (($cfgRelation['bookmarkwork'] == TRUE) ? $enabled : $disabled)
 240              . '</td></tr>' . "\n";
 241         echo '    <tr><th align="left">MIME ...</th><td align="right">'
 242              . (($cfgRelation['mimework'] == TRUE) ? $hit : sprintf($shit, 'col_com'))
 243              . '</td></tr>' . "\n";
 244
 245              if (($cfgRelation['commwork'] == TRUE) && ($cfgRelation['mimework'] != TRUE)) {
 246                  echo '<tr><td colspan=2 align="left">' . $GLOBALS['strUpdComTab'] . '</td></tr>' . "\n";
 247              }
 248
 249         echo '    <tr><th align="left">$cfg[\'Servers\'][$i][\'history\'] ... </th><td align="right">'
 250              . ((isset($cfgRelation['history'])) ? $hit : sprintf($shit, 'history'))
 251              . '</td></tr>' . "\n";
 252         echo '    <tr><td colspan=2 align="center">' . $GLOBALS['strQuerySQLHistory'] . ': '
 253              . (($cfgRelation['historywork'] == TRUE) ? $enabled : $disabled)
 254              . '</td></tr>' . "\n";
 255
 256         echo '</table>' . "\n";
 257     } // end if ($verbose == TRUE) {
 258
 259     return $cfgRelation;
 260 } // end of the 'PMA_getRelationsParam()' function
 261
 262
 263 /**
 264  * Gets all Relations to foreign tables for a given table or
 265  * optionally a given column in a table
 266  *
 267  * @param   string   the name of the db to check for
 268  * @param   string   the name of the table to check for
 269  * @param   string   the name of the column to check for
 270  * @param   string   the source for foreign key information
 271  *
 272  * @return  array    db,table,column
 273  *
 274  * @global  array    the list of relations settings
 275  * @global  string   the URL of the page to show in case of error
 276  *
 277  * @access  public
 278  *
 279  * @author  Mike Beck <mikebeck@users.sourceforge.net> and Marc Delisle
 280  */
 281 function PMA_getForeigners($db, $table, $column = '', $source = 'both') {
 282     global $cfgRelation, $err_url_0;
 283
 284     if ($cfgRelation['relwork'] && ($source == 'both' || $source == 'internal')) {
 285         $rel_query          = 'SELECT master_field, foreign_db, foreign_table, foreign_field'
 286                             . ' FROM ' . PMA_backquote($cfgRelation['relation'])
 287                             . ' WHERE master_db =  \'' . PMA_sqlAddslashes($db) . '\' '
 288                             . ' AND   master_table = \'' . PMA_sqlAddslashes($table) . '\' ';
 289         if (!empty($column)) {
 290             $rel_query .= ' AND   master_field = \'' . PMA_sqlAddslashes($column) . '\'';
 291         }
 292         $relations     = PMA_query_as_cu($rel_query);
 293         $i = 0;
 294         while ($relrow = PMA_DBI_fetch_assoc($relations)) {
 295             $field                            = $relrow['master_field'];
 296             $foreign[$field]['foreign_db']    = $relrow['foreign_db'];
 297             $foreign[$field]['foreign_table'] = $relrow['foreign_table'];
 298             $foreign[$field]['foreign_field'] = $relrow['foreign_field'];
 299             $i++;
 300         } // end while
 301         PMA_DBI_free_result($relations);
 302         unset($relations);
 303     }
 304
 305     if (($source == 'both' || $source == 'innodb') && !empty($table)) {
 306         $show_create_table_query = 'SHOW CREATE TABLE '
 307             . PMA_backquote($db) . '.' . PMA_backquote($table);
 308         $show_create_table_res = PMA_DBI_query($show_create_table_query);
 309         list(,$show_create_table) = PMA_DBI_fetch_row($show_create_table_res);
 310         PMA_DBI_free_result($show_create_table_res);
 311         unset($show_create_table_res, $show_create_table_query);
 312         $analyzed_sql = PMA_SQP_analyze(PMA_SQP_parse($show_create_table));
 313
 314         foreach ($analyzed_sql[0]['foreign_keys'] AS $one_key) {
 315
 316         // the analyzer may return more than one column name in the
 317         // index list or the ref_index_list
 318             foreach ($one_key['index_list'] AS $i => $field) {
 319
 320         // If a foreign key is defined in the 'internal' source (pmadb)
 321         // and in 'innodb', we won't get it twice if $source='both'
 322         // because we use $field as key
 323
 324                 // The parser looks for a CONSTRAINT clause just before
 325                 // the FOREIGN KEY clause. It finds it (as output from
 326                 // SHOW CREATE TABLE) in MySQL 4.0.13, but not in older
 327                 // versions like 3.23.58.
 328                 // In those cases, the FOREIGN KEY parsing will put numbers
 329                 // like -1, 0, 1... instead of the constraint number.
 330
 331                 if (isset($one_key['constraint'])) {
 332                     $foreign[$field]['constraint'] = $one_key['constraint'];
 333                 }
 334
 335                 if (isset($one_key['ref_db_name'])) {
 336                     $foreign[$field]['foreign_db']    = $one_key['ref_db_name'];
 337                 } else {
 338                     $foreign[$field]['foreign_db']    = $db;
 339                 }
 340                 $foreign[$field]['foreign_table'] = $one_key['ref_table_name'];
 341                 $foreign[$field]['foreign_field'] = $one_key['ref_index_list'][$i];
 342                 if (isset($one_key['on_delete'])) {
 343                     $foreign[$field]['on_delete'] = $one_key['on_delete'];
 344                 }
 345                 if (isset($one_key['on_update'])) {
 346                     $foreign[$field]['on_update'] = $one_key['on_update'];
 347                 }
 348             }
 349         }
 350     }
 351
 352     /**
 353      * Emulating relations for some information_schema tables
 354      */
 355     if (PMA_MYSQL_INT_VERSION >= 50002 && $db == 'information_schema'
 356         && ($source == 'internal' || $source == 'both')) {
 357
 358         require_once('./libraries/information_schema_relations.lib.php');
 359
 360         if (!isset($foreign)) {
 361             $foreign = array();
 362         }
 363
 364         if (isset($GLOBALS['information_schema_relations'][$table])) {
 365             foreach ($GLOBALS['information_schema_relations'][$table] as $field => $relations) {
 366                 if ((empty($column) || $column == $field) && empty($foreign[$field])) {
 367                     $foreign[$field] = $relations;
 368                 }
 369             }
 370         }
 371     }
 372
 373     if (!empty($foreign) && is_array($foreign)) {
 374        return $foreign;
 375     } else {
 376         return FALSE;
 377     }
 378
 379 } // end of the 'PMA_getForeigners()' function
 380
 381
 382 /**
 383  * Gets the display field of a table
 384  *
 385  * @param   string   the name of the db to check for
 386  * @param   string   the name of the table to check for
 387  *
 388  * @return  string   field name
 389  *
 390  * @global  array    the list of relations settings
 391  *
 392  * @access  public
 393  *
 394  * @author  Mike Beck <mikebeck@users.sourceforge.net>
 395  */
 396 function PMA_getDisplayField($db, $table) {
 397     global $cfgRelation;
 398
 399     /**
 400      * Try to fetch the display field from DB.
 401      */
 402     if (trim(@$cfgRelation['table_info']) != '') {
 403
 404         $disp_query = 'SELECT display_field FROM ' . PMA_backquote($cfgRelation['table_info'])
 405                     . ' WHERE db_name    = \'' . PMA_sqlAddslashes($db) . '\''
 406                     . ' AND   table_name = \'' . PMA_sqlAddslashes($table) . '\'';
 407
 408         $disp_res   = PMA_query_as_cu($disp_query);
 409         $row        = ($disp_res ? PMA_DBI_fetch_assoc($disp_res) : '');
 410         PMA_DBI_free_result($disp_res);
 411         if (isset($row['display_field'])) {
 412             return $row['display_field'];
 413         }
 414
 415     }
 416
 417     /**
 418      * Emulating the display field for some information_schema tables.
 419      */
 420     if (PMA_MYSQL_INT_VERSION >= 50002 && $db == 'information_schema') {
 421         switch ($table) {
 422             case 'CHARACTER_SETS': return 'DESCRIPTION';
 423             case 'TABLES':         return 'TABLE_COMMENT';
 424         }
 425     }
 426
 427     /**
 428      * No Luck...
 429      */
 430     return FALSE;
 431
 432 } // end of the 'PMA_getDisplayField()' function
 433
 434
 435 /**
 436  * Gets the comments for all rows of a table
 437  *
 438  * @param   string   the name of the db to check for
 439  * @param   string   the name of the table to check for
 440  *
 441  * @return  array    [field_name] = comment
 442  *
 443  * @global  array    the list of relations settings
 444  *
 445  * @access  public
 446  *
 447  * @authors  Mike Beck <mikebeck@users.sourceforge.net>
 448  *           and lem9
 449  */
 450 function PMA_getComments($db, $table = '') {
 451     global $cfgRelation;
 452
 453     if ($table != '') {
 454
 455         // MySQL 4.1.x native column comments
 456         if (PMA_MYSQL_INT_VERSION >= 40100) {
 457             $fields = PMA_DBI_get_fields($db, $table);
 458             if ($fields) {
 459                 foreach($fields as $key=>$field) {
 460                     $tmp_col = $field['Field'];
 461                     if (!empty($field['Comment'])) {
 462                         $native_comment[$tmp_col] = $field['Comment'];
 463                     }
 464                 }
 465                 if (isset($native_comment)) {
 466                     $comment = $native_comment;
 467                 }
 468             }
 469         }
 470
 471         // pmadb internal column comments
 472         // (this function can be called even if $cfgRelation['commwork'] is
 473         // FALSE, to get native column comments, so recheck here)
 474         if ($cfgRelation['commwork']) {
 475             $com_qry = 'SELECT column_name, comment FROM ' . PMA_backquote($cfgRelation['db']) . '.' .PMA_backquote($cfgRelation['column_info'])
 476                      . ' WHERE db_name    = \'' . PMA_sqlAddslashes($db) . '\''
 477                      . ' AND   table_name = \'' . PMA_sqlAddslashes($table) . '\'';
 478             $com_rs   = PMA_query_as_cu($com_qry, TRUE, PMA_DBI_QUERY_STORE);
 479         }
 480     } else {
 481         // pmadb internal db comments
 482         $com_qry = 'SELECT ' . PMA_backquote('comment') . ' FROM ' . PMA_backquote($cfgRelation['db']) . '.' . PMA_backquote($cfgRelation['column_info'])
 483                  . ' WHERE db_name     = \'' . PMA_sqlAddslashes($db) . '\''
 484                  . ' AND   table_name  = \'\''
 485                  . ' AND   column_name = \'(db_comment)\'';
 486         $com_rs   = PMA_query_as_cu($com_qry, TRUE, PMA_DBI_QUERY_STORE);
 487     }
 488
 489
 490     if (isset($com_rs) && PMA_DBI_num_rows($com_rs) > 0) {
 491         $i = 0;
 492         while ($row = PMA_DBI_fetch_assoc($com_rs)) {
 493             $i++;
 494             $col           = ($table != '' ? $row['column_name'] : $i);
 495
 496             if (strlen($row['comment']) > 0) {
 497                 $comment[$col] = $row['comment'];
 498                 // if this version supports native comments and this function
 499                 // was called with a table parameter
 500                 if (PMA_MYSQL_INT_VERSION >= 40100 && !empty($table)) {
 501                     // if native comment found, use it instead of pmadb
 502                     if (!empty($native_comment[$col])) {
 503                         $comment[$col] = $native_comment[$col];
 504                     } else {
 505                         // no native comment, so migrate pmadb-style to native
 506                         PMA_setComment($db, $table, $col, $comment[$col],'','native');
 507                         // and erase the pmadb-style comment
 508                         PMA_setComment($db, $table, $col, '','','pmadb');
 509                     }
 510                 }
 511             }
 512         } // end while
 513
 514         PMA_DBI_free_result($com_rs);
 515         unset($com_rs);
 516     }
 517
 518     if (isset($comment) && is_array($comment)) {
 519         return $comment;
 520      } else {
 521         return FALSE;
 522      }
 523  } // end of the 'PMA_getComments()' function
 524
 525 /**
 526  * Adds/removes slashes if required
 527  *
 528  * @param   string  the string to slash
 529  *
 530  * @return  string  the slashed string
 531  *
 532  * @access  public
 533  */
 534 function PMA_handleSlashes($val) {
 535   return (get_magic_quotes_gpc() ? str_replace('\\"', '"', $val) : PMA_sqlAddslashes($val));
 536 } // end of the "PMA_handleSlashes()" function
 537
 538 /**
 539  * Set a single comment to a certain value.
 540  *
 541  * @param   string   the name of the db
 542  * @param   string   the name of the table (may be empty in case of a db comment)
 543  * @param   string   the name of the column
 544  * @param   string   the value of the column
 545  * @param   string   (optional) if a column is renamed, this is the name of the former key which will get deleted
 546  * @param   string   whether we set pmadb comments, native comments or both
 547  *
 548  * @return  boolean  true, if comment-query was made.
 549  *
 550  * @global  array    the list of relations settings
 551  *
 552  * @access  public
 553  */
 554 function PMA_setComment($db, $table, $col, $comment, $removekey = '', $mode='auto') {
 555     global $cfgRelation;
 556
 557     if ($mode=='auto') {
 558         if (PMA_MYSQL_INT_VERSION >= 40100) {
 559             $mode='native';
 560         } else {
 561             $mode='pmadb';
 562         }
 563     }
 564
 565     // native mode is only for column comments so we need a table name
 566     if ($mode == 'native' && !empty($table)) {
 567         $query = 'ALTER TABLE ' . PMA_backquote($table) . ' CHANGE '
 568             . PMA_generateAlterTable($col, $col, '', '', '', '', FALSE, '', FALSE, '', $comment, '', '');
 569         PMA_DBI_try_query($query, NULL, PMA_DBI_QUERY_STORE);
 570         return TRUE;
 571     }
 572
 573     // $mode == 'pmadb' section:
 574
 575     $cols = array(
 576         'db_name'     => 'db_name    ',
 577         'table_name'  => 'table_name ',
 578         'column_name' => 'column_name'
 579     );
 580
 581     if ($removekey != '' AND $removekey != $col) {
 582         $remove_query = 'DELETE FROM ' . PMA_backquote($cfgRelation['column_info'])
 583                       . ' WHERE ' . $cols['db_name']     . ' = \'' . PMA_sqlAddslashes($db) . '\''
 584                       . ' AND   ' . $cols['table_name']  . ' = \'' . PMA_sqlAddslashes($table) . '\''
 585                       . ' AND   ' . $cols['column_name'] . ' = \'' . PMA_sqlAddslashes($removekey) . '\'';
 586         PMA_query_as_cu($remove_query);
 587         unset($remove_query);
 588     }
 589
 590     $test_qry = 'SELECT ' . PMA_backquote('comment') . ', mimetype, transformation, transformation_options FROM ' . PMA_backquote($cfgRelation['column_info'])
 591               . ' WHERE ' . $cols['db_name']     . ' = \'' . PMA_sqlAddslashes($db) . '\''
 592               . ' AND   ' . $cols['table_name']  . ' = \'' . PMA_sqlAddslashes($table) . '\''
 593               . ' AND   ' . $cols['column_name'] . ' = \'' . PMA_sqlAddslashes($col) . '\'';
 594     $test_rs   = PMA_query_as_cu($test_qry, TRUE, PMA_DBI_QUERY_STORE);
 595
 596     if ($test_rs && PMA_DBI_num_rows($test_rs) > 0) {
 597         $row = PMA_DBI_fetch_assoc($test_rs);
 598         PMA_DBI_free_result($test_rs);
 599
 600         if (strlen($comment) > 0 || strlen($row['mimetype']) > 0 || strlen($row['transformation']) > 0 || strlen($row['transformation_options']) > 0) {
 601             $upd_query = 'UPDATE ' . PMA_backquote($cfgRelation['column_info'])
 602                        . ' SET ' . PMA_backquote('comment') . ' = \'' . PMA_sqlAddslashes($comment) . '\''
 603                        . ' WHERE ' . $cols['db_name']     . ' = \'' . PMA_sqlAddslashes($db) . '\''
 604                        . ' AND   ' . $cols['table_name']  . ' = \'' . PMA_sqlAddslashes($table) . '\''
 605                        . ' AND   ' . $cols['column_name'] . ' = \'' . PMA_sqlAddSlashes($col) . '\'';
 606         } else {
 607             $upd_query = 'DELETE FROM ' . PMA_backquote($cfgRelation['column_info'])
 608                        . ' WHERE ' . $cols['db_name']     . ' = \'' . PMA_sqlAddslashes($db) . '\''
 609                        . ' AND   ' . $cols['table_name']  . ' = \'' . PMA_sqlAddslashes($table) . '\''
 610                        . ' AND   ' . $cols['column_name'] . ' = \'' . PMA_sqlAddslashes($col) . '\'';
 611         }
 612     } else if (strlen($comment) > 0) {
 613         $upd_query = 'INSERT INTO ' . PMA_backquote($cfgRelation['column_info'])
 614                    . ' (db_name, table_name, column_name, ' . PMA_backquote('comment') . ') '
 615                    . ' VALUES('
 616                    . '\'' . PMA_sqlAddslashes($db) . '\','
 617                    . '\'' . PMA_sqlAddslashes($table) . '\','
 618                    . '\'' . PMA_sqlAddslashes($col) . '\','
 619                    . '\'' . PMA_sqlAddslashes($comment) . '\')';
 620     }
 621
 622     if (isset($upd_query)){
 623         $upd_rs    = PMA_query_as_cu($upd_query);
 624         unset($upd_query);
 625         return true;
 626     } else {
 627         return false;
 628     }
 629 } // end of 'PMA_setComment()' function
 630
 631 /**
 632  * Set a SQL history entry
 633  *
 634  * @param   string   the name of the db
 635  * @param   string   the name of the table
 636  * @param   string   the username
 637  * @param   string   the sql query
 638  *
 639  * @global  array    the list of relations settings
 640  *
 641  * @return  boolean  true
 642  *
 643  * @access  public
 644  */
 645 function PMA_setHistory($db, $table, $username, $sqlquery) {
 646     global $cfgRelation;
 647
 648     $hist_rs    = PMA_query_as_cu('INSERT INTO ' . PMA_backquote($cfgRelation['history']) . ' ('
 649                 . PMA_backquote('username') . ','
 650                 . PMA_backquote('db') . ','
 651                 . PMA_backquote('table') . ','
 652                 . PMA_backquote('timevalue') . ','
 653                 . PMA_backquote('sqlquery')
 654                 . ') VALUES ('
 655                 . '\'' . PMA_sqlAddslashes($username) . '\','
 656                 . '\'' . PMA_sqlAddslashes($db) . '\','
 657                 . '\'' . PMA_sqlAddslashes($table) . '\','
 658                 . 'NOW(),'
 659                 . '\'' . PMA_sqlAddslashes($sqlquery) . '\')');
 660     return true;
 661 } // end of 'PMA_setHistory()' function
 662
 663 /**
 664  * Gets a SQL history entry
 665  *
 666  * @param   string   the username
 667  *
 668  * @global  array    the list of relations settings
 669  *
 670  * @return  array    list of history items
 671  *
 672  * @access  public
 673  */
 674 function PMA_getHistory($username) {
 675     global $cfgRelation;
 676
 677     $hist_query = 'SELECT '
 678                 . PMA_backquote('db') . ','
 679                 . PMA_backquote('table') . ','
 680                 . PMA_backquote('sqlquery')
 681                 . ' FROM ' . PMA_backquote($cfgRelation['history'])
 682                 . ' WHERE username = \'' . PMA_sqlAddslashes($username) . '\''
 683                 . ' ORDER BY id DESC';
 684
 685     $hist_rs = PMA_query_as_cu($hist_query);
 686     unset($hist_query);
 687
 688     $history = array();
 689
 690     while ($row = PMA_DBI_fetch_assoc($hist_rs)) {
 691         $history[] = $row;
 692     }
 693     PMA_DBI_free_result($hist_rs);
 694
 695     return $history;
 696
 697 } // end of 'PMA_getHistory()' function
 698
 699 /**
 700  * Set a SQL history entry
 701  *
 702  * @param   string   the name of the db
 703  * @param   string   the name of the table
 704  * @param   string   the username
 705  * @param   string   the sql query
 706  *
 707  * @global  array    the list of relations settings
 708  * @global  array    global phpMyAdmin configuration
 709  *
 710  * @return  boolean  true
 711  *
 712  * @access  public
 713  */
 714 function PMA_purgeHistory($username) {
 715     global $cfgRelation, $cfg;
 716
 717     $purge_query = 'SELECT timevalue FROM ' . PMA_backquote($cfgRelation['history'])
 718                  . ' WHERE username = \'' . PMA_sqlAddSlashes($username) . '\''
 719                  . ' ORDER BY timevalue DESC LIMIT ' . $cfg['QueryHistoryMax'] . ', 1';
 720     $purge_rs = PMA_query_as_cu($purge_query);
 721     $i = 0;
 722     $row = PMA_DBI_fetch_row($purge_rs);
 723     PMA_DBI_free_result($purge_rs);
 724
 725     if (is_array($row) && isset($row[0]) && $row[0] > 0) {
 726         $maxtime = $row[0];
 727         // quotes added around $maxtime to prevent a difficult to
 728         // reproduce problem
 729         $remove_rs = PMA_query_as_cu('DELETE FROM ' . PMA_backquote($cfgRelation['history']) . ' WHERE timevalue <= "' . $maxtime . '"');
 730     }
 731
 732     return true;
 733 } // end of 'PMA_purgeHistory()' function
 734
 735
 736 /**
 737  * Prepares the dropdown for one mode
 738  *
 739  * @param   array    the keys and values for foreigns
 740  * @param   string   the current data of the dropdown
 741  * @param   string   the needed mode
 742  *
 743  * @global  array    global phpMyAdmin configuration
 744  *
 745  * @return  array   the <option value=""><option>s
 746  *
 747  * @access  private
 748  */
 749 function PMA_foreignDropdownBuild($foreign, $data, $mode) {
 750     global $cfg;
 751
 752     $reloptions = array();
 753
 754     foreach ($foreign as $key => $value) {
 755
 756         if (PMA_strlen($value) <= $cfg['LimitChars']) {
 757             $vtitle = '';
 758             $value  = htmlspecialchars($value);
 759         } else {
 760             $vtitle  = htmlspecialchars($value);
 761             $value  = htmlspecialchars(substr($value, 0, $cfg['LimitChars']) . '...');
 762         }
 763
 764         $reloption = '                <option value="' . htmlspecialchars($key) . '"';
 765         if ($vtitle != '') {
 766             $reloption .= ' title="' . $vtitle . '"';
 767         }
 768
 769         if ($key == $data) {
 770            $reloption .= ' selected="selected"';
 771         }
 772
 773         if ($mode == 'content-id') {
 774             $reloptions[] = $reloption . '>' . $value . '&nbsp;-&nbsp;' . htmlspecialchars($key) .  '</option>' . "\n";
 775         } else {
 776             $reloptions[] = $reloption . '>' . htmlspecialchars($key) .  '&nbsp;-&nbsp;' . $value . '</option>' . "\n";
 777         }
 778     } // end foreach
 779
 780     return $reloptions;
 781 } // end of 'PMA_foreignDropdownBuild' function
 782
 783 /**
 784  * Outputs dropdown with values of foreign fields
 785  *
 786  * @param   string   the query of the foreign keys
 787  * @param   string   the foreign field
 788  * @param   string   the foreign field to display
 789  * @param   string   the current data of the dropdown
 790  *
 791  * @global  array    global phpMyAdmin configuration
 792  *
 793  * @return  string   the <option value=""><option>s
 794  *
 795  * @access  public
 796  */
 797 function PMA_foreignDropdown($disp, $foreign_field, $foreign_display, $data, $max) {
 798     global $cfg;
 799
 800     // collect the data
 801     foreach ($disp as $relrow) {
 802         $key   = $relrow[$foreign_field];
 803
 804         // if the display field has been defined for this foreign table
 805         if ($foreign_display) {
 806             $value  = $relrow[$foreign_display];
 807         } else {
 808             $value = '';
 809         } // end if ($foreign_display)
 810
 811         $foreign[$key] = $value;
 812     } // end foreach
 813
 814     // beginning of dropdown
 815     $ret = '<option value=""></option>' . "\n";
 816
 817     // master array for dropdowns
 818     $reloptions = array('content-id' => array(), 'id-content' => array());
 819
 820     // sort for id-content
 821     if ($cfg['NaturalOrder']) {
 822         uksort($foreign, 'strnatcasecmp');
 823     } else {
 824         ksort($foreign);
 825     }
 826
 827     // build id-content dropdown
 828     $reloptions['id-content'] = PMA_foreignDropdownBuild($foreign, $data, 'id-content');
 829
 830     // sort for content-id
 831     if ($cfg['NaturalOrder']) {
 832         natcasesort($foreign);
 833     } else {
 834         asort($foreign);
 835     }
 836
 837     // build content-id dropdown
 838     $reloptions['content-id'] = PMA_foreignDropdownBuild($foreign, $data, 'content-id');
 839
 840
 841     // put the dropdown sections in correct order
 842
 843     $c = count($cfg['ForeignKeyDropdownOrder']);
 844     if($c == 2) {
 845         $top = $reloptions[$cfg['ForeignKeyDropdownOrder'][0]];
 846         $bot = $reloptions[$cfg['ForeignKeyDropdownOrder'][1]];
 847     } elseif($c == 1) {
 848         $bot = $reloptions[$cfg['ForeignKeyDropdownOrder'][0]];
 849         $top = NULL;
 850     } else {
 851         $top = $reloptions['id-content'];
 852         $bot = $reloptions['content-id'];
 853     }
 854     $str_bot = implode('', $bot);
 855     if($top !== NULL) {
 856         $str_top = implode('', $top);
 857         $top_count = count($top);
 858         if ($max == -1 || $top_count < $max) {
 859             $ret .= $str_top;
 860             if ($top_count > 0) {
 861                 $ret .= '                <option value=""></option>' . "\n";
 862                 $ret .= '                <option value=""></option>' . "\n";
 863             }
 864         }
 865     }
 866     $ret .= $str_bot;
 867
 868     return $ret;
 869 } // end of 'PMA_foreignDropdown()' function
 870
 871 ?>