diff options
| -rw-r--r-- | public/common/client.js | 2 | ||||
| -rw-r--r-- | public/join.js | 15 | ||||
| -rw-r--r-- | public/style.css | 2 | ||||
| -rw-r--r-- | server.js | 293 | ||||
| -rw-r--r-- | tools/sql/schema.txt | 53 | ||||
| -rw-r--r-- | views/games.ejs | 28 | ||||
| -rw-r--r-- | views/info.ejs | 23 | ||||
| -rw-r--r-- | views/join.ejs | 1 | ||||
| -rw-r--r-- | views/login.ejs | 2 | ||||
| -rw-r--r-- | views/profile.ejs | 18 |
10 files changed, 176 insertions, 261 deletions
diff --git a/public/common/client.js b/public/common/client.js index 28b4f8e..670fa07 100644 --- a/public/common/client.js +++ b/public/common/client.js @@ -220,7 +220,7 @@ function init_client(roles) { document.querySelector(".grid_top").classList.add(player); for (let i = 0; i < roles.length; ++i) { let p = players.find(p => p.role == roles[i]); - document.querySelector(USER_SEL[i]).textContent = p ? p.name : "NONE"; + document.querySelector(USER_SEL[i]).textContent = p ? p.user_name : "NONE"; } }); diff --git a/public/join.js b/public/join.js index d2d39ab..5353f11 100644 --- a/public/join.js +++ b/public/join.js @@ -84,24 +84,23 @@ function update() { let element = document.getElementById(role_id); if (player) { if (game.status > 0) { - if (game.active === role || game.active === "Both" || game.active === "All") - element.className = "your_turn"; + if (game.active_role === role || game.active_role === "Both" || game.active_role === "All") + element.className = "is_your_turn"; else element.className = ""; if (player.user_id === user_id) element.innerHTML = `<a href="/play/${game.game_id}/${role}">Play</a>`; else - element.innerHTML = player.name; + element.innerHTML = player.user_name; } else { if ((player.user_id === user_id) || (game.owner_id === user_id)) - element.innerHTML = `<a class="red" href="javascript:send('/part/${game.game_id}/${role}')">\u274c</a> ${player.name}`; + element.innerHTML = `<a class="red" href="javascript:send('/part/${game.game_id}/${role}')">\u274c</a> ${player.user_name}`; else - element.innerHTML = player.name; + element.innerHTML = player.user_name; } } else { if (game.status === 0) - //element.innerHTML = `<a class="join" href="javascript:send('/join/${game.game_id}/${role}')">Join</a>`; - element.innerHTML = `<a class="join" onclick="send('/join/${game.game_id}/${role}')" href="javascript:void 0">Join</a>`; + element.innerHTML = `<a class="join" href="javascript:send('/join/${game.game_id}/${role}')">Join</a>`; else element.innerHTML = "<i>Empty</i>"; } @@ -122,7 +121,7 @@ function update() { if (game.owner_id === user_id) { window.start_button.disabled = !ready; window.start_button.classList = (game.status === 0) ? "" : "hide"; - window.delete_button.classList = (game.status === 0 || solo) ? "" : "hide"; + window.delete_button.classList = (game.status === 0 || game.is_solo) ? "" : "hide"; } if (game.status === 0 && ready) diff --git a/public/style.css b/public/style.css index 0503574..d5c4230 100644 --- a/public/style.css +++ b/public/style.css @@ -39,7 +39,7 @@ input[type="text"], input[type="password"] { padding: 5px; } select { padding-right: 20px; } form { display: inline; } .nowrap { white-space: nowrap; } -.your_turn { background-color: lemonchiffon; } +.is_your_turn { background-color: lemonchiffon; } button, select { margin: 5px 10px 5px 0; padding: 1px 10px; @@ -21,20 +21,21 @@ const MAX_OPEN_GAMES = 3; let session_store = new SQLiteStore(); let db = new sqlite3(process.env.DATABASE || "./db"); let app = express(); -let http_port = process.env.PORT || 8080; let https_port = process.env.HTTPS_PORT || 8443; -let http = require('http').createServer(app); let https = require('https').createServer({ key: fs.readFileSync(process.env.SSL_KEY || "key.pem"), cert: fs.readFileSync(process.env.SSL_CERT || "cert.pem") }, app); -let socket_io = require('socket.io'); -let io1 = socket_io(http); -let io2 = socket_io(https); -let io = { - use: function (fn) { io1.use(fn); io2.use(fn); }, - on: function (ev,fn) { io1.on(ev,fn); io2.on(ev,fn); }, -}; +https.listen(https_port, '0.0.0.0', () => { console.log('listening HTTPS on *:' + https_port); }); + +let io = require('socket.io')(https); + +// REDIRECT HTTP TO HTTPS +let http_port = process.env.HTTP_PORT || 8080; +let http_app = express(); +let http_server = require('http').createServer(http_app); +http_app.use((req, res) => res.redirect(301, 'https://' + req.hostname + ":" + https_port + req.path)); +http_server.listen(http_port, '0.0.0.0', () => { console.log('listening HTTP on *:' + http_port); }); let mailer = null; if (process.env.MAIL_HOST && process.env.MAIL_PORT) { @@ -315,31 +316,6 @@ app.post('/signup', }) ); -app.get('/users', function (req, res) { - LOG(req, "GET /users"); - let rows = db.prepare("SELECT name, mail, ctime, atime FROM users ORDER BY atime DESC").all(); - rows.forEach(row => { - row.avatar = get_avatar(row.mail); - row.ctime = human_date(row.ctime); - row.atime = human_date(row.atime); - }); - res.render('users.ejs', { user: req.user, message: req.flash('message'), userList: rows }); -}); - -const QUERY_STATS = db.prepare(` - SELECT title_name, scenario, result, count(*) AS count - FROM games - JOIN titles ON games.title_id=titles.title_id - WHERE status=2 AND private=0 - GROUP BY title_name, scenario, result - `); - -app.get('/stats', function (req, res) { - LOG(req, "GET /stats"); - let stats = QUERY_STATS.all(); - res.render('stats.ejs', { user: req.user, message: req.flash('message'), stats: stats }); -}); - app.get('/change_password', must_be_logged_in, function (req, res) { LOG(req, "GET /change_password"); res.render('change_password.ejs', { user: req.user, message: req.flash('message') }); @@ -499,115 +475,33 @@ app.post('/change_password', must_be_logged_in, function (req, res) { */ let RULES = {}; -let PLAYER_COUNT = {}; -let QUERY_PLAYER_COUNT = db.prepare("SELECT COUNT(*) FROM roles WHERE title_id = ?").pluck(); for (let title_id of db.prepare("SELECT * FROM titles").pluck().all()) { console.log("Loading rules for " + title_id); try { RULES[title_id] = require("./public/" + title_id + "/rules.js"); - PLAYER_COUNT[title_id] = QUERY_PLAYER_COUNT.get(title_id); } catch (err) { console.log(err); } } -const QUERY_GAME = db.prepare(` - SELECT - games.game_id, - games.title_id AS title_id, - titles.title_name AS title_name, - games.scenario AS scenario, - games.owner AS owner_id, - users.name AS owner_name, - games.ctime, - games.mtime, - games.description, - games.status, - games.private, - games.random, - games.result, - games.active - FROM games - JOIN users ON games.owner = users.user_id - JOIN titles ON games.title_id = titles.title_id - WHERE game_id = ? -`); - -const QUERY_LIST_GAMES = db.prepare(` - SELECT - games.game_id, - games.title_id AS title_id, - games.scenario AS scenario, - games.owner AS owner_id, - users.name AS owner_name, - games.ctime, - games.mtime, - games.description, - games.status, - games.result, - games.active, - titles.title_name - FROM games - LEFT JOIN users ON games.owner = users.user_id - LEFT JOIN titles ON games.title_id = titles.title_id - WHERE private = 0 AND status < 2 -`); - +const QUERY_GAME = db.prepare("SELECT * FROM game_view WHERE game_id = ?"); const QUERY_LIST_GAMES_OF_TITLE = db.prepare(` - SELECT - games.game_id, - games.title_id AS title_id, - games.scenario AS scenario, - games.owner AS owner_id, - users.name AS owner_name, - games.ctime, - games.mtime, - games.description, - games.status, - games.result, - games.active - FROM games - JOIN users ON games.owner = users.user_id + SELECT *, ( active_id = ? OR active_role = 'Both' OR active_role = 'All' ) AS is_your_turn FROM game_view WHERE title_id = ? AND private = 0 ORDER BY status ASC, mtime DESC `); - const QUERY_LIST_GAMES_OF_USER = db.prepare(` - SELECT DISTINCT - games.game_id, - games.title_id, - titles.title_name, - games.scenario AS scenario, - users.name AS owner_name, - games.description, - games.ctime, - games.mtime, - games.status, - games.result, - games.active - FROM games - LEFT JOIN players ON games.game_id = players.game_id - LEFT JOIN users ON games.owner = users.user_id - LEFT JOIN titles ON games.title_id = titles.title_id - WHERE games.owner = ? OR players.user_id = ? + SELECT DISTINCT game_view.*, ( active_id = $user_id OR active_role = 'Both' OR active_role = 'All' ) AS is_your_turn FROM game_view + LEFT JOIN players ON game_view.game_id = players.game_id + WHERE game_view.owner_id = $user_id OR players.user_id = $user_id ORDER BY status ASC, mtime DESC `); - -const QUERY_PLAYERS = db.prepare(` - SELECT - players.user_id, - players.role, - users.name - FROM players - JOIN users ON players.user_id = users.user_id - WHERE players.game_id = ? -`); - +const QUERY_PLAYERS = db.prepare("SELECT role, user_id, user_name FROM player_view WHERE game_id = ?"); const QUERY_PLAYERS_FULL = db.prepare(` SELECT players.user_id, players.role, - users.name, + users.name AS user_name, users.mail, users.notifications FROM players @@ -615,18 +509,9 @@ const QUERY_PLAYERS_FULL = db.prepare(` WHERE players.game_id = ? `); -const QUERY_PLAYER_NAMES = db.prepare(` - SELECT - users.name AS name - FROM players - JOIN users ON players.user_id = users.user_id - WHERE players.game_id = ? - ORDER BY players.role -`).pluck(); - const QUERY_TITLE = db.prepare("SELECT * FROM titles WHERE title_id = ?"); -const QUERY_ROLES = db.prepare("SELECT role FROM roles WHERE title_id = ?").pluck(); -const QUERY_GAME_OWNER = db.prepare("SELECT * FROM games WHERE game_id = ? AND owner = ?"); +const QUERY_ROLES = db.prepare("SELECT role FROM roles WHERE title_id = ? ORDER BY rowid").pluck(); +const QUERY_GAME_OWNER = db.prepare("SELECT * FROM games WHERE game_id = ? AND owner_id = ?"); const QUERY_TITLE_FROM_GAME = db.prepare("SELECT title_id FROM games WHERE game_id = ?").pluck(); const QUERY_ROLE_FROM_GAME_AND_USER = db.prepare("SELECT role FROM players WHERE game_id = ? AND user_id = ?").pluck(); const QUERY_IS_SOLO = db.prepare("SELECT COUNT(DISTINCT user_id) = 1 FROM players WHERE game_id = ?").pluck(); @@ -636,16 +521,14 @@ const QUERY_PART_GAME = db.prepare("DELETE FROM players WHERE game_id = ? AND ro const QUERY_START_GAME = db.prepare("UPDATE games SET status = 1, state = ?, active = ? WHERE game_id = ?"); const QUERY_CREATE_GAME = db.prepare(` INSERT INTO games - (owner,title_id,scenario,private,random,ctime,mtime,description,status,state) + (owner_id,title_id,scenario,private,random,ctime,mtime,description,status,state) VALUES (?,?,?,?,?,datetime('now'),datetime('now'),?,0,NULL) `); const QUERY_UPDATE_GAME_SET_PRIVATE = db.prepare("UPDATE games SET private = 1 WHERE game_id = ?"); const QUERY_ASSIGN_ROLE = db.prepare("UPDATE players SET role = ? WHERE game_id = ? AND user_id = ? AND role = ?"); -const QUERY_IS_PLAYER = db.prepare("SELECT COUNT(*) FROM players WHERE game_id = ? AND user_id = ?").pluck(); -const QUERY_IS_ACTIVE = db.prepare("SELECT COUNT(*) FROM players WHERE game_id = ? AND role = ? AND user_id = ?").pluck(); -const QUERY_COUNT_OPEN_GAMES = db.prepare("SELECT COUNT(*) FROM games WHERE owner = ? AND status = 0").pluck(); +const QUERY_COUNT_OPEN_GAMES = db.prepare("SELECT COUNT(*) FROM games WHERE owner_id = ? AND status = 0").pluck(); const QUERY_DELETE_GAME = db.prepare("DELETE FROM games WHERE game_id = ?"); const QUERY_REMATCH_FIND = db.prepare(` @@ -654,7 +537,7 @@ const QUERY_REMATCH_FIND = db.prepare(` const QUERY_REMATCH_CREATE = db.prepare(` INSERT INTO games - (owner, title_id, scenario, private, random, ctime, mtime, description, status, state) + (owner_id, title_id, scenario, private, random, ctime, mtime, description, status, state) SELECT $user_id, title_id, scenario, private, random, datetime('now'), datetime('now'), $magic, 0, NULL FROM games @@ -667,23 +550,11 @@ app.get('/', function (req, res) { res.render('index.ejs', { user: req.user, message: req.flash('message') }); }); -function is_your_turn(game, user) { - if (!game.active || game.active == "None") - return false; - if (game.active == "All" || game.active == "Both") - return QUERY_IS_PLAYER.get(game.game_id, user.user_id); - return QUERY_IS_ACTIVE.get(game.game_id, game.active, user.user_id); -} - app.get('/profile', must_be_logged_in, function (req, res) { LOG(req, "GET /profile"); let avatar = get_avatar(req.user.mail); - let games = QUERY_LIST_GAMES_OF_USER.all(req.user.user_id, req.user.user_id); + let games = QUERY_LIST_GAMES_OF_USER.all({user_id: req.user.user_id}); humanize(games); - for (let game of games) { - game.players = QUERY_PLAYER_NAMES.all(game.game_id); - game.your_turn = is_your_turn(game, req.user); - } let open_games = games.filter(game => game.status == 0); let active_games = games.filter(game => game.status == 1); let finished_games = games.filter(game => game.status == 2); @@ -696,24 +567,6 @@ app.get('/profile', must_be_logged_in, function (req, res) { }); }); -app.get('/games', must_be_logged_in, function (req, res) { - LOG(req, "GET /join"); - let games = QUERY_LIST_GAMES.all(); - humanize(games); - for (let game of games) { - game.players = QUERY_PLAYER_NAMES.all(game.game_id); - game.your_turn = is_your_turn(game, req.user); - } - let open_games = games.filter(game => game.status == 0); - let active_games = games.filter(game => game.status == 1); - res.set("Cache-Control", "no-store"); - res.render('games.ejs', { user: req.user, - open_games: open_games, - active_games: active_games, - message: req.flash('message') - }); -}); - app.get('/info/:title_id', function (req, res) { LOG(req, "GET /info/" + req.params.title_id); let title_id = req.params.title_id; @@ -723,17 +576,11 @@ app.get('/info/:title_id', function (req, res) { return res.redirect('/'); } if (req.isAuthenticated()) { - let games = QUERY_LIST_GAMES_OF_TITLE.all(title_id); + let games = QUERY_LIST_GAMES_OF_TITLE.all(req.user.user_id, title_id); humanize(games); let open_games = games.filter(game => game.status == 0); let active_games = games.filter(game => game.status == 1); - for (let game of active_games) { - game.players = QUERY_PLAYER_NAMES.all(game.game_id); - game.your_turn = is_your_turn(game, req.user); - } let finished_games = games.filter(game => game.status == 2); - for (let game of finished_games) - game.players = QUERY_PLAYER_NAMES.all(game.game_id); res.set("Cache-Control", "no-store"); res.render('info.ejs', { user: req.user, title: title, open_games: open_games, @@ -835,9 +682,8 @@ let join_clients = {}; function update_join_clients_deleted(game_id) { let list = join_clients[game_id]; if (list && list.length > 0) { - console.log("UPDATE JOIN DELETED", game_id, list.length) + console.log("JOIN: UPDATE GAME DELETED", game_id, list.title_id, list.length, players.map(p => p.user_name)); for (let res of list) { - console.log("PUSH JOIN DELETED", game_id); res.write("retry: 15000\n"); res.write("event: deleted\n"); res.write("data: The game doesn't exist.\n\n"); @@ -848,10 +694,9 @@ function update_join_clients_deleted(game_id) { function update_join_clients_game(game_id) { let list = join_clients[game_id]; if (list && list.length > 0) { - console.log("UPDATE JOIN GAME", game_id, list.length) let game = QUERY_GAME.get(game_id); + console.log("JOIN: UPDATE GAME STATUS", game_id, list.title_id, list.length, game) for (let res of list) { - console.log("PUSH JOIN GAME", game_id); res.write("retry: 15000\n"); res.write("event: game\n"); res.write("data: " + JSON.stringify(game) + "\n\n"); @@ -861,12 +706,11 @@ function update_join_clients_game(game_id) { function update_join_clients_players(game_id) { let list = join_clients[game_id]; - if (list) { - console.log("UPDATE JOIN PLAYERS", game_id, list.length) + if (list && list.length > 0) { let players = QUERY_PLAYERS.all(game_id); let ready = RULES[list.title_id].ready(list.scenario, players); + console.log("JOIN: UPDATE PLAYERS", game_id, list.title_id, list.length, players.map(p => p.role + ": " + p.user_name), ready) for (let res of list) { - console.log("PUSH JOIN PLAYERS", game_id); res.write("retry: 15000\n"); res.write("event: players\n"); res.write("data: " + JSON.stringify(players) + "\n\n"); @@ -893,8 +737,7 @@ app.get('/join/:game_id', must_be_logged_in, function (req, res) { game: game, roles: roles, players: players, - solo: players.every(p => p.user_id == req.user.user_id), - ready: players.length == roles.length, + ready: ready, message: req.flash('message') }); }); @@ -920,8 +763,8 @@ app.get('/join-events/:game_id', must_be_logged_in, function (req, res) { } join_clients[game_id].push(res); - res.on('close', err => { - console.log("CLOSE JOIN EVENTS", err); + res.on('close', () => { + console.log("JOIN: CLOSE CONNECTION TO", game_id); let list = join_clients[game_id]; let i = list.indexOf(res); if (i >= 0) @@ -974,7 +817,7 @@ function assign_random_roles(game, players) { for (let p of players) { let old_role = p.role; p.role = pick_random_item(roles); - console.log("ASSIGN ROLE", "(" + p.name + ")", old_role, "->", p.role); + console.log("ASSIGN ROLE", "(" + p.user_name + ")", old_role, "->", p.role); QUERY_ASSIGN_ROLE.run(p.role, game.game_id, p.user_id, old_role); } } @@ -1028,10 +871,13 @@ app.get('/play/:game_id', must_be_logged_in, function (req, res) { let game_id = req.params.game_id | 0; let user_id = req.user.user_id | 0; try { + let title = QUERY_TITLE_FROM_GAME.get(game_id); + if (!title) + return res.redirect('/join/'+game_id); let role = QUERY_ROLE_FROM_GAME_AND_USER.get(game_id, user_id); if (!role) - return res.redirect('/play/'+game_id+'/Observer'); - return res.redirect('/play/'+game_id+'/'+role); + res.redirect('/'+title+'/play.html?game='+game_id+'&role=Observer'); + return res.redirect('/'+title+'/play.html?game='+game_id+'&role='+role); } catch (err) { req.flash('message', err.toString()); return res.redirect('/join/'+game_id); @@ -1052,18 +898,12 @@ const sql_offline_user = db.prepare("SELECT * FROM users WHERE user_id = ? AND d const QUERY_LIST_YOUR_TURN = db.prepare(` SELECT games.game_id, games.title_id, players.user_id, users.name, users.mail, users.notifications FROM games - JOIN players ON games.game_id = players.game_id AND ( games.active = players.role OR games.active = 'All' OR games.active = 'Both' ) + JOIN players ON games.game_id = players.game_id AND ( games.active = players.role OR games.active = 'Both' OR games.active = 'All' ) JOIN users ON users.user_id = players.user_id WHERE games.status = 1 AND datetime('now') > datetime(games.mtime, '+1 hour') `); -const QUERY_LIST_READY_TO_START = db.prepare(` - SELECT games.game_id, games.title_id, games.owner, COUNT(*) AS joined - FROM games - JOIN players ON games.game_id = players.game_id - WHERE games.status = 0 - GROUP BY games.game_id -`); +const QUERY_LIST_UNSTARTED_GAMES = db.prepare("SELECT * FROM game_view WHERE status = 0"); function mail_callback(err, info) { console.log("MAIL SENT", err, info); @@ -1159,9 +999,10 @@ function notify_your_turn_reminder() { } function notify_ready_to_start_reminder() { - for (let game of QUERY_LIST_READY_TO_START.all()) { - if (game.joined == PLAYER_COUNT[game.title_id]) { - let owner = sql_offline_user.get(game.owner, '+3 minutes'); + for (let game of QUERY_LIST_UNSTARTED_GAMES.all()) { + let players = QUERY_PLAYERS.all(game.game_id); + if (RULES[game.title_id].ready(game.scenario, players)) { + let owner = sql_offline_user.get(game.owner_id, '+3 minutes'); if (owner) { console.log("REMINDER: READY TO START", game.title_id, game.game_id, owner.name, owner.mail, owner.notifications); if (owner.notifications) @@ -1171,10 +1012,10 @@ function notify_ready_to_start_reminder() { } } -// Check and send 'your turn' reminders quarterly. +// Check and send 'your turn' reminders every 15 minutes. setInterval(notify_your_turn_reminder, 15 * 60 * 1000); -// Check and send ready to start notifications once a minute. +// Check and send ready to start notifications every minute. setInterval(notify_ready_to_start_reminder, 60 * 1000); /* @@ -1426,5 +1267,45 @@ io.on('connection', (socket) => { } }); -http.listen(http_port, '0.0.0.0', () => { console.log('listening HTTP on *:' + http_port); }); -https.listen(https_port, '0.0.0.0', () => { console.log('listening HTTPS on *:' + https_port); }); +// EXTRAS + +const QUERY_STATS = db.prepare(` + SELECT title_name, scenario, result, count(*) AS count + FROM games + JOIN titles ON games.title_id=titles.title_id + WHERE status=2 AND private=0 + GROUP BY title_name, scenario, result + `); + +app.get('/stats', function (req, res) { + LOG(req, "GET /stats"); + let stats = QUERY_STATS.all(); + res.render('stats.ejs', { user: req.user, message: req.flash('message'), stats: stats }); +}); + +app.get('/users', function (req, res) { + LOG(req, "GET /users"); + let rows = db.prepare("SELECT name, mail, ctime, atime FROM users ORDER BY atime DESC").all(); + rows.forEach(row => { + row.avatar = get_avatar(row.mail); + row.ctime = human_date(row.ctime); + row.atime = human_date(row.atime); + }); + res.render('users.ejs', { user: req.user, message: req.flash('message'), userList: rows }); +}); + +const QUERY_LIST_GAMES = db.prepare("SELECT *, ( active_id = ? OR active_role = 'Both' OR active_role = 'All' ) AS is_your_turn FROM game_view WHERE private = 0 AND status < 2"); + +app.get('/games', must_be_logged_in, function (req, res) { + LOG(req, "GET /join"); + let games = QUERY_LIST_GAMES.all(req.user.user_id); + humanize(games); + let open_games = games.filter(game => game.status == 0); + let active_games = games.filter(game => game.status == 1); + res.set("Cache-Control", "no-store"); + res.render('games.ejs', { user: req.user, + open_games: open_games, + active_games: active_games, + message: req.flash('message') + }); +}); diff --git a/tools/sql/schema.txt b/tools/sql/schema.txt index 2f84e82..71b6f72 100644 --- a/tools/sql/schema.txt +++ b/tools/sql/schema.txt @@ -43,7 +43,7 @@ CREATE TABLE IF NOT EXISTS games ( game_id INTEGER PRIMARY KEY, title_id TEXT, scenario TEXT, - owner INTEGER, + owner_id INTEGER, private BOOLEAN, random BOOLEAN, description TEXT, @@ -111,3 +111,54 @@ BEGIN DELETE FROM notifications WHERE game_id = old.game_id; DELETE FROM chats WHERE game_id = old.game_id; END; + +DROP VIEW IF EXISTS player_view; +DROP VIEW IF EXISTS player_list_view; +DROP VIEW IF EXISTS game_view; + +CREATE VIEW player_view AS + SELECT players.game_id + , players.role AS role + , players.user_id AS user_id + , users.name AS user_name + FROM players + JOIN users ON players.user_id = users.user_id + ; + +CREATE VIEW player_list_view AS + SELECT game_id + , group_concat(name, ', ') AS player_names + , COUNT(DISTINCT user_id) AS user_count + , COUNT(user_id) AS role_count + FROM players + JOIN users USING ( user_id ) + GROUP BY game_id + ; + +CREATE VIEW game_view AS + SELECT games.game_id + , games.title_id + , titles.title_name + , games.scenario + , games.owner_id + , owner.name AS owner_name + , players.player_names + , players.user_count = 1 AS is_solo + , players.user_count <> players.role_count AS is_shared + , games.private + , games.random + , games.description + , games.ctime + , games.mtime + , games.status + , games.result + , games.active AS active_role + , active.user_id AS active_id + , active.user_name AS active_name + -- , games.state + FROM games + JOIN titles USING ( title_id ) + JOIN users AS owner ON games.owner_id = owner.user_id + LEFT JOIN player_list_view AS players USING ( game_id ) + LEFT JOIN player_view AS active ON games.game_id = active.game_id AND games.active = active.role + ; diff --git a/views/games.ejs b/views/games.ejs index fb58be8..4e6f2a4 100644 --- a/views/games.ejs +++ b/views/games.ejs @@ -5,52 +5,44 @@ td.nowrap a { color: black; text-decoration: none; } <h2>Open</h2> <table class="wide"> -<tr><th>ID<th>Title<th>Scenario<th>Owner<th>Description<th>Created<th>Players<th> +<tr><th>ID<th>Title<th>Scenario<th>Players<th>Description<th>Created<th> <% if (open_games.length > 0) { %> <% open_games.forEach((row) => { %> <tr> <td><%= row.game_id %> <td class="nowrap"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a> <td><%= row.scenario %> -<td><%= row.owner_name %> +<td><%= row.player_names || row.owner_name %> <td><%= row.description %> <td class="nowrap"><%= row.ctime %> -<td><%= row.players.join(", ") %> <td><a href="/join/<%= row.game_id %>">Join</a> <% }); } else { %> -<tr><td colspan="6">No open games. +<tr><td colspan="7">No open games. <% } %> </table> <h2>Active</h2> <table class="wide"> -<tr><th>ID<th>Title<th>Scenario<th>Description<th>Changed<th>Players<th>Active<th> +<tr><th>ID<th>Title<th>Scenario<th>Players<th>Description<th>Changed<th>Active<th> <% if (active_games.length > 0) { %> <% active_games.forEach((row) => { %> <tr> <td><%= row.game_id %> <td class="nowrap"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a> <td><%= row.scenario %> +<td><%= row.player_names %> <td><%= row.description %> <td class="nowrap"><%= row.mtime %> -<td><%= row.players.join(", ") %> <% - if (row.your_turn) { - %><td class="your_turn"><%= row.active %><% + if (row.is_your_turn) { + %><td class="is_your_turn"><%= row.active_role %><% } else { - %><td><%= row.active %><% - } - let me = row.players.reduce((n,p) => n + (p === user.name ? 1 : 0), 0); - if (me == 1) { - %><td><a href="/play/<%= row.game_id %>">Play</a><% - } else if (me > 1) { - %><td><a href="/join/<%= row.game_id %>">Play</a><% - } else { - %><td><a href="/join/<%= row.game_id %>">View</a><% + %><td><%= row.active_role %><% } + %><td><a href="/join/<%= row.game_id %>">Enter</a><% %> <% }); } else { %> -<tr><td colspan="6">No active games. +<tr><td colspan="8">No active games. <% } %> </table> diff --git a/views/info.ejs b/views/info.ejs index 55b8faa..02ce2dd 100644 --- a/views/info.ejs +++ b/views/info.ejs @@ -9,13 +9,13 @@ Read more about the game on <h2>Open Games</h2> <table class="wide"> -<tr><th>ID<th>Scenario<th>Owner<th>Description<th>Created<th> +<tr><th>ID<th>Scenario<th>Players<th>Description<th>Created<th> <% if (open_games.length > 0) { %> <% open_games.forEach((row) => { %> <tr> <td><%= row.game_id %> <td><%= row.scenario %> -<td><%= row.owner_name %> +<td><%= row.player_names || row.owner_name %> <td><%= row.description %> <td class="nowrap"><%= row.ctime %> <td><a href="/join/<%= row.game_id %>">Join</a> @@ -35,23 +35,16 @@ Read more about the game on <tr> <td><%= row.game_id %> <td><%= row.scenario %> -<td><%= row.players.join(", ") %> +<td><%= row.player_names %> <td><%= row.description %> <td class="nowrap"><%= row.mtime %> <% - if (row.your_turn) { - %><td class="your_turn"><%= row.active %><% + if (row.is_your_turn) { + %><td class="is_your_turn"><%= row.active_role %><% } else { - %><td><%= row.active %><% - } - let me = row.players.reduce((n,p) => n + (p === user.name ? 1 : 0), 0); - if (me == 1) { - %><td><a href="/play/<%= row.game_id %>">Play</a><% - } else if (me > 1) { - %><td><a href="/join/<%= row.game_id %>">Play</a><% - } else { - %><td><a href="/join/<%= row.game_id %>">View</a><% + %><td><%= row.active_role %><% } + %><td><a href="/join/<%= row.game_id %>">Enter</a><% %> <% }); %> </table> @@ -65,7 +58,7 @@ Read more about the game on <tr> <td><%= row.game_id %> <td><%= row.scenario %> -<td><%= row.players.join(", ") %> +<td><%= row.player_names %> <td><%= row.description %> <td class="nowrap"><%= row.mtime %> <td><%= row.result %> diff --git a/views/join.ejs b/views/join.ejs index 46c1e56..1d9347d 100644 --- a/views/join.ejs +++ b/views/join.ejs @@ -9,7 +9,6 @@ let game = <%- JSON.stringify(game) %>; let roles = <%- JSON.stringify(roles) %>; let players = <%- JSON.stringify(players) %>; let user_id = <%- user.user_id %>; -let solo = <%- solo %>; let ready = <%- ready %>; </script> <script src="/join.js"></script> diff --git a/views/login.ejs b/views/login.ejs index b4089fc..3e1dd43 100644 --- a/views/login.ejs +++ b/views/login.ejs @@ -4,7 +4,7 @@ <% } else { %> <form action="/login" method="post"> <p> -<label for="username">Name: </label><br> +<label for="username">Name or mail: </label><br> <input type="text" id="username" name="username" required> <p> <label for="password">Password: </label><br> diff --git a/views/profile.ejs b/views/profile.ejs index 02f6e72..7829870 100644 --- a/views/profile.ejs +++ b/views/profile.ejs @@ -36,7 +36,7 @@ Your mail address is <%= user.mail %>. <td><%= row.game_id %> <td class="nowrap"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a> <td><%= row.scenario %> -<td><%= row.players.join(", ") %> +<td><%= row.player_names %> <td><%= row.description %> <td class="nowrap"><%= row.ctime %> <td><a href="/join/<%= row.game_id %>">Join</a> @@ -53,19 +53,19 @@ Your mail address is <%= user.mail %>. <td><%= row.game_id %> <td class="nowrap"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a> <td><%= row.scenario %> -<td><%= row.players.join(", ") %> +<td><%= row.player_names %> <td><%= row.description %> <td class="nowrap"><%= row.mtime %> <% - if (row.your_turn) { - %><td class="your_turn"><%= row.active %><% + if (row.is_your_turn) { + %><td class="is_your_turn"><%= row.active_role %><% } else { - %><td><%= row.active %><% + %><td><%= row.active_role %><% } - if (row.players.reduce((n,p) => n + (p === user.name ? 1 : 0), 0) == 1) { - %><td><a href="/play/<%= row.game_id %>">Play</a><% + if (row.is_shared) { + %><td><a href="/join/<%= row.game_id %>">Enter</a><% } else { - %><td><a href="/join/<%= row.game_id %>">Play</a><% + %><td><a href="/play/<%= row.game_id %>">Play</a><% } %> <% }); %> @@ -81,7 +81,7 @@ Your mail address is <%= user.mail %>. <td><%= row.game_id %> <td class="nowrap"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a> <td><%= row.scenario %> -<td><%= row.players.join(", ") %> +<td><%= row.player_names %> <td><%= row.description %> <td class="nowrap"><%= row.mtime %> <td><%= row.result %> |