Move mtime and active into games table.

Avoid joining with game_state for all the game list views.

4 files changed, 92 insertions, 46 deletions

diff --git a/schema.sql b/schema.sql
index bebccb5..6d7fc13 100644
--- a/schema.sql
+++ b/schema.sql
@@ -111,7 +111,6 @@ create view user_dynamic_view as
 			from
 				players
 				join games using(game_id)
-				join game_state using(game_id)
 			where
 				status = 1
 				and user_count = player_count
@@ -149,6 +148,21 @@ create view user_dynamic_view as
 
 -- Elo ratings & match making --
 
+drop view if exists rated_games_view;
+create view rated_games_view as
+	select
+		game_id, title_id, player_count, scenario, result, mtime
+	from
+		games
+	where
+		status > 1
+		and user_count = player_count
+		and player_count > 1
+		and not exists (
+			select 1 from players where players.game_id = games.game_id and user_id = 0
+		)
+;
+
 create table if not exists ratings (
 	title_id integer,
 	user_id integer,
@@ -325,20 +339,27 @@ create virtual table if not exists forum_search using fts5(thread_id, post_id, t
 
 create table if not exists games (
 	game_id integer primary key,
+	status integer default 0,
+
 	title_id text,
 	scenario text,
 	options text,
-	player_count integer,
+
+	player_count integer default 2,
 	join_count integer default 0,
+	invite_count integer default 0,
 	user_count integer default 0,
+
 	owner_id integer default 0,
-	ctime datetime default current_timestamp,
+	notice text,
 	is_private boolean default 0,
 	is_random boolean default 0,
-	notice text,
-	status integer default 0,
-	result text,
-	xtime datetime
+
+	ctime datetime default current_timestamp,
+	mtime datetime default current_timestamp,
+	moves integer default 0,
+	active text,
+	result text
 );
 
 create index if not exists games_title_idx on games(title_id);
@@ -346,8 +367,6 @@ create index if not exists games_status_idx on games(status);
 
 create table if not exists game_state (
 	game_id integer primary key,
-	mtime datetime,
-	active text,
 	state text
 );
 
@@ -415,23 +434,38 @@ create view game_view as
 		games.*,
 		titles.title_name,
 		owner.name as owner_name,
-		coalesce(game_state.mtime, xtime) as mtime,
-		user_count = join_count and join_count > 1 as is_opposed,
-		game_state.active
+		user_count = join_count and join_count > 1 as is_opposed
 	from
 		games
-		natural left join game_state
 		natural join titles
 		join users as owner
 			on owner.user_id = games.owner_id
 	;
 
+drop view if exists ready_to_start_reminder;
+create view ready_to_start_reminder as
+	select
+		game_id, owner_id as user_id, name, mail, notify
+	from
+		games
+		join users on user_id = owner_id
+	where
+		status = 0
+		and join_count = player_count
+		and not exists (
+			select 1 from players
+			where
+				players.game_id = players.game_id
+				and is_invite
+		)
+	;
+
 drop view if exists your_turn_reminder;
 create view your_turn_reminder as
 	select
 		game_id, role, user_id, name, mail, notify
 	from
-		game_view
+		games
 		join players using(game_id)
 		join users using(user_id)
 	where
@@ -522,9 +556,9 @@ begin
 	delete from posts where author_id = old.user_id;
 	delete from threads where author_id = old.user_id;
 	delete from game_chat where user_id = old.user_id;
-	delete from players where user_id = old.user_id;
 	delete from ratings where user_id = old.user_id;
 	update games set owner_id = 0 where owner_id = old.user_id;
+	update players set user_id = 0 where user_id = old.user_id;
 end;
 
 drop trigger if exists trigger_delete_on_threads;
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)
 	}
diff --git a/tools/elo.js b/tools/elo.js
index 95ca58b..a6f9c9d 100644
--- a/tools/elo.js
+++ b/tools/elo.js
@@ -4,7 +4,7 @@ const sqlite3 = require("better-sqlite3")
 
 const db = new sqlite3("db")
 
-const SQL_SELECT_GAMES = db.prepare("select * from games where status>1 and user_count=player_count and player_count>1 order by xtime")
+const SQL_SELECT_GAMES = db.prepare("select * from rated_games_view order by mtime")
 const SQL_SELECT_RATING = db.prepare("select * from player_rating_view where game_id=?")
 const SQL_INSERT_RATING = db.prepare("insert or replace into ratings (title_id,user_id,rating,count,last) values (?,?,?,?,?)")
 
diff --git a/tools/patchgame.js b/tools/patchgame.js
index 6808704..afed8a3 100755
--- a/tools/patchgame.js
+++ b/tools/patchgame.js
@@ -13,7 +13,8 @@ let insert_replay = db.prepare("insert into game_replay (game_id,replay_id,role,
 let delete_snap = db.prepare("delete from game_snap where game_id=?")
 let insert_snap = db.prepare("insert into game_snap(game_id,snap_id,state) values (?,?,?)")
 
-let update_state = db.prepare("update game_state set active=?, state=? where game_id=?")
+let update_state = db.prepare("update game_state set state=? where game_id=?")
+let update_active = db.prepare("update games set active=? where game_id=?")
 
 const CRC32C_TABLE = new Int32Array([
 	0x00000000, 0xf26b8303, 0xe13b70f7, 0x1350f3f4, 0xc79a971f, 0x35f1141c, 0x26a1e7e8, 0xd4ca64eb,
@@ -175,7 +176,8 @@ function patch_game(game_id, {validate_actions=true, save_snaps=true, delete_und
 					insert_snap.run(game_id, ++snap_id, item.state)
 		}
 
-		update_state.run(state.active, JSON.stringify(state), game_id)
+		update_active.run(state.active, game_id)
+		update_state.run(JSON.stringify(state), game_id)
 
 		db.exec("commit")