From c3f8d65a4fb1f5674557ee67cf7f74369df86ad1 Mon Sep 17 00:00:00 2001 From: Darkelarious Date: Sat, 19 Nov 2016 20:56:04 +0100 Subject: Massive improvements on efficiency, robustness, security, reliability and more --- data/sql/tables-Pg.sql | 35 +++++++++++++++++++++++++++++++++++ data/sql/tables-SQLite.sql | 35 +++++++++++++++++++++++++++++++++++ data/sql/tables-mysql.sql | 37 +++++++++++++++++++++++++++++++++++++ 3 files changed, 107 insertions(+) create mode 100755 data/sql/tables-Pg.sql create mode 100755 data/sql/tables-SQLite.sql create mode 100755 data/sql/tables-mysql.sql (limited to 'data/sql') diff --git a/data/sql/tables-Pg.sql b/data/sql/tables-Pg.sql new file mode 100755 index 0000000..f5663ff --- /dev/null +++ b/data/sql/tables-Pg.sql @@ -0,0 +1,35 @@ +CREATE TABLE serverlist( + id SERIAL UNIQUE NOT NULL PRIMARY KEY, + ip inet NOT NULL DEFAULT '0.0.0.0', + port INTEGER NOT NULL DEFAULT 0, + gamename VARCHAR(50) NOT NULL DEFAULT ' ', + gamever VARCHAR(50) NOT NULL DEFAULT ' ', + hostname VARCHAR(100) NOT NULL DEFAULT ' ', + hostport INTEGER NOT NULL DEFAULT 0, + country VARCHAR(5), + b333ms BOOLEAN NOT NULL DEFAULT FALSE, + blacklisted BOOLEAN NOT NULL DEFAULT FALSE, + added timestamptz NOT NULL DEFAULT NOW(), + beacon timestamptz NOT NULL DEFAULT NOW(), + updated timestamptz NOT NULL DEFAULT NOW() +); + +CREATE TABLE pending( + id SERIAL UNIQUE NOT NULL PRIMARY KEY, + ip inet NOT NULL DEFAULT '0.0.0.0', + beaconport INTEGER NOT NULL DEFAULT 0, + heartbeat INTEGER NOT NULL DEFAULT 0, + gamename VARCHAR(25) NOT NULL DEFAULT ' ', + secure VARCHAR(12) NOT NULL DEFAULT ' ', + enctype INTEGER NOT NULL DEFAULT 0, + added timestamptz NOT NULL DEFAULT NOW() +); + +CREATE TABLE games( + gamename VARCHAR(50) NOT NULL, + cipher VARCHAR(10) NOT NULL DEFAULT ' ', + description VARCHAR(200) NOT NULL DEFAULT ' ', + default_qport INTEGER NOT NULL DEFAULT 0, + num_uplink INTEGER NOT NULL DEFAULT 0, + num_total INTEGER NOT NULL DEFAULT 0 +); diff --git a/data/sql/tables-SQLite.sql b/data/sql/tables-SQLite.sql new file mode 100755 index 0000000..7d68786 --- /dev/null +++ b/data/sql/tables-SQLite.sql @@ -0,0 +1,35 @@ +CREATE TABLE serverlist( + id INTEGER PRIMARY KEY AUTOINCREMENT, + ip VARCHAR(15) NOT NULL DEFAULT '0.0.0.0', + port INTEGER NOT NULL DEFAULT 0, + gamename VARCHAR(100) NOT NULL DEFAULT ' ', + gamever VARCHAR(50) NOT NULL DEFAULT ' ', + hostname VARCHAR(100) NOT NULL DEFAULT ' ', + hostport INTEGER NOT NULL DEFAULT 0, + country VARCHAR(5), + b333ms BOOLEAN NOT NULL DEFAULT 0, + blacklisted BOOLEAN NOT NULL DEFAULT 0, + added timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP, + beacon timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP, + updated timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP +); + +CREATE TABLE pending( + id INTEGER PRIMARY KEY AUTOINCREMENT, + ip VARCHAR(15) NOT NULL DEFAULT '0.0.0.0', + beaconport INTEGER NOT NULL DEFAULT 0, + heartbeat INTEGER NOT NULL DEFAULT 0, + gamename VARCHAR(25) NOT NULL DEFAULT ' ', + secure VARCHAR(12) NOT NULL DEFAULT ' ', + enctype INTEGER NOT NULL DEFAULT 0, + added timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP +); + +CREATE TABLE games( + gamename VARCHAR(50) NOT NULL, + cipher VARCHAR(10) NOT NULL DEFAULT ' ', + description VARCHAR(200) NOT NULL DEFAULT ' ', + default_qport INTEGER NOT NULL DEFAULT 0, + num_uplink INTEGER NOT NULL DEFAULT 0, + num_total INTEGER NOT NULL DEFAULT 0 +); diff --git a/data/sql/tables-mysql.sql b/data/sql/tables-mysql.sql new file mode 100755 index 0000000..c460b1b --- /dev/null +++ b/data/sql/tables-mysql.sql @@ -0,0 +1,37 @@ +CREATE TABLE serverlist( + id INTEGER NOT NULL AUTO_INCREMENT, + ip VARCHAR(15) NOT NULL DEFAULT '0.0.0.0', + port INTEGER NOT NULL DEFAULT 0, + gamename VARCHAR(100) NOT NULL DEFAULT ' ', + gamever VARCHAR(50) NOT NULL DEFAULT ' ', + hostname VARCHAR(100) NOT NULL DEFAULT ' ', + hostport INTEGER NOT NULL DEFAULT 0, + country VARCHAR(5), + b333ms BOOLEAN NOT NULL DEFAULT 0, + blacklisted BOOLEAN NOT NULL DEFAULT 0, + added TIMESTAMP NOT NULL DEFAULT NOW(), + beacon TIMESTAMP NOT NULL DEFAULT NOW(), + updated TIMESTAMP NOT NULL DEFAULT NOW(), + PRIMARY KEY (id) +); + +CREATE TABLE pending( + id INTEGER NOT NULL AUTO_INCREMENT, + ip VARCHAR(15) NOT NULL DEFAULT '0.0.0.0', + beaconport INTEGER NOT NULL DEFAULT 0, + heartbeat INTEGER NOT NULL DEFAULT 0, + gamename VARCHAR(25) NOT NULL DEFAULT ' ', + secure VARCHAR(12) NOT NULL DEFAULT ' ', + enctype INTEGER NOT NULL DEFAULT 0, + added TIMESTAMP NOT NULL DEFAULT NOW(), + PRIMARY KEY (id) +); + +CREATE TABLE games( + gamename VARCHAR(50) NOT NULL, + cipher VARCHAR(10) NOT NULL DEFAULT ' ', + description VARCHAR(200) NOT NULL DEFAULT ' ', + default_qport INTEGER NOT NULL DEFAULT 0, + num_uplink INTEGER NOT NULL DEFAULT 0, + num_total INTEGER NOT NULL DEFAULT 0 +); -- cgit v1.2.3