Class: FreeZipcodeData::CountyTable

Inherits:
DbTable
  • Object
show all
Defined in:
lib/free_zipcode_data/county_table.rb

Constant Summary

Constants inherited from DbTable

DbTable::ISSUE_URL

Instance Attribute Summary

Attributes inherited from DbTable

#database, #tablename

Instance Method Summary collapse

Methods inherited from DbTable

#initialize, #update_progress

Constructor Details

This class inherits a constructor from FreeZipcodeData::DbTable

Instance Method Details

#build ⇒ Object 



7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
# File 'lib/free_zipcode_data/county_table.rb', line 7

def build
  schema = <<-SQL
    create table #{tablename} (
      id integer not null primary key,
      state_id integer,
      abbr varchar(255),
      name varchar(255),
      county_seat varchar(255)
    )
  SQL
  database.execute_batch(schema)

  ndx = <<-SQL
    CREATE UNIQUE INDEX "main"."unique_county"
    ON #{tablename} (state_id, abbr, name COLLATE NOCASE ASC);
  SQL
  database.execute_batch(ndx)
end

#write(row) ⇒ Object 



26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
 
# File 'lib/free_zipcode_data/county_table.rb', line 26

def write(row)
  return nil unless row[:county]

  state_id = get_state_id(row[:country], row[:short_state], row[:state])
  unless state_id
    logger.verbose(
      "Skipping county '#{row[:county]}': no state found for " \
      "abbr='#{row[:short_state]}', country='#{row[:country]}'"
    )
    return nil
  end

  sql = <<-SQL
    INSERT INTO counties (state_id, abbr, name)
    VALUES ('#{state_id}',
      '#{row[:short_county]}',
      '#{escape_single_quotes(row[:county])}'
    )
  SQL

  begin
    database.execute(sql)
  rescue SQLite3::ConstraintException => e
    unless e.message.include?('UNIQUE')
      raise "Please file an issue at #{ISSUE_URL}: [#{e}] -> SQL: [#{sql}]"
    end
  rescue StandardError => e
    raise "Please file an issue at #{ISSUE_URL}: [#{e}] -> SQL: [#{sql}]"
  end

  update_progress
end