Add server and lobby code.

8 files changed, 127 insertions, 0 deletions

diff --git a/tools/editgame.sh b/tools/editgame.sh
new file mode 100644
index 0000000..7f21fcb
--- /dev/null
+++ b/tools/editgame.sh
@@ -0,0 +1,8 @@
+#!/bin/bash
+if [ -n "$1" -a -n "$VISUAL" ]
+then
+	sqlite3 db "update games set state=edit(state) where game_id = $1"
+else
+	echo "usage: bash tools/editgame.sh GAME"
+	echo "note: \$VISUAL must be set to your preferred editor"
+fi
diff --git a/tools/makecert.sh b/tools/makecert.sh
new file mode 100644
index 0000000..94d8bb7
--- /dev/null
+++ b/tools/makecert.sh
@@ -0,0 +1,6 @@
+#!/bin/bash
+#
+# Create a simple self-signed SSL certificate.
+#
+
+openssl req -nodes -new -x509 -keyout key.pem -out cert.pem
diff --git a/tools/purge.sh b/tools/purge.sh
new file mode 100644
index 0000000..8c1ea7e
--- /dev/null
+++ b/tools/purge.sh
@@ -0,0 +1,6 @@
+#!/bin/bash
+
+# Clean out stale games from the database.
+
+sqlite3 db "DELETE FROM games WHERE status = 0 AND mtime < datetime('now', '-7 days')"
+sqlite3 db "UPDATE games SET status = 3 WHERE status = 1 AND mtime < datetime('now', '-28 days')"
diff --git a/tools/readgame.sh b/tools/readgame.sh
new file mode 100644
index 0000000..5bf93de
--- /dev/null
+++ b/tools/readgame.sh
@@ -0,0 +1,10 @@
+#!/bin/bash
+if [ -n "$1" -a -n "$2" ]
+then
+	sqlite3 db "select writefile('$2',state) from games where game_id = $1"
+elif [ -n "$1" ]
+then
+	sqlite3 db "select state from games where game_id = $1"
+else
+	echo "usage: bash tools/readgame.sh GAME [ state.json ]"
+fi
diff --git a/tools/sql/schema.txt b/tools/sql/schema.txt
new file mode 100644
index 0000000..97e8b25
--- /dev/null
+++ b/tools/sql/schema.txt
@@ -0,0 +1,86 @@
+CREATE TABLE IF NOT EXISTS users (
+	user_id INTEGER PRIMARY KEY,
+	name TEXT UNIQUE,
+	mail TEXT UNIQUE,
+	password TEXT,
+	salt TEXT,
+	ctime TIMESTAMP,
+	cip TEXT,
+	atime TIMESTAMP,
+	aip TEXT
+);
+
+CREATE TABLE IF NOT EXISTS blacklist_ip ( ip TEXT PRIMARY KEY );
+CREATE TABLE IF NOT EXISTS blacklist_mail ( mail TEXT PRIMARY KEY );
+
+CREATE TABLE IF NOT EXISTS titles (
+	title_id TEXT UNIQUE,
+	title_name TEXT,
+	bgg INTEGER
+);
+
+CREATE TABLE IF NOT EXISTS roles (
+	title_id TEXT,
+	role TEXT,
+	UNIQUE ( title_id, role )
+);
+
+CREATE TABLE IF NOT EXISTS games (
+	game_id INTEGER PRIMARY KEY,
+	title_id TEXT,
+	scenario TEXT,
+	owner INTEGER,
+	private BOOLEAN,
+	ctime TIMESTAMP,
+	mtime TIMESTAMP,
+	description TEXT,
+	status INTEGER,
+	active TEXT,
+	result TEXT,
+	state TEXT,
+	chat TEXT
+);
+
+CREATE TABLE IF NOT EXISTS players (
+	user_id INTEGER,
+	game_id INTEGER,
+	role TEXT,
+	UNIQUE ( game_id, user_id, role ),
+	UNIQUE ( game_id, role )
+);
+
+DROP TRIGGER IF EXISTS no_part_on_active_game;
+CREATE TRIGGER no_part_on_active_game BEFORE DELETE ON players
+BEGIN
+	SELECT CASE
+	WHEN ( SELECT status FROM games WHERE game_id = old.game_id ) > 0
+	THEN RAISE(ABORT, "Cannot remove players from started games.")
+	END;
+END;
+
+DROP TRIGGER IF EXISTS no_join_on_active_game;
+CREATE TRIGGER no_join_on_active_game BEFORE INSERT ON players
+BEGIN
+	SELECT CASE
+	WHEN ( SELECT status FROM games WHERE game_id = new.game_id ) > 0
+	THEN RAISE(ABORT, "Cannot add players to started games.")
+	END;
+END;
+
+DROP TRIGGER IF EXISTS must_be_valid_role;
+CREATE TRIGGER must_be_valid_role BEFORE INSERT ON players
+BEGIN
+	SELECT CASE
+	WHEN ( SELECT COUNT(*) FROM roles, games WHERE
+		roles.title_id = games.title_id AND
+		games.game_id = new.game_id AND
+		roles.role = new.role ) <> 1
+	THEN RAISE(ABORT, "Invalid role for that title.")
+	END;
+END;
+
+DROP TRIGGER IF EXISTS purge_players;
+CREATE TRIGGER purge_players AFTER DELETE ON games
+BEGIN
+	DELETE FROM players WHERE game_id = old.game_id;
+END;
diff --git a/tools/start.sh b/tools/start.sh
new file mode 100644
index 0000000..eb5240a
--- /dev/null
+++ b/tools/start.sh
@@ -0,0 +1,2 @@
+#!/bin/bash
+forever start -a --uid rally --killSignal=SIGTERM -c 'nodemon --exitcrash' server.js
diff --git a/tools/stop.sh b/tools/stop.sh
new file mode 100644
index 0000000..39f920a
--- /dev/null
+++ b/tools/stop.sh
@@ -0,0 +1,2 @@
+#!/bin/bash
+forever stop rally
diff --git a/tools/writegame.sh b/tools/writegame.sh
new file mode 100644
index 0000000..82cff23
--- /dev/null
+++ b/tools/writegame.sh
@@ -0,0 +1,7 @@
+#!/bin/bash
+if [ -n "$1" -a -f "$2" ]
+then
+	sqlite3 db "update games set state=readfile('$2') where game_id = $1"
+else
+	echo "usage: bash tools/writegame.sh GAME state.json"
+fi