TimelineService.php
No OneTemporary
Actions

Size

14 KB

Referenced Files

None

Subscribers

None

TimelineService.php
View Options

	<?php

	namespace MediaWiki\CheckUser\Investigate\Services;

	use LogicException;
	use Wikimedia\IPUtils;
	use Wikimedia\Rdbms\SelectQueryBuilder;
	use Wikimedia\Rdbms\Subquery;

	class TimelineService extends ChangeService {

	/**
	* Get timeline query info
	*
	* @param string[] $targets The targets of the check
	* @param string[] $excludeTargets The targets to exclude from the check
	* @param string $start The start offset
	* @param int $limit The limit for the check
	* @return array
	*/
	public function getQueryInfo( array $targets, array $excludeTargets, string $start, int $limit ): array {
	// Split the targets into users and IP addresses, so that two queries can be made (one for the users and one
	// for the IPs) and then unioned together.
	$ipTargets = array_filter( $targets, [ IPUtils::class, 'isIPAddress' ] );
	$userTargets = array_diff( $targets, $ipTargets );

	$dbr = $this->dbProvider->getReplicaDatabase();
	$unionQueryBuilder = $dbr->newUnionQueryBuilder()->caller( __METHOD__ );

	// Keep a track of whether a valid SELECT query has been added to the UNION builder.
	$hasValidQuery = false;
	foreach ( self::RESULT_TABLES as $table ) {
	// Generate the queries to be combined in a UNION. If there are no valid targets for the query, then the
	// query will not be run.
	if ( count( $ipTargets ) ) {
	$ipTargetsQuery = $this->getQueryBuilderForTable(
	$table, $ipTargets, true, $excludeTargets, $start, $limit
	);
	if ( $ipTargetsQuery !== null ) {
	$unionQueryBuilder->add( $ipTargetsQuery );
	$hasValidQuery = true;
	}
	}
	if ( count( $userTargets ) ) {
	$userTargetsQuery = $this->getQueryBuilderForTable(
	$table, $userTargets, false, $excludeTargets, $start, $limit
	);
	if ( $userTargetsQuery !== null ) {
	$unionQueryBuilder->add( $userTargetsQuery );
	$hasValidQuery = true;
	}
	}
	}

	if ( !$hasValidQuery ) {
	throw new LogicException( 'Cannot get query info when $targets is empty or contains all invalid targets.' );
	}

	$derivedTable = $unionQueryBuilder->getSQL();

	return [
	'tables' => [ 'a' => new Subquery( $derivedTable ) ],
	'fields' => [
	'namespace', 'title', 'timestamp', 'page_id', 'ip', 'xff', 'agent', 'id', 'user', 'user_text', 'actor',
	'comment_text', 'comment_data', 'type', 'this_oldid', 'last_oldid', 'minor',
	'log_type', 'log_action', 'log_params', 'log_deleted', 'log_id',
	],
	];
	}

	/**
	* Gets the partial query builder for a specific $table.
	*
	* @param string $table The table the query is being performed on and must also be one of the tables listed in
	* {@link CheckUserQueryInterface::RESULT_TABLES}
	* @param array $targets A subset of the $targets provided to ::getQueryInfo, which must all be IPs or accounts
	* @param bool $targetsAreIPs Whether the targets in $targets are all IP addresses (false is for all
	* targets being accounts).
	* @param array $excludeTargets See ::getQueryInfo
	* @param string $start See ::getQueryInfo
	* @param int $limit See ::getQueryInfo
	* @return SelectQueryBuilder\|null The query builder specific to the table, or null if no valid query builder could
	* be generated.
	*/
	private function getQueryBuilderForTable(
	string $table, array $targets, bool $targetsAreIPs, array $excludeTargets, string $start, int $limit
	): ?SelectQueryBuilder {
	// Get the query builder for the table which has all table-specific information added but needs the
	// table non-specific information added.
	$queryBuilder = null;
	if ( $table === self::CHANGES_TABLE ) {
	$queryBuilder = $this->getPartialQueryBuilderForCuChanges( $targets );
	} elseif ( $table === self::LOG_EVENT_TABLE ) {
	$queryBuilder = $this->getPartialQueryBuilderForCuLogEvent( $targets );
	} elseif ( $table === self::PRIVATE_LOG_EVENT_TABLE ) {
	$queryBuilder = $this->getPartialQueryBuilderForCuPrivateEvent( $targets, $targetsAreIPs );
	}
	// Don't attempt to add any table-independent conditions if the query builder is null.
	if ( $queryBuilder === null ) {
	return null;
	}
	// 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 ) {
	$queryBuilder->where( $excludeTargetsExpr );
	}
	// Add the start timestamp WHERE conditions to the query, if they can be generated.
	$startExpr = $this->buildStartExpr( $start, $table );
	if ( $startExpr !== null ) {
	$queryBuilder->where( $startExpr );
	}
	// FORCE INDEX is needed to avoid slow queries for users with very high action counts in the result tables.
	$queryBuilder->useIndex( [
	$table => $this->checkUserLookupUtils->getIndexName( $targetsAreIPs ? false : null, $table ),
	] );
	$dbr = $this->dbProvider->getReplicaDatabase();
	if ( $dbr->unionSupportsOrderAndLimit() ) {
	// Add the limit to the query (if using LIMIT is supported in a UNION for this DB type).
	$queryBuilder->limit( $limit + 1 );
	}
	return $queryBuilder;
	}

	/**
	* Get the SelectQueryBuilder which can be used to query cu_changes for results.
	*
	* @param array $targets See ::getQueryInfo
	* @return ?SelectQueryBuilder The query builder specific to cu_changes, or null if no valid query builder could
	* be generated.
	*/
	private function getPartialQueryBuilderForCuChanges( array $targets ): ?SelectQueryBuilder {
	$targetsExpr = $this->buildTargetExprMultiple( $targets, self::CHANGES_TABLE );
	// Don't run the query if no targets are valid.
	if ( $targetsExpr === null ) {
	return null;
	}
	// Important: If updating the fields here, make sure that these are the same and in the same order
	// with the fields in other query builders as the UNION query require that the fields are in the
	// same order and have the same names.
	//
	// Common fields for all queries
	$fields = [
	'namespace' => 'cuc_namespace', 'title' => 'cuc_title', 'timestamp' => 'cuc_timestamp',
	'page_id' => 'cuc_page_id', 'ip' => 'cuc_ip', 'xff' => 'cuc_xff', 'agent' => 'cuc_agent',
	'id' => 'cuc_id', 'user' => 'actor_user', 'user_text' => 'actor_name', 'actor' => 'cuc_actor',
	'comment_text', 'comment_data', 'type' => 'cuc_type',
	];
	// Fields only specific to cu_changes
	$fields += [
	'this_oldid' => 'cuc_this_oldid', 'last_oldid' => 'cuc_last_oldid', 'minor' => 'cuc_minor',
	];
	// Fields relevant to cu_log_event and cu_private_event
	$fields += $this->markUnusedFieldsAsNull( [ 'log_type', 'log_action', 'log_params' ] );
	$fields += $this->markUnusedFieldsAsNull( [ 'log_deleted' ], 'smallint' );
	$fields += $this->markUnusedFieldsAsNull( [ 'log_id' ], 'int' );
	$dbr = $this->dbProvider->getReplicaDatabase();
	$queryBuilder = $dbr->newSelectQueryBuilder()
	->select( $fields )
	->from( 'cu_changes' )
	->join( 'actor', null, 'actor_id=cuc_actor' )
	->join( 'comment', null, 'comment_id=cuc_comment_id' )
	->where( $targetsExpr )
	->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.
	*
	* @param array $targets See ::getQueryInfo
	* @return ?SelectQueryBuilder The query builder specific to cu_log_event, or null if no valid query builder could
	* be generated.
	*/
	private function getPartialQueryBuilderForCuLogEvent( array $targets ): ?SelectQueryBuilder {
	$targetsExpr = $this->buildTargetExprMultiple( $targets, self::LOG_EVENT_TABLE );
	// Don't run the query if no targets are valid.
	if ( $targetsExpr === null ) {
	return null;
	}
	// Important: If updating the fields here, make sure that these are the same and in the same order
	// with the fields in other query builders as the UNION query require that the fields are in the
	// same order and have the same names.
	//
	// Common fields for all queries
	$fields = [
	'namespace' => 'log_namespace', 'title' => 'log_title', 'timestamp' => 'cule_timestamp',
	'page_id' => 'log_page', 'ip' => 'cule_ip', 'xff' => 'cule_xff', 'agent' => 'cule_agent',
	'id' => 'cule_id', 'user' => 'actor_user', 'user_text' => 'actor_name', 'actor' => 'cule_actor',
	'comment_text', 'comment_data', 'type' => $this->castValueToType( (string)RC_LOG, 'smallint' ),
	];
	// Fields only specific to cu_changes
	$fields += $this->markUnusedFieldsAsNull( [ 'this_oldid', 'last_oldid' ], 'int' );
	$fields += $this->markUnusedFieldsAsNull( [ 'minor' ], 'smallint' );
	// Fields relevant to cu_log_event and cu_private_event
	$fields += [
	'log_type' => 'log_type', 'log_action' => 'log_action', 'log_params' => 'log_params',
	'log_deleted' => 'log_deleted', 'log_id' => 'cule_log_id',
	];
	$dbr = $this->dbProvider->getReplicaDatabase();
	$queryBuilder = $dbr->newSelectQueryBuilder()
	->select( $fields )
	->from( 'cu_log_event' )
	->join( 'actor', null, 'actor_id=cule_actor' )
	->join( 'logging', null, 'log_id=cule_log_id' )
	->join( 'comment', null, 'comment_id=log_comment_id' )
	->where( $targetsExpr )
	->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.
	*
	* @param array $targets See ::getQueryInfo
	* @param bool $targetsAreIPs Whether the targets in $targets are all IP addresses (false is for all accounts).
	* @return ?SelectQueryBuilder The query builder specific to cu_private_event, or null if no valid query builder
	* could be generated.
	*/
	private function getPartialQueryBuilderForCuPrivateEvent(
	array $targets, bool $targetsAreIPs
	): ?SelectQueryBuilder {
	$targetsExpr = $this->buildTargetExprMultiple( $targets, self::PRIVATE_LOG_EVENT_TABLE );
	// Don't run the query if no targets are valid.
	if ( $targetsExpr === null ) {
	return null;
	}
	// Important: If updating the fields here, make sure that these are the same and in the same order
	// with the fields in other query builders as the UNION query require that the fields are in the
	// same order and have the same names.
	//
	// Common fields for all queries
	$fields = [
	'namespace' => 'cupe_namespace', 'title' => 'cupe_title', 'timestamp' => 'cupe_timestamp',
	'page_id' => 'cupe_page', 'ip' => 'cupe_ip', 'xff' => 'cupe_xff', 'agent' => 'cupe_agent',
	'id' => 'cupe_id', 'user' => 'actor_user', 'user_text' => 'actor_name', 'actor' => 'cupe_actor',
	'comment_text', 'comment_data', 'type' => $this->castValueToType( (string)RC_LOG, 'smallint' ),
	];
	// Fields only specific to cu_changes
	$fields += $this->markUnusedFieldsAsNull( [ 'this_oldid', 'last_oldid' ], 'int' );
	$fields += $this->markUnusedFieldsAsNull( [ 'minor' ], 'smallint' );
	// Fields relevant to cu_log_event and cu_private_event
	$fields += [
	'log_type' => 'cupe_log_type', 'log_action' => 'cupe_log_action', 'log_params' => 'cupe_params',
	];
	// Rows in cu_private_event cannot be revision-deleted, so the value of log_deleted for the rows from this
	// table will always be 0.
	$fields['log_deleted'] = $this->castValueToType( (string)0, 'smallint' );
	// Fields only specific to cu_log_event
	$fields += $this->markUnusedFieldsAsNull( [ 'log_id' ], 'int' );
	$dbr = $this->dbProvider->getReplicaDatabase();
	$queryBuilder = $dbr->newSelectQueryBuilder()
	->select( $fields )
	->from( 'cu_private_event' )
	->join( 'comment', null, 'comment_id=cupe_comment_id' )
	->where( $targetsExpr )
	->caller( __METHOD__ );
	if ( $targetsAreIPs ) {
	// 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;
	}

	/**
	* Sets the value of every provided field to NULL.
	*
	* Used to meet the requirement that all the SELECT sub-queries have the same number of columns. NULL will
	* indicate that the value is not applicable for this row.
	*
	* If using postgres the NULL will be cast to the type specified in the second argument. This is because in
	* postgres a NULL without a cast is assumed to be of the text type which will not work if the column is
	* an integer (or other non-text based type) in another table.
	*
	* @param array $fields Where the values are the aliased field names that are unused for this query
	* @param ?string $postgresType
	* @return string[]
	*/
	private function markUnusedFieldsAsNull( array $fields, ?string $postgresType = null ): array {
	$fieldsToReturn = [];
	foreach ( $fields as $alias ) {
	$fieldsToReturn[$alias] = $this->castValueToType( 'Null', $postgresType );
	}
	return $fieldsToReturn;
	}

	/**
	* Casts the provided value to the specified type if this is necessary for the current DB type.
	*
	* This is necessary because postgres will not automatically cast integers to smallint if the other columns
	* in the UNION use a smallint type. For DBs other than postgres, this method currently is a no-op.
	*
	* @param string $value The value to cast, which should be properly SQL escaped
	* @param ?string $postgresType The type of the column for postgres, or null to skip casting for all DB types.
	* @return string The value after appropriate casting, which should be treated as escaped SQL.
	*/
	private function castValueToType( string $value, ?string $postgresType ): string {
	$dbr = $this->dbProvider->getReplicaDatabase();
	if ( $dbr->getType() === 'postgres' && $postgresType !== null ) {
	return "CAST($value AS $postgresType)";
	} else {
	return $value;
	}
	}
	}

File Metadata

Mime Type: text/x-php
Expires: Tue, Aug 18, 16:00 (3 w, 5 d ago)
Storage Engine: local-disk
Storage Format: Raw Data
Storage Handle: 3a/d6/288377b29d378199146e3082f5b5
Default Alt Text: TimelineService.php (14 KB)

TimelineService.phpNo OneTemporaryActions

TimelineService.phpView Options

File Metadata

Event Timeline

TimelineService.php
No OneTemporary
Actions

TimelineService.php
View Options