PHP PMA_previewSQL函数代码示例

/**
 * Function to handle the creation or edit of an index
 *
 * @param string    $db    current db
 * @param string    $table current table
 * @param PMA_Index $index current index
 *
 * @return void
 */
function PMA_handleCreateOrEditIndex($db, $table, $index)
{
    $error = false;
    $sql_query = PMA_getSqlQueryForIndexCreateOrEdit($db, $table, $index, $error);
    // If there is a request for SQL previewing.
    if (isset($_REQUEST['preview_sql'])) {
        PMA_previewSQL($sql_query);
    }
    if (!$error) {
        $GLOBALS['dbi']->query($sql_query);
        $message = PMA_Message::success(__('Table %1$s has been altered successfully.'));
        $message->addParam($table);
        if ($GLOBALS['is_ajax_request'] == true) {
            $response = PMA_Response::getInstance();
            $response->addJSON('message', $message);
            $response->addJSON('index_table', PMA_Index::getView($table, $db));
            $response->addJSON('sql_query', PMA_Util::getMessage(null, $sql_query));
        } else {
            include 'tbl_structure.php';
        }
        exit;
    } else {
        $response = PMA_Response::getInstance();
        $response->isSuccess(false);
        $response->addJSON('message', $error);
        exit;
    }
}

if ($GLOBALS['dbi']->getColumns($db, $table)) {
    // table exists already
    PMA\libraries\Util::mysqlDie(sprintf(__('Table %s already exists!'), htmlspecialchars($table)), '', false, 'db_structure.php' . PMA_URL_getCommon(array('db' => $db)));
}
// for libraries/tbl_columns_definition_form.inc.php
// check number of fields to be created
$num_fields = PMA_getNumberOfFieldsFromRequest();
$action = 'tbl_create.php';
/**
 * The form used to define the structure of the table has been submitted
 */
