Use sequential per-game chat ids.

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])