<commit_before># frozen_string_literal: true

# Migration to include new columns for the most current RNA ID and populate
# reconstructed values from existing data.
class AddCurrentRnaIdToLighthouseSample < ActiveRecord::Migration[6.0]
  def self.up
    # Add a column for boolean value indicating the current RNA ID for a sample.
    change_table :lighthouse_sample, bulk: true do |t|
      t.boolean :is_current, null: false, default: false, comment: 'Identifies if this sample has the most up to date information for the same rna_id'
      t.index %i[is_current], unique: false # same uniqueness criteria as in MongoDB
    end

    # Add a generated column for the current RNA ID.
    execute %{
      ALTER TABLE lighthouse_sample
      ADD COLUMN current_rna_id VARCHAR(255)
      GENERATED ALWAYS AS (IF(is_current = 1, rna_id, NULL))
      STORED;
    }

    # Add an index for the generated column
    execute %{
      CREATE UNIQUE INDEX index_lighthouse_sample_on_current_rna_id
      ON lighthouse_sample (current_rna_id);
    }

    # Infer and populate historic values for is_current.
    execute %{
      UPDATE
        lighthouse_sample AS lsample,
        (
          SELECT
            temp_lsample.id,
            IF(most_recent_samples.most_recent_id IS NULL, 0, 1) AS is_current
          FROM lighthouse_sample AS temp_lsample
          LEFT JOIN (
            SELECT rna_id, max(id) AS most_recent_id
            FROM lighthouse_sample
            GROUP BY rna_id
          ) AS most_recent_samples
          ON temp_lsample.rna_id=most_recent_samples.rna_id
            AND temp_lsample.id=most_recent_samples.most_recent_id
        ) AS updated_data
      SET lsample.is_current=updated_data.is_current
      WHERE lsample.id=updated_data.id;
    }
  end

  def self.down
    # Drop the index for current RNA ID.
    execute %{
      DROP INDEX index_lighthouse_sample_on_current_rna_id
      ON lighthouse_sample;
    }

    # Drop the generated column for current RNA ID.
    execute %{
      ALTER TABLE lighthouse_sample
      DROP COLUMN current_rna_id;
    }

    # Drop index and column for is_current.
    change_table :lighthouse_sample, bulk: true do |t|
      t.remove_index %i[is_current]
      t.remove :is_current
    end
  end
end<commit_msg>Use built in Rails functionality for virtual column<commit_after># frozen_string_literal: true

# Migration to include new columns for the most current RNA ID and populate
# reconstructed values from existing data.
class AddCurrentRnaIdToLighthouseSample < ActiveRecord::Migration[6.0]
  def self.up
    # Add a columns for boolean is_current and current RNA ID.
    change_table :lighthouse_sample, bulk: true do |t|
      t.boolean :is_current, null: false, default: false, comment: 'Identifies if this sample has the most up to date information for the same rna_id'
      t.index %i[is_current], unique: false # same uniqueness criteria as in MongoDB

      t.virtual :current_rna_id, type: :string, as: "if((is_current = 1),rna_id,NULL)", stored: true
      t.index %i[current_rna_id], unique: true # same uniqueness criteria as in MongoDB
    end

    # Infer and populate historic values for is_current.
    execute %{
      UPDATE
        lighthouse_sample AS lsample,
        (
          SELECT
            temp_lsample.id,
            IF(most_recent_samples.most_recent_id IS NULL, 0, 1) AS is_current
          FROM lighthouse_sample AS temp_lsample
          LEFT JOIN (
            SELECT rna_id, max(id) AS most_recent_id
            FROM lighthouse_sample
            GROUP BY rna_id
          ) AS most_recent_samples
          ON temp_lsample.rna_id=most_recent_samples.rna_id
            AND temp_lsample.id=most_recent_samples.most_recent_id
        ) AS updated_data
      SET lsample.is_current=updated_data.is_current
      WHERE lsample.id=updated_data.id;
    }
  end

  def self.down
    # Drop index and column for current_rna_id.
    change_table :lighthouse_sample, bulk: true do |t|
      t.remove_index %i[current_rna_id]
      t.remove :current_rna_id
    end

    # Drop index and column for is_current.
    change_table :lighthouse_sample, bulk: true do |t|
      t.remove_index %i[is_current]
      t.remove :is_current
    end
  end
end
{'fraction_non_alphanumeric': 0.05739825923312162, 'fraction_numerical': 0.0030581039755351682, 'mean_word_length': 2.803220035778175, 'pattern_counts': {'":': 0, '<': 5, '<?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': '139384', 'n_tokens_mistral': 1366, 'n_tokens_neox': 1267, 'n_words': 406}