Move archive export statement into schema view.

4 files changed, 136 insertions, 161 deletions

diff --git a/bin/rtt-archive-backup b/bin/rtt-archive-backup
index f1b63e5..88e19b1 100755
--- a/bin/rtt-archive-backup
+++ b/bin/rtt-archive-backup
@@ -1,4 +1,7 @@
 #!/bin/bash
+
+sqlite3 archive.db < tools/archive-schema.sql
+
 sqlite3 <<EOF
 
 -- Make a copy of finished games in a separate archive database.
@@ -18,62 +21,6 @@ create temporary view candidates as
 		game_id not in (select game_id from archive.games)
 ;
 
-create table if not exists archive.users (
-	user_id integer primary key,
-	name text unique collate nocase
-);
-
-create table if not exists archive.players (
-	game_id integer,
-	role text,
-	user_id integer,
-	primary key (game_id, role)
-) without rowid;
-
-create table if not exists archive.games (
-	game_id integer primary key,
-	title_id text,
-	scenario text,
-	options text,
-	player_count integer,
-	ctime datetime,
-	mtime datetime,
-	moves integer,
-	result text
-);
-
-create table if not exists archive.game_state (
-	game_id integer primary key,
-	state text
-);
-
-create table if not exists archive.game_replay (
-	game_id integer,
-	replay_id integer,
-	role text,
-	action text,
-	arguments json,
-	primary key (game_id, replay_id)
-) without rowid;
-
-create table if not exists archive.game_chat (
-	game_id integer,
-	chat_id integer,
-	user_id integer,
-	time datetime,
-	message text,
-	primary key (game_id, chat_id)
-) without rowid;
-
-drop trigger if exists archive.trigger_delete;
-create trigger archive.trigger_delete after delete on archive.games
-begin
-	delete from players where game_id = old.game_id;
-	delete from game_state where game_id = old.game_id;
-	delete from game_replay where game_id = old.game_id;
-	delete from game_chat where game_id = old.game_id;
-end;
-
 begin immediate;
 
 select 'ARCHIVING ' || count(*) || ' GAMES' from candidates;
diff --git a/schema.sql b/schema.sql
index 8cda3d8..02a4c99 100644
--- a/schema.sql
+++ b/schema.sql
@@ -683,6 +683,39 @@ create view game_export_view as
 	from games as outer
 	;
 
