diff options
| -rwxr-xr-x | migrate_1_to_2.sh | 39 | ||||
| -rw-r--r-- | schema.sql | 6 |
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'." @@ -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); |