libraries/relation.lib.php

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