Contents

# frozen_string_literal: true

class AddShortNameToCoreBasedStatisticalAreas < ActiveRecord::Migration[5.0]
  def up
    return if column_exists?(:us_geo_core_based_statistical_areas, :short_name)

    add_column :us_geo_core_based_statistical_areas, :short_name, :string, null: true

    update_sql = "UPDATE us_geo_core_based_statistical_areas SET short_name = ? WHERE geoid = ?"

    select_all("SELECT geoid, name FROM us_geo_core_based_statistical_areas").each do |row|
      city, state = row["name"].split(", ", 2)
      short_name = "#{city.split("-").first}, #{state.split("-").first}"
      update ActiveRecord::Base.sanitize_sql([update_sql, short_name, row["geoid"]])
    end

    change_column_null :us_geo_core_based_statistical_areas, :short_name, false
    add_index :us_geo_core_based_statistical_areas, :short_name, unique: true
  end

  def down
    remove_column :us_geo_core_based_statistical_areas, :short_name
  end
end

Version data entries

5 entries across 5 versions & 1 rubygems

Version	Path
us_geo-2.1.1	db/migrate/20230414000700_add_short_name_to_core_based_statistical_areas.rb
us_geo-2.1.0	db/migrate/20230414000700_add_short_name_to_core_based_statistical_areas.rb
us_geo-2.0.4	db/migrate/20230414000700_add_short_name_to_core_based_statistical_areas.rb
us_geo-2.0.3	db/migrate/20230414000700_add_short_name_to_core_based_statistical_areas.rb
us_geo-2.0.2	db/migrate/20230414000700_add_short_name_to_core_based_statistical_areas.rb