-- Collection of code snippets by Arne Vajhøj 
-- (from articles on eksperten.dk / vajhoej.dk written sometime between 2004 and now) 
-- load whi

CREATE TEMPORARY TABLE tmpwhi (
    f1 VARCHAR(10),
    f2 VARCHAR(10),
    f3 CHAR(2),
    f4 CHAR(3),
    f5 VARCHAR(30)
);

LOAD DATA INFILE 'C:\\e\\ip-to-country.csv'
INTO TABLE tmpwhi
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

DROP TABLE whi;

CREATE TABLE whi (
    id INTEGER NOT NULL AUTO_INCREMENT,
    ip1 NUMERIC(10,0),
    ip2 NUMERIC(10,0),
    country CHAR(2),
    PRIMARY KEY(id)
);

CREATE INDEX ixip1 ON whi (ip1);

CREATE INDEX ixip2 ON whi (ip2);

INSERT INTO whi (ip1, ip2,country)
SELECT f1,f2,f3 FROM tmpwhi;

-- load mm

CREATE TEMPORARY TABLE tmpmm (
    f1 VARCHAR(13),
    f2 VARCHAR(13),
    f3 VARCHAR(10),
    f4 VARCHAR(10),
    f5 CHAR(2),
    f6 VARCHAR(30)
);

LOAD DATA INFILE 'C:\\e\\GeoIPCountryWhois.csv'
INTO TABLE tmpmm
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

DROP TABLE mm;

CREATE TABLE mm (
    id INTEGER NOT NULL AUTO_INCREMENT,
    ip1 NUMERIC(10,0),
    ip2 NUMERIC(10,0),
    country CHAR(2),
    PRIMARY KEY(id)
);

CREATE INDEX ixip1 ON mm (ip1);

CREATE INDEX ixip2 ON mm (ip2);

INSERT INTO mm (ip1, ip2,country)
SELECT f3,f4,f5 FROM tmpmm;
