-- ----------------------------------------------------------------------
-- MySQL GRT Application
-- SQL Script
-- ----------------------------------------------------------------------

-- -------------------------------------
-- Tables

DROP TABLE IF EXISTS db.geoadmin1;

CREATE TABLE db.geoadmin1 (
  Admin1_str_code VARCHAR(5) NOT NULL,
  Country_str_code VARCHAR(2) NOT NULL,
  Admin1_str_type VARCHAR(50) NOT NULL,
  Admin1_str_local_type VARCHAR(50) NULL,
  Admin1_str_name VARCHAR(100) NULL,
  PRIMARY KEY (Admin1_str_code),
  INDEX Admin1_str_code (Admin1_str_code),
  INDEX Country_str_code (Country_str_code),
  CONSTRAINT GeoAdmin1_Country_str_code FOREIGN KEY Country_str_code (Country_str_code)
    REFERENCES db.geocountry (Country_str_code)
    ON DELETE NO ACTION
    ON UPDATE CASCADE 
)
ENGINE = InnoDB
ROW_FORMAT = Compact
CHARACTER SET utf8 COLLATE utf8_general_ci;

DROP TABLE IF EXISTS db.geocountry;

CREATE TABLE db.geocountry (
  Country_str_code VARCHAR(2) NOT NULL,
  Region_str_code VARCHAR(3) NULL,
  Country_str_ISO3166_2char_code VARCHAR(2) NULL,
  Country_str_ISO3166_3char_code VARCHAR(3) NULL,
  Country_str_ISO3166_numeric_code VARCHAR(3) NULL,
  Country_str_type VARCHAR(30) NULL,
  Country_str_name VARCHAR(100) NULL,
  Country_str_local_name VARCHAR(100) NULL,
  Country_str_long_name VARCHAR(100) NULL,
  Country_str_local_long_name VARCHAR(150) NULL,
  Country_str_controlling_entity VARCHAR(25) NULL,
  Country_Str_former_name VARCHAR(100) NULL,
  Country_str_name_abbreviation VARCHAR(25) NULL,
  PRIMARY KEY (Country_str_code),
  INDEX Country_str_code (Country_str_code),
  INDEX Region_str_code (Region_str_code),
  CONSTRAINT GeoCountry_Country_str_code FOREIGN KEY Country_str_code (Country_str_code)
    REFERENCES db.geofeature (Country_str_code)
    ON DELETE NO ACTION
    ON UPDATE CASCADE 
)
ENGINE = InnoDB
ROW_FORMAT = Compact
CHARACTER SET utf8 COLLATE utf8_general_ci;

DROP TABLE IF EXISTS db.geocountrynote;

CREATE TABLE db.geocountrynote (
  Country_str_code VARCHAR(2) NOT NULL,
  Country_txt_location TEXT NULL,
  Country_txt_government_type TEXT NULL,
  Country_txt_government_type_notes TEXT NULL,
  Country_str_capital VARCHAR(200) NULL,
  Country_txt_dependency_status TEXT NULL,
  Country_txt_dependency_status_notes TEXT NULL,
  Country_txt_dependant_areas TEXT NULL,
  Country_str_internet_country_code VARCHAR(10) NULL,
  Country_str_population VARCHAR(200) NULL,
  Country_txt_languages TEXT NULL,
  Country_txt_languages_notes TEXT NULL,
  Country_str_currency VARCHAR(100) NULL,
  Country_str_currency_code VARCHAR(25) NULL,
  Country_txt_currency_notes TEXT NULL,
  Country_txt_background TEXT NULL,
  Country_txt_climate TEXT NULL,
  Country_txt_terrain TEXT NULL,
  Country_str_nationality_noun VARCHAR(100) NULL,
  Country_str_nationality_adjective VARCHAR(100) NULL,
  Country_str_land_area VARCHAR(100) NULL,
  Country_str_water_area VARCHAR(50) NULL,
  Country_str_total_area VARCHAR(100) NULL,
  Country_txt_area_notes TEXT NULL,
  Country_str_land_boundaries_total VARCHAR(25) NULL,
  Country_str_land_boundaries_border_countries VARCHAR(350) NULL,
  Country_str_coastline VARCHAR(150) NULL,
  Country_txt_independence TEXT NULL,
  Country_txt_independence_notes TEXT NULL,
  Country_txt_national_holiday TEXT NULL,
  Country_txt_constitution TEXT NULL,
  Country_txt_constitution_notes TEXT NULL,
  Country_str_general TEXT NULL,
  PRIMARY KEY (Country_str_code),
  INDEX Country_str_code (Country_str_code),
  CONSTRAINT GeoCountryNote_Country_str_code FOREIGN KEY Country_str_code (Country_str_code)
    REFERENCES db.geocountry (Country_str_code)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
)
ENGINE = InnoDB
ROW_FORMAT = Compact
CHARACTER SET utf8 COLLATE utf8_general_ci;

