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  */
   8
   9 /**
  10  *
  11  */
  12 // PMA_DBI_try_query()
  13 define('PMA_DBI_QUERY_STORE',       1);  // Force STORE_RESULT method, ignored by classic MySQL.
  14 define('PMA_DBI_QUERY_UNBUFFERED',  2);  // Do not read whole query
  15 // PMA_DBI_get_variable()
  16 define('PMA_DBI_GETVAR_SESSION', 1);
  17 define('PMA_DBI_GETVAR_GLOBAL', 2);
  18
  19 /**
  20  * Loads the mysql extensions if it is not loaded yet
  21  *
  22  * @param   string  $extension  mysql extension to load
  23  */
  24 function PMA_DBI_checkAndLoadMysqlExtension($extension = 'mysql') {
  25     if (! function_exists($extension . '_connect')) {
  26         PMA_dl($extension);
  27         // check whether mysql is available
  28         if (! function_exists($extension . '_connect')) {
  29             return false;
  30         }
  31     }
  32
  33     return true;
  34 }
  35
  36
  37 /**
  38  * check for requested extension
  39  */
  40 if (! PMA_DBI_checkAndLoadMysqlExtension($GLOBALS['cfg']['Server']['extension'])) {
  41
  42     // if it fails try alternative extension ...
  43     // and display an error ...
  44
  45     /**
  46      * @todo 2.7.1: add different messages for alternativ extension
  47      * and complete fail (no alternativ 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_checkAndLoadMysqlExtension($alternativ_extension)) {
  63         // if alternativ 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, usally 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  * returns array of all tables in given db or dbs
 190  * this function expects unqoted names:
 191  * RIGHT: my_database
 192  * WRONG: `my_database`
 193  * WRONG: my\_database
 194  * if $tbl_is_group is true, $table is used as filter for table names
 195  * if $tbl_is_group is 'comment, $table is used as filter for table comments
 196  *
 197  * <code>
 198  * PMA_DBI_get_tables_full('my_database');
 199  * PMA_DBI_get_tables_full('my_database', 'my_table'));
 200  * PMA_DBI_get_tables_full('my_database', 'my_tables_', true));
 201  * PMA_DBI_get_tables_full('my_database', 'my_tables_', 'comment'));
 202  * </code>
 203  *
 204  * @uses    PMA_DBI_fetch_result()
 205  * @uses    PMA_escape_mysql_wildcards()
 206  * @uses    PMA_backquote()
 207  * @uses    is_array()
 208  * @uses    addslashes()
 209  * @uses    strpos()
 210  * @uses    strtoupper()
 211  * @param   string          $databases      database
 212  * @param   string          $table          table
 213  * @param   boolean|string  $tbl_is_group   $table is a table group
 214  * @param   resource        $link           mysql link
 215  * @return  array           list of tables in given db(s)
 216  */
 217 function PMA_DBI_get_tables_full($database, $table = false,
 218     $tbl_is_group = false, $link = null, $limit_offset = 0, $limit_count = false)
 219 {
 220     // currently supported for MySQL >= 50002
 221     if (true === $limit_count) {
 222         $limit_count = $GLOBALS['cfg']['MaxTableList'];
 223     }
 224     // prepare and check parameters
 225     if (! is_array($database)) {
 226         $databases = array($database);
 227     } else {
 228         $databases = $database;
 229     }
 230
 231     $tables = array();
 232
 233     // get table information from information_schema
 234     if ($table) {
 235         if (true === $tbl_is_group) {
 236             $sql_where_table = 'AND `TABLE_NAME` LIKE \''
 237                 . PMA_escape_mysql_wildcards(addslashes($table)) . '%\'';
 238         } elseif ('comment' === $tbl_is_group) {
 239             $sql_where_table = 'AND `TABLE_COMMENT` LIKE \''
 240                 . PMA_escape_mysql_wildcards(addslashes($table)) . '%\'';
 241         } else {
 242             $sql_where_table = 'AND `TABLE_NAME` = \'' . addslashes($table) . '\'';
 243         }
 244     } else {
 245         $sql_where_table = '';
 246     }
 247
 248     // for PMA bc:
 249     // `SCHEMA_FIELD_NAME` AS `SHOW_TABLE_STATUS_FIELD_NAME`
 250     //
 251     // on non-Windows servers,
 252     // added BINARY in the WHERE clause to force a case sensitive
 253     // comparison (if we are looking for the db Aa we don't want
 254     // to find the db aa)
 255     $this_databases = array_map('PMA_sqlAddslashes', $databases);
 256
 257     $sql = '
 258          SELECT *,
 259                 `TABLE_SCHEMA`       AS `Db`,
 260                 `TABLE_NAME`         AS `Name`,
 261                 `ENGINE`             AS `Engine`,
 262                 `ENGINE`             AS `Type`,
 263                 `VERSION`            AS `Version`,
 264                 `ROW_FORMAT`         AS `Row_format`,
 265                 `TABLE_ROWS`         AS `Rows`,
 266                 `AVG_ROW_LENGTH`     AS `Avg_row_length`,
 267                 `DATA_LENGTH`        AS `Data_length`,
 268                 `MAX_DATA_LENGTH`    AS `Max_data_length`,
 269                 `INDEX_LENGTH`       AS `Index_length`,
 270                 `DATA_FREE`          AS `Data_free`,
 271                 `AUTO_INCREMENT`     AS `Auto_increment`,
 272                 `CREATE_TIME`        AS `Create_time`,
 273                 `UPDATE_TIME`        AS `Update_time`,
 274                 `CHECK_TIME`         AS `Check_time`,
 275                 `TABLE_COLLATION`    AS `Collation`,
 276                 `CHECKSUM`           AS `Checksum`,
 277                 `CREATE_OPTIONS`     AS `Create_options`,
 278                 `TABLE_COMMENT`      AS `Comment`
 279            FROM `information_schema`.`TABLES`
 280           WHERE ' . (PMA_IS_WINDOWS ? '' : 'BINARY') . ' `TABLE_SCHEMA` IN (\'' . implode("', '", $this_databases) . '\')
 281             ' . $sql_where_table;
 282
 283     if ($limit_count) {
 284         $sql .= ' LIMIT ' . $limit_count . ' OFFSET ' . $limit_offset;
 285     }
 286     $tables = PMA_DBI_fetch_result($sql, array('TABLE_SCHEMA', 'TABLE_NAME'),
 287         null, $link);
 288     unset($sql_where_table, $sql);
 289
 290     // If permissions are wrong on even one database directory,
 291     // information_schema does not return any table info for any database
 292     // this is why we fall back to SHOW TABLE STATUS even for MySQL >= 50002
 293     if (empty($tables)) {
 294         foreach ($databases as $each_database) {
 295             if (true === $tbl_is_group) {
 296                 $sql = 'SHOW TABLE STATUS FROM '
 297                     . PMA_backquote($each_database)
 298                     .' LIKE \'' . PMA_escape_mysql_wildcards(addslashes($table)) . '%\'';
 299             } else {
 300                 $sql = 'SHOW TABLE STATUS FROM '
 301                     . PMA_backquote($each_database) . ';';
 302             }
 303             $each_tables = PMA_DBI_fetch_result($sql, 'Name', null, $link);
 304             foreach ($each_tables as $table_name => $each_table) {
 305                 if ('comment' === $tbl_is_group
 306                   && 0 === strpos($each_table['Comment'], $table))
 307                 {
 308                     // remove table from list
 309                     unset($each_tables[$table_name]);
 310                     continue;
 311                 }
 312
 313                 if (! isset($each_tables[$table_name]['Type'])
 314                   && isset($each_tables[$table_name]['Engine'])) {
 315                     // pma BC, same parts of PMA still uses 'Type'
 316                     $each_tables[$table_name]['Type']
 317                         =& $each_tables[$table_name]['Engine'];
 318                 } elseif (! isset($each_tables[$table_name]['Engine'])
 319                   && isset($each_tables[$table_name]['Type'])) {
 320                     // old MySQL reports Type, newer MySQL reports Engine
 321                     $each_tables[$table_name]['Engine']
 322                         =& $each_tables[$table_name]['Type'];
 323                 }
 324
 325                 // MySQL forward compatibility
 326                 // so pma could use this array as if every server is of version >5.0
 327                 $each_tables[$table_name]['TABLE_SCHEMA']      = $each_database;
 328                 $each_tables[$table_name]['TABLE_NAME']        =& $each_tables[$table_name]['Name'];
 329                 $each_tables[$table_name]['ENGINE']            =& $each_tables[$table_name]['Engine'];
 330                 $each_tables[$table_name]['VERSION']           =& $each_tables[$table_name]['Version'];
 331                 $each_tables[$table_name]['ROW_FORMAT']        =& $each_tables[$table_name]['Row_format'];
 332                 $each_tables[$table_name]['TABLE_ROWS']        =& $each_tables[$table_name]['Rows'];
 333                 $each_tables[$table_name]['AVG_ROW_LENGTH']    =& $each_tables[$table_name]['Avg_row_length'];
 334                 $each_tables[$table_name]['DATA_LENGTH']       =& $each_tables[$table_name]['Data_length'];
 335                 $each_tables[$table_name]['MAX_DATA_LENGTH']   =& $each_tables[$table_name]['Max_data_length'];
 336                 $each_tables[$table_name]['INDEX_LENGTH']      =& $each_tables[$table_name]['Index_length'];
 337                 $each_tables[$table_name]['DATA_FREE']         =& $each_tables[$table_name]['Data_free'];
 338                 $each_tables[$table_name]['AUTO_INCREMENT']    =& $each_tables[$table_name]['Auto_increment'];
 339                 $each_tables[$table_name]['CREATE_TIME']       =& $each_tables[$table_name]['Create_time'];
 340                 $each_tables[$table_name]['UPDATE_TIME']       =& $each_tables[$table_name]['Update_time'];
 341                 $each_tables[$table_name]['CHECK_TIME']        =& $each_tables[$table_name]['Check_time'];
 342                 $each_tables[$table_name]['TABLE_COLLATION']   =& $each_tables[$table_name]['Collation'];
 343                 $each_tables[$table_name]['CHECKSUM']          =& $each_tables[$table_name]['Checksum'];
 344                 $each_tables[$table_name]['CREATE_OPTIONS']    =& $each_tables[$table_name]['Create_options'];
 345                 $each_tables[$table_name]['TABLE_COMMENT']     =& $each_tables[$table_name]['Comment'];
 346
 347                 if (strtoupper($each_tables[$table_name]['Comment']) === 'VIEW') {
 348                     $each_tables[$table_name]['TABLE_TYPE'] = 'VIEW';
 349                 } else {
 350                     /**
 351                      * @todo difference between 'TEMPORARY' and 'BASE TABLE' but how to detect?
 352                      */
 353                     $each_tables[$table_name]['TABLE_TYPE'] = 'BASE TABLE';
 354                 }
 355             }
 356
 357             $tables[$each_database] = $each_tables;
 358         }
 359     }
 360
 361     if ($GLOBALS['cfg']['NaturalOrder']) {
 362         foreach ($tables as $key => $val) {
 363             uksort($tables[$key], 'strnatcasecmp');
 364         }
 365     }
 366
 367     if (! is_array($database)) {
 368         if (isset($tables[$database])) {
 369             return $tables[$database];
 370         } elseif (isset($tables[strtolower($database)])) {
 371             // on windows with lower_case_table_names = 1
 372             // MySQL returns
 373             // with SHOW DATABASES or information_schema.SCHEMATA: `Test`
 374             // but information_schema.TABLES gives `test`
 375             // bug #1436171
 376             // http://sf.net/support/tracker.php?aid=1436171
 377             return $tables[strtolower($database)];
 378         } else {
 379             return $tables;
 380         }
 381     } else {
 382         return $tables;
 383     }
 384 }
 385
 386 /**
 387  * returns array with databases containing extended infos about them
 388  *
 389  * @todo    move into PMA_List_Database?
 390  * @param   string      $databases      database
 391  * @param   boolean     $force_stats    retrieve stats also for MySQL < 5
 392  * @param   resource    $link           mysql link
 393  * @param   string      $sort_by        collumn to order by
 394  * @param   string      $sort_order     ASC or DESC
 395  * @param   integer     $limit_offset   starting offset for LIMIT
 396  * @param   bool|int    $limit_count    row count for LIMIT or true for $GLOBALS['cfg']['MaxDbList']
 397  * @return  array       $databases
 398  */
 399 function PMA_DBI_get_databases_full($database = null, $force_stats = false,
 400     $link = null, $sort_by = 'SCHEMA_NAME', $sort_order = 'ASC',
 401     $limit_offset = 0, $limit_count = false)
 402 {
 403     $sort_order = strtoupper($sort_order);
 404
 405     if (true === $limit_count) {
 406         $limit_count = $GLOBALS['cfg']['MaxDbList'];
 407     }
 408
 409     // initialize to avoid errors when there are no databases
 410     $databases = array();
 411
 412     $apply_limit_and_order_manual = true;
 413
 414     /**
 415      * if $GLOBALS['cfg']['NaturalOrder'] is enabled, we cannot use LIMIT
 416      * cause MySQL does not support natural ordering, we have to do it afterward
 417      */
 418     if ($GLOBALS['cfg']['NaturalOrder']) {
 419         $limit = '';
 420     } else {
 421         if ($limit_count) {
 422             $limit = ' LIMIT ' . $limit_count . ' OFFSET ' . $limit_offset;
 423         }
 424
 425         $apply_limit_and_order_manual = false;
 426     }
 427
 428     // get table information from information_schema
 429     if ($database) {
 430         $sql_where_schema = 'WHERE `SCHEMA_NAME` LIKE \''
 431             . addslashes($database) . '\'';
 432     } else {
 433         $sql_where_schema = '';
 434     }
 435
 436     // for PMA bc:
 437     // `SCHEMA_FIELD_NAME` AS `SHOW_TABLE_STATUS_FIELD_NAME`
 438     $sql = '
 439          SELECT `information_schema`.`SCHEMATA`.*';
 440     if ($force_stats) {
 441         $sql .= ',
 442                 COUNT(`information_schema`.`TABLES`.`TABLE_SCHEMA`)
 443                     AS `SCHEMA_TABLES`,
 444                 SUM(`information_schema`.`TABLES`.`TABLE_ROWS`)
 445                     AS `SCHEMA_TABLE_ROWS`,
 446                 SUM(`information_schema`.`TABLES`.`DATA_LENGTH`)
 447                     AS `SCHEMA_DATA_LENGTH`,
 448                 SUM(`information_schema`.`TABLES`.`MAX_DATA_LENGTH`)
 449                     AS `SCHEMA_MAX_DATA_LENGTH`,
 450                 SUM(`information_schema`.`TABLES`.`INDEX_LENGTH`)
 451                     AS `SCHEMA_INDEX_LENGTH`,
 452                 SUM(`information_schema`.`TABLES`.`DATA_LENGTH`
 453                   + `information_schema`.`TABLES`.`INDEX_LENGTH`)
 454                     AS `SCHEMA_LENGTH`,
 455                 SUM(`information_schema`.`TABLES`.`DATA_FREE`)
 456                     AS `SCHEMA_DATA_FREE`';
 457     }
 458     $sql .= '
 459            FROM `information_schema`.`SCHEMATA`';
 460     if ($force_stats) {
 461         $sql .= '
 462       LEFT JOIN `information_schema`.`TABLES`
 463              ON BINARY `information_schema`.`TABLES`.`TABLE_SCHEMA`
 464               = BINARY `information_schema`.`SCHEMATA`.`SCHEMA_NAME`';
 465     }
 466     $sql .= '
 467           ' . $sql_where_schema . '
 468        GROUP BY BINARY `information_schema`.`SCHEMATA`.`SCHEMA_NAME`
 469        ORDER BY BINARY ' . PMA_backquote($sort_by) . ' ' . $sort_order
 470        . $limit;
 471     $databases = PMA_DBI_fetch_result($sql, 'SCHEMA_NAME', null, $link);
 472
 473     $mysql_error = PMA_DBI_getError($link);
 474     if (! count($databases) && $GLOBALS['errno']) {
 475         PMA_mysqlDie($mysql_error, $sql);
 476     }
 477
 478     // display only databases also in official database list
 479     // f.e. to apply hide_db and only_db
 480     $drops = array_diff(array_keys($databases), $GLOBALS['PMA_List_Database']->items);
 481     if (count($drops)) {
 482         foreach ($drops as $drop) {
 483             unset($databases[$drop]);
 484         }
 485         unset($drop);
 486     }
 487     unset($sql_where_schema, $sql, $drops);
 488
 489     /**
 490      * apply limit and order manually now
 491      * (caused by older MySQL < 5 or $GLOBALS['cfg']['NaturalOrder'])
 492      */
 493     if ($apply_limit_and_order_manual) {
 494
 495         /**
 496          * first apply ordering
 497          */
 498         if ($GLOBALS['cfg']['NaturalOrder']) {
 499             $sorter = 'strnatcasecmp';
 500         } else {
 501             $sorter = 'strcasecmp';
 502         }
 503
 504         // produces f.e.:
 505         // return -1 * strnatcasecmp($a["SCHEMA_TABLES"], $b["SCHEMA_TABLES"])
 506         $sort_function = '
 507             return ' . ($sort_order == 'ASC' ? 1 : -1) . ' * ' . $sorter . '($a["' . $sort_by . '"], $b["' . $sort_by . '"]);
 508         ';
 509
 510         usort($databases, create_function('$a, $b', $sort_function));
 511
 512         /**
 513          * now apply limit
 514          */
 515         if ($limit_count) {
 516             $databases = array_slice($databases, $limit_offset, $limit_count);
 517         }
 518     }
 519
 520     return $databases;
 521 }
 522
 523 /**
 524  * returns detailed array with all columns for given table in database,
 525  * or all tables/databases
 526  *
 527  * @param   string  $database   name of database
 528  * @param   string  $table      name of table to retrieve columns from
 529  * @param   string  $column     name of specific column
 530  * @param   mixed   $link       mysql link resource
 531  */
 532 function PMA_DBI_get_columns_full($database = null, $table = null,
 533     $column = null, $link = null)
 534 {
 535     $columns = array();
 536
 537     $sql_wheres = array();
 538     $array_keys = array();
 539
 540     // get columns information from information_schema
 541     if (null !== $database) {
 542         $sql_wheres[] = '`TABLE_SCHEMA` = \'' . addslashes($database) . '\' ';
 543     } else {
 544         $array_keys[] = 'TABLE_SCHEMA';
 545     }
 546     if (null !== $table) {
 547         $sql_wheres[] = '`TABLE_NAME` = \'' . addslashes($table) . '\' ';
 548     } else {
 549         $array_keys[] = 'TABLE_NAME';
 550     }
 551     if (null !== $column) {
 552         $sql_wheres[] = '`COLUMN_NAME` = \'' . addslashes($column) . '\' ';
 553     } else {
 554         $array_keys[] = 'COLUMN_NAME';
 555     }
 556
 557     // for PMA bc:
 558     // `[SCHEMA_FIELD_NAME]` AS `[SHOW_FULL_COLUMNS_FIELD_NAME]`
 559     $sql = '
 560          SELECT *,
 561                 `COLUMN_NAME`       AS `Field`,
 562                 `COLUMN_TYPE`       AS `Type`,
 563                 `COLLATION_NAME`    AS `Collation`,
 564                 `IS_NULLABLE`       AS `Null`,
 565                 `COLUMN_KEY`        AS `Key`,
 566                 `COLUMN_DEFAULT`    AS `Default`,
 567                 `EXTRA`             AS `Extra`,
 568                 `PRIVILEGES`        AS `Privileges`,
 569                 `COLUMN_COMMENT`    AS `Comment`
 570            FROM `information_schema`.`COLUMNS`';
 571     if (count($sql_wheres)) {
 572         $sql .= "\n" . ' WHERE ' . implode(' AND ', $sql_wheres);
 573     }
 574
 575     return PMA_DBI_fetch_result($sql, $array_keys, null, $link);
 576 }
 577
 578 /**
 579  * @todo should only return columns names, for more info use PMA_DBI_get_columns_full()
 580  *
 581  * @deprecated by PMA_DBI_get_columns() or PMA_DBI_get_columns_full()
 582  * @param   string  $database   name of database
 583  * @param   string  $table      name of table to retrieve columns from
 584  * @param   mixed   $link       mysql link resource
 585  * @return  array   column info
 586  */
 587 function PMA_DBI_get_fields($database, $table, $link = null)
 588 {
 589     // here we use a try_query because when coming from
 590     // tbl_create + tbl_properties.inc.php, the table does not exist
 591     $fields = PMA_DBI_fetch_result(
 592         'SHOW FULL COLUMNS
 593         FROM ' . PMA_backquote($database) . '.' . PMA_backquote($table),
 594         null, null, $link);
 595     if (! is_array($fields) || count($fields) < 1) {
 596         return false;
 597     }
 598     return $fields;
 599 }
 600
 601 /**
 602  * array PMA_DBI_get_columns(string $database, string $table, bool $full = false, mysql db link $link = null)
 603  *
 604  * @param   string  $database   name of database
 605  * @param   string  $table      name of table to retrieve columns from
 606  * @param   boolean $full       wether to return full info or only column names
 607  * @param   mixed   $link       mysql link resource
 608  * @return  array   column names
 609  */
 610 function PMA_DBI_get_columns($database, $table, $full = false, $link = null)
 611 {
 612     $fields = PMA_DBI_fetch_result(
 613         'SHOW ' . ($full ? 'FULL' : '') . ' COLUMNS
 614         FROM ' . PMA_backquote($database) . '.' . PMA_backquote($table),
 615         'Field', ($full ? null : 'Field'), $link);
 616     if (! is_array($fields) || count($fields) < 1) {
 617         return false;
 618     }
 619     return $fields;
 620 }
 621
 622 /**
 623  * returns value of given mysql server variable
 624  *
 625  * @param   string  $var    mysql server variable name
 626  * @param   int     $type   PMA_DBI_GETVAR_SESSION|PMA_DBI_GETVAR_GLOBAL
 627  * @param   mixed   $link   mysql link resource|object
 628  * @return  mixed   value for mysql server variable
 629  */
 630 function PMA_DBI_get_variable($var, $type = PMA_DBI_GETVAR_SESSION, $link = null)
 631 {
 632     if ($link === null) {
 633         if (isset($GLOBALS['userlink'])) {
 634             $link = $GLOBALS['userlink'];
 635         } else {
 636             return false;
 637         }
 638     }
 639
 640     switch ($type) {
 641         case PMA_DBI_GETVAR_SESSION:
 642             $modifier = ' SESSION';
 643             break;
 644         case PMA_DBI_GETVAR_GLOBAL:
 645             $modifier = ' GLOBAL';
 646             break;
 647         default:
 648             $modifier = '';
 649     }
 650     return PMA_DBI_fetch_value(
 651         'SHOW' . $modifier . ' VARIABLES LIKE \'' . $var . '\';', 0, 1, $link);
 652 }
 653
 654 /**
 655  * @uses    ./libraries/charset_conversion.lib.php
 656  * @uses    PMA_DBI_QUERY_STORE
 657  * @uses    PMA_MYSQL_INT_VERSION
 658  * @uses    PMA_MYSQL_STR_VERSION
 659  * @uses    PMA_DBI_GETVAR_SESSION
 660  * @uses    PMA_DBI_fetch_value()
 661  * @uses    PMA_DBI_query()
 662  * @uses    PMA_DBI_get_variable()
 663  * @uses    $GLOBALS['collation_connection']
 664  * @uses    $GLOBALS['charset_connection']
 665  * @uses    $GLOBALS['available_languages']
 666  * @uses    $GLOBALS['mysql_charset_map']
 667  * @uses    $GLOBALS['charset']
 668  * @uses    $GLOBALS['lang']
 669  * @uses    $GLOBALS['cfg']['Lang']
 670  * @uses    $GLOBALS['cfg']['ColumnTypes']
 671  * @uses    defined()
 672  * @uses    explode()
 673  * @uses    sprintf()
 674  * @uses    intval()
 675  * @uses    define()
 676  * @uses    defined()
 677  * @uses    substr()
 678  * @uses    count()
 679  * @param   mixed   $link   mysql link resource|object
 680  * @param   boolean $is_controluser
 681  */
 682 function PMA_DBI_postConnect($link, $is_controluser = false)
 683 {
 684     if (!defined('PMA_MYSQL_INT_VERSION')) {
 685         $mysql_version = PMA_DBI_fetch_value(
 686             'SELECT VERSION()', 0, 0, $link, PMA_DBI_QUERY_STORE);
 687         if ($mysql_version) {
 688             $match = explode('.', $mysql_version);
 689             define('PMA_MYSQL_INT_VERSION',
 690                 (int) sprintf('%d%02d%02d', $match[0], $match[1],
 691                         intval($match[2])));
 692             define('PMA_MYSQL_STR_VERSION', $mysql_version);
 693             unset($mysql_version, $match);
 694         } else {
 695             define('PMA_MYSQL_INT_VERSION', 32332);
 696             define('PMA_MYSQL_STR_VERSION', '3.23.32');
 697         }
 698     }
 699
 700     if (!defined('PMA_ENGINE_KEYWORD')) {
 701         define('PMA_ENGINE_KEYWORD','ENGINE');
 702     }
 703
 704     $mysql_charset = $GLOBALS['mysql_charset_map'][$GLOBALS['charset']];
 705     if ($is_controluser
 706       || empty($GLOBALS['collation_connection'])
 707       || (strpos($GLOBALS['collation_connection'], '_')
 708             ? substr($GLOBALS['collation_connection'], 0, strpos($GLOBALS['collation_connection'], '_'))
 709             : $GLOBALS['collation_connection']) == $mysql_charset) {
 710
 711         PMA_DBI_query('SET NAMES ' . $mysql_charset . ';', $link,
 712             PMA_DBI_QUERY_STORE);
 713     } else {
 714         PMA_DBI_query('SET CHARACTER SET ' . $mysql_charset . ';', $link,
 715             PMA_DBI_QUERY_STORE);
 716     }
 717     if (!empty($GLOBALS['collation_connection'])) {
 718         PMA_DBI_query('SET collation_connection = \'' . $GLOBALS['collation_connection'] . '\';',
 719             $link, PMA_DBI_QUERY_STORE);
 720     }
 721     if (!$is_controluser) {
 722         $GLOBALS['collation_connection'] = PMA_DBI_get_variable('collation_connection',
 723             PMA_DBI_GETVAR_SESSION, $link);
 724         $GLOBALS['charset_connection']   = PMA_DBI_get_variable('character_set_connection',
 725             PMA_DBI_GETVAR_SESSION, $link);
 726     }
 727
 728     // Add some field types to the list, this needs to be done once per session!
 729     if (!in_array('BINARY', $GLOBALS['cfg']['ColumnTypes'])) {
 730         $GLOBALS['cfg']['ColumnTypes'][] = 'BINARY';
 731     }
 732     if (!in_array('VARBINARY', $GLOBALS['cfg']['ColumnTypes'])) {
 733         $GLOBALS['cfg']['ColumnTypes'][] = 'VARBINARY';
 734     }
 735 }
 736
 737 /**
 738  * returns a single value from the given result or query,
 739  * if the query or the result has more than one row or field
 740  * the first field of the first row is returned
 741  *
 742  * <code>
 743  * $sql = 'SELECT `name` FROM `user` WHERE `id` = 123';
 744  * $user_name = PMA_DBI_fetch_value($sql);
 745  * // produces
 746  * // $user_name = 'John Doe'
 747  * </code>
 748  *
 749  * @uses    is_string()
 750  * @uses    is_int()
 751  * @uses    PMA_DBI_try_query()
 752  * @uses    PMA_DBI_num_rows()
 753  * @uses    PMA_DBI_fetch_row()
 754  * @uses    PMA_DBI_fetch_assoc()
 755  * @uses    PMA_DBI_free_result()
 756  * @param   string|mysql_result $result query or mysql result
 757  * @param   integer             $row_number row to fetch the value from,
 758  *                                      starting at 0, with 0 beeing default
 759  * @param   integer|string      $field  field to fetch the value from,
 760  *                                      starting at 0, with 0 beeing default
 761  * @param   resource            $link   mysql link
 762  * @param   mixed               $options
 763  * @return  mixed               value of first field in first row from result
 764  *                              or false if not found
 765  */
 766 function PMA_DBI_fetch_value($result, $row_number = 0, $field = 0, $link = null, $options = 0) {
 767     $value = false;
 768
 769     if (is_string($result)) {
 770         $result = PMA_DBI_try_query($result, $link, $options | PMA_DBI_QUERY_STORE);
 771     }
 772
 773     // return false if result is empty or false
 774     // or requested row is larger than rows in result
 775     if (PMA_DBI_num_rows($result) < ($row_number + 1)) {
 776         return $value;
 777     }
 778
 779     // if $field is an integer use non associative mysql fetch function
 780     if (is_int($field)) {
 781         $fetch_function = 'PMA_DBI_fetch_row';
 782     } else {
 783         $fetch_function = 'PMA_DBI_fetch_assoc';
 784     }
 785
 786     // get requested row
 787     for ($i = 0; $i <= $row_number; $i++) {
 788         $row = $fetch_function($result);
 789     }
 790     PMA_DBI_free_result($result);
 791
 792     // return requested field
 793     if (isset($row[$field])) {
 794         $value = $row[$field];
 795     }
 796     unset($row);
 797
 798     return $value;
 799 }
 800
 801 /**
 802  * returns only the first row from the result
 803  *
 804  * <code>
 805  * $sql = 'SELECT * FROM `user` WHERE `id` = 123';
 806  * $user = PMA_DBI_fetch_single_row($sql);
 807  * // produces
 808  * // $user = array('id' => 123, 'name' => 'John Doe')
 809  * </code>
 810  *
 811  * @uses    is_string()
 812  * @uses    PMA_DBI_try_query()
 813  * @uses    PMA_DBI_num_rows()
 814  * @uses    PMA_DBI_fetch_row()
 815  * @uses    PMA_DBI_fetch_assoc()
 816  * @uses    PMA_DBI_fetch_array()
 817  * @uses    PMA_DBI_free_result()
 818  * @param   string|mysql_result $result query or mysql result
 819  * @param   string              $type   NUM|ASSOC|BOTH
 820  *                                      returned array should either numeric
 821  *                                      associativ or booth
 822  * @param   resource            $link   mysql link
 823  * @param   mixed               $options
 824  * @return  array|boolean       first row from result
 825  *                              or false if result is empty
 826  */
 827 function PMA_DBI_fetch_single_row($result, $type = 'ASSOC', $link = null, $options = 0) {
 828     if (is_string($result)) {
 829         $result = PMA_DBI_try_query($result, $link, $options | PMA_DBI_QUERY_STORE);
 830     }
 831
 832     // return null if result is empty or false
 833     if (! PMA_DBI_num_rows($result)) {
 834         return false;
 835     }
 836
 837     switch ($type) {
 838         case 'NUM' :
 839             $fetch_function = 'PMA_DBI_fetch_row';
 840             break;
 841         case 'ASSOC' :
 842             $fetch_function = 'PMA_DBI_fetch_assoc';
 843             break;
 844         case 'BOTH' :
 845         default :
 846             $fetch_function = 'PMA_DBI_fetch_array';
 847             break;
 848     }
 849
 850     $row = $fetch_function($result);
 851     PMA_DBI_free_result($result);
 852     return $row;
 853 }
 854
 855 /**
 856  * returns all rows in the resultset in one array
 857  *
 858  * <code>
 859  * $sql = 'SELECT * FROM `user`';
 860  * $users = PMA_DBI_fetch_result($sql);
 861  * // produces
 862  * // $users[] = array('id' => 123, 'name' => 'John Doe')
 863  *
 864  * $sql = 'SELECT `id`, `name` FROM `user`';
 865  * $users = PMA_DBI_fetch_result($sql, 'id');
 866  * // produces
 867  * // $users['123'] = array('id' => 123, 'name' => 'John Doe')
 868  *
 869  * $sql = 'SELECT `id`, `name` FROM `user`';
 870  * $users = PMA_DBI_fetch_result($sql, 0);
 871  * // produces
 872  * // $users['123'] = array(0 => 123, 1 => 'John Doe')
 873  *
 874  * $sql = 'SELECT `id`, `name` FROM `user`';
 875  * $users = PMA_DBI_fetch_result($sql, 'id', 'name');
 876  * // or
 877  * $users = PMA_DBI_fetch_result($sql, 0, 1);
 878  * // produces
 879  * // $users['123'] = 'John Doe'
 880  *
 881  * $sql = 'SELECT `name` FROM `user`';
 882  * $users = PMA_DBI_fetch_result($sql);
 883  * // produces
 884  * // $users[] = 'John Doe'
 885  * </code>
 886  *
 887  * @uses    is_string()
 888  * @uses    is_int()
 889  * @uses    PMA_DBI_try_query()
 890  * @uses    PMA_DBI_num_rows()
 891  * @uses    PMA_DBI_num_fields()
 892  * @uses    PMA_DBI_fetch_row()
 893  * @uses    PMA_DBI_fetch_assoc()
 894  * @uses    PMA_DBI_free_result()
 895  * @param   string|mysql_result $result query or mysql result
 896  * @param   string|integer      $key    field-name or offset
 897  *                                      used as key for array
 898  * @param   string|integer      $value  value-name or offset
 899  *                                      used as value for array
 900  * @param   resource            $link   mysql link
 901  * @param   mixed               $options
 902  * @return  array               resultrows or values indexed by $key
 903  */
 904 function PMA_DBI_fetch_result($result, $key = null, $value = null,
 905     $link = null, $options = 0)
 906 {
 907     $resultrows = array();
 908
 909     if (is_string($result)) {
 910         $result = PMA_DBI_try_query($result, $link, $options);
 911     }
 912
 913     // return empty array if result is empty or false
 914     if (! $result) {
 915         return $resultrows;
 916     }
 917
 918     $fetch_function = 'PMA_DBI_fetch_assoc';
 919
 920     // no nested array if only one field is in result
 921     if (null === $key && 1 === PMA_DBI_num_fields($result)) {
 922         $value = 0;
 923         $fetch_function = 'PMA_DBI_fetch_row';
 924     }
 925
 926     // if $key is an integer use non associative mysql fetch function
 927     if (is_int($key)) {
 928         $fetch_function = 'PMA_DBI_fetch_row';
 929     }
 930
 931     if (null === $key && null === $value) {
 932         while ($row = $fetch_function($result)) {
 933             $resultrows[] = $row;
 934         }
 935     } elseif (null === $key) {
 936         while ($row = $fetch_function($result)) {
 937             $resultrows[] = $row[$value];
 938         }
 939     } elseif (null === $value) {
 940         if (is_array($key)) {
 941             while ($row = $fetch_function($result)) {
 942                 $result_target =& $resultrows;
 943                 foreach ($key as $key_index) {
 944                     if (! isset($result_target[$row[$key_index]])) {
 945                         $result_target[$row[$key_index]] = array();
 946                     }
 947                     $result_target =& $result_target[$row[$key_index]];
 948                 }
 949                 $result_target = $row;
 950             }
 951         } else {
 952             while ($row = $fetch_function($result)) {
 953                 $resultrows[$row[$key]] = $row;
 954             }
 955         }
 956     } else {
 957         if (is_array($key)) {
 958             while ($row = $fetch_function($result)) {
 959                 $result_target =& $resultrows;
 960                 foreach ($key as $key_index) {
 961                     if (! isset($result_target[$row[$key_index]])) {
 962                         $result_target[$row[$key_index]] = array();
 963                     }
 964                     $result_target =& $result_target[$row[$key_index]];
 965                 }
 966                 $result_target = $row[$value];
 967             }
 968         } else {
 969             while ($row = $fetch_function($result)) {
 970                 $resultrows[$row[$key]] = $row[$value];
 971             }
 972         }
 973     }
 974
 975     PMA_DBI_free_result($result);
 976     return $resultrows;
 977 }
 978
 979 /**
 980  * return default table engine for given database
 981  *
 982  * @return  string  default table engine
 983  */
 984 function PMA_DBI_get_default_engine()
 985 {
 986     return PMA_DBI_fetch_value('SHOW VARIABLES LIKE \'storage_engine\';', 0, 1);
 987 }
 988
 989 /**
 990  * Get supported SQL compatibility modes
 991  *
 992  * @return  array   supported SQL compatibility modes
 993  */
 994 function PMA_DBI_getCompatibilities()
 995 {
 996     $compats = array('NONE');
 997     $compats[] = 'ANSI';
 998     $compats[] = 'DB2';
 999     $compats[] = 'MAXDB';
1000     $compats[] = 'MYSQL323';
1001     $compats[] = 'MYSQL40';
1002     $compats[] = 'MSSQL';
1003     $compats[] = 'ORACLE';
1004     // removed; in MySQL 5.0.33, this produces exports that
1005     // can't be read by POSTGRESQL (see our bug #1596328)
1006     //$compats[] = 'POSTGRESQL';
1007     $compats[] = 'TRADITIONAL';
1008
1009     return $compats;
1010 }
1011
1012 /**
1013  * returns warnings for last query
1014  *
1015  * @uses    $GLOBALS['userlink']
1016  * @uses    PMA_DBI_fetch_result()
1017  * @param   resource mysql link  $link   mysql link resource
1018  * @return  array   warnings
1019  */
1020 function PMA_DBI_get_warnings($link = null)
1021 {
1022     if (empty($link)) {
1023         if (isset($GLOBALS['userlink'])) {
1024             $link = $GLOBALS['userlink'];
1025         } else {
1026             return array();
1027         }
1028     }
1029
1030     return PMA_DBI_fetch_result('SHOW WARNINGS', null, null, $link);
1031 }
1032
1033 /**
1034  * returns true (int > 0) if current user is superuser
1035  * otherwise 0
1036  *
1037  * @return integer  $is_superuser
1038  */
1039 function PMA_isSuperuser() {
1040     return PMA_DBI_try_query('SELECT COUNT(*) FROM mysql.user',
1041         $GLOBALS['userlink'], PMA_DBI_QUERY_STORE);
1042 }
1043
1044
1045 /**
1046  * returns an array of PROCEDURE or FUNCTION names for a db
1047  *
1048  * @uses    PMA_DBI_free_result()
1049  * @param   string              $db     db name
1050  * @param   string              $which  PROCEDURE | FUNCTION
1051  * @param   resource            $link   mysql link
1052  *
1053  * @return  array   the procedure names or function names
1054  */
1055 function PMA_DBI_get_procedures_or_functions($db, $which, $link = null) {
1056
1057     $shows = PMA_DBI_fetch_result('SHOW ' . $which . ' STATUS;', null, null, $link);
1058     $result = array();
1059     foreach ($shows as $one_show) {
1060         if ($one_show['Db'] == $db && $one_show['Type'] == $which) {
1061             $result[] = $one_show['Name'];
1062         }
1063     }
1064     return($result);
1065 }
1066
1067 /**
1068  * returns the definition of a specific PROCEDURE or FUNCTION
1069  *
1070  * @uses    PMA_DBI_fetch_value()
1071  * @param   string              $db     db name
1072  * @param   string              $which  PROCEDURE | FUNCTION
1073  * @param   string              $proc_or_function_name  the procedure name or function name
1074  * @param   resource            $link   mysql link
1075  *
1076  * @return  string              the procedure's or function's definition
1077  */
1078 function PMA_DBI_get_procedure_or_function_def($db, $which, $proc_or_function_name, $link = null) {
1079
1080     $returned_field = array('PROCEDURE' => 'Create Procedure', 'FUNCTION' => 'Create Function');
1081     $query = 'SHOW CREATE ' . $which . ' ' . PMA_backquote($db) . '.' . PMA_backquote($proc_or_function_name);
1082     return(PMA_DBI_fetch_value($query, 0, $returned_field[$which]));
1083 }
1084
1085 /**
1086  * returns details about the TRIGGERs of a specific table
1087  *
1088  * @uses    PMA_DBI_fetch_result()
1089  * @param   string              $db     db name
1090  * @param   string              $table  table name
1091  *
1092  * @return  array               information about triggers (may be empty)
1093  */
1094 function PMA_DBI_get_triggers($db, $table) {
1095
1096     $result = array();
1097
1098     $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 EVENT_OBJECT_SCHEMA= '" . PMA_sqlAddslashes($db,true) . "' and EVENT_OBJECT_TABLE = '" . PMA_sqlAddslashes($table, true) . "';");
1099
1100     if ($triggers) {
1101         $delimiter = '//';
1102         foreach ($triggers as $trigger) {
1103             $one_result = array();
1104             $one_result['name'] = $trigger['TRIGGER_NAME'];
1105             $one_result['action_timing'] = $trigger['ACTION_TIMING'];
1106             $one_result['event_manipulation'] = $trigger['EVENT_MANIPULATION'];
1107
1108             $one_result['full_trigger_name'] = PMA_backquote($trigger['TRIGGER_SCHEMA']) . '.' . PMA_backquote($trigger['TRIGGER_NAME']);
1109             $one_result['drop'] = 'DROP TRIGGER ' . $one_result['full_trigger_name'];
1110             $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";
1111
1112             $result[] = $one_result;
1113         }
1114     }
1115     return($result);
1116 }
1117 ?>