EditCounterRepository::globalEditCountsFromCentralAuth() - Code Metrics - Inspection of "EditCounter: Speed up global contribs for IP addre..." - x-tools/xtools - Measure and Improve Code Quality continuously with Scrutinizer

Passed
Push — master ( b25a2f...9287df )

by MusikAnimal
created 2019-06-11 16:15 UTC
globalEditCountsFromCentralAuth() A

↳ Parent: EditCounterRepository
Complexity

Conditions	5
Paths	5
Size

Total Lines	38
Code Lines	22
Duplication

Lines	0
Ratio	0 %
Importance

Changes
Metric	Value
cc	5
eloc	22
nc	5
nop	2
dl	0
loc	38
rs	9.2568
c	0
b	0
f	0
<?php
/**
 * This file contains only the EditCounterRepository class.
 */

declare(strict_types = 1);

namespace AppBundle\Repository;

use AppBundle\Model\Project;
use AppBundle\Model\User;
use Mediawiki\Api\SimpleRequest;

/**
 * An EditCounterRepository is responsible for retrieving edit count information from the
 * databases and API. It doesn't do any post-processing of that information.
 * @codeCoverageIgnore
 */
class EditCounterRepository extends UserRightsRepository
{
    /**
     * Get data about revisions, pages, etc.
     * @param Project $project The project.
     * @param User $user The user.
     * @return string[] With keys: 'deleted', 'live', 'total', '24h', '7d', '30d',
     * '365d', 'small', 'large', 'with_comments', and 'minor_edits', ...
     */
    public function getPairData(Project $project, User $user): array
    {
        // Set up cache.
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_pairdata');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }

        // Prepare the queries and execute them.
        $archiveTable = $project->getTableName('archive');
        $revisionTable = $project->getTableName('revision');

        $sql = "
            -- Revision counts.
            (SELECT 'deleted' AS `key`, COUNT(ar_id) AS val FROM $archiveTable
                WHERE ar_actor = :actorId
            ) UNION (
            SELECT 'live' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                WHERE rev_actor = :actorId
            ) UNION (
            SELECT 'day' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                WHERE rev_actor = :actorId AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
            ) UNION (
            SELECT 'week' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                WHERE rev_actor = :actorId AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 WEEK)
            ) UNION (
            SELECT 'month' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                WHERE rev_actor = :actorId AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
            ) UNION (
            SELECT 'year' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                WHERE rev_actor = :actorId AND rev_timestamp >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
            ) UNION (
            SELECT 'minor' AS `key`, COUNT(rev_id) AS val FROM $revisionTable
                WHERE rev_actor = :actorId AND rev_minor_edit = 1

            -- Page counts.
            ) UNION (
            SELECT 'edited-live' AS `key`, COUNT(DISTINCT rev_page) AS `val`
                FROM $revisionTable
                WHERE rev_actor = :actorId
            ) UNION (
            SELECT 'edited-deleted' AS `key`, COUNT(DISTINCT ar_page_id) AS `val`
                FROM $archiveTable
                WHERE ar_actor = :actorId
            ) UNION (
            SELECT 'created-live' AS `key`, COUNT(DISTINCT rev_page) AS `val`
                FROM $revisionTable
                WHERE rev_actor = :actorId AND rev_parent_id = 0
            ) UNION (
            SELECT 'created-deleted' AS `key`, COUNT(DISTINCT ar_page_id) AS `val`
                FROM $archiveTable
                WHERE ar_actor = :actorId AND ar_parent_id = 0
            )
        ";

        $resultQuery = $this->executeProjectsQuery($sql, [
            'actorId' => $user->getActorId($project),
        ]);

        $revisionCounts = [];
        while ($result = $resultQuery->fetch()) {
            $revisionCounts[$result['key']] = (int)$result['val'];
        }

