/**
* Test for generateAlter
*
* @return void
*/
public function testGenerateAlter()
{
//parameter
$oldcol = 'name';
$newcol = 'new_name';
$type = 'VARCHAR';
$length = '2';
$attribute = 'new_name';
$collation = 'charset1';
$null = 'NULL';
$default_type = 'USER_DEFINED';
$default_value = 'VARCHAR';
$extra = 'AUTO_INCREMENT';
$comment = 'PMA comment';
$virtuality = '';
$expression = '';
$move_to = 'new_name';
$result = PMA_Table::generateAlter($oldcol, $newcol, $type, $length, $attribute, $collation, $null, $default_type, $default_value, $extra, $comment, $virtuality, $expression, $move_to);
$expect = "`name` `new_name` VARCHAR(2) new_name CHARACTER " . "SET charset1 NULL DEFAULT 'VARCHAR' " . "AUTO_INCREMENT COMMENT 'PMA comment' AFTER `new_name`";
$this->assertEquals($expect, $result);
}
/**
* Moves columns in the table's structure based on $_REQUEST
*
* @param string $db database name
* @param string $table table name
*
* @return void
*/
function PMA_moveColumns($db, $table)
{
$GLOBALS['dbi']->selectDb($db);
/*
* load the definitions for all columns
*/
$columns = $GLOBALS['dbi']->getColumnsFull($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['Type'] == 'datetime') && $data['Default'] == 'CURRENT_TIMESTAMP') {
$current_timestamp = true;
}
$default_type = $data['Null'] === 'YES' && $data['Default'] === null ? 'NULL' : ($current_timestamp ? 'CURRENT_TIMESTAMP' : ($data['Default'] === null ? '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['COLUMN_COMMENT']) && $data['COLUMN_COMMENT'] !== '' ? $data['COLUMN_COMMENT'] : 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
$GLOBALS['dbi']->tryQuery($move_query);
$tmp_error = $GLOBALS['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;
}
$changes = array();
for ($i = 0; $i < $field_cnt; $i++) {
$changes[] = 'CHANGE ' . PMA_Table::generateAlter(
$_REQUEST['field_orig'][$i],
$_REQUEST['field_name'][$i],
$_REQUEST['field_type'][$i],
$_REQUEST['field_length'][$i],
$_REQUEST['field_attribute'][$i],
isset($_REQUEST['field_collation'][$i])
? $_REQUEST['field_collation'][$i]
: '',
isset($_REQUEST['field_null'][$i])
? $_REQUEST['field_null'][$i]
: 'NOT NULL',
$_REQUEST['field_default_type'][$i],
$_REQUEST['field_default_value'][$i],
isset($_REQUEST['field_extra'][$i])
? $_REQUEST['field_extra'][$i]
: false,
isset($_REQUEST['field_comments'][$i])
? $_REQUEST['field_comments'][$i]
: '',
$key_fields,
$i,
isset($_REQUEST['field_move_to'][$i])
? $_REQUEST['field_move_to'][$i]
: ''
);
} // end for
// Builds the primary keys statements and updates the table
/**
* Test for generateAlter
*
* @return void
*/
public function testGenerateAlter()
{
$table = 'PMA_BookMark';
$db = 'PMA';
//parameter
$oldcol = 'name';
$newcol = 'new_name';
$type = 'VARCHAR';
$length = '2';
$attribute = 'new_name';
$collation = 'charset1';
$null = 'NULL';
$default_type = 'USER_DEFINED';
$default_value = 'VARCHAR';
$extra = 'AUTO_INCREMENT';
$comment = 'PMA comment';
$field_primary = 'new_name';
$index = array('new_name');
$move_to = 'new_name';
$result = PMA_Table::generateAlter($oldcol, $newcol, $type, $length, $attribute, $collation, $null, $default_type, $default_value, $extra, $comment, $field_primary, $index, $move_to);
$expect = "";
if (PMA_DRIZZLE) {
$expect = "`name` `new_name` VARCHAR(2) new_name " . "COLLATE charset1 NULL DEFAULT 'VARCHAR' " . "AUTO_INCREMENT COMMENT 'PMA comment' AFTER `new_name`";
} else {
$expect = "`name` `new_name` VARCHAR(2) new_name CHARACTER " . "SET charset1 NULL DEFAULT 'VARCHAR' " . "AUTO_INCREMENT COMMENT 'PMA comment' AFTER `new_name`";
}
$this->assertEquals($expect, $result);
}
/**
* Set a single comment to a certain value.
*
* @uses PMA_MYSQL_INT_VERSION
* @uses PMA_DBI_QUERY_STORE
* @uses PMA_DBI_try_query()
* @uses PMA_DBI_num_rows()
* @uses PMA_DBI_fetch_assoc()
* @uses PMA_DBI_free_result()
* @uses PMA_Table::generateAlter()
* @uses PMA_getRelationsParam()
* @uses PMA_backquote()
* @uses PMA_sqlAddslashes()
* @uses PMA_query_as_cu()
* @uses strlen()
* @access public
* @param string $db the name of the db
* @param string $table the name of the table (may be empty in case of a db comment)
* @param string $col the name of the column
* @param string $comment the value of the column
* @param string $removekey if a column is renamed, this is the name of the former key which will get deleted
* @param string $mode whether we set pmadb comments, native comments or both
* @return boolean true, if comment-query was made.
*/
function PMA_setComment($db, $table, $col, $comment, $removekey = '', $mode = 'auto')
{
$cfgRelation = PMA_getRelationsParam();
if ($mode == 'auto') {
if (PMA_MYSQL_INT_VERSION >= 40100) {
$mode = 'native';
} else {
$mode = 'pmadb';
}
}
// native mode is only for column comments so we need a table name
if ($mode == 'native' && strlen($table)) {
$query = 'ALTER TABLE ' . PMA_backquote($table) . ' CHANGE ' . PMA_Table::generateAlter($col, $col, '', '', '', '', false, '', false, '', $comment, '', '');
return PMA_DBI_try_query($query, null, PMA_DBI_QUERY_STORE);
}
if (!$cfgRelation['commwork']) {
return false;
}
// $mode == 'pmadb' section:
if ($removekey != '' && $removekey != $col) {
$remove_query = '
DELETE FROM
' . PMA_backquote($cfgRelation['db']) . '.' . PMA_backquote($cfgRelation['column_info']) . '
WHERE `db_name` = \'' . PMA_sqlAddslashes($db) . '\'
AND `table_name` = \'' . PMA_sqlAddslashes($table) . '\'
AND `column_name` = \'' . PMA_sqlAddslashes($removekey) . '\'';
PMA_query_as_cu($remove_query);
}
$test_qry = '
SELECT `comment`,
mimetype,
transformation,
transformation_options
FROM ' . PMA_backquote($cfgRelation['db']) . '.' . PMA_backquote($cfgRelation['column_info']) . '
WHERE `db_name` = \'' . PMA_sqlAddslashes($db) . '\'
AND `table_name` = \'' . PMA_sqlAddslashes($table) . '\'
AND `column_name` = \'' . PMA_sqlAddslashes($col) . '\'';
$test_rs = PMA_query_as_cu($test_qry, true, PMA_DBI_QUERY_STORE);
if ($test_rs && PMA_DBI_num_rows($test_rs) > 0) {
$row = PMA_DBI_fetch_assoc($test_rs);
PMA_DBI_free_result($test_rs);
if (strlen($comment) || strlen($row['mimetype']) || strlen($row['transformation']) || strlen($row['transformation_options'])) {
$upd_query = '
UPDATE ' . PMA_backquote($cfgRelation['db']) . '.' . PMA_backquote($cfgRelation['column_info']) . '
SET `comment` = \'' . PMA_sqlAddslashes($comment) . '\'
WHERE `db_name` = \'' . PMA_sqlAddslashes($db) . '\'
AND `table_name` = \'' . PMA_sqlAddslashes($table) . '\'
AND `column_name` = \'' . PMA_sqlAddSlashes($col) . '\'';
} else {
$upd_query = '
DELETE FROM
' . PMA_backquote($cfgRelation['db']) . '.' . PMA_backquote($cfgRelation['column_info']) . '
WHERE `db_name` = \'' . PMA_sqlAddslashes($db) . '\'
AND `table_name` = \'' . PMA_sqlAddslashes($table) . '\'
AND `column_name` = \'' . PMA_sqlAddslashes($col) . '\'';
}
} elseif (strlen($comment)) {
$upd_query = '
INSERT INTO
' . PMA_backquote($cfgRelation['db']) . '.' . PMA_backquote($cfgRelation['column_info']) . '
(`db_name`, `table_name`, `column_name`, `comment`)
VALUES (
\'' . PMA_sqlAddslashes($db) . '\',
\'' . PMA_sqlAddslashes($table) . '\',
\'' . PMA_sqlAddslashes($col) . '\',
\'' . PMA_sqlAddslashes($comment) . '\')';
}
if (isset($upd_query)) {
return PMA_query_as_cu($upd_query);
}
return false;
}
$field_name[$i] = $field_orig[$i];
}
$field_default_orig[$i] = urldecode($field_default_orig[$i]);
if (strcmp(str_replace('"', '"', $field_default_orig[$i]), $field_default[$i]) == 0) {
$field_default[$i] = $field_default_orig[$i];
}
$field_length_orig[$i] = urldecode($field_length_orig[$i]);
if (strcmp(str_replace('"', '"', $field_length_orig[$i]), $field_length[$i]) == 0) {
$field_length[$i] = $field_length_orig[$i];
}
if (!isset($query)) {
$query = '';
} else {
$query .= ', CHANGE ';
}
$query .= PMA_Table::generateAlter($field_orig[$i], $field_name[$i], $field_type[$i], $field_length[$i], $field_attribute[$i], isset($field_collation[$i]) ? $field_collation[$i] : '', $field_null[$i], $field_default[$i], isset($field_default_current_timestamp[$i]), $field_extra[$i], isset($field_comments[$i]) ? $field_comments[$i] : '', $field_default_orig[$i]);
}
// end for
// To allow replication, we first select the db to use and then run queries
// on this db.
PMA_DBI_select_db($db) or PMA_mysqlDie(PMA_DBI_getError(), 'USE ' . PMA_backquote($db) . ';', '', $err_url);
// Optimization fix - 2 May 2001 - Robbat2
$sql_query = 'ALTER TABLE ' . PMA_backquote($table) . ' CHANGE ' . $query;
$error_create = FALSE;
$result = PMA_DBI_try_query($sql_query) or $error_create = TRUE;
if ($error_create == FALSE) {
$message = $strTable . ' ' . htmlspecialchars($table) . ' ' . $strHasBeenAltered;
$btnDrop = 'Fake';
// garvin: If comments were sent, enable relation stuff
require_once './libraries/relation.lib.php';
require_once './libraries/transformations.lib.php';
/**
* Set a single comment to a certain value.
*
* @param string the name of the db
* @param string the name of the table (may be empty in case of a db comment)
* @param string the name of the column
* @param string the value of the column
* @param string (optional) if a column is renamed, this is the name of the former key which will get deleted
* @param string whether we set pmadb comments, native comments or both
*
* @return boolean true, if comment-query was made.
*
* @global array the list of relations settings
*
* @access public
*/
function PMA_setComment($db, $table, $col, $comment, $removekey = '', $mode = 'auto')
{
global $cfgRelation;
if ($mode == 'auto') {
if (PMA_MYSQL_INT_VERSION >= 40100) {
$mode = 'native';
} else {
$mode = 'pmadb';
}
}
// native mode is only for column comments so we need a table name
if ($mode == 'native' && isset($table) && strlen($table)) {
$query = 'ALTER TABLE ' . PMA_backquote($table) . ' CHANGE ' . PMA_Table::generateAlter($col, $col, '', '', '', '', false, '', false, '', $comment, '', '');
PMA_DBI_try_query($query, null, PMA_DBI_QUERY_STORE);
return true;
}
// $mode == 'pmadb' section:
$cols = array('db_name' => 'db_name ', 'table_name' => 'table_name ', 'column_name' => 'column_name');
if ($removekey != '' and $removekey != $col) {
$remove_query = '
DELETE FROM
' . PMA_backquote($cfgRelation['db']) . '.' . PMA_backquote($cfgRelation['column_info']) . '
WHERE ' . $cols['db_name'] . ' = \'' . PMA_sqlAddslashes($db) . '\'
AND ' . $cols['table_name'] . ' = \'' . PMA_sqlAddslashes($table) . '\'
AND ' . $cols['column_name'] . ' = \'' . PMA_sqlAddslashes($removekey) . '\'';
PMA_query_as_cu($remove_query);
unset($remove_query);
}
$test_qry = '
SELECT ' . PMA_backquote('comment') . ',
mimetype,
transformation,
transformation_options
FROM ' . PMA_backquote($cfgRelation['db']) . '.' . PMA_backquote($cfgRelation['column_info']) . '
WHERE ' . $cols['db_name'] . ' = \'' . PMA_sqlAddslashes($db) . '\'
AND ' . $cols['table_name'] . ' = \'' . PMA_sqlAddslashes($table) . '\'
AND ' . $cols['column_name'] . ' = \'' . PMA_sqlAddslashes($col) . '\'';
$test_rs = PMA_query_as_cu($test_qry, true, PMA_DBI_QUERY_STORE);
if ($test_rs && PMA_DBI_num_rows($test_rs) > 0) {
$row = PMA_DBI_fetch_assoc($test_rs);
PMA_DBI_free_result($test_rs);
if (strlen($comment) > 0 || strlen($row['mimetype']) > 0 || strlen($row['transformation']) > 0 || strlen($row['transformation_options']) > 0) {
$upd_query = '
UPDATE ' . PMA_backquote($cfgRelation['db']) . '.' . PMA_backquote($cfgRelation['column_info']) . '
SET ' . PMA_backquote('comment') . ' = \'' . PMA_sqlAddslashes($comment) . '\'
WHERE ' . $cols['db_name'] . ' = \'' . PMA_sqlAddslashes($db) . '\'
AND ' . $cols['table_name'] . ' = \'' . PMA_sqlAddslashes($table) . '\'
AND ' . $cols['column_name'] . ' = \'' . PMA_sqlAddSlashes($col) . '\'';
} else {
$upd_query = '
DELETE FROM
' . PMA_backquote($cfgRelation['db']) . '.' . PMA_backquote($cfgRelation['column_info']) . '
WHERE ' . $cols['db_name'] . ' = \'' . PMA_sqlAddslashes($db) . '\'
AND ' . $cols['table_name'] . ' = \'' . PMA_sqlAddslashes($table) . '\'
AND ' . $cols['column_name'] . ' = \'' . PMA_sqlAddslashes($col) . '\'';
}
} elseif (strlen($comment) > 0) {
$upd_query = '
INSERT INTO
' . PMA_backquote($cfgRelation['db']) . '.' . PMA_backquote($cfgRelation['column_info']) . '
(db_name, table_name, column_name, ' . PMA_backquote('comment') . ')
VALUES (
\'' . PMA_sqlAddslashes($db) . '\',
\'' . PMA_sqlAddslashes($table) . '\',
\'' . PMA_sqlAddslashes($col) . '\',
\'' . PMA_sqlAddslashes($comment) . '\')';
}
if (isset($upd_query)) {
$upd_rs = PMA_query_as_cu($upd_query);
unset($upd_query);
return true;
} else {
return false;
}
}
$changes = array();
for ($i = 0; $i < $field_cnt; $i++) {
$field_null_value = isset($_REQUEST['field_null'][$i]) ? 'Y' : 'N';
print_r($_REQUEST['field_null']);
print_r($_REQUEST['field_null_orig'][$i]);
if ($field_null_value == $_REQUEST['field_null_orig'][$i]) {
print_r('asfasdxxxx');
}
$field_null_value = $field_null_value == $_REQUEST['field_null_orig'][$i] ? false : ($field_null_value == 'Y' ? 'NULL' : 'NOT NULL');
print_r($field_null_value);
print_r('xxx');
//print_r($field_null_str);
//if($field_null_value == 'N')
//$field_null_str = 'NOT NULL';
//elseif(
$changes[] = '' . PMA_Table::generateAlter('', $_REQUEST['field_name'][$i], $_REQUEST['field_type'][$i], $_REQUEST['field_length'][$i], $_REQUEST['field_attribute'][$i], isset($_REQUEST['field_collation'][$i]) ? $_REQUEST['field_collation'][$i] : '', $field_null_value, $_REQUEST['field_default_type'][$i], $_REQUEST['field_default_value'][$i], isset($_REQUEST['field_extra'][$i]) ? $_REQUEST['field_extra'][$i] : false, isset($_REQUEST['field_comments'][$i]) ? $_REQUEST['field_comments'][$i] : '', $key_fields, $i, $_REQUEST['field_default_orig'][$i]);
}
// end for
//print_r($changes);
//print_r($_REQUEST);
// Builds the primary keys statements and updates the table
$key_query = '';
/**
* this is a little bit more complex
*
* @todo if someone selects A_I when altering a column we need to check:
* - no other column with A_I
* - the column has an index, if not create one
*
if (count($key_fields)) {
$fields = array();
