/**
* returns detailed array with all columns for given table in database,
* or all tables/databases
*
* @param string $database name of database
* @param string $table name of table to retrieve columns from
* @param string $column name of specific column
* @param mixed $link mysql link resource
*/
function PMA_DBI_get_columns_full($database = null, $table = null, $column = null, $link = null)
{
$columns = array();
if (!$GLOBALS['cfg']['Server']['DisableIS']) {
$sql_wheres = array();
$array_keys = array();
// get columns information from information_schema
if (null !== $database) {
$sql_wheres[] = '`TABLE_SCHEMA` = \'' . addslashes($database) . '\' ';
} else {
$array_keys[] = 'TABLE_SCHEMA';
}
if (null !== $table) {
$sql_wheres[] = '`TABLE_NAME` = \'' . addslashes($table) . '\' ';
} else {
$array_keys[] = 'TABLE_NAME';
}
if (null !== $column) {
$sql_wheres[] = '`COLUMN_NAME` = \'' . addslashes($column) . '\' ';
} else {
$array_keys[] = 'COLUMN_NAME';
}
// for PMA bc:
// `[SCHEMA_FIELD_NAME]` AS `[SHOW_FULL_COLUMNS_FIELD_NAME]`
$sql = '
SELECT *,
`COLUMN_NAME` AS `Field`,
`COLUMN_TYPE` AS `Type`,
`COLLATION_NAME` AS `Collation`,
`IS_NULLABLE` AS `Null`,
`COLUMN_KEY` AS `Key`,
`COLUMN_DEFAULT` AS `Default`,
`EXTRA` AS `Extra`,
`PRIVILEGES` AS `Privileges`,
`COLUMN_COMMENT` AS `Comment`
FROM `information_schema`.`COLUMNS`';
if (count($sql_wheres)) {
$sql .= "\n" . ' WHERE ' . implode(' AND ', $sql_wheres);
}
$columns = PMA_DBI_fetch_result($sql, $array_keys, null, $link);
unset($sql_wheres, $sql);
} else {
if (null === $database) {
foreach ($GLOBALS['pma']->databases as $database) {
$columns[$database] = PMA_DBI_get_columns_full($database, null, null, $link);
}
return $columns;
} elseif (null === $table) {
$tables = PMA_DBI_get_tables($database);
foreach ($tables as $table) {
$columns[$table] = PMA_DBI_get_columns_full($database, $table, null, $link);
}
return $columns;
}
$sql = 'SHOW FULL COLUMNS FROM ' . PMA_backquote($database) . '.' . PMA_backquote($table);
if (null !== $column) {
$sql .= " LIKE '" . $column . "'";
}
$columns = PMA_DBI_fetch_result($sql, 'Field', null, $link);
$ordinal_position = 1;
foreach ($columns as $column_name => $each_column) {
// MySQL forward compatibility
// so pma could use this array as if every server is of version >5.0
$columns[$column_name]['COLUMN_NAME'] =& $columns[$column_name]['Field'];
$columns[$column_name]['COLUMN_TYPE'] =& $columns[$column_name]['Type'];
$columns[$column_name]['COLLATION_NAME'] =& $columns[$column_name]['Collation'];
$columns[$column_name]['IS_NULLABLE'] =& $columns[$column_name]['Null'];
$columns[$column_name]['COLUMN_KEY'] =& $columns[$column_name]['Key'];
$columns[$column_name]['COLUMN_DEFAULT'] =& $columns[$column_name]['Default'];
$columns[$column_name]['EXTRA'] =& $columns[$column_name]['Extra'];
$columns[$column_name]['PRIVILEGES'] =& $columns[$column_name]['Privileges'];
$columns[$column_name]['COLUMN_COMMENT'] =& $columns[$column_name]['Comment'];
$columns[$column_name]['TABLE_CATALOG'] = null;
$columns[$column_name]['TABLE_SCHEMA'] = $database;
$columns[$column_name]['TABLE_NAME'] = $table;
$columns[$column_name]['ORDINAL_POSITION'] = $ordinal_position;
$columns[$column_name]['DATA_TYPE'] = substr($columns[$column_name]['COLUMN_TYPE'], 0, strpos($columns[$column_name]['COLUMN_TYPE'], '('));
/**
* @todo guess CHARACTER_MAXIMUM_LENGTH from COLUMN_TYPE
*/
$columns[$column_name]['CHARACTER_MAXIMUM_LENGTH'] = null;
/**
* @todo guess CHARACTER_OCTET_LENGTH from CHARACTER_MAXIMUM_LENGTH
*/
$columns[$column_name]['CHARACTER_OCTET_LENGTH'] = null;
$columns[$column_name]['NUMERIC_PRECISION'] = null;
$columns[$column_name]['NUMERIC_SCALE'] = null;
$columns[$column_name]['CHARACTER_SET_NAME'] = substr($columns[$column_name]['COLLATION_NAME'], 0, strpos($columns[$column_name]['COLLATION_NAME'], '_'));
$ordinal_position++;
}
if (null !== $column) {
reset($columns);
$columns = current($columns);
}
}
return $columns;
}
/**
* Returns a stand-in CREATE definition to resolve view dependencies
*
* @param string $db the database name
* @param string $view the view name
* @param string $crlf the end of line sequence
*
* @return string resulting definition
*/
public function getTableDefStandIn($db, $view, $crlf)
{
$create_query = '';
if (!empty($GLOBALS['sql_drop_table'])) {
$create_query .= 'DROP VIEW IF EXISTS ' . PMA_Util::backquote($view) . ';' . $crlf;
}
$create_query .= 'CREATE TABLE ';
if (isset($GLOBALS['sql_if_not_exists']) && $GLOBALS['sql_if_not_exists']) {
$create_query .= 'IF NOT EXISTS ';
}
$create_query .= PMA_Util::backquote($view) . ' (' . $crlf;
$tmp = array();
$columns = PMA_DBI_get_columns_full($db, $view);
foreach ($columns as $column_name => $definition) {
$tmp[] = PMA_Util::backquote($column_name) . ' ' . $definition['Type'] . $crlf;
}
$create_query .= implode(',', $tmp) . ');';
return $create_query;
}
/**
* Gets all the column information for source and target table.
* Compare columns on their names.
* If column exists in target then compare Type, Null, Collation, Key, Default and Comment for that column.
* If column does not exist in target table then it is placed in $add_column_array.
* If column exists in target table but criteria is different then it is palced in $alter_str_array.
* If column does not exist in source table but is present in target table then it is placed in $uncommon_columns.
* Keys for all the source tables that have a corresponding target table are placed in $matching_tables_keys.
* Keys for all the target tables that have a corresponding source table are placed in $target_tables_keys.
*
* @param string $src_db name of source database
* @param string $trg_db name of target database
* @param mixed $src_link connection established with source server
* @param mixed $trg_link connection established with target server
* @param array $matching_tables names of matching tables
* @param array &$source_columns columns information of the source tables
* @param array &$target_columns columns information of the target tables
* @param array &$alter_str_array three dimensional associative array first index being the matching table index, second index being column name for which target
* column have some criteria different and third index containing the criteria which is different.
* @param array &$add_column_array two dimensional associative array, first index of the array contain the matching table number and second index contain the
* column name which is to be added in the target table
* @param array &$uncommon_columns columns that are present in the target table but not in the source table
* @param array $criteria criteria which are to be checked for field that is present in source table and target table
* @param array &$target_tables_keys field names which is key in the target table
* @param int $matching_table_index number of the matching table
*/
function PMA_structureDiffInTables($src_db, $trg_db, $src_link, $trg_link, $matching_tables, &$source_columns, &$target_columns, &$alter_str_array, &$add_column_array, &$uncommon_columns, $criteria, &$target_tables_keys, $matching_table_index)
{
//Gets column information for source and target table
$source_columns[$matching_table_index] = PMA_DBI_get_columns_full($src_db, $matching_tables[$matching_table_index], null, $src_link);
$target_columns[$matching_table_index] = PMA_DBI_get_columns_full($trg_db, $matching_tables[$matching_table_index], null, $trg_link);
foreach ($source_columns[$matching_table_index] as $column_name => $each_column) {
if (isset($target_columns[$matching_table_index][$column_name]['Field'])) {
//If column exists in target table then matches criteria like type, null, collation, key, default, comment of the column
for ($i = 0; $i < sizeof($criteria); $i++) {
if ($source_columns[$matching_table_index][$column_name][$criteria[$i]] != $target_columns[$matching_table_index][$column_name][$criteria[$i]]) {
if ($criteria[$i] == 'Default' && $source_columns[$matching_table_index][$column_name][$criteria[$i]] == '') {
$alter_str_array[$matching_table_index][$column_name][$criteria[$i]] = 'None';
} else {
if (!($criteria[$i] == 'Key' && ($source_columns[$matching_table_index][$column_name][$criteria[$i]] == 'MUL' || $target_columns[$matching_table_index][$column_name][$criteria[$i]] == 'MUL' || $source_columns[$matching_table_index][$column_name][$criteria[$i]] == 'UNI' || $target_columns[$matching_table_index][$column_name][$criteria[$i]] == 'UNI'))) {
$alter_str_array[$matching_table_index][$column_name][$criteria[$i]] = $source_columns[$matching_table_index][$column_name][$criteria[$i]];
}
}
}
}
} else {
$add_column_array[$matching_table_index][$column_name] = $column_name;
}
}
//Finds column names that are present in target table but not in source table
foreach ($target_columns[$matching_table_index] as $fld_name => $each_column) {
if (!isset($source_columns[$matching_table_index][$fld_name]['Field'])) {
$fields_uncommon[] = $fld_name;
}
if ($target_columns[$matching_table_index][$fld_name]['Key'] == 'PRI') {
$keys[] = $fld_name;
}
}
if (isset($fields_uncommon)) {
$uncommon_columns[$matching_table_index] = $fields_uncommon;
}
if (isset($keys)) {
$target_tables_keys[$matching_table_index] = $keys;
}
}
/**
* returns detailed array with all columns for given table in database,
* or all tables/databases
*
* @param string $database name of database
* @param string $table name of table to retrieve columns from
* @param string $column name of specific column
* @param mixed $link mysql link resource
*
* @return array
*/
function PMA_DBI_get_columns_full($database = null, $table = null, $column = null, $link = null)
{
$common_functions = PMA_CommonFunctions::getInstance();
$columns = array();
if (!$GLOBALS['cfg']['Server']['DisableIS']) {
$sql_wheres = array();
$array_keys = array();
// get columns information from information_schema
if (null !== $database) {
$sql_wheres[] = '`TABLE_SCHEMA` = \'' . $common_functions->sqlAddSlashes($database) . '\' ';
} else {
$array_keys[] = 'TABLE_SCHEMA';
}
if (null !== $table) {
$sql_wheres[] = '`TABLE_NAME` = \'' . $common_functions->sqlAddSlashes($table) . '\' ';
} else {
$array_keys[] = 'TABLE_NAME';
}
if (null !== $column) {
$sql_wheres[] = '`COLUMN_NAME` = \'' . $common_functions->sqlAddSlashes($column) . '\' ';
} else {
$array_keys[] = 'COLUMN_NAME';
}
// for PMA bc:
// `[SCHEMA_FIELD_NAME]` AS `[SHOW_FULL_COLUMNS_FIELD_NAME]`
if (PMA_DRIZZLE) {
$sql = "SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,\n column_name AS `Field`,\n (CASE\n WHEN character_maximum_length > 0\n THEN concat(lower(data_type), '(', character_maximum_length, ')')\n WHEN numeric_precision > 0 OR numeric_scale > 0\n THEN concat(lower(data_type), '(', numeric_precision, ',', numeric_scale, ')')\n WHEN enum_values IS NOT NULL\n THEN concat(lower(data_type), '(', enum_values, ')')\n ELSE lower(data_type) END)\n AS `Type`,\n collation_name AS `Collation`,\n (CASE is_nullable\n WHEN 1 THEN 'YES'\n ELSE 'NO' END) AS `Null`,\n (CASE\n WHEN is_used_in_primary THEN 'PRI'\n ELSE '' END) AS `Key`,\n column_default AS `Default`,\n (CASE\n WHEN is_auto_increment THEN 'auto_increment'\n WHEN column_default_update THEN 'on update ' || column_default_update\n ELSE '' END) AS `Extra`,\n NULL AS `Privileges`,\n column_comment AS `Comment`\n FROM data_dictionary.columns";
} else {
$sql = '
SELECT *,
`COLUMN_NAME` AS `Field`,
`COLUMN_TYPE` AS `Type`,
`COLLATION_NAME` AS `Collation`,
`IS_NULLABLE` AS `Null`,
`COLUMN_KEY` AS `Key`,
`COLUMN_DEFAULT` AS `Default`,
`EXTRA` AS `Extra`,
`PRIVILEGES` AS `Privileges`,
`COLUMN_COMMENT` AS `Comment`
FROM `information_schema`.`COLUMNS`';
}
if (count($sql_wheres)) {
$sql .= "\n" . ' WHERE ' . implode(' AND ', $sql_wheres);
}
$columns = PMA_DBI_fetch_result($sql, $array_keys, null, $link);
unset($sql_wheres, $sql);
} else {
if (null === $database) {
foreach ($GLOBALS['pma']->databases as $database) {
$columns[$database] = PMA_DBI_get_columns_full($database, null, null, $link);
}
return $columns;
} elseif (null === $table) {
$tables = PMA_DBI_get_tables($database);
foreach ($tables as $table) {
$columns[$table] = PMA_DBI_get_columns_full($database, $table, null, $link);
}
return $columns;
}
$sql = 'SHOW FULL COLUMNS FROM ' . $common_functions->backquote($database) . '.' . $common_functions->backquote($table);
if (null !== $column) {
$sql .= " LIKE '" . $common_functions->sqlAddSlashes($column, true) . "'";
}
$columns = PMA_DBI_fetch_result($sql, 'Field', null, $link);
}
$ordinal_position = 1;
foreach ($columns as $column_name => $each_column) {
// MySQL forward compatibility
// so pma could use this array as if every server is of version >5.0
// todo : remove and check the rest of the code for usage,
// MySQL 5.0 or higher is required for current PMA version
$columns[$column_name]['COLUMN_NAME'] =& $columns[$column_name]['Field'];
$columns[$column_name]['COLUMN_TYPE'] =& $columns[$column_name]['Type'];
$columns[$column_name]['COLLATION_NAME'] =& $columns[$column_name]['Collation'];
$columns[$column_name]['IS_NULLABLE'] =& $columns[$column_name]['Null'];
$columns[$column_name]['COLUMN_KEY'] =& $columns[$column_name]['Key'];
$columns[$column_name]['COLUMN_DEFAULT'] =& $columns[$column_name]['Default'];
$columns[$column_name]['EXTRA'] =& $columns[$column_name]['Extra'];
$columns[$column_name]['PRIVILEGES'] =& $columns[$column_name]['Privileges'];
$columns[$column_name]['COLUMN_COMMENT'] =& $columns[$column_name]['Comment'];
$columns[$column_name]['TABLE_CATALOG'] = null;
$columns[$column_name]['TABLE_SCHEMA'] = $database;
$columns[$column_name]['TABLE_NAME'] = $table;
$columns[$column_name]['ORDINAL_POSITION'] = $ordinal_position;
$columns[$column_name]['DATA_TYPE'] = substr($columns[$column_name]['COLUMN_TYPE'], 0, strpos($columns[$column_name]['COLUMN_TYPE'], '('));
/**
* @todo guess CHARACTER_MAXIMUM_LENGTH from COLUMN_TYPE
*/
$columns[$column_name]['CHARACTER_MAXIMUM_LENGTH'] = null;
/**
* @todo guess CHARACTER_OCTET_LENGTH from CHARACTER_MAXIMUM_LENGTH
*/
$columns[$column_name]['CHARACTER_OCTET_LENGTH'] = null;
$columns[$column_name]['NUMERIC_PRECISION'] = null;
$columns[$column_name]['NUMERIC_SCALE'] = null;
$columns[$column_name]['CHARACTER_SET_NAME'] = substr($columns[$column_name]['COLLATION_NAME'], 0, strpos($columns[$column_name]['COLLATION_NAME'], '_'));
$ordinal_position++;
}
if (null !== $column) {
reset($columns);
$columns = current($columns);
}
return $columns;
}
/**
* Defines the url to return to in case of error in a sql statement
*/
$err_url = 'tbl_structure.php?' . PMA_generate_common_url($db, $table);
/**
* Moving columns
*/
if (isset($_REQUEST['move_columns'])
&& is_array($_REQUEST['move_columns'])
&& $GLOBALS['is_ajax_request']) {
/*
* first, load the definitions for all columns
*/
$columns = PMA_DBI_get_columns_full($db, $table);
$column_names = array_keys($columns);
$changes = array();
$we_dont_change_keys = array();
// move columns from first to last
for ($i = 0, $l = count($_REQUEST['move_columns']); $i < $l; $i++) {
$column = $_REQUEST['move_columns'][$i];
// is this column already correctly placed?
if ($column_names[$i] == $column) {
continue;
}
// it is not, let's move it to index $i
$data = $columns[$column];
$extracted_columnspec = $common_functions->extractColumnSpec($data['Type']);
$and_or_row = isset($_REQUEST['and_or_row']) ? $_REQUEST['and_or_row'] : array_fill(0, $col_cnt, '');
$and_or_col = isset($_REQUEST['and_or_col']) ? $_REQUEST['and_or_col'] : array_fill(0, $col_cnt, '');
// minimum width
$form_column_width = 12;
$col = max($col_cnt + $add_col, 0);
$row = max($rows + $add_row, 0);
// The tables list sent by a previously submitted form
if (!empty($TableList)) {
$cnt_table_list = count($TableList);
for ($x = 0; $x < $cnt_table_list; $x++) {
$tbl_names[urldecode($TableList[$x])] = ' selected="selected"';
}
}
// end if
$columns = PMA_DBI_get_columns_full($GLOBALS['db']);
$tables = PMA_DBI_get_columns_full($GLOBALS['db']);
/**
* Prepares the form
*/
$tbl_result = PMA_DBI_query('SHOW TABLES FROM ' . PMA_backquote($db) . ';', null, PMA_DBI_QUERY_STORE);
$tbl_result_cnt = PMA_DBI_num_rows($tbl_result);
$i = 0;
$k = 0;
// The tables list gets from MySQL
while ($i < $tbl_result_cnt) {
list($tbl) = PMA_DBI_fetch_row($tbl_result);
$fld_results = PMA_DBI_get_fields($db, $tbl);
$fld_results_cnt = $fld_results ? count($fld_results) : 0;
$j = 0;
if (empty($tbl_names[$tbl]) && !empty($TableList)) {
$tbl_names[$tbl] = '';
/**
* Returns a stand-in CREATE definition to resolve view dependencies
*
* @param string the database name
* @param string the vew name
* @param string the end of line sequence
*
* @return string resulting definition
*
* @access public
*/
function PMA_getTableDefStandIn($db, $view, $crlf)
{
$create_query = 'CREATE TABLE ' . PMA_backquote($view) . ' (' . $crlf;
$tmp = array();
$columns = PMA_DBI_get_columns_full($db, $view);
foreach ($columns as $column_name => $definition) {
$tmp[] = PMA_backquote($column_name) . ' ' . $definition['Type'] . $crlf;
}
$create_query .= implode(',', $tmp) . ');';
return $create_query;
}
/**
* Moves columns in the table's structure based on $_REQUEST
*
* @param string $db database name
* @param string $table table name
*/
function PMA_moveColumns($db, $table)
{
PMA_DBI_select_db($db);
/*
* load the definitions for all columns
*/
$columns = PMA_DBI_get_columns_full($db, $table);
$column_names = array_keys($columns);
$changes = array();
$we_dont_change_keys = array();
// move columns from first to last
for ($i = 0, $l = count($_REQUEST['move_columns']); $i < $l; $i++) {
$column = $_REQUEST['move_columns'][$i];
// is this column already correctly placed?
if ($column_names[$i] == $column) {
continue;
}
// it is not, let's move it to index $i
$data = $columns[$column];
$extracted_columnspec = PMA_Util::extractColumnSpec($data['Type']);
if (isset($data['Extra']) && $data['Extra'] == 'on update CURRENT_TIMESTAMP') {
$extracted_columnspec['attribute'] = $data['Extra'];
unset($data['Extra']);
}
$current_timestamp = false;
if ($data['Type'] == 'timestamp' && $data['Default'] == 'CURRENT_TIMESTAMP') {
$current_timestamp = true;
}
$default_type = $data['Null'] === 'YES' && $data['Default'] === null ? 'NULL' : ($current_timestamp ? 'CURRENT_TIMESTAMP' : ($data['Default'] == '' ? 'NONE' : 'USER_DEFINED'));
$changes[] = 'CHANGE ' . PMA_Table::generateAlter($column, $column, strtoupper($extracted_columnspec['type']), $extracted_columnspec['spec_in_brackets'], $extracted_columnspec['attribute'], isset($data['Collation']) ? $data['Collation'] : '', $data['Null'] === 'YES' ? 'NULL' : 'NOT NULL', $default_type, $current_timestamp ? '' : $data['Default'], isset($data['Extra']) && $data['Extra'] !== '' ? $data['Extra'] : false, isset($data['Comments']) && $data['Comments'] !== '' ? $data['Comments'] : false, $we_dont_change_keys, $i, $i === 0 ? '-first' : $column_names[$i - 1]);
// update current column_names array, first delete old position
for ($j = 0, $ll = count($column_names); $j < $ll; $j++) {
if ($column_names[$j] == $column) {
unset($column_names[$j]);
}
}
// insert moved column
array_splice($column_names, $i, 0, $column);
}
$response = PMA_Response::getInstance();
if (empty($changes)) {
// should never happen
$response->isSuccess(false);
exit;
}
$move_query = 'ALTER TABLE ' . PMA_Util::backquote($table) . ' ';
$move_query .= implode(', ', $changes);
// move columns
$result = PMA_DBI_try_query($move_query);
$tmp_error = PMA_DBI_getError();
if ($tmp_error) {
$response->isSuccess(false);
$response->addJSON('message', PMA_Message::error($tmp_error));
} else {
$message = PMA_Message::success(__('The columns have been moved successfully.'));
$response->addJSON('message', $message);
$response->addJSON('columns', $column_names);
}
exit;
}
/**
* Displays index edit/creation form and handles it
*
* @package PhpMyAdmin
*/
/**
* Gets some core libraries
*/
require_once 'libraries/common.inc.php';
require_once 'libraries/Index.class.php';
require_once 'libraries/tbl_common.php';
// Get fields and stores their name/type
$fields = array();
foreach (PMA_DBI_get_columns_full($db, $table) as $row) {
if (preg_match('@^(set|enum)\((.+)\)$@i', $row['Type'], $tmp)) {
$tmp[2] = substr(preg_replace('@([^,])\'\'@', '\\1\\\'',
',' . $tmp[2]), 1);
$fields[$row['Field']] = $tmp[1] . '(' . str_replace(',', ', ', $tmp[2]) . ')';
} else {
$fields[$row['Field']] = $row['Type'];
}
} // end while
// Prepares the form values
if (isset($_REQUEST['index'])) {
if (is_array($_REQUEST['index'])) {
// coming already from form
$index = new PMA_Index($_REQUEST['index']);
} else {