class WorldDb::CreateDb

Public Instance Methods

up() click to toggle source
# File lib/worlddb/schema.rb, line 6
def up

ActiveRecord::Schema.define do

create_table :places do |t|
  t.string  :name,   null: false
  t.string  :kind,   null: false  # --  kind/feature (note: type reserved for activerecord sti)
  #####
  # continent:
  #  CONT - continent (use CNTI or CENT why??)
  # country:
  #  SUPR - supra (e.g. European Union)
  #  CNTY - country
  #  TERR - terr
  # state:
  #  STAT  -- was: ADM1 - e.g. state/province/etc.
  #  PART   e.g. regierungsbezirk ADM2
  #  COUN -- was ADM2/ADM3 - e.g. county/bezirk/kreis/etc.
  #  MUNI -  was ADM3/ADM4 - e.g. municipality/gemeinde/etc.
  # city:
  #  MTRO - metro
  #  CITY - city/town/village/hamlet //(de)ort/stadt/markt
  #  DIST - district/
  #
  #  add new table for zones (e.g. informal regions e.g. tourism, wine regions, etc.) ??
  #   why? why not??


  t.float   :lat   # optional for now (latitude)
  t.float   :lng   # optional for now (longitude)

  ## todo: add parent for hierachy ?? or keep it in country/state/city etc. table
  ##    or use extra hierachy join table ??

  ## timestamp at last
  t.timestamps
end


#############
# todo: use many-to-many assoc w/ join table  for name and place ???
#  why? why not?
#    - wien -> city n state n metro ?  collect more samples of names used more than once

### alternative names for places
create_table :names do |t|
  t.string     :name,   null: false
  t.references :place,      null: false
  t.string     :place_kind, null: false  #  add (cached) place_kind for easier search queries (w/o join)

  ### todo/fix: add lang_id to reference lang from lang table!!! (for now make it a duplicate; e.g. keep stirng lang for now)
  t.string     :lang,   null: false, default: 'en'   # default to english for now (or use unknown/undeterminded/unspecified???)

  ## todo: add kind/type   ? e.g. postal for postal code ??
  ## or wikipedia for wikipedia?  or use separate table ?? (linkable/links) etc.??

  ## timestamp at last
  t.timestamps
end



create_table :continents do |t|
  t.string     :name,     null: false
  t.string     :slug,     null: false   # auto-generate default
  t.string     :key,      null: false
  t.references :place,    null: false
  t.string     :alt_names  # comma separated list of alternate names (synonyms)

  ## timestamp at last
  t.timestamps
end

add_index :continents, :key, unique: true


create_table :country_codes do |t|
  t.string     :name, null: false
  t.string     :kind, null: false   # e.g. ALPHA2,NUM,FIFA,IOC,FIPS,NET,etc.
  t.references :country, null: false
end

add_index :country_codes, [:name, :kind], unique: true


create_table :countries do |t|
  t.string     :name,   null: false
  t.string     :slug,   null: false   # auto-generate default
  t.string     :key,    null: false
  t.references :place,  null: false
  t.string     :code,   null: false  # short three letter code (FIFA country code e.g. ITA)
  t.string     :alt_names  # comma separated list of alternate names (synonyms)
  t.string     :hist_names # comma separated list of historic names (no longer in use)
  t.integer    :pop,    null: false    # population count
  t.integer    :area,   null: false    #  area in square km (sq. km)
  t.references :continent
  t.references :country    # for supra(nationals) n depend(encies)

  ## flags (use single int named flags - why? why not?
  ### fix: use a generic kind string type flag!!!!!!
  t.boolean :s,  null: false, default: false   # supra(national) flag e.g. eu
  t.boolean :c,  null: false, default: false   # country flag (is this needed?)
  t.boolean :d,  null: false, default: false   # dependency flag

  t.boolean :m,  null: false, default: false   # misc(ellaneous) flag
  # misc use for territories w/ shared or disputed claims
  #   e.g. Antartica/Western Sahara/Paracel Islands/Spratly Islands/etc.

  # extras - country codes
  t.string  :motor      # optional  motor (vehicle) licene plate code (bumper sticker)
  t.string  :alpha2       # optional iso two letter country code
  t.string  :alpha3       # optional iso three letter country code
  t.string  :num        #  optional iso numeric country code - NOTE: same numeric code as string!!! e.g. 043 etc.
  t.string  :fifa       # optional fifa country code
  t.string  :ioc
  t.string  :fips
  t.string  :net        # optional internet top level domain (tld)

  t.string  :wikipedia  # optional wikipedia page name -- en.wikipedia.org/wiki/<name>

  ## timestamp at last
  t.timestamps
end


add_index :countries, :key,  unique: true
add_index :countries, :code, unique: true



######
# used for state/provice/land/regioni/etc.
create_table :states do |t|
  t.string     :name,   null: false
  t.string     :key,    null: false
  t.references :place,  null: false
  t.string :code     # short two or three letter code e.g. NY, OAX, etc.
  t.string :abbr     # optional conventional abbrevation (e.g. Stmk., Gto., etc.)
  t.string :iso      # iso code
  t.string :nuts     # nuts code (europe/eu only)
  t.string :alt_names  # comma separated list of alternate names (synonyms)

  t.references :country,  null: false
  t.integer    :level,    null: false, default: 1  # default assumes 1 e.g. state/province/etc.
  ### change to l (instead of level)!!!! - shorter, why, why not???

  t.integer :pop     # optional population count
  t.integer :area    # optional area in square km (sq. km)
  t.timestamps
end

### fix: add kind to unique ???
add_index :states, [:key, :country_id], unique: true


