Move mtime and active into games table.

Avoid joining with game_state for all the game list views.

1 files changed, 38 insertions, 28 deletions

diff --git a/server.js b/server.js
index 9133f46..3e23a74 100644
--- a/server.js
+++ b/server.js
@@ -1129,6 +1129,31 @@ load_rules()
 const SQL_INSERT_GAME = SQL("INSERT INTO games (owner_id,title_id,scenario,options,player_count,is_private,is_random,notice) VALUES (?,?,?,?,?,?,?,?)")
 const SQL_DELETE_GAME = SQL("DELETE FROM games WHERE game_id=? AND owner_id=?")
 
+const SQL_START_GAME = SQL(`
+	update games set
+		status = 1,
+		is_private = (is_private or user_count < player_count),
+		mtime = datetime(),
+		active = ?
+	where
+		game_id = ?
+`)
+
+const SQL_FINISH_GAME = SQL(`
+	update games set
+		status = 2,
+		mtime = datetime(),
+		active = null,
+		result = ?
+	where
+		game_id = ?
+`)
+
+const SQL_UPDATE_GAME_ACTIVE = SQL("update games set active=?,mtime=datetime(),moves=moves+1 where game_id=?")
+
+const SQL_SELECT_GAME_STATE = SQL("select state from game_state where game_id=?").pluck()
+const SQL_INSERT_GAME_STATE = SQL("insert or replace into game_state (game_id,state) values (?,?)")
+
 const SQL_SELECT_UNREAD_CHAT_GAMES = SQL("select game_id from unread_chats where user_id = ?").pluck()
 const SQL_INSERT_UNREAD_CHAT = SQL("insert or ignore into unread_chats (user_id,game_id) values (?,?)")
 const SQL_DELETE_UNREAD_CHAT = SQL("delete from unread_chats where user_id = ? and game_id = ?")
