From 3ec391a75f6edad855fc218c8369b0ce52158bb0 Mon Sep 17 00:00:00 2001
From: Tor Andersson <tor@ccxvii.net>
Date: Fri, 25 Apr 2025 20:00:09 +0200
Subject: Move archive export statement into schema view.

---
 bin/rtt-archive-backup | 59 +++-----------------------------------------------
 1 file changed, 3 insertions(+), 56 deletions(-)

(limited to 'bin')

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;
-- 
cgit v1.2.3