Move mtime and active into games table.

Avoid joining with game_state for all the game list views.

2 files changed, 5 insertions, 3 deletions

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")