From fc3501382c2aa3ef5b692f4f55c2616f9cade3f5 Mon Sep 17 00:00:00 2001
From: Tor Andersson <tor@ccxvii.net>
Date: Fri, 25 Apr 2025 12:18:00 +0200
Subject: Move toolbox scripts to a "bin" directory.

Add a super "rtt" command to run the scripts.
---
 bin/rtt-archive-backup | 106 +++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 106 insertions(+)
 create mode 100755 bin/rtt-archive-backup

(limited to 'bin/rtt-archive-backup')

diff --git a/bin/rtt-archive-backup b/bin/rtt-archive-backup
new file mode 100755
index 0000000..f1b63e5
--- /dev/null
+++ b/bin/rtt-archive-backup
@@ -0,0 +1,106 @@
+#!/bin/bash
+sqlite3 <<EOF
+
+-- Make a copy of finished games in a separate archive database.
+
+attach database 'db' as live;
+attach database 'archive.db' as archive;
+
+pragma live.busy_timeout=10000;
+
+-- List finished (and rated) games in live that are not in archive.
+create temporary view candidates as
+	select
+		game_id
+	from
+		live.rated_games_view
+	where
+		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;
+
+insert or ignore into archive.users (user_id, name) select user_id, name
+	from live.users;
+
+insert into archive.players (game_id, role, user_id)
+	select game_id, role, user_id
+	from candidates join live.players using(game_id);
+
+insert into archive.game_state (game_id, state)
+	select game_id, state
+	from candidates join live.game_state using(game_id);
+
+insert into archive.game_replay (game_id, replay_id, role, action, arguments)
+	select game_id, replay_id, role, action, arguments
+	from candidates join live.game_replay using(game_id);
+
+insert into archive.game_chat (game_id, chat_id, user_id, time, message)
+	select game_id, chat_id, user_id, time, message
+	from candidates join live.game_chat using(game_id);
+
+insert into archive.games (game_id, title_id, scenario, options, player_count, ctime, mtime, moves, result)
+	select game_id, title_id, scenario, options, player_count, ctime, mtime, moves, result
+	from candidates join live.games using(game_id);
+
+commit;
+
+EOF
-- 
cgit v1.2.3