Page MenuHomeWickedGov Phorge
Files F4104866

CompareService.php
No OneTemporary
Actions

Size
12 KB
Referenced Files
None
Subscribers
None

CompareService.php
View Options

<?php
namespace MediaWiki\CheckUser\Investigate\Services;
use LogicException;
use MediaWiki\CheckUser\Services\CheckUserLookupUtils;
use MediaWiki\Config\ServiceOptions;
use MediaWiki\User\UserIdentityLookup;
use Wikimedia\IPUtils;
use Wikimedia\Rdbms\IConnectionProvider;
use Wikimedia\Rdbms\IExpression;
use Wikimedia\Rdbms\SelectQueryBuilder;
use Wikimedia\Rdbms\Subquery;
class CompareService extends ChangeService {
/**
* @internal For use by ServiceWiring
*/
public const CONSTRUCTOR_OPTIONS = [
'CheckUserInvestigateMaximumRowCount',
];
private int $limit;
/**
* @param ServiceOptions $options
* @param IConnectionProvider $dbProvider
* @param UserIdentityLookup $userIdentityLookup
* @param CheckUserLookupUtils $checkUserLookupUtils
*/
public function __construct(
ServiceOptions $options,
IConnectionProvider $dbProvider,
UserIdentityLookup $userIdentityLookup,
CheckUserLookupUtils $checkUserLookupUtils
) {
parent::__construct( $dbProvider, $userIdentityLookup, $checkUserLookupUtils );
$options->assertRequiredOptions( self::CONSTRUCTOR_OPTIONS );
$this->limit = $options->get( 'CheckUserInvestigateMaximumRowCount' );
}
/**
* Get the total number of actions made from an IP.
*
* @param string $ipHex
* @return int
*/
public function getTotalActionsFromIP( string $ipHex ): int {
$dbr = $this->dbProvider->getReplicaDatabase();
$totalCount = 0;
foreach ( self::RESULT_TABLES as $table ) {
$tablePrefix = self::RESULT_TABLE_TO_PREFIX[$table];
$totalCount += $dbr->newSelectQueryBuilder()
->select( $tablePrefix . 'id' )
->from( $table )
->where( [ $tablePrefix . 'ip_hex' => $ipHex ] )
->limit( $this->limit )
->caller( __METHOD__ )
->fetchRowCount();
}
return $totalCount;
}
/**
* Get the compare query info
*
* @param string[] $targets
* @param string[] $excludeTargets
* @param string $start
* @return array
*/
public function getQueryInfo( array $targets, array $excludeTargets, string $start ): array {
$dbr = $this->dbProvider->getReplicaDatabase();
if ( $targets === [] ) {
throw new LogicException( 'Cannot get query info when $targets is empty.' );
}
$limit = $this->getLimitPerQuery( $targets );
$unionQueryBuilder = $dbr->newUnionQueryBuilder()->caller( __METHOD__ );
foreach ( $targets as $target ) {
foreach ( self::RESULT_TABLES as $table ) {
if ( $table === self::CHANGES_TABLE ) {
$queryBuilder = $this->getPartialQueryBuilderForCuChanges( $target, $excludeTargets, $start );
} elseif ( $table === self::LOG_EVENT_TABLE ) {
$queryBuilder = $this->getPartialQueryBuilderForCuLogEvent( $target, $excludeTargets, $start );
} elseif ( $table === self::PRIVATE_LOG_EVENT_TABLE ) {
$queryBuilder = $this->getPartialQueryBuilderForCuPrivateEvent( $target, $excludeTargets, $start );
} else {
throw new LogicException( "Invalid table: $table" );
}
if ( $queryBuilder !== null ) {
if ( $dbr->unionSupportsOrderAndLimit() ) {
// Add the limit to the query (if using LIMIT is supported in a UNION for this DB type).
$queryBuilder->limit( $limit );
}
$queryBuilder->useIndex( [
$table => $this->checkUserLookupUtils->getIndexName(
IPUtils::isIPAddress( $target ) ? false : null, $table
),
] );
$unionQueryBuilder->add( $queryBuilder );
}
}
}
$derivedTable = $unionQueryBuilder->getSQL();
return [
'tables' => [ 'a' => new Subquery( $derivedTable ) ],
'fields' => [
'user' => 'a.user',
'user_text' => 'a.user_text',
'actor' => 'MIN(a.actor)',
'ip' => 'a.ip',
'ip_hex' => 'a.ip_hex',
'agent' => 'a.agent',
'first_action' => 'MIN(a.timestamp)',
'last_action' => 'MAX(a.timestamp)',
'total_actions' => 'count(*)',
],
'options' => [
'GROUP BY' => [
'user',
'user_text',
'ip',
'ip_hex',
'agent',
],
],
];
}
/**
* Get the SelectQueryBuilder which can be used to query cu_changes for results. This must be
* extended with table independent query information.
*
* @param string $target The target of this specific query
* @param string[] $excludeTargets The targets to exclude from the query
* @param string $start The start offset, used for paging.
* @return ?SelectQueryBuilder The query builder specific to cu_changes, or null if no valid query builder could
* be generated.
*/
private function getPartialQueryBuilderForCuChanges(
string $target, array $excludeTargets, string $start
): ?SelectQueryBuilder {
$targetExpr = $this->buildExprForSingleTarget( $target, $excludeTargets, $start, self::CHANGES_TABLE );
if ( $targetExpr === null ) {
return null;
}
$dbr = $this->dbProvider->getReplicaDatabase();
$queryBuilder = $dbr->newSelectQueryBuilder()
->select( [
'id' => 'cuc_id', 'user' => 'actor_user', 'user_text' => 'actor_name', 'actor' => 'cuc_actor',
'ip' => 'cuc_ip', 'ip_hex' => 'cuc_ip_hex', 'agent' => 'cuc_agent', 'timestamp' => 'cuc_timestamp',
] )
->from( 'cu_changes' )
->join( 'actor', null, 'actor_id=cuc_actor' )
->where( $targetExpr )
->caller( __METHOD__ );
if ( $dbr->unionSupportsOrderAndLimit() ) {
// TODO: T360712: Add cuc_id to the ORDER BY clause to ensure unique ordering.
$queryBuilder->orderBy( 'cuc_timestamp', SelectQueryBuilder::SORT_DESC );
}
return $queryBuilder;
}
/**
* Get the SelectQueryBuilder which can be used to query cu_log_event for results. This must be
* extended with table independent query information.
*
* @param string $target The target of this specific query
* @param string[] $excludeTargets The targets to exclude from the query
* @param string $start The start offset, used for paging.
* @return ?SelectQueryBuilder The query builder specific to cu_log_event, or null if no valid query builder could
* be generated.
*/
private function getPartialQueryBuilderForCuLogEvent(
string $target, array $excludeTargets, string $start
): ?SelectQueryBuilder {
$targetExpr = $this->buildExprForSingleTarget( $target, $excludeTargets, $start, self::LOG_EVENT_TABLE );
if ( $targetExpr === null ) {
return null;
}
$dbr = $this->dbProvider->getReplicaDatabase();
$queryBuilder = $dbr->newSelectQueryBuilder()
->select( [
'id' => 'cule_id', 'user' => 'actor_user', 'user_text' => 'actor_name', 'actor' => 'cule_actor',
'ip' => 'cule_ip', 'ip_hex' => 'cule_ip_hex', 'agent' => 'cule_agent', 'timestamp' => 'cule_timestamp',
] )
->from( 'cu_log_event' )
->join( 'actor', null, 'actor_id=cule_actor' )
->where( $targetExpr )
->caller( __METHOD__ );
if ( $dbr->unionSupportsOrderAndLimit() ) {
// TODO: T360712: Add cule_id to the ORDER BY clause to ensure unique ordering.
$queryBuilder->orderBy( 'cule_timestamp', SelectQueryBuilder::SORT_DESC );
}
return $queryBuilder;
}
/**
* Get the SelectQueryBuilder which can be used to query cu_private_event for results. This must be
* extended with table independent query information.
*
* @param string $target The target of this specific query
* @param string[] $excludeTargets The targets to exclude from the query
* @param string $start The start offset, used for paging.
* @return ?SelectQueryBuilder The query builder specific to cu_private_event, or null if no valid query builder
* could be generated.
*/
private function getPartialQueryBuilderForCuPrivateEvent(
string $target, array $excludeTargets, string $start
): ?SelectQueryBuilder {
$targetExpr = $this->buildExprForSingleTarget(
$target, $excludeTargets, $start, self::PRIVATE_LOG_EVENT_TABLE
);
if ( $targetExpr === null ) {
return null;
}
$dbr = $this->dbProvider->getReplicaDatabase();
$queryBuilder = $dbr->newSelectQueryBuilder()
->select( [
'id' => 'cupe_id', 'user' => 'actor_user', 'user_text' => 'actor_name', 'actor' => 'cupe_actor',
'ip' => 'cupe_ip', 'ip_hex' => 'cupe_ip_hex', 'agent' => 'cupe_agent', 'timestamp' => 'cupe_timestamp',
] )
->from( 'cu_private_event' )
->where( $targetExpr )
->caller( __METHOD__ );
if ( IPUtils::isIPAddress( $target ) ) {
// A LEFT JOIN is required because cupe_actor can be NULL if the performer was an IP address and temporary
// accounts were enabled.
$queryBuilder->leftJoin( 'actor', null, 'actor_id=cupe_actor' );
} else {
// We only need a JOIN if the target of the check is a username because the username will have a valid
// actor ID.
$queryBuilder->join( 'actor', null, 'actor_id=cupe_actor' );
}
if ( $dbr->unionSupportsOrderAndLimit() ) {
// TODO: T360712: Add cupe_id to the ORDER BY clause to ensure unique ordering.
$queryBuilder->orderBy( 'cupe_timestamp', SelectQueryBuilder::SORT_DESC );
}
return $queryBuilder;
}
/**
* Get the WHERE conditions for a single target in an IExpression object.
*
* For the main investigation, this is used in a subquery that contributes to a derived
* table, used by getQueryInfo.
*
* For a limit check, this is used to build a query that is used to check whether the number of results for
* the target exceed the limit-per-target in getQueryInfo.
*
* @param string $target
* @param string[] $excludeTargets
* @param string $start
* @param string $table
* @return IExpression|null Return null for invalid target.
*/
private function buildExprForSingleTarget(
string $target,
array $excludeTargets,
string $start,
string $table
): ?IExpression {
$targetExpr = $this->buildTargetExpr( $target, $table );
if ( $targetExpr === null ) {
return null;
}
$andExpressionGroup = $this->dbProvider->getReplicaDatabase()->andExpr( [ $targetExpr ] );
// Add the WHERE conditions to exclude the targets in the $excludeTargets array, if they can be generated.
$excludeTargetsExpr = $this->buildExcludeTargetsExpr( $excludeTargets, $table );
if ( $excludeTargetsExpr !== null ) {
$andExpressionGroup = $andExpressionGroup->andExpr( $excludeTargetsExpr );
}
// Add the start timestamp WHERE conditions to the query, if they can be generated.
$startExpr = $this->buildStartExpr( $start, $table );
if ( $startExpr !== null ) {
$andExpressionGroup = $andExpressionGroup->andExpr( $startExpr );
}
return $andExpressionGroup;
}
/**
* We set a maximum number of rows per table per target to be grouped in the Compare table query,
* for performance reasons (see ::getQueryInfo). We share these uniformly between the targets,
* so the maximum number of rows per target is the limit divided by the number of targets divided again
* by the number of tables that will be queried.
*
* @param array $targets
* @return int
*/
private function getLimitPerQuery( array $targets ) {
return ceil( $this->limit / ( count( $targets ) * 3 ) );
}
/**
* Check if we have incomplete data for any of the targets.
*
* @param string[] $targets
* @param string[] $excludeTargets
* @param string $start
* @return string[]
*/
public function getTargetsOverLimit(
array $targets,
array $excludeTargets,
string $start
): array {
if ( $targets === [] ) {
return $targets;
}
$dbr = $this->dbProvider->getReplicaDatabase();
// If the database does not support order and limit on a UNION
// then none of the targets can be over the limit.
if ( !$dbr->unionSupportsOrderAndLimit() ) {
return [];
}
$targetsOverLimit = [];
$offset = $this->getLimitPerQuery( $targets );
foreach ( $targets as $target ) {
foreach ( self::RESULT_TABLES as $table ) {
$targetExpr = $this->buildExprForSingleTarget( $target, $excludeTargets, $start, $table );
if ( $targetExpr !== null ) {
$tablePrefix = self::RESULT_TABLE_TO_PREFIX[$table];
$limitCheck = $dbr->newSelectQueryBuilder()
->select( $tablePrefix . 'id' )
->from( $table )
->where( $targetExpr )
->offset( $offset )
->limit( 1 )
->caller( __METHOD__ );
if ( $table === self::PRIVATE_LOG_EVENT_TABLE && IPUtils::isIPAddress( $target ) ) {
// A LEFT JOIN is required because cupe_actor can be NULL if the performer was an IP address
// and temporary accounts were enabled.
$limitCheck->leftJoin( 'actor', null, 'actor_id=cupe_actor' );
} else {
// We only need a JOIN if the target of the check is a username or the table isn't the
// cu_private_event table because all rows should have a valid actor ID.
$limitCheck->join( 'actor', null, 'actor_id=' . $tablePrefix . 'actor' );
}
if ( $limitCheck->fetchRowCount() > 0 ) {
$targetsOverLimit[] = $target;
// If the target is over limit, then no need to check the other result tables too to see if
// the target is over limit.
break;
}
}
}
}
return $targetsOverLimit;
}
}

File Metadata

Mime Type
text/x-php
Expires
Tue, Aug 18, 18:41 (3 w, 21 h ago)
Storage Engine
local-disk
Storage Format
Raw Data
Storage Handle
12/32/07ddd0b27688b6405469e3399847
Default Alt Text
CompareService.php (12 KB)

Event Timeline