<commit_before><commit_msg>removed orphaned data and added foreign key and non null constraints (issue #47)<commit_after>class AddForeignKeyRelations < ActiveRecord::Migration
  def up
    fks = [
      ['clouds',          'scenario_id', 'scenarios', 'id'],
      ['questions',       'scenario_id', 'scenarios', 'id'],
      ['groups',          'scenario_id', 'scenarios', 'id'],
      ['recipes',         'scenario_id', 'scenarios', 'id'],
      ['subnets',         'cloud_id',    'clouds',    'id'],
      ['instances',       'subnet_id',   'subnets',   'id'],
      ['role_recipes',    'recipe_id',   'recipes',   'id'],
      ['instance_groups', 'group_id',    'groups',    'id'],
      ['instance_groups', 'instance_id', 'instances', 'id'],
      ['instance_roles',  'role_id',     'roles',     'id'],
      ['instance_roles',  'instance_id', 'instances', 'id'],
    ]

    fks.each do |line|
      source_table, source_column, target_table, target_column = line
      execute "DELETE FROM #{source_table} WHERE #{source_column} NOT IN (SELECT #{target_column} FROM #{target_table})"
      change_column_null(source_table, source_column, false)
      add_foreign_key(source_table, target_table, on_delete: :cascade, name: "fk_#{source_table}_#{target_table}")
    end

    execute "UPDATE players SET user_id = NULL WHERE user_id NOT IN (SELECT id FROM users)"
    add_foreign_key(:players, :users, on_delete: :nullify, name: 'fk_players_users')

    execute "UPDATE players SET student_group_id = NULL WHERE student_group_id NOT IN (SELECT id FROM student_groups)"
    add_foreign_key(:players, :student_groups, on_delete: :nullify, name: 'fk_players_student_groups')

    execute "DELETE FROM players WHERE group_id NOT IN (SELECT id FROM groups)"
    add_foreign_key(:players, :groups, on_delete: :cascade, name: 'fk_players_groups')
  end
end

{'fraction_non_alphanumeric': 0.14146868250539957, 'fraction_numerical': 0.0010799136069114472, 'mean_word_length': 3.9021164021164023, 'pattern_counts': {'":': 0, '<': 4, '<?xml version=': 0, '>': 3, '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': '672840', 'n_tokens_mistral': 606, 'n_tokens_neox': 595, 'n_words': 152}