/**
* Prepares queries for adding users and
* also create database and return query and message
*
* @param boolean $_error whether user create or not
* @param string $real_sql_query SQL query for add a user
* @param string $sql_query SQL query to be displayed
* @param string $username username
* @param string $hostname host name
* @param string $dbname database name
*
* @return array $sql_query, $message
*/
function PMA_addUserAndCreateDatabase($_error, $real_sql_query, $sql_query, $username, $hostname, $dbname)
{
if ($_error || !empty($real_sql_query) && !$GLOBALS['dbi']->tryQuery($real_sql_query)) {
$_REQUEST['createdb-1'] = $_REQUEST['createdb-2'] = $_REQUEST['createdb-3'] = null;
$message = PMA_Message::rawError($GLOBALS['dbi']->getError());
} else {
$message = PMA_Message::success(__('You have added a new user.'));
}
if (isset($_REQUEST['createdb-1'])) {
// Create database with same name and grant all privileges
$q = 'CREATE DATABASE IF NOT EXISTS ' . PMA_Util::backquote(PMA_Util::sqlAddSlashes($username)) . ';';
$sql_query .= $q;
if (!$GLOBALS['dbi']->tryQuery($q)) {
$message = PMA_Message::rawError($GLOBALS['dbi']->getError());
}
/**
* Reload the navigation
*/
$GLOBALS['reload'] = true;
$GLOBALS['db'] = $username;
$q = 'GRANT ALL PRIVILEGES ON ' . PMA_Util::backquote(PMA_Util::escapeMysqlWildcards(PMA_Util::sqlAddSlashes($username))) . '.* TO \'' . PMA_Util::sqlAddSlashes($username) . '\'@\'' . PMA_Util::sqlAddSlashes($hostname) . '\';';
$sql_query .= $q;
if (!$GLOBALS['dbi']->tryQuery($q)) {
$message = PMA_Message::rawError($GLOBALS['dbi']->getError());
}
}
if (isset($_REQUEST['createdb-2'])) {
// Grant all privileges on wildcard name (username\_%)
$q = 'GRANT ALL PRIVILEGES ON ' . PMA_Util::backquote(PMA_Util::sqlAddSlashes($username) . '\\_%') . '.* TO \'' . PMA_Util::sqlAddSlashes($username) . '\'@\'' . PMA_Util::sqlAddSlashes($hostname) . '\';';
$sql_query .= $q;
if (!$GLOBALS['dbi']->tryQuery($q)) {
$message = PMA_Message::rawError($GLOBALS['dbi']->getError());
}
}
if (isset($_REQUEST['createdb-3'])) {
// Grant all privileges on the specified database to the new user
$q = 'GRANT ALL PRIVILEGES ON ' . PMA_Util::backquote(PMA_Util::sqlAddSlashes($dbname)) . '.* TO \'' . PMA_Util::sqlAddSlashes($username) . '\'@\'' . PMA_Util::sqlAddSlashes($hostname) . '\';';
$sql_query .= $q;
if (!$GLOBALS['dbi']->tryQuery($q)) {
$message = PMA_Message::rawError($GLOBALS['dbi']->getError());
}
}
return array($sql_query, $message);
}
/**
* 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
*
* <code>
* $GLOBALS['dbi']->getTablesFull('my_database');
* $GLOBALS['dbi']->getTablesFull('my_database', 'my_table'));
* $GLOBALS['dbi']->getTablesFull('my_database', 'my_tables_', true));
* </code>
*
* @param string $database database
* @param string|bool $table table or false
* @param boolean $tbl_is_group $table is a table group
* @param mixed $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)
* @param string $tble_type whether table or view
*
* @todo move into PMA_Table
*
* @return array list of tables in given db(s)
*/
public function getTablesFull($database, $table = false, $tbl_is_group = false, $link = null, $limit_offset = 0, $limit_count = false, $sort_by = 'Name', $sort_order = 'ASC', $tble_type = null)
{
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();
// get table information from information_schema
if ($table) {
if (true === $tbl_is_group) {
$sql_where_table = 'AND t.`TABLE_NAME` LIKE \'' . PMA_Util::escapeMysqlWildcards(PMA_Util::sqlAddSlashes($table)) . '%\'';
} else {
$sql_where_table = 'AND t.`TABLE_NAME` = \'' . PMA_Util::sqlAddSlashes($table) . '\'';
}
} else {
$sql_where_table = '';
}
if ($tble_type) {
if ($tble_type == 'view') {
if (PMA_DRIZZLE) {
$sql_where_table .= " AND t.`TABLE_TYPE` != 'BASE'";
} else {
$sql_where_table .= " AND t.`TABLE_TYPE` != 'BASE TABLE'";
}
} else {
if ($tble_type == 'table') {
if (PMA_DRIZZLE) {
$sql_where_table .= " AND t.`TABLE_TYPE` = 'BASE'";
} else {
$sql_where_table .= " AND t.`TABLE_TYPE` = 'BASE 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_Util::sqlAddSlashes', $databases);
if (PMA_DRIZZLE) {
$engine_info = PMA_Util::cacheGet('drizzle_engines', true);
$stats_join = "LEFT JOIN (SELECT 0 NUM_ROWS) AS stat ON false";
if (isset($engine_info['InnoDB']) && $engine_info['InnoDB']['module_library'] == 'innobase') {
$stats_join = "LEFT JOIN data_dictionary.INNODB_SYS_TABLESTATS" . " stat ON (t.ENGINE = 'InnoDB' AND stat.NAME" . " = (t.TABLE_SCHEMA || '/') || t.TABLE_NAME)";
}
// data_dictionary.table_cache may not contain any data
// for some tables, it's just a table cache
// auto_increment == 0 is cast to NULL because currently
// (2011.03.13 GA)
// Drizzle doesn't provide correct value
$sql = "\n SELECT t.*,\n t.TABLE_SCHEMA AS `Db`,\n t.TABLE_NAME AS `Name`,\n t.TABLE_TYPE AS `TABLE_TYPE`,\n t.ENGINE AS `Engine`,\n t.ENGINE AS `Type`,\n t.TABLE_VERSION AS `Version`,-- VERSION\n t.ROW_FORMAT AS `Row_format`,\n coalesce(tc.ROWS, stat.NUM_ROWS)\n AS `Rows`,-- TABLE_ROWS,\n coalesce(tc.ROWS, stat.NUM_ROWS)\n AS `TABLE_ROWS`,\n tc.AVG_ROW_LENGTH AS `Avg_row_length`, -- AVG_ROW_LENGTH\n tc.TABLE_SIZE AS `Data_length`, -- DATA_LENGTH\n NULL AS `Max_data_length`, -- MAX_DATA_LENGTH\n NULL AS `Index_length`, -- INDEX_LENGTH\n NULL AS `Data_free`, -- DATA_FREE\n nullif(t.AUTO_INCREMENT, 0)\n AS `Auto_increment`,\n t.TABLE_CREATION_TIME AS `Create_time`, -- CREATE_TIME\n t.TABLE_UPDATE_TIME AS `Update_time`, -- UPDATE_TIME\n NULL AS `Check_time`, -- CHECK_TIME\n t.TABLE_COLLATION AS `Collation`,\n NULL AS `Checksum`, -- CHECKSUM\n NULL AS `Create_options`, -- CREATE_OPTIONS\n t.TABLE_COMMENT AS `Comment`\n FROM data_dictionary.TABLES t\n LEFT JOIN data_dictionary.TABLE_CACHE tc\n ON tc.TABLE_SCHEMA = t.TABLE_SCHEMA AND tc.TABLE_NAME\n = t.TABLE_NAME\n {$stats_join}\n WHERE t.TABLE_SCHEMA IN ('" . implode("', '", $this_databases) . "')\n " . $sql_where_table;
} else {
$sql = '
SELECT *,
`TABLE_SCHEMA` AS `Db`,
`TABLE_NAME` AS `Name`,
`TABLE_TYPE` AS `TABLE_TYPE`,
`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` t
WHERE ' . (PMA_IS_WINDOWS ? '' : 'BINARY') . ' `TABLE_SCHEMA`
IN (\'' . implode("', '", $this_databases) . '\')
' . $sql_where_table;
}
// Sort the tables
$sql .= " ORDER BY {$sort_by} {$sort_order}";
if ($limit_count) {
$sql .= ' LIMIT ' . $limit_count . ' OFFSET ' . $limit_offset;
}
$tables = $this->fetchResult($sql, array('TABLE_SCHEMA', 'TABLE_NAME'), null, $link);
unset($sql_where_table, $sql);
if (PMA_DRIZZLE) {
// correct I_S and D_D names returned by D_D.TABLES -
// Drizzle generally uses lower case for them,
// but TABLES returns uppercase
foreach ((array) $database as $db) {
$db_upper = strtoupper($db);
if (!isset($tables[$db]) && isset($tables[$db_upper])) {
$tables[$db] = $tables[$db_upper];
unset($tables[$db_upper]);
}
}
}
if ($sort_by == 'Name' && $GLOBALS['cfg']['NaturalOrder']) {
// here, the array's first key is by schema name
foreach ($tables as $one_database_name => $one_database_tables) {
uksort($one_database_tables, 'strnatcasecmp');
if ($sort_order == 'DESC') {
$one_database_tables = array_reverse($one_database_tables);
}
$tables[$one_database_name] = $one_database_tables;
}
} else {
if ($sort_by == 'Data_length') {
// Size = Data_length + Index_length
foreach ($tables as $one_database_name => $one_database_tables) {
uasort($one_database_tables, function ($a, $b) {
$aLength = $a['Data_length'] + $a['Index_length'];
$bLength = $b['Data_length'] + $b['Index_length'];
return $aLength == $bLength ? 0 : $aLength < $bLength ? -1 : 1;
});
if ($sort_order == 'DESC') {
$one_database_tables = array_reverse($one_database_tables);
}
$tables[$one_database_name] = $one_database_tables;
}
}
}
// end (get information from table schema)
// 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) && !PMA_DRIZZLE) {
foreach ($databases as $each_database) {
if ($table || true === $tbl_is_group || $tble_type) {
$sql = 'SHOW TABLE STATUS FROM ' . PMA_Util::backquote($each_database) . ' WHERE';
$needAnd = false;
if ($table || true === $tbl_is_group) {
$sql .= " `Name` LIKE '" . PMA_Util::escapeMysqlWildcards(PMA_Util::sqlAddSlashes($table, true)) . "%'";
$needAnd = true;
}
if ($tble_type) {
if ($needAnd) {
$sql .= " AND";
}
if ($tble_type == 'view') {
$sql .= " `Comment` = 'VIEW'";
} else {
if ($tble_type == 'table') {
$sql .= " `Comment` != 'VIEW'";
}
}
}
} else {
$sql = 'SHOW TABLE STATUS FROM ' . PMA_Util::backquote($each_database);
}
$useStatusCache = false;
if (extension_loaded('apc') && isset($GLOBALS['cfg']['Server']['StatusCacheDatabases']) && !empty($GLOBALS['cfg']['Server']['StatusCacheLifetime'])) {
$statusCacheDatabases = (array) $GLOBALS['cfg']['Server']['StatusCacheDatabases'];
if (in_array($each_database, $statusCacheDatabases)) {
$useStatusCache = true;
}
}
$each_tables = null;
if ($useStatusCache) {
$cacheKey = 'phpMyAdmin_tableStatus_' . sha1($GLOBALS['cfg']['Server']['host'] . '_' . $sql);
$each_tables = apc_fetch($cacheKey);
}
if (!$each_tables) {
$each_tables = $this->fetchResult($sql, 'Name', null, $link);
}
if ($useStatusCache) {
apc_store($cacheKey, $each_tables, $GLOBALS['cfg']['Server']['StatusCacheLifetime']);
}
// 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
// Size = Data_length + Index_length
if ($sort_by == 'Data_length') {
foreach ($each_tables as $table_name => $table_data) {
${$sort_by}[$table_name] = strtolower($table_data['Data_length'] + $table_data['Index_length']);
}
} else {
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);
}
$tables[$each_database] = $this->copyTableProperties($each_tables, $each_database);
}
}
// 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
foreach ($tables as $one_database => $its_tables) {
if (isset(PMA_Table::$cache[$one_database])) {
// the + operator does not do the intended effect
// when the cache for one table already exists
if ($table && isset(PMA_Table::$cache[$one_database][$table])) {
unset(PMA_Table::$cache[$one_database][$table]);
}
PMA_Table::$cache[$one_database] = PMA_Table::$cache[$one_database] + $tables[$one_database];
} else {
PMA_Table::$cache[$one_database] = $tables[$one_database];
}
}
unset($one_database, $its_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 #2036
// https://sourceforge.net/p/phpmyadmin/bugs/2036/
return $tables[strtolower($database)];
} else {
// one database but inexact letter case match
// as Drizzle is always case insensitive,
// we can safely return the only result
if (PMA_DRIZZLE && count($tables) == 1) {
$keys = array_keys($tables);
if (strlen(array_pop($keys)) == strlen($database)) {
return array_pop($tables);
}
}
return $tables;
}
} else {
return $tables;
}
}
/**
* @global bool whether selected db is information_schema
*/
$db_is_information_schema = false;
if (PMA_is_system_schema($db)) {
$is_show_stats = false;
$db_is_information_schema = true;
}
/**
* @global array information about tables in db
*/
$tables = array();
// When used in Nested table group mode,
// only show tables matching the given groupname
if (PMA_isValid($_REQUEST['tbl_group'])) {
$tbl_group_sql = ' LIKE "' . PMA_Util::escapeMysqlWildcards($_REQUEST['tbl_group']) . '%"';
} else {
$tbl_group_sql = '';
}
$tooltip_truename = array();
$tooltip_aliasname = array();
// Special speedup for newer MySQL Versions (in 4.0 format changed)
if (true === $cfg['SkipLockedTables']) {
$db_info_result = PMA_DBI_query('SHOW OPEN TABLES FROM ' . PMA_Util::backquote($db) . ';');
// Blending out tables in use
if ($db_info_result && PMA_DBI_num_rows($db_info_result) > 0) {
while ($tmp = PMA_DBI_fetch_row($db_info_result)) {
// if in use memorize tablename
if (preg_match('@in_use=[1-9]+@i', $tmp[1])) {
$sot_cache[$tmp[0]] = true;
}
/**
* 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
*
* <code>
* $GLOBALS['dbi']->getTablesFull('my_database');
* $GLOBALS['dbi']->getTablesFull('my_database', 'my_table'));
* $GLOBALS['dbi']->getTablesFull('my_database', 'my_tables_', true));
* </code>
*
* @param string $database database
* @param string $table table name
* @param boolean $tbl_is_group $table is a table group
* @param mixed $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)
* @param string $table_type whether table or view
*
* @todo move into PMA_Table
*
* @return array list of tables in given db(s)
*/
public function getTablesFull($database, $table = '', $tbl_is_group = false, $link = null, $limit_offset = 0, $limit_count = false, $sort_by = 'Name', $sort_order = 'ASC', $table_type = null)
{
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 (!isset($GLOBALS['cfg']['Server']['DisableIS']) || !$GLOBALS['cfg']['Server']['DisableIS']) {
$sql_where_table = $this->_getTableCondition($table, $tbl_is_group, $table_type);
// 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_Util::sqlAddSlashes', $databases);
$sql = $this->_getSqlForTablesFull($this_databases, $sql_where_table);
// Sort the tables
$sql .= " ORDER BY {$sort_by} {$sort_order}";
if ($limit_count) {
$sql .= ' LIMIT ' . $limit_count . ' OFFSET ' . $limit_offset;
}
$tables = $this->fetchResult($sql, array('TABLE_SCHEMA', 'TABLE_NAME'), null, $link);
if (PMA_DRIZZLE) {
// correct I_S and D_D names returned by D_D.TABLES -
// Drizzle generally uses lower case for them,
// but TABLES returns uppercase
foreach ((array) $database as $db) {
$db_upper = mb_strtoupper($db);
if (!isset($tables[$db]) && isset($tables[$db_upper])) {
$tables[$db] = $tables[$db_upper];
unset($tables[$db_upper]);
}
}
}
if ($sort_by == 'Name' && $GLOBALS['cfg']['NaturalOrder']) {
// here, the array's first key is by schema name
foreach ($tables as $one_database_name => $one_database_tables) {
uksort($one_database_tables, 'strnatcasecmp');
if ($sort_order == 'DESC') {
$one_database_tables = array_reverse($one_database_tables);
}
$tables[$one_database_name] = $one_database_tables;
}
} else {
if ($sort_by == 'Data_length') {
// Size = Data_length + Index_length
foreach ($tables as $one_database_name => $one_database_tables) {
uasort($one_database_tables, function ($a, $b) {
$aLength = $a['Data_length'] + $a['Index_length'];
$bLength = $b['Data_length'] + $b['Index_length'];
return $aLength == $bLength ? 0 : $aLength < $bLength ? -1 : 1;
});
if ($sort_order == 'DESC') {
$one_database_tables = array_reverse($one_database_tables);
}
$tables[$one_database_name] = $one_database_tables;
}
}
}
}
// end (get information from table schema)
// 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) && !PMA_DRIZZLE) {
foreach ($databases as $each_database) {
if ($table || true === $tbl_is_group || !empty($table_type)) {
$sql = 'SHOW TABLE STATUS FROM ' . PMA_Util::backquote($each_database) . ' WHERE';
$needAnd = false;
if ($table || true === $tbl_is_group) {
$sql .= " `Name` LIKE '" . PMA_Util::escapeMysqlWildcards(PMA_Util::sqlAddSlashes($table, true)) . "%'";
$needAnd = true;
}
if (!empty($table_type)) {
if ($needAnd) {
$sql .= " AND";
}
if ($table_type == 'view') {
$sql .= " `Comment` = 'VIEW'";
} else {
if ($table_type == 'table') {
$sql .= " `Comment` != 'VIEW'";
}
}
}
} else {
$sql = 'SHOW TABLE STATUS FROM ' . PMA_Util::backquote($each_database);
}
$useStatusCache = false;
if (extension_loaded('apc') && isset($GLOBALS['cfg']['Server']['StatusCacheDatabases']) && !empty($GLOBALS['cfg']['Server']['StatusCacheLifetime'])) {
$statusCacheDatabases = (array) $GLOBALS['cfg']['Server']['StatusCacheDatabases'];
if (in_array($each_database, $statusCacheDatabases)) {
$useStatusCache = true;
}
}
$each_tables = null;
if ($useStatusCache) {
$cacheKey = 'phpMyAdmin_tableStatus_' . sha1($GLOBALS['cfg']['Server']['host'] . '_' . $sql);
$each_tables = apc_fetch($cacheKey);
}
if (!$each_tables) {
$each_tables = $this->fetchResult($sql, 'Name', null, $link);
}
if ($useStatusCache) {
apc_store($cacheKey, $each_tables, $GLOBALS['cfg']['Server']['StatusCacheLifetime']);
}
// 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
// Size = Data_length + Index_length
if ($sort_by == 'Data_length') {
foreach ($each_tables as $table_name => $table_data) {
${$sort_by}[$table_name] = strtolower($table_data['Data_length'] + $table_data['Index_length']);
}
} else {
foreach ($each_tables as $table_name => $table_data) {
${$sort_by}[$table_name] = strtolower($table_data[$sort_by]);
}
}
if (!empty(${$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 (!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
// todo : remove and check usage in the rest of the code,
// MySQL 5.0 is required by current PMA version
$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';
} elseif ($each_database == 'information_schema') {
$each_tables[$table_name]['TABLE_TYPE'] = 'SYSTEM 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
$this->_cacheTableData($tables, $table);
if (is_array($database)) {
return $tables;
}
if (isset($tables[$database])) {
return $tables[$database];
}
if (isset($tables[mb_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 #2036
// https://sourceforge.net/p/phpmyadmin/bugs/2036/
return $tables[mb_strtolower($database)];
}
// one database but inexact letter case match
// as Drizzle is always case insensitive,
// we can safely return the only result
if (!PMA_DRIZZLE || !count($tables) == 1) {
return $tables;
}
$keys = array_keys($tables);
if (mb_strlen(array_pop($keys)) == mb_strlen($database)) {
return array_pop($tables);
}
return $tables;
}
/**
* Gets the list of tables in the current db, taking into account
* that they might be "in use"
*
* @param string $db database name
* @param object $db_info_result result set
*
* @return array $tables list of tables
*
*/
public function getTablesWhenOpen($db, $db_info_result)
{
$tables = array();
while ($tmp = $GLOBALS['dbi']->fetchAssoc($db_info_result)) {
// if in use, memorize table name
if ($tmp['In_use'] > 0) {
$sot_cache[$tmp['Table']] = true;
}
}
$GLOBALS['dbi']->freeResult($db_info_result);
// is there at least one "in use" table?
if (isset($sot_cache)) {
$db_info_result = false;
$tblGroupSql = "";
$whereAdded = false;
if (PMA_isValid($_REQUEST['tbl_group'])) {
$group = PMA_Util::escapeMysqlWildcards($_REQUEST['tbl_group']);
$groupWithSeparator = PMA_Util::escapeMysqlWildcards($_REQUEST['tbl_group'] . $GLOBALS['cfg']['NavigationTreeTableSeparator']);
$tblGroupSql .= " WHERE (" . PMA_Util::backquote('Tables_in_' . $db) . " LIKE '" . $groupWithSeparator . "%'" . " OR " . PMA_Util::backquote('Tables_in_' . $db) . " LIKE '" . $group . "')";
$whereAdded = true;
}
if (PMA_isValid($_REQUEST['tbl_type'], array('table', 'view'))) {
$tblGroupSql .= $whereAdded ? " AND" : " WHERE";
if ($_REQUEST['tbl_type'] == 'view') {
$tblGroupSql .= " `Table_type` != 'BASE TABLE'";
} else {
$tblGroupSql .= " `Table_type` = 'BASE TABLE'";
}
}
$db_info_result = $GLOBALS['dbi']->query('SHOW FULL TABLES FROM ' . PMA_Util::backquote($db) . $tblGroupSql, null, PMA_DatabaseInterface::QUERY_STORE);
unset($tblGroupSql, $whereAdded);
if ($db_info_result && $GLOBALS['dbi']->numRows($db_info_result) > 0) {
while ($tmp = $GLOBALS['dbi']->fetchRow($db_info_result)) {
if (!isset($sot_cache[$tmp[0]])) {
$sts_result = $GLOBALS['dbi']->query("SHOW TABLE STATUS FROM " . PMA_Util::backquote($db) . " LIKE '" . PMA_Util::sqlAddSlashes($tmp[0], true) . "';");
$sts_tmp = $GLOBALS['dbi']->fetchAssoc($sts_result);
$GLOBALS['dbi']->freeResult($sts_result);
unset($sts_result);
$tableArray = $GLOBALS['dbi']->copyTableProperties(array($sts_tmp), $db);
$tables[$sts_tmp['Name']] = $tableArray[0];
} else {
// table in use
$tables[$tmp[0]] = array('TABLE_NAME' => $tmp[0], 'ENGINE' => '', 'TABLE_TYPE' => '', 'TABLE_ROWS' => 0);
}
}
// end while
if ($GLOBALS['cfg']['NaturalOrder']) {
uksort($tables, 'strnatcasecmp');
}
} elseif ($db_info_result) {
$GLOBALS['dbi']->freeResult($db_info_result);
}
unset($sot_cache);
}
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
*
* <code>
* $GLOBALS['dbi']->getTablesFull('my_database');
* $GLOBALS['dbi']->getTablesFull('my_database', 'my_table'));
* $GLOBALS['dbi']->getTablesFull('my_database', 'my_tables_', true));
* </code>
*
* @param string $database database
* @param string|bool $table table name or false
* @param boolean $tbl_is_group $table is a table group
* @param mixed $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)
* @param string $tble_type whether table or view
*
* @todo move into PMA_Table
*
* @return array list of tables in given db(s)
*/
public function getTablesFull($database, $table = false, $tbl_is_group = false, $link = null, $limit_offset = 0, $limit_count = false, $sort_by = 'Name', $sort_order = 'ASC', $tble_type = null)
{
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();
$sql_where_table = $this->_getTableCondition($table, $tbl_is_group, $tble_type);
// 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_Util::sqlAddSlashes', $databases);
$sql = $this->_getSqlForTablesFull($this_databases, $sql_where_table);
unset($sql_where_table);
// Sort the tables
$sql .= " ORDER BY {$sort_by} {$sort_order}";
if ($limit_count) {
$sql .= ' LIMIT ' . $limit_count . ' OFFSET ' . $limit_offset;
}
$tables = $this->fetchResult($sql, array('TABLE_SCHEMA', 'TABLE_NAME'), null, $link);
unset($sql);
if (PMA_DRIZZLE) {
// correct I_S and D_D names returned by D_D.TABLES -
// Drizzle generally uses lower case for them,
// but TABLES returns uppercase
foreach ((array) $database as $db) {
$db_upper = strtoupper($db);
if (!isset($tables[$db]) && isset($tables[$db_upper])) {
$tables[$db] = $tables[$db_upper];
unset($tables[$db_upper]);
}
}
}
if ($sort_by == 'Name' && $GLOBALS['cfg']['NaturalOrder']) {
// here, the array's first key is by schema name
foreach ($tables as $one_database_name => $one_database_tables) {
uksort($one_database_tables, 'strnatcasecmp');
if ($sort_order == 'DESC') {
$one_database_tables = array_reverse($one_database_tables);
}
$tables[$one_database_name] = $one_database_tables;
}
} else {
if ($sort_by == 'Data_length') {
// Size = Data_length + Index_length
foreach ($tables as $one_database_name => $one_database_tables) {
uasort($one_database_tables, function ($a, $b) {
$aLength = $a['Data_length'] + $a['Index_length'];
$bLength = $b['Data_length'] + $b['Index_length'];
return $aLength == $bLength ? 0 : $aLength < $bLength ? -1 : 1;
});
if ($sort_order == 'DESC') {
$one_database_tables = array_reverse($one_database_tables);
}
$tables[$one_database_name] = $one_database_tables;
}
}
}
// end (get information from table schema)
// 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) && !PMA_DRIZZLE) {
foreach ($databases as $each_database) {
if ($table || true === $tbl_is_group || $tble_type) {
$sql = 'SHOW TABLE STATUS FROM ' . PMA_Util::backquote($each_database) . ' WHERE';
$needAnd = false;
if ($table || true === $tbl_is_group) {
$sql .= " `Name` LIKE '" . PMA_Util::escapeMysqlWildcards(PMA_Util::sqlAddSlashes($table, true)) . "%'";
$needAnd = true;
}
if ($tble_type) {
if ($needAnd) {
$sql .= " AND";
}
if ($tble_type == 'view') {
$sql .= " `Comment` = 'VIEW'";
} else {
if ($tble_type == 'table') {
$sql .= " `Comment` != 'VIEW'";
}
}
}
} else {
$sql = 'SHOW TABLE STATUS FROM ' . PMA_Util::backquote($each_database);
}
$useStatusCache = false;
if (extension_loaded('apc') && isset($GLOBALS['cfg']['Server']['StatusCacheDatabases']) && !empty($GLOBALS['cfg']['Server']['StatusCacheLifetime'])) {
$statusCacheDatabases = (array) $GLOBALS['cfg']['Server']['StatusCacheDatabases'];
if (in_array($each_database, $statusCacheDatabases)) {
$useStatusCache = true;
}
}
$each_tables = null;
if ($useStatusCache) {
$cacheKey = 'phpMyAdmin_tableStatus_' . sha1($GLOBALS['cfg']['Server']['host'] . '_' . $sql);
$each_tables = apc_fetch($cacheKey);
}
if (!$each_tables) {
$each_tables = $this->fetchResult($sql, 'Name', null, $link);
}
if ($useStatusCache) {
apc_store($cacheKey, $each_tables, $GLOBALS['cfg']['Server']['StatusCacheLifetime']);
}
// 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
// Size = Data_length + Index_length
if ($sort_by == 'Data_length') {
foreach ($each_tables as $table_name => $table_data) {
${$sort_by}[$table_name] = strtolower($table_data['Data_length'] + $table_data['Index_length']);
}
} else {
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);
}
$tables[$each_database] = $this->copyTableProperties($each_tables, $each_database);
}
}
$this->_cacheTableData($tables, $table);
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 #2036
// https://sourceforge.net/p/phpmyadmin/bugs/2036/
return $tables[strtolower($database)];
} else {
// one database but inexact letter case match
// as Drizzle is always case insensitive,
// we can safely return the only result
if (PMA_DRIZZLE && count($tables) == 1) {
$keys = array_keys($tables);
if (strlen(array_pop($keys)) == strlen($database)) {
return array_pop($tables);
}
}
return $tables;
}
} else {
return $tables;
}
}
// Blending out tables in use
if ($db_info_result && $GLOBALS['dbi']->numRows($db_info_result) > 0) {
while ($tmp = $GLOBALS['dbi']->fetchAssoc($db_info_result)) {
// if in use, memorize table name
if ($tmp['In_use'] > 0) {
$sot_cache[$tmp['Table']] = true;
}
}
$GLOBALS['dbi']->freeResult($db_info_result);
if (isset($sot_cache)) {
$db_info_result = false;
$tblGroupSql = "";
$whereAdded = false;
if (PMA_isValid($_REQUEST['tbl_group'])) {
$group = PMA_Util::escapeMysqlWildcards($_REQUEST['tbl_group']);
$groupWithSeparator = PMA_Util::escapeMysqlWildcards($_REQUEST['tbl_group'] . $GLOBALS['cfg']['NavigationTreeTableSeparator']);
$tblGroupSql .= " WHERE (" . PMA_Util::backquote('Tables_in_' . $db) . " LIKE '" . $groupWithSeparator . "%'" . " OR " . PMA_Util::backquote('Tables_in_' . $db) . " LIKE '" . $group . "')";
$whereAdded = true;
}
if (PMA_isValid($_REQUEST['tbl_type'], array('table', 'view'))) {
$tblGroupSql .= $whereAdded ? " AND" : " WHERE";
if ($_REQUEST['tbl_type'] == 'view') {
$tblGroupSql .= " `Table_type` != 'BASE TABLE'";
} else {
$tblGroupSql .= " `Table_type` = 'BASE TABLE'";
}
}
$db_info_result = $GLOBALS['dbi']->query('SHOW FULL TABLES FROM ' . PMA_Util::backquote($db) . $tblGroupSql, null, PMA_DatabaseInterface::QUERY_STORE);
unset($tblGroupSql, $whereAdded);
if ($db_info_result && $GLOBALS['dbi']->numRows($db_info_result) > 0) {
while ($tmp = $GLOBALS['dbi']->fetchRow($db_info_result)) {
/**
* PMA_Util::escapeMysqlWildcards tests
*
* @param string $a String to escape
* @param string $b Expected value
*
* @return void
*
* @dataProvider escapeDataProvider
*/
public function testEscape($a, $b)
{
$this->assertEquals($a, PMA_Util::escapeMysqlWildcards($b));
}
/**
* Prepares queries for adding users and
* also create database and return query and message
*
* @param boolean $_error whether user create or not
* @param string $real_sql_query SQL query for add a user
* @param string $sql_query SQL query to be displayed
* @param string $username username
* @param string $hostname host name
*
* @return array $sql_query, $message
*/
function PMA_addUserAndCreateDatabase($_error, $real_sql_query, $sql_query, $username, $hostname)
{
if ($_error || !PMA_DBI_try_query($real_sql_query)) {
$_REQUEST['createdb-1'] = $_REQUEST['createdb-2'] = $_REQUEST['createdb-3'] = false;
$message = PMA_Message::rawError(PMA_DBI_getError());
} else {
$message = PMA_Message::success(__('You have added a new user.'));
}
if (isset($_REQUEST['createdb-1'])) {
// Create database with same name and grant all privileges
$q = 'CREATE DATABASE IF NOT EXISTS ' . PMA_Util::backquote(PMA_Util::sqlAddSlashes($username)) . ';';
$sql_query .= $q;
if (!PMA_DBI_try_query($q)) {
$message = PMA_Message::rawError(PMA_DBI_getError());
}
/**
* If we are not in an Ajax request, we can't reload navigation now
*/
if ($GLOBALS['is_ajax_request'] != true) {
// this is needed in case tracking is on:
$GLOBALS['db'] = $username;
$GLOBALS['reload'] = true;
echo PMA_Util::getReloadNavigationScript();
}
$q = 'GRANT ALL PRIVILEGES ON ' . PMA_Util::backquote(PMA_Util::escapeMysqlWildcards(PMA_Util::sqlAddSlashes($username))) . '.* TO \'' . PMA_Util::sqlAddSlashes($username) . '\'@\'' . PMA_Util::sqlAddSlashes($hostname) . '\';';
$sql_query .= $q;
if (!PMA_DBI_try_query($q)) {
$message = PMA_Message::rawError(PMA_DBI_getError());
}
}
if (isset($_REQUEST['createdb-2'])) {
// Grant all privileges on wildcard name (username\_%)
$q = 'GRANT ALL PRIVILEGES ON ' . PMA_Util::backquote(PMA_Util::sqlAddSlashes($username) . '\\_%') . '.* TO \'' . PMA_Util::sqlAddSlashes($username) . '\'@\'' . PMA_Util::sqlAddSlashes($hostname) . '\';';
$sql_query .= $q;
if (!PMA_DBI_try_query($q)) {
$message = PMA_Message::rawError(PMA_DBI_getError());
}
}
if (isset($_REQUEST['createdb-3'])) {
// Grant all privileges on the specified database to the new user
$q = 'GRANT ALL PRIVILEGES ON ' . PMA_Util::backquote(PMA_Util::sqlAddSlashes($dbname)) . '.* TO \'' . PMA_Util::sqlAddSlashes($username) . '\'@\'' . PMA_Util::sqlAddSlashes($hostname) . '\';';
$sql_query .= $q;
if (!PMA_DBI_try_query($q)) {
$message = PMA_Message::rawError(PMA_DBI_getError());
}
}
return array($sql_query, $message);
}
/**
* @global bool whether selected db is information_schema
*/
$db_is_information_schema = false;
if (PMA_is_system_schema($db)) {
$is_show_stats = false;
$db_is_information_schema = true;
}
/**
* @global array information about tables in db
*/
$tables = array();
// When used in Nested table group mode,
// only show tables matching the given groupname
if (PMA_isValid($tbl_group)) {
$tbl_group_sql = ' LIKE "' . PMA_Util::escapeMysqlWildcards($tbl_group) . '%"';
} else {
$tbl_group_sql = '';
}
$tooltip_truename = array();
$tooltip_aliasname = array();
// Special speedup for newer MySQL Versions (in 4.0 format changed)
if (true === $cfg['SkipLockedTables']) {
$db_info_result = PMA_DBI_query('SHOW OPEN TABLES FROM ' . PMA_Util::backquote($db) . ';');
// Blending out tables in use
if ($db_info_result && PMA_DBI_num_rows($db_info_result) > 0) {
while ($tmp = PMA_DBI_fetch_row($db_info_result)) {
// if in use memorize tablename
if (preg_match('@in_use=[1-9]+@i', $tmp[1])) {
$sot_cache[$tmp[0]] = true;
}
$db_info_result = $GLOBALS['dbi']->query('SHOW OPEN TABLES FROM ' . PMA_Util::backquote($db) . ';');
// Blending out tables in use
if ($db_info_result && $GLOBALS['dbi']->numRows($db_info_result) > 0) {
while ($tmp = $GLOBALS['dbi']->fetchAssoc($db_info_result)) {
// if in use, memorize table name
if ($tmp['In_use'] > 0) {
$sot_cache[$tmp['Table']] = true;
}
}
$GLOBALS['dbi']->freeResult($db_info_result);
if (isset($sot_cache)) {
$db_info_result = false;
$tblGroupSql = "";
$whereAdded = false;
if (PMA_isValid($_REQUEST['tbl_group'])) {
$tblGroupSql .= " WHERE " . PMA_Util::backquote('Tables_in_' . $db) . " LIKE '" . PMA_Util::escapeMysqlWildcards($_REQUEST['tbl_group']) . "%'";
$whereAdded = true;
}
if (PMA_isValid($_REQUEST['tbl_type'], array('table', 'view'))) {
$tblGroupSql .= $whereAdded ? " AND" : " WHERE";
if ($_REQUEST['tbl_type'] == 'view') {
$tblGroupSql .= " `Table_type` != 'BASE TABLE'";
} else {
$tblGroupSql .= " `Table_type` = 'BASE TABLE'";
}
}
$db_info_result = $GLOBALS['dbi']->query('SHOW FULL TABLES FROM ' . PMA_Util::backquote($db) . $tblGroupSql, null, PMA_DatabaseInterface::QUERY_STORE);
unset($tblGroupSql, $whereAdded);
if ($db_info_result && $GLOBALS['dbi']->numRows($db_info_result) > 0) {
while ($tmp = $GLOBALS['dbi']->fetchRow($db_info_result)) {
if (!isset($sot_cache[$tmp[0]])) {
/**
* returns a segment of the SQL WHERE clause regarding table name and type
*
* @param string|bool $table table or false
* @param boolean $tbl_is_group $table is a table group
* @param string $table_type whether table or view
*
* @return string a segment of the WHERE clause
*/
private function _getTableCondition($table, $tbl_is_group, $table_type)
{
// get table information from information_schema
if ($table && is_string($table)) {
if (true === $tbl_is_group) {
$sql_where_table = 'AND t.`TABLE_NAME` LIKE \'' . PMA_Util::escapeMysqlWildcards(PMA_Util::sqlAddSlashes($table)) . '%\'';
} else {
$sql_where_table = 'AND t.`TABLE_NAME` = \'' . PMA_Util::sqlAddSlashes($table) . '\'';
}
} else {
$sql_where_table = '';
}
if ($table_type) {
if ($table_type == 'view') {
if (PMA_DRIZZLE) {
$sql_where_table .= " AND t.`TABLE_TYPE` != 'BASE'";
} else {
$sql_where_table .= " AND t.`TABLE_TYPE` != 'BASE TABLE'";
}
} else {
if ($table_type == 'table') {
if (PMA_DRIZZLE) {
$sql_where_table .= " AND t.`TABLE_TYPE` = 'BASE'";
} else {
$sql_where_table .= " AND t.`TABLE_TYPE` = 'BASE TABLE'";
}
}
}
}
return $sql_where_table;
}
