From 909ada35ccb617344d244d4e76c9ce85fd2b922b Mon Sep 17 00:00:00 2001
From: Tom Smeding <tom.smeding@gmail.com>
Date: Mon, 27 Jul 2020 20:24:44 +0200
Subject: server: Migrate database to version 2

---
 migrate_1_to_2.sh | 39 +++++++++++++++++++++++++++++++++++++++
 1 file changed, 39 insertions(+)
 create mode 100755 migrate_1_to_2.sh

(limited to 'migrate_1_to_2.sh')

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'."
-- 
cgit v1.2.3-70-g09d2