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