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