####
#  todo: use a view (of states/admins) instead - why? why not??
#  parts used for regierungsbezirke
create_table :parts do |t|
  t.string     :name,   null: false
  t.string     :key,    null: false
  t.references :place,  null: false
  t.string :code     # short two or three letter code e.g. NY, OAX, etc.
  t.string :abbr     # optional conventional abbrevation (e.g. Stmk., Gto., etc.)
  t.string :iso      # iso code  -- check in use/possible - ???
  t.string :nuts     # nuts code (europe/eu only)
  t.string :alt_names  # comma separated list of alternate names (synonyms)

  t.references :state,  null: false
  t.integer    :level,  null: false, default: 2  # default assumes 2
  ### change to l (instead of level)!!!! - shorter, why, why not???

  t.integer :pop     # optional population count
  t.integer :area    # optional area in square km (sq. km)
  t.timestamps
end

####
#  todo: use a view (of states/admins) instead - why? why not??
#    counties used for kreise, bezirke, etc.

create_table :counties do |t|
  t.string     :name,   null: false
  t.string     :key,    null: false
  t.references :place,  null: false
  t.string :code     # short two or three letter code e.g. NY, OAX, etc.
  t.string :abbr     # optional conventional abbrevation (e.g. Stmk., Gto., etc.)
  t.string :iso      # iso code  -- check in use/possible - ???
  t.string :nuts     # nuts code (europe/eu only)
  t.string :alt_names  # comma separated list of alternate names (synonyms)

  t.references :state,  null: false
  t.references :part    # optional part (e.g. regierungsbezirk, etc.)
  t.integer    :level,  null: false, default: 2  # default assumes 2 - note: is (change to) 3 if parts incl.
  ### change to l (instead of level)!!!! - shorter, why, why not???

  t.integer :pop     # optional population count
  t.integer :area    # optional area in square km (sq. km)
  t.timestamps
end


####
#  todo: use a view (of states/admins) instead - why? why not??
#    counties used for kreise, bezirke, etc.

create_table :munis do |t|
  t.string     :name,   null: false
  t.string     :key,    null: false
  t.references :place,  null: false
  t.string :code     # short two or three letter code e.g. NY, OAX, etc.
  t.string :abbr     # optional conventional abbrevation (e.g. Stmk., Gto., etc.)
  t.string :iso      # iso code  -- check in use/possible - ???
  t.string :nuts     # nuts code (europe/eu only)
  t.string :alt_names  # comma separated list of alternate names (synonyms)

  t.references :state,  null: false
  t.references :county   # optional county (e.g. bezirk,kreis, etc.)
  t.integer    :level,  null: false, default: 3  # default assumes 3 - note: is (change to) 4 if parts incl.
  ### change to l (instead of level)!!!! - shorter, why, why not???

  t.integer :pop     # optional population count
  t.integer :area    # optional area in square km (sq. km)
  t.timestamps
end



## kind of regions but not hierachical (used for tourist/colloquial zones etc.)
#    uses many-to-many join tables w/ cities n states/admins
#
#  examples:
#    Salzkammergut (part of Salzburg and Oberoesterreich)
#  others
#   Oberfranken -> Fichtelgebierge/Fraenkische Schweiz/etc.
create_table :zones do |t|
  # to be done
end


create_table :cities do |t|
  t.string     :name,   null: false
  t.string     :key,    null: false
  t.references :place,  null: false
  t.string     :code     # short three letter code (ITAT/airport code e.g. NYC or VIE)
  t.string     :alt_names  # comma separated list of alternate names (synonyms)

  t.references :country,  null: false
  t.references :state    # optional for now (e.g. state, bundesland, etc.)  -- ADM1
  t.references :part     # optional for now (e.g. regierungsbezirk, etc.)   -- x   /ADM2
  t.references :county   # optional for now (e.g. landkreis, bezirk, etc.)  -- ADM2/ADM3
  t.references :muni     # optional for now (e.g. gemeinde, etc.)           -- ADM3/ADM4

  t.references :metro    # optional for now (part of metro e.g. Ruhrgebiet or Vienna Metro(politean) Area etc.)

  t.integer :pop     # optional population count (city proper); see metro for metro pop
  t.integer :area    # optional area in square km (sq. km)

  ### t.boolean :capital, null: false, default: false  # is national captial?

  t.timestamps
end

create_table :metros do |t|
  t.string     :name,   null: false
  t.string     :key,    null: false
  t.references :place,  null: false
  t.string     :code     # short three letter code (ITAT/airport code e.g. NYC or VIE)
  t.string     :alt_names  # comma separated list of alternate names (synonyms)
  t.references :country,  null: false

  t.integer :pop     # optional population count
  t.integer :area    # optional area in square km (sq. km)

  t.timestamps
end

create_table :districts do |t|
  t.string     :name,   null: false
  t.string     :key,    null: false
  t.references :place,  null: false
  t.string     :code     # short three letter code (ITAT/airport code e.g. NYC or VIE)
  t.string     :alt_names  # comma separated list of alternate names (synonyms)
  t.references :city,  null: false

  t.integer :pop     # optional population count
  t.integer :area    # optional area in square km (sq. km)

  t.timestamps
end



create_table :langs do |t|  # langs == languages (e.g. en/English, de/Deutsch, etc.)
  t.string :key,   null: false
  t.string :name,  null: false
  t.timestamps
end


create_table :usages do |t|  # join table for countries_langs
  t.references :country,  null: false
  t.references :lang,     null: false
  t.boolean    :official, null: false, default: true  # is_official language in country
  t.boolean    :minor,    null: false, default: false # spoken by minority
  t.float      :percent     # usage in percent e.g. 90.0, 0.55, etc.
  t.timestamps
end

end # block Schema.define

end