Move archive export statement into schema view.

1 files changed, 97 insertions, 0 deletions

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
+;