class FreeZipcodeData::DbTable

Constants

ISSUE_URL

Attributes

database[R]
tablename[R]

Public Class Methods

new(database:, tablename:) click to toggle source
# File lib/free_zipcode_data/db_table.rb, line 13
def initialize(database:, tablename:)
  @database  = database
  @tablename = tablename
  lc = select_first('SELECT value FROM meta where name = "line_count"')
  @@progressbar = ProgressBar.create(total: lc.to_i * 4, format: '%t: |%B| %e')
end

Public Instance Methods

update_progress() click to toggle source
# File lib/free_zipcode_data/db_table.rb, line 20
def update_progress
  @@progressbar.increment
end

Private Instance Methods

country_lookup_table() click to toggle source
# File lib/free_zipcode_data/db_table.rb, line 26
def country_lookup_table
  @country_lookup_table ||=
    begin
      path = File.expand_path('../../country_lookup_table.yml', __dir__)
      YAML.load_file(path)
    end
end
escape_single_quotes(string) click to toggle source
# File lib/free_zipcode_data/db_table.rb, line 58
def escape_single_quotes(string)
  string&.gsub(/[']/, '\'\'') || ''
end
get_country_id(country) click to toggle source
# File lib/free_zipcode_data/db_table.rb, line 41
def get_country_id(country)
  sql = "SELECT id FROM countries WHERE alpha2 = '#{country}'"
  select_first(sql)
end
get_county_id(county) click to toggle source
# File lib/free_zipcode_data/db_table.rb, line 52
def get_county_id(county)
  return nil if county.nil?
  sql = "SELECT id FROM counties WHERE name = '#{escape_single_quotes(county)}'"
  select_first(sql)
end
get_state_id(state_abbr, state_name) click to toggle source
# File lib/free_zipcode_data/db_table.rb, line 46
def get_state_id(state_abbr, state_name)
  sql = "SELECT id FROM states
    WHERE abbr = '#{state_abbr}' OR name = '#{escape_single_quotes(state_name)}'"
  select_first(sql)
end
select_first(sql) click to toggle source
# File lib/free_zipcode_data/db_table.rb, line 34
def select_first(sql)
  rows = database.execute(sql)
  rows[0].nil? ? nil : rows[0].first
rescue SQLite3::SQLException => err
  raise "Please file an issue at #{ISSUE_URL}: [#{err}] -> SQL: [#{sql}]"
end