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