diff --git a/htdocs/sql/sql.php b/htdocs/sql/sql.php new file mode 100755 --- /dev/null +++ b/htdocs/sql/sql.php @@ -0,0 +1,771 @@ +' . $strDropDatabaseStrongWarning . ''; + } + echo '
' . "\n" + .PMA_generate_common_hidden_inputs($db, $table); + ?> + + + + + + + + + + ' . "\n" + .' ' . $strDoYouReally . '' + .' ' . htmlspecialchars($stripped_sql_query) . '' . "\n" + .'' . "\n" + .'
' . "\n"; + ?> + + + ' . "\n" + . '' . "\n"; + + /** + * Displays the footer and exit + */ + require_once './libraries/footer.inc.php'; +} // end if $do_confirm + + +// Defines some variables +// A table has to be created, renamed, dropped -> navi frame should be reloaded +/** + * @todo use the parser/analyzer + */ + +if (empty($reload) + && preg_match('/^(CREATE|ALTER|DROP)\s+(VIEW|TABLE|DATABASE|SCHEMA)\s+/i', $sql_query)) { + $reload = 1; +} + +// SK -- Patch: $is_group added for use in calculation of total number of +// rows. +// $is_count is changed for more correct "LIMIT" clause +// appending in queries like +// "SELECT COUNT(...) FROM ... GROUP BY ..." + +/** + * @todo detect all this with the parser, to avoid problems finding + * those strings in comments or backquoted identifiers + */ + +$is_explain = $is_count = $is_export = $is_delete = $is_insert = $is_affected = $is_show = $is_maint = $is_analyse = $is_group = $is_func = $is_replace = false; +if ($is_select) { // see line 141 + $is_group = preg_match('@(GROUP[[:space:]]+BY|HAVING|SELECT[[:space:]]+DISTINCT)[[:space:]]+@i', $sql_query); + $is_func = !$is_group && (preg_match('@[[:space:]]+(SUM|AVG|STD|STDDEV|MIN|MAX|BIT_OR|BIT_AND)\s*\(@i', $sql_query)); + $is_count = !$is_group && (preg_match('@^SELECT[[:space:]]+COUNT\((.*\.+)?.*\)@i', $sql_query)); + $is_export = (preg_match('@[[:space:]]+INTO[[:space:]]+OUTFILE[[:space:]]+@i', $sql_query)); + $is_analyse = (preg_match('@[[:space:]]+PROCEDURE[[:space:]]+ANALYSE@i', $sql_query)); +} elseif (preg_match('@^EXPLAIN[[:space:]]+@i', $sql_query)) { + $is_explain = true; +} 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('@^[[:space:]]*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 ((! $cfg['ShowAll'] || $_SESSION['userconf']['max_rows'] != 'all') + && ! ($is_count || $is_export || $is_func || $is_analyse) + && isset($analyzed_sql[0]['queryflags']['select_from']) + && ! isset($analyzed_sql[0]['queryflags']['offset']) + && empty($analyzed_sql[0]['limit_clause']) + ) { + $sql_limit_to_append = ' LIMIT ' . $_SESSION['userconf']['pos'] . ', ' . $_SESSION['userconf']['max_rows'] . " "; + + $full_sql_query = $analyzed_sql[0]['section_before_limit'] . "\n" . $sql_limit_to_append . $analyzed_sql[0]['section_after_limit']; + /** + * @todo 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 (strlen($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 { + if (isset($_SESSION['profiling']) && PMA_profilingSupported()) { + PMA_DBI_query('SET PROFILING=1;'); + } + + // 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|tbl)_@', $err_url)) + ? $err_url . '&show_query=1&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(); + } + + // Grabs the profiling results + if (isset($_SESSION['profiling']) && PMA_profilingSupported() ) { + $profiling_results = PMA_DBI_fetch_result('SHOW PROFILE;'); + } + + // 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 (strlen($db) && 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 + //$_SESSION['userconf']['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_Table::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); + // 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, ';'); + } 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 (strlen($table) && strlen($db)) { + PMA_relationsCleanupTable($db, $table); + } elseif (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) + && 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 . ' ' . $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 . ' ' . $num_rows; + } else { + $message = $strInsertedRows . ' ' . $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 . ' ' . ($insert_id + $num_rows - 1); + } + } elseif ($is_affected) { + $message = $strAffectedRows . ' ' . $num_rows; + + // Ok, here is an explanation for the !$is_select. + // The form generated by sql_query_form.lib.php + // and db_sql.php has many submit buttons + // on the same form, and some confusion arises from the + // fact that $zero_rows is sent for every case. + // The $zero_rows containing $strSuccess and sent with + // the form should not have priority over + // errors like $strEmptyResultSet + } elseif (!empty($zero_rows) && !$is_select) { + $message = $zero_rows; + } elseif (!empty($GLOBALS['show_as_php'])) { + $message = $strShowingPhp; + } elseif (isset($GLOBALS['show_as_php'])) { + /* User disable showing as PHP, query is only displayed */ + $message = $strShowingSQL; + } elseif (!empty($GLOBALS['validatequery'])) { + $message = $strValidateSQL; + } else { + $message = $strEmptyResultSet; + } + + $message .= ' ' . (isset($GLOBALS['querytime']) ? '(' . sprintf($strQueryTime, $GLOBALS['querytime']) . ')' : ''); + + if ($is_gotofile) { + $goto = PMA_securePath($goto); + // Checks for a valid target script + $is_db = $is_table = false; + include 'libraries/db_table_exists.lib.php'; + if (strpos($goto, 'tbl_') === 0 && ! $is_table) { + if (strlen($table)) { + $table = ''; + } + $goto = 'db_sql.php'; + } + if (strpos($goto, 'db_') === 0 && ! $is_db) { + if (strlen($db)) { + $db = ''; + } + $goto = 'main.php'; + } + // Loads to target script + if (strpos($goto, 'db_') === 0 + || strpos($goto, 'tbl_') === 0) { + $js_to_run = 'functions.js'; + } + if ($goto != 'main.php') { + require_once './libraries/header.inc.php'; + } + $active_page = $goto; + require './' . $goto; + } else { + // avoid a redirect loop when last record was deleted + if ('sql.php' == $cfg['DefaultTabTable']) { + $goto = str_replace('sql.php','tbl_structure.php',$goto); + } + PMA_sendHeaderLocation($cfg['PmaAbsoluteUri'] . str_replace('&', '&', $goto) . '&message=' . urlencode($message)); + } // end else + exit(); +} // end no rows returned + +// At least one row is returned -> displays a table with results +else { + // Displays the headers + if (isset($show_query)) { + unset($show_query); + } + if (isset($printview) && $printview == '1') { + require_once './libraries/header_printview.inc.php'; + } else { + $js_to_run = 'functions.js'; + unset($message); + if (strlen($table)) { + require './libraries/tbl_common.php'; + $url_query .= '&goto=tbl_sql.php&back=tbl_sql.php'; + require './libraries/tbl_info.inc.php'; + require './libraries/tbl_links.inc.php'; + } elseif (strlen($db)) { + require './libraries/db_common.inc.php'; + require './libraries/db_info.inc.php'; + } else { + require './libraries/server_common.inc.php'; + require './libraries/server_links.inc.php'; + } + } + + if (strlen($db)) { + require_once './libraries/relation.lib.php'; + $cfgRelation = PMA_getRelationsParam(); + } + + // Gets the list of fields properties + if (isset($result) && $result) { + $fields_meta = PMA_DBI_get_fields_meta($result); + $fields_cnt = count($fields_meta); + } + + // Display previous update query (from tbl_replace) + if (isset($disp_query) && $cfg['ShowSQL'] == true) { + $tmp_sql_query = $GLOBALS['sql_query']; + $GLOBALS['sql_query'] = $disp_query; + PMA_showMessage($disp_message); + $GLOBALS['sql_query'] = $tmp_sql_query; + } + + if (isset($profiling_results)) { + PMA_profilingResults($profiling_results); + } + + // Displays the results in a table + if (empty($disp_mode)) { + // see the "PMA_setDisplayMode()" function in + // libraries/display_tbl.lib.php + $disp_mode = 'urdr111101'; + } + + // hide edit and delete links for information_schema + if (PMA_MYSQL_INT_VERSION >= 50002 && $db == 'information_schema') { + $disp_mode = 'nnnn110111'; + } + + PMA_displayTable($result, $disp_mode, $analyzed_sql); + PMA_DBI_free_result($result); + + // BEGIN INDEX CHECK See if indexes should be checked. + if (isset($query_type) && $query_type == 'check_tbl' && isset($selected) && is_array($selected)) { + foreach ($selected as $idx => $tbl_name) { + $check = PMA_check_indexes($tbl_name); + if (! empty($check)) { + ?> + + + + + +
+ +
+ + + + + +
+ ' : '') + . $strBookmarkThis; +?> + + +
+ + +
+ +
+ + +
+ +
+
+
+ +
+
+ + +