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