Index: branches/5.2.x/core/kernel/db/dblist.php =================================================================== diff -u -N -r14572 -r14602 --- branches/5.2.x/core/kernel/db/dblist.php (.../dblist.php) (revision 14572) +++ branches/5.2.x/core/kernel/db/dblist.php (.../dblist.php) (revision 14602) @@ -1,6 +1,6 @@ 'WhereFilter', 'class' => kDBList::FLT_SYSTEM, 'join_using' => kDBList::FLT_TYPE_AND), - Array ('type' => 'WhereFilter', 'class' => kDBList::FLT_NORMAL, 'join_using' => kDBList::FLT_TYPE_OR), - Array ('type' => 'WhereFilter', 'class' => kDBList::FLT_SEARCH, 'join_using' => kDBList::FLT_TYPE_OR), - Array ('type' => 'WhereFilter', 'class' => kDBList::FLT_VIEW, 'join_using' => kDBList::FLT_TYPE_AND), - Array ('type' => 'WhereFilter', 'class' => kDBList::FLT_CUSTOM, 'join_using' => kDBList::FLT_TYPE_AND), + Array ('type' => 'WhereFilter', 'class' => self::FLT_SYSTEM, 'join_using' => self::FLT_TYPE_AND), + Array ('type' => 'WhereFilter', 'class' => self::FLT_NORMAL, 'join_using' => self::FLT_TYPE_OR), + Array ('type' => 'WhereFilter', 'class' => self::FLT_SEARCH, 'join_using' => self::FLT_TYPE_OR), + Array ('type' => 'WhereFilter', 'class' => self::FLT_VIEW, 'join_using' => self::FLT_TYPE_AND), + Array ('type' => 'WhereFilter', 'class' => self::FLT_CUSTOM, 'join_using' => self::FLT_TYPE_AND), - Array ('type' => 'HavingFilter', 'class' => kDBList::FLT_SYSTEM, 'join_using' => kDBList::FLT_TYPE_AND), - Array ('type' => 'HavingFilter', 'class' => kDBList::FLT_NORMAL, 'join_using' => kDBList::FLT_TYPE_OR), - Array ('type' => 'HavingFilter', 'class' => kDBList::FLT_SEARCH, 'join_using' => kDBList::FLT_TYPE_OR), - Array ('type' => 'HavingFilter', 'class' => kDBList::FLT_VIEW, 'join_using' => kDBList::FLT_TYPE_AND), - Array ('type' => 'HavingFilter', 'class' => kDBList::FLT_CUSTOM, 'join_using' => kDBList::FLT_TYPE_AND), + Array ('type' => 'HavingFilter', 'class' => self::FLT_SYSTEM, 'join_using' => self::FLT_TYPE_AND), + Array ('type' => 'HavingFilter', 'class' => self::FLT_NORMAL, 'join_using' => self::FLT_TYPE_OR), + Array ('type' => 'HavingFilter', 'class' => self::FLT_SEARCH, 'join_using' => self::FLT_TYPE_OR), + Array ('type' => 'HavingFilter', 'class' => self::FLT_VIEW, 'join_using' => self::FLT_TYPE_AND), + Array ('type' => 'HavingFilter', 'class' => self::FLT_CUSTOM, 'join_using' => self::FLT_TYPE_AND), - Array ('type' => 'AggregateFilter', 'class' => kDBList::FLT_SYSTEM, 'join_using' => kDBList::FLT_TYPE_AND), - Array ('type' => 'AggregateFilter', 'class' => kDBList::FLT_NORMAL, 'join_using' => kDBList::FLT_TYPE_OR), - Array ('type' => 'AggregateFilter', 'class' => kDBList::FLT_VIEW, 'join_using' => kDBList::FLT_TYPE_AND), + Array ('type' => 'AggregateFilter', 'class' => self::FLT_SYSTEM, 'join_using' => self::FLT_TYPE_AND), + Array ('type' => 'AggregateFilter', 'class' => self::FLT_NORMAL, 'join_using' => self::FLT_TYPE_OR), + Array ('type' => 'AggregateFilter', 'class' => self::FLT_VIEW, 'join_using' => self::FLT_TYPE_AND), ); return $filters; @@ -307,16 +307,21 @@ * @param int $filter_scope filter subtype: FLT_NORMAL,FLT_SYSTEM,FLT_SEARCH,FLT_VIEW,FLT_CUSTOM * @access public */ - public function addFilter($name, $clause, $filter_type = kDBList::WHERE_FILTER, $filter_scope = kDBList::FLT_SYSTEM) + public function addFilter($name, $clause, $filter_type = self::WHERE_FILTER, $filter_scope = self::FLT_SYSTEM) { - $filter_source = Array( kDBList::WHERE_FILTER => 'WhereFilter', - kDBList::HAVING_FILTER => 'HavingFilter', - kDBList::AGGREGATE_FILTER => 'AggregateFilter'); + $filter_source = Array ( + self::WHERE_FILTER => 'WhereFilter', + self::HAVING_FILTER => 'HavingFilter', + self::AGGREGATE_FILTER => 'AggregateFilter' + ); + $filter_name = $filter_source[$filter_type]; $filter =& $this->$filter_name; $filter =& $filter[$filter_scope]; - $filter->addFilter($name,$clause); + /* @var $filter kMultipleFilter */ + + $filter->addFilter($name, $clause); } /** @@ -325,17 +330,23 @@ * @param string $name filter name (for internal use) * @param int $filter_type is filter having filter or where filter * @param int $filter_scope filter subtype: FLT_NORMAL,FLT_SYSTEM,FLT_SEARCH,FLT_VIEW,FLT_CUSTOM + * @return string * @access public */ - public function getFilter($name, $filter_type = kDBList::WHERE_FILTER, $filter_scope = kDBList::FLT_SYSTEM) + public function getFilter($name, $filter_type = self::WHERE_FILTER, $filter_scope = self::FLT_SYSTEM) { - $filter_source = Array( kDBList::WHERE_FILTER => 'WhereFilter', - kDBList::HAVING_FILTER => 'HavingFilter', - kDBList::AGGREGATE_FILTER => 'AggregateFilter'); + $filter_source = Array ( + self::WHERE_FILTER => 'WhereFilter', + self::HAVING_FILTER => 'HavingFilter', + self::AGGREGATE_FILTER => 'AggregateFilter' + ); + $filter_name = $filter_source[$filter_type]; $filter =& $this->$filter_name; $filter =& $filter[$filter_scope]; + /* @var $filter kMultipleFilter */ + return $filter->getFilter($name); } @@ -347,15 +358,20 @@ * @param int $filter_scope filter subtype: FLT_NORMAL,FLT_SYSTEM,FLT_SEARCH,FLT_VIEW,FLT_CUSTOM * @access public */ - public function removeFilter($name, $filter_type = kDBList::WHERE_FILTER, $filter_scope = kDBList::FLT_SYSTEM) + public function removeFilter($name, $filter_type = self::WHERE_FILTER, $filter_scope = self::FLT_SYSTEM) { - $filter_source = Array( kDBList::WHERE_FILTER => 'WhereFilter', - kDBList::HAVING_FILTER => 'HavingFilter', - kDBList::AGGREGATE_FILTER => 'AggregateFilter'); + $filter_source = Array ( + self::WHERE_FILTER => 'WhereFilter', + self::HAVING_FILTER => 'HavingFilter', + self::AGGREGATE_FILTER => 'AggregateFilter' + ); + $filter_name = $filter_source[$filter_type]; $filter =& $this->$filter_name; $filter =& $filter[$filter_scope]; + /* @var $filter kMultipleFilter */ + $filter->removeFilter($name); } @@ -464,13 +480,14 @@ } /** - * Queries the database with SQL resulted from {@link kDBList::GetSelectSQL()} and stores result in {@link kDBList::SelectRS} - * - * All the sorting, pagination, filtration of the list should be set prior to calling Query(). - * - * @param bool $force force re-query, when already queried - * @access public - */ + * Queries the database with SQL resulted from {@link kDBList::GetSelectSQL()} and stores result in {@link kDBList::SelectRS} + * + * All the sorting, pagination, filtration of the list should be set prior to calling Query(). + * + * @param bool $force force re-query, when already queried + * @return bool + * @access public + */ public function Query($force=false) { if (!$force && $this->Queried) return true; @@ -640,13 +657,16 @@ */ function GetFormattedTotal($field, $total_function) { - $val = $this->getTotal($field, $total_function); - $options = $this->GetFieldOptions($field); - $res = $val; - if (isset($options['formatter'])) { - $formatter =& $this->Application->recallObject($options['formatter']); - $res = $formatter->Format($val, $field, $this ); + $res = $this->getTotal($field, $total_function); + $formatter_class = $this->GetFieldOption($field, 'formatter'); + + if ( $formatter_class ) { + $formatter =& $this->Application->recallObject($formatter_class); + /* @var $formatter kFormatter */ + + $res = $formatter->Format($res, $field, $this); } + return $res; } @@ -668,6 +688,11 @@ $order = $this->GetOrderClause(); $group = $this->GetGroupClause(); + if ( $for_counting ) { + $optimizer = new LeftJoinOptimizer($q, $where . '|' . $having . '|' . $order . '|' . $group); + $q = $optimizer->simplify(); + } + if (!empty($where)) $q .= ' WHERE ' . $where; if (!empty($group)) $q .= ' GROUP BY ' . $group; if (!empty($having)) $q .= ' HAVING ' . $having; @@ -707,22 +732,25 @@ private function GetWhereClause($for_counting=false,$system_filters_only=false) { $where =& $this->Application->makeClass('kMultipleFilter'); + /* @var $where kMultipleFilter */ + + $where->addFilter('system_where', $this->WhereFilter[self::FLT_SYSTEM] ); - $where->addFilter('system_where', $this->WhereFilter[kDBList::FLT_SYSTEM] ); - if (!$system_filters_only) { - $where->addFilter('view_where', $this->WhereFilter[kDBList::FLT_VIEW] ); - $search_w = $this->WhereFilter[kDBList::FLT_SEARCH]->getSQL(); + $where->addFilter('view_where', $this->WhereFilter[self::FLT_VIEW] ); + $search_w = $this->WhereFilter[self::FLT_SEARCH]->getSQL(); + if ($search_w || $for_counting) { // move search_having to search_where in case search_where isset or we are counting - $search_h = $this->extractCalculatedFields( $this->HavingFilter[kDBList::FLT_SEARCH]->getSQL() ); + $search_h = $this->extractCalculatedFields( $this->HavingFilter[self::FLT_SEARCH]->getSQL() ); $search_w = ($search_w && $search_h) ? $search_w.' OR '.$search_h : $search_w.$search_h; $where->addFilter('search_where', $search_w ); } // CUSTOM - $search_w = $this->WhereFilter[kDBList::FLT_CUSTOM]->getSQL(); + $search_w = $this->WhereFilter[self::FLT_CUSTOM]->getSQL(); + if ($search_w || $for_counting) { // move search_having to search_where in case search_where isset or we are counting - $search_h = $this->extractCalculatedFields( $this->HavingFilter[kDBList::FLT_CUSTOM]->getSQL() ); + $search_h = $this->extractCalculatedFields( $this->HavingFilter[self::FLT_CUSTOM]->getSQL() ); $search_w = ($search_w && $search_h) ? $search_w.' AND '.$search_h : $search_w.$search_h; $where->addFilter('custom_where', $search_w ); } @@ -751,6 +779,8 @@ { if ($for_counting) { $aggregate_filter =& $this->Application->makeClass('kMultipleFilter'); + /* @var $aggregate_filter kMultipleFilter */ + $aggregate_filter->addFilter('aggregate_system', $this->AggregateFilter[kDBList::FLT_SYSTEM]); if (!$system_filters_only) { $aggregate_filter->addFilter('aggregate_view', $this->AggregateFilter[kDBList::FLT_VIEW]); @@ -759,6 +789,7 @@ } $having =& $this->Application->makeClass('kMultipleFilter'); + /* @var $having kMultipleFilter */ $having->addFilter('system_having', $this->HavingFilter[kDBList::FLT_SYSTEM] ); if ($aggregated == 0) { @@ -1283,6 +1314,7 @@ * @param string $error_label * @param Array $error_params * @access public + * @todo Might not work correctly! */ public function SetError($field, $pseudo, $error_label = null, $error_params = null) { @@ -1368,4 +1400,195 @@ { $this->mainList = true; } +} + + +class LeftJoinOptimizer { + + /** + * Input sql for optimization + * + * @var string + * @access private + */ + private $sql = ''; + + /** + * All sql parts, where LEFT JOINed table aliases could be used + * + * @var string + * @access private + */ + private $usageString = ''; + + /** + * List of discovered LEFT JOINs + * + * @var Array + * @access private + */ + private $joins = Array (); + + /** + * LEFT JOIN relations + * + * @var Array + * @access private + */ + private $joinRelations = Array (); + + /** + * LEFT JOIN table aliases scheduled for removal + * + * @var Array + * @access private + */ + private $aliasesToRemove = Array (); + + /** + * Creates new instance of the class + * + * @param string $sql + * @param string $usage_string + */ + public function __construct($sql, $usage_string) + { + $this->sql = $sql; + $this->usageString = $usage_string; + + $this->parseJoins(); + } + + /** + * Tries to remove unused LEFT JOINs + * + * @return string + * @access public + */ + public function simplify() + { + if ( !$this->joins ) { + // no LEFT JOIN used, return unchanged sql + return $this->sql; + } + + $this->updateRelations(); + $this->removeAliases(); + + return $this->sql; + } + + /** + * Discovers LEFT JOINs based on given sql + * + * @return void + * @access private + */ + private function parseJoins() + { + if ( !preg_match_all('/LEFT\s+JOIN\s+(.*?|.*?\s+AS\s+.*?|.*?\s+.*?)\s+ON\s+(.*?\n|.*?$)/i', $this->sql, $regs) ) { + $this->joins = Array (); + } + + // get all LEFT JOIN clause info from sql (without filters) + foreach ($regs[1] as $index => $match) { + $match_parts = preg_split('/\s+AS\s+|\s+/i', $match, 2); + $table_alias = count($match_parts) == 1 ? $match : $match_parts[1]; + + $this->joins[$table_alias] = Array ( + 'table' => $match_parts[0], + 'join_clause' => $regs[0][$index], + ); + } + } + + /** + * Detects relations between LEFT JOINs + * + * @return void + * @access private + */ + private function updateRelations() + { + foreach ($this->joins as $table_alias => $left_join_info) { + $escaped_alias = preg_quote($table_alias, '/'); + + foreach ($this->joins as $sub_table_alias => $sub_left_join_info) { + if ($table_alias == $sub_table_alias) { + continue; + } + + if ( $this->matchAlias($escaped_alias, $sub_left_join_info['join_clause']) ) { + $this->joinRelations[] = $sub_table_alias . ':' . $table_alias; + } + } + } + } + + /** + * Removes scheduled LEFT JOINs, but only if they are not protected + * + * @return void + * @access private + */ + private function removeAliases() + { + $this->prepareAliasesRemoval(); + + foreach ($this->aliasesToRemove as $to_remove_alias) { + if ( !$this->aliasProtected($to_remove_alias) ) { + $this->sql = str_replace($this->joins[$to_remove_alias]['join_clause'], '', $this->sql); + } + } + } + + /** + * Schedules unused LEFT JOINs to for removal + * + * @return void + * @access private + */ + private function prepareAliasesRemoval() + { + foreach ($this->joins as $table_alias => $left_join_info) { + $escaped_alias = preg_quote($table_alias, '/'); + + if ( !$this->matchAlias($escaped_alias, $this->usageString) ) { + $this->aliasesToRemove[] = $table_alias; + } + } + } + + /** + * Checks if someone wants to remove LEFT JOIN, but it's used by some other LEFT JOIN, that stays + * + * @param string $table_alias + * @return bool + * @access private + */ + private function aliasProtected($table_alias) + { + foreach ($this->joinRelations as $relation) { + list ($main_alias, $used_alias) = explode(':', $relation); + + if ( ($used_alias == $table_alias) && !in_array($main_alias, $this->aliasesToRemove) ) { + return true; + } + } + + return false; + } + + /** + * Matches given escaped alias to a string + * + * @param string $escaped_alias + * @param string $string + * @return bool + * @access private + */ + private function matchAlias($escaped_alias, $string) + { + return preg_match('/(`' . $escaped_alias . '`|' . $escaped_alias . ')\./', $string); + } } \ No newline at end of file