45 lines
1.9 KiB
SQL
45 lines
1.9 KiB
SQL
-- released_at is when maxmind released this data
|
|
CREATE TABLE max_mind_releases (
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
released_at DATE UNIQUE NOT NULL,
|
|
imported BOOLEAN NOT NULL DEFAULT FALSE,
|
|
imported_at DATE,
|
|
geo_ip_124_url VARCHAR(2000),
|
|
geo_ip_124_md5 VARCHAR(255),
|
|
geo_ip_124_size INTEGER,
|
|
geo_ip_134_url VARCHAR(2000),
|
|
geo_ip_134_md5 VARCHAR(255),
|
|
geo_ip_134_size INTEGER,
|
|
region_codes_url VARCHAR(2000),
|
|
region_codes_md5 VARCHAR(255),
|
|
region_codes_size INTEGER,
|
|
iso3166_url VARCHAR(2000),
|
|
iso3166_md5 VARCHAR(255),
|
|
iso3166_size INTEGER,
|
|
table_dumps_url VARCHAR(2000),
|
|
table_dumps_md5 VARCHAR(255),
|
|
table_dumps_size INTEGER,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 'maxmind/2014-07-01/GeoIP-139_20140701.zip', '8487b681cc14ea9f603b52db5763a77a', 62399148,
|
|
-- 'maxmind/2014-07-01/GeoIP-142_20140701.zip', '2fb4288fa3004ad68a06388f716e4ee5', 2265920,
|
|
|
|
-- the 1st available release
|
|
INSERT INTO max_mind_releases VALUES (DEFAULT, DATE '2014-07-01', FALSE, NULL,
|
|
'maxmind/2014-07-01/GeoIP-124_20140701.zip', '93430c4b34b366030054a97c1b595f6f', 1997587,
|
|
'maxmind/2014-07-01/GeoIP-134_20140701.zip', '893c8674656271dac4964d5a56325203', 48198205,
|
|
'maxmind/2014-07-01/region_codes.csv', '74c174dc9132a95e56adf4ce32d38909', 76500,
|
|
'maxmind/2014-07-01/iso3166.csv', 'f2c15e4a163468b0b08ebedab1507911', 4282,
|
|
'maxmind/2014-07-01/copies.zip', '3a7ddf36b3a8433c19e1b9afcbd2bb77', 178660266,
|
|
DEFAULT, DEFAULT);
|
|
|
|
-- this created_at column will be used by the score_histories import process to chunk work correctly
|
|
ALTER TABLE scores ADD COLUMN created_at TIMESTAMP;
|
|
UPDATE SCORES SET created_at = score_dt;
|
|
ALTER TABLE scores ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP;
|
|
ALTER TABLE scores ALTER COLUMN created_at SET NOT NULL;
|
|
|
|
DROP TABLE max_mind_isp;
|
|
DROP TABLE max_mind_geo; |