if (isset($_REQUEST['do_save_data'])) {
    $sql_query = PMA_getTableCreationQuery($db, $table);
    // If there is a request for SQL previewing.
    if (isset($_REQUEST['preview_sql'])) {
        PMA_previewSQL($sql_query);
    }
    // Executes the query
    $result = $GLOBALS['dbi']->tryQuery($sql_query);
    if ($result) {
        // If comments were sent, enable relation stuff
        include_once 'libraries/transformations.lib.php';
        // Update comment table for mime types [MIME]
        if (isset($_REQUEST['field_mimetype']) && is_array($_REQUEST['field_mimetype']) && $cfg['BrowseMIME']) {
            foreach ($_REQUEST['field_mimetype'] as $fieldindex => $mimetype) {
                if (isset($_REQUEST['field_name'][$fieldindex]) && mb_strlen($_REQUEST['field_name'][$fieldindex])) {
                    PMA_setMIME($db, $table, $_REQUEST['field_name'][$fieldindex], $mimetype, $_REQUEST['field_transformation'][$fieldindex], $_REQUEST['field_transformation_options'][$fieldindex], $_REQUEST['field_input_transformation'][$fieldindex], $_REQUEST['field_input_transformation_options'][$fieldindex]);
                }
            }
        }
    } else {

// Builds the sql query
if ($is_insert && count($value_sets) > 0) {
    $query = PMA_buildSqlQuery($is_insertignore, $query_fields, $value_sets);
} elseif (empty($query) && !isset($_REQUEST['preview_sql']) && !$row_skipped) {
    // No change -> move back to the calling script
    //
    // Note: logic passes here for inline edit
    $message = PMA_Message::success(__('No change'));
    $active_page = $goto_include;
    include '' . PMA_securePath($goto_include);
    exit;
}
unset($multi_edit_columns, $is_insertignore);
// If there is a request for SQL previewing.
if (isset($_REQUEST['preview_sql'])) {
    PMA_previewSQL($query);
}
/**
 * Executes the sql query and get the result, then move back to the calling
 * page
 */
list($url_params, $total_affected_rows, $last_messages, $warning_messages, $error_messages, $return_to_sql_query) = PMA_executeSqlQuery($url_params, $query);
if ($is_insert && (count($value_sets) > 0 || $row_skipped)) {
    $message = PMA_Message::getMessageForInsertedRows($total_affected_rows);
    $unsaved_values = array_values($unsaved_values);
} else {
    $message = PMA_Message::getMessageForAffectedRows($total_affected_rows);
}
if ($row_skipped) {
    $goto_include = 'tbl_change.php';
    $message->addMessages($insert_errors, '<br />');

/**
 * Function to execute the column creation statement
 *
 * @param string $db      current database
 * @param string $table   current table
 * @param string $err_url error page url
 *
 * @return array
 */
function PMA_tryColumnCreationQuery($db, $table, $err_url)
{
    // get column addition statements
    $sql_statement = PMA_getColumnCreationStatements(false);
    // To allow replication, we first select the db to use and then run queries
    // on this db.
    if (!$GLOBALS['dbi']->selectDb($db)) {
        PMA\libraries\Util::mysqlDie($GLOBALS['dbi']->getError(), 'USE ' . PMA\libraries\Util::backquote($db), false, $err_url);
    }
    $sql_query = 'ALTER TABLE ' . PMA\libraries\Util::backquote($table) . ' ' . $sql_statement . ';';
    // If there is a request for SQL previewing.
    if (isset($_REQUEST['preview_sql'])) {
        PMA_previewSQL($sql_query);
    }
    return array($GLOBALS['dbi']->tryQuery($sql_query), $sql_query);
}

/**
 * Update the table's structure based on $_REQUEST
 *
 * @param string $db    database name
 * @param string $table table name
 *
 * @return boolean $regenerate              true if error occurred
 *
 */
function PMA_updateColumns($db, $table)
{
    $err_url = 'tbl_structure.php' . PMA_URL_getCommon(array('db' => $db, 'table' => $table));
    $regenerate = false;
    $field_cnt = count($_REQUEST['field_name']);
    $changes = array();
    $pmatable = new PMA_Table($table, $db);
    for ($i = 0; $i < $field_cnt; $i++) {
        if (PMA_columnNeedsAlterTable($i)) {
            $changes[] = 'CHANGE ' . PMA_Table::generateAlter(isset($_REQUEST['field_orig'][$i]) ? $_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] : '', isset($_REQUEST['field_move_to'][$i]) ? $_REQUEST['field_move_to'][$i] : '');
            // find the remembered sort expression
            $sorted_col = $pmatable->getUiProp(PMA_Table::PROP_SORTED_COLUMN);
            // if the old column name is part of the remembered sort expression
            if (mb_strpos($sorted_col, PMA_Util::backquote($_REQUEST['field_orig'][$i])) !== false) {
                // delete the whole remembered sort expression
                $pmatable->removeUiProp(PMA_Table::PROP_SORTED_COLUMN);
            }
        }
    }
    // end for
    $response = PMA_Response::getInstance();
    if (count($changes) > 0 || isset($_REQUEST['preview_sql'])) {
        // 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
         *
         */
        // To allow replication, we first select the db to use
        // and then run queries on this db.
        if (!$GLOBALS['dbi']->selectDb($db)) {
            PMA_Util::mysqlDie($GLOBALS['dbi']->getError(), 'USE ' . PMA_Util::backquote($db) . ';', false, $err_url);
        }
        $sql_query = 'ALTER TABLE ' . PMA_Util::backquote($table) . ' ';
        $sql_query .= implode(', ', $changes) . $key_query;
        $sql_query .= ';';
        // If there is a request for SQL previewing.
        if (isset($_REQUEST['preview_sql'])) {
            PMA_previewSQL(count($changes) > 0 ? $sql_query : '');
        }
        $result = $GLOBALS['dbi']->tryQuery($sql_query);
        if ($result !== false) {
            $message = PMA_Message::success(__('Table %1$s has been altered successfully.'));
            $message->addParam($table);
            $response->addHTML(PMA_Util::getMessage($message, $sql_query, 'success'));
        } else {
            // An error happened while inserting/updating a table definition
            $response->isSuccess(false);
            $response->addJSON('message', PMA_Message::rawError(__('Query error') . ':<br />' . $GLOBALS['dbi']->getError()));
            $regenerate = true;
        }
    }
    include_once 'libraries/transformations.lib.php';
    // update field names in relation
    if (isset($_REQUEST['field_orig']) && is_array($_REQUEST['field_orig'])) {
        foreach ($_REQUEST['field_orig'] as $fieldindex => $fieldcontent) {
            if ($_REQUEST['field_name'][$fieldindex] != $fieldcontent) {
                PMA_REL_renameField($db, $table, $fieldcontent, $_REQUEST['field_name'][$fieldindex]);
            }
        }
    }
    // update mime types
    if (isset($_REQUEST['field_mimetype']) && is_array($_REQUEST['field_mimetype']) && $GLOBALS['cfg']['BrowseMIME']) {
        foreach ($_REQUEST['field_mimetype'] as $fieldindex => $mimetype) {
            if (isset($_REQUEST['field_name'][$fieldindex]) && mb_strlen($_REQUEST['field_name'][$fieldindex])) {
                PMA_setMIME($db, $table, $_REQUEST['field_name'][$fieldindex], $mimetype, $_REQUEST['field_transformation'][$fieldindex], $_REQUEST['field_transformation_options'][$fieldindex], $_REQUEST['field_input_transformation'][$fieldindex], $_REQUEST['field_input_transformation_options'][$fieldindex]);
            }
        }
    }
    return $regenerate;
}

 /**
  * Update for FK
  *
  * @return void
  */
 public function updateForForeignKeysAction()
 {
     $multi_edit_columns_name = isset($_REQUEST['foreign_key_fields_name']) ? $_REQUEST['foreign_key_fields_name'] : null;
     // (for now, one index name only; we keep the definitions if the
     // foreign db is not the same)
     list($html, $preview_sql_data, $display_query, $seen_error) = $this->upd_query->updateForeignKeys($_POST['destination_foreign_db'], $multi_edit_columns_name, $_POST['destination_foreign_table'], $_POST['destination_foreign_column'], $this->options_array, $this->table, isset($this->existrel_foreign) ? $this->existrel_foreign['foreign_keys_data'] : null);
     $this->response->addHTML($html);
     // If there is a request for SQL previewing.
     if (isset($_REQUEST['preview_sql'])) {
         PMA_previewSQL($preview_sql_data);
     }
     if (!empty($display_query) && !$seen_error) {
         $GLOBALS['display_query'] = $display_query;
         $this->response->addHTML(PMA_Util::getMessage(__('Your SQL query has been executed successfully.'), null, 'success'));
     }
 }

/**
 * Function to handle foreign key updates
 *
 * @param array  $destination_foreign_db     destination foreign database
 * @param array  $multi_edit_columns_name    multi edit column names
 * @param array  $destination_foreign_table  destination foreign table
 * @param array  $destination_foreign_column destination foreign column
 * @param array  $options_array              options array
 * @param string $table                      current table
 * @param array  $existrel_foreign           db, table, column
 *
 * @return string
 */
function PMA_handleUpdatesForForeignKeys($destination_foreign_db, $multi_edit_columns_name, $destination_foreign_table, $destination_foreign_column, $options_array, $table, $existrel_foreign)
{
    $html_output = '';
    $preview_sql_data = '';
    $display_query = '';
    $seen_error = false;
    $preview_sql = isset($_REQUEST['preview_sql']) ? true : false;
    foreach ($destination_foreign_db as $master_field_md5 => $foreign_db) {
        list($html, $sql_data) = PMA_handleUpdateForForeignKey($multi_edit_columns_name, $master_field_md5, $destination_foreign_table, $destination_foreign_column, $options_array, $existrel_foreign, $table, $seen_error, $display_query, $foreign_db, $preview_sql);
        $html_output .= $html;
        $preview_sql_data .= $sql_data;
    }
    // end foreach
    // If there is a request for SQL previewing.
    if ($preview_sql) {
        PMA_previewSQL($preview_sql_data);
    }
    if (!empty($display_query) && !$seen_error) {
        $GLOBALS['display_query'] = $display_query;
        $html_output = PMA_Util::getMessage(__('Your SQL query has been executed successfully.'), null, 'success');
    }
    return $html_output;
}

 /**
  * Update the table's structure based on $_REQUEST
  *
  * @return boolean $regenerate              true if error occurred
  *
  */
 protected function updateColumns()
 {
     $err_url = 'tbl_structure.php' . PMA_URL_getCommon(array('db' => $this->db, 'table' => $this->table));
     $regenerate = false;
     $field_cnt = count($_REQUEST['field_name']);
     $changes = array();
     $adjust_privileges = array();
     for ($i = 0; $i < $field_cnt; $i++) {
         if (!$this->columnNeedsAlterTable($i)) {
             continue;
         }
         $changes[] = 'CHANGE ' . PMA_Table::generateAlter(Util\get($_REQUEST, "field_orig.{$i}", ''), $_REQUEST['field_name'][$i], $_REQUEST['field_type'][$i], $_REQUEST['field_length'][$i], $_REQUEST['field_attribute'][$i], Util\get($_REQUEST, "field_collation.{$i}", ''), Util\get($_REQUEST, "field_null.{$i}", 'NOT NULL'), $_REQUEST['field_default_type'][$i], $_REQUEST['field_default_value'][$i], Util\get($_REQUEST, "field_extra.{$i}", false), Util\get($_REQUEST, "field_comments.{$i}", ''), Util\get($_REQUEST, "field_virtuality.{$i}", ''), Util\get($_REQUEST, "field_expression.{$i}", ''), Util\get($_REQUEST, "field_move_to.{$i}", ''));
         // find the remembered sort expression
         $sorted_col = $this->table_obj->getUiProp(PMA_Table::PROP_SORTED_COLUMN);
         // if the old column name is part of the remembered sort expression
         if (mb_strpos($sorted_col, PMA_Util::backquote($_REQUEST['field_orig'][$i])) !== false) {
             // delete the whole remembered sort expression
             $this->table_obj->removeUiProp(PMA_Table::PROP_SORTED_COLUMN);
         }
         if (isset($_REQUEST['field_adjust_privileges'][$i]) && !empty($_REQUEST['field_adjust_privileges'][$i]) && $_REQUEST['field_orig'][$i] != $_REQUEST['field_name'][$i]) {
             $adjust_privileges[$_REQUEST['field_orig'][$i]] = $_REQUEST['field_name'][$i];
         }
     }
     // end for
     if (count($changes) > 0 || isset($_REQUEST['preview_sql'])) {
         // 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
          *
          */
         // To allow replication, we first select the db to use
         // and then run queries on this db.
         if (!$this->dbi->selectDb($this->db)) {
             PMA_Util::mysqlDie($this->dbi->getError(), 'USE ' . PMA_Util::backquote($this->db) . ';', false, $err_url);
         }
         $sql_query = 'ALTER TABLE ' . PMA_Util::backquote($this->table) . ' ';
         $sql_query .= implode(', ', $changes) . $key_query;
         $sql_query .= ';';
         // If there is a request for SQL previewing.
         if (isset($_REQUEST['preview_sql'])) {
             PMA_previewSQL(count($changes) > 0 ? $sql_query : '');
         }
         $changedToBlob = array();
         // While changing the Column Collation
         // First change to BLOB
         for ($i = 0; $i < $field_cnt; $i++) {
             if (isset($_REQUEST['field_collation'][$i]) && isset($_REQUEST['field_collation_orig'][$i]) && $_REQUEST['field_collation'][$i] !== $_REQUEST['field_collation_orig'][$i]) {
                 $secondary_query = 'ALTER TABLE ' . PMA_Util::backquote($this->table) . ' CHANGE ' . PMA_Util::backquote($_REQUEST['field_orig'][$i]) . ' ' . PMA_Util::backquote($_REQUEST['field_orig'][$i]) . ' BLOB;';
                 $this->dbi->query($secondary_query);
                 $changedToBlob[$i] = true;
             } else {
                 $changedToBlob[$i] = false;
             }
         }
         // Then make the requested changes
         $result = $this->dbi->tryQuery($sql_query);
         if ($result !== false) {
             $changed_privileges = $this->adjustColumnPrivileges($this->db, $this->table, $adjust_privileges);
             if ($changed_privileges) {
                 $message = PMA_Message::success(__('Table %1$s has been altered successfully. Privileges ' . 'have been adjusted.'));
             } else {
                 $message = PMA_Message::success(__('Table %1$s has been altered successfully.'));
             }
             $message->addParam($this->table);
             $this->response->addHTML(PMA_Util::getMessage($message, $sql_query, 'success'));
         } else {
             // An error happened while inserting/updating a table definition
             // Save the Original Error
             $orig_error = $this->dbi->getError();
             $changes_revert = array();
             // Change back to Orignal Collation and data type
             for ($i = 0; $i < $field_cnt; $i++) {
                 if ($changedToBlob[$i]) {
                     $changes_revert[] = 'CHANGE ' . PMA_Table::generateAlter(Util\get($_REQUEST, "field_orig.{$i}", ''), $_REQUEST['field_name'][$i], $_REQUEST['field_type_orig'][$i], $_REQUEST['field_length_orig'][$i], $_REQUEST['field_attribute_orig'][$i], Util\get($_REQUEST, "field_collation_orig.{$i}", ''), Util\get($_REQUEST, "field_null_orig.{$i}", 'NOT NULL'), $_REQUEST['field_default_type_orig'][$i], $_REQUEST['field_default_value_orig'][$i], Util\get($_REQUEST, "field_extra_orig.{$i}", false), Util\get($_REQUEST, "field_comments_orig.{$i}", ''), Util\get($_REQUEST, "field_move_to_orig.{$i}", ''));
                 }
             }
             $revert_query = 'ALTER TABLE ' . PMA_Util::backquote($this->table) . ' ';
             $revert_query .= implode(', ', $changes_revert) . '';
             $revert_query .= ';';
             // Column reverted back to original
             $this->dbi->query($revert_query);
             $this->response->isSuccess(false);
             $this->response->addJSON('message', PMA_Message::rawError(__('Query error') . ':<br />' . $orig_error));
             $regenerate = true;
         }
     }
     // update field names in relation
     if (isset($_REQUEST['field_orig']) && is_array($_REQUEST['field_orig'])) {
         foreach ($_REQUEST['field_orig'] as $fieldindex => $fieldcontent) {
//.........这里部分代码省略.........

if (isset($_POST['destination_db']) && $cfgRelation['relwork']) {
    if ($upd_query->updateInternalRelations($multi_edit_columns_name, $_POST['destination_db'], $_POST['destination_table'], $_POST['destination_column'], $cfgRelation, isset($existrel) ? $existrel : null)) {
        $html_output .= PMA_Util::getMessage(__('Internal relations were successfully updated.'), '', 'success');
    }
}
// end if (updates for internal relations)
$multi_edit_columns_name = isset($_REQUEST['foreign_key_fields_name']) ? $_REQUEST['foreign_key_fields_name'] : null;
// u p d a t e s    f o r    f o r e i g n    k e y s
// (for now, one index name only; we keep the definitions if the
// foreign db is not the same)
if (isset($_POST['destination_foreign_db'])) {
    list($html, $preview_sql_data, $display_query, $seen_error) = $upd_query->updateForeignKeys($_POST['destination_foreign_db'], $multi_edit_columns_name, $_POST['destination_foreign_table'], $_POST['destination_foreign_column'], $options_array, $table, isset($existrel_foreign) ? $existrel_foreign['foreign_keys_data'] : null);
    $html_output .= $html;
    // If there is a request for SQL previewing.
    if (isset($_REQUEST['preview_sql'])) {
        PMA_previewSQL($preview_sql_data);
    }
    if (!empty($display_query) && !$seen_error) {
        $GLOBALS['display_query'] = $display_query;
        $html_output .= PMA_Util::getMessage(__('Your SQL query has been executed successfully.'), null, 'success');
    }
}
// end if isset($destination_foreign)
// U p d a t e s   f o r   d i s p l a y   f i e l d
if ($cfgRelation['displaywork'] && isset($_POST['display_field'])) {
    if ($upd_query->updateDisplayField($disp, $_POST['display_field'], $cfgRelation)) {
        $html_output .= PMA_Util::getMessage(__('Display column was successfully updated.'), '', 'success');
    }
}
// end if
// If we did an update, refresh our data