aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rwxr-xr-xmigrate_1_to_2.sh39
-rw-r--r--schema.sql6
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);