Shell Scripting — Database Centric: Complete Database Operations Toolkit

#!/usr/bin/env bash
# db_ops.sh — Complete database operations library
# Source this file: source /opt/myapp/lib/db_ops.sh

# ── Auto-detect environment and load config ────────────────
_db_init() {
  local env
  env=$(echo "${APP_ENV:-$(hostname)}" | grep -oE "(prod|staging|dev)[a-z]*" | head -1 || echo dev)
  DB_CONF="${DB_CONF:-/etc/myapp/${env}-mysql.conf}"
  DB_NAME="${DB_NAME:-myapp}"
  export _DB_INITIALISED=1
}

# ── Core query functions ───────────────────────────────────
db() {
  [[ "${_DB_INITIALISED:-}" != "1" ]] && _db_init
  mysql --defaults-file="${DB_CONF}" -BNs -D "${DB_NAME}" -e "$1"
}

db_file() {
  [[ "${_DB_INITIALISED:-}" != "1" ]] && _db_init
  mysql --defaults-file="${DB_CONF}" -D "${DB_NAME}" < "$1"
}

db_csv() {
  [[ "${_DB_INITIALISED:-}" != "1" ]] && _db_init
  mysql --defaults-file="${DB_CONF}" --csv -D "${DB_NAME}" -e "$1"
}

# ── Connection health ──────────────────────────────────────
db_ping() {
  db "SELECT 1" &>/dev/null && echo "OK" || echo "FAIL"
}

db_wait() {
  local timeout="${1:-60}" elapsed=0
  echo -n "Waiting for DB"
  while ! db "SELECT 1" &>/dev/null; do
    (( elapsed >= timeout )) && { echo " TIMEOUT"; return 1; }
    echo -n "."; sleep 2; (( elapsed += 2 ))
  done
  echo " READY"
}

# ── Backup helpers ────────────────────────────────────────
db_backup() {
  local dest="${1:-/tmp/db_backup_$(date +%Y%m%d_%H%M%S).sql.gz}"
  mysqldump --defaults-file="${DB_CONF}" --single-transaction     --routines --triggers "${DB_NAME}" | gzip > "${dest}"
  gzip -t "${dest}" && echo "${dest}" || { rm -f "${dest}"; return 1; }
}

# ── Locking ───────────────────────────────────────────────
db_lock() {
  local name="${1}" ttl="${2:-300}"
  local lock_file="/var/lock/dbops-${name}.lock"
  exec 9>"${lock_file}"
  flock -w "${ttl}" 9 || { echo "Cannot acquire lock: ${name}"; return 1; }
  echo "${name}"
}

# ── Row helpers ───────────────────────────────────────────
db_count() { db "SELECT COUNT(*) FROM $1 ${2:-}"; }
db_exists() { [[ $(db "SELECT COUNT(*) FROM $1 WHERE $2") -gt 0 ]]; }
db_value() { db "SELECT $1 FROM $2 WHERE $3 LIMIT 1"; }

# ── Alert integration ─────────────────────────────────────
db_alert() {
  local level="$1" msg="$2"
  echo "[$(date --iso-8601=seconds)] [${level}] ${msg}" >> "${DB_ALERT_LOG:-/var/log/myapp/db-ops.log}"
  [[ -n "${SLACK_WEBHOOK_URL:-}" ]] && curl -s -X POST "${SLACK_WEBHOOK_URL}"     -d "{"text":"[${level}] ${msg}"}" &>/dev/null
}

#!/usr/bin/env bash
# deploy_gate.sh — Complete pre-deployment database gate using db_ops.sh

source /opt/myapp/lib/db_ops.sh

PASS=0; FAIL=0
check() {
  printf "  %-35s" "$1:"
  if shift && "$@" &>/dev/null 2>&1; then
    echo "OK"; (( PASS++ ))
  else
    echo "FAIL"; (( FAIL++ ))
  fi
}

echo "=== Deployment Gate ==="

# Connectivity
check "DB reachable"          bash -c '[[ "$(db_ping)" == "OK" ]]'

# Schema version
CURRENT=$(db "SELECT COALESCE(MAX(version),'000') FROM schema_migrations" 2>/dev/null || echo 0)
check "Schema >= ${REQUIRED_VERSION:-001}"   bash -c "[[ '${CURRENT}' >= '${REQUIRED_VERSION:-001}' ]]"

# Data integrity spot checks
check "Users table populated"    db_exists "users" "active=1"
check "No orphaned orders"       bash -c '[[ $(db "SELECT COUNT(*) FROM orders o LEFT JOIN users u ON u.id=o.user_id WHERE u.id IS NULL") -eq 0 ]]'
check "Backup exists (24h)"      bash -c 'find /backups/mysql -name "*.sql.gz" -mtime -1 | grep -q .'
check "Disk space ok (<80%)"     bash -c '[[ $(df /var/lib/mysql | awk "NR==2{gsub(/%/,"",",$5);print $5}") -lt 80 ]]'
check "Connections ok (<80%)"    bash -c '(( $(db "SELECT COUNT(*) FROM information_schema.PROCESSLIST") * 100 / $(db "SELECT @@max_connections") < 80 ))'

echo ""
echo "  Results: ${PASS} passed, ${FAIL} failed"
(( FAIL > 0 )) && { db_alert "CRITICAL" "Deployment gate FAILED: ${FAIL} checks"; exit 1; }
db_alert "INFO" "Deployment gate passed: proceeding"
echo "  Gate: PASSED"