Generate JSON for replay view in SQL.

Also optimize the format by not JSON encoding some data twice.

2 files changed, 38 insertions, 9 deletions

diff --git a/public/common/play.js b/public/common/play.js
index dce1573..bb3c5ae 100644
--- a/public/common/play.js
+++ b/public/common/play.js
@@ -825,8 +825,6 @@ async function init_replay() {
 
 	let ss
 	for (p = 0; p < replay.length; ++p) {
-		replay[p][2] = JSON.parse(replay[p][2])
-
 		if (rules.is_checkpoint) {
 			replay[p].is_checkpoint = p > 1 && rules.is_checkpoint(ss, s)
 			ss = object_copy(s)
@@ -1009,7 +1007,7 @@ async function init_replay() {
 		window.addEventListener("hashchange", on_hash_change)
 	} else {
 		console.log("REPLAY NOT AVAILABLE")
-		s = JSON.parse(body.state)
+		s = body.state
 		update_replay_view()
 	}
 }
diff --git a/server.js b/server.js
index d3dfe80..48fa9ce 100644
--- a/server.js
+++ b/server.js
@@ -1117,8 +1117,42 @@ const SQL_SELECT_GAME_STATE = SQL("SELECT state FROM game_state WHERE game_id=?"
 const SQL_UPDATE_GAME_STATE = SQL("INSERT OR REPLACE INTO game_state (game_id,state,active,mtime) VALUES (?,?,?,julianday())")
 const SQL_UPDATE_GAME_RESULT = SQL("UPDATE games SET status=?, result=? 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,role,action,arguments) VALUES (?,?,?,?)")
-const SQL_SELECT_REPLAY = SQL("SELECT role,action,arguments FROM game_replay WHERE game_id=?").raw()
+
+const SQL_SELECT_REPLAY = SQL(`
+	select json_object(
+			'title', title_id,
+			'scenario', scenario,
+			'options', json(options),
+			'players',
+				(select json_group_array(
+						json_object('role', role, 'name', name)
+					)
+					from players
+					natural join users
+					where game_id = :game_id
+				),
+			'state',
+				(select json(state)
+					from game_state
+					where game_id = :game_id
+				),
+			'replay',
+				(select json_group_array(
+						case when arguments is null then
+							json_array(role, action)
+						else
+							json_array(role, action, json(arguments))
+						end
+					)
+					from game_replay
+					where game_id = :game_id
+				)
+	)
+	from games
+	where game_id = :game_id
+`).pluck()
 
 const SQL_SELECT_GAME = SQL("SELECT * FROM games WHERE game_id=?")
 const SQL_SELECT_GAME_VIEW = SQL("SELECT * FROM game_view WHERE game_id=?")
@@ -1713,16 +1747,13 @@ app.get('/:title_id/replay\::game_id', function (req, res) {
 })
 
 app.get('/api/replay/:game_id', function (req, res) {
-	let game_id = req.params.game_id
+	let game_id = req.params.game_id | 0
 	let game = SQL_SELECT_GAME.get(game_id)
 	if (!game)
 		return res.status(404).send("Invalid game ID.")
 	if (game.status < 2 && (!req.user || req.user.user_id !== 1))
 		return res.status(401).send("Not authorized to debug.")
-	let players = SQL_SELECT_PLAYERS_JOIN.all(game_id)
-	let state = SQL_SELECT_GAME_STATE.get(game_id)
-	let replay = SQL_SELECT_REPLAY.all(game_id)
-	return res.json({players, state, replay})
+	return res.send(SQL_SELECT_REPLAY.get({game_id}))
 })
 
 /*