From d4eaac958d3c5c68a6e4bfd47ababb61bd9fd524 Mon Sep 17 00:00:00 2001 From: Tor Andersson Date: Mon, 10 Jul 2023 15:40:45 +0200 Subject: Use sequential per-game chat ids. --- schema.sql | 13 ++++++------- server.js | 4 ++-- 2 files changed, 8 insertions(+), 9 deletions(-) diff --git a/schema.sql b/schema.sql index f959720..144ba7d 100644 --- a/schema.sql +++ b/schema.sql @@ -293,12 +293,13 @@ create table if not exists game_state ( ); create table if not exists game_chat ( - chat_id integer primary key, game_id integer, - time datetime default current_timestamp, + chat_id integer, user_id integer, - message text -); + time datetime default current_timestamp, + message text, + primary key (game_id, chat_id) +) without rowid; create table if not exists unread_chats ( user_id integer, @@ -309,14 +310,12 @@ create table if not exists unread_chats ( drop view if exists game_chat_view; create view game_chat_view as select - chat_id, game_id, time, name, message + game_id, chat_id, time, name, message from game_chat natural join users ; -create index if not exists game_chat_idx on game_chat(game_id); - create table if not exists game_replay ( game_id integer, replay_id integer, diff --git a/server.js b/server.js index 12ec18f..4cc8e05 100644 --- a/server.js +++ b/server.js @@ -1053,7 +1053,7 @@ const SQL_INSERT_UNREAD_CHAT = SQL("insert or ignore into unread_chats (user_id, const SQL_DELETE_UNREAD_CHAT = SQL("delete from unread_chats where user_id = ? and game_id = ?") const SQL_SELECT_GAME_CHAT = SQL("SELECT chat_id,unixepoch(time),name,message FROM game_chat_view WHERE game_id=? AND chat_id>?").raw() -const SQL_INSERT_GAME_CHAT = SQL("INSERT INTO game_chat (game_id,user_id,message) VALUES (?,?,?) RETURNING chat_id,unixepoch(time),NULL,message").raw() +const SQL_INSERT_GAME_CHAT = SQL("INSERT INTO game_chat (game_id,chat_id,user_id,message) VALUES (?, (select coalesce(max(chat_id), 0) + 1 from game_chat where game_id=?), ?,?) RETURNING chat_id,unixepoch(time),NULL,message").raw() const SQL_SELECT_GAME_NOTE = SQL("SELECT note FROM game_notes WHERE game_id=? AND role=?").pluck() const SQL_UPDATE_GAME_NOTE = SQL("INSERT OR REPLACE INTO game_notes (game_id,role,note) VALUES (?,?,?)") @@ -2202,7 +2202,7 @@ function on_getchat(socket, seen) { function on_chat(socket, message) { message = message.substring(0,4000) try { - let chat = SQL_INSERT_GAME_CHAT.get(socket.game_id, socket.user.user_id, message) + let chat = SQL_INSERT_GAME_CHAT.get(socket.game_id, socket.game_id, socket.user.user_id, message) chat[2] = socket.user.name SLOG(socket, "CHAT") for (let other of game_clients[socket.game_id]) -- cgit v1.2.3