Page MenuHomeIn-Portal Phabricator
Files F800020

in-portal
No OneTemporary
Actions

File Metadata

Created
Fri, Feb 21, 11:59 PM

in-portal
View Options

Index: branches/5.2.x/core/kernel/db/dblist.php
===================================================================
--- branches/5.2.x/core/kernel/db/dblist.php (revision 16757)
+++ branches/5.2.x/core/kernel/db/dblist.php (revision 16758)
@@ -1,1770 +1,1789 @@
<?php
/**
* @version $Id$
* @package In-Portal
* @copyright Copyright (C) 1997 - 2009 Intechnic. All rights reserved.
* @license GNU/GPL
* In-Portal is Open Source software.
* This means that this software may have been modified pursuant
* the GNU General Public License, and as distributed it includes
* or is derivative of works licensed under the GNU General Public License
* or other free or open source software licenses.
* See http://www.in-portal.org/license for copyright notices and details.
*/
defined('FULL_PATH') or die('restricted access!');
/**
* DBList
*
*/
class kDBList extends kDBBase implements Iterator, Countable {
// kDBList filter types (then, types are divided into classes)
/**
* Having filter [step1]
*
*/
const HAVING_FILTER = 1;
/**
* Where filter [step1]
*
*/
const WHERE_FILTER = 2;
/**
* Aggregated filter [step1]
*
*/
const AGGREGATE_FILTER = 3;
/**
* System filter [step2, AND]
*
*/
const FLT_SYSTEM = 1;
/**
* User filter [step2, OR]
* @deprecated
*
*/
const FLT_NORMAL = 2;
/**
* User "Search" filter [step2, OR]
*
*/
const FLT_SEARCH = 3;
/**
* User "View Menu" filter [step2, AND]
*
*/
const FLT_VIEW = 4;
/**
* User "Custom" (above grid columns) filter [step2, AND]
*
*/
const FLT_CUSTOM = 5;
/**
* kMultipleFilter AND filter [step3]
*
*/
const FLT_TYPE_AND = 'AND';
/**
* kMultipleFilter OR filter [step3]
*
*/
const FLT_TYPE_OR = 'OR';
/**
* Totals for fields specified in config
*
* @var Array
* @access protected
*/
protected $Totals = Array ();
/**
* Remembers if totals were calculated
*
* @var bool
* @access protected
*/
protected $TotalsCalculated = false;
/**
* List of "ORDER BY" fields
*
* @var Array
* @access protected
*/
protected $OrderFields = Array ();
/**
* Counted total number of records in the query - without pagination (system+user filters)
*
* @var int
* @access protected
*/
protected $RecordsCount = 0;
/**
* Record count with only system filters applied
*
* @var int
* @access protected
*/
protected $NoFilterCount = 0;
/**
* Record count selected to be showed on current page
*
* @var int
* @access protected
*/
protected $SelectedCount = 0;
/**
* Array of selected records
*
* @var Array
* @access public
*/
public $Records;
/**
* Current record index
*
* @var int
* @access protected
*/
protected $CurrentIndex = 0;
/**
* List items per-page
*
* @var int
* @access protected
*/
protected $PerPage;
/**
* Page count in list based on PerPage & RecordsCount attributes
*
* @var int
* @access protected
*/
protected $TotalPages;
/**
* Current page number - used when forming LIMIT clause of SELECT statement
*
* @var int
* @access protected
*/
protected $Page;
/**
* Offset for LIMIT clause, calculated in {@link kDBList::PerPage()}
*
* @var int
* @access protected
*/
protected $Offset;
/**
* WHERE filter objects
*
* @var kMultipleFilter[]
* @access protected
*/
protected $WhereFilter = Array ();
/**
* HAVING filter objects
*
* @var kMultipleFilter[]
* @access protected
*/
protected $HavingFilter = Array ();
/**
* AGGREGATED filter objects
*
* @var kMultipleFilter[]
* @access protected
*/
protected $AggregateFilter = Array ();
/**
* List of "GROUP BY" fields
*
* @var Array
* @access protected
*/
protected $GroupByFields = Array ();
/**
* Remembers if list was queried
*
* @var bool
* @access protected
*/
protected $Queried = false;
/**
* Remembers if list was counted
*
* @var bool
* @access protected
*/
protected $Counted = false;
/**
* Name of the grid, used to display the list
*
* @var string
*/
var $gridName = '';
/**
* Identifies this list as main on the page, that allows to react on "page", "per_page" and "sort_by" parameters from url
*
* @var bool
* @access protected
*/
protected $mainList = false;
/**
* Holds field errors
*
* @var Array
* @access protected
*/
protected $FieldErrors = Array ();
/**
* Creates kDBList
*
* @return kDBList
* @access public
*/
public function __construct()
{
parent::__construct();
$this->OrderFields = Array();
foreach ( $this->getFilterStructure() as $filter_params ) {
$property_name = $filter_params['type'];
$filter_group =& $this->$property_name;
$filter_group[$filter_params['class']] = $this->Application->makeClass(
'kMultipleFilter',
array($filter_params['join_using'])
);
}
$this->PerPage = -1;
}
function setGridName($grid_name)
{
$this->gridName = $grid_name;
}
/**
* Returns information about all possible filter types
*
* @return Array
* @access protected
*/
protected function getFilterStructure()
{
$filters = Array (
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' => 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' => 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;
}
/**
* Adds new or replaces old filter with same name
*
* @param string $name filter name (for internal use)
* @param string $clause where/having clause part (no OR/AND allowed)
* @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
* @access public
*/
public function addFilter($name, $clause, $filter_type = self::WHERE_FILTER, $filter_scope = self::FLT_SYSTEM)
{
$this->getFilterCollection($filter_type, $filter_scope)->addFilter($name, $clause);
}
/**
* Reads filter content
*
* @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 = self::WHERE_FILTER, $filter_scope = self::FLT_SYSTEM)
{
return $this->getFilterCollection($filter_type, $filter_scope)->getFilter($name);
}
/**
* Removes specified filter from filters list
*
* @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
* @access public
*/
public function removeFilter($name, $filter_type = self::WHERE_FILTER, $filter_scope = self::FLT_SYSTEM)
{
$this->getFilterCollection($filter_type, $filter_scope)->removeFilter($name);
}
/**
* Returns filter collection.
*
* @param integer $filter_type Is filter having filter or where filter.
* @param integer $filter_scope Filter subtype: FLT_NORMAL,FLT_SYSTEM,FLT_SEARCH,FLT_VIEW,FLT_CUSTOM.
*
* @return kMultipleFilter
*/
protected function getFilterCollection($filter_type = self::WHERE_FILTER, $filter_scope = self::FLT_SYSTEM)
{
$filter_source = array(
self::WHERE_FILTER => 'WhereFilter',
self::HAVING_FILTER => 'HavingFilter',
self::AGGREGATE_FILTER => 'AggregateFilter'
);
/** @var kMultipleFilter[] $filters */
$property_name = $filter_source[$filter_type];
$filters =& $this->$property_name;
return $filters[$filter_scope];
}
/**
* Clear all filters
*
* @access public
*/
public function clearFilters()
{
foreach ( $this->getFilterStructure() as $filter_params ) {
$property_name = $filter_params['type'];
$filter_group =& $this->$property_name;
$filter_group[$filter_params['class']]->clearFilters();
}
}
/**
* Counts the total number of records base on the query resulted from {@link kDBList::GetSelectSQL()}
*
* The method modifies the query to substitude SELECT part (fields listing) with COUNT(*).
* Special care should be applied when working with lists based on grouped queries, all aggregate function fields
* like SUM(), AVERAGE() etc. should be added to CountedSQL by using {@link kDBList::SetCountedSQL()}
*
* @access protected
*/
protected function CountRecs()
{
$all_sql = $this->GetSelectSQL(true,false);
$sql = $this->getCountSQL($all_sql);
$this->Counted = true;
if( $this->GetGroupClause() )
{
$this->RecordsCount = count( $this->Conn->GetCol($sql) );
}
else
{
$this->RecordsCount = (int)$this->Conn->GetOne($sql);
}
$system_sql = $this->GetSelectSQL(true,true);
if($system_sql == $all_sql) //no need to query the same again
{
$this->NoFilterCount = $this->RecordsCount;
return;
}
$sql = $this->getCountSQL($system_sql);
if( $this->GetGroupClause() )
{
$this->NoFilterCount = count( $this->Conn->GetCol($sql) );
}
else
{
$this->NoFilterCount = (int)$this->Conn->GetOne($sql);
}
}
/**
* Returns record count in list with/without user filters applied
*
* @param bool $with_filters
* @return int
* @access public
*/
public function GetRecordsCount($with_filters = true)
{
if (!$this->Counted) {
$this->CountRecs();
}
return $with_filters ? $this->RecordsCount : $this->NoFilterCount;
}
/**
* Returns record count, that were actually selected
*
* @return int
* @access public
*/
public function GetSelectedCount()
{
$this->Query();
return $this->SelectedCount;
}
/**
* Transforms given query into count query (DISTINCT is also processed)
*
* @param string $sql
* @return string
* @access public
*/
public function getCountSQL($sql)
{
if ( preg_match("/^\s*SELECT\s+DISTINCT(.*?\s)FROM(?!_)/is",$sql,$regs ) )
{
return preg_replace("/^\s*SELECT\s+DISTINCT(.*?\s)FROM(?!_)/is", "SELECT COUNT(DISTINCT ".$regs[1].") AS count FROM", $sql);
}
else
{
return preg_replace("/^\s*SELECT(.*?\s)FROM(?!_)/is", "SELECT COUNT(*) AS count FROM ", $sql);
}
}
/**
* 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;
$q = $this->GetSelectSQL();
//$rs = $this->Conn->SelectLimit($q, $this->PerPage, $this->Offset);
//in case we have not counted records try to select one more item to find out if we have something more than perpage
$limit = $this->Counted ? $this->PerPage : $this->PerPage+1;
$sql = $q.' '.$this->Conn->getLimitClause($this->Offset,$limit);
$this->Records = $this->Conn->Query($sql);
if (!$this->Records && ($this->Page > 1)) {
if ( $this->Application->isAdmin ) {
// no records & page > 1, try to reset to 1st page (works only when list in not counted before)
$this->Application->StoreVar($this->getPrefixSpecial() . '_Page', 1, true);
$this->SetPage(1);
$this->Query($force);
}
else if ( $this->Application->HttpQuery->refererIsOurSite() ) {
// no records & page > 1, try to reset to last page
$this->SetPage($this->GetTotalPages());
$this->Query($force);
}
else {
// no records & page > 1, show 404 page
trigger_error('Unknown page <strong>' . $this->Page . '</strong> in <strong>' . $this->getPrefixSpecial() . '</strong> list, leading to "404 Not Found"', E_USER_NOTICE);
$this->Application->UrlManager->show404();
}
}
$this->SelectedCount = count($this->Records);
if (!$this->Counted) $this->RecordsCount = $this->SelectedCount;
if (!$this->Counted && $this->SelectedCount > $this->PerPage && $this->PerPage != -1) $this->SelectedCount--;
if ($this->Records === false) {
//handle errors here
return false;
}
$this->Queried = true;
$this->Application->HandleEvent(new kEvent($this->getPrefixSpecial() . ':OnAfterListQuery'));
return true;
}
/**
* Adds one more record to list virtually and updates all counters
*
* @param Array $record
* @access public
*/
public function addRecord($record)
{
$this->Records[] = $record;
$this->SelectedCount++;
$this->RecordsCount++;
}
/**
* Calculates totals based on config
*
* @access protected
*/
protected function CalculateTotals()
{
$fields = Array();
$this->Totals = Array();
if ($this->gridName) {
$grids = $this->Application->getUnitOption($this->Prefix, 'Grids');
$grid_fields = $grids[$this->gridName]['Fields'];
}
else {
$grid_fields = $this->Fields;
}
foreach ($grid_fields as $field_name => $field_options) {
if ($this->gridName && array_key_exists('totals', $field_options) && $field_options['totals']) {
$totals = $field_options['totals'];
}
elseif (array_key_exists('totals', $this->Fields[$field_name]) && $this->Fields[$field_name]['totals']) {
$totals = $this->Fields[$field_name]['totals'];
}
else {
continue;
}
$calculated_field = array_key_exists($field_name, $this->CalculatedFields) && array_key_exists($field_name, $this->VirtualFields);
$db_field = !array_key_exists($field_name, $this->VirtualFields);
if ($calculated_field || $db_field) {
$field_expression = $calculated_field ? $this->CalculatedFields[$field_name] : '`'.$this->TableName.'`.`'.$field_name.'`';
$fields[$field_name] = $totals.'('.$field_expression.') AS '.$field_name.'_'.$totals;
}
}
if (!$fields) {
return ;
}
$fields = str_replace('%1$s', $this->TableName, implode(', ', $fields));
$sql = $this->GetSelectSQL(true, false, $fields);
if ( preg_match("/DISTINCT(.*?\s)FROM(?!_)/is",$sql,$regs ) )
{
$sql = preg_replace("/^\s*SELECT DISTINCT(.*?\s)FROM(?!_)/is", 'SELECT '.$fields.' FROM', $sql);
}
else
{
$sql = preg_replace("/^\s*SELECT(.*?\s)FROM(?!_)/is", 'SELECT '.$fields.' FROM ', $sql);
}
$totals = $this->Conn->Query($sql);
foreach($totals as $totals_row)
{
foreach($totals_row as $total_field => $field_value)
{
if(!isset($this->Totals[$total_field])) $this->Totals[$total_field] = 0;
$this->Totals[$total_field] += $field_value;
}
}
$this->TotalsCalculated = true;
}
/**
* Returns previously calculated total (not formatted)
*
* @param string $field
* @param string $total_function
* @return float
* @access public
*/
public function getTotal($field, $total_function)
{
if (!$this->TotalsCalculated) {
$this->CalculateTotals();
}
return $this->Totals[$field . '_' . $total_function];
}
function setTotal($field, $total_function, $value)
{
$this->Totals[$field . '_' . $total_function] = $value;
}
function getTotalFunction($field)
{
if ($this->gridName) {
$grids = $this->Application->getUnitOption($this->Prefix, 'Grids');
$field_options = $grids[$this->gridName]['Fields'][$field];
}
else {
$field_options = $this->Fields[$field];
}
if ($this->gridName && array_key_exists('totals', $field_options) && $field_options['totals']) {
return $field_options['totals'];
}
elseif (array_key_exists('totals', $this->Fields[$field]) && $this->Fields[$field]['totals']) {
return $this->Fields[$field]['totals'];
}
return false;
}
/**
* Returns previously calculated total (formatted)
*
* @param string $field Field.
* @param string $total_function Total function.
* @param string $format Format.
*
* @return float
*/
public function GetFormattedTotal($field, $total_function, $format = null)
{
$res = $this->getTotal($field, $total_function);
$formatter_class = $this->GetFieldOption($field, 'formatter');
if ( $formatter_class ) {
/** @var kFormatter $formatter */
$formatter = $this->Application->recallObject($formatter_class);
$res = $formatter->Format($res, $field, $this, $format);
}
return $res;
}
/**
* Builds full select query except for LIMIT clause
*
* @param bool $for_counting
* @param bool $system_filters_only
* @param string $keep_clause
* @return string
* @access public
*/
public function GetSelectSQL($for_counting = false, $system_filters_only = false, $keep_clause = '')
{
$q = parent::GetSelectSQL($this->SelectClause);
$q = !$for_counting ? $this->addCalculatedFields($q, 0) : str_replace('%2$s', '', $q);
$where = $this->GetWhereClause($for_counting,$system_filters_only);
$having = $this->GetHavingClause($for_counting,$system_filters_only);
$order = $this->GetOrderClause();
$group = $this->GetGroupClause();
if ( $for_counting ) {
$usage_string = $where . '|' . $having . '|' . $order . '|' . $group . '|' . $keep_clause;
$optimizer = new LeftJoinOptimizer($q, $this->replaceModePrefix( str_replace('%1$s', $this->TableName, $usage_string) ));
$q = $optimizer->simplify();
}
if (!empty($where)) $q .= ' WHERE ' . $where;
if (!empty($group)) $q .= ' GROUP BY ' . $group;
if (!empty($having)) $q .= ' HAVING ' . $having;
if ( !$for_counting && !empty($order) ) $q .= ' ORDER BY ' . $order;
return $this->replaceModePrefix( str_replace('%1$s', $this->TableName, $q) );
}
/**
* Replaces all calculated field occurrences with their associated expressions
*
* @param string $clause where clause to extract calculated fields from
* @param int $aggregated 0 - having + aggregated, 1 - having only, 2 - aggregated only
* @param bool $replace_table
* @return string
* @access public
*/
public function extractCalculatedFields($clause, $aggregated = 1, $replace_table = false)
{
$fields = $this->getCalculatedFields($aggregated);
if ( is_array($fields) && count($fields) > 0 ) {
$fields = str_replace('%2$s', $this->Application->GetVar('m_lang'), $fields);
foreach ($fields as $field_name => $field_expression) {
$clause = preg_replace('/(\\(+)[(,` ]*' . $field_name . '[` ]{1}/', '\1 (' . $field_expression . ') ', $clause);
$clause = preg_replace('/[,` ]{1}' . $field_name . '[` ]{1}/', ' (' . $field_expression . ') ', $clause);
}
}
return $replace_table ? str_replace('%1$s', $this->TableName, $clause) : $clause;
}
/**
* Returns WHERE clause of the query
*
* @param bool $for_counting merge where filters with having filters + replace field names for having fields with their values
* @param bool $system_filters_only
* @return string
* @access private
*/
private function GetWhereClause($for_counting=false,$system_filters_only=false)
{
/** @var kMultipleFilter $where */
$where = $this->Application->makeClass('kMultipleFilter');
$where->addFilter(
'system_where',
$this->extractCalculatedFields($this->WhereFilter[self::FLT_SYSTEM]->getSQL())
);
if (!$system_filters_only) {
$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[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[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[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 );
}
// CUSTOM
}
if( $for_counting ) // add system_having and view_having to where
{
$where->addFilter('system_having', $this->extractCalculatedFields($this->HavingFilter[kDBList::FLT_SYSTEM]->getSQL()) );
if (!$system_filters_only) $where->addFilter('view_having', $this->extractCalculatedFields( $this->HavingFilter[kDBList::FLT_VIEW]->getSQL() ) );
}
return $where->getSQL();
}
/**
* Returns HAVING clause of the query
*
* @param bool $for_counting don't return having filter in case if this is counting sql
* @param bool $system_filters_only return only system having filters
* @param int $aggregated 0 - aggregated and having, 1 - having only, 2 - aggregated only
* @return string
* @access private
*/
private function GetHavingClause($for_counting=false, $system_filters_only=false, $aggregated = 0)
{
if ($for_counting) {
/** @var kMultipleFilter $aggregate_filter */
$aggregate_filter = $this->Application->makeClass('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]);
}
return $this->extractCalculatedFields($aggregate_filter->getSQL(), 2);
}
/** @var kMultipleFilter $having */
$having = $this->Application->makeClass('kMultipleFilter');
$having->addFilter('system_having', $this->HavingFilter[kDBList::FLT_SYSTEM] );
if ($aggregated == 0) {
if (!$system_filters_only) {
$having->addFilter('view_aggregated', $this->AggregateFilter[kDBList::FLT_VIEW] );
}
$having->addFilter('system_aggregated', $this->AggregateFilter[kDBList::FLT_SYSTEM]);
}
if (!$system_filters_only) {
$having->addFilter('view_having', $this->HavingFilter[kDBList::FLT_VIEW] );
$having->addFilter('custom_having', $this->HavingFilter[kDBList::FLT_CUSTOM] );
$search_w = $this->WhereFilter[kDBList::FLT_SEARCH]->getSQL();
if (!$search_w) {
$having->addFilter('search_having', $this->HavingFilter[kDBList::FLT_SEARCH] );
}
}
return $having->getSQL();
}
/**
* Returns GROUP BY clause of the query
*
* @return string
* @access protected
*/
protected function GetGroupClause()
{
return $this->GroupByFields ? implode(',', $this->GroupByFields) : '';
}
/**
* Adds new group by field
*
* @param string $field
* @access public
*/
public function AddGroupByField($field)
{
$this->GroupByFields[$field] = $field;
}
/**
* Removes group by field added before
*
* @param string $field
* @access public
*/
public function RemoveGroupByField($field)
{
unset($this->GroupByFields[$field]);
}
/**
* Adds order field to ORDER BY clause
*
* @param string $field Field name
* @param string $direction Direction of ordering (asc|desc)
* @param bool $is_expression this is expression, that should not be escapted by "`" symbols
* @return int
* @access public
*/
public function AddOrderField($field, $direction = 'asc', $is_expression = false)
{
// original multilanguage field - convert to current lang field
$formatter = isset($this->Fields[$field]['formatter']) ? $this->Fields[$field]['formatter'] : false;
if ($formatter == 'kMultiLanguage' && !isset($this->Fields[$field]['master_field'])) {
// for now kMultiLanguage formatter is only supported for real (non-virtual) fields
$is_expression = true;
$field = $this->getMLSortField($field);
}
if (!isset($this->Fields[$field]) && $field != 'RAND()' && !$is_expression) {
trigger_error('<span class="debug_error">Incorrect sorting</span> defined (field = <b>'.$field.'</b>; direction = <b>'.$direction.'</b>) in config for prefix <b>'.$this->Prefix.'</b>', E_USER_NOTICE);
}
$this->OrderFields[] = Array($field, $direction, $is_expression);
return count($this->OrderFields) - 1;
}
/**
* Sets new order fields, replacing existing ones
*
* @param Array $order_fields
* @return void
* @access public
*/
public function setOrderFields($order_fields)
{
$this->OrderFields = $order_fields;
}
/**
* Changes sorting direction for a given sorting field index
*
* @param int $field_index
* @param string $direction
* @return void
* @access public
*/
public function changeOrderDirection($field_index, $direction)
{
if ( !isset($this->OrderFields[$field_index]) ) {
return;
}
$this->OrderFields[$field_index][1] = $direction;
}
/**
* Returns expression, used to sort given multilingual field
*
* @param string $field
* @return string
*/
function getMLSortField($field)
{
$table_name = '`' . $this->TableName . '`';
$lang = $this->Application->GetVar('m_lang');
$primary_lang = $this->Application->GetDefaultLanguageId();
$ret = 'IF(COALESCE(%1$s.l' . $lang . '_' . $field . ', ""), %1$s.l' . $lang . '_' . $field . ', %1$s.l' . $primary_lang . '_' . $field . ')';
return sprintf($ret, $table_name);
}
/**
* Removes all order fields
*
* @access public
*/
public function ClearOrderFields()
{
$this->OrderFields = Array();
}
/**
* Returns ORDER BY Clause of the query
*
* The method builds order by clause by iterating {@link kDBList::OrderFields} array and concatenating it.
*
* @return string
* @access private
*/
private function GetOrderClause()
{
$ret = '';
foreach ($this->OrderFields as $field) {
$name = $field[0];
$ret .= isset($this->Fields[$name]) && !isset($this->VirtualFields[$name]) ? '`'.$this->TableName.'`.' : '';
if ($field[0] == 'RAND()' || $field[2]) {
$ret .= $field[0].' '.$field[1].',';
}
else {
$ret .= (strpos($field[0], '.') === false ? '`'.$field[0] . '`' : $field[0]) . ' ' . $field[1] . ',';
}
}
$ret = rtrim($ret, ',');
return $ret;
}
/**
* Returns order field name in given position
*
* @param int $pos
* @param bool $no_default
* @return string
* @access public
*/
public function GetOrderField($pos = NULL, $no_default = false)
{
if ( !(isset($this->OrderFields[$pos]) && $this->OrderFields[$pos]) && !$no_default ) {
$pos = 0;
}
if ( isset($this->OrderFields[$pos][0]) ) {
$field = $this->OrderFields[$pos][0];
$lang = $this->Application->GetVar('m_lang');
if ( preg_match('/^IF\(COALESCE\(.*?\.(l' . $lang . '_.*?), ""\),/', $field, $regs) ) {
// undo result of kDBList::getMLSortField method
return $regs[1];
}
return $field;
}
return '';
}
/**
* Returns list order fields
*
* @return Array
* @access public
*/
public function getOrderFields()
{
return $this->OrderFields;
}
/**
* Returns order field direction in given position
*
* @param int $pos
* @param bool $no_default
* @return string
* @access public
*/
public function GetOrderDirection($pos = NULL, $no_default = false)
{
if ( !(isset($this->OrderFields[$pos]) && $this->OrderFields[$pos]) && !$no_default ) {
$pos = 0;
}
return isset($this->OrderFields[$pos][1]) ? $this->OrderFields[$pos][1] : '';
}
/**
* Returns ID of currently processed record
*
* @return int
* @access public
*/
public function GetID()
{
return $this->Queried ? $this->GetDBField($this->IDField) : null;
}
/**
* Allows kDBTagProcessor.SectionTitle to detect if it's editing or new item creation
*
* @return bool
* @access public
*/
public function IsNewItem()
{
// no such thing as NewItem for lists :)
return false;
}
/**
* Return unformatted field value
*
* @param string $name
* @return string
* @access public
*/
public function GetDBField($name)
{
$row =& $this->getCurrentRecord();
if (defined('DEBUG_MODE') && DEBUG_MODE && $this->Queried && !array_key_exists($name, $row)) {
if ( $this->Application->isDebugMode() ) {
$this->Application->Debugger->appendTrace();
}
trigger_error('Field "<strong>' . $name . '</strong>" doesn\'t exist in prefix <strong>' . $this->getPrefixSpecial() . '</strong>', E_USER_WARNING);
return 'NO SUCH FIELD';
}
// return "null" for missing fields, because formatter require such behaviour !
return array_key_exists($name, $row) ? $row[$name] : null;
}
/**
* Checks if requested field is present after database query
*
* @param string $name
* @return bool
* @access public
*/
public function HasField($name)
{
$row =& $this->getCurrentRecord();
return isset($row[$name]);
}
/**
* Returns current record fields
*
* @return Array
* @access public
*/
public function GetFieldValues()
{
$record =& $this->getCurrentRecord();
return $record;
}
/**
* Returns current record from list
*
* @param int $offset Offset relative to current record index
* @return Array
* @access public
*/
public function &getCurrentRecord($offset = 0)
{
$record_index = $this->CurrentIndex + $offset;
if ($record_index >=0 && $record_index < $this->SelectedCount) {
return $this->Records[$record_index];
}
$false = false;
return $false;
}
/**
* Goes to record with given index
*
* @param int $index
* @access public
*/
public function GoIndex($index)
{
$this->CurrentIndex = $index;
}
/**
* Goes to first record
*
* @access public
*/
public function GoFirst()
{
$this->CurrentIndex = 0;
}
/**
* Goes to next record
*
* @access public
*/
public function GoNext()
{
$this->CurrentIndex++;
}
/**
* Goes to previous record
*
* @access public
*/
public function GoPrev()
{
if ($this->CurrentIndex>0) {
$this->CurrentIndex--;
}
}
/**
* Checks if there is no end of list
*
* @return bool
* @access public
*/
public function EOL()
{
return ($this->CurrentIndex >= $this->SelectedCount);
}
/**
* Returns total page count based on list per-page
*
* @return int
* @access public
*/
public function GetTotalPages()
{
if ( !$this->Counted ) {
$this->CountRecs();
}
if ( $this->PerPage == -1 ) {
return 1;
}
$integer_part = ($this->RecordsCount - ($this->RecordsCount % $this->PerPage)) / $this->PerPage;
$reminder = ($this->RecordsCount % $this->PerPage) != 0; // adds 1 if there is a reminder
$this->TotalPages = $integer_part + $reminder;
return $this->TotalPages;
}
/**
* Sets number of records to query per page
*
* @param int $per_page Number of records to display per page
* @access public
*/
public function SetPerPage($per_page)
{
$this->PerPage = $per_page;
}
/**
* Returns records per page count
*
* @param bool $in_fact
* @return int
* @access public
*/
public function GetPerPage($in_fact = false)
{
if ($in_fact) {
return $this->PerPage;
}
return $this->PerPage == -1 ? $this->RecordsCount : $this->PerPage;
}
/**
* Sets current page in list
*
* @param int $page
* @access public
*/
public function SetPage($page)
{
if ($this->PerPage == -1) {
$this->Page = 1;
return;
}
if ($page < 1) $page = 1;
$this->Offset = ($page-1)*$this->PerPage;
if ($this->Counted && $this->Offset > $this->RecordsCount) {
$this->SetPage(1);
}
else {
$this->Page = $page;
}
//$this->GoFirst();
}
/**
* Returns current list page
*
* @return int
* @access public
*/
public function GetPage()
{
return $this->Page;
}
/**
* Sets list query offset
*
* @param int $offset
* @access public
*/
public function SetOffset($offset)
{
$this->Offset = $offset;
}
/**
* Gets list query offset
*
* @return int
* @access public
*/
public function GetOffset()
{
return $this->Offset;
}
/**
* Sets current item field value (doesn't apply formatting)
*
* @param string $name Name of the field
* @param mixed $value Value to set the field to
* @access public
*/
public function SetDBField($name,$value)
{
$this->Records[$this->CurrentIndex][$name] = $value;
}
/**
* Apply where clause, that links this object to it's parent item
*
* @param string $special
* @access public
*/
public function linkToParent($special)
{
$parent_prefix = $this->Application->getUnitOption($this->Prefix, 'ParentPrefix');
if ($parent_prefix) {
$parent_table_key = $this->Application->getUnitOption($this->Prefix, 'ParentTableKey');
if (is_array($parent_table_key)) $parent_table_key = getArrayValue($parent_table_key, $parent_prefix);
$foreign_key_field = $this->Application->getUnitOption($this->Prefix, 'ForeignKey');
if (is_array($foreign_key_field)) $foreign_key_field = getArrayValue($foreign_key_field, $parent_prefix);
if (!$parent_table_key || !$foreign_key_field) {
return ;
}
/** @var kDBItem $parent_object */
$parent_object = $this->Application->recallObject($parent_prefix.'.'.$special);
if (!$parent_object->isLoaded()) {
$this->addFilter('parent_filter', 'FALSE');
trigger_error('Parent ID not found (prefix: "<strong>' . rtrim($parent_prefix.'.'.$special, '.') . '</strong>"; sub-prefix: "<strong>' . $this->getPrefixSpecial() . '</strong>")', E_USER_NOTICE);
return ;
}
// only for list in this case
$parent_id = $parent_object->GetDBField($parent_table_key);
$this->addFilter('parent_filter', '`' . $this->TableName . '`.`' . $foreign_key_field . '` = ' . $this->Conn->qstr($parent_id));
}
}
/**
* Returns true if list was queried (same name as for kDBItem for easy usage)
*
* @return bool
* @access public
*/
public function isLoaded()
{
return $this->Queried && !$this->EOL();
}
/**
* Returns specified field value from all selected rows.
* Don't affect current record index
*
* @param string $field
* @param bool $formatted
* @param string $format
* @return Array
* @access public
*/
public function GetCol($field, $formatted = false, $format = null)
{
$i = 0;
$ret = Array ();
if ($formatted && array_key_exists('formatter', $this->Fields[$field])) {
/** @var kFormatter $formatter */
$formatter = $this->Application->recallObject($this->Fields[$field]['formatter']);
while ($i < $this->SelectedCount) {
$ret[] = $formatter->Format($this->Records[$i][$field], $field, $this, $format);
$i++;
}
}
else {
while ($i < $this->SelectedCount) {
$ret[] = $this->Records[$i][$field];
$i++;
}
}
return $ret;
}
/**
* Set's field error, if pseudo passed not found then create it with message text supplied.
* Don't overwrite existing pseudo translation.
*
* @param string $field
* @param string $pseudo
* @param string $error_label
* @param Array $error_params
* @return bool
* @access public
* @see kSearchHelper::processRangeField()
* @see kDateFormatter::Parse()
*/
public function SetError($field, $pseudo, $error_label = null, $error_params = null)
{
$error_field = isset($this->Fields[$field]['error_field']) ? $this->Fields[$field]['error_field'] : $field;
$this->FieldErrors[$error_field]['pseudo'] = $pseudo;
$var_name = $this->getPrefixSpecial() . '_' . $field . '_error';
$previous_pseudo = $this->Application->RecallVar($var_name);
if ( $previous_pseudo ) {
// don't set more then one error on field
return false;
}
$this->Application->StoreVar($var_name, $pseudo);
return true;
}
/**
* Returns error pseudo
*
* @param string $field
* @return string
* @access public
* @see kSearchHelper::processRangeField()
*/
public function GetErrorPseudo($field)
{
if ( !isset($this->FieldErrors[$field]) ) {
return '';
}
return isset($this->FieldErrors[$field]['pseudo']) ? $this->FieldErrors[$field]['pseudo'] : '';
}
/**
* Removes error on field
*
* @param string $field
* @access public
*/
public function RemoveError($field)
{
unset( $this->FieldErrors[$field] );
}
/**
* Group list records by header, saves internal order in group
*
* @param string $heading_field
* @access public
*/
public function groupRecords($heading_field)
{
$i = 0;
$sorted = Array ();
while ($i < $this->SelectedCount) {
$sorted[ $this->Records[$i][$heading_field] ][] = $this->Records[$i];
$i++;
}
$this->Records = Array ();
foreach ($sorted as $heading => $heading_records) {
$this->Records = array_merge_recursive($this->Records, $heading_records);
}
}
/**
* Reset list (use for requering purposes)
*
* @access public
*/
public function reset()
{
$this->Counted = false;
$this->clearFilters();
$this->ClearOrderFields();
}
/**
* Checks if list was counted
*
* @return bool
* @access public
*/
public function isCounted()
{
return $this->Counted;
}
/**
* Tells, that given list is main
*
* @return bool
* @access public
*/
public function isMainList()
{
return $this->mainList;
}
/**
* Makes given list as main
*
* @access public
*/
public function becameMain()
{
$this->mainList = true;
}
/**
* Moves recordset pointer to first element
*
* @return void
* @access public
* @implements Iterator::rewind
*/
public function rewind()
{
$this->Query();
$this->GoFirst();
}
/**
* Returns value at current position
*
* @return mixed
* @access public
* @implements Iterator::current
*/
function current()
{
return $this->getCurrentRecord();
}
/**
* Returns key at current position
*
* @return mixed
* @access public
* @implements Iterator::key
*/
function key()
{
return $this->CurrentIndex;
}
/**
* Moves recordset pointer to next position
*
* @return void
* @access public
* @implements Iterator::next
*/
function next()
{
$this->GoNext();
}
/**
* Detects if current position is within recordset bounds
*
* @return bool
* @access public
* @implements Iterator::valid
*/
public function valid()
{
return !$this->EOL();
}
/**
* Counts recordset rows
*
* @return int
* @access public
* @implements Countable::count
*/
public function count()
{
return $this->SelectedCount;
}
}
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
+ * @throws RuntimeException When failed to parse a LEFT JOIN expression.
*/
private function parseJoins()
{
- if ( !preg_match_all('/LEFT\s+JOIN\s+(.*?|.*?\s+AS\s+.*?|.*?\s+.*?)\s+ON\s+(.*?\n|.*?$)/si', $this->sql, $regs) ) {
- $this->joins = Array ();
+ $joins_found = preg_match_all(
+ '/LEFT\s+JOIN\s+(.*?|.*?\s+AS\s+.*?|.*?\s+.*?)\s+ON\s+(.*?\n|.*?$)/si',
+ $this->sql,
+ $regs
+ );
+
+ if ( !$joins_found ) {
+ $this->joins = array();
+
+ return;
}
- // 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];
+ // Get all LEFT JOIN clause info from sql (without filters).
+ foreach ( $regs[1] as $index => $join_expression ) {
+ // Format (without quotes): "tbl_name [PARTITION (partition_names)] [[AS] alias] [index_hint_list]".
+ $join_expression_parsed = preg_match(
+ '/([\S]+)(\s*PARTITION\s*\([^)]+\))?(\s*(AS\s*)?([\S]+))?((USE|IGNORE|FORCE)\s+.*)?/',
+ $join_expression,
+ $match_parts
+ );
+
+ if ( !$join_expression_parsed ) {
+ throw new RuntimeException('Unable to parse join expression: ' . $join_expression);
+ }
+
+ $joined_table_name = $match_parts[1];
+ $table_alias = isset($match_parts[5]) && $match_parts[5] !== '' ? $match_parts[5] : $joined_table_name;
- $this->joins[$table_alias] = Array (
- 'table' => $match_parts[0],
+ $this->joins[$table_alias] = array(
+ 'table' => $joined_table_name,
'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('/\b(`' . $escaped_alias . '`|' . $escaped_alias . ')\./s', $string);
}
}
Index: branches/5.2.x/core/kernel/db/db_connection.php
===================================================================
--- branches/5.2.x/core/kernel/db/db_connection.php (revision 16757)
+++ branches/5.2.x/core/kernel/db/db_connection.php (revision 16758)
@@ -1,1449 +1,1451 @@
<?php
/**
* @version $Id$
* @package In-Portal
* @copyright Copyright (C) 1997 - 2009 Intechnic. All rights reserved.
* @license GNU/GPL
* In-Portal is Open Source software.
* This means that this software may have been modified pursuant
* the GNU General Public License, and as distributed it includes
* or is derivative of works licensed under the GNU General Public License
* or other free or open source software licenses.
* See http://www.in-portal.org/license for copyright notices and details.
*/
defined('FULL_PATH') or die('restricted access!');
/**
* Multi database connection class
*
*/
class kDBConnection extends kBase implements IDBConnection {
/**
* Created connection handle
*
* @var mysqli
* @access protected
*/
protected $connectionID;
/**
* Remembers, that database connection was opened successfully
*
* @var bool
* @access public
*/
public $connectionOpened = false;
/**
* Connection parameters, that were used
*
* @var Array
* @access protected
*/
protected $connectionParams = Array ('host' => '', 'user' => '', 'pass' => '', 'db' => '');
/**
* Index of database server
*
* @var int
* @access protected
*/
protected $serverIndex = 0;
/**
* Handle of currently processed recordset
*
* @var mysqli_result
* @access protected
*/
protected $queryID = null;
/**
* Function to handle sql errors
*
* @var callable
*/
protected $errorHandler = '';
/**
* Error code
*
* @var int
* @access protected
*/
protected $errorCode = 0;
/**
* Error message
*
* @var string
* @access protected
*/
protected $errorMessage = '';
/**
* Defines if database connection
* operations should generate debug
* information
*
* @var bool
* @access public
*/
public $debugMode = false;
/**
* Save query execution statistics
*
* @var bool
* @access protected
*/
protected $_captureStatistics = false;
/**
* Last query to database
*
* @var string
* @access public
*/
public $lastQuery = '';
/**
* Total processed queries count
*
* @var int
* @access protected
*/
protected $_queryCount = 0;
/**
* Total time, used for serving queries
*
* @var Array
* @access protected
*/
protected $_queryTime = 0;
/**
* Indicates, that next database query could be cached, when memory caching is enabled
*
* @var bool
* @access public
*/
public $nextQueryCachable = false;
/**
* The "no debugging" state of the SQL queries.
*
* @var boolean
*/
public $noDebuggingState = false;
/**
* For backwards compatibility with kDBLoadBalancer class
*
* @var bool
* @access public
*/
public $nextQueryFromMaster = false;
/**
* Initializes connection class with
* db type to used in future
*
* @param string $db_type
* @param string $error_handler
* @param int $server_index
* @access public
*/
public function __construct($db_type, $error_handler = '', $server_index = 0)
{
if ( class_exists('kApplication') ) {
// prevents "Fatal Error" on 2nd installation step (when database is empty)
parent::__construct();
}
$this->serverIndex = $server_index;
if ( !$error_handler ) {
$this->setErrorHandler(array(&$this, 'handleError'));
}
else {
$this->setErrorHandler($error_handler);
}
$this->_captureStatistics = defined('DBG_CAPTURE_STATISTICS') && DBG_CAPTURE_STATISTICS && !(defined('ADMIN') && ADMIN);
}
/**
* Set's custom error
*
* @param int $code
* @param string $msg
* @access protected
*/
protected function setError($code, $msg)
{
$this->errorCode = $code;
$this->errorMessage = $msg;
}
/**
* Checks if previous query execution raised an error.
*
* @return bool
* @access public
*/
public function hasError()
{
return $this->errorCode != 0;
}
/**
* Try to connect to database server using specified parameters and set database to $db if connection made.
*
* @param string $host
* @param string $user
* @param string $pass
* @param string $db
* @param bool $retry
*
* @return bool
* @access public
* @throws RuntimeException When connection failed.
*/
public function Connect($host, $user, $pass, $db, $retry = false)
{
$this->connectionParams = Array ('host' => $host, 'user' => $user, 'pass' => $pass, 'db' => $db);
$this->setError(0, ''); // reset error
$this->connectionID = mysqli_connect($host, $user, $pass, $db);
$this->errorCode = mysqli_connect_errno();
if ( is_object($this->connectionID) ) {
if ( defined('DBG_SQL_MODE') ) {
$this->Query('SET SQL_MODE = "' . DBG_SQL_MODE . '"');
}
if ( defined('SQL_COLLATION') && defined('SQL_CHARSET') ) {
$this->Query('SET NAMES \'' . SQL_CHARSET . '\' COLLATE \'' . SQL_COLLATION . '\'');
}
if ( !$this->hasError() ) {
$this->connectionOpened = true;
return true;
}
}
$this->errorMessage = mysqli_connect_error();
$error_msg = 'Database connection failed, please check your connection settings.<br/>Error (' . $this->errorCode . '): ' . $this->errorMessage;
if ( (defined('IS_INSTALL') && IS_INSTALL) || $retry ) {
trigger_error($error_msg, E_USER_WARNING);
}
else {
$this->Application->redirectToMaintenance();
throw new RuntimeException($error_msg);
}
$this->connectionOpened = false;
return false;
}
/**
* Checks if connection to database is opened.
*
* @return bool
* @access public
*/
public function connectionOpened()
{
return $this->connectionOpened;
}
/**
* Setups the connection according given configuration.
*
* @param Array $config
* @return bool
* @access public
*/
public function setup($config)
{
if ( is_object($this->Application) ) {
$this->debugMode = $this->Application->isDebugMode();
}
return $this->Connect(
$config['Database']['DBHost'],
$config['Database']['DBUser'],
$config['Database']['DBUserPassword'],
$config['Database']['DBName']
);
}
/**
* Performs 3 reconnect attempts in case if connection to a DB was lost in the middle of script run (e.g. server restart)
*
* @return bool
* @access protected
*/
protected function ReConnect()
{
$retry_count = 0;
$connected = false;
$this->connectionID->close();
while ( $retry_count < 3 ) {
sleep(5); // wait 5 seconds before each reconnect attempt
$connected = $this->Connect(
$this->connectionParams['host'],
$this->connectionParams['user'],
$this->connectionParams['pass'],
$this->connectionParams['db'],
true
);
if ( $connected ) {
break;
}
$retry_count++;
}
return $connected;
}
/**
* Shows error message from previous operation
* if it failed
*
* @param string $sql
* @param string $key_field
* @param boolean|null $no_debug
* @return bool
* @access protected
*/
protected function showError($sql = '', $key_field = null, $no_debug = null)
{
static $retry_count = 0;
if ( $no_debug === null ) {
$no_debug = $this->noDebuggingState;
}
if ( !is_object($this->connectionID) ) {
// no connection while doing mysql_query
$this->errorCode = mysqli_connect_errno();
if ( $this->hasError() ) {
$this->errorMessage = mysqli_connect_error();
$ret = $this->callErrorHandler($sql);
if (!$ret) {
exit;
}
}
return false;
}
// checking if there was an error during last mysql_query
$this->errorCode = $this->connectionID->errno;
if ( $this->hasError() ) {
$this->errorMessage = $this->connectionID->error;
$ret = $this->callErrorHandler($sql);
if ( ($this->errorCode == 2006 || $this->errorCode == 2013) && ($retry_count < 3) ) {
// #2006 - MySQL server has gone away
// #2013 - Lost connection to MySQL server during query
$retry_count++;
if ( $this->ReConnect() ) {
return $this->Query($sql, $key_field, $no_debug);
}
}
if (!$ret) {
exit;
}
}
else {
$retry_count = 0;
}
return false;
}
/**
* Sends db error to a predefined error handler
*
* @param $sql
* @return bool
* @access protected
*/
protected function callErrorHandler($sql)
{
return call_user_func($this->errorHandler, $this->errorCode, $this->errorMessage, $sql);
}
/**
* Default error handler for sql errors
*
* @param int $code
* @param string $msg
* @param string $sql
* @return bool
* @access public
*/
public function handleError($code, $msg, $sql)
{
echo '<strong>Processing SQL</strong>: ' . $sql . '<br/>';
echo '<strong>Error (' . $code . '):</strong> ' . $msg . '<br/>';
return false;
}
/**
* Returns first field of first line of recordset if query ok or false otherwise.
*
* @param string $sql
* @param int $offset
* @return string
* @access public
*/
public function GetOne($sql, $offset = 0)
{
$row = $this->GetRow($sql, $offset);
if ( !$row ) {
return false;
}
return array_shift($row);
}
/**
* Returns first row of recordset if query ok, false otherwise.
*
* @param string $sql
* @param int $offset
* @return Array
* @access public
*/
public function GetRow($sql, $offset = 0)
{
$sql .= ' ' . $this->getLimitClause($offset, 1);
$ret = $this->Query($sql);
if ( !$ret ) {
return false;
}
return array_shift($ret);
}
/**
* Returns 1st column of recordset as one-dimensional array or false otherwise.
*
* Optional parameter $key_field can be used to set field name to be used as resulting array key.
*
* @param string $sql
* @param string $key_field
* @return Array
* @access public
*/
public function GetCol($sql, $key_field = null)
{
$rows = $this->Query($sql);
if ( !$rows ) {
return $rows;
}
$i = 0;
$row_count = count($rows);
$ret = Array ();
if ( isset($key_field) ) {
while ( $i < $row_count ) {
$ret[$rows[$i][$key_field]] = array_shift($rows[$i]);
$i++;
}
}
else {
while ( $i < $row_count ) {
$ret[] = array_shift($rows[$i]);
$i++;
}
}
return $ret;
}
/**
* Returns iterator for 1st column of a recordset or false in case of error.
*
* Optional parameter $key_field can be used to set field name to be used as resulting array key.
*
* @param string $sql
* @param string $key_field
* @return bool|kMySQLQueryCol
*/
public function GetColIterator($sql, $key_field = null)
{
return $this->GetIterator($sql, $key_field, false, 'kMySQLQueryCol');
}
/**
* Queries db with $sql query supplied and returns rows selected if any, false otherwise.
*
* Optional parameter $key_field allows to set one of the query fields value as key in string array.
*
* @param string $sql
* @param string $key_field
* @param boolean|null $no_debug
* @return Array
* @access public
*/
public function Query($sql, $key_field = null, $no_debug = null)
{
if ( $no_debug === null ) {
$no_debug = $this->noDebuggingState;
}
if ( !$no_debug ) {
$this->_queryCount++;
}
$this->lastQuery = $sql;
// set 1st checkpoint: begin
$start_time = $this->_captureStatistics ? microtime(true) : 0;
// set 1st checkpoint: end
$this->setError(0, ''); // reset error
$this->queryID = $this->connectionID->query($sql);
if ( is_object($this->queryID) ) {
$ret = Array ();
if ( isset($key_field) ) {
while ( $row = $this->queryID->fetch_assoc() ) {
$ret[$row[$key_field]] = $row;
}
}
else {
while ( $row = $this->queryID->fetch_assoc() ) {
$ret[] = $row;
}
}
$this->Destroy();
// set 2nd checkpoint: begin
if ( $this->_captureStatistics ) {
$query_time = microtime(true) - $start_time;
if ( $query_time > DBG_MAX_SQL_TIME && !$no_debug ) {
$this->Application->logSlowQuery($sql, $query_time);
}
$this->_queryTime += $query_time;
}
// set 2nd checkpoint: end
return $ret;
}
else {
// set 2nd checkpoint: begin
if ( $this->_captureStatistics ) {
$this->_queryTime += microtime(true) - $start_time;
}
// set 2nd checkpoint: end
}
return $this->showError($sql, $key_field, $no_debug);
}
/**
* Returns iterator to a recordset, produced from running $sql query.
*
* Queries db with $sql query supplied and returns kMySQLQuery iterator or false in case of error.
* Optional parameter $key_field allows to set one of the query fields value as key in string array.
*
* @param string $sql
* @param string $key_field
* @param boolean|null $no_debug
* @param string $iterator_class
* @return kMySQLQuery|bool
* @access public
*/
public function GetIterator($sql, $key_field = null, $no_debug = null, $iterator_class = 'kMySQLQuery')
{
if ( $no_debug === null ) {
$no_debug = $this->noDebuggingState;
}
if ( !$no_debug ) {
$this->_queryCount++;
}
$this->lastQuery = $sql;
// set 1st checkpoint: begin
$start_time = $this->_captureStatistics ? microtime(true) : 0;
// set 1st checkpoint: end
$this->setError(0, ''); // reset error
$this->queryID = $this->connectionID->query($sql);
if ( is_object($this->queryID) ) {
/** @var kMySQLQuery $ret */
$ret = new $iterator_class($this->queryID, $key_field);
// set 2nd checkpoint: begin
if ( $this->_captureStatistics ) {
$query_time = microtime(true) - $start_time;
if ( $query_time > DBG_MAX_SQL_TIME && !$no_debug ) {
$this->Application->logSlowQuery($sql, $query_time);
}
$this->_queryTime += $query_time;
}
// set 2nd checkpoint: end
return $ret;
}
else {
// set 2nd checkpoint: begin
if ( $this->_captureStatistics ) {
$this->_queryTime += microtime(true) - $start_time;
}
// set 2nd checkpoint: end
}
return $this->showError($sql, $key_field, $no_debug);
}
/**
* Free memory used to hold recordset handle.
*
* @access public
*/
public function Destroy()
{
$this->queryID->free();
unset($this->queryID);
}
/**
* Performs sql query, that will change database content.
*
* @param string $sql
* @return bool
* @access public
*/
public function ChangeQuery($sql)
{
$this->Query($sql);
return !$this->hasError();
}
/**
* Returns auto increment field value from insert like operation if any, zero otherwise.
*
* @return int
* @access public
*/
public function getInsertID()
{
return $this->connectionID->insert_id;
}
/**
* Returns row count affected by last query.
*
* @return int
* @access public
*/
public function getAffectedRows()
{
return $this->connectionID->affected_rows;
}
/**
* Returns LIMIT sql clause part for specific db.
*
* @param int $offset
* @param int $rows
* @return string
* @access public
*/
public function getLimitClause($offset, $rows)
{
if ( !($rows > 0) ) {
return '';
}
return 'LIMIT ' . $offset . ',' . $rows;
}
/**
* If it's a string, adds quotes and backslashes. Otherwise returns as-is.
*
* @param mixed $string
* @return string
* @access public
*/
public function qstr($string)
{
if ( is_null($string) ) {
return 'NULL';
}
# This will also quote numeric values. This should be harmless,
# and protects against weird problems that occur when they really
# _are_ strings such as article titles and string->number->string
# conversion is not 1:1.
return "'" . $this->connectionID->real_escape_string($string) . "'";
}
/**
* Calls "qstr" function for each given array element.
*
* @param Array $array
* @param string $function
* @return Array
*/
public function qstrArray($array, $function = 'qstr')
{
return array_map(Array (&$this, $function), $array);
}
/**
* Escapes string.
*
* @param mixed $string
* @return string
* @access public
*/
public function escape($string)
{
if ( is_null($string) ) {
return 'NULL';
}
$string = $this->connectionID->real_escape_string($string);
// prevent double-escaping of MySQL wildcard symbols ("%" and "_") in case if they were already escaped
return str_replace(Array ('\\\\%', '\\\\_'), Array ('\\%', '\\_'), $string);
}
/**
* Returns last error code occurred.
*
* @return int
* @access public
*/
public function getErrorCode()
{
return $this->errorCode;
}
/**
* Returns last error message.
*
* @return string
* @access public
*/
public function getErrorMsg()
{
return $this->errorMessage;
}
/**
* Performs insert of given data (useful with small number of queries)
* or stores it to perform multiple insert later (useful with large number of queries).
*
* @param Array $fields_hash
* @param string $table
* @param string $type
* @param bool $insert_now
* @return bool
* @access public
*/
public function doInsert($fields_hash, $table, $type = 'INSERT', $insert_now = true)
{
static $value_sqls = Array ();
if ($insert_now) {
$fields_sql = '`' . implode('`,`', array_keys($fields_hash)) . '`';
}
$values_sql = '';
foreach ($fields_hash as $field_name => $field_value) {
$values_sql .= $this->qstr($field_value) . ',';
}
// don't use preg here, as it may fail when string is too long
$value_sqls[] = rtrim($values_sql, ',');
$insert_result = true;
if ($insert_now) {
$insert_count = count($value_sqls);
if (($insert_count > 1) && ($value_sqls[$insert_count - 1] == $value_sqls[$insert_count - 2])) {
// last two records are the same
array_pop($value_sqls);
}
- $sql = strtoupper($type) . ' INTO `' . $table . '` (' . $fields_sql . ') VALUES (' . implode('),(', $value_sqls) . ')';
- $value_sqls = Array (); // reset before query to prevent repeated call from error handler to insert 2 records instead of 1
+ $sql = strtoupper($type) . ' INTO ' . $table . ' (' . $fields_sql . ') VALUES (' . implode('),(', $value_sqls) . ')';
+
+ // Reset before query to prevent repeated call from error handler to insert 2 records instead of 1.
+ $value_sqls = array();
$insert_result = $this->ChangeQuery($sql);
}
return $insert_result;
}
/**
* Update given field values to given record using $key_clause.
*
* @param Array $fields_hash
* @param string $table
* @param string $key_clause
* @return bool
* @access public
*/
public function doUpdate($fields_hash, $table, $key_clause)
{
if (!$fields_hash) return true;
$fields_sql = '';
foreach ($fields_hash as $field_name => $field_value) {
$fields_sql .= '`'.$field_name.'` = ' . $this->qstr($field_value) . ',';
}
// don't use preg here, as it may fail when string is too long
$fields_sql = rtrim($fields_sql, ',');
- $sql = 'UPDATE `'.$table.'` SET '.$fields_sql.' WHERE '.$key_clause;
+ $sql = 'UPDATE ' . $table . ' SET ' . $fields_sql . ' WHERE ' . $key_clause;
return $this->ChangeQuery($sql);
}
/**
* Allows to detect table's presence in database.
*
* @param string $table_name
* @param bool $force
* @return bool
* @access public
*/
public function TableFound($table_name, $force = false)
{
static $table_found = false;
if ( $table_found === false ) {
$table_found = array_flip($this->GetCol('SHOW TABLES'));
}
if ( !preg_match('/^' . preg_quote(TABLE_PREFIX, '/') . '(.*)/', $table_name) ) {
$table_name = TABLE_PREFIX . $table_name;
}
if ( $force ) {
if ( $this->Query('SHOW TABLES LIKE ' . $this->qstr($table_name)) ) {
$table_found[$table_name] = 1;
}
else {
unset($table_found[$table_name]);
}
}
return isset($table_found[$table_name]);
}
/**
* Returns query processing statistics.
*
* @return Array
* @access public
*/
public function getQueryStatistics()
{
return Array ('time' => $this->_queryTime, 'count' => $this->_queryCount);
}
/**
* Get status information from SHOW STATUS in an associative array.
*
* @param string $which
* @return Array
* @access public
*/
public function getStatus($which = '%')
{
$status = Array ();
$records = $this->Query('SHOW STATUS LIKE "' . $which . '"');
foreach ($records as $record) {
$status[ $record['Variable_name'] ] = $record['Value'];
}
return $status;
}
/**
* Get slave replication lag. It will only work if the DB user has the PROCESS privilege.
*
* @return int
* @access public
*/
public function getSlaveLag()
{
try {
$rows = $this->Query('SHOW SLAVE STATUS');
}
catch ( RuntimeException $e ) {
// When "SUPER" or "REPLICATION CLIENT" permission is missing.
return 0;
}
// On the silenced error OR database server isn't configured for a replication.
if ( $rows === false || count($rows) !== 1 ) {
return 0;
}
$row = reset($rows);
// When slave is too busy catching up with a master we'll get a NULL/empty string here.
return is_numeric($row['Seconds_Behind_Master']) ? $row['Seconds_Behind_Master'] : false;
}
/**
* Sets an error handler.
*
* @param callable $error_handler Error handler.
*
* @return void
*/
public function setErrorHandler(callable $error_handler)
{
$this->errorHandler = $error_handler;
}
}
class kDBConnectionDebug extends kDBConnection {
protected $_profileSQLs = false;
/**
* Info about this database connection to show in debugger report
*
* @var string
* @access protected
*/
protected $serverInfoLine = '';
/**
* Initializes connection class with
* db type to used in future
*
* @param string $db_type
* @param string $error_handler
* @param int $server_index
* @access public
*/
public function __construct($db_type, $error_handler = '', $server_index = 0)
{
parent::__construct($db_type, $error_handler, $server_index);
$this->_profileSQLs = defined('DBG_SQL_PROFILE') && DBG_SQL_PROFILE;
}
/**
* Try to connect to database server
* using specified parameters and set
* database to $db if connection made
*
* @param string $host
* @param string $user
* @param string $pass
* @param string $db
* @param bool $force_new
* @param bool $retry
* @return bool
* @access public
*/
public function Connect($host, $user, $pass, $db, $force_new = false, $retry = false)
{
if ( defined('DBG_SQL_SERVERINFO') && DBG_SQL_SERVERINFO ) {
$this->serverInfoLine = $this->serverIndex . ' (' . $host . ')';
}
return parent::Connect($host, $user, $pass, $db, $force_new, $retry);
}
/**
* Queries db with $sql query supplied and returns rows selected if any, false otherwise.
*
* Optional parameter $key_field allows to set one of the query fields value as key in string array.
*
* @param string $sql
* @param string $key_field
* @param boolean|null $no_debug
* @return Array
* @access public
*/
public function Query($sql, $key_field = null, $no_debug = null)
{
if ( $no_debug === null ) {
$no_debug = $this->noDebuggingState;
}
if ( $no_debug ) {
return parent::Query($sql, $key_field, $no_debug);
}
global $debugger;
$this->_queryCount++;
$this->lastQuery = $sql;
// set 1st checkpoint: begin
if ( $this->_profileSQLs ) {
$queryID = $debugger->generateID();
$debugger->profileStart('sql_' . $queryID, $debugger->formatSQL($sql));
}
// set 1st checkpoint: end
$this->setError(0, ''); // reset error
$this->queryID = $this->connectionID->query($sql);
if ( is_object($this->queryID) ) {
$ret = Array ();
if ( isset($key_field) ) {
while ( $row = $this->queryID->fetch_assoc() ) {
$ret[$row[$key_field]] = $row;
}
}
else {
while ( $row = $this->queryID->fetch_assoc() ) {
$ret[] = $row;
}
}
// set 2nd checkpoint: begin
if ( $this->_profileSQLs ) {
$current_element = current($ret);
$first_cell = count($ret) == 1 && count($current_element) == 1 ? current($current_element) : null;
if ( strlen($first_cell) > 200 ) {
$first_cell = substr($first_cell, 0, 50) . ' ...';
}
$debugger->profileFinish('sql_' . $queryID, null, null, $this->getAffectedRows(), $first_cell, $this->_queryCount, $this->nextQueryCachable, $this->serverInfoLine);
$debugger->profilerAddTotal('sql', 'sql_' . $queryID);
$this->nextQueryCachable = false;
}
// set 2nd checkpoint: end
$this->Destroy();
return $ret;
}
else {
// set 2nd checkpoint: begin
if ( $this->_profileSQLs ) {
$debugger->profileFinish('sql_' . $queryID, null, null, $this->getAffectedRows(), null, $this->_queryCount, $this->nextQueryCachable, $this->serverInfoLine);
$debugger->profilerAddTotal('sql', 'sql_' . $queryID);
$this->nextQueryCachable = false;
}
// set 2nd checkpoint: end
}
return $this->showError($sql, $key_field);
}
/**
* Returns iterator to a recordset, produced from running $sql query.
*
* Queries db with $sql query supplied and returns kMySQLQuery iterator or false in case of error.
* Optional parameter $key_field allows to set one of the query fields value as key in string array.
*
* @param string $sql
* @param string $key_field
* @param boolean|null $no_debug
* @param string $iterator_class
* @return kMySQLQuery|bool
* @access public
*/
public function GetIterator($sql, $key_field = null, $no_debug = null, $iterator_class = 'kMySQLQuery')
{
if ( $no_debug === null ) {
$no_debug = $this->noDebuggingState;
}
if ( $no_debug ) {
return parent::GetIterator($sql, $key_field, $no_debug, $iterator_class);
}
global $debugger;
$this->_queryCount++;
$this->lastQuery = $sql;
// set 1st checkpoint: begin
if ( $this->_profileSQLs ) {
$queryID = $debugger->generateID();
$debugger->profileStart('sql_' . $queryID, $debugger->formatSQL($sql));
}
// set 1st checkpoint: end
$this->setError(0, ''); // reset error
$this->queryID = $this->connectionID->query($sql);
if ( is_object($this->queryID) ) {
/** @var kMySQLQuery $ret */
$ret = new $iterator_class($this->queryID, $key_field);
// set 2nd checkpoint: begin
if ( $this->_profileSQLs ) {
$current_row = $ret->current();
if ( count($ret) == 1 && $ret->fieldCount() == 1 ) {
if ( is_array($current_row) ) {
$first_cell = current($current_row);
}
else {
$first_cell = $current_row;
}
}
else {
$first_cell = null;
}
if ( strlen($first_cell) > 200 ) {
$first_cell = substr($first_cell, 0, 50) . ' ...';
}
$debugger->profileFinish('sql_' . $queryID, null, null, $this->getAffectedRows(), $first_cell, $this->_queryCount, $this->nextQueryCachable, $this->serverInfoLine);
$debugger->profilerAddTotal('sql', 'sql_' . $queryID);
$this->nextQueryCachable = false;
}
// set 2nd checkpoint: end
return $ret;
}
else {
// set 2nd checkpoint: begin
if ( $this->_profileSQLs ) {
$debugger->profileFinish('sql_' . $queryID, null, null, $this->getAffectedRows(), null, $this->_queryCount, $this->nextQueryCachable, $this->serverInfoLine);
$debugger->profilerAddTotal('sql', 'sql_' . $queryID);
$this->nextQueryCachable = false;
}
// set 2nd checkpoint: end
}
return $this->showError($sql, $key_field);
}
}
class kMySQLQuery implements Iterator, Countable, SeekableIterator {
/**
* Current index in recordset
*
* @var int
* @access protected
*/
protected $position = -1;
/**
* Query resource
*
* @var mysqli_result
* @access protected
*/
protected $result;
/**
* Field to act as key in a resulting array
*
* @var string
* @access protected
*/
protected $keyField = null;
/**
* Data in current row of recordset
*
* @var Array
* @access protected
*/
protected $rowData = Array ();
/**
* Row count in a result
*
* @var int
* @access protected
*/
protected $rowCount = 0;
/**
* Creates new instance of a class
*
* @param mysqli_result $result
* @param null|string $key_field
*/
public function __construct(mysqli_result $result, $key_field = null)
{
$this->result = $result;
$this->keyField = $key_field;
$this->rowCount = $this->result->num_rows;
$this->rewind();
}
/**
* Moves recordset pointer to first element
*
* @return void
* @access public
* @implements Iterator::rewind
*/
public function rewind()
{
$this->seek(0);
}
/**
* Returns value at current position
*
* @return mixed
* @access public
* @implements Iterator::current
*/
function current()
{
return $this->rowData;
}
/**
* Returns key at current position
*
* @return mixed
* @access public
* @implements Iterator::key
*/
function key()
{
return $this->keyField ? $this->rowData[$this->keyField] : $this->position;
}
/**
* Moves recordset pointer to next position
*
* @return void
* @access public
* @implements Iterator::next
*/
function next()
{
$this->seek($this->position + 1);
}
/**
* Detects if current position is within recordset bounds
*
* @return bool
* @access public
* @implements Iterator::valid
*/
public function valid()
{
return $this->position < $this->rowCount;
}
/**
* Counts recordset rows
*
* @return int
* @access public
* @implements Countable::count
*/
public function count()
{
return $this->rowCount;
}
/**
* Counts fields in current row
*
* @return int
* @access public
*/
public function fieldCount()
{
return count($this->rowData);
}
/**
* Moves cursor into given position within recordset
*
* @param int $position
* @throws OutOfBoundsException
* @access public
* @implements SeekableIterator::seek
*/
public function seek($position)
{
if ( $this->position == $position ) {
return;
}
$this->position = $position;
if ( $this->valid() ) {
$this->result->data_seek($this->position);
$this->rowData = $this->result->fetch_assoc();
}
/*if ( !$this->valid() ) {
throw new OutOfBoundsException('Invalid seek position (' . $position . ')');
}*/
}
/**
* Returns first recordset row
*
* @return Array
* @access public
*/
public function first()
{
$this->seek(0);
return $this->rowData;
}
/**
* Closes recordset and freese memory
*
* @return void
* @access public
*/
public function close()
{
$this->result->free();
unset($this->result);
}
/**
* Frees memory when object is destroyed
*
* @return void
* @access public
*/
public function __destruct()
{
$this->close();
}
/**
* Returns all keys
*
* @return Array
* @access public
*/
public function keys()
{
$ret = Array ();
foreach ($this as $key => $value) {
$ret[] = $key;
}
return $ret;
}
/**
* Returns all values
*
* @return Array
* @access public
*/
public function values()
{
$ret = Array ();
foreach ($this as $value) {
$ret[] = $value;
}
return $ret;
}
/**
* Returns whole recordset as array
*
* @return Array
* @access public
*/
public function toArray()
{
$ret = Array ();
foreach ($this as $key => $value) {
$ret[$key] = $value;
}
return $ret;
}
}
class kMySQLQueryCol extends kMySQLQuery {
/**
* Returns value at current position
*
* @return mixed
* @access public
* @implements Iterator::current
*/
function current()
{
return reset($this->rowData);
}
/**
* Returns first column of first recordset row
*
* @return string
* @access public
*/
public function first()
{
$this->seek(0);
return reset($this->rowData);
}
}

Event Timeline

In-Portal CMS · In-Portal Community Pages · In-Portal Issue Tracker · In-Portal Repository Browser · Quality Assurance · In-Portal CI Server