<commit_before>require Rails.root.join('lib/tasks/helpers_for_import.rb')
namespace :import do

  desc "Import species records from csv files (usage: rake import:species[path/to/file,path/to/another])"
  task :species, 10.times.map { |i| "file_#{i}".to_sym } => [:environment] do |t, args|
    TMP_TABLE = 'species_import'
    files = files_from_args(t, args)
    files.each do |file|
      drop_table(TMP_TABLE)
      create_table_from_csv_headers(file, TMP_TABLE)
      copy_data(file, TMP_TABLE)

      kingdom = file.split('/').last.split('_')[0].titleize

      #import_data_for Rank::KINGDOM
      import_data_for kingdom, Rank::PHYLUM
      import_data_for kingdom, Rank::CLASS
      import_data_for kingdom, Rank::ORDER
      import_data_for kingdom, Rank::FAMILY
      import_data_for kingdom, Rank::SUBFAMILY
      import_data_for kingdom, Rank::GENUS
      import_data_for kingdom, Rank::SPECIES
      import_data_for kingdom, Rank::SUBSPECIES
      if kingdom == 'Plantae'
        import_data_for kingdom, Rank::VARIETY
      end
    end
  end
end

# Copies data from the temporary table to the correct tables in the database
#
# @param [String] which the rank to be copied.
def import_data_for kingdom, rank, synonyms=nil
  puts "Importing #{rank}"
  rank_id = Rank.select(:id).where(:name => rank).first.id
  existing = TaxonConcept.where(:rank_id => rank_id).count
  puts "There were #{existing} #{rank} before we started"

  sql = <<-SQL
    INSERT INTO taxon_names(scientific_name, created_at, updated_at)
      SELECT DISTINCT INITCAP(BTRIM(#{TMP_TABLE}.Name)), current_date, current_date
      FROM #{TMP_TABLE}
      WHERE NOT EXISTS (
        SELECT scientific_name
        FROM taxon_names
        WHERE UPPER(BTRIM(scientific_name)) = UPPER(BTRIM(#{TMP_TABLE}.Name))
      )
      AND UPPER(BTRIM(#{TMP_TABLE}.Name)) <> 'NULL'
      AND UPPER(BTRIM(#{TMP_TABLE}.Rank)) = UPPER('#{rank}')
      AND (
        BTRIM(#{TMP_TABLE}.Taxonomy) iLIKE '%CITES%'
        OR BTRIM(#{TMP_TABLE}.Taxonomy) iLIKE '%CMS%'
      )
  SQL
  ActiveRecord::Base.connection.execute(sql)

  ActiveRecord::Base.connection.execute('CREATE INDEX species_import_name ON species_import (name)')

  [Taxonomy::CITES_EU, Taxonomy::CMS].each do |taxonomy|
    taxonomy = Taxonomy.find_by_name(taxonomy)
    sql = <<-SQL
      WITH to_be_inserted AS (
        SELECT DISTINCT
          taxon_names.id AS taxon_name_id,
          #{rank_id} AS rank_id,
          #{taxonomy.id} AS taxonomy_id,
          parent_taxon_concepts.id AS parent_id,
          CASE
            WHEN UPPER(BTRIM(tmp.author)) = 'NULL' THEN NULL
            ELSE UPPER(BTRIM(tmp.author))
          END AS author_year,
          tmp.legacy_id,
          '#{kingdom}' AS legacy_type,
          tmp.notes,
          UPPER(BTRIM(tmp.status)) AS name_status,
          current_date, current_date
        FROM #{TMP_TABLE} tmp
        INNER JOIN taxon_names
          ON UPPER(taxon_names.scientific_name) = UPPER(BTRIM(tmp.name))<commit_msg>Author Year was being added to the database in capital letters<commit_after>require Rails.root.join('lib/tasks/helpers_for_import.rb')
namespace :import do

  desc "Import species records from csv files (usage: rake import:species[path/to/file,path/to/another])"
  task :species, 10.times.map { |i| "file_#{i}".to_sym } => [:environment] do |t, args|
    TMP_TABLE = 'species_import'
    files = files_from_args(t, args)
    files.each do |file|
      drop_table(TMP_TABLE)
      create_table_from_csv_headers(file, TMP_TABLE)
      copy_data(file, TMP_TABLE)

      kingdom = file.split('/').last.split('_')[0].titleize

      #import_data_for Rank::KINGDOM
      import_data_for kingdom, Rank::PHYLUM
      import_data_for kingdom, Rank::CLASS
      import_data_for kingdom, Rank::ORDER
      import_data_for kingdom, Rank::FAMILY
      import_data_for kingdom, Rank::SUBFAMILY
      import_data_for kingdom, Rank::GENUS
      import_data_for kingdom, Rank::SPECIES
      import_data_for kingdom, Rank::SUBSPECIES
      if kingdom == 'Plantae'
        import_data_for kingdom, Rank::VARIETY
      end
    end
  end
end

# Copies data from the temporary table to the correct tables in the database
#
# @param [String] which the rank to be copied.
def import_data_for kingdom, rank, synonyms=nil
  puts "Importing #{rank}"
  rank_id = Rank.select(:id).where(:name => rank).first.id
  existing = TaxonConcept.where(:rank_id => rank_id).count
  puts "There were #{existing} #{rank} before we started"

  sql = <<-SQL
    INSERT INTO taxon_names(scientific_name, created_at, updated_at)
      SELECT DISTINCT INITCAP(BTRIM(#{TMP_TABLE}.Name)), current_date, current_date
      FROM #{TMP_TABLE}
      WHERE NOT EXISTS (
        SELECT scientific_name
        FROM taxon_names
        WHERE UPPER(BTRIM(scientific_name)) = UPPER(BTRIM(#{TMP_TABLE}.Name))
      )
      AND UPPER(BTRIM(#{TMP_TABLE}.Name)) <> 'NULL'
      AND UPPER(BTRIM(#{TMP_TABLE}.Rank)) = UPPER('#{rank}')
      AND (
        BTRIM(#{TMP_TABLE}.Taxonomy) iLIKE '%CITES%'
        OR BTRIM(#{TMP_TABLE}.Taxonomy) iLIKE '%CMS%'
      )
  SQL
  ActiveRecord::Base.connection.execute(sql)

  ActiveRecord::Base.connection.execute('CREATE INDEX species_import_name ON species_import (name)')

  [Taxonomy::CITES_EU, Taxonomy::CMS].each do |taxonomy|
    taxonomy = Taxonomy.find_by_name(taxonomy)
    sql = <<-SQL
      WITH to_be_inserted AS (
        SELECT DISTINCT
          taxon_names.id AS taxon_name_id,
          #{rank_id} AS rank_id,
          #{taxonomy.id} AS taxonomy_id,
          parent_taxon_concepts.id AS parent_id,
          CASE
            WHEN UPPER(BTRIM(tmp.author)) = 'NULL' THEN NULL
            ELSE BTRIM(tmp.author)
          END AS author_year,
          tmp.legacy_id,
          '#{kingdom}' AS legacy_type,
          tmp.notes,
          UPPER(BTRIM(tmp.status)) AS name_status,
          current_date, current_date
        FROM #{TMP_TABLE} tmp
        INNER JOIN taxon_names
          ON UPPER(taxon_names.scientific_name) = UPPER(BTRIM(tmp.name))
{'fraction_non_alphanumeric': 0.1112214498510427, 'fraction_numerical': 0.0009930486593843098, 'mean_word_length': 3.2052887961029923, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 11, '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': '1182923', 'n_tokens_mistral': 2080, 'n_tokens_neox': 2048, 'n_words': 447}