Page MenuHomeWickedGov Phorge
Files F4099647

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)

Event Timeline