aboutsummaryrefslogtreecommitdiff
path: root/migrate_1_to_2.sh
diff options
context:
space:
mode:
Diffstat (limited to 'migrate_1_to_2.sh')
-rwxr-xr-xmigrate_1_to_2.sh39
1 files changed, 39 insertions, 0 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'."