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