#!/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'."