示例#1
0
if (isset($_REQUEST['submitorderby']) && !empty($_REQUEST['order_field'])) {
    list($sql_query, $result) = PMA_getQueryAndResultForReorderingTable();
}
// end if
/**
 * A partition operation has been requested by the user
 */
$sql_query = '';
if (isset($_REQUEST['submit_partition']) && !empty($_REQUEST['partition_operation'])) {
    list($sql_query, $result) = PMA_getQueryAndResultForPartition();
}
// end if
if ($reread_info) {
    // to avoid showing the old value (for example the AUTO_INCREMENT) after
    // a change, clear the cache
    PMA_Table::$cache = array();
    $page_checksum = $checksum = $delay_key_write = 0;
    include 'libraries/tbl_info.inc.php';
}
unset($reread_info);
if (isset($result) && empty($message_to_show)) {
    // set to success by default, because result set could be empty
    // (for example, a table rename)
    $_type = 'success';
    if (empty($_message)) {
        $_message = $result ? PMA_Message::success(__('Your SQL query has been executed successfully.')) : PMA_Message::error(__('Error'));
        // $result should exist, regardless of $_message
        $_type = $result ? 'success' : 'error';
        if (isset($GLOBALS['ajax_request']) && $GLOBALS['ajax_request'] == true) {
            $response = PMA_Response::getInstance();
            $response->isSuccess($_message->isSuccess());
/**
 * returns array of all tables in given db or dbs
 * this function expects unquoted names:
 * RIGHT: my_database
 * WRONG: `my_database`
 * WRONG: my\_database
 * if $tbl_is_group is true, $table is used as filter for table names
 * if $tbl_is_group is 'comment, $table is used as filter for table comments
 *
 * <code>
 * PMA_DBI_get_tables_full('my_database');
 * PMA_DBI_get_tables_full('my_database', 'my_table'));
 * PMA_DBI_get_tables_full('my_database', 'my_tables_', true));
 * PMA_DBI_get_tables_full('my_database', 'my_tables_', 'comment'));
 * </code>
 *
 * @todo    move into PMA_Table
 * @uses    PMA_DBI_fetch_result()
 * @uses    PMA_escape_mysql_wildcards()
 * @uses    PMA_backquote()
 * @uses    is_array()
 * @uses    addslashes()
 * @uses    strpos()
 * @uses    strtoupper()
 * @param   string          $databases      database
 * @param   string          $table          table
 * @param   boolean|string  $tbl_is_group   $table is a table group
 * @param   resource        $link           mysql link
 * @param   integer         $limit_offset   zero-based offset for the count
 * @param   boolean|integer $limit_count    number of tables to return
 * @param   string          $sort_by        table attribute to sort by
 * @param   string          $sort_order     direction to sort (ASC or DESC)
 * @return  array           list of tables in given db(s)
 */
function PMA_DBI_get_tables_full($database, $table = false, $tbl_is_group = false, $link = null, $limit_offset = 0, $limit_count = false, $sort_by = 'Name', $sort_order = 'ASC')
{
    require_once './libraries/Table.class.php';
    if (true === $limit_count) {
        $limit_count = $GLOBALS['cfg']['MaxTableList'];
    }
    // prepare and check parameters
    if (!is_array($database)) {
        $databases = array($database);
    } else {
        $databases = $database;
    }
    $tables = array();
    if (!$GLOBALS['cfg']['Server']['DisableIS']) {
        // get table information from information_schema
        if ($table) {
            if (true === $tbl_is_group) {
                $sql_where_table = 'AND `TABLE_NAME` LIKE \'' . PMA_escape_mysql_wildcards(addslashes($table)) . '%\'';
            } elseif ('comment' === $tbl_is_group) {
                $sql_where_table = 'AND `TABLE_COMMENT` LIKE \'' . PMA_escape_mysql_wildcards(addslashes($table)) . '%\'';
            } else {
                $sql_where_table = 'AND `TABLE_NAME` = \'' . addslashes($table) . '\'';
            }
        } else {
            $sql_where_table = '';
        }
        // for PMA bc:
        // `SCHEMA_FIELD_NAME` AS `SHOW_TABLE_STATUS_FIELD_NAME`
        //
        // on non-Windows servers,
        // added BINARY in the WHERE clause to force a case sensitive
        // comparison (if we are looking for the db Aa we don't want
        // to find the db aa)
        $this_databases = array_map('PMA_sqlAddslashes', $databases);
        $sql = '
           SELECT *,
                  `TABLE_SCHEMA`       AS `Db`,
                  `TABLE_NAME`         AS `Name`,
                  `ENGINE`             AS `Engine`,
                  `ENGINE`             AS `Type`,
                  `VERSION`            AS `Version`,
                  `ROW_FORMAT`         AS `Row_format`,
                  `TABLE_ROWS`         AS `Rows`,
                  `AVG_ROW_LENGTH`     AS `Avg_row_length`,
                  `DATA_LENGTH`        AS `Data_length`,
                  `MAX_DATA_LENGTH`    AS `Max_data_length`,
                  `INDEX_LENGTH`       AS `Index_length`,
                  `DATA_FREE`          AS `Data_free`,
                  `AUTO_INCREMENT`     AS `Auto_increment`,
                  `CREATE_TIME`        AS `Create_time`,
                  `UPDATE_TIME`        AS `Update_time`,
                  `CHECK_TIME`         AS `Check_time`,
                  `TABLE_COLLATION`    AS `Collation`,
                  `CHECKSUM`           AS `Checksum`,
                  `CREATE_OPTIONS`     AS `Create_options`,
                  `TABLE_COMMENT`      AS `Comment`
             FROM `information_schema`.`TABLES`
            WHERE ' . (PMA_IS_WINDOWS ? '' : 'BINARY') . ' `TABLE_SCHEMA` IN (\'' . implode("', '", $this_databases) . '\')
              ' . $sql_where_table;
        // Sort the tables
        if ($sort_by == 'Name' && $GLOBALS['cfg']['NaturalOrder']) {
            // This crazy bit of SQL was inspired by a post here:
            // http://forums.mysql.com/read.php?10,34908,35959#msg-35959
            // Find the longest table name
            $max_name_sql = "SELECT MAX(LENGTH(TABLE_NAME)) FROM `information_schema`.`TABLES`\n                           WHERE `TABLE_SCHEMA` IN ('" . implode("', '", $this_databases) . "')";
            $max_name_array = PMA_DBI_fetch_result($max_name_sql);
            $max_name_length = $max_name_array[0];
            // Put the CASE statement SQL together.
            $sql_case = '';
            for ($i = 1; $i < $max_name_length; $i++) {
                $sql_case .= " when substr(Name, {$i}) between '0' and '9' then {$i}";
            }
            $sql_case .= " ELSE {$max_name_length} end) ";
            // Add the CASE statement to the main SQL
            $sql .= " ORDER BY left(Name, (CASE ";
            $sql .= $sql_case . "-1) {$sort_order}, 0+substr(Name, CASE";
            $sql .= $sql_case . $sort_order;
        } else {
            // Just let MySQL sort as it normally does
            $sql .= " ORDER BY {$sort_by} {$sort_order}";
        }
        if ($limit_count) {
            $sql .= ' LIMIT ' . $limit_count . ' OFFSET ' . $limit_offset;
        }
        $tables = PMA_DBI_fetch_result($sql, array('TABLE_SCHEMA', 'TABLE_NAME'), null, $link);
        unset($sql_where_table, $sql);
    }
    // If permissions are wrong on even one database directory,
    // information_schema does not return any table info for any database
    // this is why we fall back to SHOW TABLE STATUS even for MySQL >= 50002
    if (empty($tables)) {
        foreach ($databases as $each_database) {
            if (true === $tbl_is_group) {
                $sql = 'SHOW TABLE STATUS FROM ' . PMA_backquote($each_database) . ' LIKE \'' . PMA_escape_mysql_wildcards(addslashes($table)) . '%\'';
            } else {
                $sql = 'SHOW TABLE STATUS FROM ' . PMA_backquote($each_database);
            }
            $each_tables = PMA_DBI_fetch_result($sql, 'Name', null, $link);
            // Sort naturally if the config allows it and we're sorting
            // the Name column.
            if ($sort_by == 'Name' && $GLOBALS['cfg']['NaturalOrder']) {
                uksort($each_tables, 'strnatcasecmp');
                if ($sort_order == 'DESC') {
                    $each_tables = array_reverse($each_tables);
                }
            } else {
                // Prepare to sort by creating array of the selected sort
                // value to pass to array_multisort
                foreach ($each_tables as $table_name => $table_data) {
                    ${$sort_by}[$table_name] = strtolower($table_data[$sort_by]);
                }
                if ($sort_order == 'DESC') {
                    array_multisort(${$sort_by}, SORT_DESC, $each_tables);
                } else {
                    array_multisort(${$sort_by}, SORT_ASC, $each_tables);
                }
                // cleanup the temporary sort array
                unset(${$sort_by});
            }
            if ($limit_count) {
                $each_tables = array_slice($each_tables, $limit_offset, $limit_count);
            }
            foreach ($each_tables as $table_name => $each_table) {
                if ('comment' === $tbl_is_group && 0 === strpos($each_table['Comment'], $table)) {
                    // remove table from list
                    unset($each_tables[$table_name]);
                    continue;
                }
                if (!isset($each_tables[$table_name]['Type']) && isset($each_tables[$table_name]['Engine'])) {
                    // pma BC, same parts of PMA still uses 'Type'
                    $each_tables[$table_name]['Type'] =& $each_tables[$table_name]['Engine'];
                } elseif (!isset($each_tables[$table_name]['Engine']) && isset($each_tables[$table_name]['Type'])) {
                    // old MySQL reports Type, newer MySQL reports Engine
                    $each_tables[$table_name]['Engine'] =& $each_tables[$table_name]['Type'];
                }
                // MySQL forward compatibility
                // so pma could use this array as if every server is of version >5.0
                $each_tables[$table_name]['TABLE_SCHEMA'] = $each_database;
                $each_tables[$table_name]['TABLE_NAME'] =& $each_tables[$table_name]['Name'];
                $each_tables[$table_name]['ENGINE'] =& $each_tables[$table_name]['Engine'];
                $each_tables[$table_name]['VERSION'] =& $each_tables[$table_name]['Version'];
                $each_tables[$table_name]['ROW_FORMAT'] =& $each_tables[$table_name]['Row_format'];
                $each_tables[$table_name]['TABLE_ROWS'] =& $each_tables[$table_name]['Rows'];
                $each_tables[$table_name]['AVG_ROW_LENGTH'] =& $each_tables[$table_name]['Avg_row_length'];
                $each_tables[$table_name]['DATA_LENGTH'] =& $each_tables[$table_name]['Data_length'];
                $each_tables[$table_name]['MAX_DATA_LENGTH'] =& $each_tables[$table_name]['Max_data_length'];
                $each_tables[$table_name]['INDEX_LENGTH'] =& $each_tables[$table_name]['Index_length'];
                $each_tables[$table_name]['DATA_FREE'] =& $each_tables[$table_name]['Data_free'];
                $each_tables[$table_name]['AUTO_INCREMENT'] =& $each_tables[$table_name]['Auto_increment'];
                $each_tables[$table_name]['CREATE_TIME'] =& $each_tables[$table_name]['Create_time'];
                $each_tables[$table_name]['UPDATE_TIME'] =& $each_tables[$table_name]['Update_time'];
                $each_tables[$table_name]['CHECK_TIME'] =& $each_tables[$table_name]['Check_time'];
                $each_tables[$table_name]['TABLE_COLLATION'] =& $each_tables[$table_name]['Collation'];
                $each_tables[$table_name]['CHECKSUM'] =& $each_tables[$table_name]['Checksum'];
                $each_tables[$table_name]['CREATE_OPTIONS'] =& $each_tables[$table_name]['Create_options'];
                $each_tables[$table_name]['TABLE_COMMENT'] =& $each_tables[$table_name]['Comment'];
                if (strtoupper($each_tables[$table_name]['Comment']) === 'VIEW' && $each_tables[$table_name]['Engine'] == NULL) {
                    $each_tables[$table_name]['TABLE_TYPE'] = 'VIEW';
                } else {
                    /**
                     * @todo difference between 'TEMPORARY' and 'BASE TABLE' but how to detect?
                     */
                    $each_tables[$table_name]['TABLE_TYPE'] = 'BASE TABLE';
                }
            }
            $tables[$each_database] = $each_tables;
        }
    }
    // cache table data
    // so PMA_Table does not require to issue SHOW TABLE STATUS again
    // Note: I don't see why we would need array_merge_recursive() here,
    // as it creates double entries for the same table (for example a double
    // entry for Comment when changing the storage engine in Operations)
    // Note 2: Instead of array_merge(), simply use the + operator because
    //  array_merge() renumbers numeric keys starting with 0, therefore
    //  we would lose a db name thats consists only of numbers
    PMA_Table::$cache = PMA_Table::$cache + $tables;
    if (!is_array($database)) {
        if (isset($tables[$database])) {
            return $tables[$database];
        } elseif (isset($tables[strtolower($database)])) {
            // on windows with lower_case_table_names = 1
            // MySQL returns
            // with SHOW DATABASES or information_schema.SCHEMATA: `Test`
            // but information_schema.TABLES gives `test`
            // bug #1436171
            // http://sf.net/support/tracker.php?aid=1436171
            return $tables[strtolower($database)];
        } else {
            return $tables;
        }
    } else {
        return $tables;
    }
}
/**
 * returns array of all tables in given db or dbs
 * this function expects unquoted names:
 * RIGHT: my_database
 * WRONG: `my_database`
 * WRONG: my\_database
 * if $tbl_is_group is true, $table is used as filter for table names
 * if $tbl_is_group is 'comment, $table is used as filter for table comments
 *
 * <code>
 * PMA_DBI_get_tables_full('my_database');
 * PMA_DBI_get_tables_full('my_database', 'my_table'));
 * PMA_DBI_get_tables_full('my_database', 'my_tables_', true));
 * PMA_DBI_get_tables_full('my_database', 'my_tables_', 'comment'));
 * </code>
 *
 * @todo    move into PMA_Table
 * @uses    PMA_DBI_fetch_result()
 * @uses    PMA_escape_mysql_wildcards()
 * @uses    PMA_backquote()
 * @uses    is_array()
 * @uses    addslashes()
 * @uses    strpos()
 * @uses    strtoupper()
 * @param   string          $databases      database
 * @param   string          $table          table
 * @param   boolean|string  $tbl_is_group   $table is a table group
 * @param   resource        $link           mysql link
 * @param   integer         $limit_offset   zero-based offset for the count
 * @param   boolean|integer $limit_count    number of tables to return
 * @return  array           list of tables in given db(s)
 */
function PMA_DBI_get_tables_full($database, $table = false, $tbl_is_group = false, $link = null, $limit_offset = 0, $limit_count = false)
{
    require_once './libraries/Table.class.php';
    if (true === $limit_count) {
        $limit_count = $GLOBALS['cfg']['MaxTableList'];
    }
    // prepare and check parameters
    if (!is_array($database)) {
        $databases = array($database);
    } else {
        $databases = $database;
    }
    $tables = array();
    if (!$GLOBALS['cfg']['Server']['DisableIS']) {
        // get table information from information_schema
        if ($table) {
            if (true === $tbl_is_group) {
                $sql_where_table = 'AND `TABLE_NAME` LIKE \'' . PMA_escape_mysql_wildcards(addslashes($table)) . '%\'';
            } elseif ('comment' === $tbl_is_group) {
                $sql_where_table = 'AND `TABLE_COMMENT` LIKE \'' . PMA_escape_mysql_wildcards(addslashes($table)) . '%\'';
            } else {
                $sql_where_table = 'AND `TABLE_NAME` = \'' . addslashes($table) . '\'';
            }
        } else {
            $sql_where_table = '';
        }
        // for PMA bc:
        // `SCHEMA_FIELD_NAME` AS `SHOW_TABLE_STATUS_FIELD_NAME`
        //
        // on non-Windows servers,
        // added BINARY in the WHERE clause to force a case sensitive
        // comparison (if we are looking for the db Aa we don't want
        // to find the db aa)
        $this_databases = array_map('PMA_sqlAddslashes', $databases);
        $sql = '
           SELECT *,
                  `TABLE_SCHEMA`       AS `Db`,
                  `TABLE_NAME`         AS `Name`,
                  `ENGINE`             AS `Engine`,
                  `ENGINE`             AS `Type`,
                  `VERSION`            AS `Version`,
                  `ROW_FORMAT`         AS `Row_format`,
                  `TABLE_ROWS`         AS `Rows`,
                  `AVG_ROW_LENGTH`     AS `Avg_row_length`,
                  `DATA_LENGTH`        AS `Data_length`,
                  `MAX_DATA_LENGTH`    AS `Max_data_length`,
                  `INDEX_LENGTH`       AS `Index_length`,
                  `DATA_FREE`          AS `Data_free`,
                  `AUTO_INCREMENT`     AS `Auto_increment`,
                  `CREATE_TIME`        AS `Create_time`,
                  `UPDATE_TIME`        AS `Update_time`,
                  `CHECK_TIME`         AS `Check_time`,
                  `TABLE_COLLATION`    AS `Collation`,
                  `CHECKSUM`           AS `Checksum`,
                  `CREATE_OPTIONS`     AS `Create_options`,
                  `TABLE_COMMENT`      AS `Comment`
             FROM `information_schema`.`TABLES`
            WHERE ' . (PMA_IS_WINDOWS ? '' : 'BINARY') . ' `TABLE_SCHEMA` IN (\'' . implode("', '", $this_databases) . '\')
              ' . $sql_where_table;
        if ($limit_count) {
            $sql .= ' LIMIT ' . $limit_count . ' OFFSET ' . $limit_offset;
        }
        $tables = PMA_DBI_fetch_result($sql, array('TABLE_SCHEMA', 'TABLE_NAME'), null, $link);
        unset($sql_where_table, $sql);
    }
    // If permissions are wrong on even one database directory,
    // information_schema does not return any table info for any database
    // this is why we fall back to SHOW TABLE STATUS even for MySQL >= 50002
    if (empty($tables)) {
        foreach ($databases as $each_database) {
            if (true === $tbl_is_group) {
                $sql = 'SHOW TABLE STATUS FROM ' . PMA_backquote($each_database) . ' LIKE \'' . PMA_escape_mysql_wildcards(addslashes($table)) . '%\'';
            } else {
                $sql = 'SHOW TABLE STATUS FROM ' . PMA_backquote($each_database);
            }
            $each_tables = PMA_DBI_fetch_result($sql, 'Name', null, $link);
            if ($limit_count) {
                $each_tables = array_slice($each_tables, $limit_offset, $limit_count);
            }
            foreach ($each_tables as $table_name => $each_table) {
                if ('comment' === $tbl_is_group && 0 === strpos($each_table['Comment'], $table)) {
                    // remove table from list
                    unset($each_tables[$table_name]);
                    continue;
                }
                if (!isset($each_tables[$table_name]['Type']) && isset($each_tables[$table_name]['Engine'])) {
                    // pma BC, same parts of PMA still uses 'Type'
                    $each_tables[$table_name]['Type'] =& $each_tables[$table_name]['Engine'];
                } elseif (!isset($each_tables[$table_name]['Engine']) && isset($each_tables[$table_name]['Type'])) {
                    // old MySQL reports Type, newer MySQL reports Engine
                    $each_tables[$table_name]['Engine'] =& $each_tables[$table_name]['Type'];
                }
                // MySQL forward compatibility
                // so pma could use this array as if every server is of version >5.0
                $each_tables[$table_name]['TABLE_SCHEMA'] = $each_database;
                $each_tables[$table_name]['TABLE_NAME'] =& $each_tables[$table_name]['Name'];
                $each_tables[$table_name]['ENGINE'] =& $each_tables[$table_name]['Engine'];
                $each_tables[$table_name]['VERSION'] =& $each_tables[$table_name]['Version'];
                $each_tables[$table_name]['ROW_FORMAT'] =& $each_tables[$table_name]['Row_format'];
                $each_tables[$table_name]['TABLE_ROWS'] =& $each_tables[$table_name]['Rows'];
                $each_tables[$table_name]['AVG_ROW_LENGTH'] =& $each_tables[$table_name]['Avg_row_length'];
                $each_tables[$table_name]['DATA_LENGTH'] =& $each_tables[$table_name]['Data_length'];
                $each_tables[$table_name]['MAX_DATA_LENGTH'] =& $each_tables[$table_name]['Max_data_length'];
                $each_tables[$table_name]['INDEX_LENGTH'] =& $each_tables[$table_name]['Index_length'];
                $each_tables[$table_name]['DATA_FREE'] =& $each_tables[$table_name]['Data_free'];
                $each_tables[$table_name]['AUTO_INCREMENT'] =& $each_tables[$table_name]['Auto_increment'];
                $each_tables[$table_name]['CREATE_TIME'] =& $each_tables[$table_name]['Create_time'];
                $each_tables[$table_name]['UPDATE_TIME'] =& $each_tables[$table_name]['Update_time'];
                $each_tables[$table_name]['CHECK_TIME'] =& $each_tables[$table_name]['Check_time'];
                $each_tables[$table_name]['TABLE_COLLATION'] =& $each_tables[$table_name]['Collation'];
                $each_tables[$table_name]['CHECKSUM'] =& $each_tables[$table_name]['Checksum'];
                $each_tables[$table_name]['CREATE_OPTIONS'] =& $each_tables[$table_name]['Create_options'];
                $each_tables[$table_name]['TABLE_COMMENT'] =& $each_tables[$table_name]['Comment'];
                if (strtoupper($each_tables[$table_name]['Comment']) === 'VIEW') {
                    $each_tables[$table_name]['TABLE_TYPE'] = 'VIEW';
                } else {
                    /**
                     * @todo difference between 'TEMPORARY' and 'BASE TABLE' but how to detect?
                     */
                    $each_tables[$table_name]['TABLE_TYPE'] = 'BASE TABLE';
                }
            }
            $tables[$each_database] = $each_tables;
        }
    }
    if ($GLOBALS['cfg']['NaturalOrder']) {
        foreach ($tables as $key => $val) {
            uksort($tables[$key], 'strnatcasecmp');
        }
    }
    // cache table data
    // so PMA_Table does not require to issue SHOW TABLE STATUS again
    PMA_Table::$cache = $tables;
    if (!is_array($database)) {
        if (isset($tables[$database])) {
            return $tables[$database];
        } elseif (isset($tables[strtolower($database)])) {
            // on windows with lower_case_table_names = 1
            // MySQL returns
            // with SHOW DATABASES or information_schema.SCHEMATA: `Test`
            // but information_schema.TABLES gives `test`
            // bug #1436171
            // http://sf.net/support/tracker.php?aid=1436171
            return $tables[strtolower($database)];
        } else {
            return $tables;
        }
    } else {
        return $tables;
    }
}