/**
* Test PMA_getSqlWithLimitClause
*
* @return void
*/
public function testGetSqlWithLimitClause()
{
// Test environment.
$GLOBALS['_SESSION']['tmpval']['pos'] = 1;
$GLOBALS['_SESSION']['tmpval']['max_rows'] = 2;
$GLOBALS['db'] = 'db';
$analyzed_sql_results = PMA_parseAndAnalyze('SELECT * FROM test LIMIT 0, 10');
$this->assertEquals('SELECT * FROM test LIMIT 1, 2 ', PMA_getSqlWithLimitClause($analyzed_sql_results));
}
/**
* Function to append the limit clause
*
* @param array $analyzed_sql analyzed sql query
*
* @return array
*/
function PMA_appendLimitClause($analyzed_sql)
{
$sql_limit_to_append = ' LIMIT ' . $_SESSION['tmpval']['pos'] . ', ' . $_SESSION['tmpval']['max_rows'] . " ";
$full_sql_query = PMA_getSqlWithLimitClause($analyzed_sql, $sql_limit_to_append);
return array($sql_limit_to_append, $full_sql_query);
}
/**
* Function to execute the query and send the response
*
* @param array $analyzed_sql_results analysed sql results
* @param bool $is_gotofile whether goto file or not
* @param string $db current database
* @param string $table current table
* @param bool|null $find_real_end whether to find real end or not
* @param string $sql_query_for_bookmark the sql query to be stored as bookmark
* @param array|null $extra_data extra data
* @param string $message_to_show message to show
* @param string $message message
* @param array|null $sql_data sql data
* @param string $goto goto page url
* @param string $pmaThemeImage uri of the PMA theme image
* @param string $disp_query display query
* @param string $disp_message display message
* @param string $query_type query type
* @param string $sql_query sql query
* @param array|null $selectedTables array of table names selected from the
* database structure page, for an action
* like check table, optimize table,
* analyze table or repair table
* @param string $complete_query complete query
*
* @return string html
*/
function PMA_executeQueryAndGetQueryResponse($analyzed_sql_results, $is_gotofile, $db, $table, $find_real_end, $sql_query_for_bookmark, $extra_data, $message_to_show, $message, $sql_data, $goto, $pmaThemeImage, $disp_query, $disp_message, $query_type, $sql_query, $selectedTables, $complete_query)
{
// Handle disable/enable foreign key checks
$default_fk_check = PMA\libraries\Util::handleDisableFKCheckInit();
// Handle remembered sorting order, only for single table query.
// Handling is not required when it's a union query
// (the parser never sets the 'union' key to 0).
// Handling is also not required if we came from the "Sort by key"
// drop-down.
if (PMA_isRememberSortingOrder($analyzed_sql_results) && empty($analyzed_sql_results['union']) && !isset($_REQUEST['sort_by_key'])) {
if (!isset($_SESSION['sql_from_query_box'])) {
PMA_handleSortOrder($db, $table, $analyzed_sql_results, $sql_query);
} else {
unset($_SESSION['sql_from_query_box']);
}
}
$displayResultsObject = new PMA\libraries\DisplayResults($GLOBALS['db'], $GLOBALS['table'], $goto, $sql_query);
$displayResultsObject->setConfigParamsForDisplayTable();
// assign default full_sql_query
$full_sql_query = $sql_query;
// Do append a "LIMIT" clause?
if (PMA_isAppendLimitClause($analyzed_sql_results)) {
$full_sql_query = PMA_getSqlWithLimitClause($analyzed_sql_results);
}
$GLOBALS['reload'] = PMA_hasCurrentDbChanged($db);
$GLOBALS['dbi']->selectDb($db);
// Execute the query
list($result, $num_rows, $unlim_num_rows, $profiling_results, $extra_data) = PMA_executeTheQuery($analyzed_sql_results, $full_sql_query, $is_gotofile, $db, $table, isset($find_real_end) ? $find_real_end : null, isset($sql_query_for_bookmark) ? $sql_query_for_bookmark : null, isset($extra_data) ? $extra_data : null);
// No rows returned -> move back to the calling page
if (0 == $num_rows && 0 == $unlim_num_rows || $analyzed_sql_results['is_affected']) {
$html_output = PMA_getQueryResponseForNoResultsReturned($analyzed_sql_results, $db, $table, isset($message_to_show) ? $message_to_show : null, $num_rows, $displayResultsObject, $extra_data, $pmaThemeImage, isset($result) ? $result : null, $sql_query, isset($complete_query) ? $complete_query : null);
} else {
// At least one row is returned -> displays a table with results
$html_output = PMA_getQueryResponseForResultsReturned(isset($result) ? $result : null, $analyzed_sql_results, $db, $table, isset($message) ? $message : null, isset($sql_data) ? $sql_data : null, $displayResultsObject, $pmaThemeImage, $unlim_num_rows, $num_rows, isset($disp_query) ? $disp_query : null, isset($disp_message) ? $disp_message : null, $profiling_results, isset($query_type) ? $query_type : null, isset($selectedTables) ? $selectedTables : null, $sql_query, isset($complete_query) ? $complete_query : null);
}
// Handle disable/enable foreign key checks
PMA\libraries\Util::handleDisableFKCheckCleanup($default_fk_check);
return $html_output;
}
/**
* Function to execute the query and send the response
*
* @param array $analyzed_sql_results analysed sql results
* @param bool $is_gotofile whether goto file or not
* @param string $db current database
* @param string $table current table
* @param bool|null $find_real_end whether to find real end or not
* @param string $sql_query_for_bookmark the sql query to be stored as bookmark
* @param array|null $extra_data extra data
* @param bool $is_affected whether affected or not
* @param string $message_to_show message to show
* @param string $message message
* @param array|null $sql_data sql data
* @param string $goto goto page url
* @param string $pmaThemeImage uri of the PMA theme image
* @param string $disp_query display query
* @param string $disp_message display message
* @param string $query_type query type
* @param string $sql_query sql query
* @param array $selectedTables array of table names selected from the
* database structure page, for an action
* like check table, optimize table,
* analyze table or repair table
* @param string $complete_query complete query
*
* @return string html
*/
function PMA_executeQueryAndGetQueryResponse($analyzed_sql_results, $is_gotofile, $db, $table, $find_real_end, $sql_query_for_bookmark, $extra_data, $is_affected, $message_to_show, $message, $sql_data, $goto, $pmaThemeImage, $disp_query, $disp_message, $query_type, $sql_query, $selectedTables, $complete_query)
{
// Include PMA_Index class for use in PMA_DisplayResults class
include_once './libraries/Index.class.php';
include_once 'libraries/DisplayResults.class.php';
// Handle remembered sorting order, only for single table query
// Handling is not required when it's a union query
// (the parser never sets the 'union' key to 0)
if (PMA_isRememberSortingOrder($analyzed_sql_results) && !isset($analyzed_sql_results['analyzed_sql'][0]['queryflags']['union'])) {
if (!isset($_SESSION['sql_from_query_box'])) {
PMA_handleSortOrder($db, $table, $analyzed_sql_results, $sql_query);
} else {
unset($_SESSION['sql_from_query_box']);
}
}
$displayResultsObject = new PMA_DisplayResults($GLOBALS['db'], $GLOBALS['table'], $GLOBALS['goto'], $sql_query);
$displayResultsObject->setConfigParamsForDisplayTable();
// assign default full_sql_query
$full_sql_query = $sql_query;
// Do append a "LIMIT" clause?
if (PMA_isAppendLimitClause($analyzed_sql_results)) {
$full_sql_query = PMA_getSqlWithLimitClause($analyzed_sql_results['analyzed_sql'], ' LIMIT ' . $_SESSION['tmpval']['pos'] . ', ' . $_SESSION['tmpval']['max_rows'] . " ");
}
$GLOBALS['reload'] = PMA_hasCurrentDbChanged($db);
$GLOBALS['dbi']->selectDb($db);
// Execute the query
list($result, $num_rows, $unlim_num_rows, $profiling_results, $extra_data) = PMA_executeTheQuery($analyzed_sql_results, $full_sql_query, $is_gotofile, $db, $table, isset($find_real_end) ? $find_real_end : null, isset($sql_query_for_bookmark) ? $sql_query_for_bookmark : null, isset($extra_data) ? $extra_data : null);
// No rows returned -> move back to the calling page
if (0 == $num_rows && 0 == $unlim_num_rows || $is_affected) {
$html_output = PMA_getQueryResponseForNoResultsReturned($analyzed_sql_results, $db, $table, isset($message_to_show) ? $message_to_show : null, $num_rows, $displayResultsObject, $extra_data);
} else {
// At least one row is returned -> displays a table with results
$html_output = PMA_getQueryResponseForResultsReturned(isset($result) ? $result : null, $analyzed_sql_results, $db, $table, isset($message) ? $message : null, isset($sql_data) ? $sql_data : null, $displayResultsObject, $pmaThemeImage, $unlim_num_rows, $num_rows, $full_sql_query, isset($disp_query) ? $disp_query : null, isset($disp_message) ? $disp_message : null, $profiling_results, isset($query_type) ? $query_type : null, isset($selectedTables) ? $selectedTables : null, $sql_query, isset($complete_query) ? $complete_query : null);
}
return $html_output;
}
/**
* Generate table html when SQL statement have multiple queries
* which return displayable results
*
* @param PMA_DisplayResults $displayResultsObject object
* @param string $db database name
* @param array $sql_data information about SQL statement
* @param string $goto URL to go back in case of errors
* @param string $pmaThemeImage path for theme images directory
* @param string $text_dir text direction
* @param string $printview whether printview is enabled
* @param string $url_query URL query
* @param array $disp_mode the display mode
* @param string $sql_limit_to_append limit clause
* @param bool $editable whether result set is editable
*
* @return string $table_html html content
*/
function getTableHtmlForMultipleQueries($displayResultsObject, $db, $sql_data, $goto, $pmaThemeImage, $text_dir, $printview, $url_query, $disp_mode, $sql_limit_to_append, $editable)
{
$table_html = '';
$tables_array = PMA_DBI_get_tables($db);
$databases_array = PMA_DBI_get_databases_full();
$multi_sql = implode(";", $sql_data['valid_sql']);
$querytime_before = array_sum(explode(' ', microtime()));
// Assignment for variable is not needed since the results are
// looiping using the connection
@PMA_DBI_try_multi_query($multi_sql);
$querytime_after = array_sum(explode(' ', microtime()));
$querytime = $querytime_after - $querytime_before;
$sql_no = 0;
do {
$analyzed_sql = array();
$is_affected = false;
$result = PMA_DBI_store_result();
$fields_meta = $result !== false ? PMA_DBI_get_fields_meta($result) : array();
$fields_cnt = count($fields_meta);
// Initialize needed params related to each query in multiquery statement
if (isset($sql_data['valid_sql'][$sql_no])) {
// 'Use' query can change the database
if (stripos($sql_data['valid_sql'][$sql_no], "use ")) {
$db = PMA_getNewDatabase($sql_data['valid_sql'][$sql_no], $databases_array);
}
$parsed_sql = PMA_SQP_parse($sql_data['valid_sql'][$sql_no]);
$table = PMA_getTableNameBySQL($sql_data['valid_sql'][$sql_no], $tables_array);
$analyzed_sql = PMA_SQP_analyze($parsed_sql);
$is_select = isset($analyzed_sql[0]['queryflags']['select_from']);
$unlim_num_rows = PMA_Table::countRecords($db, $table, true);
$showtable = PMA_Table::sGetStatusInfo($db, $table, null, true);
$url_query = PMA_generate_common_url($db, $table);
list($is_group, $is_func, $is_count, $is_export, $is_analyse, $is_explain, $is_delete, $is_affected, $is_insert, $is_replace, $is_show, $is_maint) = PMA_getDisplayPropertyParams($sql_data['valid_sql'][$sql_no], $is_select);
// Handle remembered sorting order, only for single table query
if ($GLOBALS['cfg']['RememberSorting'] && !($is_count || $is_export || $is_func || $is_analyse) && isset($analyzed_sql[0]['select_expr']) && count($analyzed_sql[0]['select_expr']) == 0 && isset($analyzed_sql[0]['queryflags']['select_from']) && count($analyzed_sql[0]['table_ref']) == 1) {
PMA_handleSortOrder($db, $table, $analyzed_sql, $sql_data['valid_sql'][$sql_no]);
}
// Do append a "LIMIT" clause?
if ($_SESSION['tmp_user_values']['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['tmp_user_values']['pos'] . ', ' . $_SESSION['tmp_user_values']['max_rows'] . " ";
$sql_data['valid_sql'][$sql_no] = PMA_getSqlWithLimitClause($sql_data['valid_sql'][$sql_no], $analyzed_sql, $sql_limit_to_append);
}
// Set the needed properties related to executing sql query
$displayResultsObject->__set('db', $db);
$displayResultsObject->__set('table', $table);
$displayResultsObject->__set('goto', $goto);
}
if (!$is_affected) {
$num_rows = $result ? @PMA_DBI_num_rows($result) : 0;
} elseif (!isset($num_rows)) {
$num_rows = @PMA_DBI_affected_rows();
}
if (isset($sql_data['valid_sql'][$sql_no])) {
$displayResultsObject->__set('sql_query', $sql_data['valid_sql'][$sql_no]);
$displayResultsObject->setProperties($unlim_num_rows, $fields_meta, $is_count, $is_export, $is_func, $is_analyse, $num_rows, $fields_cnt, $querytime, $pmaThemeImage, $text_dir, $is_maint, $is_explain, $is_show, $showtable, $printview, $url_query, $editable);
}
if ($num_rows == 0) {
continue;
}
// With multiple results, operations are limied
$disp_mode = 'nnnn000000';
$is_limited_display = true;
// Collect the tables
$table_html .= $displayResultsObject->getTable($result, $disp_mode, $analyzed_sql, $is_limited_display);
// Free the result to save the memory
PMA_DBI_free_result($result);
$sql_no++;
} while (PMA_DBI_more_results() && PMA_DBI_next_result());
return $table_html;
}
/**
* Function to append the limit clause
*
* @param String $full_sql_query full sql query
* @param array $analyzed_sql analyzed sql query
* @param String $display_query display query
*
* @return array
*/
function PMA_appendLimitClause($full_sql_query, $analyzed_sql, $display_query)
{
$sql_limit_to_append = ' LIMIT ' . $_SESSION['tmpval']['pos'] . ', ' . $_SESSION['tmpval']['max_rows'] . " ";
$full_sql_query = PMA_getSqlWithLimitClause($full_sql_query, $analyzed_sql, $sql_limit_to_append);
/**
* @todo pretty printing of this modified query
*/
if ($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'];
}
}
return array($sql_limit_to_append, $full_sql_query, isset($analyzed_display_query) ? $analyzed_display_query : null, isset($display_query) ? $display_query : null);
}