33 lines
936 B
MySQL
33 lines
936 B
MySQL
|
-- VRFS-1924
|
||
|
|
|
||
|
|
-- generic, single row config table. if you need a new config, add a column
|
||
|
|
CREATE TABLE generic_state (
|
||
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
|
|
score_history_last_imported_at TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
INSERT INTO generic_state (id, score_history_last_imported_at) VALUES ('default', TIMESTAMP '01-01-1900 00:00:00');
|
||
|
|
|
||
|
|
CREATE TABLE score_histories (
|
||
|
|
from_client_id VARCHAR(64),
|
||
|
|
from_addr BIGINT,
|
||
|
|
from_isp VARCHAR(50),
|
||
|
|
from_country VARCHAR(64),
|
||
|
|
from_region VARCHAR(64),
|
||
|
|
from_city VARCHAR(255),
|
||
|
|
from_postal VARCHAR(25),
|
||
|
|
from_latitude DOUBLE PRECISION,
|
||
|
|
from_longitude DOUBLE PRECISION,
|
||
|
|
to_client_id VARCHAR(64),
|
||
|
|
to_addr BIGINT,
|
||
|
|
to_isp VARCHAR(50),
|
||
|
|
to_country VARCHAR(64),
|
||
|
|
to_region VARCHAR(64),
|
||
|
|
to_city VARCHAR(255),
|
||
|
|
to_postal VARCHAR(25),
|
||
|
|
to_latitude DOUBLE PRECISION,
|
||
|
|
to_longitude DOUBLE PRECISION,
|
||
|
|
score INTEGER NOT NULL,
|
||
|
|
score_dt TIMESTAMP NOT NULL,
|
||
|
|
scoring_data TEXT
|
||
|
|
);
|