blob: 02269d1b9aea107a7462aa69529290e5140ca2a1 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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'."
|
