#!/bin/bash

MYSQL="mysql -e"

case "${1:-}" in

  "")
    # default
    $MYSQL "SHOW FULL PROCESSLIST;"
    ;;

  --full|-f)
    $MYSQL "SHOW FULL PROCESSLIST;"
    ;;

  --g)
    $MYSQL "SHOW FULL PROCESSLIST \G;"
    ;;

  --active|-a)
    $MYSQL "
    SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, LEFT(INFO,200) AS query
    FROM information_schema.PROCESSLIST
    WHERE COMMAND != 'Sleep'
    ORDER BY TIME DESC;"
    ;;

  --slow|-s)
    $MYSQL "
    SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, LEFT(INFO,200) AS query
    FROM information_schema.PROCESSLIST
    WHERE TIME > 10
    ORDER BY TIME DESC;"
    ;;

  --top|-t)
    $MYSQL "
    SELECT ID, USER, HOST, DB, COMMAND, TIME, LEFT(INFO,120) AS query
    FROM information_schema.PROCESSLIST
    ORDER BY TIME DESC;"
    ;;

  --state|-st)
    $MYSQL "
    SELECT STATE, COUNT(*) AS count
    FROM information_schema.PROCESSLIST
    GROUP BY STATE
    ORDER BY count DESC;"
    ;;

  --user|-u)
    USERNAME="${2:-}"
    if [[ -z "$USERNAME" ]]; then
      echo "Usage: $0 --user <username>"
      exit 1
    fi

    $MYSQL "
    SELECT *
    FROM information_schema.PROCESSLIST
    WHERE USER='$USERNAME';"
    ;;

  --sleep|-sl)
    $MYSQL "
    SELECT *
    FROM information_schema.PROCESSLIST
    WHERE COMMAND='Sleep'
    ORDER BY TIME DESC;"
    ;;

  *)
    echo "Usage:"
    echo "  $0            -> full processlist"
    echo "  $0 --g        -> vertical format"
    echo "  $0 --full|-f  -> full processlist"
    echo "  $0 --active|-a"
    echo "  $0 --slow|-s"
    echo "  $0 --top|-t"
    echo "  $0 --state|-st"
    echo "  $0 --sleep|-sl"
    echo "  $0 --user <user>"
    ;;
esac