Exemple #1
0
/**
 * Function to count the total number of rows for the same 'SELECT' query without
 * the 'LIMIT' clause that may have been programatically added
 *
 * @param int    $num_rows             number of rows affected/changed by the query
 * @param bool   $is_select            whether the query is SELECT or not
 * @param bool   $justBrowsing         whether just browsing or not
 * @param string $db                   the current database
 * @param string $table                the current table
 * @param array  $parsed_sql           parsed sql
 * @param array  $analyzed_sql_results the analyzed query and other variables set
 *                                     after analyzing the query
 *
 * @return int $unlim_num_rows unlimited number of rows
 */
function PMA_countQueryResults($num_rows, $is_select, $justBrowsing, $db, $table, $parsed_sql, $analyzed_sql_results)
{
    if (!PMA_isAppendLimitClause($analyzed_sql_results)) {
        // if we did not append a limit, set this to get a correct
        // "Showing rows..." message
        // $_SESSION['tmpval']['max_rows'] = 'all';
        $unlim_num_rows = $num_rows;
    } elseif ($is_select || $analyzed_sql_results['is_subquery']) {
        //    c o u n t    q u e r y
        // If we are "just browsing", there is only one table,
        // and no WHERE clause (or just 'WHERE 1 '),
        // we do a quick count (which uses MaxExactCount) because
        // SQL_CALC_FOUND_ROWS is not quick on large InnoDB tables
        // However, do not count again if we did it previously
        // due to $find_real_end == true
        if ($justBrowsing) {
            // Get row count (is approximate for InnoDB)
            $unlim_num_rows = PMA_Table::countRecords($db, $table, false);
            /**
             * @todo Can we know at this point that this is InnoDB,
             *       (in this case there would be no need for getting
             *       an exact count)?
             */
            if ($unlim_num_rows < $GLOBALS['cfg']['MaxExactCount']) {
                // Get the exact count if approximate count
                // is less than MaxExactCount
                /**
                 * @todo In countRecords(), MaxExactCount is also verified,
                 *       so can we avoid checking it twice?
                 */
                $unlim_num_rows = PMA_Table::countRecords($db, $table, true);
            }
        } else {
            // add select expression after the SQL_CALC_FOUND_ROWS
            // for UNION, just adding SQL_CALC_FOUND_ROWS
            // after the first SELECT works.
            // take the left part, could be:
            // SELECT
            // (SELECT
            $analyzed_sql = $analyzed_sql_results['analyzed_sql'];
            $count_query = PMA_SQP_format($parsed_sql, 'query_only', 0, $analyzed_sql[0]['position_of_first_select'] + 1);
            $count_query .= ' SQL_CALC_FOUND_ROWS ';
            // add everything that was after the first SELECT
            $count_query .= PMA_SQP_format($parsed_sql, 'query_only', $analyzed_sql[0]['position_of_first_select'] + 1);
            // ensure there is no semicolon at the end of the
            // count query because we'll probably add
            // a LIMIT 1 clause after it
            $count_query = rtrim($count_query);
            $count_query = rtrim($count_query, ';');
            // if using SQL_CALC_FOUND_ROWS, add a LIMIT to avoid
            // long delays. Returned count will be complete anyway.
            // (but a LIMIT would disrupt results in an UNION)
            if (!isset($analyzed_sql[0]['queryflags']['union'])) {
                $count_query .= ' LIMIT 1';
            }
            // run the count query
            $GLOBALS['dbi']->tryQuery($count_query);
            // if (mysql_error()) {
            // void.
            // I tried the case
            // (SELECT `User`, `Host`, `Db`, `Select_priv` FROM `db`)
            // UNION (SELECT `User`, `Host`, "%" AS "Db",
            // `Select_priv`
            // FROM `user`) ORDER BY `User`, `Host`, `Db`;
            // and although the generated count_query is wrong
            // the SELECT FOUND_ROWS() work! (maybe it gets the
            // count from the latest query that worked)
            //
            // another case where the count_query is wrong:
            // SELECT COUNT(*), f1 from t1 group by f1
            // and you click to sort on count(*)
            // }
            $unlim_num_rows = $GLOBALS['dbi']->fetchValue('SELECT FOUND_ROWS()');
        }
        // end else "just browsing"
    } else {
        // not $is_select
        $unlim_num_rows = 0;
    }
    return $unlim_num_rows;
}
Exemple #2
0
 /**
  * Copies or renames table
  *
  * @param string $source_db    source database
  * @param string $source_table source table
  * @param string $target_db    target database
  * @param string $target_table target table
  * @param string $what         what to be moved or copied (data, dataonly)
  * @param bool   $move         whether to move
  * @param string $mode         mode
  *
  * @return bool true if success, false otherwise
  */
 public static function moveCopy($source_db, $source_table, $target_db, $target_table, $what, $move, $mode)
 {
     global $err_url;
     /* Try moving table directly */
     if ($move && $what == 'data') {
         $tbl = new PMA_Table($source_table, $source_db);
         $result = $tbl->rename($target_table, $target_db);
         if ($result) {
             $GLOBALS['message'] = $tbl->getLastMessage();
             return true;
         }
     }
     // set export settings we need
     $GLOBALS['sql_backquotes'] = 1;
     $GLOBALS['asfile'] = 1;
     // Ensure the target is valid
     if (!$GLOBALS['pma']->databases->exists($source_db, $target_db)) {
         if (!$GLOBALS['pma']->databases->exists($source_db)) {
             $GLOBALS['message'] = PMA_Message::rawError(sprintf(__('Source database `%s` was not found!'), htmlspecialchars($source_db)));
         }
         if (!$GLOBALS['pma']->databases->exists($target_db)) {
             $GLOBALS['message'] = PMA_Message::rawError(sprintf(__('Target database `%s` was not found!'), htmlspecialchars($target_db)));
         }
         return false;
     }
     $source = PMA_Util::backquote($source_db) . '.' . PMA_Util::backquote($source_table);
     if (!isset($target_db) || !strlen($target_db)) {
         $target_db = $source_db;
     }
     // Doing a select_db could avoid some problems with replicated databases,
     // when moving table from replicated one to not replicated one
     $GLOBALS['dbi']->selectDb($target_db);
     $target = PMA_Util::backquote($target_db) . '.' . PMA_Util::backquote($target_table);
     // do not create the table if dataonly
     if ($what != 'dataonly') {
         include_once "libraries/plugin_interface.lib.php";
         // get Export SQL instance
         $export_sql_plugin = PMA_getPlugin("export", "sql", 'libraries/plugins/export/', array('export_type' => 'table', 'single_table' => false));
         $no_constraints_comments = true;
         $GLOBALS['sql_constraints_query'] = '';
         // set the value of global sql_auto_increment variable
         if (isset($_POST['sql_auto_increment'])) {
             $GLOBALS['sql_auto_increment'] = $_POST['sql_auto_increment'];
         }
         $sql_structure = $export_sql_plugin->getTableDef($source_db, $source_table, "\n", $err_url, false, false);
         unset($no_constraints_comments);
         $parsed_sql = PMA_SQP_parse($sql_structure);
         $analyzed_sql = PMA_SQP_analyze($parsed_sql);
         $i = 0;
         if (empty($analyzed_sql[0]['create_table_fields'])) {
             // this is not a CREATE TABLE, so find the first VIEW
             $target_for_view = PMA_Util::backquote($target_db);
             while (true) {
                 if ($parsed_sql[$i]['type'] == 'alpha_reservedWord' && $parsed_sql[$i]['data'] == 'VIEW') {
                     break;
                 }
                 $i++;
             }
         }
         unset($analyzed_sql);
         if (PMA_DRIZZLE) {
             $table_delimiter = 'quote_backtick';
         } else {
             $server_sql_mode = $GLOBALS['dbi']->fetchValue("SHOW VARIABLES LIKE 'sql_mode'", 0, 1);
             // ANSI_QUOTES might be a subset of sql_mode, for example
             // REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
             if (false !== strpos($server_sql_mode, 'ANSI_QUOTES')) {
                 $table_delimiter = 'quote_double';
             } else {
                 $table_delimiter = 'quote_backtick';
             }
             unset($server_sql_mode);
         }
         /* Find table name in query and replace it */
         while ($parsed_sql[$i]['type'] != $table_delimiter) {
             $i++;
         }
         /* no need to backquote() */
         if (isset($target_for_view)) {
             // this a view definition; we just found the first db name
             // that follows DEFINER VIEW
             // so change it for the new db name
             $parsed_sql[$i]['data'] = $target_for_view;
             // then we have to find all references to the source db
             // and change them to the target db, ensuring we stay into
             // the $parsed_sql limits
             $last = $parsed_sql['len'] - 1;
             $backquoted_source_db = PMA_Util::backquote($source_db);
             for (++$i; $i <= $last; $i++) {
                 if ($parsed_sql[$i]['type'] == $table_delimiter && $parsed_sql[$i]['data'] == $backquoted_source_db && $parsed_sql[$i - 1]['type'] != 'punct_qualifier') {
                     $parsed_sql[$i]['data'] = $target_for_view;
                 }
             }
             unset($last, $backquoted_source_db);
         } else {
             $parsed_sql[$i]['data'] = $target;
         }
         /* Generate query back */
         $sql_structure = PMA_SQP_format($parsed_sql, 'query_only');
         // If table exists, and 'add drop table' is selected: Drop it!
         $drop_query = '';
         if (isset($_REQUEST['drop_if_exists']) && $_REQUEST['drop_if_exists'] == 'true') {
             if (PMA_Table::isView($target_db, $target_table)) {
                 $drop_query = 'DROP VIEW';
             } else {
                 $drop_query = 'DROP TABLE';
             }
             $drop_query .= ' IF EXISTS ' . PMA_Util::backquote($target_db) . '.' . PMA_Util::backquote($target_table);
             $GLOBALS['dbi']->query($drop_query);
             $GLOBALS['sql_query'] .= "\n" . $drop_query . ';';
             // If an existing table gets deleted, maintain any
             // entries for the PMA_* tables
             $maintain_relations = true;
         }
         @$GLOBALS['dbi']->query($sql_structure);
         $GLOBALS['sql_query'] .= "\n" . $sql_structure . ';';
         if (($move || isset($GLOBALS['add_constraints'])) && !empty($GLOBALS['sql_constraints_query'])) {
             $parsed_sql = PMA_SQP_parse($GLOBALS['sql_constraints_query']);
             $i = 0;
             // find the first $table_delimiter, it must be the source
             // table name
             while ($parsed_sql[$i]['type'] != $table_delimiter) {
                 $i++;
                 // maybe someday we should guard against going over limit
                 //if ($i == $parsed_sql['len']) {
                 //    break;
                 //}
             }
             // replace it by the target table name, no need
             // to backquote()
             $parsed_sql[$i]['data'] = $target;
             // now we must remove all $table_delimiter that follow a
             // CONSTRAINT keyword, because a constraint name must be
             // unique in a db
             $cnt = $parsed_sql['len'] - 1;
             for ($j = $i; $j < $cnt; $j++) {
                 if ($parsed_sql[$j]['type'] == 'alpha_reservedWord' && strtoupper($parsed_sql[$j]['data']) == 'CONSTRAINT') {
                     if ($parsed_sql[$j + 1]['type'] == $table_delimiter) {
                         $parsed_sql[$j + 1]['data'] = '';
                     }
                 }
             }
             // Generate query back
             $GLOBALS['sql_constraints_query'] = PMA_SQP_format($parsed_sql, 'query_only');
             if ($mode == 'one_table') {
                 $GLOBALS['dbi']->query($GLOBALS['sql_constraints_query']);
             }
             $GLOBALS['sql_query'] .= "\n" . $GLOBALS['sql_constraints_query'];
             if ($mode == 'one_table') {
                 unset($GLOBALS['sql_constraints_query']);
             }
         }
         // add indexes to the table
         if (!empty($GLOBALS['sql_indexes'])) {
             $parsed_sql = PMA_SQP_parse($GLOBALS['sql_indexes']);
             $i = 0;
             while ($parsed_sql[$i]['type'] != $table_delimiter) {
                 $i++;
             }
             $parsed_sql[$i]['data'] = $target;
             $cnt = $parsed_sql['len'] - 1;
             for ($j = $i; $j < $cnt; $j++) {
                 if ($parsed_sql[$j]['type'] == 'alpha_reservedWord' && strtoupper($parsed_sql[$j]['data']) == 'CONSTRAINT') {
                     if ($parsed_sql[$j + 1]['type'] == $table_delimiter) {
                         $parsed_sql[$j + 1]['data'] = '';
                     }
                 }
             }
             $GLOBALS['sql_indexes'] = PMA_SQP_format($parsed_sql, 'query_only');
             if ($mode == 'one_table' || $mode == 'db_copy') {
                 $GLOBALS['dbi']->query($GLOBALS['sql_indexes']);
             }
             $GLOBALS['sql_query'] .= "\n" . $GLOBALS['sql_indexes'];
             if ($mode == 'one_table' || $mode == 'db_copy') {
                 unset($GLOBALS['sql_indexes']);
             }
         }
         /*
          * add AUTO_INCREMENT to the table
          *
          * @todo refactor with similar code above
          */
         if (!empty($GLOBALS['sql_auto_increments'])) {
             if ($mode == 'one_table' || $mode == 'db_copy') {
                 $parsed_sql = PMA_SQP_parse($GLOBALS['sql_auto_increments']);
                 $i = 0;
                 // find the first $table_delimiter, it must be the source
                 // table name
                 while ($parsed_sql[$i]['type'] != $table_delimiter) {
                     $i++;
                 }
                 // replace it by the target table name, no need
                 // to backquote()
                 $parsed_sql[$i]['data'] = $target;
                 // Generate query back
                 $GLOBALS['sql_auto_increments'] = PMA_SQP_format($parsed_sql, 'query_only');
                 $GLOBALS['dbi']->query($GLOBALS['sql_auto_increments']);
                 $GLOBALS['sql_query'] .= "\n" . $GLOBALS['sql_auto_increments'];
                 unset($GLOBALS['sql_auto_increments']);
             }
         }
     } else {
         $GLOBALS['sql_query'] = '';
     }
     // Copy the data unless this is a VIEW
     if (($what == 'data' || $what == 'dataonly') && !PMA_Table::isView($target_db, $target_table)) {
         $sql_set_mode = "SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO'";
         $GLOBALS['dbi']->query($sql_set_mode);
         $GLOBALS['sql_query'] .= "\n\n" . $sql_set_mode . ';';
         $sql_insert_data = 'INSERT INTO ' . $target . ' SELECT * FROM ' . $source;
         $GLOBALS['dbi']->query($sql_insert_data);
         $GLOBALS['sql_query'] .= "\n\n" . $sql_insert_data . ';';
     }
     $GLOBALS['cfgRelation'] = PMA_getRelationsParam();
     // Drops old table if the user has requested to move it
     if ($move) {
         // This could avoid some problems with replicated databases, when
         // moving table from replicated one to not replicated one
         $GLOBALS['dbi']->selectDb($source_db);
         if (PMA_Table::isView($source_db, $source_table)) {
             $sql_drop_query = 'DROP VIEW';
         } else {
             $sql_drop_query = 'DROP TABLE';
         }
         $sql_drop_query .= ' ' . $source;
         $GLOBALS['dbi']->query($sql_drop_query);
         // Renable table in configuration storage
         PMA_REL_renameTable($source_db, $target_db, $source_table, $target_table);
         $GLOBALS['sql_query'] .= "\n\n" . $sql_drop_query . ';';
         // end if ($move)
     } else {
         // we are copying
         // Create new entries as duplicates from old PMA DBs
         if ($what != 'dataonly' && !isset($maintain_relations)) {
             if ($GLOBALS['cfgRelation']['commwork']) {
                 // Get all comments and MIME-Types for current table
                 $comments_copy_rs = PMA_queryAsControlUser('SELECT column_name, comment' . ($GLOBALS['cfgRelation']['mimework'] ? ', mimetype, transformation, transformation_options' : '') . ' FROM ' . PMA_Util::backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_Util::backquote($GLOBALS['cfgRelation']['column_info']) . ' WHERE ' . ' db_name = \'' . PMA_Util::sqlAddSlashes($source_db) . '\'' . ' AND ' . ' table_name = \'' . PMA_Util::sqlAddSlashes($source_table) . '\'');
                 // Write every comment as new copied entry. [MIME]
                 while ($comments_copy_row = $GLOBALS['dbi']->fetchAssoc($comments_copy_rs)) {
                     $new_comment_query = 'REPLACE INTO ' . PMA_Util::backquote($GLOBALS['cfgRelation']['db']) . '.' . PMA_Util::backquote($GLOBALS['cfgRelation']['column_info']) . ' (db_name, table_name, column_name, comment' . ($GLOBALS['cfgRelation']['mimework'] ? ', mimetype, transformation, transformation_options' : '') . ') ' . ' VALUES(' . '\'' . PMA_Util::sqlAddSlashes($target_db) . '\',' . '\'' . PMA_Util::sqlAddSlashes($target_table) . '\',' . '\'' . PMA_Util::sqlAddSlashes($comments_copy_row['column_name']) . '\'' . ($GLOBALS['cfgRelation']['mimework'] ? ',\'' . PMA_Util::sqlAddSlashes($comments_copy_row['comment']) . '\',' . '\'' . PMA_Util::sqlAddSlashes($comments_copy_row['mimetype']) . '\',' . '\'' . PMA_Util::sqlAddSlashes($comments_copy_row['transformation']) . '\',' . '\'' . PMA_Util::sqlAddSlashes($comments_copy_row['transformation_options']) . '\'' : '') . ')';
                     PMA_queryAsControlUser($new_comment_query);
                 }
                 // end while
                 $GLOBALS['dbi']->freeResult($comments_copy_rs);
                 unset($comments_copy_rs);
             }
             // duplicating the bookmarks must not be done here, but
             // just once per db
             $get_fields = array('display_field');
             $where_fields = array('db_name' => $source_db, 'table_name' => $source_table);
             $new_fields = array('db_name' => $target_db, 'table_name' => $target_table);
             PMA_Table::duplicateInfo('displaywork', 'table_info', $get_fields, $where_fields, $new_fields);
             /**
              * @todo revise this code when we support cross-db relations
              */
             $get_fields = array('master_field', 'foreign_table', 'foreign_field');
             $where_fields = array('master_db' => $source_db, 'master_table' => $source_table);
             $new_fields = array('master_db' => $target_db, 'foreign_db' => $target_db, 'master_table' => $target_table);
             PMA_Table::duplicateInfo('relwork', 'relation', $get_fields, $where_fields, $new_fields);
             $get_fields = array('foreign_field', 'master_table', 'master_field');
             $where_fields = array('foreign_db' => $source_db, 'foreign_table' => $source_table);
             $new_fields = array('master_db' => $target_db, 'foreign_db' => $target_db, 'foreign_table' => $target_table);
             PMA_Table::duplicateInfo('relwork', 'relation', $get_fields, $where_fields, $new_fields);
             $get_fields = array('x', 'y', 'v', 'h');
             $where_fields = array('db_name' => $source_db, 'table_name' => $source_table);
             $new_fields = array('db_name' => $target_db, 'table_name' => $target_table);
             PMA_Table::duplicateInfo('designerwork', 'designer_coords', $get_fields, $where_fields, $new_fields);
             /**
             * @todo Can't get duplicating PDFs the right way. The
             * page numbers always get screwed up independently from
             * duplication because the numbers do not seem to be stored on a
             * per-database basis. Would the author of pdf support please
             * have a look at it?
             *
                             $get_fields = array('page_descr');
                             $where_fields = array('db_name' => $source_db);
                             $new_fields = array('db_name' => $target_db);
                             $last_id = PMA_Table::duplicateInfo(
                'pdfwork',
                'pdf_pages',
                $get_fields,
                $where_fields,
                $new_fields
                             );
             
                             if (isset($last_id) && $last_id >= 0) {
                $get_fields = array('x', 'y');
                $where_fields = array(
                    'db_name' => $source_db,
                    'table_name' => $source_table
                );
                $new_fields = array(
                    'db_name' => $target_db,
                    'table_name' => $target_table,
                    'pdf_page_number' => $last_id
                );
                PMA_Table::duplicateInfo(
                    'pdfwork',
                    'table_coords',
                    $get_fields,
                    $where_fields,
                    $new_fields
                );
                             }
             */
         }
     }
     return true;
 }