mediabox-bootstrap/migration/migrate-db.sh

#!/usr/bin/env bash
# ============================================================================
#  migrate-db.sh — apply the path remap to a COPY of the Plex database and
#                  prove it worked.
#
#  Usage:
#     sudo ./migrate-db.sh /path/to/com.plexapp.plugins.library.db [PLEX_IMAGE]
#
#  This script NEVER touches a live database. It operates on the file you hand
#  it, which must already be a copy taken from a stopped server or one of
#  Plex's own dated backups.
#
#  It uses Plex's own SQLite build, borrowed from the Plex container image, so
#  nothing has to be installed on the host and stock sqlite3 is never involved.
#
#  The final check is the one that actually matters: it takes a random sample
#  of remapped paths and stats them on disk. SQL that runs without error proves
#  nothing; files that open prove everything.
# ============================================================================
set -uo pipefail

DB="${1:?usage: migrate-db.sh <path-to-library.db> [plex-image]}"
IMAGE="${2:-lscr.io/linuxserver/plex:latest}"
HERE="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
SAMPLE=200

[ -f "$DB" ] || { echo "no such database: $DB"; exit 1; }

DBDIR="$(cd "$(dirname "$DB")" && pwd)"
DBNAME="$(basename "$DB")"

PASS=0; FAIL=0
ok(){   printf '  \033[1;32m[pass]\033[0m %s\n' "$*"; PASS=$((PASS+1)); }
bad(){  printf '  \033[1;31m[FAIL]\033[0m %s\n' "$*"; FAIL=$((FAIL+1)); }
note(){ printf '         %s\n' "$*"; }

# Run a query through Plex's SQLite inside a throwaway container.
psql() {
  docker run --rm -i \
    -v "$DBDIR:/db" \
    -v "$HERE:/migration:ro" \
    --entrypoint "/usr/lib/plexmediaserver/Plex SQLite" \
    "$IMAGE" "/db/$DBNAME" "$@" 2>/dev/null
}

echo "=== database: $DB"
echo "=== image:    $IMAGE"
echo

# ---------------------------------------------------------------------------
#  BEFORE — capture the baseline
# ---------------------------------------------------------------------------
echo "--- baseline (Windows paths present) ---"
B_PARTS=$(psql   "SELECT COUNT(*) FROM media_parts        WHERE file      LIKE '\\\\korval\\%';")
B_ROOTS=$(psql   "SELECT COUNT(*) FROM section_locations  WHERE root_path LIKE '\\\\korval\\%';")
B_STREAMS=$(psql "SELECT COUNT(*) FROM media_streams      WHERE url       LIKE 'file://korval/%';")
B_GUIDS=$(psql   "SELECT COUNT(*) FROM metadata_items     WHERE guid      LIKE 'file://korval/%';")
B_TAG=$(psql     "SELECT COUNT(*) FROM taggings           WHERE text      LIKE '%Korval%';")
B_SUM=$(psql     "SELECT COUNT(*) FROM metadata_items     WHERE summary   LIKE '%Korval%';")
note "media_parts.file            $B_PARTS"
note "section_locations.root_path $B_ROOTS"
note "media_streams.url           $B_STREAMS"
note "metadata_items.guid         $B_GUIDS"
note "taggings.text (NOT a path)  $B_TAG   <- must survive untouched"
note "metadata_items.summary      $B_SUM   <- must survive untouched"

if [ "${B_PARTS:-0}" -eq 0 ]; then
  echo
  echo "  Nothing to remap — this database has no Windows paths left."
  echo "  (Already migrated? Wrong file?)"
  exit 1
fi

# ---------------------------------------------------------------------------
#  REMAP
# ---------------------------------------------------------------------------
echo
echo "--- applying remap.sql ---"
cp -a "$DB" "${DB}.pre-remap" && note "rollback copy: ${DB}.pre-remap"
if psql ".read /migration/remap.sql"; then
  note "remap applied"
else
  bad "remap.sql failed — restore from ${DB}.pre-remap"
  exit 1
fi

# ---------------------------------------------------------------------------
#  AFTER — assert
# ---------------------------------------------------------------------------
echo
echo "--- verification ---"

A_WINPARTS=$(psql "SELECT COUNT(*) FROM media_parts       WHERE file      LIKE '\\\\korval\\%';")
A_WINROOTS=$(psql "SELECT COUNT(*) FROM section_locations WHERE root_path LIKE '\\\\korval\\%';")
A_WINSTRM=$(psql  "SELECT COUNT(*) FROM media_streams     WHERE url       LIKE 'file://korval/%';")
A_WINGUID=$(psql  "SELECT COUNT(*) FROM metadata_items    WHERE guid      LIKE 'file://korval/%';")

