diff options
Diffstat (limited to 'srcs/phpmyadmin/libraries/classes/Database/Qbe.php')
| -rw-r--r-- | srcs/phpmyadmin/libraries/classes/Database/Qbe.php | 1963 |
1 files changed, 0 insertions, 1963 deletions
diff --git a/srcs/phpmyadmin/libraries/classes/Database/Qbe.php b/srcs/phpmyadmin/libraries/classes/Database/Qbe.php deleted file mode 100644 index 27116d0..0000000 --- a/srcs/phpmyadmin/libraries/classes/Database/Qbe.php +++ /dev/null @@ -1,1963 +0,0 @@ -<?php -/* vim: set expandtab sw=4 ts=4 sts=4: */ -/** - * Handles DB QBE search - * - * @package PhpMyAdmin - */ -declare(strict_types=1); - -namespace PhpMyAdmin\Database; - -use PhpMyAdmin\Core; -use PhpMyAdmin\DatabaseInterface; -use PhpMyAdmin\Message; -use PhpMyAdmin\Relation; -use PhpMyAdmin\SavedSearches; -use PhpMyAdmin\Table; -use PhpMyAdmin\Template; -use PhpMyAdmin\Url; -use PhpMyAdmin\Util; - -/** - * Class to handle database QBE search - * - * @package PhpMyAdmin - */ -class Qbe -{ - /** - * Database name - * - * @access private - * @var string - */ - private $_db; - /** - * Table Names (selected/non-selected) - * - * @access private - * @var array - */ - private $_criteriaTables; - /** - * Column Names - * - * @access private - * @var array - */ - private $_columnNames; - /** - * Number of columns - * - * @access private - * @var integer - */ - private $_criteria_column_count; - /** - * Number of Rows - * - * @access private - * @var integer - */ - private $_criteria_row_count; - /** - * Whether to insert a new column - * - * @access private - * @var array - */ - private $_criteriaColumnInsert; - /** - * Whether to delete a column - * - * @access private - * @var array - */ - private $_criteriaColumnDelete; - /** - * Whether to insert a new row - * - * @access private - * @var array - */ - private $_criteriaRowInsert; - /** - * Whether to delete a row - * - * @access private - * @var array - */ - private $_criteriaRowDelete; - /** - * Already set criteria values - * - * @access private - * @var array - */ - private $_criteria; - /** - * Previously set criteria values - * - * @access private - * @var array - */ - private $_prev_criteria; - /** - * AND/OR relation b/w criteria columns - * - * @access private - * @var array - */ - private $_criteriaAndOrColumn; - /** - * AND/OR relation b/w criteria rows - * - * @access private - * @var array - */ - private $_criteriaAndOrRow; - /** - * Large width of a column - * - * @access private - * @var string - */ - private $_realwidth; - /** - * Minimum width of a column - * - * @access private - * @var int - */ - private $_form_column_width; - /** - * Selected columns in the form - * - * @access private - * @var array - */ - private $_formColumns; - /** - * Entered aliases in the form - * - * @access private - * @var array - */ - private $_formAliases; - /** - * Chosen sort options in the form - * - * @access private - * @var array - */ - private $_formSorts; - /** - * Chosen sort orders in the form - * - * @access private - * @var array - */ - private $_formSortOrders; - /** - * Show checkboxes in the form - * - * @access private - * @var array - */ - private $_formShows; - /** - * Entered criteria values in the form - * - * @access private - * @var array - */ - private $_formCriterions; - /** - * AND/OR column radio buttons in the form - * - * @access private - * @var array - */ - private $_formAndOrCols; - /** - * AND/OR row radio buttons in the form - * - * @access private - * @var array - */ - private $_formAndOrRows; - /** - * New column count in case of add/delete - * - * @access private - * @var integer - */ - private $_new_column_count; - /** - * New row count in case of add/delete - * - * @access private - * @var integer - */ - private $_new_row_count; - /** - * List of saved searches - * - * @access private - * @var array - */ - private $_savedSearchList = null; - /** - * Current search - * - * @access private - * @var SavedSearches - */ - private $_currentSearch = null; - - /** - * @var Relation - */ - private $relation; - - /** - * @var DatabaseInterface - */ - public $dbi; - - /** - * @var Template - */ - public $template; - - /** - * Public Constructor - * - * @param Relation $relation Relation object - * @param Template $template Template object - * @param DatabaseInterface $dbi DatabaseInterface object - * @param string $dbname Database name - * @param array $savedSearchList List of saved searches - * @param SavedSearches $currentSearch Current search id - */ - public function __construct( - Relation $relation, - Template $template, - $dbi, - $dbname, - array $savedSearchList = [], - $currentSearch = null - ) { - $this->_db = $dbname; - $this->_savedSearchList = $savedSearchList; - $this->_currentSearch = $currentSearch; - $this->dbi = $dbi; - $this->relation = $relation; - $this->template = $template; - - $this->_loadCriterias(); - // Sets criteria parameters - $this->_setSearchParams(); - $this->_setCriteriaTablesAndColumns(); - } - - /** - * Initialize criterias - * - * @return static - */ - private function _loadCriterias() - { - if (null === $this->_currentSearch - || null === $this->_currentSearch->getCriterias() - ) { - return $this; - } - - $criterias = $this->_currentSearch->getCriterias(); - $_POST = $criterias + $_POST; - - return $this; - } - - /** - * Getter for current search - * - * @return SavedSearches - */ - private function _getCurrentSearch() - { - return $this->_currentSearch; - } - - /** - * Sets search parameters - * - * @return void - */ - private function _setSearchParams() - { - $criteriaColumnCount = $this->_initializeCriteriasCount(); - - $this->_criteriaColumnInsert = Core::ifSetOr( - $_POST['criteriaColumnInsert'], - null, - 'array' - ); - $this->_criteriaColumnDelete = Core::ifSetOr( - $_POST['criteriaColumnDelete'], - null, - 'array' - ); - - $this->_prev_criteria = isset($_POST['prev_criteria']) - ? $_POST['prev_criteria'] - : []; - $this->_criteria = isset($_POST['criteria']) - ? $_POST['criteria'] - : array_fill(0, $criteriaColumnCount, ''); - - $this->_criteriaRowInsert = isset($_POST['criteriaRowInsert']) - ? $_POST['criteriaRowInsert'] - : array_fill(0, $criteriaColumnCount, ''); - $this->_criteriaRowDelete = isset($_POST['criteriaRowDelete']) - ? $_POST['criteriaRowDelete'] - : array_fill(0, $criteriaColumnCount, ''); - $this->_criteriaAndOrRow = isset($_POST['criteriaAndOrRow']) - ? $_POST['criteriaAndOrRow'] - : array_fill(0, $criteriaColumnCount, ''); - $this->_criteriaAndOrColumn = isset($_POST['criteriaAndOrColumn']) - ? $_POST['criteriaAndOrColumn'] - : array_fill(0, $criteriaColumnCount, ''); - // sets minimum width - $this->_form_column_width = 12; - $this->_formColumns = []; - $this->_formSorts = []; - $this->_formShows = []; - $this->_formCriterions = []; - $this->_formAndOrRows = []; - $this->_formAndOrCols = []; - } - - /** - * Sets criteria tables and columns - * - * @return void - */ - private function _setCriteriaTablesAndColumns() - { - // The tables list sent by a previously submitted form - if (Core::isValid($_POST['TableList'], 'array')) { - foreach ($_POST['TableList'] as $each_table) { - $this->_criteriaTables[$each_table] = ' selected="selected"'; - } - } // end if - $all_tables = $this->dbi->query( - 'SHOW TABLES FROM ' . Util::backquote($this->_db) . ';', - DatabaseInterface::CONNECT_USER, - DatabaseInterface::QUERY_STORE - ); - $all_tables_count = $this->dbi->numRows($all_tables); - if (0 == $all_tables_count) { - Message::error(__('No tables found in database.'))->display(); - exit; - } - // The tables list gets from MySQL - while (list($table) = $this->dbi->fetchRow($all_tables)) { - $columns = $this->dbi->getColumns($this->_db, $table); - - if (empty($this->_criteriaTables[$table]) - && ! empty($_POST['TableList']) - ) { - $this->_criteriaTables[$table] = ''; - } else { - $this->_criteriaTables[$table] = ' selected="selected"'; - } // end if - - // The fields list per selected tables - if ($this->_criteriaTables[$table] == ' selected="selected"') { - $each_table = Util::backquote($table); - $this->_columnNames[] = $each_table . '.*'; - foreach ($columns as $each_column) { - $each_column = $each_table . '.' - . Util::backquote($each_column['Field']); - $this->_columnNames[] = $each_column; - // increase the width if necessary - $this->_form_column_width = max( - mb_strlen($each_column), - $this->_form_column_width - ); - } // end foreach - } // end if - } // end while - $this->dbi->freeResult($all_tables); - - // sets the largest width found - $this->_realwidth = $this->_form_column_width . 'ex'; - } - /** - * Provides select options list containing column names - * - * @param integer $column_number Column Number (0,1,2) or more - * @param string $selected Selected criteria column name - * - * @return string HTML for select options - */ - private function _showColumnSelectCell($column_number, $selected = '') - { - return $this->template->render('database/qbe/column_select_cell', [ - 'column_number' => $column_number, - 'column_names' => $this->_columnNames, - 'selected' => $selected, - ]); - } - - /** - * Provides select options list containing sort options (ASC/DESC) - * - * @param integer $columnNumber Column Number (0,1,2) or more - * @param string $selected Selected criteria 'ASC' or 'DESC' - * - * @return string HTML for select options - */ - private function _getSortSelectCell( - $columnNumber, - $selected = '' - ) { - return $this->template->render('database/qbe/sort_select_cell', [ - 'real_width' => $this->_realwidth, - 'column_number' => $columnNumber, - 'selected' => $selected, - ]); - } - - /** - * Provides select options list containing sort order - * - * @param integer $columnNumber Column Number (0,1,2) or more - * @param integer $sortOrder Sort order - * - * @return string HTML for select options - */ - private function _getSortOrderSelectCell($columnNumber, $sortOrder) - { - $totalColumnCount = $this->_getNewColumnCount(); - return $this->template->render('database/qbe/sort_order_select_cell', [ - 'total_column_count' => $totalColumnCount, - 'column_number' => $columnNumber, - 'sort_order' => $sortOrder, - ]); - } - - /** - * Returns the new column count after adding and removing columns as instructed - * - * @return int new column count - */ - private function _getNewColumnCount() - { - $totalColumnCount = $this->_criteria_column_count; - if (! empty($this->_criteriaColumnInsert)) { - $totalColumnCount += count($this->_criteriaColumnInsert); - } - if (! empty($this->_criteriaColumnDelete)) { - $totalColumnCount -= count($this->_criteriaColumnDelete); - } - return $totalColumnCount; - } - - /** - * Provides search form's row containing column select options - * - * @return string HTML for search table's row - */ - private function _getColumnNamesRow() - { - $html_output = '<tr class="noclick">'; - $html_output .= '<th>' . __('Column:') . '</th>'; - $new_column_count = 0; - for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { - if (isset($this->_criteriaColumnInsert[$column_index]) - && $this->_criteriaColumnInsert[$column_index] == 'on' - ) { - $html_output .= $this->_showColumnSelectCell( - $new_column_count - ); - $new_column_count++; - } - if (! empty($this->_criteriaColumnDelete) - && isset($this->_criteriaColumnDelete[$column_index]) - && $this->_criteriaColumnDelete[$column_index] == 'on' - ) { - continue; - } - $selected = ''; - if (isset($_POST['criteriaColumn'][$column_index])) { - $selected = $_POST['criteriaColumn'][$column_index]; - $this->_formColumns[$new_column_count] - = $_POST['criteriaColumn'][$column_index]; - } - $html_output .= $this->_showColumnSelectCell( - $new_column_count, - $selected - ); - $new_column_count++; - } // end for - $this->_new_column_count = $new_column_count; - $html_output .= '</tr>'; - return $html_output; - } - - /** - * Provides search form's row containing column aliases - * - * @return string HTML for search table's row - */ - private function _getColumnAliasRow() - { - $html_output = '<tr class="noclick">'; - $html_output .= '<th>' . __('Alias:') . '</th>'; - $new_column_count = 0; - - for ($colInd = 0; $colInd < $this->_criteria_column_count; $colInd++) { - if (! empty($this->_criteriaColumnInsert) - && isset($this->_criteriaColumnInsert[$colInd]) - && $this->_criteriaColumnInsert[$colInd] == 'on' - ) { - $html_output .= '<td class="center">'; - $html_output .= '<input type="text"' - . ' name="criteriaAlias[' . $new_column_count . ']">'; - $html_output .= '</td>'; - $new_column_count++; - } // end if - - if (! empty($this->_criteriaColumnDelete) - && isset($this->_criteriaColumnDelete[$colInd]) - && $this->_criteriaColumnDelete[$colInd] == 'on' - ) { - continue; - } - - $tmp_alias = ''; - if (! empty($_POST['criteriaAlias'][$colInd])) { - $tmp_alias - = $this->_formAliases[$new_column_count] - = $_POST['criteriaAlias'][$colInd]; - }// end if - - $html_output .= '<td class="center">'; - $html_output .= '<input type="text"' - . ' name="criteriaAlias[' . $new_column_count . ']"' - . ' value="' . htmlspecialchars($tmp_alias) . '">'; - $html_output .= '</td>'; - $new_column_count++; - } // end for - $html_output .= '</tr>'; - return $html_output; - } - - /** - * Provides search form's row containing sort(ASC/DESC) select options - * - * @return string HTML for search table's row - */ - private function _getSortRow() - { - $html_output = '<tr class="noclick">'; - $html_output .= '<th>' . __('Sort:') . '</th>'; - $new_column_count = 0; - - for ($colInd = 0; $colInd < $this->_criteria_column_count; $colInd++) { - if (! empty($this->_criteriaColumnInsert) - && isset($this->_criteriaColumnInsert[$colInd]) - && $this->_criteriaColumnInsert[$colInd] == 'on' - ) { - $html_output .= $this->_getSortSelectCell($new_column_count); - $new_column_count++; - } // end if - - if (! empty($this->_criteriaColumnDelete) - && isset($this->_criteriaColumnDelete[$colInd]) - && $this->_criteriaColumnDelete[$colInd] == 'on' - ) { - continue; - } - // If they have chosen all fields using the * selector, - // then sorting is not available, Fix for Bug #570698 - if (isset($_POST['criteriaSort'][$colInd]) - && isset($_POST['criteriaColumn'][$colInd]) - && mb_substr($_POST['criteriaColumn'][$colInd], -2) == '.*' - ) { - $_POST['criteriaSort'][$colInd] = ''; - } //end if - - $selected = ''; - if (isset($_POST['criteriaSort'][$colInd])) { - $this->_formSorts[$new_column_count] - = $_POST['criteriaSort'][$colInd]; - - if ($_POST['criteriaSort'][$colInd] == 'ASC') { - $selected = 'ASC'; - } elseif ($_POST['criteriaSort'][$colInd] == 'DESC') { - $selected = 'DESC'; - } - } else { - $this->_formSorts[$new_column_count] = ''; - } - - $html_output .= $this->_getSortSelectCell( - $new_column_count, - $selected - ); - $new_column_count++; - } // end for - $html_output .= '</tr>'; - return $html_output; - } - - /** - * Provides search form's row containing sort order - * - * @return string HTML for search table's row - */ - private function _getSortOrder() - { - $html_output = '<tr class="noclick">'; - $html_output .= '<th>' . __('Sort order:') . '</th>'; - $new_column_count = 0; - - for ($colInd = 0; $colInd < $this->_criteria_column_count; $colInd++) { - if (! empty($this->_criteriaColumnInsert) - && isset($this->_criteriaColumnInsert[$colInd]) - && $this->_criteriaColumnInsert[$colInd] == 'on' - ) { - $html_output .= $this->_getSortOrderSelectCell( - $new_column_count, - null - ); - $new_column_count++; - } // end if - - if (! empty($this->_criteriaColumnDelete) - && isset($this->_criteriaColumnDelete[$colInd]) - && $this->_criteriaColumnDelete[$colInd] == 'on' - ) { - continue; - } - - $sortOrder = null; - if (! empty($_POST['criteriaSortOrder'][$colInd])) { - $sortOrder - = $this->_formSortOrders[$new_column_count] - = $_POST['criteriaSortOrder'][$colInd]; - } - - $html_output .= $this->_getSortOrderSelectCell( - $new_column_count, - $sortOrder - ); - $new_column_count++; - } // end for - $html_output .= '</tr>'; - return $html_output; - } - - /** - * Provides search form's row containing SHOW checkboxes - * - * @return string HTML for search table's row - */ - private function _getShowRow() - { - $html_output = '<tr class="noclick">'; - $html_output .= '<th>' . __('Show:') . '</th>'; - $new_column_count = 0; - for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { - if (! empty($this->_criteriaColumnInsert) - && isset($this->_criteriaColumnInsert[$column_index]) - && $this->_criteriaColumnInsert[$column_index] == 'on' - ) { - $html_output .= '<td class="center">'; - $html_output .= '<input type="checkbox"' - . ' name="criteriaShow[' . $new_column_count . ']">'; - $html_output .= '</td>'; - $new_column_count++; - } // end if - if (! empty($this->_criteriaColumnDelete) - && isset($this->_criteriaColumnDelete[$column_index]) - && $this->_criteriaColumnDelete[$column_index] == 'on' - ) { - continue; - } - if (isset($_POST['criteriaShow'][$column_index])) { - $checked_options = ' checked="checked"'; - $this->_formShows[$new_column_count] - = $_POST['criteriaShow'][$column_index]; - } else { - $checked_options = ''; - } - $html_output .= '<td class="center">'; - $html_output .= '<input type="checkbox"' - . ' name="criteriaShow[' . $new_column_count . ']"' - . $checked_options . '>'; - $html_output .= '</td>'; - $new_column_count++; - } // end for - $html_output .= '</tr>'; - return $html_output; - } - - /** - * Provides search form's row containing criteria Inputboxes - * - * @return string HTML for search table's row - */ - private function _getCriteriaInputboxRow() - { - $html_output = '<tr class="noclick">'; - $html_output .= '<th>' . __('Criteria:') . '</th>'; - $new_column_count = 0; - for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { - if (! empty($this->_criteriaColumnInsert) - && isset($this->_criteriaColumnInsert[$column_index]) - && $this->_criteriaColumnInsert[$column_index] == 'on' - ) { - $html_output .= '<td class="center">'; - $html_output .= '<input type="text"' - . ' name="criteria[' . $new_column_count . ']"' - . ' class="textfield"' - . ' style="width: ' . $this->_realwidth . '"' - . ' size="20">'; - $html_output .= '</td>'; - $new_column_count++; - } // end if - if (! empty($this->_criteriaColumnDelete) - && isset($this->_criteriaColumnDelete[$column_index]) - && $this->_criteriaColumnDelete[$column_index] == 'on' - ) { - continue; - } - $tmp_criteria = ''; - if (isset($this->_criteria[$column_index])) { - $tmp_criteria = $this->_criteria[$column_index]; - } - if ((empty($this->_prev_criteria) - || ! isset($this->_prev_criteria[$column_index])) - || $this->_prev_criteria[$column_index] != htmlspecialchars($tmp_criteria) - ) { - $this->_formCriterions[$new_column_count] = $tmp_criteria; - } else { - $this->_formCriterions[$new_column_count] - = $this->_prev_criteria[$column_index]; - } - $html_output .= '<td class="center">'; - $html_output .= '<input type="hidden"' - . ' name="prev_criteria[' . $new_column_count . ']"' - . ' value="' - . htmlspecialchars($this->_formCriterions[$new_column_count]) - . '">'; - $html_output .= '<input type="text"' - . ' name="criteria[' . $new_column_count . ']"' - . ' value="' . htmlspecialchars($tmp_criteria) . '"' - . ' class="textfield"' - . ' style="width: ' . $this->_realwidth . '"' - . ' size="20">'; - $html_output .= '</td>'; - $new_column_count++; - } // end for - $html_output .= '</tr>'; - return $html_output; - } - - /** - * Provides footer options for adding/deleting row/columns - * - * @param string $type Whether row or column - * - * @return string HTML for footer options - */ - private function _getFootersOptions($type) - { - return $this->template->render('database/qbe/footer_options', [ - 'type' => $type, - ]); - } - - /** - * Provides search form table's footer options - * - * @return string HTML for table footer - */ - private function _getTableFooters() - { - $html_output = '<fieldset class="tblFooters">'; - $html_output .= $this->_getFootersOptions("row"); - $html_output .= $this->_getFootersOptions("column"); - $html_output .= '<div class="floatleft">'; - $html_output .= '<input class="btn btn-secondary" type="submit" name="modify"' - . ' value="' . __('Update Query') . '">'; - $html_output .= '</div>'; - $html_output .= '</fieldset>'; - return $html_output; - } - - /** - * Provides a select list of database tables - * - * @return string HTML for table select list - */ - private function _getTablesList() - { - $html_output = '<div class="floatleft width100">'; - $html_output .= '<fieldset>'; - $html_output .= '<legend>' . __('Use Tables') . '</legend>'; - // Build the options list for each table name - $options = ''; - $numTableListOptions = 0; - foreach ($this->_criteriaTables as $key => $val) { - $options .= '<option value="' . htmlspecialchars($key) . '"' . $val . '>' - . str_replace(' ', ' ', htmlspecialchars($key)) . '</option>'; - $numTableListOptions++; - } - $html_output .= '<select name="TableList[]"' - . ' multiple="multiple" id="listTable"' - . ' size="' . ($numTableListOptions > 30 ? '15' : '7') . '">'; - $html_output .= $options; - $html_output .= '</select>'; - $html_output .= '</fieldset>'; - $html_output .= '<fieldset class="tblFooters">'; - $html_output .= '<input class="btn btn-secondary" type="submit" name="modify" value="' - . __('Update Query') . '">'; - $html_output .= '</fieldset>'; - $html_output .= '</div>'; - return $html_output; - } - - /** - * Provides And/Or modification cell along with Insert/Delete options - * (For modifying search form's table columns) - * - * @param integer $column_number Column Number (0,1,2) or more - * @param array|null $selected Selected criteria column name - * @param bool $last_column Whether this is the last column - * - * @return string HTML for modification cell - */ - private function _getAndOrColCell( - $column_number, - $selected = null, - $last_column = false - ) { - $html_output = '<td class="center">'; - if (! $last_column) { - $html_output .= '<strong>' . __('Or:') . '</strong>'; - $html_output .= '<input type="radio"' - . ' name="criteriaAndOrColumn[' . $column_number . ']"' - . ' value="or"' . ($selected['or'] ?? '') . '>'; - $html_output .= ' <strong>' . __('And:') . '</strong>'; - $html_output .= '<input type="radio"' - . ' name="criteriaAndOrColumn[' . $column_number . ']"' - . ' value="and"' . ($selected['and'] ?? '') . '>'; - } - $html_output .= '<br>' . __('Ins'); - $html_output .= '<input type="checkbox"' - . ' name="criteriaColumnInsert[' . $column_number . ']">'; - $html_output .= ' ' . __('Del'); - $html_output .= '<input type="checkbox"' - . ' name="criteriaColumnDelete[' . $column_number . ']">'; - $html_output .= '</td>'; - return $html_output; - } - - /** - * Provides search form's row containing column modifications options - * (For modifying search form's table columns) - * - * @return string HTML for search table's row - */ - private function _getModifyColumnsRow() - { - $html_output = '<tr class="noclick">'; - $html_output .= '<th>' . __('Modify:') . '</th>'; - $new_column_count = 0; - for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { - if (! empty($this->_criteriaColumnInsert) - && isset($this->_criteriaColumnInsert[$column_index]) - && $this->_criteriaColumnInsert[$column_index] == 'on' - ) { - $html_output .= $this->_getAndOrColCell($new_column_count); - $new_column_count++; - } // end if - - if (! empty($this->_criteriaColumnDelete) - && isset($this->_criteriaColumnDelete[$column_index]) - && $this->_criteriaColumnDelete[$column_index] == 'on' - ) { - continue; - } - - if (isset($this->_criteriaAndOrColumn[$column_index])) { - $this->_formAndOrCols[$new_column_count] - = $this->_criteriaAndOrColumn[$column_index]; - } - $checked_options = []; - if (isset($this->_criteriaAndOrColumn[$column_index]) - && $this->_criteriaAndOrColumn[$column_index] == 'or' - ) { - $checked_options['or'] = ' checked="checked"'; - $checked_options['and'] = ''; - } else { - $checked_options['and'] = ' checked="checked"'; - $checked_options['or'] = ''; - } - $html_output .= $this->_getAndOrColCell( - $new_column_count, - $checked_options, - $column_index + 1 == $this->_criteria_column_count - ); - $new_column_count++; - } // end for - $html_output .= '</tr>'; - return $html_output; - } - - /** - * Provides Insert/Delete options for criteria inputbox - * with AND/OR relationship modification options - * - * @param integer $row_index Number of criteria row - * @param array $checked_options If checked - * - * @return string HTML - */ - private function _getInsDelAndOrCell($row_index, array $checked_options) - { - $html_output = '<td class="value nowrap">'; - $html_output .= '<!-- Row controls -->'; - $html_output .= '<table class="nospacing nopadding">'; - $html_output .= '<tr>'; - $html_output .= '<td class="value nowrap">'; - $html_output .= '<small>' . __('Ins:') . '</small>'; - $html_output .= '<input type="checkbox"' - . ' name="criteriaRowInsert[' . $row_index . ']">'; - $html_output .= '</td>'; - $html_output .= '<td class="value">'; - $html_output .= '<strong>' . __('And:') . '</strong>'; - $html_output .= '</td>'; - $html_output .= '<td>'; - $html_output .= '<input type="radio"' - . ' name="criteriaAndOrRow[' . $row_index . ']" value="and"' - . $checked_options['and'] . '>'; - $html_output .= '</td>'; - $html_output .= '</tr>'; - $html_output .= '<tr>'; - $html_output .= '<td class="value nowrap">'; - $html_output .= '<small>' . __('Del:') . '</small>'; - $html_output .= '<input type="checkbox"' - . ' name="criteriaRowDelete[' . $row_index . ']">'; - $html_output .= '</td>'; - $html_output .= '<td class="value">'; - $html_output .= '<strong>' . __('Or:') . '</strong>'; - $html_output .= '</td>'; - $html_output .= '<td>'; - $html_output .= '<input type="radio"' - . ' name="criteriaAndOrRow[' . $row_index . ']"' - . ' value="or"' . $checked_options['or'] . '>'; - $html_output .= '</td>'; - $html_output .= '</tr>'; - $html_output .= '</table>'; - $html_output .= '</td>'; - return $html_output; - } - - /** - * Provides rows for criteria inputbox Insert/Delete options - * with AND/OR relationship modification options - * - * @param integer $new_row_index New row index if rows are added/deleted - * - * @return string HTML table rows - */ - private function _getInputboxRow($new_row_index) - { - $html_output = ''; - $new_column_count = 0; - for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { - if (! empty($this->_criteriaColumnInsert) - && isset($this->_criteriaColumnInsert[$column_index]) - && $this->_criteriaColumnInsert[$column_index] == 'on' - ) { - $orFieldName = 'Or' . $new_row_index . '[' . $new_column_count . ']'; - $html_output .= '<td class="center">'; - $html_output .= '<input type="text"' - . ' name="Or' . $orFieldName . '" class="textfield"' - . ' style="width: ' . $this->_realwidth . '" size="20">'; - $html_output .= '</td>'; - $new_column_count++; - } // end if - if (! empty($this->_criteriaColumnDelete) - && isset($this->_criteriaColumnDelete[$column_index]) - && $this->_criteriaColumnDelete[$column_index] == 'on' - ) { - continue; - } - $or = 'Or' . $new_row_index; - if (! empty($_POST[$or]) && isset($_POST[$or][$column_index])) { - $tmp_or = $_POST[$or][$column_index]; - } else { - $tmp_or = ''; - } - $html_output .= '<td class="center">'; - $html_output .= '<input type="text"' - . ' name="Or' . $new_row_index . '[' . $new_column_count . ']"' - . ' value="' . htmlspecialchars($tmp_or) . '" class="textfield"' - . ' style="width: ' . $this->_realwidth . '" size="20">'; - $html_output .= '</td>'; - if (! empty(${$or}) && isset(${$or}[$column_index])) { - $GLOBALS[${'cur' . $or}][$new_column_count] - = ${$or}[$column_index]; - } - $new_column_count++; - } // end for - return $html_output; - } - - /** - * Provides rows for criteria inputbox Insert/Delete options - * with AND/OR relationship modification options - * - * @return string HTML table rows - */ - private function _getInsDelAndOrCriteriaRows() - { - $html_output = ''; - $new_row_count = 0; - $checked_options = []; - for ($row_index = 0; $row_index <= $this->_criteria_row_count; $row_index++) { - if (isset($this->_criteriaRowInsert[$row_index]) - && $this->_criteriaRowInsert[$row_index] == 'on' - ) { - $checked_options['or'] = ' checked="checked"'; - $checked_options['and'] = ''; - $html_output .= '<tr class="noclick">'; - $html_output .= $this->_getInsDelAndOrCell( - $new_row_count, - $checked_options - ); - $html_output .= $this->_getInputboxRow( - $new_row_count - ); - $new_row_count++; - $html_output .= '</tr>'; - } // end if - if (isset($this->_criteriaRowDelete[$row_index]) - && $this->_criteriaRowDelete[$row_index] == 'on' - ) { - continue; - } - if (isset($this->_criteriaAndOrRow[$row_index])) { - $this->_formAndOrRows[$new_row_count] - = $this->_criteriaAndOrRow[$row_index]; - } - if (isset($this->_criteriaAndOrRow[$row_index]) - && $this->_criteriaAndOrRow[$row_index] == 'and' - ) { - $checked_options['and'] = ' checked="checked"'; - $checked_options['or'] = ''; - } else { - $checked_options['or'] = ' checked="checked"'; - $checked_options['and'] = ''; - } - $html_output .= '<tr class="noclick">'; - $html_output .= $this->_getInsDelAndOrCell( - $new_row_count, - $checked_options - ); - $html_output .= $this->_getInputboxRow( - $new_row_count - ); - $new_row_count++; - $html_output .= '</tr>'; - } // end for - $this->_new_row_count = $new_row_count; - return $html_output; - } - - /** - * Provides SELECT clause for building SQL query - * - * @return string Select clause - */ - private function _getSelectClause() - { - $select_clause = ''; - $select_clauses = []; - for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { - if (! empty($this->_formColumns[$column_index]) - && isset($this->_formShows[$column_index]) - && $this->_formShows[$column_index] == 'on' - ) { - $select = $this->_formColumns[$column_index]; - if (! empty($this->_formAliases[$column_index])) { - $select .= " AS " - . Util::backquote($this->_formAliases[$column_index]); - } - $select_clauses[] = $select; - } - } // end for - if (! empty($select_clauses)) { - $select_clause = 'SELECT ' - . htmlspecialchars(implode(", ", $select_clauses)) . "\n"; - } - return $select_clause; - } - - /** - * Provides WHERE clause for building SQL query - * - * @return string Where clause - */ - private function _getWhereClause() - { - $where_clause = ''; - $criteria_cnt = 0; - for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { - if (! empty($this->_formColumns[$column_index]) - && ! empty($this->_formCriterions[$column_index]) - && $column_index - && isset($last_where) - && isset($this->_formAndOrCols) - ) { - $where_clause .= ' ' - . mb_strtoupper($this->_formAndOrCols[$last_where]) - . ' '; - } - if (! empty($this->_formColumns[$column_index]) - && ! empty($this->_formCriterions[$column_index]) - ) { - $where_clause .= '(' . $this->_formColumns[$column_index] . ' ' - . $this->_formCriterions[$column_index] . ')'; - $last_where = $column_index; - $criteria_cnt++; - } - } // end for - if ($criteria_cnt > 1) { - $where_clause = '(' . $where_clause . ')'; - } - // OR rows ${'cur' . $or}[$column_index] - if (! isset($this->_formAndOrRows)) { - $this->_formAndOrRows = []; - } - for ($row_index = 0; $row_index <= $this->_criteria_row_count; $row_index++) { - $criteria_cnt = 0; - $qry_orwhere = ''; - $last_orwhere = ''; - for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { - if (! empty($this->_formColumns[$column_index]) - && ! empty($_POST['Or' . $row_index][$column_index]) - && $column_index - ) { - $qry_orwhere .= ' ' - . mb_strtoupper( - $this->_formAndOrCols[$last_orwhere] - ) - . ' '; - } - if (! empty($this->_formColumns[$column_index]) - && ! empty($_POST['Or' . $row_index][$column_index]) - ) { - $qry_orwhere .= '(' . $this->_formColumns[$column_index] - . ' ' - . $_POST['Or' . $row_index][$column_index] - . ')'; - $last_orwhere = $column_index; - $criteria_cnt++; - } - } // end for - if ($criteria_cnt > 1) { - $qry_orwhere = '(' . $qry_orwhere . ')'; - } - if (! empty($qry_orwhere)) { - $where_clause .= "\n" - . mb_strtoupper( - isset($this->_formAndOrRows[$row_index]) - ? $this->_formAndOrRows[$row_index] . ' ' - : '' - ) - . $qry_orwhere; - } // end if - } // end for - - if (! empty($where_clause) && $where_clause != '()') { - $where_clause = 'WHERE ' . htmlspecialchars($where_clause) . "\n"; - } // end if - return $where_clause; - } - - /** - * Provides ORDER BY clause for building SQL query - * - * @return string Order By clause - */ - private function _getOrderByClause() - { - $orderby_clause = ''; - $orderby_clauses = []; - - // Create copy of instance variables - $columns = $this->_formColumns; - $sort = $this->_formSorts; - $sortOrder = $this->_formSortOrders; - if (! empty($sortOrder) - && count($sortOrder) == count($sort) - && count($sortOrder) == count($columns) - ) { - // Sort all three arrays based on sort order - array_multisort($sortOrder, $sort, $columns); - } - - for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { - // if all columns are chosen with * selector, - // then sorting isn't available - // Fix for Bug #570698 - if (empty($columns[$column_index]) - && empty($sort[$column_index]) - ) { - continue; - } - - if (mb_substr($columns[$column_index], -2) == '.*') { - continue; - } - - if (! empty($sort[$column_index])) { - $orderby_clauses[] = $columns[$column_index] . ' ' - . $sort[$column_index]; - } - } // end for - if (! empty($orderby_clauses)) { - $orderby_clause = 'ORDER BY ' - . htmlspecialchars(implode(", ", $orderby_clauses)) . "\n"; - } - return $orderby_clause; - } - - /** - * Provides UNIQUE columns and INDEX columns present in criteria tables - * - * @param array $search_tables Tables involved in the search - * @param array $search_columns Columns involved in the search - * @param array $where_clause_columns Columns having criteria where clause - * - * @return array having UNIQUE and INDEX columns - */ - private function _getIndexes( - array $search_tables, - array $search_columns, - array $where_clause_columns - ) { - $unique_columns = []; - $index_columns = []; - - foreach ($search_tables as $table) { - $indexes = $this->dbi->getTableIndexes($this->_db, $table); - foreach ($indexes as $index) { - $column = $table . '.' . $index['Column_name']; - if (isset($search_columns[$column])) { - if ($index['Non_unique'] == 0) { - if (isset($where_clause_columns[$column])) { - $unique_columns[$column] = 'Y'; - } else { - $unique_columns[$column] = 'N'; - } - } else { - if (isset($where_clause_columns[$column])) { - $index_columns[$column] = 'Y'; - } else { - $index_columns[$column] = 'N'; - } - } - } - } // end while (each index of a table) - } // end while (each table) - - return [ - 'unique' => $unique_columns, - 'index' => $index_columns, - ]; - } - - /** - * Provides UNIQUE columns and INDEX columns present in criteria tables - * - * @param array $search_tables Tables involved in the search - * @param array $search_columns Columns involved in the search - * @param array $where_clause_columns Columns having criteria where clause - * - * @return array having UNIQUE and INDEX columns - */ - private function _getLeftJoinColumnCandidates( - array $search_tables, - array $search_columns, - array $where_clause_columns - ) { - $this->dbi->selectDb($this->_db); - - // Get unique columns and index columns - $indexes = $this->_getIndexes( - $search_tables, - $search_columns, - $where_clause_columns - ); - $unique_columns = $indexes['unique']; - $index_columns = $indexes['index']; - - list($candidate_columns, $needsort) - = $this->_getLeftJoinColumnCandidatesBest( - $search_tables, - $where_clause_columns, - $unique_columns, - $index_columns - ); - - // If we came up with $unique_columns (very good) or $index_columns (still - // good) as $candidate_columns we want to check if we have any 'Y' there - // (that would mean that they were also found in the whereclauses - // which would be great). if yes, we take only those - if ($needsort != 1) { - return $candidate_columns; - } - - $very_good = []; - $still_good = []; - foreach ($candidate_columns as $column => $is_where) { - $table = explode('.', $column); - $table = $table[0]; - if ($is_where == 'Y') { - $very_good[$column] = $table; - } else { - $still_good[$column] = $table; - } - } - if (count($very_good) > 0) { - $candidate_columns = $very_good; - // Candidates restricted in index+where - } else { - $candidate_columns = $still_good; - // None of the candidates where in a where-clause - } - - return $candidate_columns; - } - - /** - * Provides the main table to form the LEFT JOIN clause - * - * @param array $search_tables Tables involved in the search - * @param array $search_columns Columns involved in the search - * @param array $where_clause_columns Columns having criteria where clause - * @param array $where_clause_tables Tables having criteria where clause - * - * @return string table name - */ - private function _getMasterTable( - array $search_tables, - array $search_columns, - array $where_clause_columns, - array $where_clause_tables - ) { - if (count($where_clause_tables) === 1) { - // If there is exactly one column that has a decent where-clause - // we will just use this - return key($where_clause_tables); - } - - // Now let's find out which of the tables has an index - // (When the control user is the same as the normal user - // because he is using one of his databases as pmadb, - // the last db selected is not always the one where we need to work) - $candidate_columns = $this->_getLeftJoinColumnCandidates( - $search_tables, - $search_columns, - $where_clause_columns - ); - - // Generally, we need to display all the rows of foreign (referenced) - // table, whether they have any matching row in child table or not. - // So we select candidate tables which are foreign tables. - $foreign_tables = []; - foreach ($candidate_columns as $one_table) { - $foreigners = $this->relation->getForeigners($this->_db, $one_table); - foreach ($foreigners as $key => $foreigner) { - if ($key != 'foreign_keys_data') { - if (in_array($foreigner['foreign_table'], $candidate_columns)) { - $foreign_tables[$foreigner['foreign_table']] - = $foreigner['foreign_table']; - } - continue; - } - foreach ($foreigner as $one_key) { - if (in_array($one_key['ref_table_name'], $candidate_columns)) { - $foreign_tables[$one_key['ref_table_name']] - = $one_key['ref_table_name']; - } - } - } - } - if (count($foreign_tables)) { - $candidate_columns = $foreign_tables; - } - - // If our array of candidates has more than one member we'll just - // find the smallest table. - // Of course the actual query would be faster if we check for - // the Criteria which gives the smallest result set in its table, - // but it would take too much time to check this - if (! (count($candidate_columns) > 1)) { - // Only one single candidate - return reset($candidate_columns); - } - - // Of course we only want to check each table once - $checked_tables = $candidate_columns; - $tsize = []; - $maxsize = -1; - $result = ''; - foreach ($candidate_columns as $table) { - if ($checked_tables[$table] != 1) { - $_table = new Table($table, $this->_db); - $tsize[$table] = $_table->countRecords(); - $checked_tables[$table] = 1; - } - if ($tsize[$table] > $maxsize) { - $maxsize = $tsize[$table]; - $result = $table; - } - } - // Return largest table - return $result; - } - - /** - * Provides columns and tables that have valid where clause criteria - * - * @return array - */ - private function _getWhereClauseTablesAndColumns() - { - $where_clause_columns = []; - $where_clause_tables = []; - - // Now we need all tables that we have in the where clause - for ($column_index = 0, $nb = count($this->_criteria); $column_index < $nb; $column_index++) { - $current_table = explode('.', $_POST['criteriaColumn'][$column_index]); - if (empty($current_table[0]) || empty($current_table[1])) { - continue; - } // end if - $table = str_replace('`', '', $current_table[0]); - $column = str_replace('`', '', $current_table[1]); - $column = $table . '.' . $column; - // Now we know that our array has the same numbers as $criteria - // we can check which of our columns has a where clause - if (! empty($this->_criteria[$column_index])) { - if (mb_substr($this->_criteria[$column_index], 0, 1) == '=' - || false !== stripos($this->_criteria[$column_index], 'is') - ) { - $where_clause_columns[$column] = $column; - $where_clause_tables[$table] = $table; - } - } // end if - } // end for - return [ - 'where_clause_tables' => $where_clause_tables, - 'where_clause_columns' => $where_clause_columns, - ]; - } - - /** - * Provides FROM clause for building SQL query - * - * @param array $formColumns List of selected columns in the form - * - * @return string FROM clause - */ - private function _getFromClause(array $formColumns) - { - $from_clause = ''; - if (empty($formColumns)) { - return $from_clause; - } - - // Initialize some variables - $search_tables = $search_columns = []; - - // We only start this if we have fields, otherwise it would be dumb - foreach ($formColumns as $value) { - $parts = explode('.', $value); - if (! empty($parts[0]) && ! empty($parts[1])) { - $table = str_replace('`', '', $parts[0]); - $search_tables[$table] = $table; - $search_columns[] = $table . '.' . str_replace( - '`', - '', - $parts[1] - ); - } - } // end while - - // Create LEFT JOINS out of Relations - $from_clause = $this->_getJoinForFromClause( - $search_tables, - $search_columns - ); - - // In case relations are not defined, just generate the FROM clause - // from the list of tables, however we don't generate any JOIN - if (empty($from_clause)) { - // Create cartesian product - $from_clause = implode( - ', ', - array_map([Util::class, 'backquote'], $search_tables) - ); - } - - return $from_clause; - } - - /** - * Formulates the WHERE clause by JOINing tables - * - * @param array $searchTables Tables involved in the search - * @param array $searchColumns Columns involved in the search - * - * @return string table name - */ - private function _getJoinForFromClause(array $searchTables, array $searchColumns) - { - // $relations[master_table][foreign_table] => clause - $relations = []; - - // Fill $relations with inter table relationship data - foreach ($searchTables as $oneTable) { - $this->_loadRelationsForTable($relations, $oneTable); - } - - // Get tables and columns with valid where clauses - $validWhereClauses = $this->_getWhereClauseTablesAndColumns(); - $whereClauseTables = $validWhereClauses['where_clause_tables']; - $whereClauseColumns = $validWhereClauses['where_clause_columns']; - - // Get master table - $master = $this->_getMasterTable( - $searchTables, - $searchColumns, - $whereClauseColumns, - $whereClauseTables - ); - - // Will include master tables and all tables that can be combined into - // a cluster by their relation - $finalized = []; - if (strlen($master) > 0) { - // Add master tables - $finalized[$master] = ''; - } - // Fill the $finalized array with JOIN clauses for each table - $this->_fillJoinClauses($finalized, $relations, $searchTables); - - // JOIN clause - $join = ''; - - // Tables that can not be combined with the table cluster - // which includes master table - $unfinalized = array_diff($searchTables, array_keys($finalized)); - if (count($unfinalized) > 0) { - // We need to look for intermediary tables to JOIN unfinalized tables - // Heuristic to chose intermediary tables is to look for tables - // having relationships with unfinalized tables - foreach ($unfinalized as $oneTable) { - $references = $this->relation->getChildReferences($this->_db, $oneTable); - foreach ($references as $column => $columnReferences) { - foreach ($columnReferences as $reference) { - // Only from this schema - if ($reference['table_schema'] != $this->_db) { - continue; - } - - $table = $reference['table_name']; - - $this->_loadRelationsForTable($relations, $table); - - // Make copies - $tempFinalized = $finalized; - $tempSearchTables = $searchTables; - $tempSearchTables[] = $table; - - // Try joining with the added table - $this->_fillJoinClauses( - $tempFinalized, - $relations, - $tempSearchTables - ); - - $tempUnfinalized = array_diff( - $tempSearchTables, - array_keys($tempFinalized) - ); - // Take greedy approach. - // If the unfinalized count drops we keep the new table - // and switch temporary varibles with the original ones - if (count($tempUnfinalized) < count($unfinalized)) { - $finalized = $tempFinalized; - $searchTables = $tempSearchTables; - } - - // We are done if no unfinalized tables anymore - if (count($tempUnfinalized) === 0) { - break 3; - } - } - } - } - - $unfinalized = array_diff($searchTables, array_keys($finalized)); - // If there are still unfinalized tables - if (count($unfinalized) > 0) { - // Add these tables as cartesian product before joined tables - $join .= implode( - ', ', - array_map([Util::class, 'backquote'], $unfinalized) - ); - } - } - - $first = true; - // Add joined tables - foreach ($finalized as $table => $clause) { - if ($first) { - if (! empty($join)) { - $join .= ", "; - } - $join .= Util::backquote($table); - $first = false; - } else { - $join .= "\n LEFT JOIN " . Util::backquote( - $table - ) . " ON " . $clause; - } - } - - return $join; - } - - /** - * Loads relations for a given table into the $relations array - * - * @param array $relations array of relations - * @param string $oneTable the table - * - * @return void - */ - private function _loadRelationsForTable(array &$relations, $oneTable) - { - $relations[$oneTable] = []; - - $foreigners = $this->relation->getForeigners($GLOBALS['db'], $oneTable); - foreach ($foreigners as $field => $foreigner) { - // Foreign keys data - if ($field == 'foreign_keys_data') { - foreach ($foreigner as $oneKey) { - $clauses = []; - // There may be multiple column relations - foreach ($oneKey['index_list'] as $index => $oneField) { - $clauses[] - = Util::backquote($oneTable) . "." - . Util::backquote($oneField) . " = " - . Util::backquote($oneKey['ref_table_name']) . "." - . Util::backquote($oneKey['ref_index_list'][$index]); - } - // Combine multiple column relations with AND - $relations[$oneTable][$oneKey['ref_table_name']] - = implode(" AND ", $clauses); - } - } else { // Internal relations - $relations[$oneTable][$foreigner['foreign_table']] - = Util::backquote($oneTable) . "." - . Util::backquote($field) . " = " - . Util::backquote($foreigner['foreign_table']) . "." - . Util::backquote($foreigner['foreign_field']); - } - } - } - - /** - * Fills the $finalized arrays with JOIN clauses for each of the tables - * - * @param array $finalized JOIN clauses for each table - * @param array $relations Relations among tables - * @param array $searchTables Tables involved in the search - * - * @return void - */ - private function _fillJoinClauses(array &$finalized, array $relations, array $searchTables) - { - while (true) { - $added = false; - foreach ($searchTables as $masterTable) { - $foreignData = $relations[$masterTable]; - foreach ($foreignData as $foreignTable => $clause) { - if (! isset($finalized[$masterTable]) - && isset($finalized[$foreignTable]) - ) { - $finalized[$masterTable] = $clause; - $added = true; - } elseif (! isset($finalized[$foreignTable]) - && isset($finalized[$masterTable]) - && in_array($foreignTable, $searchTables) - ) { - $finalized[$foreignTable] = $clause; - $added = true; - } - if ($added) { - // We are done if all tables are in $finalized - if (count($finalized) == count($searchTables)) { - return; - } - } - } - } - // If no new tables were added during this iteration, break; - if (! $added) { - return; - } - } - } - - /** - * Provides the generated SQL query - * - * @param array $formColumns List of selected columns in the form - * - * @return string SQL query - */ - private function _getSQLQuery(array $formColumns) - { - $sql_query = ''; - // get SELECT clause - $sql_query .= $this->_getSelectClause(); - // get FROM clause - $from_clause = $this->_getFromClause($formColumns); - if (! empty($from_clause)) { - $sql_query .= 'FROM ' . htmlspecialchars($from_clause) . "\n"; - } - // get WHERE clause - $sql_query .= $this->_getWhereClause(); - // get ORDER BY clause - $sql_query .= $this->_getOrderByClause(); - return $sql_query; - } - - /** - * Provides the generated QBE form - * - * @return string QBE form - */ - public function getSelectionForm() - { - $html_output = '<form action="db_qbe.php" method="post" id="formQBE" ' - . 'class="lock-page">'; - $html_output .= '<div class="width100">'; - $html_output .= '<fieldset>'; - - if ($GLOBALS['cfgRelation']['savedsearcheswork']) { - $html_output .= $this->_getSavedSearchesField(); - } - - $html_output .= '<div class="responsivetable jsresponsive">'; - $html_output .= '<table class="data" style="width: 100%;">'; - // Get table's <tr> elements - $html_output .= $this->_getColumnNamesRow(); - $html_output .= $this->_getColumnAliasRow(); - $html_output .= $this->_getShowRow(); - $html_output .= $this->_getSortRow(); - $html_output .= $this->_getSortOrder(); - $html_output .= $this->_getCriteriaInputboxRow(); - $html_output .= $this->_getInsDelAndOrCriteriaRows(); - $html_output .= $this->_getModifyColumnsRow(); - $html_output .= '</table>'; - $this->_new_row_count--; - $url_params = []; - $url_params['db'] = $this->_db; - $url_params['criteriaColumnCount'] = $this->_new_column_count; - $url_params['rows'] = $this->_new_row_count; - $html_output .= Url::getHiddenInputs($url_params); - $html_output .= '</div>'; - $html_output .= '</fieldset>'; - $html_output .= '</div>'; - // get footers - $html_output .= $this->_getTableFooters(); - // get tables select list - $html_output .= $this->_getTablesList(); - $html_output .= '</form>'; - $html_output .= '<form action="db_qbe.php" method="post" class="lock-page">'; - $html_output .= Url::getHiddenInputs(['db' => $this->_db]); - // get SQL query - $html_output .= '<div class="floatleft desktop50">'; - $html_output .= '<fieldset id="tblQbe">'; - $html_output .= '<legend>' - . sprintf( - __('SQL query on database <b>%s</b>:'), - Util::getDbLink($this->_db) - ); - $html_output .= '</legend>'; - $text_dir = 'ltr'; - $html_output .= '<textarea cols="80" name="sql_query" id="textSqlquery"' - . ' rows="' . (count($this->_criteriaTables) > 30 ? '15' : '7') . '"' - . ' dir="' . $text_dir . '">'; - - if (empty($this->_formColumns)) { - $this->_formColumns = []; - } - $html_output .= $this->_getSQLQuery($this->_formColumns); - - $html_output .= '</textarea>'; - $html_output .= '</fieldset>'; - // displays form's footers - $html_output .= '<fieldset class="tblFooters" id="tblQbeFooters">'; - $html_output .= '<input type="hidden" name="submit_sql" value="1">'; - $html_output .= '<input class="btn btn-primary" type="submit" value="' . __('Submit Query') . '">'; - $html_output .= '</fieldset>'; - $html_output .= '</div>'; - $html_output .= '</form>'; - return $html_output; - } - - /** - * Get fields to display - * - * @return string - */ - private function _getSavedSearchesField() - { - $html_output = __('Saved bookmarked search:'); - $html_output .= ' <select name="searchId" id="searchId">'; - $html_output .= '<option value="">' . __('New bookmark') . '</option>'; - - $currentSearch = $this->_getCurrentSearch(); - $currentSearchId = null; - $currentSearchName = null; - if (null != $currentSearch) { - $currentSearchId = $currentSearch->getId(); - $currentSearchName = $currentSearch->getSearchName(); - } - - foreach ($this->_savedSearchList as $id => $name) { - $html_output .= '<option value="' . htmlspecialchars($id) - . '" ' . ( - $id == $currentSearchId - ? 'selected="selected" ' - : '' - ) - . '>' - . htmlspecialchars($name) - . '</option>'; - } - $html_output .= '</select>'; - $html_output .= '<input type="text" name="searchName" id="searchName" ' - . 'value="' . htmlspecialchars((string) $currentSearchName) . '">'; - $html_output .= '<input type="hidden" name="action" id="action" value="">'; - $html_output .= '<input class="btn btn-secondary" type="submit" name="saveSearch" id="saveSearch" ' - . 'value="' . __('Create bookmark') . '">'; - if (null !== $currentSearchId) { - $html_output .= '<input class="btn btn-secondary" type="submit" name="updateSearch" ' - . 'id="updateSearch" value="' . __('Update bookmark') . '">'; - $html_output .= '<input class="btn btn-secondary" type="submit" name="deleteSearch" ' - . 'id="deleteSearch" value="' . __('Delete bookmark') . '">'; - } - - return $html_output; - } - - /** - * Initialize _criteria_column_count - * - * @return int Previous number of columns - */ - private function _initializeCriteriasCount(): int - { - // sets column count - $criteriaColumnCount = Core::ifSetOr( - $_POST['criteriaColumnCount'], - 3, - 'numeric' - ); - $criteriaColumnAdd = Core::ifSetOr( - $_POST['criteriaColumnAdd'], - 0, - 'numeric' - ); - $this->_criteria_column_count = max( - $criteriaColumnCount + $criteriaColumnAdd, - 0 - ); - - // sets row count - $rows = Core::ifSetOr($_POST['rows'], 0, 'numeric'); - $criteriaRowAdd = Core::ifSetOr($_POST['criteriaRowAdd'], 0, 'numeric'); - $this->_criteria_row_count = min( - 100, - max($rows + $criteriaRowAdd, 0) - ); - - return (int) $criteriaColumnCount; - } - - /** - * Get best - * - * @param array $search_tables Tables involved in the search - * @param array|null $where_clause_columns Columns with where clause - * @param array|null $unique_columns Unique columns - * @param array|null $index_columns Indexed columns - * - * @return array - */ - private function _getLeftJoinColumnCandidatesBest( - array $search_tables, - ?array $where_clause_columns, - ?array $unique_columns, - ?array $index_columns - ) { - // now we want to find the best. - if (isset($unique_columns) && count($unique_columns) > 0) { - $candidate_columns = $unique_columns; - $needsort = 1; - return [ - $candidate_columns, - $needsort, - ]; - } elseif (isset($index_columns) && count($index_columns) > 0) { - $candidate_columns = $index_columns; - $needsort = 1; - return [ - $candidate_columns, - $needsort, - ]; - } elseif (isset($where_clause_columns) && count($where_clause_columns) > 0) { - $candidate_columns = $where_clause_columns; - $needsort = 0; - return [ - $candidate_columns, - $needsort, - ]; - } - - $candidate_columns = $search_tables; - $needsort = 0; - return [ - $candidate_columns, - $needsort, - ]; - } -} |