From bd6644214c757a08c1c533048352a902fdefe4ae Mon Sep 17 00:00:00 2001
From: Tor Andersson <tor@ccxvii.net>
Date: Sun, 21 May 2023 11:53:02 +0200
Subject: Improve replay id handling.

Use a without rowid table and create the replay_id dynamically.
This saves a lot of database space and performance as it removes
the need for a separate index.
---
 schema.sql | 9 ++++-----
 1 file changed, 4 insertions(+), 5 deletions(-)

(limited to 'schema.sql')

diff --git a/schema.sql b/schema.sql
index e685e3b..95e2690 100644
--- a/schema.sql
+++ b/schema.sql
@@ -318,14 +318,13 @@ create view game_chat_view as
 create index if not exists game_chat_idx on game_chat(game_id);
 
 create table if not exists game_replay (
-	replay_id integer primary key,
 	game_id integer,
+	replay_id integer,
 	role text,
 	action text,
-	arguments json -- numeric affinity is more compact for numbers
-);
-
-create index if not exists game_replay_idx on game_replay(game_id);
+	arguments json, -- numeric affinity is more compact for numbers
+	primary key (game_id, replay_id)
+) without rowid;
 
 create table if not exists game_notes (
 	game_id integer,
-- 
cgit v1.2.3

