Darkstar db migration script

migrate_db.bash

#!/bin/bash
# Migrate database automatically.
#
# Automatically migrates between schema changes.
# Where fields were either added or removed.
#
# However does not understand, if field names were renamed.
# You stil have to manually migrate those (eg. linkshellid1 -> linkshellid)
# These fields should show up as "old fields" and "new fields" in stdout.
#
# Make sure you backup your db before running.
# Though the script will try to restore on failure.
#
# Usage: ./migrate_db.bash <server> <user> <db> [password]

shopt -s extglob

server="$1"
user="$2"
db="$3"
password="$4"

# Drop database
drop() {
   mysqladmin $opts drop -f "$@" > /dev/null 2>&1
}

# Recreate database (All data is lost)
recreate() {
   drop "$@"
   mysqladmin $opts create "$@" > /dev/null 2>&1
}

# Copy database (This will overwrite dest)
# $1 == src
# $2 == dest
copy() {
   recreate "$2"
   mysqldump $opts "$1" | mysql $opts "$2"
}

# Does database exist?
exists() {
   mysqlshow $opts "$@" > /dev/null 2>&1
}

# Does table exist?
# $1 = db
# $2 = table
table_exists() {
   mysql $opts -e "desc $1.$2" > /dev/null 2>&1
}

# Print usage and exit with code 1
usage() {
   cat <<< 'Usage: ./migrate_db.bash <server> <user> <db> [password]'
   exit 1
}

die() {
   echo "-!- FATAL: $@" 1>&2;
   if exists "$db" && exists "${db}_old"; then
      echo "-!- Restoring database" 1>&2;
      copy "${db}_old" "$db"
      drop "${db}_old"
   fi
   exit 1
}

sigint() {
   die "SIGINT"
}

trap sigint INT

if [[ ! "$server" ]] || [[ ! "$user" ]] || [[ ! "$db" ]]; then
   usage
fi

[[ -d sql ]] || die "Could not find sql files. Run this from darkstar source directory."

if [[ "$password" ]]; then
   opts="-h $server -u $user -p$password"
else
   opts="-h $server -u $user"
fi

exists "$db" || die "No such database: $db"

echo "=> Copying database"
copy "$db" "${db}_old" || die "Failed to copy db"

echo "=> Creating new database"
recreate "$db"
for f in sql/!(*triggers).sql; do
   echo "=> Importing $f"
   mysql $opts "$db" < "$f" || die "Failed to import: $f"
done

echo "=> Migrating data"
mysql $opts --batch --skip-column-names -e "select TABLE_NAME from information_schema.tables where table_schema='${db}_old';" | \
   while read -r l; do
      table_exists "$db" "$l" || continue
      mysql $opts --batch --skip-column-names -e "select column_name from information_schema.columns where table_name = '$l' and table_schema='${db}_old';" > /tmp/db-migrate-columns1
      mysql $opts --batch --skip-column-names -e "select column_name from information_schema.columns where table_name = '$l' and table_schema='${db}';" > /tmp/db-migrate-columns2
      c="\`$(grep -F -f /tmp/db-migrate-columns2 /tmp/db-migrate-columns1 | sed ':a;N;$!ba;s/\n/`,`/g')\`"
      o="$(grep -Fv -f /tmp/db-migrate-columns2 /tmp/db-migrate-columns1)"
      n="$(grep -Fv -f /tmp/db-migrate-columns1 /tmp/db-migrate-columns2)"
      rm -f /tmp/db-migrate-columns{1,2}
      echo "=> Migrating $l"
      mysql $opts -e "insert ignore $db.$l ($c) select $c from ${db}_old.$l;" || die "Failed to migrate table: $l"
      [[ "$o" ]] && echo -e "-!- Removed fields:\n$o"
      [[ "$n" ]] && echo -e "-!- New fields:\n$n"
   done

[[ $? -eq 0 ]] || exit 1

mysql $opts --batch --skip-column-names -e "select TABLE_NAME from information_schema.tables where table_schema='${db}_old';" > /tmp/db-old-tables
mysql $opts --batch --skip-column-names -e "select TABLE_NAME from information_schema.tables where table_schema='${db}';" > /tmp/db-new-tables
o="$(grep -Fv -f /tmp/db-new-tables /tmp/db-old-tables)"
n="$(grep -Fv -f /tmp/db-old-tables /tmp/db-new-tables)"
[[ "$o" ]] && echo -e "-!- Removed tables:\n$o"
[[ "$n" ]] && echo -e "-!- New tables:\n$n"
rm -f /tmp/db-{new,old}-tables

echo "=> Migrating triggers"
mysql $opts "$db" < "sql/triggers.sql" || die "Failed to import: sql/triggers.sql"

echo "=> Deleting temporary database"
drop "${db}_old"
echo "=> Done"