        // Cache and return.
        return $this->setCache($cacheKey, $revisionCounts);
    }

    /**
     * Get log totals for a user.
     * @param Project $project The project.
     * @param User $user The user.
     * @return integer[] Keys are "<log>-<action>" strings, values are counts.
     */
    public function getLogCounts(Project $project, User $user): array
    {
        // Set up cache.
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_logcounts');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }

        // Query.
        $loggingTable = $this->getTableName($project->getDatabaseName(), 'logging');
        $sql = "
        (SELECT CONCAT(log_type, '-', log_action) AS source, COUNT(log_id) AS value
            FROM $loggingTable
            WHERE log_actor = :actorId
            GROUP BY log_type, log_action
        )";

        $results = $this->executeProjectsQuery($sql, [
            'actorId' => $user->getActorId($project),
        ])->fetchAll();

        $logCounts = array_combine(
            array_map(function ($e) {
                return $e['source'];
            }, $results),
            array_map(function ($e) {
                return (int)$e['value'];
            }, $results)
        );

        // Make sure there is some value for each of the wanted counts.
        $requiredCounts = [
            'thanks-thank',
            'review-approve',
            'newusers-create2',
            'newusers-byemail',
            'patrol-patrol',
            'block-block',
            'block-reblock',
            'block-unblock',
            'protect-protect',
            'protect-modify',
            'protect-unprotect',
            'rights-rights',
            'move-move',
            'delete-delete',
            'delete-revision',
            'delete-restore',
            'import-import',
            'import-interwiki',
            'import-upload',
            'upload-upload',
            'upload-overwrite',
            'abusefilter-modify',
            'merge-merge',
        ];
        foreach ($requiredCounts as $req) {
            if (!isset($logCounts[$req])) {
                $logCounts[$req] = 0;
            }
        }

        // Add Commons upload count, if applicable.
        $logCounts['files_uploaded_commons'] = 0;
        if ($this->isLabs() && !$user->isAnon()) {
            $sql = "SELECT COUNT(log_id)
                    FROM commonswiki_p.logging_userindex
                    JOIN commonswiki_p.actor ON actor_id = log_actor
                    WHERE log_type = 'upload' AND log_action = 'upload'
                        AND actor_name = :username";
            $resultQuery = $this->executeProjectsQuery($sql, [
                'username' => $user->getUsername(),
            ]);
            $logCounts['files_uploaded_commons'] = (int)$resultQuery->fetchColumn();
        }

        // Cache and return.
        return $this->setCache($cacheKey, $logCounts);
    }

    /**
     * Get the IDs and timestamps of the latest edit and logged action by the given user.
     * @param Project $project
     * @param User $user
     * @return string[] With keys 'rev_first', 'rev_latest', 'log_latest'.
     */
    public function getFirstAndLatestActions(Project $project, User $user): array
    {
        $loggingTable = $project->getTableName('logging', 'userindex');
        $revisionTable = $project->getTableName('revision');

        $sql = "(
                    SELECT 'rev_first' AS `key`, rev_id AS `id`,
                        rev_timestamp AS `timestamp`, NULL as `type`
                    FROM $revisionTable
                    WHERE rev_actor = :actorId
                    LIMIT 1
                ) UNION (
                    SELECT 'rev_latest' AS `key`, rev_id AS `id`,
                        rev_timestamp AS `timestamp`, NULL as `type`
                    FROM $revisionTable
                    WHERE rev_actor = :actorId
                    ORDER BY rev_timestamp DESC LIMIT 1
                ) UNION (
                    SELECT 'log_latest' AS `key`, log_id AS `id`,
                        log_timestamp AS `timestamp`, log_type AS `type`
                    FROM $loggingTable
                    WHERE log_actor = :actorId
                    ORDER BY log_timestamp DESC LIMIT 1
                )";

        $resultQuery = $this->executeProjectsQuery($sql, [
            'actorId' => $user->getActorId($project),
        ]);

        $actions = [];
        while ($result = $resultQuery->fetch()) {
            $actions[$result['key']] = [
                'id' => $result['id'],
                'timestamp' => $result['timestamp'],
                'type' => $result['type'],
            ];
        }

        return $actions;
    }

    /**
     * Get data for all blocks set on the given user.
     * @param Project $project
     * @param User $user
     * @return array
     */
    public function getBlocksReceived(Project $project, User $user): array
    {
        $loggingTable = $this->getTableName($project->getDatabaseName(), 'logging', 'logindex');
        $sql = "SELECT log_action, log_timestamp, log_params FROM $loggingTable
                WHERE log_type = 'block'
                AND log_action IN ('block', 'reblock', 'unblock')
                AND log_timestamp > 0
                AND log_title = :username
                AND log_namespace = 2
                ORDER BY log_timestamp ASC";
        $username = str_replace(' ', '_', $user->getUsername());

        return $this->executeProjectsQuery($sql, [
            'username' => $username,
        ])->fetchAll();
    }

    /**
     * Get a user's edit count for each project.
     * @see EditCounterRepository::globalEditCountsFromCentralAuth()
     * @see EditCounterRepository::globalEditCountsFromDatabases()
     * @param User $user The user.
     * @param Project $project The project to start from.
     * @return mixed[] Elements are arrays with 'project' (Project), and 'total' (int).
     */
    public function globalEditCounts(User $user, Project $project): array
    {
        // Get the edit counts from CentralAuth or database.
        $editCounts = $this->globalEditCountsFromCentralAuth($user, $project);
        if (false === $editCounts) {
            $editCounts = $this->globalEditCountsFromDatabases($user, $project);
        }

        // Pre-populate all projects' metadata, to prevent each project call from fetching it.
        $project->getRepository()->getAll();
        $project->getRepository()->/** @scrutinizer ignore-call */ getAll();

        // Compile the output.
        $out = [];
        foreach ($editCounts as $editCount) {
            $out[] = [
                'project' => ProjectRepository::getProject($editCount['dbName'], $this->container),
                'total' => $editCount['total'],
            ];
        }
        return $out;
    }

    /**
     * Get a user's total edit count on one or more project.
     * Requires the CentralAuth extension to be installed on the project.
     *
     * @param User $user The user.
     * @param Project $project The project to start from.
     * @return mixed[]|false Elements are arrays with 'dbName' (string), and 'total' (int). False for logged out users.
     */
    protected function globalEditCountsFromCentralAuth(User $user, Project $project)
    {
        if (true === $user->isAnon()) {
            return false;
        }

        // Set up cache.
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_globaleditcounts');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }

        $this->log->debug(__METHOD__." Getting global edit counts from for ".$user->getUsername());

        // Load all projects, so it doesn't have to request metadata about each one as it goes.
        $project->getRepository()->getAll();

        $api = $this->getMediawikiApi($project);
        $params = [
            'meta' => 'globaluserinfo',
            'guiprop' => 'editcount|merged',
            'guiuser' => $user->getUsername(),
        ];
        $query = new SimpleRequest('query', $params);
        $result = $api->getRequest($query);
        if (!isset($result['query']['globaluserinfo']['merged'])) {
            return [];
        }
        $out = [];
        foreach ($result['query']['globaluserinfo']['merged'] as $result) {
            $out[] = [
                'dbName' => $result['wiki'],
                'total' => $result['editcount'],
            ];
        }

        // Cache and return.
        return $this->setCache($cacheKey, $out);
    }

    /**
     * Get total edit counts from all projects for this user.
     * @param User $user The user.
     * @param Project $project The project to start from.
     * @return mixed[] Elements are arrays with 'dbName' (string), and 'total' (int).
     * @see EditCounterRepository::globalEditCountsFromCentralAuth()
     */
    protected function globalEditCountsFromDatabases(User $user, Project $project): array
    {
        $this->log->debug(__METHOD__." Getting global edit counts for ".$user->getUsername());
        $allProjects = $project->getRepository()->getAll();
        $topEditCounts = [];
        foreach ($allProjects as $projectMeta) {
            $revisionTableName = $this->getTableName($projectMeta['dbName'], 'revision');
            $actorTable = $this->getTableName($projectMeta['dbName'], 'actor', 'revision');

            $sql = "SELECT COUNT(rev_id)
                FROM $revisionTableName
                JOIN $actorTable ON rev_actor = actor_id
                WHERE actor_name = :actor";

            $resultQuery = $this->executeProjectsQuery($sql, [
                'actor' => $user->getUsername(),
            ]);
            $total = (int)$resultQuery->fetchColumn();
            $topEditCounts[] = [
                'dbName' => $projectMeta['dbName'],
                'total' => $total,
            ];
        }
        return $topEditCounts;
    }

    /**
     * Needed because we can't cache objects (Project in this case).
     * @see self::getProjectsWikiEdits()
     * @param string[] $databases
     * @return mixed[] Keys are database names, values are Project objects.
     */
    private function formatProjectsWikiEdits(array $databases): array
    {
        $projects = [];
        foreach ($databases as $database) {
            $projects[$database] = ProjectRepository::getProject($database, $this->container);
        }
        return $projects;
    }

    /**
     * Get Projects that the user has made at least one edit on.
     * @param User $user
     * @return mixed[] Keys are database names, values are Projects.
     */
    public function getProjectsWithEdits(User $user): array
    {
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_projects_with_edits');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->formatProjectsWikiEdits($this->cache->getItem($cacheKey)->get());
        }

        $projectRepo = new ProjectRepository();
        $projectRepo->setContainer($this->container);
        $allProjects = $projectRepo->getAll();
        $databases = [];

        foreach ($allProjects as $projectMeta) {
            $actorTable = $this->getTableName($projectMeta['dbName'], 'actor', 'revision');
            $sql = "SELECT 1 FROM $actorTable WHERE actor_name = :actor";

            $resultQuery = $this->executeProjectsQuery($sql, [
                'actor' => $user->getUsername(),
            ]);
            if ($resultQuery->fetch()) {
                $databases[] = $projectMeta['dbName'];
            }
        }

        $projects = $this->setCache($cacheKey, $databases);

        return $this->formatProjectsWikiEdits($projects);
    }

    /**
     * Get the given user's total edit counts per namespace on the given project.
     * @param Project $project The project.
     * @param User $user The user.
     * @return array Array keys are namespace IDs, values are the edit counts.
     */
    public function getNamespaceTotals(Project $project, User $user): array
    {
        // Cache?
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_namespacetotals');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }

        // Query.
        $revisionTable = $this->getTableName($project->getDatabaseName(), 'revision');
        $pageTable = $this->getTableName($project->getDatabaseName(), 'page');
        $sql = "SELECT page_namespace, COUNT(rev_id) AS total
            FROM $pageTable p JOIN $revisionTable r ON (r.rev_page = p.page_id)
            WHERE r.rev_actor = :actorId
            GROUP BY page_namespace";

        $results = $this->executeProjectsQuery($sql, [
            'actorId' => $user->getActorId($project),
        ])->fetchAll();

        $namespaceTotals = array_combine(array_map(function ($e) {
            return $e['page_namespace'];
        }, $results), array_map(function ($e) {
            return (int)$e['total'];
        }, $results));

        // Cache and return.
        return $this->setCache($cacheKey, $namespaceTotals);
    }

    /**
     * Get revisions by this user across the given Projects.
     * @param Project[] $projects The projects.
     * @param User $user The user.
     * @param int $limit The maximum number of revisions to fetch from each project.
     * @param int $offset Offset results by this number of rows.
     * @return array|mixed
     */
    public function getRevisions(array $projects, User $user, int $limit = 30, int $offset = 0)
    {
        // Check cache.
        $cacheKey = $this->getCacheKey('ec_globalcontribs.'.$user->getCacheKey().'.'.$limit.'.'.$offset);
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }

        $username = $this->getProjectsConnection()->quote($user->getUsername(), \PDO::PARAM_STR);

        // Assemble queries.
        $queries = [];
        foreach ($projects as $project) {
            $revisionTable = $project->getTableName('revision');
            $pageTable = $project->getTableName('page');
            $commentTable = $project->getTableName('comment', 'revision');
            $actorId = $user->getActorId($project);
            $sql = "SELECT
                    '".$project->getDatabaseName()."' AS project_name,
                    revs.rev_id AS id,
                    revs.rev_timestamp AS timestamp,
                    UNIX_TIMESTAMP(revs.rev_timestamp) AS unix_timestamp,
                    revs.rev_minor_edit AS minor,
                    revs.rev_deleted AS deleted,
                    revs.rev_len AS length,
                    (CAST(revs.rev_len AS SIGNED) - IFNULL(parentrevs.rev_len, 0)) AS length_change,
                    revs.rev_parent_id AS parent_id,
                    $username AS username,
                    page.page_title,
                    page.page_namespace,
                    comment_text AS `comment`
                FROM $revisionTable AS revs
                    JOIN $pageTable AS page ON (rev_page = page_id)
                    LEFT JOIN $revisionTable AS parentrevs ON (revs.rev_parent_id = parentrevs.rev_id)
                    LEFT OUTER JOIN $commentTable ON revs.rev_comment_id = comment_id
                WHERE revs.rev_actor = $actorId";
            $queries[] = $sql;
        }
        $sql = "SELECT * FROM ((\n" . join("\n) UNION (\n", $queries) . ")) a ORDER BY timestamp DESC LIMIT $limit";

        if (is_numeric($offset)) {

            $sql .= " OFFSET $offset";
        }

        $revisions = $this->executeProjectsQuery($sql)->fetchAll();

        // Cache and return.
        return $this->setCache($cacheKey, $revisions);
    }

    /**
     * Get data for a bar chart of monthly edit totals per namespace.
     * @param Project $project The project.
     * @param User $user The user.
     * @return string[] [
     *                      [
     *                          'year' => <year>,
     *                          'month' => <month>,
     *                          'page_namespace' => <namespace>,
     *                          'count' => <count>,
     *                      ],
     *                      ...
     *                  ]
     */
    public function getMonthCounts(Project $project, User $user): array
    {
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_monthcounts');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }

        $revisionTable = $project->getTableName('revision');
        $pageTable = $project->getTableName('page');
        $sql =
            "SELECT "
            . "     YEAR(rev_timestamp) AS `year`,"
            . "     MONTH(rev_timestamp) AS `month`,"
            . "     page_namespace,"
            . "     COUNT(rev_id) AS `count` "
            .  " FROM $revisionTable JOIN $pageTable ON (rev_page = page_id)"
            . " WHERE rev_actor = :actorId"
            . " GROUP BY YEAR(rev_timestamp), MONTH(rev_timestamp), page_namespace";

        $totals = $this->executeProjectsQuery($sql, [
            'actorId' => $user->getActorId($project),
        ])->fetchAll();

        // Cache and return.
        return $this->setCache($cacheKey, $totals);
    }

    /**
     * Get data for the timecard chart, with totals grouped by day and to the nearest two-hours.
     * @param Project $project
     * @param User $user
     * @return string[]
     */
    public function getTimeCard(Project $project, User $user): array
    {
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_timecard');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }

        $hourInterval = 2;
        $xCalc = "ROUND(HOUR(rev_timestamp)/$hourInterval) * $hourInterval";
        $revisionTable = $this->getTableName($project->getDatabaseName(), 'revision');
        $sql = "SELECT "
            . "     DAYOFWEEK(rev_timestamp) AS `day_of_week`, "
            . "     $xCalc AS `hour`, "
            . "     COUNT(rev_id) AS `value` "
            . " FROM $revisionTable"
            . " WHERE rev_actor = :actorId"
            . " GROUP BY DAYOFWEEK(rev_timestamp), $xCalc ";

        $totals = $this->executeProjectsQuery($sql, [
            'actorId' => $user->getActorId($project),
        ])->fetchAll();

        // Cache and return.
        return $this->setCache($cacheKey, $totals);
    }

    /**
     * Get various data about edit sizes of the past 5,000 edits.
     * Will cache the result for 10 minutes.
     * @param Project $project The project.
     * @param User $user The user.
     * @return string[] Values with for keys 'average_size',
     *                  'small_edits' and 'large_edits'
     */
    public function getEditSizeData(Project $project, User $user): array
    {
        // Set up cache.
        $cacheKey = $this->getCacheKey(func_get_args(), 'ec_editsizes');
        if ($this->cache->hasItem($cacheKey)) {
            return $this->cache->getItem($cacheKey)->get();
        }

        // Prepare the queries and execute them.
        $revisionTable = $project->getTableName('revision');
        $sql = "SELECT AVG(sizes.size) AS average_size,
                COUNT(CASE WHEN sizes.size < 20 THEN 1 END) AS small_edits,
                COUNT(CASE WHEN sizes.size > 1000 THEN 1 END) AS large_edits
                FROM (
                    SELECT (CAST(revs.rev_len AS SIGNED) - IFNULL(parentrevs.rev_len, 0)) AS size
                    FROM $revisionTable AS revs
                    LEFT JOIN $revisionTable AS parentrevs ON (revs.rev_parent_id = parentrevs.rev_id)
                    WHERE revs.rev_actor = :actorId
                    ORDER BY revs.rev_timestamp DESC
                    LIMIT 5000
                ) sizes";
        $results = $this->executeProjectsQuery($sql, [
            'actorId' => $user->getActorId($project),
        ])->fetch();

        // Cache and return.
        return $this->setCache($cacheKey, $results);
    }

    /**
     * Get the number of edits this user made using semi-automated tools.
     * @param Project $project
     * @param User $user
     * @return int Result of query, see below.
     */
    public function countAutomatedEdits(Project $project, User $user): int
    {
        $autoEditsRepo = new AutoEditsRepository();
        $autoEditsRepo->setContainer($this->container);
        return $autoEditsRepo->countAutomatedEdits($project, $user);
    }
}

x-tools / xtools

Push — master ( b25a2f...9287df )

globalEditCountsFromCentralAuth() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like
