PHP PMA_DBI_get_columns_full函数代码示例

/**
 * 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) {
//.........这里部分代码省略.........

 /**
  * 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;
 }

/**
 * 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;
//.........这里部分代码省略.........

/**
 * 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;
}