libraries/database_interface.lib.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Common Option Constants For DBI Functions
   5  *
   6  * @version $Id$
   7  * @package phpMyAdmin
   8  */
   9 if (! defined('PHPMYADMIN')) {
  10     exit;
  11 }
  12
  13 /**
  14  *
  15  */
  16 // PMA_DBI_try_query()
  17 define('PMA_DBI_QUERY_STORE',       1);  // Force STORE_RESULT method, ignored by classic MySQL.
  18 define('PMA_DBI_QUERY_UNBUFFERED',  2);  // Do not read whole query
  19 // PMA_DBI_get_variable()
  20 define('PMA_DBI_GETVAR_SESSION',    1);
  21 define('PMA_DBI_GETVAR_GLOBAL',     2);
  22
  23 /**
  24  * Checks one of the mysql extensions
  25  *
  26  * @param   string  $extension  mysql extension to check
  27  */
  28 function PMA_DBI_checkMysqlExtension($extension = 'mysql') {
  29     if (! function_exists($extension . '_connect')) {
  30         return false;
  31     }
  32
  33     return true;
  34 }
  35
  36 /**
  37  * check for requested extension
  38  */
  39 if (! PMA_DBI_checkMysqlExtension($GLOBALS['cfg']['Server']['extension'])) {
  40
  41     // if it fails try alternative extension ...
  42     // and display an error ...
  43
  44     /**
  45      * @todo add different messages for alternative extension
  46      * and complete fail (no alternative extension too)
  47      */
  48     PMA_warnMissingExtension($GLOBALS['cfg']['Server']['extension'], false, PMA_showDocu('faqmysql'));
  49
  50     if ($GLOBALS['cfg']['Server']['extension'] === 'mysql') {
  51         $alternativ_extension = 'mysqli';
  52     } else {
  53         $alternativ_extension = 'mysql';
  54     }
  55
  56     if (! PMA_DBI_checkMysqlExtension($alternativ_extension)) {
  57         // if alternative fails too ...
  58         PMA_warnMissingExtension($GLOBALS['cfg']['Server']['extension'], true, PMA_showDocu('faqmysql'));
  59     }
  60
  61     $GLOBALS['cfg']['Server']['extension'] = $alternativ_extension;
  62     unset($alternativ_extension);
  63 }
  64
  65 /**
  66  * Including The DBI Plugin
  67  */
  68 require_once './libraries/dbi/' . $GLOBALS['cfg']['Server']['extension'] . '.dbi.lib.php';
  69
  70 /**
  71  * Common Functions
  72  */
  73 function PMA_DBI_query($query, $link = null, $options = 0) {
  74     $res = PMA_DBI_try_query($query, $link, $options)
  75         or PMA_mysqlDie(PMA_DBI_getError($link), $query);
  76     return $res;
  77 }
  78
  79 /**
  80  * converts charset of a mysql message, usually coming from mysql_error(),
  81  * into PMA charset, usally UTF-8
  82  * uses language to charset mapping from mysql/share/errmsg.txt
  83  * and charset names to ISO charset from information_schema.CHARACTER_SETS
  84  *
  85  * @uses    $GLOBALS['cfg']['IconvExtraParams']
  86  * @uses    $GLOBALS['charset']     as target charset
  87  * @uses    PMA_DBI_fetch_value()   to get server_language
  88  * @uses    preg_match()            to filter server_language
  89  * @uses    in_array()
  90  * @uses    function_exists()       to check for a convert function
  91  * @uses    iconv()                 to convert message
  92  * @uses    libiconv()              to convert message
  93  * @uses    recode_string()         to convert message
  94  * @uses    mb_convert_encoding()   to convert message
  95  * @param   string  $message
  96  * @return  string  $message
  97  */
  98 function PMA_DBI_convert_message($message) {
  99     // latin always last!
 100     $encodings = array(
 101         'japanese'      => 'EUC-JP', //'ujis',
 102         'japanese-sjis' => 'Shift-JIS', //'sjis',
 103         'korean'        => 'EUC-KR', //'euckr',
 104         'russian'       => 'KOI8-R', //'koi8r',
 105         'ukrainian'     => 'KOI8-U', //'koi8u',
 106         'greek'         => 'ISO-8859-7', //'greek',
 107         'serbian'       => 'CP1250', //'cp1250',
 108         'estonian'      => 'ISO-8859-13', //'latin7',
 109         'slovak'        => 'ISO-8859-2', //'latin2',
 110         'czech'         => 'ISO-8859-2', //'latin2',
 111         'hungarian'     => 'ISO-8859-2', //'latin2',
 112         'polish'        => 'ISO-8859-2', //'latin2',
 113         'romanian'      => 'ISO-8859-2', //'latin2',
 114         'spanish'       => 'CP1252', //'latin1',
 115         'swedish'       => 'CP1252', //'latin1',
 116         'italian'       => 'CP1252', //'latin1',
 117         'norwegian-ny'  => 'CP1252', //'latin1',
 118         'norwegian'     => 'CP1252', //'latin1',
 119         'portuguese'    => 'CP1252', //'latin1',
 120         'danish'        => 'CP1252', //'latin1',
 121         'dutch'         => 'CP1252', //'latin1',
 122         'english'       => 'CP1252', //'latin1',
 123         'french'        => 'CP1252', //'latin1',
 124         'german'        => 'CP1252', //'latin1',
 125     );
 126
 127     if ($server_language = PMA_DBI_fetch_value('SHOW VARIABLES LIKE \'language\';', 0, 1)) {
 128         $found = array();
 129         if (preg_match('&(?:\\\|\\/)([^\\\\\/]*)(?:\\\|\\/)$&i', $server_language, $found)) {
 130             $server_language = $found[1];
 131         }
 132     }
 133
 134     if (! empty($server_language) && isset($encodings[$server_language])) {
 135         if (function_exists('iconv')) {
 136             if ((@stristr(PHP_OS, 'AIX')) && (@strcasecmp(ICONV_IMPL, 'unknown') == 0) && (@strcasecmp(ICONV_VERSION, 'unknown') == 0)) {
 137                 require_once './libraries/iconv_wrapper.lib.php';
 138                 $message = PMA_aix_iconv_wrapper($encodings[$server_language],
 139                     $GLOBALS['charset'] . $GLOBALS['cfg']['IconvExtraParams'], $message);
 140             } else {
 141                 $message = iconv($encodings[$server_language],
 142                     $GLOBALS['charset'] . $GLOBALS['cfg']['IconvExtraParams'], $message);
 143             }
 144         } elseif (function_exists('recode_string')) {
 145             $message = recode_string($encodings[$server_language] . '..'  . $GLOBALS['charset'],
 146                 $message);
 147         } elseif (function_exists('libiconv')) {
 148             $message = libiconv($encodings[$server_language], $GLOBALS['charset'], $message);
 149         } elseif (function_exists('mb_convert_encoding')) {
 150             // do not try unsupported charsets
 151             if (! in_array($server_language, array('ukrainian', 'greek', 'serbian'))) {
 152                 $message = mb_convert_encoding($message, $GLOBALS['charset'],
 153                     $encodings[$server_language]);
 154             }
 155         }
 156     } else {
 157         /**
 158          * @todo lang not found, try all, what TODO ?
 159          */
 160     }
 161
 162     return $message;
 163 }
 164
 165 /**
 166  * returns array with table names for given db
 167  *
 168  * @param   string  $database   name of database
 169  * @param   mixed   $link       mysql link resource|object
 170  * @return  array   tables names
 171  */
 172 function PMA_DBI_get_tables($database, $link = null)
 173 {
 174     return PMA_DBI_fetch_result('SHOW TABLES FROM ' . PMA_backquote($database) . ';',
 175         null, 0, $link, PMA_DBI_QUERY_STORE);
 176 }
 177
 178 /**
 179  * usort comparison callback
 180  *
 181  * @param   string  $a first argument to sort
 182  * @param   string  $b second argument to sort
 183  *
 184  * @return  integer  a value representing whether $a should be before $b in the
 185  *                   sorted array or not
 186  *
 187  * @global  string   the column the array shall be sorted by
 188  * @global  string   the sorting order ('ASC' or 'DESC')
 189  *
 190  * @access  private
 191  */
 192 function PMA_usort_comparison_callback($a, $b)
 193 {
 194     if ($GLOBALS['cfg']['NaturalOrder']) {
 195         $sorter = 'strnatcasecmp';
 196     } else {
 197         $sorter = 'strcasecmp';
 198     }
 199     // produces f.e.:
 200     // return -1 * strnatcasecmp($a["SCHEMA_TABLES"], $b["SCHEMA_TABLES"])
 201     return ($GLOBALS['callback_sort_order'] == 'ASC' ? 1 : -1) * $sorter($a[$GLOBALS['callback_sort_by']], $b[$GLOBALS['callback_sort_by']]);
 202 } // end of the 'PMA_usort_comparison_callback()' function
 203
 204 /**
 205  * returns array of all tables in given db or dbs
 206  * this function expects unquoted names:
 207  * RIGHT: my_database
 208  * WRONG: `my_database`
 209  * WRONG: my\_database
 210  * if $tbl_is_group is true, $table is used as filter for table names
 211  * if $tbl_is_group is 'comment, $table is used as filter for table comments
 212  *
 213  * <code>
 214  * PMA_DBI_get_tables_full('my_database');
 215  * PMA_DBI_get_tables_full('my_database', 'my_table'));
 216  * PMA_DBI_get_tables_full('my_database', 'my_tables_', true));
 217  * PMA_DBI_get_tables_full('my_database', 'my_tables_', 'comment'));
 218  * </code>
 219  *
 220  * @todo    move into PMA_Table
 221  * @uses    PMA_DBI_fetch_result()
 222  * @uses    PMA_escape_mysql_wildcards()
 223  * @uses    PMA_backquote()
 224  * @uses    is_array()
 225  * @uses    addslashes()
 226  * @uses    strpos()
 227  * @uses    strtoupper()
 228  * @param   string          $databases      database
 229  * @param   string          $table          table
 230  * @param   boolean|string  $tbl_is_group   $table is a table group
 231  * @param   resource        $link           mysql link
 232  * @param   integer         $limit_offset   zero-based offset for the count
 233  * @param   boolean|integer $limit_count    number of tables to return
 234  * @param   string          $sort_by        table attribute to sort by
 235  * @param   string          $sort_order     direction to sort (ASC or DESC)
 236  * @return  array           list of tables in given db(s)
 237  */
 238 function PMA_DBI_get_tables_full($database, $table = false, $tbl_is_group = false, $link = null,
 239      $limit_offset = 0, $limit_count = false, $sort_by = 'Name', $sort_order = 'ASC')
 240 {
 241     require_once './libraries/Table.class.php';
 242
 243     if (true === $limit_count) {
 244         $limit_count = $GLOBALS['cfg']['MaxTableList'];
 245     }
 246     // prepare and check parameters
 247     if (! is_array($database)) {
 248         $databases = array($database);
 249     } else {
 250         $databases = $database;
 251     }
 252
 253     $tables = array();
 254
 255     if (! $GLOBALS['cfg']['Server']['DisableIS']) {
 256       // get table information from information_schema
 257         if ($table) {
 258             if (true === $tbl_is_group) {
 259                 $sql_where_table = 'AND `TABLE_NAME` LIKE \''
 260                   . PMA_escape_mysql_wildcards(addslashes($table)) . '%\'';
 261             } elseif ('comment' === $tbl_is_group) {
 262                 $sql_where_table = 'AND `TABLE_COMMENT` LIKE \''
 263                   . PMA_escape_mysql_wildcards(addslashes($table)) . '%\'';
 264             } else {
 265                 $sql_where_table = 'AND `TABLE_NAME` = \'' . addslashes($table) . '\'';
 266             }
 267         } else {
 268             $sql_where_table = '';
 269         }
 270
 271       // for PMA bc:
 272       // `SCHEMA_FIELD_NAME` AS `SHOW_TABLE_STATUS_FIELD_NAME`
 273       //
 274       // on non-Windows servers,
 275       // added BINARY in the WHERE clause to force a case sensitive
 276       // comparison (if we are looking for the db Aa we don't want
 277       // to find the db aa)
 278         $this_databases = array_map('PMA_sqlAddslashes', $databases);
 279
 280         $sql = '
 281            SELECT *,
 282                   `TABLE_SCHEMA`       AS `Db`,
 283                   `TABLE_NAME`         AS `Name`,
 284                   `TABLE_TYPE`         ÀS `TABLE_TYPE`,
 285                   `ENGINE`             AS `Engine`,
 286                   `ENGINE`             AS `Type`,
 287                   `VERSION`            AS `Version`,
 288                   `ROW_FORMAT`         AS `Row_format`,
 289                   `TABLE_ROWS`         AS `Rows`,
 290                   `AVG_ROW_LENGTH`     AS `Avg_row_length`,
 291                   `DATA_LENGTH`        AS `Data_length`,
 292                   `MAX_DATA_LENGTH`    AS `Max_data_length`,
 293                   `INDEX_LENGTH`       AS `Index_length`,
 294                   `DATA_FREE`          AS `Data_free`,
 295                   `AUTO_INCREMENT`     AS `Auto_increment`,
 296                   `CREATE_TIME`        AS `Create_time`,
 297                   `UPDATE_TIME`        AS `Update_time`,
 298                   `CHECK_TIME`         AS `Check_time`,
 299                   `TABLE_COLLATION`    AS `Collation`,
 300                   `CHECKSUM`           AS `Checksum`,
 301                   `CREATE_OPTIONS`     AS `Create_options`,
 302                   `TABLE_COMMENT`      AS `Comment`
 303              FROM `information_schema`.`TABLES`
 304             WHERE ' . (PMA_IS_WINDOWS ? '' : 'BINARY') . ' `TABLE_SCHEMA` IN (\'' . implode("', '", $this_databases) . '\')
 305               ' . $sql_where_table;
 306
 307         // Sort the tables
 308         $sql .= " ORDER BY $sort_by $sort_order";
 309
 310         if ($limit_count) {
 311             $sql .= ' LIMIT ' . $limit_count . ' OFFSET ' . $limit_offset;
 312         }
 313
 314         $tables = PMA_DBI_fetch_result($sql, array('TABLE_SCHEMA', 'TABLE_NAME'),
 315           null, $link);
 316         unset($sql_where_table, $sql);
 317         if ($sort_by == 'Name' && $GLOBALS['cfg']['NaturalOrder']) {
 318             // here, the array's first key is by schema name
 319             foreach($tables as $one_database_name => $one_database_tables) {
 320                 uksort($one_database_tables, 'strnatcasecmp');
 321
 322                 if ($sort_order == 'DESC') {
 323                     $one_database_tables = array_reverse($one_database_tables);
 324                 }
 325                 $tables[$one_database_name] = $one_database_tables;
 326             }
 327         }
 328     } // end (get information from table schema)
 329
 330     // If permissions are wrong on even one database directory,
 331     // information_schema does not return any table info for any database
 332     // this is why we fall back to SHOW TABLE STATUS even for MySQL >= 50002
 333     if (empty($tables)) {
 334         foreach ($databases as $each_database) {
 335             if ($table || (true === $tbl_is_group)) {
 336                 $sql = 'SHOW TABLE STATUS FROM '
 337                     . PMA_backquote($each_database)
 338                     .' LIKE \'' . PMA_escape_mysql_wildcards(addslashes($table)) . '%\'';
 339             } else {
 340                 $sql = 'SHOW TABLE STATUS FROM '
 341                     . PMA_backquote($each_database);
 342             }
 343
 344             $each_tables = PMA_DBI_fetch_result($sql, 'Name', null, $link);
 345
 346             // Sort naturally if the config allows it and we're sorting
 347             // the Name column.
 348             if ($sort_by == 'Name' && $GLOBALS['cfg']['NaturalOrder']) {
 349                 uksort($each_tables, 'strnatcasecmp');
 350
 351                 if ($sort_order == 'DESC') {
 352                     $each_tables = array_reverse($each_tables);
 353                 }
 354             } else {
 355                 // Prepare to sort by creating array of the selected sort
 356                 // value to pass to array_multisort
 357                 foreach ($each_tables as $table_name => $table_data) {
 358                     ${$sort_by}[$table_name] = strtolower($table_data[$sort_by]);
 359                 }
 360
 361                 if ($sort_order == 'DESC') {
 362                     array_multisort($$sort_by, SORT_DESC, $each_tables);
 363                 } else {
 364                     array_multisort($$sort_by, SORT_ASC, $each_tables);
 365                 }
 366
 367                 // cleanup the temporary sort array
 368                 unset($$sort_by);
 369             }
 370
 371             if ($limit_count) {
 372                 $each_tables = array_slice($each_tables, $limit_offset, $limit_count);
 373             }
 374
 375             foreach ($each_tables as $table_name => $each_table) {
 376                 if ('comment' === $tbl_is_group
 377                   && 0 === strpos($each_table['Comment'], $table))
 378                 {
 379                     // remove table from list
 380                     unset($each_tables[$table_name]);
 381                     continue;
 382                 }
 383
 384                 if (! isset($each_tables[$table_name]['Type'])
 385                   && isset($each_tables[$table_name]['Engine'])) {
 386                     // pma BC, same parts of PMA still uses 'Type'
 387                     $each_tables[$table_name]['Type']
 388                         =& $each_tables[$table_name]['Engine'];
 389                 } elseif (! isset($each_tables[$table_name]['Engine'])
 390                   && isset($each_tables[$table_name]['Type'])) {
 391                     // old MySQL reports Type, newer MySQL reports Engine
 392                     $each_tables[$table_name]['Engine']
 393                         =& $each_tables[$table_name]['Type'];
 394                 }
 395
 396                 // MySQL forward compatibility
 397                 // so pma could use this array as if every server is of version >5.0
 398                 $each_tables[$table_name]['TABLE_SCHEMA']      = $each_database;
 399                 $each_tables[$table_name]['TABLE_NAME']        =& $each_tables[$table_name]['Name'];
 400                 $each_tables[$table_name]['ENGINE']            =& $each_tables[$table_name]['Engine'];
 401                 $each_tables[$table_name]['VERSION']           =& $each_tables[$table_name]['Version'];
 402                 $each_tables[$table_name]['ROW_FORMAT']        =& $each_tables[$table_name]['Row_format'];
 403                 $each_tables[$table_name]['TABLE_ROWS']        =& $each_tables[$table_name]['Rows'];
 404                 $each_tables[$table_name]['AVG_ROW_LENGTH']    =& $each_tables[$table_name]['Avg_row_length'];
 405                 $each_tables[$table_name]['DATA_LENGTH']       =& $each_tables[$table_name]['Data_length'];
 406                 $each_tables[$table_name]['MAX_DATA_LENGTH']   =& $each_tables[$table_name]['Max_data_length'];
 407                 $each_tables[$table_name]['INDEX_LENGTH']      =& $each_tables[$table_name]['Index_length'];
 408                 $each_tables[$table_name]['DATA_FREE']         =& $each_tables[$table_name]['Data_free'];
 409                 $each_tables[$table_name]['AUTO_INCREMENT']    =& $each_tables[$table_name]['Auto_increment'];
 410                 $each_tables[$table_name]['CREATE_TIME']       =& $each_tables[$table_name]['Create_time'];
 411                 $each_tables[$table_name]['UPDATE_TIME']       =& $each_tables[$table_name]['Update_time'];
 412                 $each_tables[$table_name]['CHECK_TIME']        =& $each_tables[$table_name]['Check_time'];
 413                 $each_tables[$table_name]['TABLE_COLLATION']   =& $each_tables[$table_name]['Collation'];
 414                 $each_tables[$table_name]['CHECKSUM']          =& $each_tables[$table_name]['Checksum'];
 415                 $each_tables[$table_name]['CREATE_OPTIONS']    =& $each_tables[$table_name]['Create_options'];
 416                 $each_tables[$table_name]['TABLE_COMMENT']     =& $each_tables[$table_name]['Comment'];
 417
 418                 if (strtoupper($each_tables[$table_name]['Comment']) === 'VIEW'
 419                         && $each_tables[$table_name]['Engine'] == NULL) {
 420                     $each_tables[$table_name]['TABLE_TYPE'] = 'VIEW';
 421                 } else {
 422                     /**
 423                      * @todo difference between 'TEMPORARY' and 'BASE TABLE' but how to detect?
 424                      */
 425                     $each_tables[$table_name]['TABLE_TYPE'] = 'BASE TABLE';
 426                 }
 427             }
 428
 429             $tables[$each_database] = $each_tables;
 430         }
 431     }
 432
 433     // cache table data
 434     // so PMA_Table does not require to issue SHOW TABLE STATUS again
 435     // Note: I don't see why we would need array_merge_recursive() here,
 436     // as it creates double entries for the same table (for example a double
 437     // entry for Comment when changing the storage engine in Operations)
 438     // Note 2: Instead of array_merge(), simply use the + operator because
 439     //  array_merge() renumbers numeric keys starting with 0, therefore
 440     //  we would lose a db name thats consists only of numbers
 441     foreach($tables as $one_database => $its_tables) {
 442         if (isset(PMA_Table::$cache[$one_database])) {
 443             PMA_Table::$cache[$one_database] = PMA_Table::$cache[$one_database] + $tables[$one_database];
 444         } else {
 445             PMA_Table::$cache[$one_database] = $tables[$one_database];
 446         }
 447     }
 448     unset($one_database, $its_tables);
 449
 450     if (! is_array($database)) {
 451         if (isset($tables[$database])) {
 452             return $tables[$database];
 453         } elseif (isset($tables[strtolower($database)])) {
 454             // on windows with lower_case_table_names = 1
 455             // MySQL returns
 456             // with SHOW DATABASES or information_schema.SCHEMATA: `Test`
 457             // but information_schema.TABLES gives `test`
 458             // bug #1436171
 459             // http://sf.net/support/tracker.php?aid=1436171
 460             return $tables[strtolower($database)];
 461         } else {
 462             return $tables;
 463         }
 464     } else {
 465         return $tables;
 466     }
 467 }
 468
 469 /**
 470  * returns array with databases containing extended infos about them
 471  *
 472  * @todo    move into PMA_List_Database?
 473  * @param   string      $databases      database
 474  * @param   boolean     $force_stats    retrieve stats also for MySQL < 5
 475  * @param   resource    $link           mysql link
 476  * @param   string      $sort_by        column to order by
 477  * @param   string      $sort_order     ASC or DESC
 478  * @param   integer     $limit_offset   starting offset for LIMIT
 479  * @param   bool|int    $limit_count    row count for LIMIT or true for $GLOBALS['cfg']['MaxDbList']
 480  * @return  array       $databases
 481  */
 482 function PMA_DBI_get_databases_full($database = null, $force_stats = false,
 483     $link = null, $sort_by = 'SCHEMA_NAME', $sort_order = 'ASC',
 484     $limit_offset = 0, $limit_count = false)
 485 {
 486     $sort_order = strtoupper($sort_order);
 487
 488     if (true === $limit_count) {
 489         $limit_count = $GLOBALS['cfg']['MaxDbList'];
 490     }
 491
 492     // initialize to avoid errors when there are no databases
 493     $databases = array();
 494
 495     $apply_limit_and_order_manual = true;
 496
 497     if (! $GLOBALS['cfg']['Server']['DisableIS']) {
 498         /**
 499          * if $GLOBALS['cfg']['NaturalOrder'] is enabled, we cannot use LIMIT
 500          * cause MySQL does not support natural ordering, we have to do it afterward
 501          */
 502         if ($GLOBALS['cfg']['NaturalOrder']) {
 503             $limit = '';
 504         } else {
 505             if ($limit_count) {
 506                 $limit = ' LIMIT ' . $limit_count . ' OFFSET ' . $limit_offset;
 507             }
 508
 509             $apply_limit_and_order_manual = false;
 510         }
 511
 512         // get table information from information_schema
 513         if ($database) {
 514             $sql_where_schema = 'WHERE `SCHEMA_NAME` LIKE \''
 515                 . addslashes($database) . '\'';
 516         } else {
 517             $sql_where_schema = '';
 518         }
 519
 520         // for PMA bc:
 521         // `SCHEMA_FIELD_NAME` AS `SHOW_TABLE_STATUS_FIELD_NAME`
 522         $sql = '
 523              SELECT `information_schema`.`SCHEMATA`.*';
 524         if ($force_stats) {
 525             $sql .= ',
 526                     COUNT(`information_schema`.`TABLES`.`TABLE_SCHEMA`)
 527                         AS `SCHEMA_TABLES`,
 528                     SUM(`information_schema`.`TABLES`.`TABLE_ROWS`)
 529                         AS `SCHEMA_TABLE_ROWS`,
 530                     SUM(`information_schema`.`TABLES`.`DATA_LENGTH`)
 531                         AS `SCHEMA_DATA_LENGTH`,
 532                     SUM(`information_schema`.`TABLES`.`MAX_DATA_LENGTH`)
 533                         AS `SCHEMA_MAX_DATA_LENGTH`,
 534                     SUM(`information_schema`.`TABLES`.`INDEX_LENGTH`)
 535                         AS `SCHEMA_INDEX_LENGTH`,
 536                     SUM(`information_schema`.`TABLES`.`DATA_LENGTH`
 537                       + `information_schema`.`TABLES`.`INDEX_LENGTH`)
 538                         AS `SCHEMA_LENGTH`,
 539                     SUM(`information_schema`.`TABLES`.`DATA_FREE`)
 540                         AS `SCHEMA_DATA_FREE`';
 541         }
 542         $sql .= '
 543                FROM `information_schema`.`SCHEMATA`';
 544         if ($force_stats) {
 545             $sql .= '
 546           LEFT JOIN `information_schema`.`TABLES`
 547                  ON BINARY `information_schema`.`TABLES`.`TABLE_SCHEMA`
 548                   = BINARY `information_schema`.`SCHEMATA`.`SCHEMA_NAME`';
 549         }
 550         $sql .= '
 551               ' . $sql_where_schema . '
 552            GROUP BY BINARY `information_schema`.`SCHEMATA`.`SCHEMA_NAME`
 553            ORDER BY BINARY ' . PMA_backquote($sort_by) . ' ' . $sort_order
 554            . $limit;
 555         $databases = PMA_DBI_fetch_result($sql, 'SCHEMA_NAME', null, $link);
 556
 557         $mysql_error = PMA_DBI_getError($link);
 558         if (! count($databases) && $GLOBALS['errno']) {
 559             PMA_mysqlDie($mysql_error, $sql);
 560         }
 561
 562         // display only databases also in official database list
 563         // f.e. to apply hide_db and only_db
 564         $drops = array_diff(array_keys($databases), (array) $GLOBALS['pma']->databases);
 565         if (count($drops)) {
 566             foreach ($drops as $drop) {
 567                 unset($databases[$drop]);
 568             }
 569             unset($drop);
 570         }
 571         unset($sql_where_schema, $sql, $drops);
 572     } else {
 573         foreach ($GLOBALS['pma']->databases as $database_name) {
 574             // MySQL forward compatibility
 575             // so pma could use this array as if every server is of version >5.0
 576             $databases[$database_name]['SCHEMA_NAME']      = $database_name;
 577
 578             if ($force_stats) {
 579                 require_once './libraries/mysql_charsets.lib.php';
 580
 581                 $databases[$database_name]['DEFAULT_COLLATION_NAME']
 582                     = PMA_getDbCollation($database_name);
 583
 584                 // get additional info about tables
 585                 $databases[$database_name]['SCHEMA_TABLES']          = 0;
 586                 $databases[$database_name]['SCHEMA_TABLE_ROWS']      = 0;
 587                 $databases[$database_name]['SCHEMA_DATA_LENGTH']     = 0;
 588                 $databases[$database_name]['SCHEMA_MAX_DATA_LENGTH'] = 0;
 589                 $databases[$database_name]['SCHEMA_INDEX_LENGTH']    = 0;
 590                 $databases[$database_name]['SCHEMA_LENGTH']          = 0;
 591                 $databases[$database_name]['SCHEMA_DATA_FREE']       = 0;
 592
 593                 $res = PMA_DBI_query('SHOW TABLE STATUS FROM ' . PMA_backquote($database_name) . ';');
 594                 while ($row = PMA_DBI_fetch_assoc($res)) {
 595                     $databases[$database_name]['SCHEMA_TABLES']++;
 596                     $databases[$database_name]['SCHEMA_TABLE_ROWS']
 597                         += $row['Rows'];
 598                     $databases[$database_name]['SCHEMA_DATA_LENGTH']
 599                         += $row['Data_length'];
 600                     $databases[$database_name]['SCHEMA_MAX_DATA_LENGTH']
 601                         += $row['Max_data_length'];
 602                     $databases[$database_name]['SCHEMA_INDEX_LENGTH']
 603                         += $row['Index_length'];
 604
 605                     // for InnoDB, this does not contain the number of
 606                     // overhead bytes but the total free space
 607                     if ('InnoDB' != $row['Engine']) {
 608                         $databases[$database_name]['SCHEMA_DATA_FREE']
 609                             += $row['Data_free'];
 610                     }
 611                     $databases[$database_name]['SCHEMA_LENGTH']
 612                         += $row['Data_length'] + $row['Index_length'];
 613                 }
 614                 PMA_DBI_free_result($res);
 615                 unset($res);
 616             }
 617         }
 618     }
 619
 620
 621     /**
 622      * apply limit and order manually now
 623      * (caused by older MySQL < 5 or $GLOBALS['cfg']['NaturalOrder'])
 624      */
 625     if ($apply_limit_and_order_manual) {
 626         $GLOBALS['callback_sort_order'] = $sort_order;
 627         $GLOBALS['callback_sort_by'] = $sort_by;
 628         usort($databases, 'PMA_usort_comparison_callback');
 629         unset($GLOBALS['callback_sort_order'], $GLOBALS['callback_sort_by']);
 630
 631         /**
 632          * now apply limit
 633          */
 634         if ($limit_count) {
 635             $databases = array_slice($databases, $limit_offset, $limit_count);
 636         }
 637     }
 638
 639     return $databases;
 640 }
 641
 642 /**
 643  * returns detailed array with all columns for given table in database,
 644  * or all tables/databases
 645  *
 646  * @param   string  $database   name of database
 647  * @param   string  $table      name of table to retrieve columns from
 648  * @param   string  $column     name of specific column
 649  * @param   mixed   $link       mysql link resource
 650  */
 651 function PMA_DBI_get_columns_full($database = null, $table = null,
 652     $column = null, $link = null)
 653 {
 654     $columns = array();
 655
 656     if (! $GLOBALS['cfg']['Server']['DisableIS']) {
 657         $sql_wheres = array();
 658         $array_keys = array();
 659
 660         // get columns information from information_schema
 661         if (null !== $database) {
 662             $sql_wheres[] = '`TABLE_SCHEMA` = \'' . addslashes($database) . '\' ';
 663         } else {
 664             $array_keys[] = 'TABLE_SCHEMA';
 665         }
 666         if (null !== $table) {
 667             $sql_wheres[] = '`TABLE_NAME` = \'' . addslashes($table) . '\' ';
 668         } else {
 669             $array_keys[] = 'TABLE_NAME';
 670         }
 671         if (null !== $column) {
 672             $sql_wheres[] = '`COLUMN_NAME` = \'' . addslashes($column) . '\' ';
 673         } else {
 674             $array_keys[] = 'COLUMN_NAME';
 675         }
 676
 677         // for PMA bc:
 678         // `[SCHEMA_FIELD_NAME]` AS `[SHOW_FULL_COLUMNS_FIELD_NAME]`
 679         $sql = '
 680              SELECT *,
 681                     `COLUMN_NAME`       AS `Field`,
 682                     `COLUMN_TYPE`       AS `Type`,
 683                     `COLLATION_NAME`    AS `Collation`,
 684                     `IS_NULLABLE`       AS `Null`,
 685                     `COLUMN_KEY`        AS `Key`,
 686                     `COLUMN_DEFAULT`    AS `Default`,
 687                     `EXTRA`             AS `Extra`,
 688                     `PRIVILEGES`        AS `Privileges`,
 689                     `COLUMN_COMMENT`    AS `Comment`
 690                FROM `information_schema`.`COLUMNS`';
 691         if (count($sql_wheres)) {
 692             $sql .= "\n" . ' WHERE ' . implode(' AND ', $sql_wheres);
 693         }
 694
 695         $columns = PMA_DBI_fetch_result($sql, $array_keys, null, $link);
 696         unset($sql_wheres, $sql);
 697     } else {
 698         if (null === $database) {
 699             foreach ($GLOBALS['pma']->databases as $database) {
 700                 $columns[$database] = PMA_DBI_get_columns_full($database, null,
 701                     null, $link);
 702             }
 703             return $columns;
 704         } elseif (null === $table) {
 705             $tables = PMA_DBI_get_tables($database);
 706             foreach ($tables as $table) {
 707                 $columns[$table] = PMA_DBI_get_columns_full(
 708                     $database, $table, null, $link);
 709             }
 710             return $columns;
 711         }
 712
 713         $sql = 'SHOW FULL COLUMNS FROM '
 714             . PMA_backquote($database) . '.' . PMA_backquote($table);
 715         if (null !== $column) {
 716             $sql .= " LIKE '" . $column . "'";
 717         }
 718
 719         $columns = PMA_DBI_fetch_result($sql, 'Field', null, $link);
 720
 721         $ordinal_position = 1;
 722         foreach ($columns as $column_name => $each_column) {
 723
 724             // MySQL forward compatibility
 725             // so pma could use this array as if every server is of version >5.0
 726             $columns[$column_name]['COLUMN_NAME']                 =& $columns[$column_name]['Field'];
 727             $columns[$column_name]['COLUMN_TYPE']                 =& $columns[$column_name]['Type'];
 728             $columns[$column_name]['COLLATION_NAME']              =& $columns[$column_name]['Collation'];
 729             $columns[$column_name]['IS_NULLABLE']                 =& $columns[$column_name]['Null'];
 730             $columns[$column_name]['COLUMN_KEY']                  =& $columns[$column_name]['Key'];
 731             $columns[$column_name]['COLUMN_DEFAULT']              =& $columns[$column_name]['Default'];
 732             $columns[$column_name]['EXTRA']                       =& $columns[$column_name]['Extra'];
 733             $columns[$column_name]['PRIVILEGES']                  =& $columns[$column_name]['Privileges'];
 734             $columns[$column_name]['COLUMN_COMMENT']              =& $columns[$column_name]['Comment'];
 735
 736             $columns[$column_name]['TABLE_CATALOG']               = null;
 737             $columns[$column_name]['TABLE_SCHEMA']                = $database;
 738             $columns[$column_name]['TABLE_NAME']                  = $table;
 739             $columns[$column_name]['ORDINAL_POSITION']            = $ordinal_position;
 740             $columns[$column_name]['DATA_TYPE']                   =
 741                 substr($columns[$column_name]['COLUMN_TYPE'], 0,
 742                     strpos($columns[$column_name]['COLUMN_TYPE'], '('));
 743             /**
 744              * @todo guess CHARACTER_MAXIMUM_LENGTH from COLUMN_TYPE
 745              */
 746             $columns[$column_name]['CHARACTER_MAXIMUM_LENGTH']    = null;
 747             /**
 748              * @todo guess CHARACTER_OCTET_LENGTH from CHARACTER_MAXIMUM_LENGTH
 749              */
 750             $columns[$column_name]['CHARACTER_OCTET_LENGTH']      = null;
 751             $columns[$column_name]['NUMERIC_PRECISION']           = null;
 752             $columns[$column_name]['NUMERIC_SCALE']               = null;
 753             $columns[$column_name]['CHARACTER_SET_NAME']          =
 754                 substr($columns[$column_name]['COLLATION_NAME'], 0,
 755                     strpos($columns[$column_name]['COLLATION_NAME'], '_'));
 756
 757             $ordinal_position++;
 758         }
 759
 760         if (null !== $column) {
 761             reset($columns);
 762             $columns = current($columns);
 763         }
 764     }
 765
 766     return $columns;
 767 }
 768
 769 /**
 770  * @todo should only return columns names, for more info use PMA_DBI_get_columns_full()
 771  *
 772  * @deprecated by PMA_DBI_get_columns() or PMA_DBI_get_columns_full()
 773  * @param   string  $database   name of database
 774  * @param   string  $table      name of table to retrieve columns from
 775  * @param   mixed   $link       mysql link resource
 776  * @return  array   column info
 777  */
 778 function PMA_DBI_get_fields($database, $table, $link = null)
 779 {
 780     // here we use a try_query because when coming from
 781     // tbl_create + tbl_properties.inc.php, the table does not exist
 782     $fields = PMA_DBI_fetch_result(
 783         'SHOW FULL COLUMNS
 784         FROM ' . PMA_backquote($database) . '.' . PMA_backquote($table),
 785         null, null, $link);
 786     if (! is_array($fields) || count($fields) < 1) {
 787         return false;
 788     }
 789     return $fields;
 790 }
 791
 792 /**
 793  * array PMA_DBI_get_columns(string $database, string $table, bool $full = false, mysql db link $link = null)
 794  *
 795  * @param   string  $database   name of database
 796  * @param   string  $table      name of table to retrieve columns from
 797  * @param   boolean $full       whether to return full info or only column names
 798  * @param   mixed   $link       mysql link resource
 799  * @return  array   column names
 800  */
 801 function PMA_DBI_get_columns($database, $table, $full = false, $link = null)
 802 {
 803     $fields = PMA_DBI_fetch_result(
 804         'SHOW ' . ($full ? 'FULL' : '') . ' COLUMNS
 805         FROM ' . PMA_backquote($database) . '.' . PMA_backquote($table),
 806         'Field', ($full ? null : 'Field'), $link);
 807     if (! is_array($fields) || count($fields) < 1) {
 808         return false;
 809     }
 810     return $fields;
 811 }
 812
 813 /**
 814  * array PMA_DBI_get_column_values (string $database, string $table, string $column , mysql db link $link = null)
 815  *
 816  * @param   string  $database   name of database
 817  * @param   string  $table      name of table to retrieve columns from
 818  * @param   string  $column     name of the column to retrieve data from
 819  * @param   mixed   $link       mysql link resource
 820  * @return  array   $field_values
 821  */
 822
 823 function PMA_DBI_get_column_values($database, $table, $column, $link = null)
 824 {
 825     $query = 'SELECT ';
 826     for($i=0; $i< sizeof($column); $i++)
 827     {
 828         $query.= PMA_backquote($column[$i]);
 829         if($i < (sizeof($column)-1))
 830         {
 831             $query.= ', ';
 832         }
 833     }
 834     $query.= ' FROM ' . PMA_backquote($database) . '.' . PMA_backquote($table);
 835     $field_values = PMA_DBI_fetch_result($query, null, null, $link);
 836
 837     if (! is_array($field_values) || count($field_values) < 1) {
 838         return false;
 839     }
 840     return $field_values;
 841 }
 842 /**
 843  * array PMA_DBI_get_table_data (string $database, string $table, mysql db link $link = null)
 844  *
 845  * @param   string  $database   name of database
 846  * @param   string  $table      name of table to retrieve columns from
 847  * @param   mixed   $link       mysql link resource
 848  * @return  array   $result
 849  */
 850
 851  function PMA_DBI_get_table_data($database, $table, $link = null)
 852  {
 853
 854     $result = PMA_DBI_fetch_result(
 855         'SELECT * FROM ' . PMA_backquote($database) . '.' . PMA_backquote($table),
 856         null,null, $link);
 857
 858     if (! is_array($result) || count($result) < 1) {
 859         return false;
 860     }
 861     return $result;
 862  }
 863
 864 /**
 865 * array  PMA_DBI_get_table_indexes($database, $table, $link = null)
 866 *
 867 * @param    string  $database   name of database
 868 * @param    string  $table      name of the table whose indexes are to be retreived
 869 * @param    mixed   $link       mysql link resource
 870 * @return   array   $indexes
 871 */
 872
 873 function PMA_DBI_get_table_indexes($database, $table, $link = null)
 874 {
 875
 876     $indexes = PMA_DBI_fetch_result(
 877               'SHOW INDEXES FROM ' .PMA_backquote($database) . '.' . PMA_backquote($table),
 878                null, null, $link);
 879
 880     if (! is_array($indexes) || count($indexes) < 1) {
 881         return false;
 882     }
 883     return $indexes;
 884 }
 885
 886  /**
 887  * returns value of given mysql server variable
 888  *
 889  * @param   string  $var    mysql server variable name
 890  * @param   int     $type   PMA_DBI_GETVAR_SESSION|PMA_DBI_GETVAR_GLOBAL
 891  * @param   mixed   $link   mysql link resource|object
 892  * @return  mixed   value for mysql server variable
 893  */
 894
 895
 896 function PMA_DBI_get_variable($var, $type = PMA_DBI_GETVAR_SESSION, $link = null)
 897 {
 898     if ($link === null) {
 899         if (isset($GLOBALS['userlink'])) {
 900             $link = $GLOBALS['userlink'];
 901         } else {
 902             return false;
 903         }
 904     }
 905
 906     switch ($type) {
 907         case PMA_DBI_GETVAR_SESSION:
 908             $modifier = ' SESSION';
 909             break;
 910         case PMA_DBI_GETVAR_GLOBAL:
 911             $modifier = ' GLOBAL';
 912             break;
 913         default:
 914             $modifier = '';
 915     }
 916     return PMA_DBI_fetch_value(
 917         'SHOW' . $modifier . ' VARIABLES LIKE \'' . $var . '\';', 0, 1, $link);
 918 }
 919
 920 /**
 921  *  Function called just after a connection to the MySQL database server has been established
 922  *  It sets the connection collation, and determins the version of MySQL which is running.
 923  *
 924  * @uses    ./libraries/charset_conversion.lib.php
 925  * @uses    PMA_DBI_QUERY_STORE
 926  * @uses    PMA_MYSQL_INT_VERSION to set it
 927  * @uses    PMA_MYSQL_STR_VERSION to set it
 928  * @uses    $_SESSION['PMA_MYSQL_INT_VERSION'] for caching
 929  * @uses    $_SESSION['PMA_MYSQL_STR_VERSION'] for caching
 930  * @uses    PMA_DBI_GETVAR_SESSION
 931  * @uses    PMA_DBI_fetch_value()
 932  * @uses    PMA_DBI_query()
 933  * @uses    PMA_DBI_get_variable()
 934  * @uses    $GLOBALS['collation_connection']
 935  * @uses    $GLOBALS['available_languages']
 936  * @uses    $GLOBALS['mysql_charset_map']
 937  * @uses    $GLOBALS['charset']
 938  * @uses    $GLOBALS['lang']
 939  * @uses    $GLOBALS['server']
 940  * @uses    $GLOBALS['cfg']['Lang']
 941  * @uses    defined()
 942  * @uses    explode()
 943  * @uses    sprintf()
 944  * @uses    intval()
 945  * @uses    define()
 946  * @uses    defined()
 947  * @uses    substr()
 948  * @uses    count()
 949  * @param   mixed   $link   mysql link resource|object
 950  * @param   boolean $is_controluser
 951  */
 952 function PMA_DBI_postConnect($link, $is_controluser = false)
 953 {
 954     if (! defined('PMA_MYSQL_INT_VERSION')) {
 955         if (PMA_cacheExists('PMA_MYSQL_INT_VERSION', true)) {
 956             define('PMA_MYSQL_INT_VERSION', PMA_cacheGet('PMA_MYSQL_INT_VERSION', true));
 957             define('PMA_MYSQL_STR_VERSION', PMA_cacheGet('PMA_MYSQL_STR_VERSION', true));
 958         } else {
 959             $mysql_version = PMA_DBI_fetch_value(
 960                 'SELECT VERSION()', 0, 0, $link, PMA_DBI_QUERY_STORE);
 961             if ($mysql_version) {
 962                 $match = explode('.', $mysql_version);
 963                 define('PMA_MYSQL_INT_VERSION',
 964                     (int) sprintf('%d%02d%02d', $match[0], $match[1],
 965                             intval($match[2])));
 966                 define('PMA_MYSQL_STR_VERSION', $mysql_version);
 967                 unset($mysql_version, $match);
 968             } else {
 969                 define('PMA_MYSQL_INT_VERSION', 50015);
 970                 define('PMA_MYSQL_STR_VERSION', '5.00.15');
 971             }
 972             PMA_cacheSet('PMA_MYSQL_INT_VERSION', PMA_MYSQL_INT_VERSION, true);
 973             PMA_cacheSet('PMA_MYSQL_STR_VERSION', PMA_MYSQL_STR_VERSION, true);
 974         }
 975     }
 976
 977     if (! empty($GLOBALS['collation_connection'])) {
 978         PMA_DBI_query("SET CHARACTER SET 'utf8';", $link, PMA_DBI_QUERY_STORE);
 979         $mysql_charset = explode('_', $GLOBALS['collation_connection']);
 980         PMA_DBI_query("SET collation_connection = '" . PMA_sqlAddslashes($GLOBALS['collation_connection']) . "';", $link, PMA_DBI_QUERY_STORE);
 981     } else {
 982         PMA_DBI_query("SET NAMES 'utf8' COLLATE 'utf8_general_ci';", $link, PMA_DBI_QUERY_STORE);
 983     }
 984 }
 985
 986 /**
 987  * returns a single value from the given result or query,
 988  * if the query or the result has more than one row or field
 989  * the first field of the first row is returned
 990  *
 991  * <code>
 992  * $sql = 'SELECT `name` FROM `user` WHERE `id` = 123';
 993  * $user_name = PMA_DBI_fetch_value($sql);
 994  * // produces
 995  * // $user_name = 'John Doe'
 996  * </code>
 997  *
 998  * @uses    is_string()
 999  * @uses    is_int()
1000  * @uses    PMA_DBI_try_query()
1001  * @uses    PMA_DBI_num_rows()
1002  * @uses    PMA_DBI_fetch_row()
1003  * @uses    PMA_DBI_fetch_assoc()
1004  * @uses    PMA_DBI_free_result()
1005  * @param   string|mysql_result $result query or mysql result
1006  * @param   integer             $row_number row to fetch the value from,
1007  *                                      starting at 0, with 0 beeing default
1008  * @param   integer|string      $field  field to fetch the value from,
1009  *                                      starting at 0, with 0 beeing default
1010  * @param   resource            $link   mysql link
1011  * @param   mixed               $options
1012  * @return  mixed               value of first field in first row from result
1013  *                              or false if not found
1014  */
1015 function PMA_DBI_fetch_value($result, $row_number = 0, $field = 0, $link = null, $options = 0) {
1016     $value = false;
1017
1018     if (is_string($result)) {
1019         $result = PMA_DBI_try_query($result, $link, $options | PMA_DBI_QUERY_STORE);
1020     }
1021
1022     // return false if result is empty or false
1023     // or requested row is larger than rows in result
1024     if (PMA_DBI_num_rows($result) < ($row_number + 1)) {
1025         return $value;
1026     }
1027
1028     // if $field is an integer use non associative mysql fetch function
1029     if (is_int($field)) {
1030         $fetch_function = 'PMA_DBI_fetch_row';
1031     } else {
1032         $fetch_function = 'PMA_DBI_fetch_assoc';
1033     }
1034
1035     // get requested row
1036     for ($i = 0; $i <= $row_number; $i++) {
1037         $row = $fetch_function($result);
1038     }
1039     PMA_DBI_free_result($result);
1040
1041     // return requested field
1042     if (isset($row[$field])) {
1043         $value = $row[$field];
1044     }
1045     unset($row);
1046
1047     return $value;
1048 }
1049
1050 /**
1051  * returns only the first row from the result
1052  *
1053  * <code>
1054  * $sql = 'SELECT * FROM `user` WHERE `id` = 123';
1055  * $user = PMA_DBI_fetch_single_row($sql);
1056  * // produces
1057  * // $user = array('id' => 123, 'name' => 'John Doe')
1058  * </code>
1059  *
1060  * @uses    is_string()
1061  * @uses    PMA_DBI_try_query()
1062  * @uses    PMA_DBI_num_rows()
1063  * @uses    PMA_DBI_fetch_row()
1064  * @uses    PMA_DBI_fetch_assoc()
1065  * @uses    PMA_DBI_fetch_array()
1066  * @uses    PMA_DBI_free_result()
1067  * @param   string|mysql_result $result query or mysql result
1068  * @param   string              $type   NUM|ASSOC|BOTH
1069  *                                      returned array should either numeric
1070  *                                      associativ or booth
1071  * @param   resource            $link   mysql link
1072  * @param   mixed               $options
1073  * @return  array|boolean       first row from result
1074  *                              or false if result is empty
1075  */
1076 function PMA_DBI_fetch_single_row($result, $type = 'ASSOC', $link = null, $options = 0) {
1077     if (is_string($result)) {
1078         $result = PMA_DBI_try_query($result, $link, $options | PMA_DBI_QUERY_STORE);
1079     }
1080
1081     // return null if result is empty or false
1082     if (! PMA_DBI_num_rows($result)) {
1083         return false;
1084     }
1085
1086     switch ($type) {
1087         case 'NUM' :
1088             $fetch_function = 'PMA_DBI_fetch_row';
1089             break;
1090         case 'ASSOC' :
1091             $fetch_function = 'PMA_DBI_fetch_assoc';
1092             break;
1093         case 'BOTH' :
1094         default :
1095             $fetch_function = 'PMA_DBI_fetch_array';
1096             break;
1097     }
1098
1099     $row = $fetch_function($result);
1100     PMA_DBI_free_result($result);
1101     return $row;
1102 }
1103
1104 /**
1105  * returns all rows in the resultset in one array
1106  *
1107  * <code>
1108  * $sql = 'SELECT * FROM `user`';
1109  * $users = PMA_DBI_fetch_result($sql);
1110  * // produces
1111  * // $users[] = array('id' => 123, 'name' => 'John Doe')
1112  *
1113  * $sql = 'SELECT `id`, `name` FROM `user`';
1114  * $users = PMA_DBI_fetch_result($sql, 'id');
1115  * // produces
1116  * // $users['123'] = array('id' => 123, 'name' => 'John Doe')
1117  *
1118  * $sql = 'SELECT `id`, `name` FROM `user`';
1119  * $users = PMA_DBI_fetch_result($sql, 0);
1120  * // produces
1121  * // $users['123'] = array(0 => 123, 1 => 'John Doe')
1122  *
1123  * $sql = 'SELECT `id`, `name` FROM `user`';
1124  * $users = PMA_DBI_fetch_result($sql, 'id', 'name');
1125  * // or
1126  * $users = PMA_DBI_fetch_result($sql, 0, 1);
1127  * // produces
1128  * // $users['123'] = 'John Doe'
1129  *
1130  * $sql = 'SELECT `name` FROM `user`';
1131  * $users = PMA_DBI_fetch_result($sql);
1132  * // produces
1133  * // $users[] = 'John Doe'
1134  *
1135  * $sql = 'SELECT `group`, `name` FROM `user`'
1136  * $users = PMA_DBI_fetch_result($sql, array('group', null), 'name');
1137  * // produces
1138  * // $users['admin'][] = 'John Doe'
1139  *
1140  * $sql = 'SELECT `group`, `name` FROM `user`'
1141  * $users = PMA_DBI_fetch_result($sql, array('group', 'name'), 'id');
1142  * // produces
1143  * // $users['admin']['John Doe'] = '123'
1144  * </code>
1145  *
1146  * @uses    is_string()
1147  * @uses    is_int()
1148  * @uses    PMA_DBI_try_query()
1149  * @uses    PMA_DBI_num_rows()
1150  * @uses    PMA_DBI_num_fields()
1151  * @uses    PMA_DBI_fetch_row()
1152  * @uses    PMA_DBI_fetch_assoc()
1153  * @uses    PMA_DBI_free_result()
1154  * @param   string|mysql_result $result query or mysql result
1155  * @param   string|integer      $key    field-name or offset
1156  *                                      used as key for array
1157  * @param   string|integer      $value  value-name or offset
1158  *                                      used as value for array
1159  * @param   resource            $link   mysql link
1160  * @param   mixed               $options
1161  * @return  array               resultrows or values indexed by $key
1162  */
1163 function PMA_DBI_fetch_result($result, $key = null, $value = null,
1164     $link = null, $options = 0)
1165 {
1166     $resultrows = array();
1167
1168     if (is_string($result)) {
1169         $result = PMA_DBI_try_query($result, $link, $options);
1170     }
1171
1172     // return empty array if result is empty or false
1173     if (! $result) {
1174         return $resultrows;
1175     }
1176
1177     $fetch_function = 'PMA_DBI_fetch_assoc';
1178
1179     // no nested array if only one field is in result
1180     if (null === $key && 1 === PMA_DBI_num_fields($result)) {
1181         $value = 0;
1182         $fetch_function = 'PMA_DBI_fetch_row';
1183     }
1184
1185     // if $key is an integer use non associative mysql fetch function
1186     if (is_int($key)) {
1187         $fetch_function = 'PMA_DBI_fetch_row';
1188     }
1189
1190     if (null === $key && null === $value) {
1191         while ($row = $fetch_function($result)) {
1192             $resultrows[] = $row;
1193         }
1194     } elseif (null === $key) {
1195         while ($row = $fetch_function($result)) {
1196             $resultrows[] = $row[$value];
1197         }
1198     } elseif (null === $value) {
1199         if (is_array($key)) {
1200             while ($row = $fetch_function($result)) {
1201                 $result_target =& $resultrows;
1202                 foreach ($key as $key_index) {
1203                     if (null === $key_index) {
1204                         $result_target =& $result_target[];
1205                         continue;
1206                     }
1207
1208                     if (! isset($result_target[$row[$key_index]])) {
1209                         $result_target[$row[$key_index]] = array();
1210                     }
1211                     $result_target =& $result_target[$row[$key_index]];
1212                 }
1213                 $result_target = $row;
1214             }
1215         } else {
1216             while ($row = $fetch_function($result)) {
1217                 $resultrows[$row[$key]] = $row;
1218             }
1219         }
1220     } else {
1221         if (is_array($key)) {
1222             while ($row = $fetch_function($result)) {
1223                 $result_target =& $resultrows;
1224                 foreach ($key as $key_index) {
1225                     if (null === $key_index) {
1226                         $result_target =& $result_target[];
1227                         continue;
1228                     }
1229
1230                     if (! isset($result_target[$row[$key_index]])) {
1231                         $result_target[$row[$key_index]] = array();
1232                     }
1233                     $result_target =& $result_target[$row[$key_index]];
1234                 }
1235                 $result_target = $row[$value];
1236             }
1237         } else {
1238             while ($row = $fetch_function($result)) {
1239                 $resultrows[$row[$key]] = $row[$value];
1240             }
1241         }
1242     }
1243
1244     PMA_DBI_free_result($result);
1245     return $resultrows;
1246 }
1247
1248 /**
1249  * return default table engine for given database
1250  *
1251  * @return  string  default table engine
1252  */
1253 function PMA_DBI_get_default_engine()
1254 {
1255     return PMA_DBI_fetch_value('SHOW VARIABLES LIKE \'storage_engine\';', 0, 1);
1256 }
1257
1258 /**
1259  * Get supported SQL compatibility modes
1260  *
1261  * @return  array   supported SQL compatibility modes
1262  */
1263 function PMA_DBI_getCompatibilities()
1264 {
1265     $compats = array('NONE');
1266     $compats[] = 'ANSI';
1267     $compats[] = 'DB2';
1268     $compats[] = 'MAXDB';
1269     $compats[] = 'MYSQL323';
1270     $compats[] = 'MYSQL40';
1271     $compats[] = 'MSSQL';
1272     $compats[] = 'ORACLE';
1273     // removed; in MySQL 5.0.33, this produces exports that
1274     // can't be read by POSTGRESQL (see our bug #1596328)
1275     //$compats[] = 'POSTGRESQL';
1276     $compats[] = 'TRADITIONAL';
1277
1278     return $compats;
1279 }
1280
1281 /**
1282  * returns warnings for last query
1283  *
1284  * @uses    $GLOBALS['userlink']
1285  * @uses    PMA_DBI_fetch_result()
1286  * @param   resource mysql link  $link   mysql link resource
1287  * @return  array   warnings
1288  */
1289 function PMA_DBI_get_warnings($link = null)
1290 {
1291     if (empty($link)) {
1292         if (isset($GLOBALS['userlink'])) {
1293             $link = $GLOBALS['userlink'];
1294         } else {
1295             return array();
1296         }
1297     }
1298
1299     return PMA_DBI_fetch_result('SHOW WARNINGS', null, null, $link);
1300 }
1301
1302 /**
1303  * returns true (int > 0) if current user is superuser
1304  * otherwise 0
1305  *
1306  * @uses    $_SESSION['is_superuser'] for caching
1307  * @uses    $GLOBALS['userlink']
1308  * @uses    $GLOBALS['server']
1309  * @uses    PMA_DBI_try_query()
1310  * @uses    PMA_DBI_QUERY_STORE
1311  * @return  integer  $is_superuser
1312  */
1313 function PMA_isSuperuser()
1314 {
1315     if (PMA_cacheExists('is_superuser', true)) {
1316         return PMA_cacheGet('is_superuser', true);
1317     }
1318
1319     // with mysql extension, when connection failed we don't have
1320     // a $userlink
1321     if (isset($GLOBALS['userlink'])) {
1322         $r = (bool) PMA_DBI_try_query('SELECT COUNT(*) FROM mysql.user', $GLOBALS['userlink'], PMA_DBI_QUERY_STORE);
1323         PMA_cacheSet('is_superuser', $r, true);
1324     } else {
1325         PMA_cacheSet('is_superuser', false, true);
1326     }
1327
1328     return PMA_cacheGet('is_superuser', true);
1329 }
1330
1331 /**
1332  * returns an array of PROCEDURE or FUNCTION names for a db
1333  *
1334  * @uses    PMA_DBI_free_result()
1335  * @param   string              $db     db name
1336  * @param   string              $which  PROCEDURE | FUNCTION
1337  * @param   resource            $link   mysql link
1338  *
1339  * @return  array   the procedure names or function names
1340  */
1341 function PMA_DBI_get_procedures_or_functions($db, $which, $link = null)
1342 {
1343     $shows = PMA_DBI_fetch_result('SHOW ' . $which . ' STATUS;', null, null, $link);
1344     $result = array();
1345     foreach ($shows as $one_show) {
1346         if ($one_show['Db'] == $db && $one_show['Type'] == $which) {
1347             $result[] = $one_show['Name'];
1348         }
1349     }
1350     return($result);
1351 }
1352
1353 /**
1354  * returns the definition of a specific PROCEDURE, FUNCTION or EVENT
1355  *
1356  * @uses    PMA_DBI_fetch_value()
1357  * @param   string              $db     db name
1358  * @param   string              $which  PROCEDURE | FUNCTION | EVENT
1359  * @param   string              $name  the procedure|function|event name
1360  * @param   resource            $link   mysql link
1361  *
1362  * @return  string              the definition
1363  */
1364 function PMA_DBI_get_definition($db, $which, $name, $link = null)
1365 {
1366     $returned_field = array(
1367         'PROCEDURE' => 'Create Procedure',
1368         'FUNCTION'  => 'Create Function',
1369         'EVENT'     => 'Create Event'
1370     );
1371     $query = 'SHOW CREATE ' . $which . ' ' . PMA_backquote($db) . '.' . PMA_backquote($name);
1372     return(PMA_DBI_fetch_value($query, 0, $returned_field[$which]));
1373 }
1374
1375 /**
1376  * returns details about the TRIGGERs of a specific table
1377  *
1378  * @uses    PMA_DBI_fetch_result()
1379  * @param   string              $db     db name
1380  * @param   string              $table  table name
1381  * @param   string              $delimiter  the delimiter to use (may be empty)
1382  *
1383  * @return  array               information about triggers (may be empty)
1384  */
1385 function PMA_DBI_get_triggers($db, $table, $delimiter = '//')
1386 {
1387     $result = array();
1388
1389     if (! $GLOBALS['cfg']['Server']['DisableIS']) {
1390     // Note: in http://dev.mysql.com/doc/refman/5.0/en/faqs-triggers.html
1391     // their example uses WHERE TRIGGER_SCHEMA='dbname' so let's use this
1392     // instead of WHERE EVENT_OBJECT_SCHEMA='dbname'
1393         $triggers = PMA_DBI_fetch_result("SELECT TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_MANIPULATION, ACTION_TIMING, ACTION_STATEMENT, EVENT_OBJECT_SCHEMA, EVENT_OBJECT_TABLE FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA= '" . PMA_sqlAddslashes($db,true) . "' and EVENT_OBJECT_TABLE = '" . PMA_sqlAddslashes($table, true) . "';");
1394     } else {
1395         $triggers = PMA_DBI_fetch_result("SHOW TRIGGERS FROM " . PMA_sqlAddslashes($db,true) . " LIKE '" . PMA_sqlAddslashes($table, true) . "';");
1396     }
1397
1398     if ($triggers) {
1399         foreach ($triggers as $trigger) {
1400             if ($GLOBALS['cfg']['Server']['DisableIS']) {
1401                 $trigger['TRIGGER_NAME'] = $trigger['Trigger'];
1402                 $trigger['ACTION_TIMING'] = $trigger['Timing'];
1403                 $trigger['EVENT_MANIPULATION'] = $trigger['Event'];
1404                 $trigger['EVENT_OBJECT_TABLE'] = $trigger['Table'];
1405                 $trigger['ACTION_STATEMENT'] = $trigger['Statement'];
1406             }
1407             $one_result = array();
1408             $one_result['name'] = $trigger['TRIGGER_NAME'];
1409             $one_result['action_timing'] = $trigger['ACTION_TIMING'];
1410             $one_result['event_manipulation'] = $trigger['EVENT_MANIPULATION'];
1411
1412             // do not prepend the schema name; this way, importing the
1413             // definition into another schema will work
1414             $one_result['full_trigger_name'] = PMA_backquote($trigger['TRIGGER_NAME']);
1415             $one_result['drop'] = 'DROP TRIGGER IF EXISTS ' . $one_result['full_trigger_name'];
1416             $one_result['create'] = 'CREATE TRIGGER ' . $one_result['full_trigger_name'] . ' ' . $trigger['ACTION_TIMING']. ' ' . $trigger['EVENT_MANIPULATION'] . ' ON ' . PMA_backquote($trigger['EVENT_OBJECT_TABLE']) . "\n" . ' FOR EACH ROW ' . $trigger['ACTION_STATEMENT'] . "\n" . $delimiter . "\n";
1417
1418             $result[] = $one_result;
1419         }
1420     }
1421     return($result);
1422 }
1423
1424 /**
1425  * Returns TRUE if $db.$view_name is a view, FALSE if not
1426  *
1427  * @uses   PMA_DBI_fetch_result()
1428  * @param  string $db         database name
1429  * @param  string $view_name  view/table name
1430  *
1431  * @return bool               TRUE if $db.$view_name is a view, FALSE if not
1432  */
1433 function PMA_isView($db, $view_name)
1434 {
1435     $result = PMA_DBI_fetch_result("SELECT TABLE_NAME FROM information_schema.VIEWS WHERE TABLE_SCHEMA = '".$db."' and TABLE_NAME = '".$view_name."';");
1436
1437     if ($result) {
1438         return TRUE;
1439     } else {
1440         return FALSE;
1441     }
1442 }
1443 ?>