diff options
Diffstat (limited to 'data/sql')
| -rwxr-xr-x | data/sql/tables-Pg.sql | 103 | ||||
| -rwxr-xr-x | data/sql/tables-SQLite.sql | 104 |
2 files changed, 207 insertions, 0 deletions
diff --git a/data/sql/tables-Pg.sql b/data/sql/tables-Pg.sql new file mode 100755 index 0000000..96f8f91 --- /dev/null +++ b/data/sql/tables-Pg.sql @@ -0,0 +1,103 @@ +CREATE TABLE appletlist( + id SERIAL UNIQUE NOT NULL PRIMARY KEY, + ip inet NOT NULL, + hostport INTEGER NOT NULL, + gamename TEXT NOT NULL DEFAULT '', + added timestamptz NOT NULL DEFAULT NOW(), + updated timestamptz NOT NULL DEFAULT NOW() +); + +CREATE TABLE serverlist( + id SERIAL UNIQUE NOT NULL PRIMARY KEY, + ip inet NOT NULL, + port INTEGER NOT NULL DEFAULT 0, + gamename TEXT NOT NULL DEFAULT '', + gamever TEXT, + hostname TEXT, + hostport INTEGER DEFAULT 0, + country TEXT, + b333ms BOOLEAN DEFAULT FALSE, + blacklisted BOOLEAN DEFAULT FALSE, + added timestamptz NOT NULL DEFAULT NOW(), + beacon timestamptz NOT NULL DEFAULT NOW(), + updated timestamptz NOT NULL DEFAULT NOW() +); +CREATE INDEX pendingaddress ON serverlist(ip, port); +CREATE INDEX updateaddress ON serverlist(ip, hostport); +CREATE INDEX directbeacons ON serverlist(gamename, b333ms); + +CREATE TABLE games( + gamename TEXT NOT NULL, + cipher TEXT, + description TEXT, + default_qport INTEGER DEFAULT 0, + num_uplink INTEGER DEFAULT 0, + num_total INTEGER DEFAULT 0 +); +CREATE INDEX gameprops ON games(gamename); + +CREATE TABLE pending( + id SERIAL UNIQUE NOT NULL PRIMARY KEY, + ip inet NOT NULL, + heartbeat INTEGER NOT NULL DEFAULT 0, + added timestamptz NOT NULL DEFAULT NOW() +); + +CREATE TABLE extended_info( + server_id SERIAL REFERENCES serverlist(id), + minnetver TEXT, + location TEXT, + listenserver TEXT, + adminname TEXT, + adminemail TEXT, + password TEXT, + gametype TEXT, + gamestyle TEXT, + changelevels TEXT, + maptitle TEXT, + mapname TEXT, + numplayers INTEGER DEFAULT 0, + maxplayers INTEGER DEFAULT 0, + minplayers INTEGER DEFAULT 0, + botskill TEXT, + balanceteams TEXT, + playersbalanceteams TEXT, + friendlyfire TEXT, + maxteams TEXT, + timelimit TEXT, + goalteamscore TEXT, + fraglimit TEXT, + mutators TEXT DEFAULT 'None', + updated timestamptz NOT NULL DEFAULT NOW() +); + +CREATE TABLE player_info( + server_id SERIAL NOT NULL, + player TEXT DEFAULT 'Player', + team TEXT, + frags INTEGER DEFAULT 0, + mesh TEXT, + skin TEXT, + face TEXT, + ping INTEGER DEFAULT 0, + ngsecret TEXT, + updated timestamptz NOT NULL DEFAULT NOW() +); + +CREATE TABLE kfstats( + UTkey TEXT NOT NULL, + Username TEXT DEFAULT ' ', + CurrentVeterancy TEXT DEFAULT 'None', + TotalKills INTEGER NOT NULL DEFAULT 0, + DecaptedKills INTEGER NOT NULL DEFAULT 0, + TotalMeleeDamage INTEGER NOT NULL DEFAULT 0, + MeleeKills INTEGER NOT NULL DEFAULT 0, + PowerWpnKills INTEGER NOT NULL DEFAULT 0, + BullpupDamage INTEGER NOT NULL DEFAULT 0, + StalkerKills INTEGER NOT NULL DEFAULT 0, + TotalWelded INTEGER NOT NULL DEFAULT 0, + TotalHealed INTEGER NOT NULL DEFAULT 0, + TotalPlaytime INTEGER NOT NULL DEFAULT 0, + GamesWon INTEGER NOT NULL DEFAULT 0, + GamesLost 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..5608a66 --- /dev/null +++ b/data/sql/tables-SQLite.sql @@ -0,0 +1,104 @@ +CREATE TABLE appletlist( + id INTEGER PRIMARY KEY AUTOINCREMENT, + ip inet NOT NULL, + hostport INTEGER NOT NULL, + gamename TEXT NOT NULL DEFAULT '', + added timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP, + updated timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP +); + +CREATE TABLE serverlist( + id INTEGER PRIMARY KEY AUTOINCREMENT, + ip inet NOT NULL, + port INTEGER NOT NULL DEFAULT 0, + gamename TEXT NOT NULL DEFAULT '', + gamever TEXT, + hostname TEXT, + hostport INTEGER DEFAULT 0, + country TEXT, + b333ms INTEGER DEFAULT 0, + blacklisted INTEGER DEFAULT 0, + added timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP, + beacon timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP, + updated timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP +); +CREATE INDEX pendingaddress ON serverlist(ip, port); +CREATE INDEX updateaddress ON serverlist(ip, hostport); +CREATE INDEX directbeacons ON serverlist(gamename, b333ms); + +CREATE TABLE games( + gamename TEXT NOT NULL, + cipher TEXT, + description TEXT, + default_qport INTEGER DEFAULT 0, + num_uplink INTEGER DEFAULT 0, + num_total INTEGER DEFAULT 0 +); +CREATE INDEX gameprops ON games(gamename); + +CREATE TABLE pending( + id INTEGER PRIMARY KEY AUTOINCREMENT, + ip inet NOT NULL, + heartbeat INTEGER NOT NULL DEFAULT 0, + added timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP +); + +CREATE TABLE extended_info( + server_id INTEGER PRIMARY KEY AUTOINCREMENT, + minnetver TEXT, + location TEXT, + listenserver TEXT, + adminname TEXT, + adminemail TEXT, + password TEXT, + gametype TEXT, + gamestyle TEXT, + changelevels TEXT, + maptitle TEXT, + mapname TEXT, + numplayers INTEGER DEFAULT 0, + maxplayers INTEGER DEFAULT 0, + minplayers INTEGER DEFAULT 0, + botskill TEXT, + balanceteams TEXT, + playersbalanceteams TEXT, + friendlyfire TEXT, + maxteams TEXT, + timelimit TEXT, + goalteamscore TEXT, + fraglimit TEXT, + mutators TEXT DEFAULT 'None', + updated timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP, + FOREIGN KEY(server_id) REFERENCES serverlist(id) +); + +CREATE TABLE player_info( + server_id INTEGER NOT NULL, + player TEXT DEFAULT 'Player', + team TEXT, + frags INTEGER DEFAULT 0, + mesh TEXT, + skin TEXT, + face TEXT, + ping INTEGER DEFAULT 0, + ngsecret TEXT, + updated timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP +); + +CREATE TABLE kfstats( + UTkey TEXT NOT NULL, + Username TEXT DEFAULT ' ', + CurrentVeterancy TEXT DEFAULT 'None', + TotalKills INTEGER NOT NULL DEFAULT 0, + DecaptedKills INTEGER NOT NULL DEFAULT 0, + TotalMeleeDamage INTEGER NOT NULL DEFAULT 0, + MeleeKills INTEGER NOT NULL DEFAULT 0, + PowerWpnKills INTEGER NOT NULL DEFAULT 0, + BullpupDamage INTEGER NOT NULL DEFAULT 0, + StalkerKills INTEGER NOT NULL DEFAULT 0, + TotalWelded INTEGER NOT NULL DEFAULT 0, + TotalHealed INTEGER NOT NULL DEFAULT 0, + TotalPlaytime INTEGER NOT NULL DEFAULT 0, + GamesWon INTEGER NOT NULL DEFAULT 0, + GamesLost INTEGER NOT NULL DEFAULT 0 +); |