<commit_before>class UniqueIndexOnProfile < ActiveRecord::Migration
  def self.up
    columns = Profile.column_names
    ["id", "created_at", "updated_at"].each{|n| columns.delete(n)}

    sql = <<-SQL
    SELECT `profiles`.person_id FROM `profiles`
      GROUP BY #{columns.join(',')}
        HAVING COUNT(*)>1 AND `profiles`.person_id IS NOT NULL;
    SQL
    result = Profile.connection.execute(sql)
    duplicate_person_ids = result.to_a.flatten

    undesired_profile_ids = []
    duplicate_person_ids.each do |person_id|
      ids = Profile.where(:person_id => person_id).map!{|p| p.id}
      ids.pop
      undesired_profile_id.concat(ids)
    end
    Profile.where(:id => undesired_profile_ids).delete_all

    remove_index :profiles, :person_id
    add_index    :profiles, :person_id, :unique => true
  end

  def self.down<commit_msg>Use raw sql<commit_after>class UniqueIndexOnProfile < ActiveRecord::Migration
  def self.up
    conn = ActiveRecord::Base.connection
    columns = conn.columns("profiles").map{|c| c.name}
    ["id", "created_at", "updated_at"].each{|n| columns.delete(n)}

    sql = <<-SQL
    SELECT `profiles`.person_id FROM `profiles`
      GROUP BY #{columns.join(',')}
        HAVING COUNT(*)>1 AND `profiles`.person_id IS NOT NULL;
    SQL
    result = conn.execute(sql)
    duplicate_person_ids = result.to_a.flatten

    undesired_profile_ids = []
    duplicate_person_ids.each do |person_id|
      profile_ids = conn.execute("
        SELECT `profiles`.id FROM `profiles`
        WHERE `profiles`.person_id = #{person_id};").to_a.flatten
      profile_ids.pop
      undesired_profile_ids.concat(profile_ids)
    end
    conn.execute("DELETE FROM `profiles`
      WHERE `profiles`.id IN (#{undesired_profile_ids.join(",")});") unless undesired_profile_ids.empty?

    remove_index :profiles, :person_id
    add_index    :profiles, :person_id, :unique => true
  end

  def self.down
{'fraction_non_alphanumeric': 0.12689295039164492, 'fraction_numerical': 0.0010443864229765013, 'mean_word_length': 3.583732057416268, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 9, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '8235', 'n_tokens_mistral': 653, 'n_tokens_neox': 632, 'n_words': 128}