migrate-sqlite.sh

#!/bin/sh
set -eu

usage() {
    cat >&2 <<'EOF'
usage:
  scripts/migrate-sqlite.sh [apply] <db_path> <migrations_dir>
  scripts/migrate-sqlite.sh status <db_path> <migrations_dir>
  scripts/migrate-sqlite.sh dry-run <db_path> <migrations_dir>
  scripts/migrate-sqlite.sh verify <db_path> <migrations_dir>

env:
  SQLITE_MIGRATIONS_TABLE=nginx_sqlite_migrations
  SQLITE_MIGRATIONS_BACKUP_ENABLED=false
  SQLITE_MIGRATIONS_BACKUP_DIR=<db directory>
  SQLITE_MIGRATIONS_LOCK_TIMEOUT=3000
  SQLITE_MIGRATIONS_REQUIRE_CURRENT=false
EOF
    exit 2
}

fail() {
    echo "migrate-sqlite: $*" >&2
    exit 1
}

checksum_file() {
    file="$1"
    # Prefer ubiquitous checksum tools while keeping macOS and minimal images usable
    if command -v sha256sum >/dev/null 2>&1; then
        sha256sum "$file" | awk '{print $1}'
    elif command -v shasum >/dev/null 2>&1; then
        shasum -a 256 "$file" | awk '{print $1}'
    elif command -v openssl >/dev/null 2>&1; then
        openssl dgst -sha256 -r "$file" | awk '{print $1}'
    else
        fail "sha256sum, shasum, or openssl is required to checksum migrations"
    fi
}

sql_quote() {
    # Values are injected into sqlite3 CLI snippets, so single quotes are doubled
    printf "%s" "$1" | sed "s/'/''/g"
}

is_true() {
    case "$(printf '%s' "$1" | tr '[:upper:]' '[:lower:]')" in
    1 | true | yes | on) return 0 ;;
    *) return 1 ;;
    esac
}

validate_table_name() {
    # The table name is used as an identifier, not a SQL string literal
    lower_name="$(printf "%s" "$1" | tr '[:upper:]' '[:lower:]')"
    case "$1" in
    '' | *[!ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_]*)
        fail "SQLITE_MIGRATIONS_TABLE must contain only letters, numbers, and underscores"
        ;;
    esac
    case "$lower_name" in
    sqlite_*)
        fail "SQLITE_MIGRATIONS_TABLE must not start with reserved SQLite prefix sqlite_"
        ;;
    esac
}

table_exists() {
    [ -f "$DB_PATH" ] || return 1
    exists="$(sqlite3 -batch -cmd ".timeout $MIGRATIONS_LOCK_TIMEOUT" \
        "$DB_PATH" "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = '$(sql_quote "$MIGRATIONS_TABLE")';")"
    [ "$exists" = "1" ]
}

applied_checksum_for() {
    version="$1"
    if [ "$TRACKING_TABLE_EXISTS" = "true" ]; then
        sqlite3 -batch -cmd ".timeout $MIGRATIONS_LOCK_TIMEOUT" \
            "$DB_PATH" "SELECT checksum FROM \"$MIGRATIONS_TABLE\" WHERE version = '$(sql_quote "$version")';"
    fi
}

ensure_tracking_table() {
    DB_DIR="$(dirname "$DB_PATH")"
    mkdir -p "$DB_DIR"

    # Use WAL and a busy timeout so startup migrations cooperate with a live demo
    # DB instead of failing on short writer locks
    sqlite3 -batch "$DB_PATH" >/dev/null <<SQL
.bail on
.timeout $MIGRATIONS_LOCK_TIMEOUT
PRAGMA journal_mode=WAL;
PRAGMA busy_timeout=$MIGRATIONS_LOCK_TIMEOUT;
CREATE TABLE IF NOT EXISTS "$MIGRATIONS_TABLE" (
  version TEXT PRIMARY KEY,
  checksum TEXT NOT NULL,
  applied_at INTEGER NOT NULL
) WITHOUT ROWID;
SQL
    TRACKING_TABLE_EXISTS=true
}

maybe_backup_db() {
    [ -f "$DB_PATH" ] || return 0
    is_true "${SQLITE_MIGRATIONS_BACKUP_ENABLED:-false}" || return 0

    # Backups are opt-in because container examples can regenerate their DB
    backup_dir="${SQLITE_MIGRATIONS_BACKUP_DIR:-$(dirname "$DB_PATH")}"
    mkdir -p "$backup_dir"
    backup_file="${backup_dir}/$(basename "$DB_PATH").$(date +%Y%m%d%H%M%S).$$.bak"
    backup_sql_path="$(printf '%s' "$backup_file" | sed "s/'/''/g")"
    sqlite3 -batch "$DB_PATH" \
        ".timeout $MIGRATIONS_LOCK_TIMEOUT" \
        ".backup '$backup_sql_path'"
    echo "Created migration backup $backup_file"
}

