sql.php

一个用PHP编写的

    } elseif (preg_match('@^DELETE[[:space:]]+@i', $sql_query)) {
        $is_delete   = true;
        $is_affected = true;
    } elseif (preg_match('@^(INSERT|LOAD[[:space:]]+DATA|REPLACE)[[:space:]]+@i', $sql_query)) {
        $is_insert   = true;
        $is_affected = true;
        if (preg_match('@^(REPLACE)[[:space:]]+@i', $sql_query)) {
            $is_replace = true;
        }
    } elseif (preg_match('@^UPDATE[[:space:]]+@i', $sql_query)) {
        $is_affected = true;
    } elseif (preg_match('@^SHOW[[:space:]]+@i', $sql_query)) {
        $is_show     = true;
    } elseif (preg_match('@^(CHECK|ANALYZE|REPAIR|OPTIMIZE)[[:space:]]+TABLE[[:space:]]+@i', $sql_query)) {
        $is_maint    = true;
    }

    // Do append a "LIMIT" clause?
    if (isset($pos)
        && (!$cfg['ShowAll'] || $session_max_rows != 'all')
        && !($is_count || $is_export || $is_func || $is_analyse)
        && isset($analyzed_sql[0]['queryflags']['select_from'])
        && !isset($analyzed_sql[0]['queryflags']['offset'])
        && !preg_match('@[[:space:]]LIMIT[[:space:]0-9,-]+(;)?$@i', $sql_query)) {
        $sql_limit_to_append = " LIMIT $pos, ".$cfg['MaxRows'] . " ";

        $full_sql_query  = $analyzed_sql[0]['section_before_limit'] . "\n" . $sql_limit_to_append . $analyzed_sql[0]['section_after_limit'];
        // FIXME: pretty printing of this modified query

        if (isset($display_query)) {
            // if the analysis of the original query revealed that we found
            // a section_after_limit, we now have to analyze $display_query
            // to display it correctly

            if (!empty($analyzed_sql[0]['section_after_limit']) && trim($analyzed_sql[0]['section_after_limit']) != ';') {
                $analyzed_display_query = PMA_SQP_analyze(PMA_SQP_parse($display_query));
                $display_query  = $analyzed_display_query[0]['section_before_limit'] . "\n" . $sql_limit_to_append . $analyzed_display_query[0]['section_after_limit'];
            }
        }

    } else {
        $full_sql_query      = $sql_query;
    } // end if...else

    if (isset($db)) {
        PMA_DBI_select_db($db);
    }

    // If the query is a DELETE query with no WHERE clause, get the number of
    // rows that will be deleted (mysql_affected_rows will always return 0 in
    // this case)
    // Note: testing shows that this no longer applies since MySQL 4.0.x

    if (PMA_MYSQL_INT_VERSION < 40000) {
        if ($is_delete
            && preg_match('@^DELETE([[:space:]].+)?(FROM[[:space:]](.+))$@i', $sql_query, $parts)
            && !preg_match('@[[:space:]]WHERE[[:space:]]@i', $parts[3])) {
            $cnt_all_result = @PMA_DBI_try_query('SELECT COUNT(*) as count ' .  $parts[2]);
            if ($cnt_all_result) {
                list($num_rows) = PMA_DBI_fetch_row($cnt_all_result);
                PMA_DBI_free_result($cnt_all_result);
            } else {
                $num_rows   = 0;
            }
        }
    }

    //  E x e c u t e    t h e    q u e r y

    // Only if we didn't ask to see the php code (mikebeck)
    if (isset($GLOBALS['show_as_php']) || !empty($GLOBALS['validatequery'])) {
        unset($result);
        $num_rows = 0;
    } else {
        // garvin: Measure query time.
        // TODO-Item http://sourceforge.net/tracker/index.php?func=detail&aid=571934&group_id=23067&atid=377411
        $querytime_before = array_sum(explode(' ', microtime()));

        $result   = @PMA_DBI_try_query($full_sql_query, null, PMA_DBI_QUERY_STORE);

        $querytime_after = array_sum(explode(' ', microtime()));

        $GLOBALS['querytime'] = $querytime_after - $querytime_before;

        // Displays an error message if required and stop parsing the script
        if ($error        = PMA_DBI_getError()) {
            require_once './libraries/header.inc.php';
            $full_err_url = (preg_match('@^(db_details|tbl_properties)@', $err_url))
                          ? $err_url . '&amp;show_query=1&amp;sql_query=' . urlencode($sql_query)
                          : $err_url;
            PMA_mysqlDie($error, $full_sql_query, '', $full_err_url);
        }
        unset($error);

        // Gets the number of rows affected/returned
        // (This must be done immediately after the query because
        // mysql_affected_rows() reports about the last query done)

        if (!$is_affected) {
            $num_rows = ($result) ? @PMA_DBI_num_rows($result) : 0;
        } elseif (!isset($num_rows)) {
            $num_rows = @PMA_DBI_affected_rows();
        }

        // Checks if the current database has changed
        // This could happen if the user sends a query like "USE `database`;"
        $res = PMA_DBI_query('SELECT DATABASE() AS \'db\';');
        $row = PMA_DBI_fetch_row($res);
        if (is_array($row) && isset($row[0]) && (strcasecmp($db, $row[0]) != 0)) {
            $db     = $row[0];
            $reload = 1;
        }
        @PMA_DBI_free_result($res);
        unset($res, $row);

        // tmpfile remove after convert encoding appended by Y.Kawada
        if (function_exists('PMA_kanji_file_conv')
            && (isset($textfile) && file_exists($textfile))) {
            unlink($textfile);
        }

        // Counts the total number of rows for the same 'SELECT' query without the
        // 'LIMIT' clause that may have been programatically added

        if (empty($sql_limit_to_append)) {
            $unlim_num_rows         = $num_rows;
            // if we did not append a limit, set this to get a correct
            // "Showing rows..." message
            $GLOBALS['session_max_rows'] = 'all';
        } elseif ($is_select) {

                //    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 '),
                // so we do a quick count (which uses MaxExactCount)
                // because SQL_CALC_FOUND_ROWS
                // is not quick on large InnoDB tables

                // but do not count again if we did it previously
                // due to $find_real_end == true

                if (!$is_group
                 && !isset($analyzed_sql[0]['queryflags']['union'])
                 && !isset($analyzed_sql[0]['table_ref'][1]['table_name'])
                 && (empty($analyzed_sql[0]['where_clause'])
                   || $analyzed_sql[0]['where_clause'] == '1 ')
                 && !isset($find_real_end)
                 ) {

                    // "j u s t   b r o w s i n g"
                    $unlim_num_rows = PMA_countRecords($db, $table, true);

                } else { // n o t   " j u s t   b r o w s i n g "

                    if (PMA_MYSQL_INT_VERSION < 40000) {

                        // detect this case:
                        // SELECT DISTINCT x AS foo, y AS bar FROM sometable

                        if (isset($analyzed_sql[0]['queryflags']['distinct'])) {
                            $count_what = 'DISTINCT ';
                            $first_expr = true;
                            foreach ($analyzed_sql[0]['select_expr'] as $part) {
                                $count_what .= (!$first_expr ? ', ' : '') . $part['expr'];
                                $first_expr = false;
                            }
                         } else {
                             $count_what = '*';
                         }
                        // this one does not apply to VIEWs
                        $count_query = 'SELECT COUNT(' . $count_what . ') AS count';
                    }

                    // add the remaining of select expression if there is
                    // a GROUP BY or HAVING clause
                    if (PMA_MYSQL_INT_VERSION < 40000
                     && $count_what =='*'
                     && (!empty($analyzed_sql[0]['group_by_clause'])
                        || !empty($analyzed_sql[0]['having_clause']))) {
                        $count_query .= ' ,' . $analyzed_sql[0]['select_expr_clause'];
                    }

                    if (PMA_MYSQL_INT_VERSION >= 40000) {
                         // 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
                            $count_query = PMA_SQP_formatHtml($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_formatHtml($parsed_sql, 'query_only', $analyzed_sql[0]['position_of_first_select']+1);
                    } else { // PMA_MYSQL_INT_VERSION < 40000

                        if (!empty($analyzed_sql[0]['from_clause'])) {
                            $count_query .= ' FROM ' . $analyzed_sql[0]['from_clause'];
                        }
                        if (!empty($analyzed_sql[0]['where_clause'])) {
                            $count_query .= ' WHERE ' . $analyzed_sql[0]['where_clause'];
                        }
                        if (!empty($analyzed_sql[0]['group_by_clause'])) {
                            $count_query .= ' GROUP BY ' . $analyzed_sql[0]['group_by_clause'];
                        }
                        if (!empty($analyzed_sql[0]['having_clause'])) {
                            $count_query .= ' HAVING ' . $analyzed_sql[0]['having_clause'];
                        }
                    } // end if

                    // 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 (PMA_MYSQL_INT_VERSION >= 40000
                    && !isset($analyzed_sql[0]['queryflags']['union'])) {
                        $count_query .= ' LIMIT 1';
                    }

                    // run the count query

                    if (PMA_MYSQL_INT_VERSION < 40000) {
                        if ($cnt_all_result = PMA_DBI_try_query($count_query)) {
                            if ($is_group && $count_what == '*') {
                                $unlim_num_rows = @PMA_DBI_num_rows($cnt_all_result);
                            } else {
                                $unlim_num_rows = PMA_DBI_fetch_assoc($cnt_all_result);
                                $unlim_num_rows = $unlim_num_rows['count'];
                            }
                            PMA_DBI_free_result($cnt_all_result);
                        } else {
                            if (PMA_DBI_getError()) {

                                // there are some cases where the generated
                                // count_query (for MySQL 3) is wrong,
                                // so we get here.
                                //TODO: use a big unlimited query to get
                                // the correct number of rows (depending
                                // on a config variable?)
                                $unlim_num_rows = 0;
                            }
                        }
                    } else {
                        PMA_DBI_try_query($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(*)
                        // }
                        $cnt_all_result       = PMA_DBI_query('SELECT FOUND_ROWS() as count;');
                        list($unlim_num_rows) = PMA_DBI_fetch_row($cnt_all_result);
                        @PMA_DBI_free_result($cnt_all_result);
                    }
            } // end else "just browsing"

        } else { // not $is_select
             $unlim_num_rows         = 0;
        } // end rows total count

        // garvin: if a table or database gets dropped, check column comments.
        if (isset($purge) && $purge == '1') {
            require_once './libraries/relation_cleanup.lib.php';

            if (isset($table) && isset($db) && strlen($table) && strlen($db)) {
                PMA_relationsCleanupTable($db, $table);
            } elseif (isset($db) && strlen($db)) {
                PMA_relationsCleanupDatabase($db);
            } else {
                // garvin: VOID. No DB/Table gets deleted.
            } // end if relation-stuff
         } // end if ($purge)

        // garvin: If a column gets dropped, do relation magic.
        if (isset($cpurge) && $cpurge == '1' && isset($purgekey)
          && isset($db) && isset($table)
          && strlen($db) && strlen($table) && !empty($purgekey)) {
            require_once './libraries/relation_cleanup.lib.php';
            PMA_relationsCleanupColumn($db, $table, $purgekey);

        } // end if column PMA_* purge
    } // end else "didn't ask to see php code"

    // No rows returned -> move back to the calling page
    if ($num_rows < 1 || $is_affected) {
        if ($is_delete) {
            $message = $strDeletedRows . '&nbsp;' . $num_rows;
        } elseif ($is_insert) {
            if ($is_replace) {
                /* For replace we get DELETED + INSERTED row count, so we have to call it affected */
                $message = $strAffectedRows . '&nbsp;' . $num_rows;
            } else {
                $message = $strInsertedRows . '&nbsp;' . $num_rows;
            }
            $insert_id = PMA_DBI_insert_id();
            if ($insert_id != 0) {
                // insert_id is id of FIRST record inserted in one insert, so if we inserted multiple rows, we had to increment this
                $message .= '[br]'.$strInsertedRowId . '&nbsp;' . ($insert_id + $num_rows - 1);
            }
        } elseif ($is_affected) {
            $message = $strAffectedRows . '&nbsp;' . $num_rows;

            // Ok, here is an explanation for the !$is_select.
            // The form generated by sql_query_form.lib.php
            // and db_details.php has many submit buttons
            // on the same form, and some confusion arises from the