digiserver-v2/restore_database.sh

#!/bin/bash
# ============================================================================
# DigiServer - Database Restore
# ----------------------------------------------------------------------------
# Restores a database snapshot created by ./backup_players_playlists.sh.
#
# Usage:
#   ./restore_database.sh                 # restore the latest backup
#   ./restore_database.sh 20260915_143000 # restore a specific backup ID
#   ./restore_database.sh --list          # list available backups
#   ./restore_database.sh <id> --skip-migrations
#
# Typical use: run AFTER upgrading the Docker image / docker host.
# ============================================================================
set -euo pipefail

PROJECT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
COMPOSE="docker compose -f $PROJECT_DIR/docker-compose.yml"
BACKUP_ROOT="$PROJECT_DIR/backups"
DB_HOST="$PROJECT_DIR/data/instance/dashboard.db"

SKIP_MIGRATIONS=0
BACKUP_ID=""
for arg in "$@"; do
  case "$arg" in
    --list)            ls -1 "$BACKUP_ROOT" 2>/dev/null | sort || echo "(no backups found)"; exit 0 ;;
    --skip-migrations) SKIP_MIGRATIONS=1 ;;
    -*)                echo "Unknown option: $arg"; exit 1 ;;
    *)                 BACKUP_ID="$arg" ;;
  esac
done

if [ -z "$BACKUP_ID" ]; then
  BACKUP_ID="$(ls -1 "$BACKUP_ROOT" 2>/dev/null | sort | tail -1 || true)"
fi
if [ -z "$BACKUP_ID" ]; then
  echo "ERROR: no backups found in $BACKUP_ROOT"
  exit 1
fi

DEST="$BACKUP_ROOT/$BACKUP_ID"
if [ ! -d "$DEST" ]; then
  echo "ERROR: backup '$BACKUP_ID' not found in $BACKUP_ROOT"
  echo "Available:"
  ls -1 "$BACKUP_ROOT" 2>/dev/null | sed 's/^/  /'
  exit 1
fi

echo "============================================================"
echo " DigiServer database restore"
echo " Backup: $BACKUP_ID"
echo "============================================================"

# ---------------------------------------------------------------------------
# 1. Verify integrity
# ---------------------------------------------------------------------------
if [ -f "$DEST/SHA256SUMS" ]; then
  echo
  echo "[1/6] Verifying checksums..."
  ( cd "$DEST" && sha256sum -c SHA256SUMS )
else
  echo
  echo "[1/6] No SHA256SUMS found, skipping verification"
fi

# ---------------------------------------------------------------------------
# 2. Stop the app (so nothing writes during restore)
# ---------------------------------------------------------------------------
echo
echo "[2/6] Stopping application container..."
$COMPOSE stop digiserver-app

# ---------------------------------------------------------------------------
# 3. Back up the CURRENT database before overwriting it
# ---------------------------------------------------------------------------
if [ -f "$DB_HOST" ]; then
  SAFETY="$BACKUP_ROOT/pre_restore_$(date +%Y%m%d_%H%M%S)_dashboard.db"
  echo
  echo "[3/6] Saving current DB as a safety copy -> $(basename "$SAFETY")"
  cp "$DB_HOST" "$SAFETY"
else
  echo
  echo "[3/6] No existing DB to preserve"
fi

# ---------------------------------------------------------------------------
# 4. Restore the snapshot
# ---------------------------------------------------------------------------
echo
echo "[4/6] Restoring database snapshot..."
if [ -f "$DEST/dashboard.db.gz" ]; then
  gunzip -c "$DEST/dashboard.db.gz" > "$DB_HOST"
elif [ -f "$DEST/dashboard.db" ]; then
  cp "$DEST/dashboard.db" "$DB_HOST"
else
  echo "ERROR: no dashboard.db(.gz) in backup $BACKUP_ID"
  exit 1
fi

# Remove any stale journal left over from the old DB
rm -f "$DB_HOST-journal" "$DB_HOST-wal" "$DB_HOST-shm"

# Ownership: container runs as uid 1000; host files are pi:pi (1000)
if [ "$(id -u)" -eq 0 ]; then
  chown 1000:1000 "$DB_HOST"
else
  sudo chown 1000:1000 "$DB_HOST"
fi
chmod 644 "$DB_HOST"
echo "  -> restored $(du -h "$DB_HOST" | cut -f1)"

# ---------------------------------------------------------------------------
# 5. Start the (upgraded) app
# ---------------------------------------------------------------------------
echo
echo "[5/6] Starting application..."
$COMPOSE up -d --build
sleep 8

# ---------------------------------------------------------------------------
# 6. Re-run migrations so any new schema is applied to the restored data
# ---------------------------------------------------------------------------
if [ "$SKIP_MIGRATIONS" -eq 0 ]; then
  echo
  echo "[6/6] Re-running migrations..."
  for m in add_https_config_table add_player_user_table add_email_to_https_config migrate_player_user_global; do
    if $COMPOSE exec -T digiserver-app test -f "/app/migrations/$m.py"; then
      echo "  - $m"
      $COMPOSE exec -T digiserver-app python "/app/migrations/$m.py" || \
        echo "    (warning: $m reported an issue - often means it was already applied)"
    fi
  done
else
  echo
  echo "[6/6] Migrations skipped (--skip-migrations)"
fi

# ---------------------------------------------------------------------------
# Verify
# ---------------------------------------------------------------------------
echo
echo "Verification:"
$COMPOSE exec -T digiserver-app python -c "
import sqlite3
c = sqlite3.connect('/app/instance/dashboard.db')
for t in ['player','playlist','playlist_content']:
    try:
        print('  %-18s %d' % (t, c.execute('SELECT COUNT(*) FROM \"%s\"' % t).fetchone()[0]))
    except Exception as e:
        print('  %-18s ERROR %s' % (t, e))
"

echo
echo "============================================================"
echo " Restore complete: $BACKUP_ID"
echo "============================================================"