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