DROP TABLE IF EXISTS db.geofeature;

CREATE TABLE db.geofeature (
  Feature_int_id INT NOT NULL,
  Feature_int_variant_of_feature_int_ID INT NULL,
  FeatureType_str_code VARCHAR(5) NOT NULL,
  Admin1_str_code VARCHAR(5) NOT NULL,
  Country_str_code VARCHAR(2) NOT NULL,
  Feature_int_font_code INT NULL DEFAULT '1',
  Feature_str_name VARCHAR(120) NULL,
  Feature_str_local_name VARCHAR(120) NULL,
  Feature_dec_lat DECIMAL(12, 7) NULL,
  Feature_dec_lon DECIMAL(12, 7) NULL,
  Feature_str_lat VARCHAR(12) NULL,
  Feature_str_lon VARCHAR(12) NULL,
  Feature_dec_source_lat DECIMAL(12, 7) NULL,
  Feature_dec_source_lon DECIMAL(12, 7) NULL,
  Feature_str_source_lat VARCHAR(12) NULL,
  Feature_str_source_lon VARCHAR(12) NULL,
  PRIMARY KEY (Feature_int_id),
  INDEX FeatureType_str_code (FeatureType_str_code),
  INDEX Admin1_str_code (Admin1_str_code),
  INDEX Country_str_code (Country_str_code),
  INDEX Feature_int_id (Feature_int_id)
)
ENGINE = InnoDB
ROW_FORMAT = Compact
CHARACTER SET utf8 COLLATE utf8_general_ci;

DROP TABLE IF EXISTS db.geofeaturetype;

CREATE TABLE db.geofeaturetype (
  FeatureType_str_code VARCHAR(5) NOT NULL,
  FeatureType_str_type VARCHAR(35) NULL,
  FeatureType_str_name VARCHAR(50) NULL,
  FeatureType_str_description VARCHAR(255) NULL,
  PRIMARY KEY (FeatureType_str_code),
  INDEX FeatureType_str_code (FeatureType_str_code),
  CONSTRAINT GeoFeatureType_FeatureType_str_code FOREIGN KEY FeatureType_str_code (FeatureType_str_code)
    REFERENCES db.geofeature (FeatureType_str_code)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
)
ENGINE = InnoDB
ROW_FORMAT = Compact
CHARACTER SET utf8 COLLATE utf8_general_ci;

DROP TABLE IF EXISTS db.georegion;

CREATE TABLE db.georegion (
  Region_str_code VARCHAR(3) NOT NULL,
  Region_str_name VARCHAR(50) NULL,
  PRIMARY KEY (Region_str_code),
  INDEX Region_str_code (Region_str_code),
  CONSTRAINT GeoRegion_Region_str_code FOREIGN KEY Region_str_code (Region_str_code)
    REFERENCES db.geocountry (Region_str_code)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
)
ENGINE = InnoDB
ROW_FORMAT = Compact
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- -------------------------------------
-- Views

--DROP VIEW IF EXISTS db.GeoData;

--SHOW WARNINGS;

--CREATE OR REPLACE VIEW db.GeoData AS
--SELECT *
--FROM db.geoFeature F
--JOIN db.geoFeatureType FT ON (FT.FeatureType_str_code = F.FeatureType_str_code)
--JOIN db.geoAdmin1 A1 ON (A1.Admin1_str_code = F.Admin1_str_code)
--JOIN db.geoCountry C ON (C.Country_str_code = F.Country_str_code)
--JOIN db.geoRegion R ON (C.Region_str_code = R.Region_str_code)
--ORDER BY FT.FeatureType_str_type, C.Country_str_name, A1.Admin1_str_name, F.Feature_str_name;

-- Disable show warnings after every action

\w;

-- ----------------------------------------------------------------------
-- Importing the Geography files into the tables

LOAD DATA LOCAL INFILE '\\\\LocustServer\\Storage\\db\\GeoData\\Admin1.txt'
INTO TABLE db.geoadmin1
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '\\\\LocustServer\\Storage\\db\\GeoData\\Country.txt'
INTO TABLE db.geocountry
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '\\\\LocustServer\\Storage\\db\\GeoData\\CountryNote.txt'
INTO TABLE db.geocountrynote
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '\\\\LocustServer\\Storage\\db\\GeoData\\Feature.txt'
INTO TABLE db.geofeature
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '\\\\LocustServer\\Storage\\db\\GeoData\\FeatureType.txt'
INTO TABLE db.geofeaturetype
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '\\\\LocustServer\\Storage\\db\\GeoData\\Region.txt'
INTO TABLE db.georegion
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------------------------------------------------
-- EOF