+drop view if exists game_replay_view;
+create view game_replay_view as
+	select
+		game_id,
+		json_object(
+			'players',
+				(select json_group_array(
+						json_object('role', role, 'name', name)
+					)
+					from players
+					left join users using(user_id)
+					where game_id = outer.game_id
+				),
+			'state',
+				(select json(state)
+					from game_state
+					where game_id = outer.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 = outer.game_id
+				)
+		) as export
+	from games as outer
+	;
+
 -- Tournaments --
 
 create table if not exists tm_seeds (
diff --git a/server.js b/server.js
index 36fb03b..b315886 100644
--- a/server.js
+++ b/server.js
@@ -1447,111 +1447,9 @@ const SQL_SELECT_SNAP_COUNT = SQL("select max(snap_id) from game_snap where game
 const SQL_DELETE_GAME_SNAP = SQL("delete from game_snap where game_id=? and snap_id > ?")
 const SQL_DELETE_GAME_REPLAY = SQL("delete from game_replay where game_id=? and replay_id > ?")
 
-const SQL_SELECT_REPLAY = SQL(`
-	select
-		json_object(
-			'players',
-				(select json_group_array(
-						json_object('role', role, 'name', name)
-					)
-					from players
-					left join users using(user_id)
-					where game_id = outer.game_id
-				),
-			'state',
-				(select json(state)
-					from game_state
-					where game_id = outer.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 = outer.game_id
-				)
-		) as export
-	from games as outer
-	where game_id = ?
-`).pluck()
-
-const ARCHIVE_SELECT_REPLAY = ENABLE_ARCHIVE ? SQL(`
-	select
-		json_object(
-			'players',
-				(select json_group_array(
-						json_object('role', role, 'name', name)
-					)
-					from players
-					left join users using(user_id)
-					where game_id = outer.game_id
-				),
-			'state',
-				(select json(state)
-					from archive.game_state
-					where game_id = outer.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 archive.game_replay
-					where game_id = outer.game_id
-				)
-		) as export
-	from games as outer
-	where game_id = ?
-`).pluck() : null
-
-const ARCHIVE_SELECT_EXPORT = ENABLE_ARCHIVE ? SQL(`
-	select
-		game_id,
-		json_object(
-			'setup', json_object(
-					'game_id', game_id,
-					'title_id', title_id,
-					'scenario', scenario,
-					'options', json(options),
-					'player_count', player_count,
-					'notice', notice
-				),
-			'players',
-				(select json_group_array(
-						json_object('role', role, 'name', name)
-					)
-					from players
-					left join users using(user_id)
-					where game_id = outer.game_id
-				),
-			'state',
-				(select json(state)
-					from archive.game_state
-					where game_id = outer.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 archive.game_replay
-					where game_id = outer.game_id
-				)
-		) as export
-	from games as outer
-`).pluck() : null
-
+const ARCHIVE_SELECT_EXPORT = ENABLE_ARCHIVE ? SQL("select export from archive.game_export_view where game_id = ?").pluck() : null
 const SQL_SELECT_EXPORT = SQL("select export from game_export_view where game_id=?").pluck()
+const SQL_SELECT_REPLAY = SQL("select export from game_replay_view where 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=?")
@@ -2378,7 +2276,7 @@ app.get("/api/replay/:game_id", function (req, res) {
 		return res.status(401).send("Not authorized to debug.")
 	if (ENABLE_ARCHIVE) {
 		if (game.status === STATUS_ARCHIVED)
-			return res.type("application/json").send(ARCHIVE_SELECT_REPLAY.get(game_id))
+			return res.type("application/json").send(ARCHIVE_SELECT_EXPORT.get(game_id))
 	}
 	return res.type("application/json").send(SQL_SELECT_REPLAY.get(game_id))
 })
diff --git a/tools/archive-schema.sql b/tools/archive-schema.sql
new file mode 100644
index 0000000..cf63b04
--- /dev/null
+++ b/tools/archive-schema.sql
@@ -0,0 +1,97 @@
+-- schema for archive.db containing limited game and user data
+
+create table if not exists users (
+	user_id integer primary key,
+	name text unique collate nocase
+);
+
+create table if not exists players (
+	game_id integer,
+	role text,
+	user_id integer,
+	primary key (game_id, role)
+) without rowid;
+
+create table if not exists games (
+	game_id integer primary key,
+	title_id text,
+	scenario text,
+	options text,
+	player_count integer,
+	ctime datetime,
+	mtime datetime,
+	moves integer,
+	result text
+);
+
+create table if not exists game_state (
+	game_id integer primary key,
+	state text
+);
+
+create table if not exists game_replay (
+	game_id integer,
+	replay_id integer,
+	role text,
+	action text,
+	arguments json,
+	primary key (game_id, replay_id)
+) without rowid;
+
+create table if not exists game_chat (
+	game_id integer,
+	chat_id integer,
+	user_id integer,
+	time datetime,
+	message text,
+	primary key (game_id, chat_id)
+) without rowid;
+
+drop trigger if exists trigger_delete;
+create trigger trigger_delete after delete on games
+begin
+	delete from players where game_id = old.game_id;
+	delete from game_state where game_id = old.game_id;
+	delete from game_replay where game_id = old.game_id;
+	delete from game_chat where game_id = old.game_id;
+end;
+
+drop view if exists game_export_view;
+create view game_export_view as
+	select
+		game_id,
+		json_object(
+			'setup', json_object(
+					'game_id', game_id,
+					'title_id', title_id,
+					'scenario', scenario,
+					'options', json(options),
+					'player_count', player_count
+				),
+			'players',
+				(select json_group_array(
+						json_object('role', role, 'name', name)
+					)
+					from players
+					left join users using(user_id)
+					where game_id = outer.game_id
+				),
+			'state',
+				(select json(state)
+					from game_state
+					where game_id = outer.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 = outer.game_id
+				)
+		) as export
+	from games as outer
+;