150 lines
5.1 KiB
SQL
150 lines
5.1 KiB
SQL
BEGIN EXCLUSIVE TRANSACTION;
|
|
CREATE TABLE version (version INTEGER);
|
|
INSERT INTO version (version) VALUES (1);
|
|
|
|
-- PLAYER
|
|
CREATE TABLE player_status (
|
|
id INTEGER PRIMARY KEY
|
|
, name TEXT NOT NULL
|
|
);
|
|
CREATE INDEX player_status_name ON player_status(name);
|
|
|
|
CREATE TABLE player (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT
|
|
, name TEXT NOT NULL
|
|
, master_id INTEGER
|
|
, current_status_id INTEGER
|
|
, last_login_id INTEGER
|
|
, flagged BOOLEAN NOT NULL DEFAULT 0
|
|
, FOREIGN KEY (master_id) REFERENCES player(id)
|
|
, FOREIGN KEY (current_status_id) REFERENCES player_status_log(id)
|
|
, FOREIGN KEY (last_login_id) REFERENCES connection_log(id)
|
|
);
|
|
CREATE UNIQUE INDEX player_name ON player(LOWER(name));
|
|
CREATE INDEX player_master_id ON player(master_id);
|
|
CREATE INDEX player_current_status_id ON player(current_status_id);
|
|
CREATE INDEX player_last_login_id ON player(last_login_id);
|
|
|
|
CREATE TABLE player_status_log (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT
|
|
, executor_id INTEGER NOT NULL
|
|
, player_id INTEGER NOT NULL
|
|
, status_id INTEGER NOT NULL
|
|
, timestamp INTEGER NOT NULL
|
|
, reason TEXT
|
|
, expires INTEGER
|
|
, FOREIGN KEY (executor_id) REFERENCES player(id)
|
|
, FOREIGN KEY (player_id) REFERENCES player(id)
|
|
, FOREIGN KEY (status_id) REFERENCES player_status(id)
|
|
, UNIQUE (player_id, status_id, timestamp)
|
|
);
|
|
CREATE INDEX player_status_log_player_id ON player_status_log(player_id);
|
|
CREATE INDEX player_status_log_timestamp ON player_status_log(timestamp);
|
|
CREATE INDEX player_status_log_reason ON player_status_log(reason);
|
|
-- END PLAYER
|
|
-- IP
|
|
CREATE TABLE ip_status (
|
|
id INTEGER PRIMARY KEY
|
|
, name TEXT NOT NULL
|
|
);
|
|
CREATE INDEX ip_status_name ON ip_status(name);
|
|
|
|
CREATE TABLE ip (
|
|
ip INTEGER PRIMARY KEY
|
|
, current_status_id INTEGER
|
|
, FOREIGN KEY (current_status_id) REFERENCES ip_status_log(id)
|
|
);
|
|
CREATE INDEX ip_current_status_id ON ip(current_status_id);
|
|
|
|
CREATE TABLE ip_status_log (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT
|
|
, executor_id INTEGER NOT NULL
|
|
, ip INTEGER NOT NULL
|
|
, status_id INTEGER NOT NULL
|
|
, timestamp INTEGER NOT NULL
|
|
, reason TEXT
|
|
, expires INTEGER
|
|
, FOREIGN KEY (executor_id) REFERENCES player(id)
|
|
, FOREIGN KEY (ip) REFERENCES ip(ip)
|
|
, FOREIGN KEY (status_id) REFERENCES ip_status(id)
|
|
);
|
|
CREATE INDEX ip_status_log_ip ON ip_status_log(ip);
|
|
CREATE INDEX ip_status_log_timestamp ON ip_status_log(timestamp);
|
|
CREATE INDEX ip_status_log_reason ON ip_status_log(reason);
|
|
-- END IP
|
|
-- ASN
|
|
CREATE TABLE asn_status (
|
|
id INTEGER PRIMARY KEY
|
|
, name TEXT NOT NULL
|
|
);
|
|
CREATE INDEX asn_status_name ON asn_status(name);
|
|
|
|
CREATE TABLE asn (
|
|
asn INTEGER PRIMARY KEY
|
|
, current_status_id INTEGER
|
|
, FOREIGN KEY (current_status_id) REFERENCES asn_status_log(id)
|
|
);
|
|
CREATE INDEX asn_current_status_id ON asn(current_status_id);
|
|
|
|
CREATE TABLE asn_status_log (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT
|
|
, executor_id INTEGER NOT NULL
|
|
, asn INTEGER NOT NULL
|
|
, status_id INTEGER NOT NULL
|
|
, timestamp INTEGER NOT NULL
|
|
, reason TEXT
|
|
, expires INTEGER
|
|
, FOREIGN KEY (executor_id) REFERENCES player(id)
|
|
, FOREIGN KEY (asn) REFERENCES asn(asn)
|
|
, FOREIGN KEY (status_id) REFERENCES asn_status(id)
|
|
);
|
|
CREATE INDEX asn_status_log_asn ON asn_status_log(asn);
|
|
CREATE INDEX asn_status_log_timestamp ON asn_status_log(timestamp);
|
|
CREATE INDEX asn_status_log_reason ON asn_status_log(reason);
|
|
-- END ASN
|
|
-- LOGS AND ASSOCIATIONS
|
|
CREATE TABLE connection_log (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT
|
|
, player_id INTEGER NOT NULL
|
|
, ip INTEGER NOT NULL
|
|
, asn INTEGER NOT NULL
|
|
, success INTEGER NOT NULL
|
|
, timestamp INTEGER NOT NULL
|
|
, FOREIGN KEY (player_id) REFERENCES player(id)
|
|
, FOREIGN KEY (ip) REFERENCES ip(ip)
|
|
, FOREIGN KEY (asn) REFERENCES asn(asn)
|
|
, UNIQUE (player_id, ip, success, timestamp)
|
|
);
|
|
CREATE INDEX log_player ON connection_log(player_id);
|
|
CREATE INDEX log_ip ON connection_log(ip);
|
|
CREATE INDEX log_asn ON connection_log(asn);
|
|
CREATE INDEX log_timestamp ON connection_log(timestamp);
|
|
|
|
CREATE TABLE assoc (
|
|
player_id INTEGER NOT NULL
|
|
, ip INTEGER NOT NULL
|
|
, asn INTEGER NOT NULL
|
|
, first_seen INTEGER NOT NULL
|
|
, last_seen INTEGER NOT NULL
|
|
, PRIMARY KEY (player_id, ip, asn)
|
|
, FOREIGN KEY (player_id) REFERENCES player(id)
|
|
, FOREIGN KEY (ip) REFERENCES ip(ip)
|
|
, FOREIGN KEY (asn) REFERENCES asn(asn)
|
|
);
|
|
CREATE INDEX assoc_player ON assoc(player_id);
|
|
CREATE INDEX assoc_ip ON assoc(ip);
|
|
CREATE INDEX assoc_asn ON assoc(asn);
|
|
-- END LOGS AND ASSOCIATIONS
|
|
-- REPORTS
|
|
CREATE TABLE report (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT
|
|
, reporter_id INTEGER NOT NULL
|
|
, report TEXT NOT NULL
|
|
, timestamp INTEGER NOT NULL
|
|
, FOREIGN KEY (reporter_id) REFERENCES player(id)
|
|
);
|
|
CREATE INDEX report_reporter ON report(reporter_id);
|
|
CREATE INDEX report_timestamp ON report(timestamp);
|
|
-- END REPORTS
|
|
COMMIT TRANSACTION;
|