From 652852e3104ce4020de53231ee7691a4970439d6 Mon Sep 17 00:00:00 2001 From: Tor Andersson Date: Sat, 1 May 2021 00:48:35 +0200 Subject: Add server and lobby code. --- tools/editgame.sh | 8 +++++ tools/makecert.sh | 6 ++++ tools/purge.sh | 6 ++++ tools/readgame.sh | 10 ++++++ tools/sql/schema.txt | 86 ++++++++++++++++++++++++++++++++++++++++++++++++++++ tools/start.sh | 2 ++ tools/stop.sh | 2 ++ tools/writegame.sh | 7 +++++ 8 files changed, 127 insertions(+) create mode 100644 tools/editgame.sh create mode 100644 tools/makecert.sh create mode 100644 tools/purge.sh create mode 100644 tools/readgame.sh create mode 100644 tools/sql/schema.txt create mode 100644 tools/start.sh create mode 100644 tools/stop.sh create mode 100644 tools/writegame.sh (limited to 'tools') 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 -- cgit v1.2.3