From c235a8239177d38117f3313d17b904b66a806378 Mon Sep 17 00:00:00 2001 From: Tor Andersson Date: Wed, 14 Feb 2024 23:20:44 +0100 Subject: Add import-game and export-game scripts. To bulk transfer all game state, replay, and snapshots into a new game. --- schema.sql | 50 ++++++++++++++++++++++++++++++++++++++++++++++++++ server.js | 9 ++++----- tools/export-game.sh | 8 ++++++++ tools/import-game.js | 47 +++++++++++++++++++++++++++++++++++++++++++++++ tools/patchgame.js | 4 ++-- 5 files changed, 111 insertions(+), 7 deletions(-) create mode 100644 tools/export-game.sh create mode 100755 tools/import-game.js diff --git a/schema.sql b/schema.sql index 36c2efc..a0a3b02 100644 --- a/schema.sql +++ b/schema.sql @@ -556,6 +556,56 @@ create view invite_reminder as is_invite = 1 ; +-- Export game state as JSON + +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, + 'notice', notice + ), + 'players', + (select json_group_array( + json_object('role', role, 'name', name) + ) + from players + 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 + ), + 'snaps', + (select json_group_array( + json_array(replay_id, json(state)) + ) + from game_snap + where game_id = outer.game_id + ) + ) as export + from games as outer + ; + -- Trigger to update player counts when players join and part games drop trigger if exists trigger_join_game; diff --git a/server.js b/server.js index 93a1971..5572868 100644 --- a/server.js +++ b/server.js @@ -1206,8 +1206,10 @@ const SQL_FINISH_GAME = SQL(` game_id = ? `) -const SQL_REOPEN_GAME = SQL("update games set status=1,active=? where game_id=?") +const SQL_REWIND_GAME = SQL("update games set status=1,moves=?,active=?,mtime=datetime() where game_id=?") + const SQL_UPDATE_GAME_ACTIVE = SQL("update games set active=?,mtime=datetime(),moves=moves+1 where game_id=?") +const SQL_UPDATE_GAME_MOVES = SQL("update games set moves=? where game_id=?") const SQL_UPDATE_GAME_SCENARIO = SQL("update games set scenario=? where game_id=?") const SQL_SELECT_GAME_STATE = SQL("select state from game_state where game_id=?").pluck() @@ -2094,10 +2096,7 @@ app.get("/admin/rewind/:game_id/:snap_id", must_be_administrator, function (req, SQL_DELETE_GAME_REPLAY.run(game_id, snap.replay_id) SQL_INSERT_GAME_STATE.run(game_id, JSON.stringify(snap_state)) - SQL_REOPEN_GAME.run(snap_state.active, game_id) - - if (snap_state.active !== game_state.active) - SQL_UPDATE_GAME_ACTIVE.run(snap_state.active, game_id) + SQL_REWIND_GAME.run(snap_id - 1, snap_state.active, game_id) update_join_clients_game(game_id) if (game_clients[game_id]) diff --git a/tools/export-game.sh b/tools/export-game.sh new file mode 100644 index 0000000..19cd4e7 --- /dev/null +++ b/tools/export-game.sh @@ -0,0 +1,8 @@ +#!/bin/bash +if [ -n "$1" ] +then + sqlite3 db "select export from game_export_view where game_id=$1" +else + echo "usage: bash tools/export-game.sh GAME > game.json" +fi + diff --git a/tools/import-game.js b/tools/import-game.js new file mode 100755 index 0000000..e8553df --- /dev/null +++ b/tools/import-game.js @@ -0,0 +1,47 @@ +#!/usr/bin/env -S node + +const fs = require("fs") +const sqlite3 = require("better-sqlite3") + +if (process.argv.length !== 3) { + console.error("usage: node tools/import-game.js game.json") + process.exit(1) +} + +var game = JSON.parse(fs.readFileSync(process.argv[2], "utf8")) + +game.setup.active = game.state.active +game.setup.moves = game.snaps && game.snaps.length > 0 ? game.snaps.length - 1 : 0 + +let db = new sqlite3("db") + +let insert_game = db.prepare("insert into games(status,title_id,scenario,options,player_count,active,moves,notice) values (1,:title_id,:scenario,:options,:player_count,:active,:moves,:notice) returning game_id").pluck() +let insert_player = db.prepare("insert into players(game_id,role,user_id) values (?,?,1)") +let insert_state = db.prepare("insert into game_state(game_id,state) values (?,?)") + +db.exec("begin") + +game.setup.options = JSON.stringify(game.setup.options) + +let game_id = insert_game.get(game.setup) +for (let p of game.players) + insert_player.run(game_id, p.role) +insert_state.run(game_id, JSON.stringify(game.state)) + +if (game.replay) { + let insert_replay = db.prepare("insert into game_replay(game_id,replay_id,role,action,arguments) values (?,?,?,?,?)") + game.replay.forEach(([role, action, args], i) => { + insert_replay.run(game_id, i+1, role, action, JSON.stringify(args)) + }) +} + +if (game.snaps) { + let insert_snap = db.prepare("insert into game_snap(game_id,snap_id,replay_id,state) values (?,?,?,?)") + game.snaps.forEach(([replay_id, state], i) => { + insert_snap.run(game_id, i+1, replay_id, JSON.stringify(state)) + }) +} + +console.log(game_id) + +db.exec("commit") diff --git a/tools/patchgame.js b/tools/patchgame.js index 3a21f67..e191df8 100755 --- a/tools/patchgame.js +++ b/tools/patchgame.js @@ -79,10 +79,10 @@ function is_valid_action(rules, state, role, action, arg) { if (va) { if (Array.isArray(arg)) arg = arg[0] - if (arg === undefined || arg === null) - return (va === 1 || va === true || typeof va === "string") if (Array.isArray(va) && va.includes(arg)) return true + if (arg === undefined || arg === null || arg === 1) + return (va === 1 || va === true || typeof va === "string") } return false } -- cgit v1.2.3