From 3ec391a75f6edad855fc218c8369b0ce52158bb0 Mon Sep 17 00:00:00 2001 From: Tor Andersson Date: Fri, 25 Apr 2025 20:00:09 +0200 Subject: Move archive export statement into schema view. --- bin/rtt-archive-backup | 59 ++------------------------ schema.sql | 33 +++++++++++++++ server.js | 108 ++--------------------------------------------- tools/archive-schema.sql | 97 ++++++++++++++++++++++++++++++++++++++++++ 4 files changed, 136 insertions(+), 161 deletions(-) create mode 100644 tools/archive-schema.sql 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 < ?") 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 +; -- cgit v1.2.3