for_each_migration() {
    found=0
    # Shell glob order is lexicographic, matching the numeric filename convention
    for migration in "$MIGRATIONS_DIR"/*.sql; do
        [ -e "$migration" ] || continue
        file_name="$(basename "$migration")"
        case "$file_name" in
        [0-9][0-9][0-9]_*.sql) ;;
        *) fail "migration filename must match NNN_name.sql: $file_name" ;;
        esac
        found=1
        "$@" "$migration"
    done
    if [ "$found" -eq 0 ]; then
        echo "No migrations found in $MIGRATIONS_DIR"
    fi
}

report_migration() {
    migration="$1"
    file_name="$(basename "$migration")"
    version="${file_name%.sql}"
    checksum="$(checksum_file "$migration")"
    applied_checksum="$(applied_checksum_for "$version")"

    if [ -z "$applied_checksum" ]; then
        echo "pending $version $checksum"
    elif [ "$applied_checksum" = "$checksum" ]; then
        echo "applied $version $checksum"
    else
        echo "changed $version applied=$applied_checksum file=$checksum"
    fi
}

apply_migration() {
    migration="$1"
    file_name="$(basename "$migration")"
    version="${file_name%.sql}"
    checksum="$(checksum_file "$migration")"
    version_sql="$(sql_quote "$version")"
    checksum_sql="$(sql_quote "$checksum")"
    applied_checksum="$(applied_checksum_for "$version")"

    if [ -n "$applied_checksum" ]; then
        # Applied migrations are immutable; changing one should be a new migration
        if [ "$applied_checksum" = "$checksum" ]; then
            echo "Skipping migration $version"
            return 0
        fi
        fail "migration $version was already applied with checksum $applied_checksum, but file checksum is $checksum"
    fi

    tmp_sql="$(mktemp)"
    {
        # Record the migration in the same transaction as its SQL so partial applies
        # are not marked successful
        printf '.bail on\n'
        printf '.timeout %s\n' "$MIGRATIONS_LOCK_TIMEOUT"
        printf 'BEGIN IMMEDIATE;\n'
        # Claim the immutable version before running its SQL. A concurrent
        # runner then fails on the unique key before applying statements.
        printf 'INSERT INTO "%s" (version, checksum, applied_at) VALUES (' "$MIGRATIONS_TABLE"
        printf "'%s', '%s', strftime('%%s','now'));\n" "$version_sql" "$checksum_sql"
        cat "$migration"
        printf 'COMMIT;\n'
    } >"$tmp_sql"

    echo "Applying migration $version"
    if ! sqlite3 -batch "$DB_PATH" <"$tmp_sql"; then
        rm -f "$tmp_sql"
        concurrent_checksum="$(applied_checksum_for "$version")"
        if [ "$concurrent_checksum" = "$checksum" ]; then
            echo "Skipping migration $version (applied concurrently)"
            return 0
        fi
        fail "migration failed: $version"
    fi
    rm -f "$tmp_sql"
}

MODE="apply"
case "${1:-}" in
apply | status | dry-run | verify)
    MODE="$1"
    shift
    ;;
esac

[ "$#" -eq 2 ] || usage

DB_PATH="$1"
MIGRATIONS_DIR="$2"
MIGRATIONS_TABLE="${SQLITE_MIGRATIONS_TABLE:-nginx_sqlite_migrations}"
MIGRATIONS_LOCK_TIMEOUT="${SQLITE_MIGRATIONS_LOCK_TIMEOUT:-3000}"

case "$MIGRATIONS_LOCK_TIMEOUT" in
'' | *[!0-9]*) fail "SQLITE_MIGRATIONS_LOCK_TIMEOUT must be milliseconds" ;;
esac

validate_table_name "$MIGRATIONS_TABLE"

command -v sqlite3 >/dev/null 2>&1 || fail "sqlite3 CLI is required"
[ -d "$MIGRATIONS_DIR" ] || fail "migrations directory does not exist: $MIGRATIONS_DIR"

# Status/verify reuse one schema lookup for the entire migration loop instead
# of starting an extra sqlite3 process for every file.
TRACKING_TABLE_EXISTS=false
if [ "$MODE" != "apply" ] && table_exists; then
    TRACKING_TABLE_EXISTS=true
fi

case "$MODE" in
apply)
    maybe_backup_db
    ensure_tracking_table
    for_each_migration apply_migration
    ;;
status | dry-run)
    for_each_migration report_migration
    ;;
verify)
    verify_out="$(mktemp)"
    for_each_migration report_migration >"$verify_out"
    cat "$verify_out"

    # verify is intended for CI: pending migrations are informational, changed
    # migrations fail because checksums no longer match history
    if grep '^changed ' "$verify_out" >/dev/null; then
        rm -f "$verify_out"
        fail "one or more applied migrations have changed"
    fi
    if is_true "${SQLITE_MIGRATIONS_REQUIRE_CURRENT:-false}" &&
        grep '^pending ' "$verify_out" >/dev/null; then
        rm -f "$verify_out"
        fail "one or more migrations are pending"
    fi
    rm -f "$verify_out"
    ;;
*)
    usage
    ;;
esac