/** * 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();