zammad/db/migrate/20180426134922_issue_1977_remove_invalid_user_foreign_keys.rb

class Issue1977RemoveInvalidUserForeignKeys < ActiveRecord::Migration[5.1]
  def change

    # return if it's a new setup
    return if !Setting.find_by(name: 'system_init_done')

    # cleanup
    OnlineNotification.joins('LEFT OUTER JOIN users ON online_notifications.user_id = users.id')
                      .where('users.id IS NULL')
                      .destroy_all

    RecentView.joins('LEFT OUTER JOIN users ON recent_views.created_by_id = users.id')
              .where('users.id IS NULL')
              .destroy_all

    Avatar.joins('LEFT OUTER JOIN users ON avatars.o_id = users.id')
          .where('users.id IS NULL')
          .where(object_lookup_id: ObjectLookup.by_name('User'))
          .destroy_all

    # add (possibly) missing foreign_key
    foreign_keys = [
      %i[online_notifications users],
    ]

    foreign_keys.each do |args|
      begin
        add_foreign_key(*args)
      rescue ActiveRecord::StatementInvalid => e
        Rails.logger.info "Can't add foreign_keys '#{args.inspect}'"
        Rails.logger.info e
        ActiveRecord::Base.connection.reconnect!
      end
    end
  end
end