From 5b8213e9efc8807b9bf2c67ce86106a938cdf2be Mon Sep 17 00:00:00 2001 From: Roan Kattouw Date: Sat, 10 May 2008 10:49:26 +0000 Subject: [PATCH] * Re-applying r34449, r34500 and r34518 which Brion reverted by accident * Adding ApiQueryBase::addJoinConds() as wrapper for Database::select()'s $join_conds parameter * Migrating query modules to addJoinConds() * Using implicit join rather than INNER JOIN in ApiQueryBacklinks * Using FORCE INDEX (times) on logging table in ApiQueryLogEvents; although MySQL 4 seems to pick this index automatically (evidenced by the fact the WMF servers are still alive), MySQL 5 doesn't and filesorts * Replacing LEFT JOIN with implicit (inner) join in ApiQueryContributions: revisions without a corresponding page table entry shouldn't be shown anyway --- includes/api/ApiQueryAllUsers.php | 49 ++++++++++++++-------- includes/api/ApiQueryAllpages.php | 11 ++--- includes/api/ApiQueryBacklinks.php | 26 +++++------- includes/api/ApiQueryBase.php | 30 +++++++++++-- includes/api/ApiQueryLogEvents.php | 13 +++--- includes/api/ApiQueryRecentChanges.php | 12 +++--- includes/api/ApiQuerySiteinfo.php | 5 ++- includes/api/ApiQueryUserContributions.php | 5 +-- includes/api/ApiQueryUsers.php | 25 ++++++----- 9 files changed, 106 insertions(+), 70 deletions(-) diff --git a/includes/api/ApiQueryAllUsers.php b/includes/api/ApiQueryAllUsers.php index 4c6914bf56..9ec1276637 100644 --- a/includes/api/ApiQueryAllUsers.php +++ b/includes/api/ApiQueryAllUsers.php @@ -46,26 +46,27 @@ class ApiQueryAllUsers extends ApiQueryBase { $prop = $params['prop']; if (!is_null($prop)) { $prop = array_flip($prop); + $fld_blockinfo = isset($prop['blockinfo']); $fld_editcount = isset($prop['editcount']); $fld_groups = isset($prop['groups']); $fld_registration = isset($prop['registration']); - } else { - $fld_editcount = $fld_groups = $fld_registration = false; + } else { + $fld_blockinfo = $fld_editcount = $fld_groups = $fld_registration = false; } $limit = $params['limit']; - $tables = $db->tableName('user'); + $this->addTables('user', 'u1'); if( !is_null( $params['from'] ) ) - $this->addWhere( 'user_name >= ' . $db->addQuotes( self::keyToTitle( $params['from'] ) ) ); + $this->addWhere( 'u1.user_name >= ' . $db->addQuotes( self::keyToTitle( $params['from'] ) ) ); if( isset( $params['prefix'] ) ) - $this->addWhere( 'user_name LIKE "' . $db->escapeLike( self::keyToTitle( $params['prefix'] ) ) . '%"' ); + $this->addWhere( 'u1.user_name LIKE "' . $db->escapeLike( self::keyToTitle( $params['prefix'] ) ) . '%"' ); if (!is_null($params['group'])) { // Filter only users that belong to a given group - $tblName = $db->tableName('user_groups'); - $tables = "$tables INNER JOIN $tblName ug1 ON ug1.ug_user=user_id"; + $this->addTables('user_groups', 'ug1'); + $this->addWhere('ug1.ug_user=u1.user_id'); $this->addWhereFld('ug1.ug_group', $params['group']); } @@ -75,23 +76,30 @@ class ApiQueryAllUsers extends ApiQueryBase { $groupCount = count(User::getAllGroups()); $sqlLimit = $limit+$groupCount+1; - $tblName = $db->tableName('user_groups'); - $tables = "$tables LEFT JOIN $tblName ug2 ON ug2.ug_user=user_id"; + $this->addTables('user_groups', 'ug2'); + $tname = $this->getAliasedName('user_groups', 'ug2'); + $this->addJoinConds(array($tname => array('LEFT JOIN', 'ug2.ug_user=u1.user_id'))); $this->addFields('ug2.ug_group ug_group2'); } else { $sqlLimit = $limit+1; } - - if ($fld_registration) - $this->addFields('user_registration'); + if ($fld_blockinfo) { + $this->addTables('ipblocks'); + $this->addTables('user', 'u2'); + $u2 = $this->getAliasedName('user', 'u2'); + $this->addJoinConds(array( + 'ipblocks' => array('LEFT JOIN', 'ipb_user=u1.user_id'), + $u2 => array('LEFT JOIN', 'ipb_by=u2.user_id'))); + $this->addFields(array('ipb_reason', 'u2.user_name blocker_name')); + } $this->addOption('LIMIT', $sqlLimit); - $this->addTables($tables); - $this->addFields('user_name'); - $this->addFieldsIf('user_editcount', $fld_editcount); + $this->addFields('u1.user_name'); + $this->addFieldsIf('u1.user_editcount', $fld_editcount); + $this->addFieldsIf('u1.user_registration', $fld_registration); - $this->addOption('ORDER BY', 'user_name'); + $this->addOption('ORDER BY', 'u1.user_name'); $res = $this->select(__METHOD__); @@ -131,6 +139,10 @@ class ApiQueryAllUsers extends ApiQueryBase { // Record new user's data $lastUser = $row->user_name; $lastUserData = array( 'name' => $lastUser ); + if ($fld_blockinfo) { + $lastUserData['blockedby'] = $row->blocker_name; + $lastUserData['blockreason'] = $row->ipb_reason; + } if ($fld_editcount) $lastUserData['editcount'] = intval($row->user_editcount); if ($fld_registration) @@ -168,9 +180,10 @@ class ApiQueryAllUsers extends ApiQueryBase { 'prop' => array ( ApiBase :: PARAM_ISMULTI => true, ApiBase :: PARAM_TYPE => array ( - 'editcount', + 'blockinfo', 'groups', - 'registration', + 'editcount', + 'registration' ) ), 'limit' => array ( diff --git a/includes/api/ApiQueryAllpages.php b/includes/api/ApiQueryAllpages.php index 4758542e5e..222b42ad4a 100644 --- a/includes/api/ApiQueryAllpages.php +++ b/includes/api/ApiQueryAllpages.php @@ -57,6 +57,7 @@ class ApiQueryAllpages extends ApiQueryGeneratorBase { $params = $this->extractRequestParams(); // Page filters + $this->addTables('page'); if (!$this->addWhereIf('page_is_redirect = 1', $params['filterredir'] === 'redirects')) $this->addWhereIf('page_is_redirect = 0', $params['filterredir'] === 'nonredirects'); $this->addWhereFld('page_namespace', $params['namespace']); @@ -97,18 +98,14 @@ class ApiQueryAllpages extends ApiQueryGeneratorBase { } if($params['filterlanglinks'] == 'withoutlanglinks') { - $pageName = $this->getDB()->tableName('page'); - $llName = $this->getDB()->tableName('langlinks'); - $tables = "$pageName LEFT JOIN $llName ON page_id=ll_from"; + $this->addTables('langlinks'); + $this->addJoinConds(array('langlinks' => array('LEFT JOIN', 'page_id=ll_from'))); $this->addWhere('ll_from IS NULL'); - $this->addTables($tables); $forceNameTitleIndex = false; } else if($params['filterlanglinks'] == 'withlanglinks') { - $this->addTables(array('page', 'langlinks')); + $this->addTables('langlinks'); $this->addWhere('page_id=ll_from'); $forceNameTitleIndex = false; - } else { - $this->addTables('page'); } if ($forceNameTitleIndex) $this->addOption('USE INDEX', 'name_title'); diff --git a/includes/api/ApiQueryBacklinks.php b/includes/api/ApiQueryBacklinks.php index e2cfcfb21b..8081837fb1 100644 --- a/includes/api/ApiQueryBacklinks.php +++ b/includes/api/ApiQueryBacklinks.php @@ -96,13 +96,13 @@ class ApiQueryBacklinks extends ApiQueryGeneratorBase { private function prepareFirstQuery($resultPageSet = null) { /* SELECT page_id, page_title, page_namespace, page_is_redirect - * FROM pagelinks JOIN page ON pl_from=page_id - * WHERE pl_title='Foo' AND pl_namespace=0 + * FROM pagelinks, page WHERE pl_from=page_id + * AND pl_title='Foo' AND pl_namespace=0 * LIMIT 11 ORDER BY pl_from */ $db = $this->getDb(); - list($tblpage, $tbllinks) = $db->tableNamesN('page', $this->bl_table); - $this->addTables("$tbllinks JOIN $tblpage ON {$this->bl_from}=page_id"); + $this->addTables(array('page', $this->bl_table)); + $this->addWhere("{$this->bl_from}=page_id"); if(is_null($resultPageSet)) $this->addFields(array('page_id', 'page_title', 'page_namespace')); else @@ -124,13 +124,13 @@ class ApiQueryBacklinks extends ApiQueryGeneratorBase { private function prepareSecondQuery($resultPageSet = null) { /* SELECT page_id, page_title, page_namespace, page_is_redirect, pl_title, pl_namespace - * FROM pagelinks JOIN page ON pl_from=page_id - * WHERE (pl_title='Foo' AND pl_namespace=0) OR (pl_title='Bar' AND pl_namespace=1) + * FROM pagelinks, page WHERE pl_from=page_id + * AND (pl_title='Foo' AND pl_namespace=0) OR (pl_title='Bar' AND pl_namespace=1) * LIMIT 11 ORDER BY pl_namespace, pl_title, pl_from */ $db = $this->getDb(); - list($tblpage, $tbllinks) = $db->tableNamesN('page', $this->bl_table); - $this->addTables("$tbllinks JOIN $tblpage ON {$this->bl_from}=page_id"); + $this->addTables(array('page', $this->bl_table)); + $this->addWhere("{$this->bl_from}=page_id"); if(is_null($resultPageSet)) $this->addFields(array('page_id', 'page_title', 'page_namespace', 'page_is_redirect')); else @@ -260,20 +260,14 @@ class ApiQueryBacklinks extends ApiQueryGeneratorBase { } protected function processContinue() { - $pageSet = $this->getPageSet(); - $count = $pageSet->getTitleCount(); - if (!is_null($this->params['continue'])) $this->parseContinueParam(); else { $title = $this->params['title']; if (!is_null($title)) { $this->rootTitle = Title :: newFromText($title); - } else { // This case is obsolete. Will support this for a while - if ($count !== 1) - $this->dieUsage("The {$this->getModuleName()} query requires one title to start", 'bad_title_count'); - $this->rootTitle = current($pageSet->getTitles()); // only one title there - $this->setWarning('Using titles parameter is obsolete for this list. Use ' . $this->encodeParamName('title') . ' instead.'); + } else { + $this->dieUsageMsg(array('missingparam', 'title')); } } diff --git a/includes/api/ApiQueryBase.php b/includes/api/ApiQueryBase.php index d3b7d8d941..b9f3e8ae4d 100644 --- a/includes/api/ApiQueryBase.php +++ b/includes/api/ApiQueryBase.php @@ -36,7 +36,7 @@ if (!defined('MEDIAWIKI')) { */ abstract class ApiQueryBase extends ApiBase { - private $mQueryModule, $mDb, $tables, $where, $fields, $options; + private $mQueryModule, $mDb, $tables, $where, $fields, $options, $join_conds; public function __construct($query, $moduleName, $paramPrefix = '') { parent :: __construct($query->getMain(), $moduleName, $paramPrefix); @@ -53,6 +53,7 @@ abstract class ApiQueryBase extends ApiBase { $this->where = array (); $this->fields = array (); $this->options = array (); + $this->join_conds = array (); } /** @@ -67,10 +68,33 @@ abstract class ApiQueryBase extends ApiBase { $this->tables = array_merge($this->tables, $tables); } else { if (!is_null($alias)) - $tables = $this->getDB()->tableName($tables) . ' ' . $alias; + $tables = $this->getAliasedName($tables, $alias); $this->tables[] = $tables; } } + + /** + * Get the SQL for a table name with alias + * @param string $table Table name + * @param string $alias Alias + * @return string SQL + */ + protected function getAliasedName($table, $alias) { + return $this->getDB()->tableName($table) . ' ' . $alias; + } + + /** + * Add a set of JOIN conditions to the internal array + * + * JOIN conditions are formatted as array( tablename => array(jointype, conditions) + * e.g. array('page' => array('LEFT JOIN', 'page_id=rev_page')) + * @param array $join_conds JOIN conditions + */ + protected function addJoinConds($join_conds) { + if(!is_array($join_conds)) + ApiBase::dieDebug(__METHOD__, 'Join conditions have to be arrays'); + $this->join_conds = array_merge($this->join_conds, $join_conds); + } /** * Add a set of fields to select to the internal array @@ -187,7 +211,7 @@ abstract class ApiQueryBase extends ApiBase { $db = $this->getDB(); $this->profileDBIn(); - $res = $db->select($this->tables, $this->fields, $this->where, $method, $this->options); + $res = $db->select($this->tables, $this->fields, $this->where, $method, $this->options, $this->join_conds); $this->profileDBOut(); return $res; diff --git a/includes/api/ApiQueryLogEvents.php b/includes/api/ApiQueryLogEvents.php index df93e7c692..1d3ba3b9f3 100644 --- a/includes/api/ApiQueryLogEvents.php +++ b/includes/api/ApiQueryLogEvents.php @@ -58,10 +58,14 @@ class ApiQueryLogEvents extends ApiQueryBase { if($hideLogs !== false) $this->addWhere($hideLogs); - $this->addOption('STRAIGHT_JOIN'); - $this->addTables("$tbl_logging LEFT OUTER JOIN $tbl_page ON " . - "log_namespace=page_namespace AND log_title=page_title " . - "INNER JOIN $tbl_user ON user_id=log_user"); + // Order is significant here + $this->addTables(array('user', 'page', 'logging')); + $this->addJoinConds(array( + 'page' => array('LEFT JOIN', + array( 'log_namespace=page_namespace', + 'log_title=page_title')))); + $this->addWhere('user_id=log_user'); + $this->addOption('USE INDEX', array('logging' => 'times')); $this->addFields(array ( 'log_type', @@ -79,7 +83,6 @@ class ApiQueryLogEvents extends ApiQueryBase { $this->addFieldsIf('log_comment', $this->fld_comment); $this->addFieldsIf('log_params', $this->fld_details); - $this->addWhereFld('log_deleted', 0); $this->addWhereFld('log_type', $params['type']); $this->addWhereRange('log_timestamp', $params['dir'], $params['start'], $params['end']); diff --git a/includes/api/ApiQueryRecentChanges.php b/includes/api/ApiQueryRecentChanges.php index 16dd43b31a..2a5c83a395 100644 --- a/includes/api/ApiQueryRecentChanges.php +++ b/includes/api/ApiQueryRecentChanges.php @@ -55,12 +55,13 @@ class ApiQueryRecentChanges extends ApiQueryBase { extract($this->extractRequestParams()); /* Build our basic query. Namely, something along the lines of: - * SELECT * from recentchanges WHERE rc_timestamp > $start + * SELECT * FROM recentchanges WHERE rc_timestamp > $start * AND rc_timestamp < $end AND rc_namespace = $namespace * AND rc_deleted = '0' */ $db = $this->getDB(); - $rc = $db->tableName('recentchanges'); + $this->addTables('recentchanges'); + $this->addOption('USE INDEX', array('recentchanges' => 'rc_timestamp')); $this->addWhereRange('rc_timestamp', $dir, $start, $end); $this->addWhereFld('rc_namespace', $namespace); $this->addWhereFld('rc_deleted', 0); @@ -164,14 +165,11 @@ class ApiQueryRecentChanges extends ApiQueryBase { $this->addFieldsIf('rc_patrolled', $this->fld_patrolled); if($this->fld_redirect || isset($show['redirect']) || isset($show['!redirect'])) { - $page = $db->tableName('page'); - $tables = "$page RIGHT JOIN $rc FORCE INDEX(rc_timestamp) ON page_namespace=rc_namespace AND page_title=rc_title"; + $this->addTables('page'); + $this->addJoinConds(array('page' => array('RIGHT JOIN', array('page_namespace=rc_namespace', 'page_title=rc_title')))); $this->addFields('page_is_redirect'); } } - if(!isset($tables)) - $tables = "$rc FORCE INDEX(rc_timestamp)"; - $this->addTables($tables); /* Specify the limit for our query. It's $limit+1 because we (possibly) need to * generate a "continue" parameter, to allow paging. */ $this->addOption('LIMIT', $limit +1); diff --git a/includes/api/ApiQuerySiteinfo.php b/includes/api/ApiQuerySiteinfo.php index 98467f208d..244a66531e 100644 --- a/includes/api/ApiQuerySiteinfo.php +++ b/includes/api/ApiQuerySiteinfo.php @@ -170,13 +170,16 @@ class ApiQuerySiteinfo extends ApiQueryBase { $res = $this->select(__METHOD__); $data = array(); + $langNames = Language::getLanguageNames(); while($row = $db->fetchObject($res)) { $val = array(); $val['prefix'] = $row->iw_prefix; - if ($row->iw_local == '1') + if($row->iw_local == '1') $val['local'] = ''; // $val['trans'] = intval($row->iw_trans); // should this be exposed? + if(isset($langNames[$row->iw_prefix])) + $val['language'] = $langNames[$row->iw_prefix]; $val['url'] = $row->iw_url; $data[] = $val; diff --git a/includes/api/ApiQueryUserContributions.php b/includes/api/ApiQueryUserContributions.php index 21ceb27c46..1ada118535 100644 --- a/includes/api/ApiQueryUserContributions.php +++ b/includes/api/ApiQueryUserContributions.php @@ -59,7 +59,6 @@ class ApiQueryContributions extends ApiQueryBase { $this->selectNamedDB('contributions', DB_SLAVE, 'contributions'); $db = $this->getDB(); - if(isset($this->params['userprefix'])) { $this->prefixMode = true; @@ -131,8 +130,8 @@ class ApiQueryContributions extends ApiQueryBase { //We're after the revision table, and the corresponding page row for //anything we retrieve. - list ($tbl_page, $tbl_revision) = $this->getDB()->tableNamesN('page', 'revision'); - $this->addTables("$tbl_revision LEFT OUTER JOIN $tbl_page ON page_id=rev_page"); + $this->addTables(array('revision', 'page')); + $this->addWhere('page_id=rev_page'); $this->addWhereFld('rev_deleted', 0); // We only want pages by the specified users. diff --git a/includes/api/ApiQueryUsers.php b/includes/api/ApiQueryUsers.php index c0cae506d2..1d94648620 100644 --- a/includes/api/ApiQueryUsers.php +++ b/includes/api/ApiQueryUsers.php @@ -72,24 +72,26 @@ if (!defined('MEDIAWIKI')) { return $retval; $db = $this->getDb(); - $userTable = $db->tableName('user'); - $tables = "$userTable AS u1"; + $this->addTables('user', 'u1'); $this->addFields('u1.user_name'); $this->addWhereFld('u1.user_name', $goodNames); $this->addFieldsIf('u1.user_editcount', isset($this->prop['editcount'])); + $this->addFieldsIf('u1.user_registration', isset($this->prop['registration'])); if(isset($this->prop['groups'])) { - $ug = $db->tableName('user_groups'); - $tables = "$tables LEFT JOIN $ug ON ug_user=u1.user_id"; + $this->addTables('user_groups'); + $this->addJoinConds(array('user_groups' => array('LEFT JOIN', 'ug_user=u1.user_id'))); $this->addFields('ug_group'); } if(isset($this->prop['blockinfo'])) { - $ipb = $db->tableName('ipblocks'); - $tables = "$tables LEFT JOIN $ipb ON ipb_user=u1.user_id"; - $tables = "$tables LEFT JOIN $userTable AS u2 ON ipb_by=u2.user_id"; - $this->addFields(array('ipb_reason', 'u2.user_name AS blocker_name')); + $this->addTables('ipblocks'); + $this->addTables('user', 'u2'); + $u2 = $this->getAliasedName('user', 'u2'); + $this->addJoinConds(array( + 'ipblocks' => array('LEFT JOIN', 'ipb_user=u1.user_id'), + $u2 => array('LEFT JOIN', 'ipb_by=u2.user_id'))); + $this->addFields(array('ipb_reason', 'u2.user_name blocker_name')); } - $this->addTables($tables); $data = array(); $res = $this->select(__METHOD__); @@ -97,6 +99,8 @@ if (!defined('MEDIAWIKI')) { $data[$r->user_name]['name'] = $r->user_name; if(isset($this->prop['editcount'])) $data[$r->user_name]['editcount'] = $r->user_editcount; + if(isset($this->prop['registration'])) + $data[$r->user_name]['registration'] = wfTimestamp(TS_ISO_8601, $r->user_registration); if(isset($this->prop['groups'])) // This row contains only one group, others will be added from other rows if(!is_null($r->ug_group)) @@ -129,7 +133,8 @@ if (!defined('MEDIAWIKI')) { ApiBase :: PARAM_TYPE => array ( 'blockinfo', 'groups', - 'editcount' + 'editcount', + 'registration' ) ), 'users' => array( -- 2.20.1