maintenance/removeUnusedAccounts.php

   1 <?php
   2 /**
   3  * Remove unused user accounts from the database
   4  * An unused account is one which has made no edits
   5  *
   6  * This program is free software; you can redistribute it and/or modify
   7  * it under the terms of the GNU General Public License as published by
   8  * the Free Software Foundation; either version 2 of the License, or
   9  * (at your option) any later version.
  10  *
  11  * This program is distributed in the hope that it will be useful,
  12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  14  * GNU General Public License for more details.
  15  *
  16  * You should have received a copy of the GNU General Public License along
  17  * with this program; if not, write to the Free Software Foundation, Inc.,
  18  * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
  19  * http://www.gnu.org/copyleft/gpl.html
  20  *
  21  * @file
  22  * @ingroup Maintenance
  23  * @author Rob Church <robchur@gmail.com>
  24  */
  25
  26 use MediaWiki\Maintenance\Maintenance;
  27 use MediaWiki\User\UserIdentity;
  28
  29 // @codeCoverageIgnoreStart
  30 require_once __DIR__ . '/Maintenance.php';
  31 // @codeCoverageIgnoreEnd
  32
  33 /**
  34  * Maintenance script that removes unused user accounts from the database.
  35  *
  36  * @ingroup Maintenance
  37  */
  38 class RemoveUnusedAccounts extends Maintenance {
  39         public function __construct() {
  40                 parent::__construct();
  41                 $this->addOption( 'delete', 'Actually delete the account' );
  42                 $this->addOption( 'ignore-groups', 'List of comma-separated groups to exclude', false, true );
  43                 $this->addOption( 'ignore-touched', 'Skip accounts touched in last N days', false, true );
  44         }
  45
  46         public function execute() {
  47                 $services = $this->getServiceContainer();
  48                 $userFactory = $services->getUserFactory();
  49                 $userGroupManager = $services->getUserGroupManager();
  50                 $this->output( "Remove unused accounts\n\n" );
  51
  52                 # Do an initial scan for inactive accounts and report the result
  53                 $this->output( "Checking for unused user accounts...\n" );
  54                 $delUser = [];
  55                 $delActor = [];
  56                 $dbr = $this->getReplicaDB();
  57                 $res = $dbr->newSelectQueryBuilder()
  58                         ->select( [ 'user_id', 'user_name', 'user_touched', 'actor_id' ] )
  59                         ->from( 'user' )
  60                         ->leftJoin( 'actor', null, 'user_id = actor_user' )
  61                         ->caller( __METHOD__ )->fetchResultSet();
  62                 if ( $this->hasOption( 'ignore-groups' ) ) {
  63                         $excludedGroups = explode( ',', $this->getOption( 'ignore-groups' ) );
  64                 } else {
  65                         $excludedGroups = [];
  66                 }
  67                 $touched = $this->getOption( 'ignore-touched', "1" );
  68                 if ( !ctype_digit( $touched ) ) {
  69                         $this->fatalError( "Please put a valid positive integer on the --ignore-touched parameter." );
  70                 }
  71                 $touchedSeconds = 86400 * $touched;
  72                 foreach ( $res as $row ) {
  73                         # Check the account, but ignore it if it's within a $excludedGroups
  74                         # group or if it's touched within the $touchedSeconds seconds.
  75                         $instance = $userFactory->newFromId( $row->user_id );
  76                         if ( count(
  77                                 array_intersect( $userGroupManager->getUserEffectiveGroups( $instance ), $excludedGroups ) ) == 0
  78                                 && $this->isInactiveAccount( $instance, $row->actor_id ?? null, true )
  79                                 && wfTimestamp( TS_UNIX, $row->user_touched ) < wfTimestamp( TS_UNIX, time() - $touchedSeconds
  80                                 )
  81                         ) {
  82                                 # Inactive; print out the name and flag it
  83                                 $delUser[] = $row->user_id;
  84                                 if ( isset( $row->actor_id ) && $row->actor_id ) {
  85                                         $delActor[] = $row->actor_id;
  86                                 }
  87                                 $this->output( $row->user_name . "\n" );
  88                         }
  89                 }
  90                 $count = count( $delUser );
  91                 $this->output( "...found {$count}.\n" );
  92
  93                 # If required, go back and delete each marked account
  94                 if ( $count > 0 && $this->hasOption( 'delete' ) ) {
  95                         $this->output( "\nDeleting unused accounts..." );
  96                         $dbw = $this->getPrimaryDB();
  97                         $dbw->newDeleteQueryBuilder()
  98                                 ->deleteFrom( 'user' )
  99                                 ->where( [ 'user_id' => $delUser ] )
 100                                 ->caller( __METHOD__ )->execute();
 101                         # Keep actor rows referenced from block
 102                         $keep = $dbw->newSelectQueryBuilder()
 103                                 ->select( 'bl_by_actor' )
 104                                 ->from( 'block' )
 105                                 ->where( [ 'bl_by_actor' => $delActor ] )
 106                                 ->caller( __METHOD__ )->fetchFieldValues();
 107                         $del = array_diff( $delActor, $keep );
 108                         if ( $del ) {
 109                                 $dbw->newDeleteQueryBuilder()
 110                                         ->deleteFrom( 'actor' )
 111                                         ->where( [ 'actor_id' => $del ] )
 112                                         ->caller( __METHOD__ )->execute();
 113                         }
 114                         if ( $keep ) {
 115                                 $dbw->newUpdateQueryBuilder()
 116                                         ->update( 'actor' )
 117                                         ->set( [ 'actor_user' => null ] )
 118                                         ->where( [ 'actor_id' => $keep ] )
 119                                         ->caller( __METHOD__ )
 120                                         ->execute();
 121                         }
 122                         $dbw->newDeleteQueryBuilder()
 123                                 ->deleteFrom( 'user_groups' )
 124                                 ->where( [ 'ug_user' => $delUser ] )
 125                                 ->caller( __METHOD__ )->execute();
 126                         $dbw->newDeleteQueryBuilder()
 127                                 ->deleteFrom( 'user_former_groups' )
 128                                 ->where( [ 'ufg_user' => $delUser ] )
 129                                 ->caller( __METHOD__ )->execute();
 130                         $dbw->newDeleteQueryBuilder()
 131                                 ->deleteFrom( 'user_properties' )
 132                                 ->where( [ 'up_user' => $delUser ] )
 133                                 ->caller( __METHOD__ )->execute();
 134                         $dbw->newDeleteQueryBuilder()
 135                                 ->deleteFrom( 'logging' )
 136                                 ->where( [ 'log_actor' => $delActor ] )
 137                                 ->caller( __METHOD__ )->execute();
 138                         $dbw->newDeleteQueryBuilder()
 139                                 ->deleteFrom( 'recentchanges' )
 140                                 ->where( [ 'rc_actor' => $delActor ] )
 141                                 ->caller( __METHOD__ )->execute();
 142                         $this->output( "done.\n" );
 143                         # Update the site_stats.ss_users field
 144                         $users = $dbw->newSelectQueryBuilder()
 145                                 ->select( 'COUNT(*)' )
 146                                 ->from( 'user' )
 147                                 ->caller( __METHOD__ )->fetchField();
 148                         $dbw->newUpdateQueryBuilder()
 149                                 ->update( 'site_stats' )
 150                                 ->set( [ 'ss_users' => $users ] )
 151                                 ->where( [ 'ss_row_id' => 1 ] )
 152                                 ->caller( __METHOD__ )
 153                                 ->execute();
 154                 } elseif ( $count > 0 ) {
 155                         $this->output( "\nRun the script again with --delete to remove them from the database.\n" );
 156                 }
 157                 $this->output( "\n" );
 158         }
 159
 160         /**
 161          * Could the specified user account be deemed inactive?
 162          * (No edits, no deleted edits, no log entries, no current/old uploads)
 163          *
 164          * @param UserIdentity $user
 165          * @param int|null $actor User's actor ID
 166          * @param bool $primary Perform checking on the primary DB
 167          * @return bool
 168          */
 169         private function isInactiveAccount( $user, $actor, $primary = false ) {
 170                 if ( $actor === null ) {
 171                         // There's no longer a way for a user to be active in any of
 172                         // these tables without having an actor ID. The only way to link
 173                         // to a user row is via an actor row.
 174                         return true;
 175                 }
 176
 177                 $dbo = $primary ? $this->getPrimaryDB() : $this->getReplicaDB();
 178                 $checks = [
 179                         'archive' => 'ar',
 180                         'image' => 'img',
 181                         'oldimage' => 'oi',
 182                         'filearchive' => 'fa',
 183                         'revision' => 'rev',
 184                 ];
 185                 $count = 0;
 186
 187                 $this->beginTransaction( $dbo, __METHOD__ );
 188                 foreach ( $checks as $table => $prefix ) {
 189                         $count += (int)$dbo->newSelectQueryBuilder()
 190                                 ->select( 'COUNT(*)' )
 191                                 ->from( $table )
 192                                 ->where( [ "{$prefix}_actor" => $actor ] )
 193                                 ->caller( __METHOD__ )
 194                                 ->fetchField();
 195                 }
 196
 197                 $count += (int)$dbo->newSelectQueryBuilder()
 198                         ->select( 'COUNT(*)' )
 199                         ->from( 'logging' )
 200                         ->where( [ 'log_actor' => $actor, $dbo->expr( 'log_type', '!=', 'newusers' ) ] )
 201                         ->caller( __METHOD__ )->fetchField();
 202
 203                 $this->commitTransaction( $dbo, __METHOD__ );
 204
 205                 return $count == 0;
 206         }
 207 }
 208
 209 // @codeCoverageIgnoreStart
 210 $maintClass = RemoveUnusedAccounts::class;
 211 require_once RUN_MAINTENANCE_IF_MAIN;
 212 // @codeCoverageIgnoreEnd