<commit_before># frozen_string_literal: true

require 'tty-prompt'
require_relative '../../config/boot'
require_relative '../../config/environment'
require_relative 'cli_helper'

module Mastodon
  class MaintenanceCLI < Thor
    include CLIHelper

    def self.exit_on_failure?
      true
    end

    MIN_SUPPORTED_VERSION = 2019_10_01_213028
    MAX_SUPPORTED_VERSION = 2021_03_08_133107

    # Stubs to enjoy ActiveRecord queries while not depending on a particular
    # version of the code/database

    class Status < ApplicationRecord; end
    class StatusPin < ApplicationRecord; end
    class Poll < ApplicationRecord; end
    class Report < ApplicationRecord; end
    class Tombstone < ApplicationRecord; end
    class Favourite < ApplicationRecord; end
    class Follow < ApplicationRecord; end
    class FollowRequest < ApplicationRecord; end
    class Block < ApplicationRecord; end
    class Mute < ApplicationRecord; end
    class AccountIdentityProof < ApplicationRecord; end
    class AccountModerationNote < ApplicationRecord; end
    class AccountPin < ApplicationRecord; end
    class ListAccount < ApplicationRecord; end
    class PollVote < ApplicationRecord; end
    class Mention < ApplicationRecord; end
    class AccountDomainBlock < ApplicationRecord; end
    class AnnouncementReaction < ApplicationRecord; end
    class FeaturedTag < ApplicationRecord; end
    class CustomEmoji < ApplicationRecord; end
    class CustomEmojiCategory < ApplicationRecord; end
    class Bookmark < ApplicationRecord; end
    class WebauthnCredential < ApplicationRecord; end

    class PreviewCard < ApplicationRecord
      self.inheritance_column = false
    end

    class MediaAttachment < ApplicationRecord
      self.inheritance_column = nil
    end

    class AccountStat < ApplicationRecord
      belongs_to :account, inverse_of: :account_stat
    end

    # Dummy class, to make migration possible across version changes
    class Account < ApplicationRecord
      has_one :user, inverse_of: :account
      has_one :account_stat, inverse_of: :account

      scope :local, -> { where(domain: nil) }

      def local?
        domain.nil?
      end

      def acct
        local? ? username : "#{username}@#{domain}"
      end

      # This is a duplicate of the AccountMerging concern because we need it to
      # be independent from code version.
      def merge_with!(other_account)
        # Since it's the same remote resource, the remote resource likely
        # already believes we are following/blocking, so it's safe to
        # re-attribute the relationships too. However, during the presence
        # of the index bug users could have *also* followed the reference
        # account already, therefore mass update will not work and we need
        # to check for (and skip past) uniqueness errors

        owned_classes = [
          Status, StatusPin, MediaAttachment, Poll, Report, Tombstone, Favourite,
          Follow, FollowRequest, Block, Mute, AccountIdentityProof,
          AccountModerationNote, AccountPin, AccountStat, ListAccount,
          PollVote, Mention
        ]
        owned_classes << AccountDeletionRequest if ActiveRecord::Base.connection.table_exists?(:account_deletion_requests)
        owned_classes << AccountNote if ActiveRecord::Base.connection.table_exists?(:account_notes)

        owned_classes.each do |klass|
          klass.where(account_id: other_account.id).find_each do |record|
            begin
              record.update_attribute(:account_id, id)
            rescue ActiveRecord::RecordNotUnique
              next
            end
          end
        end

        target_classes = [Follow, FollowRequest, Block, Mute, AccountModerationNote, AccountPin]
        target_classes << AccountNote if ActiveRecord::Base.connection.table_exists?(:account_notes)

        target_classes.each do |klass|
          klass.where(target_account_id: other_account.id).find_each do |record|
            begin
              record.update_attribute(:target_account_id, id)
            rescue ActiveRecord::RecordNotUnique
              next
            end
          end
        end
      end
    end

    class User < ApplicationRecord
      belongs_to :account, inverse_of: :user
    end

    desc 'fix-duplicates', 'Fix duplicates in database and rebuild indexes'
    long_desc <<~LONG_DESC
      Delete or merge duplicate accounts, statuses, emojis, etc. and rebuild indexes.

      This is useful if your database indexes are corrupted because of issues such as https://wiki.postgresql.org/wiki/Locale_data_changes

      Mastodon has to be stopped to run this task, which will take a long time and may be destructive.
    LONG_DESC
    def fix_duplicates
      @prompt = TTY::Prompt.new

      if ActiveRecord::Migrator.current_version < MIN_SUPPORTED_VERSION
        @prompt.warn 'Your version of the database schema is too old and is not supported by this script.'
        @prompt.warn 'Please update to at least Mastodon 3.0.0 before running this script.'
        exit(1)
      elsif ActiveRecord::Migrator.current_version > MAX_SUPPORTED_VERSION
        @prompt.warn 'Your version of the database schema is more recent than this script, this may cause unexpected errors.'
        exit(1) unless @prompt.yes?('Continue anyway?')
      end

      @prompt.warn 'This task will take a long time to run and is potentially destructive.'
      @prompt.warn 'Please make sure to stop Mastodon and have a backup.'
      exit(1) unless @prompt.yes?('Continue?')

      deduplicate_users!
      deduplicate_account_domain_blocks!
      deduplicate_account_identity_proofs!
      deduplicate_announcement_reactions!
      deduplicate_conversations!
      deduplicate_custom_emojis!
      deduplicate_custom_emoji_categories!
      deduplicate_domain_allows!
      deduplicate_domain_blocks!
      deduplicate_unavailable_domains!
      deduplicate_email_domain_blocks!
      deduplicate_media_attachments!
      deduplicate_preview_cards!
      deduplicate_statuses!
      deduplicate_accounts!
      deduplicate_tags!
      deduplicate_webauthn_credentials!

      Rails.cache.clear

      @prompt.say 'Finished!'
    end

    private

    def deduplicate_accounts!
      remove_index_if_exists!(:accounts, 'index_accounts_on_username_and_domain_lower')

      @prompt.say 'Deduplicating accounts… for local accounts, you will be asked to chose which account to keep unchanged.'

      find_duplicate_accounts.each do |row|
        accounts = Account.where(id: row['ids'].split(',')).to_a

        if accounts.first.local?
          deduplicate_local_accounts!(accounts)
        else
          deduplicate_remote_accounts!(accounts)
        end
      end

      @prompt.say 'Restoring index_accounts_on_username_and_domain_lower…'
      if ActiveRecord::Migrator.current_version < 20200620164023
        ActiveRecord::Base.connection.add_index :accounts, 'lower (username), lower(domain)', name: 'index_accounts_on_username_and_domain_lower', unique: true
      else
        ActiveRecord::Base.connection.add_index :accounts, "lower (username), COALESCE(lower(domain), '')", name: 'index_accounts_on_username_and_domain_lower', unique: true
      end
    end

    def deduplicate_users!
      remove_index_if_exists!(:users, 'index_users_on_confirmation_token')
      remove_index_if_exists!(:users, 'index_users_on_email')
      remove_index_if_exists!(:users, 'index_users_on_remember_token')
      remove_index_if_exists!(:users, 'index_users_on_reset_password_token')

      @prompt.say 'Deduplicating user records…'

      # Deduplicating email
      ActiveRecord::Base.connection.select_all("SELECT string_agg(id::text, ',') AS ids FROM users GROUP BY email HAVING count(*) > 1").each do |row|
        users = User.where(id: row['ids'].split(',')).sort_by(&:updated_at).reverse
        ref_user = users.shift
        @prompt.warn "Multiple users registered with e-mail address #{ref_user.email}."
        @prompt.warn "e-mail will be disabled for the following accounts: #{user.map(&:account).map(&:acct).join(', ')}"
        @prompt.warn 'Please reach out to them and set another address with `tootctl account modify` or delete them.'

        i = 0
        users.each do |user|
          user.update!(email: "#{i} " + user.email)
        end
      end

      ActiveRecord::Base.connection.select_all("SELECT string_agg(id::text, ',') AS ids FROM users WHERE confirmation_token IS NOT NULL GROUP BY confirmation_token HAVING count(*) > 1").each do |row|<commit_msg>Fix maintenance script not re-indexing some indexes on textual values (#15515)<commit_after># frozen_string_literal: true

require 'tty-prompt'
require_relative '../../config/boot'
require_relative '../../config/environment'
require_relative 'cli_helper'

module Mastodon
  class MaintenanceCLI < Thor
    include CLIHelper

    def self.exit_on_failure?
      true
    end

    MIN_SUPPORTED_VERSION = 2019_10_01_213028
    MAX_SUPPORTED_VERSION = 2021_03_08_133107

    # Stubs to enjoy ActiveRecord queries while not depending on a particular
    # version of the code/database

    class Status < ApplicationRecord; end
    class StatusPin < ApplicationRecord; end
    class Poll < ApplicationRecord; end
    class Report < ApplicationRecord; end
    class Tombstone < ApplicationRecord; end
    class Favourite < ApplicationRecord; end
    class Follow < ApplicationRecord; end
    class FollowRequest < ApplicationRecord; end
    class Block < ApplicationRecord; end
    class Mute < ApplicationRecord; end
    class AccountIdentityProof < ApplicationRecord; end
    class AccountModerationNote < ApplicationRecord; end
    class AccountPin < ApplicationRecord; end
    class ListAccount < ApplicationRecord; end
    class PollVote < ApplicationRecord; end
    class Mention < ApplicationRecord; end
    class AccountDomainBlock < ApplicationRecord; end
    class AnnouncementReaction < ApplicationRecord; end
    class FeaturedTag < ApplicationRecord; end
    class CustomEmoji < ApplicationRecord; end
    class CustomEmojiCategory < ApplicationRecord; end
    class Bookmark < ApplicationRecord; end
    class WebauthnCredential < ApplicationRecord; end

    class PreviewCard < ApplicationRecord
      self.inheritance_column = false
    end

    class MediaAttachment < ApplicationRecord
      self.inheritance_column = nil
    end

    class AccountStat < ApplicationRecord
      belongs_to :account, inverse_of: :account_stat
    end

    # Dummy class, to make migration possible across version changes
    class Account < ApplicationRecord
      has_one :user, inverse_of: :account
      has_one :account_stat, inverse_of: :account

      scope :local, -> { where(domain: nil) }

      def local?
        domain.nil?
      end

      def acct
        local? ? username : "#{username}@#{domain}"
      end

      # This is a duplicate of the AccountMerging concern because we need it to
      # be independent from code version.
      def merge_with!(other_account)
        # Since it's the same remote resource, the remote resource likely
        # already believes we are following/blocking, so it's safe to
        # re-attribute the relationships too. However, during the presence
        # of the index bug users could have *also* followed the reference
        # account already, therefore mass update will not work and we need
        # to check for (and skip past) uniqueness errors

        owned_classes = [
          Status, StatusPin, MediaAttachment, Poll, Report, Tombstone, Favourite,
          Follow, FollowRequest, Block, Mute, AccountIdentityProof,
          AccountModerationNote, AccountPin, AccountStat, ListAccount,
          PollVote, Mention
        ]
        owned_classes << AccountDeletionRequest if ActiveRecord::Base.connection.table_exists?(:account_deletion_requests)
        owned_classes << AccountNote if ActiveRecord::Base.connection.table_exists?(:account_notes)

        owned_classes.each do |klass|
          klass.where(account_id: other_account.id).find_each do |record|
            begin
              record.update_attribute(:account_id, id)
            rescue ActiveRecord::RecordNotUnique
              next
            end
          end
        end

        target_classes = [Follow, FollowRequest, Block, Mute, AccountModerationNote, AccountPin]
        target_classes << AccountNote if ActiveRecord::Base.connection.table_exists?(:account_notes)

        target_classes.each do |klass|
          klass.where(target_account_id: other_account.id).find_each do |record|
            begin
              record.update_attribute(:target_account_id, id)
            rescue ActiveRecord::RecordNotUnique
              next
            end
          end
        end
      end
    end

    class User < ApplicationRecord
      belongs_to :account, inverse_of: :user
    end

    desc 'fix-duplicates', 'Fix duplicates in database and rebuild indexes'
    long_desc <<~LONG_DESC
      Delete or merge duplicate accounts, statuses, emojis, etc. and rebuild indexes.

      This is useful if your database indexes are corrupted because of issues such as https://wiki.postgresql.org/wiki/Locale_data_changes

      Mastodon has to be stopped to run this task, which will take a long time and may be destructive.
    LONG_DESC
    def fix_duplicates
      @prompt = TTY::Prompt.new

      if ActiveRecord::Migrator.current_version < MIN_SUPPORTED_VERSION
        @prompt.warn 'Your version of the database schema is too old and is not supported by this script.'
        @prompt.warn 'Please update to at least Mastodon 3.0.0 before running this script.'
        exit(1)
      elsif ActiveRecord::Migrator.current_version > MAX_SUPPORTED_VERSION
        @prompt.warn 'Your version of the database schema is more recent than this script, this may cause unexpected errors.'
        exit(1) unless @prompt.yes?('Continue anyway?')
      end

      @prompt.warn 'This task will take a long time to run and is potentially destructive.'
      @prompt.warn 'Please make sure to stop Mastodon and have a backup.'
      exit(1) unless @prompt.yes?('Continue?')

      deduplicate_users!
      deduplicate_account_domain_blocks!
      deduplicate_account_identity_proofs!
      deduplicate_announcement_reactions!
      deduplicate_conversations!
      deduplicate_custom_emojis!
      deduplicate_custom_emoji_categories!
      deduplicate_domain_allows!
      deduplicate_domain_blocks!
      deduplicate_unavailable_domains!
      deduplicate_email_domain_blocks!
      deduplicate_media_attachments!
      deduplicate_preview_cards!
      deduplicate_statuses!
      deduplicate_accounts!
      deduplicate_tags!
      deduplicate_webauthn_credentials!

      Scenic.database.refresh_materialized_view('instances', concurrently: true, cascade: false) if ActiveRecord::Migrator.current_version >= 2020_12_06_004238
      Rails.cache.clear

      @prompt.say 'Finished!'
    end

    private

    def deduplicate_accounts!
      remove_index_if_exists!(:accounts, 'index_accounts_on_username_and_domain_lower')

      @prompt.say 'Deduplicating accounts… for local accounts, you will be asked to chose which account to keep unchanged.'

      find_duplicate_accounts.each do |row|
        accounts = Account.where(id: row['ids'].split(',')).to_a

        if accounts.first.local?
          deduplicate_local_accounts!(accounts)
        else
          deduplicate_remote_accounts!(accounts)
        end
      end

      @prompt.say 'Restoring index_accounts_on_username_and_domain_lower…'
      if ActiveRecord::Migrator.current_version < 20200620164023
        ActiveRecord::Base.connection.add_index :accounts, 'lower (username), lower(domain)', name: 'index_accounts_on_username_and_domain_lower', unique: true
      else
        ActiveRecord::Base.connection.add_index :accounts, "lower (username), COALESCE(lower(domain), '')", name: 'index_accounts_on_username_and_domain_lower', unique: true
      end

      @prompt.say 'Reindexing textual indexes on accounts…'
      ActiveRecord::Base.connection.execute('REINDEX INDEX search_index;')
      ActiveRecord::Base.connection.execute('REINDEX INDEX index_accounts_on_uri;')
      ActiveRecord::Base.connection.execute('REINDEX INDEX index_accounts_on_url;')
    end

    def deduplicate_users!
      remove_index_if_exists!(:users, 'index_users_on_confirmation_token')
      remove_index_if_exists!(:users, 'index_users_on_email')
      remove_index_if_exists!(:users, 'index_users_on_remember_token')
      remove_index_if_exists!(:users, 'index_users_on_reset_password_token')

      @prompt.say 'Deduplicating user records…'

      # Deduplicating email
      ActiveRecord::Base.connection.select_all("SELECT string_agg(id::text, ',') AS ids FROM users GROUP BY email HAVING count(*) > 1").each do |row|
        users = User.where(id: row['ids'].split(',')).sort_by(&:updated_at).reverse
        ref_user = users.shift
        @prompt.warn "Multiple users registered with e-mail address #{ref_user.email}."
        @prompt.warn "e-mail will be disabled for the following accounts: #{user.map(&:account).map(&:acct).join(', ')}"
        @prompt.warn 'Please reach out to them and set another address with `tootctl account modify` or delete them.'

        i = 0
        users.each do |user|
          user.update!(email: "#{i} " + user.email)
        end
      end

      ActiveRecord::Base.connection.select_all("SELECT string_agg(id::text, ',') AS ids FROM users WHERE confirmation_token IS NOT NULL GROUP BY confirmation_token HAVING count(*) > 1").each do |row|
{'fraction_non_alphanumeric': 0.0732761071002217, 'fraction_numerical': 0.0068785174236825645, 'mean_word_length': 3.308596620132256, 'pattern_counts': {'":': 0, '<': 81, '<?xml version=': 0, '>': 12, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3722147', 'n_tokens_mistral': 4981, 'n_tokens_neox': 4741, 'n_words': 1380}