server: Migrate database to version 2

2 files changed, 44 insertions, 1 deletions

diff --git a/migrate_1_to_2.sh b/migrate_1_to_2.sh
new file mode 100755
index 0000000..02269d1
--- /dev/null
+++ b/migrate_1_to_2.sh
@@ -0,0 +1,39 @@
+#!/usr/bin/env bash
+set -euo pipefail
+INDB=db.db
+OUTDB=db_migrated.db
+
+# This script does not use ALTER TABLE ADD COLUMN because I'm scared by the
+# note in the sqlite documentation stating:
+#     The ALTER TABLE command works by modifying the SQL text of the schema
+#     stored in the sqlite_master table. No changes are made to table content.
+#     Because of this, the execution time of the ALTER TABLE command is
+#     independent of the amount of data in the table. The ALTER TABLE command
+#     runs as quickly on a table with 10 million rows as it does on a table
+#     with 1 row.
+# I might be mistaken, but that sounds like a performance issue in later usage.
+# Therefore, to be sure, we just copy the entire database here. We don't just
+# copy and rename the table inside one database to not unnecessarily double the
+# disk footprint of the database.
+
+inversion="$(sqlite3 "$INDB" 'select version from Meta')"
+
+if [[ $inversion != "1" ]]; then
+	echo >&2 "$0 migrates from version 1 to version 2, but the database is at version $inversion."
+	exit 1
+fi
+
+if [[ -f "$OUTDB" ]]; then
+	echo >&2 "Output database $OUTDB already exists; remove before running this script"
+	exit 1
+fi
+
+sqlite3 "$OUTDB" <schema.sql
+
+sqlite3 "$INDB" '.dump' |
+	sed '/^INSERT INTO/!d; s/^INSERT INTO [Mm]essages/&(id,room,user,time,message)/' |
+	sqlite3 "$OUTDB"
+
+sqlite3 "$OUTDB" 'update Meta set version = 2'
+
+echo "Migrated '$INDB' to '$OUTDB'."
diff --git a/schema.sql b/schema.sql
index 15d274c..5a1938c 100644
--- a/schema.sql
+++ b/schema.sql
@@ -1,3 +1,5 @@
+-- Database schema version 2
+
 pragma foreign_keys = on;
 
 create table Meta (
@@ -31,9 +33,11 @@ create table Messages (
 	room integer not null,
 	user integer null,
 	time integer not null,
+	reply integer null,
 	message blob,
 	foreign key(room) references Rooms(id) on delete cascade,
-	foreign key(user) references Users(id) on delete set null
+	foreign key(user) references Users(id) on delete set null,
+	foreign key(reply) references Messages(id) on delete set null
 );
 create index messages_time_index on Messages(room, time desc);