[ "$A_WINPARTS" = 0 ] && ok "no Windows paths left in media_parts"        || bad "media_parts still has $A_WINPARTS Windows paths"
[ "$A_WINROOTS" = 0 ] && ok "no Windows paths left in section_locations"  || bad "section_locations still has $A_WINROOTS"
[ "$A_WINSTRM"  = 0 ] && ok "no Windows URLs left in media_streams"       || bad "media_streams still has $A_WINSTRM"
[ "$A_WINGUID"  = 0 ] && ok "no Windows URLs left in metadata_items.guid" || bad "metadata_items.guid still has $A_WINGUID"

A_PARTS=$(psql   "SELECT COUNT(*) FROM media_parts       WHERE file LIKE '/mnt/nas/%';")
A_STREAMS=$(psql "SELECT COUNT(*) FROM media_streams     WHERE url  LIKE 'file:///mnt/nas/%';")
A_GUIDS=$(psql   "SELECT COUNT(*) FROM metadata_items    WHERE guid LIKE 'file:///mnt/nas/%';")
A_ROOTS=$(psql   "SELECT COUNT(*) FROM section_locations WHERE root_path LIKE '/mnt/nas/%';")

[ "$A_PARTS"   = "$B_PARTS" ]   && ok "media_parts remapped 1:1 ($A_PARTS)"     || bad "media_parts $B_PARTS -> $A_PARTS"
[ "$A_STREAMS" = "$B_STREAMS" ] && ok "media_streams remapped 1:1 ($A_STREAMS)" || bad "media_streams $B_STREAMS -> $A_STREAMS"
[ "$A_GUIDS"   = "$B_GUIDS" ]   && ok "metadata_items.guid remapped 1:1"        || bad "guid $B_GUIDS -> $A_GUIDS"
# roots: 11 in, 9 out — the two empty legacy roots are deliberately deleted
[ "$A_ROOTS"   = 9 ]            && ok "9 library roots (2 empty legacy roots dropped)" || bad "expected 9 roots, got $A_ROOTS"

# --- the false positives must be untouched --------------------------------
A_TAG=$(psql "SELECT COUNT(*) FROM taggings       WHERE text    LIKE '%Korval%';")
A_SUM=$(psql "SELECT COUNT(*) FROM metadata_items WHERE summary LIKE '%Korval%';")
[ "$A_TAG" = "$B_TAG" ] && ok "taggings.text untouched ($A_TAG row, 'Dr. Korval')"        || bad "taggings.text changed: $B_TAG -> $A_TAG"
[ "$A_SUM" = "$B_SUM" ] && ok "metadata_items.summary untouched ($A_SUM Liaden blurbs)"   || bad "summary changed: $B_SUM -> $A_SUM"

# --- no double-encoding or mangled separators ------------------------------
BAD_SEP=$(psql "SELECT COUNT(*) FROM media_parts WHERE file LIKE '%\\%';")
[ "$BAD_SEP" = 0 ] && ok "no backslashes remain in media_parts.file" || bad "$BAD_SEP rows still contain a backslash"
BAD_PCT=$(psql "SELECT COUNT(*) FROM media_parts WHERE file LIKE '%|%20%';")
[ "${BAD_PCT:-0}" = 0 ] && ok "no %20 leaked into filesystem paths" || bad "$BAD_PCT rows have %20 in a filesystem path"

# ---------------------------------------------------------------------------
#  THE REAL TEST — do the remapped paths open?
# ---------------------------------------------------------------------------
echo
echo "--- resolving $SAMPLE random remapped paths against the filesystem ---"
if ! mountpoint -q /mnt/nas/media 2>/dev/null; then
  note "SKIPPED: /mnt/nas/media is not mounted. Mount the NAS and re-run:"
  note "         $0 $DB"
else
  found=0; missing=0; shown=0
  while IFS= read -r p; do
    [ -z "$p" ] && continue
    if [ -e "$p" ]; then
      found=$((found+1))
    else
      missing=$((missing+1))
      if [ "$shown" -lt 5 ]; then note "MISSING: $p"; shown=$((shown+1)); fi
    fi
  done < <(psql "SELECT file FROM media_parts WHERE file LIKE '/mnt/nas/%' ORDER BY RANDOM() LIMIT $SAMPLE;")

  total=$((found+missing))
  if [ "$total" -gt 0 ] && [ "$missing" -eq 0 ]; then
    ok "$found/$total sampled files resolve on disk"
  else
    bad "$missing/$total sampled files DO NOT resolve — remap or mounts are wrong"
  fi
fi

echo
echo "=== $PASS passed, $FAIL failed ==="
if [ "$FAIL" -eq 0 ]; then
  echo "Database is ready. Rollback copy kept at ${DB}.pre-remap"
  exit 0
else
  echo "DO NOT USE THIS DATABASE. Restore: mv ${DB}.pre-remap $DB"
  exit 1
fi