@@ -1140,13 +1165,6 @@ const SQL_SELECT_GAME_NOTE = SQL("SELECT note FROM game_notes WHERE game_id=? AN
 const SQL_UPDATE_GAME_NOTE = SQL("INSERT OR REPLACE INTO game_notes (game_id,role,note) VALUES (?,?,?)")
 const SQL_DELETE_GAME_NOTE = SQL("DELETE FROM game_notes WHERE game_id=? AND role=?")
 
-const SQL_SELECT_GAME_STATE = SQL("SELECT state FROM game_state WHERE game_id=?").pluck()
-const SQL_INSERT_GAME_STATE = SQL("insert into game_state (game_id,state,active,mtime) values (?,?,?,datetime())")
-const SQL_UPDATE_GAME_STATE = SQL("update game_state set state=?, active=? where game_id=?")
-const SQL_UPDATE_GAME_MTIME = SQL("update game_state set mtime=datetime() where game_id=?")
-const SQL_UPDATE_GAME_RESULT = SQL("UPDATE games SET status=?, result=?, xtime=datetime() WHERE game_id=?")
-const SQL_UPDATE_GAME_PRIVATE = SQL("UPDATE games SET is_private=1 WHERE game_id=?")
-
 const SQL_INSERT_REPLAY = SQL("insert into game_replay (game_id,replay_id,role,action,arguments) values (?, (select coalesce(max(replay_id), 0) + 1 from game_replay where game_id=?) ,?,?,?)")
 
 const SQL_INSERT_SNAP = SQL("insert into game_snap (game_id,snap_id,state) values (?, (select coalesce(max(snap_id), 0) + 1 from game_snap where game_id=?), ?) returning snap_id").pluck()
@@ -1200,7 +1218,6 @@ const SQL_SELECT_PLAYER_ROLE = SQL("SELECT role FROM players WHERE game_id=? AND
 const SQL_INSERT_PLAYER_ROLE = SQL("INSERT OR IGNORE INTO players (game_id,role,user_id,is_invite) VALUES (?,?,?,?)")
 const SQL_DELETE_PLAYER_ROLE = SQL("DELETE FROM players WHERE game_id=? AND role=?")
 
-const SQL_SELECT_OPEN_GAMES = SQL(`SELECT * FROM games WHERE status=${STATUS_OPEN}`)
 const SQL_COUNT_OPEN_GAMES = SQL(`SELECT COUNT(*) FROM games WHERE owner_id=? AND status=${STATUS_OPEN}`).pluck()
 const SQL_COUNT_ACTIVE_GAMES = SQL(`
 	select count(*) from games
@@ -1275,7 +1292,6 @@ const QUERY_LIST_GAMES_OF_TITLE_FINISHED = SQL(`
 const QUERY_NEXT_GAME_OF_USER = SQL(`
 	select title_id, game_id, role
 	from games
-	join game_state using(game_id)
 	join players using(game_id)
 	where
 		status = ${STATUS_ACTIVE}
@@ -1812,13 +1828,10 @@ function start_game(game) {
 		if (game.is_random)
 			assign_random_roles(game, options, SQL_SELECT_PLAYERS.all(game.game_id))
 
-		SQL_UPDATE_GAME_RESULT.run(1, null, game.game_id)
-		if (game.user_count !== game.player_count)
-			SQL_UPDATE_GAME_PRIVATE.run(game.game_id)
-
+		SQL_START_GAME.run(state.active, game.game_id)
 		put_replay(game.game_id, null, ".setup", [seed, game.scenario, options])
 		put_snap(game.game_id, state)
-		SQL_INSERT_GAME_STATE.run(game.game_id, JSON.stringify(state), state.active)
+		SQL_INSERT_GAME_STATE.run(game.game_id, JSON.stringify(state))
 
 		SQL_COMMIT.run()
 	} finally {
@@ -1873,7 +1886,7 @@ app.get('/api/replay/:game_id', function (req, res) {
  * add role_rating to Ev and update role_rating with low K-value
  */
 
-const SQL_SELECT_RATING_GAME = SQL("select title_id, player_count, scenario, result, xtime from games where game_id=? and status>1 and player_count=user_count and player_count>1")
+const SQL_SELECT_RATING_GAME = SQL("select * from rated_games_view where game_id=?")
 const SQL_SELECT_RATING_PLAYERS = SQL("select * from player_rating_view where game_id=?")
 const SQL_INSERT_RATING = SQL("insert or replace into ratings (title_id,user_id,rating,count,last) values (?,?,?,?,?)")
 
@@ -1915,7 +1928,7 @@ function update_elo_ratings(game_id) {
 			p.change = elo_change(p, players, 1 / players.length)
 
 	for (let p of players)
-		SQL_INSERT_RATING.run(game.title_id, p.user_id, p.rating + p.change, p.count + 1, game.xtime)
+		SQL_INSERT_RATING.run(game.title_id, p.user_id, p.rating + p.change, p.count + 1, game.mtime)
 }
 
 /*
@@ -2098,6 +2111,7 @@ function send_play_notification(user, game_id, message) {
 	send_notification(user, game_play_link(game_id, title_id, user), `${title_name} #${game_id} (${user.role}) - ${message}`)
 }
 
+const QUERY_LIST_READY_TO_START = SQL("select * from ready_to_start_reminder")
 const QUERY_LIST_YOUR_TURN = SQL("SELECT * FROM your_turn_reminder")
 const QUERY_LIST_INVITES = SQL("SELECT * FROM invite_reminder")
 
@@ -2160,15 +2174,11 @@ function notify_invited_reminder() {
 }
 
 function notify_ready_to_start_reminder() {
-	for (let game of SQL_SELECT_OPEN_GAMES.all()) {
-		let players = SQL_SELECT_PLAYERS.all(game.game_id)
-		if (is_game_ready(game.player_count, players)) {
-			if (!is_player_online(game.game_id, game.owner_id)) {
-				let owner = SQL_SELECT_USER_VIEW.get(game.owner_id)
-				if (should_send_reminder(owner, game.game_id)) {
-					insert_last_notified(owner, game.game_id)
-					send_join_notification(owner, game.game_id, "Ready to start")
-				}
+	for (let item of QUERY_LIST_READY_TO_START.all()) {
+		if (!is_player_online(item.game_id, item.user_id)) {
+			if (should_send_reminder(item, item.game_id)) {
+				insert_last_notified(item, item.game_id)
+				send_join_notification(item, item.game_id, "Ready to start")
 			}
 		}
 	}
@@ -2260,16 +2270,16 @@ function put_snap(game_id, state) {
 function put_game_state(game_id, state, old_active, current_role) {
 	// TODO: separate state, undo, and log entries (and reuse "snap" json stringifaction?)
 
-	SQL_UPDATE_GAME_STATE.run(JSON.stringify(state), state.active, game_id)
+	SQL_INSERT_GAME_STATE.run(game_id, JSON.stringify(state))
 
 	if (state.active !== old_active) {
 		// TODO: add time spent for old_active players
 		// TODO: add time available for new_active players
-		SQL_UPDATE_GAME_MTIME.run(game_id)
+		SQL_UPDATE_GAME_ACTIVE.run(state.active, game_id)
 	}
 
 	if (state.state === "game_over") {
-		SQL_UPDATE_GAME_RESULT.run(2, state.result, game_id)
+		SQL_FINISH_GAME.run(state.result, game_id)
 		if (state.result && state.result !== "None")
 			update_elo_ratings(game_id)
 	}