diff options
| -rw-r--r-- | schema.sql | 64 | ||||
| -rw-r--r-- | server.js | 66 | ||||
| -rw-r--r-- | tools/elo.js | 2 | ||||
| -rwxr-xr-x | tools/patchgame.js | 6 |
4 files changed, 92 insertions, 46 deletions
@@ -111,7 +111,6 @@ create view user_dynamic_view as from players join games using(game_id) - join game_state using(game_id) where status = 1 and user_count = player_count @@ -149,6 +148,21 @@ create view user_dynamic_view as -- Elo ratings & match making -- +drop view if exists rated_games_view; +create view rated_games_view as + select + game_id, title_id, player_count, scenario, result, mtime + from + games + where + status > 1 + and user_count = player_count + and player_count > 1 + and not exists ( + select 1 from players where players.game_id = games.game_id and user_id = 0 + ) +; + create table if not exists ratings ( title_id integer, user_id integer, @@ -325,20 +339,27 @@ create virtual table if not exists forum_search using fts5(thread_id, post_id, t create table if not exists games ( game_id integer primary key, + status integer default 0, + title_id text, scenario text, options text, - player_count integer, + + player_count integer default 2, join_count integer default 0, + invite_count integer default 0, user_count integer default 0, + owner_id integer default 0, - ctime datetime default current_timestamp, + notice text, is_private boolean default 0, is_random boolean default 0, - notice text, - status integer default 0, - result text, - xtime datetime + + ctime datetime default current_timestamp, + mtime datetime default current_timestamp, + moves integer default 0, + active text, + result text ); create index if not exists games_title_idx on games(title_id); @@ -346,8 +367,6 @@ create index if not exists games_status_idx on games(status); create table if not exists game_state ( game_id integer primary key, - mtime datetime, - active text, state text ); @@ -415,23 +434,38 @@ create view game_view as games.*, titles.title_name, owner.name as owner_name, - coalesce(game_state.mtime, xtime) as mtime, - user_count = join_count and join_count > 1 as is_opposed, - game_state.active + user_count = join_count and join_count > 1 as is_opposed from games - natural left join game_state natural join titles join users as owner on owner.user_id = games.owner_id ; +drop view if exists ready_to_start_reminder; +create view ready_to_start_reminder as + select + game_id, owner_id as user_id, name, mail, notify + from + games + join users on user_id = owner_id + where + status = 0 + and join_count = player_count + and not exists ( + select 1 from players + where + players.game_id = players.game_id + and is_invite + ) + ; + drop view if exists your_turn_reminder; create view your_turn_reminder as select game_id, role, user_id, name, mail, notify from - game_view + games join players using(game_id) join users using(user_id) where @@ -522,9 +556,9 @@ begin delete from posts where author_id = old.user_id; delete from threads where author_id = old.user_id; delete from game_chat where user_id = old.user_id; - delete from players where user_id = old.user_id; delete from ratings where user_id = old.user_id; update games set owner_id = 0 where owner_id = old.user_id; + update players set user_id = 0 where user_id = old.user_id; end; drop trigger if exists trigger_delete_on_threads; @@ -1129,6 +1129,31 @@ load_rules() const SQL_INSERT_GAME = SQL("INSERT INTO games (owner_id,title_id,scenario,options,player_count,is_private,is_random,notice) VALUES (?,?,?,?,?,?,?,?)") const SQL_DELETE_GAME = SQL("DELETE FROM games WHERE game_id=? AND owner_id=?") +const SQL_START_GAME = SQL(` + update games set + status = 1, + is_private = (is_private or user_count < player_count), + mtime = datetime(), + active = ? + where + game_id = ? +`) + +const SQL_FINISH_GAME = SQL(` + update games set + status = 2, + mtime = datetime(), + active = null, + result = ? + where + game_id = ? +`) + +const SQL_UPDATE_GAME_ACTIVE = SQL("update games set active=?,mtime=datetime(),moves=moves+1 where game_id=?") + +const SQL_SELECT_GAME_STATE = SQL("select state from game_state where game_id=?").pluck() +const SQL_INSERT_GAME_STATE = SQL("insert or replace into game_state (game_id,state) values (?,?)") + const SQL_SELECT_UNREAD_CHAT_GAMES = SQL("select game_id from unread_chats where user_id = ?").pluck() const SQL_INSERT_UNREAD_CHAT = SQL("insert or ignore into unread_chats (user_id,game_id) values (?,?)") const SQL_DELETE_UNREAD_CHAT = SQL("delete from unread_chats where user_id = ? and game_id = ?") @@ -1140,13 +1165,6 @@ const SQL_SELECT_GAME_NOTE = SQL("SELECT note FROM game_notes WHERE game_id=? AN const SQL_UPDATE_GAME_NOTE = SQL("INSERT OR REPLACE INTO game_notes (game_id,role,note) VALUES (?,?,?)") const SQL_DELETE_GAME_NOTE = SQL("DELETE FROM game_notes WHERE game_id=? AND role=?") -const SQL_SELECT_GAME_STATE = SQL("SELECT state FROM game_state WHERE game_id=?").pluck() -const SQL_INSERT_GAME_STATE = SQL("insert into game_state (game_id,state,active,mtime) values (?,?,?,datetime())") -const SQL_UPDATE_GAME_STATE = SQL("update game_state set state=?, active=? where game_id=?") -const SQL_UPDATE_GAME_MTIME = SQL("update game_state set mtime=datetime() 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=?) ,?,?,?)") const SQL_INSERT_SNAP = SQL("insert into game_snap (game_id,snap_id,state) values (?, (select coalesce(max(snap_id), 0) + 1 from game_snap where game_id=?), ?) returning snap_id").pluck() @@ -1200,7 +1218,6 @@ const SQL_SELECT_PLAYER_ROLE = SQL("SELECT role FROM players WHERE game_id=? AND const SQL_INSERT_PLAYER_ROLE = SQL("INSERT OR IGNORE INTO players (game_id,role,user_id,is_invite) VALUES (?,?,?,?)") const SQL_DELETE_PLAYER_ROLE = SQL("DELETE FROM players WHERE game_id=? AND role=?") -const SQL_SELECT_OPEN_GAMES = SQL(`SELECT * FROM games WHERE status=${STATUS_OPEN}`) const SQL_COUNT_OPEN_GAMES = SQL(`SELECT COUNT(*) FROM games WHERE owner_id=? AND status=${STATUS_OPEN}`).pluck() const SQL_COUNT_ACTIVE_GAMES = SQL(` select count(*) from games @@ -1275,7 +1292,6 @@ const QUERY_LIST_GAMES_OF_TITLE_FINISHED = SQL(` const QUERY_NEXT_GAME_OF_USER = SQL(` select title_id, game_id, role from games - join game_state using(game_id) join players using(game_id) where status = ${STATUS_ACTIVE} @@ -1812,13 +1828,10 @@ function start_game(game) { if (game.is_random) assign_random_roles(game, options, SQL_SELECT_PLAYERS.all(game.game_id)) - SQL_UPDATE_GAME_RESULT.run(1, null, game.game_id) - if (game.user_count !== game.player_count) - SQL_UPDATE_GAME_PRIVATE.run(game.game_id) - + SQL_START_GAME.run(state.active, game.game_id) put_replay(game.game_id, null, ".setup", [seed, game.scenario, options]) put_snap(game.game_id, state) - SQL_INSERT_GAME_STATE.run(game.game_id, JSON.stringify(state), state.active) + SQL_INSERT_GAME_STATE.run(game.game_id, JSON.stringify(state)) SQL_COMMIT.run() } finally { @@ -1873,7 +1886,7 @@ app.get('/api/replay/:game_id', function (req, res) { * add role_rating to Ev and update role_rating with low K-value */ -const SQL_SELECT_RATING_GAME = SQL("select title_id, player_count, scenario, result, xtime from games where game_id=? and status>1 and player_count=user_count and player_count>1") +const SQL_SELECT_RATING_GAME = SQL("select * from rated_games_view where game_id=?") const SQL_SELECT_RATING_PLAYERS = SQL("select * from player_rating_view where game_id=?") const SQL_INSERT_RATING = SQL("insert or replace into ratings (title_id,user_id,rating,count,last) values (?,?,?,?,?)") @@ -1915,7 +1928,7 @@ function update_elo_ratings(game_id) { p.change = elo_change(p, players, 1 / players.length) for (let p of players) - SQL_INSERT_RATING.run(game.title_id, p.user_id, p.rating + p.change, p.count + 1, game.xtime) + SQL_INSERT_RATING.run(game.title_id, p.user_id, p.rating + p.change, p.count + 1, game.mtime) } /* @@ -2098,6 +2111,7 @@ function send_play_notification(user, game_id, message) { send_notification(user, game_play_link(game_id, title_id, user), `${title_name} #${game_id} (${user.role}) - ${message}`) } +const QUERY_LIST_READY_TO_START = SQL("select * from ready_to_start_reminder") const QUERY_LIST_YOUR_TURN = SQL("SELECT * FROM your_turn_reminder") const QUERY_LIST_INVITES = SQL("SELECT * FROM invite_reminder") @@ -2160,15 +2174,11 @@ function notify_invited_reminder() { } function notify_ready_to_start_reminder() { - for (let game of SQL_SELECT_OPEN_GAMES.all()) { - let players = SQL_SELECT_PLAYERS.all(game.game_id) - if (is_game_ready(game.player_count, players)) { - if (!is_player_online(game.game_id, game.owner_id)) { - let owner = SQL_SELECT_USER_VIEW.get(game.owner_id) - if (should_send_reminder(owner, game.game_id)) { - insert_last_notified(owner, game.game_id) - send_join_notification(owner, game.game_id, "Ready to start") - } + for (let item of QUERY_LIST_READY_TO_START.all()) { + if (!is_player_online(item.game_id, item.user_id)) { + if (should_send_reminder(item, item.game_id)) { + insert_last_notified(item, item.game_id) + send_join_notification(item, item.game_id, "Ready to start") } } } @@ -2260,16 +2270,16 @@ function put_snap(game_id, state) { function put_game_state(game_id, state, old_active, current_role) { // TODO: separate state, undo, and log entries (and reuse "snap" json stringifaction?) - SQL_UPDATE_GAME_STATE.run(JSON.stringify(state), state.active, game_id) + SQL_INSERT_GAME_STATE.run(game_id, JSON.stringify(state)) if (state.active !== old_active) { // TODO: add time spent for old_active players // TODO: add time available for new_active players - SQL_UPDATE_GAME_MTIME.run(game_id) + SQL_UPDATE_GAME_ACTIVE.run(state.active, game_id) } if (state.state === "game_over") { - SQL_UPDATE_GAME_RESULT.run(2, state.result, game_id) + SQL_FINISH_GAME.run(state.result, game_id) if (state.result && state.result !== "None") update_elo_ratings(game_id) } diff --git a/tools/elo.js b/tools/elo.js index 95ca58b..a6f9c9d 100644 --- a/tools/elo.js +++ b/tools/elo.js @@ -4,7 +4,7 @@ const sqlite3 = require("better-sqlite3") const db = new sqlite3("db") -const SQL_SELECT_GAMES = db.prepare("select * from games where status>1 and user_count=player_count and player_count>1 order by xtime") +const SQL_SELECT_GAMES = db.prepare("select * from rated_games_view order by mtime") const SQL_SELECT_RATING = db.prepare("select * from player_rating_view where game_id=?") const SQL_INSERT_RATING = db.prepare("insert or replace into ratings (title_id,user_id,rating,count,last) values (?,?,?,?,?)") diff --git a/tools/patchgame.js b/tools/patchgame.js index 6808704..afed8a3 100755 --- a/tools/patchgame.js +++ b/tools/patchgame.js @@ -13,7 +13,8 @@ let insert_replay = db.prepare("insert into game_replay (game_id,replay_id,role, let delete_snap = db.prepare("delete from game_snap where game_id=?") let insert_snap = db.prepare("insert into game_snap(game_id,snap_id,state) values (?,?,?)") -let update_state = db.prepare("update game_state set active=?, state=? where game_id=?") +let update_state = db.prepare("update game_state set state=? where game_id=?") +let update_active = db.prepare("update games set active=? where game_id=?") const CRC32C_TABLE = new Int32Array([ 0x00000000, 0xf26b8303, 0xe13b70f7, 0x1350f3f4, 0xc79a971f, 0x35f1141c, 0x26a1e7e8, 0xd4ca64eb, @@ -175,7 +176,8 @@ function patch_game(game_id, {validate_actions=true, save_snaps=true, delete_und insert_snap.run(game_id, ++snap_id, item.state) } - update_state.run(state.active, JSON.stringify(state), game_id) + update_active.run(state.active, game_id) + update_state.run(JSON.stringify(state), game_id) db.exec("commit") |