PHP PMA_Table::generateAlter方法代码示例

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