From 9a455686c7e71e71fa84514760e9fb3202da1ca7 Mon Sep 17 00:00:00 2001 From: Tor Andersson Date: Wed, 5 Jul 2023 15:15:21 +0200 Subject: Add "xtime" column for games to record finish date when state is missing. --- schema.sql | 5 +++-- server.js | 2 +- 2 files changed, 4 insertions(+), 3 deletions(-) diff --git a/schema.sql b/schema.sql index 144ba7d..8e5ff4d 100644 --- a/schema.sql +++ b/schema.sql @@ -279,7 +279,8 @@ create table if not exists games ( is_random boolean default 0, notice text, status integer default 0, - result text + result text, + xtime datetime ); create index if not exists games_title_idx on games(title_id); @@ -356,7 +357,7 @@ create view game_view as games.*, titles.title_name, owner.name as owner_name, - game_state.mtime, + coalesce(game_state.mtime, xtime) as mtime, game_state.active from games diff --git a/server.js b/server.js index 4cc8e05..65f5a7a 100644 --- a/server.js +++ b/server.js @@ -1061,7 +1061,7 @@ const SQL_DELETE_GAME_NOTE = SQL("DELETE FROM game_notes WHERE game_id=? AND rol const SQL_SELECT_GAME_STATE = SQL("SELECT state FROM game_state WHERE game_id=?").pluck() const SQL_UPDATE_GAME_STATE = SQL("INSERT OR REPLACE INTO game_state (game_id,state,active,mtime) VALUES (?,?,?,datetime())") -const SQL_UPDATE_GAME_RESULT = SQL("UPDATE games SET status=?, result=? WHERE game_id=?") +const SQL_UPDATE_GAME_RESULT = SQL("UPDATE games SET status=?, result=?, xtime=datetime() WHERE game_id=?") const SQL_UPDATE_GAME_PRIVATE = SQL("UPDATE games SET is_private=1 WHERE game_id=?") const SQL_INSERT_REPLAY = SQL("insert into game_replay (game_id,replay_id,role,action,arguments) values (?, (select coalesce(max(replay_id), 0) + 1 from game_replay where game_id=?) ,?,?,?) returning replay_id").pluck() -- cgit v1.2.3