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 = "    create table \#{tablename} (\n      id integer not null primary key,\n      state_id integer,\n      abbr varchar(255),\n      name varchar(255),\n      county_seat varchar(255)\n    )\n  SQL\n  database.execute_batch(schema)\n\n  ndx = <<-SQL\n    CREATE UNIQUE INDEX \"main\".\"unique_county\"\n    ON \#{tablename} (state_id, abbr, name COLLATE NOCASE ASC);\n  SQL\n  database.execute_batch(ndx)\nend\n"

#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 = "    INSERT INTO counties (state_id, abbr, name)\n    VALUES ('\#{state_id}',\n      '\#{row[:short_county]}',\n      '\#{escape_single_quotes(row[:county])}'\n    )\n  SQL\n\n  begin\n    database.execute(sql)\n  rescue SQLite3::ConstraintException => e\n    unless e.message.include?('UNIQUE')\n      raise \"Please file an issue at \#{ISSUE_URL}: [\#{e}] -> SQL: [\#{sql}]\"\n    end\n  rescue StandardError => e\n    raise \"Please file an issue at \#{ISSUE_URL}: [\#{e}] -> SQL: [\#{sql}]\"\n  end\n\n  update_progress\nend\n"