myctl

#!/bin/bash -f
#
# Copyright (C) 2014 Miroslav Vadkerti
# This program is free software: you can redistribute it and/or modify it under
# the terms of the GNU General Public License as published by the Free Software
# Foundation, either version 3 of the License, or (at your option) any later
# version.
#
# This program is distributed in the hope that it will be useful, but WITHOUT
# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
# FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License along
# with this program. If not, see http://www.gnu.org/licenses/. 
#
# Author: Miroslav Vadkerti <miro.vadkerti at mogdesign.eu>
#

# globals
VERSION="1.05"
TOOLNAME=$(basename $0)
SYSTEM_CONFIG="/etc/$TOOLNAME.conf"
USER_CONFIG="$HOME/.$TOOLNAME.conf"

# defaults
MYSQL_HOST="localhost"
MYSQL_PORT="3306"
DEF_HOST="localhost"
MYSQL_OPTS="--skip-column-names" # by default do not show column names
[ -e "$SYSTEM_CONFIG" ] && MYCTL_GROUP="$(stat -c '%G' $SYSTEM_CONFIG)" || \
  MYCTL_GROUP="myctl"
DB_IGNORE_LIST="^(mysql|performance_schema|information_schema)$" # ignore these dbs in listing

#
# helpers
#
print_info() {
  echo ":: $1"
}

print_error() {
  echo "Error: $1"
}

exit_error() {
  print_error "$1"
  [ -n "$2" ] && { echo; $2; }
  exit 1
}

exit_info() {
  print_info "$1"
  exit 0
}

mysql_exec() {
  if [ -z "$1" ]; then
    mysql -u$MYSQL_USER -p$MYSQL_PASSWD -h$MYSQL_HOST \
      -P$MYSQL_PORT $MYSQL_OPTS
  else
    echo "$@" | mysql -u$MYSQL_USER -p$MYSQL_PASSWD -h$MYSQL_HOST \
      -P$MYSQL_PORT $MYSQL_OPTS
  fi
}

mysql_exec_db() {
  [ -z "$1" ] && exit_error "No database for $FUNCNAME"

  if [ -z "$2" ]; then
    mysql -u$MYSQL_USER -p$MYSQL_PASSWD -h$MYSQL_HOST \
      -P$MYSQL_PORT -D$1 $MYSQL_OPTS
  else
    echo "${@:2}" | mysql -u$MYSQL_USER -p$MYSQL_PASSWD -h$MYSQL_HOST \
      -P$MYSQL_PORT -D$1 $MYSQL_OPTS
  fi
}

ask_user() {
    local ANS= DEFAULT="$2"

    # make sure we get a value when no default
    while [ -z "$ANS" ]; do
        [ -z "$DEFAULT" ] && echo -n "$1: " >/dev/tty || \
            echo -n "$1 [$DEFAULT]: " >/dev/tty
        read ANS
        [ -z "$ANS" ] && ANS="$DEFAULT"
    done

    echo "$ANS"
}

confirm() {
  local INP=

  [ -n "$CONFIRM" ] && return

  echo -n "$1 (y/n)? "

  read INP

  case $INP in
    y|Y)
      return
      ;;
    *)
      echo "Cancelled!"
      exit 1
  esac
}

# check if mytl inst
is_myctl_installed() {
  # binary reachable
  which myctl &>/dev/null || return 1
  # configuration exists
  [ -e "$SYSTEM_CONFIG" -o -e "$USER_CONFIG" ] || return 1
  # myctl works
  mysql_exec "exit" &>/dev/null || return 1

  return 0
}

# check if database exists
check_db() {
  myctlsubcmd_db_list | egrep -q -- "^$1 "
  return $?
}

# check if user exits
check_user() {
   myctlsubcmd_user_list | grep -q -- "^$1 "
   return $?
}

# check if user has privileges to given database
check_user_db() {
  myctlsubcmd_user_list | egrep -- "\b$1\b.*\b$2\b" &>/dev/null
  return $?
}

check_user_db_none() {
  myctlsubcmd_user_list "\b$1\b" | grep -q -- "-- none --"
  return $?
}

# dispatch command - call specific subcommand for given command or show help
dispatch_command() {
  local CMD=$1
  local SCMD=$2
  local GEN= TMP=

  # run subcommand if passed
  if [ -n "$SCMD" ]; then
    # execute given subcommand if found
    GEN=$(compgen -A function myctlsubcmd_${CMD}_${SCMD})
    if [ -n "$GEN" ]; then
      if [ $(wc -l <<< "$GEN") -gt 1 ]; then
        TMP=$(for TMP in $GEN; do echo -n "${TMP#myctlsubcmd_${CMD}_} "; done)
        exit_error "More subcommands match \"$SCMD\": $TMP" myctlcmd_${CMD}
      fi

      # shift away subcommand
      shift 2

      # run subcommand
      $GEN $@
      return $?
    fi

    # if we got here there is no subcommand to run
    exit_error "Unknown subcommand $SCMD"
  else
    # no command, display help
    myctlcmd_${CMD}
  fi
}

#
# general functions
#
sanity_checks() {
  # pop out if no configuration
  if [ ! -e "$USER_CONFIG" -a ! -e "$SYSTEM_CONFIG" ]; then
    echo "No configuration file found. Please run 'install' command or create"
    echo "$USER_CONFIG or $SYSTEM_CONFIG file with at least these variables:"
    echo
    echo "# mysql administrator username and password"
    echo "MYSQL_USR=username"
    echo "MYSQL_PASSWD=password"
    echo
    exit 0
  fi

  # check mandatory config
  [ -z "$MYSQL_USER" ] && exit_error "No MySQL admin user in configuration"
  [ -z "$MYSQL_PASSWD" ] && exit_error "No MySQL admin password in configuration"

  # check mysql connection
  is_myctl_installed || exit_error "Admin user credentials are wrong. Fix your configuration or run 'install' again."

  # check admin account requirements
}

load_config() {
  local RET=0

  # load system-wide config
  if [ -e "$SYSTEM_CONFIG" ]; then
    source $SYSTEM_CONFIG 2>/dev/null; RET=$?
  fi

  # load config from home directory
  if [ -e "$USER_CONFIG" ]; then
    source $USER_CONFIG 2>/dev/null
  else
    [ $RET -ne 0 ] && exit_error "Permission denied for $TOOLNAME system \
configuration file. Are you in $MYCTL_GROUP group?"
  fi
}


show_help() {
  echo "Usage: $TOOLNAME [options] command [sub-command]"
  echo
  echo "Command line tool for managing users, databases and privileges"
  echo "in one MySQL instance."
  echo
  echo "Run specific command with -h parameter to get help. Command abbreviations"
  echo "accepted."
  echo
  echo "Available commands:"
  echo " db          Manage and list databases"
  echo " privilege   Manage user privileges to database"
  echo " shell       Get MySQL shell or execute SQL statement"
  echo " user        Manage and list users"
  echo
  echo "Available installation commands"
  echo " install     Install $TOOLNAME tool for current user or system-wide"
  echo " uninstall   Uninstall $TOOLNAME tool"
  echo
  echo "Configuration file:"
  echo " The configuration file provides basic configuration for $TOOLNAME tool."
  echo " Global configuration file is located at \`$SYSTEM_CONFIG'. This can be"
  echo " overriden by local configuration file \`$USER_CONFIG'. Configuration"
  echo " file contains bash varibles sourced by the tool. The configuration file"
  echo " can be created manually or running the 'install' command."
  echo
  echo " Mandatory variables:"
  echo "  MYSQL_USER      - MySQL administrator username" 
  echo "  MYSQL_PASSWD    - MySQL administrator password" 
  echo
  echo " Optional variables:"
  echo "  MYSQL_HOST      - MySQL hostname (default: localhost)" 
  echo "  MYSQL_PORT      - MySQL port (default: 3309)" 
  echo "  DEF_HOST        - Default host for adding user (default: localhost)"
  echo "  DEF_PASSWD      - Default password for added user (ask user if not present)"
  echo
  exit 0
}

#
# user management
#

myctlcmd_user() {
  # run help
  echo "Usage: $TOOLNAME user [sub-command] [options]"
  echo
  echo "Available sub-commands:"
  echo "  add      Add new user"
  echo "  del      Delete user"
  echo "  list     List users with available databases"
  echo
  echo "Run sub-command with -h parameter to get detailed help."
  echo

  exit 0
}

## add user
myctlsubcmd_user_add() {
  local OPTIND= DB= DUMP= PASSWD= UNAME= DBS=
  local PASSWD=$DEF_PASSWD
  local OPTS="hd:f:H:p:"

  while getopts $OPTS arg; do
    case $arg in
      h)
        user_add_help
        ;;
      d)
        DBS=$OPTARG
        ;;
      f)
        DUMP=$OPTARG
        ;;
      p)
        PASSWD=$OPTARG
        ;;
      *)
        exit 1
        ;;
    esac
  done

  shift $((OPTIND-1))

  [ -z "$1" ] && exit_error "No username given" user_add_help
  [ $# -ne 1 ] && exit_error "Too many parameters" user_add_help
  [ -n "$DUMP" -a -z "$DBS" ] && exit_error "No database given. Option -f requires -d" user_add_help
  [ -n "$DUMP" -a ! -e "$DUMP" ] && exit_error "Dump file '$DUMP' not found"

  IFS=@ read UNAME HOST <<< "$1"
  [ -z "$HOST" ] && HOST=$DEF_HOST

  # check if user already exists
  check_user $UNAME@$HOST && exit_error "User $UNAME@$HOST already exists"

  # check if database already exists
  if [ -n "$DBS" ]; then
    for DB in $(sed 's/,/ /g' <<< "$DBS"); do
      check_db $DB && exit_error "Database $DB already exists, cannot create"
    done
  fi

  # ask for password if needed
  while [ -z "$PASSWD" ]; do
    PASSWD=$(ask_user "Enter '$UNAME@$HOST' password")
  done

  # add user
  SQL="CREATE USER '$UNAME'@'$HOST' IDENTIFIED BY '$PASSWD';"
  mysql_exec "$SQL" || exit_error "Addin user failed"
  print_info "Created user '$UNAME@$HOST' with password '$PASSWD'"

  # add database
  if [ -n "$DBS" ]; then
    for DB in $(sed 's/,/ /g' <<< "$DBS"); do
      myctlsubcmd_db_add -u "$UNAME@$HOST" "$DB"
      # restore dump from given file
      [ -n "$DUMP" ] && myctlsubcmd_db_restore $DB $DUMP
    done
  fi
}

user_add_help() {
  echo "Usage: $TOOLNAME user add [-d db,..] [-f dump] [-H host] [-p password] user[@host]"
  echo
  echo "Add new MySQL user who can connect from $DEF_HOST. Optionally specify other host, "
  echo "add also a new database and restore a MySQL dump from file."
  echo
  echo "Options:"
  echo " -d db,..          Create one or more user database separated with commas"
  echo " -f dump           Restore given dump to the database, requires -d"
  echo " -H host           Host user can authenticate from (default: $DEF_HOST)"
  echo " -p password       Use given password for user \
(default: ${DEF_PASSWD:-none, ask for password})"
  echo

  exit 0
}

## delete user
myctlsubcmd_user_del() {
  local OPTIND= DBS= DALL= UNAME= HOST= DB=
  local OPTS="hDd:"

  while getopts $OPTS arg; do
    case $arg in
      h)
        user_del_help
        ;;
      d)
        DBS=$OPTARG
        ;;
      D)
        DALL=yes
        ;;
      *)
        exit 1
        ;;
    esac
  done

  shift $((OPTIND-1))

  [ -z "$1" ] && exit_error "No username given" user_del_help
  [ -n "$DBS" -a -n "$DALL" ] && exit_error \
    "Only one option for removing databases can be specified" user_del_help

  IFS=@ read UNAME HOST <<< "$1"
  [ -z "$HOST" ] && HOST=$DEF_HOST

  # check if user already exists
  check_user "$UNAME@$HOST" || exit_error "User $UNAME@$HOST does not exist"

  # check if any databases exists
  [ -n "$DALL" ] && check_user_db_none && exit_error "No database exists for $UNAME@$HOST"

  # check if given databases exist for user
  if [ -n "$DBS" ]; then
    for DB in $(sed 's/,/ /g' <<< "$DBS"); do
      check_user_db "$UNAME@$HOST" "$DB" || \
        exit_error "Database '$DB' not found for '$UNAME@$HOST'"
    done
  fi

  # ask for confirmation
  if [ -n "$DBS" ]; then
    confirm "Really remove MySQL user '$UNAME@$HOST' and databases '$DBS'"
  elif [ -n "$DALL" ]; then
    confirm "Really remove MySQL user '$UNAME@$HOST' and all databases he has access to"
  else
    confirm "Really remove MySQL user '$UNAME@$HOST'"
  fi

  # remove given databases
  if [ -n "$DBS" ]; then
    for DB in $(sed 's/,/ /g' <<< "$DBS"); do
      mysql_exec "DROP DATABASE $DB" || print_error "Could not remove database $DB"
      print_info "Removed database '$DB'"
    done
  fi

  # remove all databases
  if [ -n "$DALL" ]; then
    for DB in $(myctlsubcmd_user_list | grep "$UNAME@$HOST" | cut -d\  -f 2-); do
      mysql_exec "DROP DATABASE $DB" || print_error "Could not remove database $DB"
      print_info "Removed database '$DB'"
    done
  fi

  # delete user
  SQL="DROP USER '$UNAME'@'$HOST';"
  mysql_exec "$SQL" || exit_error "Removing user failed"
  print_info "User '$UNAME@$HOST' removed"

  exit 0
}

user_del_help() {
  echo "Usage: $TOOLNAME user del [-D] [-d db,..] user[@host]"
  echo
  echo "Remove given MySQL user connecting from $DEF_HOST. Optionally specify other host. "
  echo "Optionally remove all or given associated databases."
  echo
  echo "Options:"
  echo " -D                Remove all databases user has access to"
  echo " -d db,..          Remove one or more databases separated with a comma"
  echo

  exit 0
}

## list users
myctlsubcmd_user_list() {
  local SQL= DATA= USER_HOST_DB= USR= HOST= DB= USRPREV= HOSTPREV= LIST= SPRIV=
  local FIRST= OPTIND=
  local OPTS="h"

  while getopts $OPTS arg; do
    case $arg in
      h)
        user_list_help
        ;;
      *)
        exit 1
        ;;
    esac
  done

  LIST=$(
    # get all users
    SQL="use mysql; SELECT DISTINCT user.user, user.host, db.db, user.Select_priv FROM user \
      left JOIN db ON user.user=db.user AND user.host=db.host ORDER BY user, host, db"
    DATA=$(mysql_exec "$SQL")

    # for all users, hosts print their databases
    while read USER_HOST_DB; do
      read USR HOST DB SPRIV <<< "$USER_HOST_DB"
      if [ "$DB" = "NULL" ]; then
        # Detect all privileges only by select privilege
        [ "$SPRIV" = "Y" ] && DB="-- all --" || DB="-- none --"
      fi
      if [ "$USR" = "$USRPREV" -a "$HOST" = "$HOSTPREV" ]; then
        echo -n "$DB "
        continue
      fi

      if  [ -z "$FIRST" ]; then
        printf "%-40s%s" "$USR@$HOST" "$DB"
      else
        printf "\n%-40s%s" "$USR@$HOST" "$DB "
      fi

      USRPREV=$USR
      HOSTPREV=$HOST
      FIRST="no"
    done <<< "$DATA" | sort | egrep -- "$1"
  )

  if [ -z "$LIST" ]; then
    echo "No data found"
    return 1
  fi

  {
    printf "%-40s%s\n" "User@Server" "Privileges to database(s)"
    printf "%80s\n" | tr ' ' -
    echo "$LIST"
  } | less -FX
}

user_list_help() {
  echo "Usage: $TOOLNAME user list [REGEXP]"
  echo
  echo "List available mysql users with privilages to databases."
  echo "Currently the privileges are not distinguished and if user"
  echo "has at least one permission it is shown."
  echo
  echo "Optionally a regular expression can be passwd to filter out results."
  echo "This REGEXP is passed to egrep directly."
  echo
  echo "Listed results are paged via less."

  exit 0
}


#
# database management
#

## db command help
myctlcmd_db() {
  echo "Usage: $TOOLNAME db [sub-command] [options]"
  echo
  echo "MySQL database management."
  echo
  echo "Available sub-commands:"
  echo "  add      Add new database"
  echo "  del      Delete database"
  echo "  dump     Dump database contents to a file"
  echo "  list     List databases"
  echo "  restore  Restore database from dump"
  echo
  echo "Run sub-command with -h parameter to get detailed help."
  echo

  exit 0
}

## list all databases
myctlsubcmd_db_list() {
  local DB= PRIV= OPTIND=
  local COUNT=0
  local OPTS="h"

  while getopts $OPTS arg; do
    case $arg in
      h)
        db_list_help
        ;;
      *)
        exit 1
        ;;
    esac
  done

  shift $((OPTIND-1))

  local DBS=$(mysql_exec "show databases" | egrep -v "$DB_IGNORE_LIST" | sort | egrep -- "$1")
  local PRIVS=$(mysql_exec "SELECT db,user,host FROM mysql.db" | awk '{print $1,$2"@"$3}') 

  if [ -z "$DBS" ]; then
    echo "No database found"
    return 1
  fi

  {
    printf "%-40s%s\n" "Database" "Users with privileges"
    printf "%80s\n" | tr ' ' -
    for DB in $DBS; do
      PRIV=$(echo "$PRIVS" | egrep -- "^$DB " | awk '{print $2}')
      [ -z "$PRIV" ] && PRIV="-- none --"
      printf "%-40s" "$DB"
      COUNT=0
      for OPRIV in $PRIV; do
        if [ $COUNT -eq 3 ]; then
          COUNT=0
          printf "\n%-40s" "$DB"
        fi
        echo -n "$OPRIV "
        COUNT=$((COUNT+1))
      done
      echo
    done
  } | less -FX
}

db_list_help() {
  echo "Usage: $TOOLNAME db list [REGEXP]"
  echo
  echo "List available MySQL databases."
  echo
  echo "Optionally a regular expression can be passed to filter out results."
  echo "This REGEXP is passwd to egrep directly."
  echo
  echo "Listed results are paged via less."
  echo

  exit 0
}

## remove database
myctlsubcmd_db_del() {
  local OPTS="h" OPTIND=

  while getopts $OPTS arg; do
    case $arg in
      h)
        db_del_help
        ;;
      *)
        exit 1
        ;;
    esac
  done

  shift $((OPTIND-1))

  # get positional param
  local DB=$1

  # sanity
  [ -z "$DB" ] && exit_error "No database given for removal" db_del_help
  [ $# -ne 1 ] && exit_error "Too many parameters" db_del_help

  # check if DB exists
  check_db "$DB" || exit_error "Database $DB does not exist"

  # confirm removal
  confirm "Really remove MySQL database $DB"

  # remove database
  mysql_exec "DROP DATABASE $DB"
  print_info "Database $DB removed"
}

db_del_help() {
  echo "Usage: $TOOLNAME db del database"
  echo
  echo "Remove given MySQL database."
  echo

  exit 0
}

## add database
myctlsubcmd_db_add() {
  local USRS= USR= UNAME= HOST= OPTIND= DUMP=
  local OPTS="hu:f:"

  while getopts $OPTS arg; do
    case $arg in
      h)
        db_add_help
        ;;
      u)
        USRS="$(sed 's/,/ /g' <<< "$OPTARG")"
        ;;
      f)
        DUMP=$OPTARG
        ;;
      *)
        exit 1
        ;;
    esac
  done

  shift $((OPTIND-1))

  # get positional param
  local DB=$1

  # check if DB exists
  [ -z "$DB" ] && exit_error "No database name given" db_add_help
  [ $# -ne 1 ] && exit_error "Too many parameters" db_add_help
  check_db "$DB" && exit_error "Database $DB already exists"

  # dump file
  [ -n "$DUMP" -a ! -e "$DUMP" ] && exit_error "Dump file '$DUMP' not found"

  # check if given users exist
  if [ -n "$USRS" ]; then
    for USR in $USRS; do
      echo $USR | egrep -q -- ".+@.+" || USR="$USR@$DEF_HOST"
      check_user "$USR" || exit_error "User $USR does not exist"
    done
  fi

  # add user
  SQL="CREATE DATABASE $DB"
  mysql_exec "$SQL" || exit_error "Creating database"
  print_info "Created database '$DB'"

  # grant all privileges to users
  if [ -n "$USRS" ]; then
    for USR in $USRS; do
      echo $USR | egrep -q -- ".+@.+" || USR="$USR@$DEF_HOST"
      IFS=@ read UNAME HOST <<< "$USR"
      SQL="GRANT ALL ON $DB.* TO '$UNAME'@'$HOST';"
      mysql_exec "$SQL" || exit_error "Granting all privileges to database '$DB'"
      print_info "Granted all privileges for '$USR' to '$DB'"
    done
  fi

  # restore dump from given file
  [ -n "$DUMP" ] && myctlsubcmd_db_restore $DB $DUMP
}

db_add_help() {
  echo "Usage: $TOOLNAME db add [options] DATABASE"
  echo
  echo "Add new database. Optionally grant all privileges for given existing user or more users."
  echo "If user has no host information, the default host is used."
  echo
  echo "Options:"
  echo " -f dump          Restore given dump to the database"
  echo " -u user@host,..  Grant all privileges for given users separated by commas"
  echo

  exit 0
}

## restore database
myctlsubcmd_db_restore() {
  local OPTIND= REMOVE= FILEINFO=
  local OPTS="hr"

  while getopts $OPTS arg; do
    case $arg in
      h)
        db_restore_help
        ;;
      r)
        REMOVE=yes
        ;;
      *)
        exit 1
        ;;
    esac
  done

  shift $((OPTIND-1))

  # get positional params
  local DB=$1
  local DUMP=$2

  # check if DB exists
  [ -z "$DB" ] && exit_error "No database given" db_restore_help
  [ $# -ne 2 ] && exit_error "Restore needs 2 parameters" db_restore_help
  check_db "$DB" || exit_error "Database $DB does not exist"

  # check if dump exists
  [ -e "$DUMP" ] || exit_error "Dump file $DUMP not found"

  # truncate database if requested, do no ask confirmation
  if [ -n "$REMOVE" ]; then
    mysql_exec <<< "DROP DATABASE $DB; CREATE DATABASE $DB" || \
      exit_error "Failed to remove database contents"
    print_info "Removed database '$DB' contents"
  fi

  # try to restore
  FILEINFO=$(file -b $DUMP)
  case ${FILEINFO,,} in
    bzip2*) bunzip2 < $DUMP ;;
    gzip*) gunzip < $DUMP ;;
    zip*) unzip -p $DUMP ;;
    *) cat $DUMP ;;
  esac | mysql -u$MYSQL_USER -p$MYSQL_PASSWD -h$MYSQL_HOST $DB || \
    exit_error "Restoring dump $DUMP to $DB failed"

  # success :)
  print_info "Restored MySQL dump '$DUMP' to database '$DB'"
}

db_restore_help() {
  echo "Usage: $TOOLNAME db restore [options] DATABASE FILE"
  echo
  echo "Restore database contexts from given dump file. Optionally truncate"
  echo "database before restoring. The dump file can be uncompressed"
  echo "or compressed MySQL dump. Supported compression formats: gzip, zip"
  echo "and bzip2."
  echo
  echo "Options:"
  echo " -h          Show help"
  echo " -r          Drop all tables before restore"
  echo

  exit 0
}

## dump database
myctlsubcmd_db_dump() {
  local OPTIND=
  local COMPRESS="gzip"
  local SUFFIX="sql.gz"
  local OPTS="hn"

  while getopts $OPTS arg; do
    case $arg in
      h)
        db_dump_help
        ;;
      n)
        COMPRESS="cat"
        SUFFIX="sql"
        ;;
      *)
        exit 1
        ;;
    esac
  done

  shift $((OPTIND-1))

  # get positional params
  local DB=$1
  local DUMP=$2

  # check if DB exists
  [ -z "$DB" ] && exit_error "No database given" db_dump_help
  [ $# -gt 2 ] && exit_error "Dump accepts one or two parameters" db_dump_help
  check_db "$DB" || exit_error "Database $DB does not exist"

  # check if dump exists or create a new filename
  if [ -n "$DUMP" ]; then
    [ -e "$DUMP" ] && exit_error "Dump file already exists"
  else
    DUMP="$DB-$(date +%Y%m%d_%H-%M).$SUFFIX"
  fi

  mysqldump -u$MYSQL_USER -p$MYSQL_PASSWD -h$MYSQL_HOST $DB | \
    $COMPRESS > $DUMP || exit_error "Dumping database '$DB' failed"

  # success :)
  print_info "Dumped database '$DB' to '$DUMP'"
}

db_dump_help() {
  echo "Usage: $TOOLNAME db dump [options] DATABASE [FILE]"
  echo
  echo "Dump database contexts to file. By default the dump is compressed"
  echo "via gzip and the the dump name is generated from database name and"
  echo "current data. Optionally the output FILE can be specified."
  echo "The compression can be disabled via the -n option."
  echo
  echo "Options:"
  echo " -h          Show help"
  echo " -n          Disable gzip compression"
  echo

  exit 0
}


#
# provilege management
#

## privilege command help
myctlcmd_privilege() {
  echo "Usage: $TOOLNAME privilage [sub-command] [options]"
  echo
  echo "Privilege management."
  echo
  echo "Available sub-commands:"
  echo "  grant    Grant all privileges for a user on an existing database"
  echo "  list     List users with privileges to databases (equal to 'user list')"
  echo "  revoke   Revoke all privileges for a user on a given database"
  echo
  echo "Run sub-command with -h parameter to get detailed help."
  echo

  exit 0
}

## list users with privileges
myctlsubcmd_privilege_list() {
  myctlsubcmd_user_list $@
}

## grant privileges
myctlsubcmd_privilege_grant() {
  local OPTIND= UNAME= HOST=
  local OPTS="h"

  while getopts $OPTS arg; do
    case $arg in
      h)
        privilege_grant_help
        ;;
      *)
        exit 1
        ;;
    esac
  done

  shift $((OPTIND-1))

  # get positional params
  IFS=@ read UNAME HOST <<< "$1"
  [ -z "$HOST" ] && HOST="$DEF_HOST"
  local DB=$2

  # check if user exists
  [ -z "$UNAME" ] && exit_error "No user specified" privilege_grant_help
  check_user "$UNAME@$HOST" || exit_error "User $UNAME@$HOST does not exist"

  if [ -n "$DB" ]; then
    # check if db exitst
    check_db "$DB" || exit_error "Database $DB does not exist"
    # grant privilege for given db
    SQL="GRANT ALL ON $DB.* TO '$UNAME'@'$HOST';"
    mysql_exec "$SQL" || exit_error "Granting all privileges to database '$DB'"
    print_info "Granted user '$UNAME@$HOST' all privileges to database '$DB'"
  else
    # ask for confirmation
    confirm "Do you want to add privileges to all databases for user $UNAME@$HOST"
    # grant privilege for all dbs
    SQL="GRANT ALL ON *.* TO '$UNAME'@'$HOST';"
    mysql_exec "$SQL" || exit_error "Granting all privileges to all databases for $UNAME@$HOST"
    print_info "Granted user '$UNAME@$HOST' all privileges to all databases"
  fi
}

privilege_grant_help() {
  echo "Usage: $TOOLNAME privilege grant [options] user[@host] [database]"
  echo
  echo "Grant all privileges for given database to specified user. If host is"
  echo "not specified - default host $DEF_HOST is used. If no database is specified"
  echo "a user will be given access to all databases after confirmation."
  echo
  echo "Options:"
  echo " -h          Show help"
  echo

  exit 0
}

## revoke privileges
myctlsubcmd_privilege_revoke() {
  local OPTIND= UNAME= HOST=
  local OPTS="h"

  while getopts $OPTS arg; do
    case $arg in
      h)
        privilege_revoke_help
        ;;
      *)
        exit 1
        ;;
    esac
  done

  shift $((OPTIND-1))

  # get positional params
  IFS=@ read UNAME HOST <<< "$1"
  [ -z "$HOST" ] && HOST="$DEF_HOST"
  local DB=$2

  # check if user exists
  [ -z "$UNAME" ] && exit_error "No user specified" privilege_grant_help
  check_user "$UNAME@$HOST" || exit_error "User $UNAME@$HOST does not exist"

  if [ -n "$DB" ]; then
    # check if db exitst
    check_db "$DB" || exit_error "Database $DB does not exist"
    # check if user already has privileges
    check_user_db "$UNAME@$HOST" "$DB" || exit_error \
      "User '$UNAME@$HOST' has no privileges to database '$DB'"
    # grant privilege for given db
    SQL="REVOKE ALL ON $DB.* FROM '$UNAME'@'$HOST';"
    mysql_exec "$SQL" || exit_error "Revoking all privileges from database '$DB'"
    print_info "Revoked all privileges from user '$UNAME@$HOST' to database '$DB'"
  else
    # check if user has some privileges
    check_user_db_none "$UNAME@$HOST" && exit_error \
      "User '$UNAME@$HOST' has no privileges"
    # ask for confirmation
    confirm "Do you want to revoke privileges to all databases from user $UNAME@$HOST"
    # grant privilege for all dbs
    SQL="REVOKE ALL PRIVILEGES, GRANT OPTION FROM '$UNAME'@'$HOST';"
    mysql_exec "$SQL" || exit_error "Revoking all privileges to all databases for $UNAME@$HOST"
    print_info "Revoked all privileges from user '$UNAME@$HOST' to all databases"
  fi
}

privilege_revoke_help() {
  echo "Usage: $TOOLNAME privilege revoke [options] user[@host] [database]"
  echo
  echo "Revoke all privileges for given database to specified user. If host is"
  echo "not specified - default host $DEF_HOST is used. If no database is specified"
  echo "a user will be revoked access to all databases after confirmation."
  echo
  echo "Options:"
  echo " -h          Show help"
  echo

  exit 0
}

#
# shell
#
myctlcmd_shell() {
  local OPTS="hnd:" OPTIND=
  local MEXEC="mysql_exec"
  local MOPTS=

  while getopts $OPTS arg; do
    case $arg in
      h)
        shell_help
        ;;
      n)
        MOPTS="--skip-column-names"
        ;;
      d)
        DB="$OPTARG"
        ;;
      *)
        exit 1
        ;;
    esac
  done
  shift $((OPTIND-1))

  # check if db exists
  if [ -n "$DB" ]; then
    check_db $DB || exit_error "Database '$DB' does not exist"
    MEXEC="mysql_exec_db $DB"
  fi

  if [ -n "$MOPTS" ]; then
      export MYSQL_OPTS="$MOPTS"
  fi

  # run the shell
  [ -z "$1" ] && $MEXEC || $MEXEC "$@"

  return $?
}

shell_help() {
  echo "Usage: $TOOLNAME shell [options] [SQL]"
  echo
  echo "Get an administrator MySQL shell or execute given SQL. Optionally"
  echo "use the specified database. By default the results are show with"
  echo "column names. Use the -n option to omit the column names"
  echo
  echo "Options:"
  echo " -h           Show help"
  echo " -n           Do not show column names"
  echo " -d DATABASE  Run shell or execute command for given database."
  echo

  exit 0
}


#
# install
#
install_get_env() {
  local RET=1

  echo
  echo "You will now be asked for configuration details for myctl. Please note"
  echo "that you will need credentials for an MySQL administrator. The tool"
  echo "will use this user for every operation."
  echo

  while [ $RET -ne 0 ]; do
    MYSQL_USER=$(ask_user "Enter MySQL admin username")
    MYSQL_PASSWD=$(ask_user "Enter MySQL admin password")
    mysql_exec "exit" >/dev/null; RET=$?
    if [ $RET -eq 0 ]; then
      # TODO check if user admin
      break
    else
      print_error "Cannot login with the given credentials to MySQL! Try again ..."
    fi
  done

  MYSQL_HOST=$(ask_user "MySQL host" "localhost")
  MYSQL_PORT=$(ask_user "MySQL port" "3306")
  DEF_HOST=$(ask_user "Default host for new user" "localhost")
  DEF_PASSWD=$(ask_user "Default password for new user" "ask for password")
  [ "$DEF_PASSWD" == "ask for password" ] && DEF_PASSWD=
}

write_config() {
  mkdir -p $(dirname $1)
  {
    echo "# $TOOLNAME configuration file created $(date)"
    echo
    echo "# MySQL admin credentials"
    echo "MYSQL_USER=\"$MYSQL_USER\""
    echo "MYSQL_PASSWD=\"$MYSQL_PASSWD\""
    echo
    echo "# MySQL server details"
    echo "MYSQL_HOST=\"$MYSQL_HOST\""
    echo "MYSQL_PORT=\"$MYSQL_PORT\""
    echo
    echo "# Default host for new users"
    echo "DEF_HOST=\"$DEF_HOST\""
    echo
    echo "# Default password for new user"
    echo "DEF_PASSWD=\"$DEF_PASSWD\""
  } > $1

  print_info "Configuration file '$1' has been created"
}

install_user() {
  local ANSW=

  echo
  echo "User installation"
  echo "-----------------"

  # write configuration file
  install_get_env
  echo
  write_config $USER_CONFIG

  # install myctl to bin folder
  if ! which $TOOLNAME &> /dev/null; then
    ANSW=$(ask_user "Copy tool to $HOME/bin folder? (y/n)")
    if [ "$ANSW" = "y" ]; then
      mkdir -p "$HOME/bin"
      cp -f $0 $HOME/bin/
      # check if $HOME/bin in PATH
      grep -q ":$HOME/bin" <<< "$PATH" || print_info \
        "Please note that '$HOME/bin' folder doesn't seem to be in your PATH. \
Update your profile with 'export PATH=\$PATH:\$HOME/bin'."
    fi
  fi

  # we are done
  print_info "User installation finished successfully. Enjoy!"
}

install_system() {
  echo

  # check if we are root
  [ $(id -u) -eq 0 ] || exit_error "Root required for system installation"

  echo "System installation"
  echo "-------------------"

  # write configuration file
  install_get_env
  echo
  write_config $SYSTEM_CONFIG

  # ask for myctl group
  MYCTL_GROUP=$(ask_user "Enter $TOOLNAME group that will be needed for using the tool" \
    "$MYCTL_GROUP")

  # create myctl group
  if ! getent group $MYCTL_GROUP &> /dev/null; then
    groupadd $MYCTL_GROUP
    print_info "Group '$MYCTL_GROUP' created. You need to add myctl users \
to this group to be able to use it."
  else
    print_info "Group '$MYCTL_GROUP' already exists, skipping creation ..."
  fi
  chmod 640 $SYSTEM_CONFIG
  chown root:$MYCTL_GROUP $SYSTEM_CONFIG

  # install myctl to system path
  cp -f $0 /usr/bin
  print_info "Tool installed to '/usr/bin' path"
  print_info "System installation finished successfully. Enjoy!"
}

myctlcmd_install() {
  local IMETHOD=

  is_myctl_installed && exit_info \
    "Tool is already installed and configured correctly. Run 'uninstall' command to remove."

  echo "You are about to install and configure $TOOLANME - MySQL command line tool - on your system."
  echo
  confirm "Do you want to continue"
  echo
  echo "This tool can be installed in two ways:"
  echo
  echo "1. System-wide installation"
  echo "   This will install the tool for all users and setup a system-wide configuration."
  echo "   A new group - myctl - will be created and only users in this group will be able"
  echo "   to use the tool. The configuration file containing possibly sensitive information"
  echo "   will be only readable by group members. Root privileges are required for this"
  echo "   type of installation."
  echo
  echo "2. User installation"
  echo "   This will install the tool only for current user. The tool will be optionally installed in"
  echo "   '$HOME/bin/' directory. The directory will be created if it does not exist and you"
  echo "   may want to manually configure your PATH for this directory. The configuration"
  echo "   file will be created in the users home directory."
  echo

  IMETHOD=$(ask_user "Please choose installation method (1/2) or enter 'q' to quit")

  case $IMETHOD in
    1)
      install_system || exit_error "System installation failed"
      ;;
    2)
      install_user || exit_error "User installation failed"
      ;;
    q)
      exit_error "Installation cancelled."
      ;;
    *)
      exit_error "Unknown choice '$IMETHOD'. Cowardly exiting ..."
      ;;
  esac
}

#
# install
#
myctlcmd_uninstall() {
  local GROUP= ANSW=

  is_myctl_installed || exit_error "Tool is not installed, cannot unistall"

  echo "You are about to uninstall '$TOOLNAME' from you system."
  echo
  echo "If you are running this tool as root, it will remove the system-wide configuration"
  echo "and root user configuration from ~/.$TOOLNAME.conf. Also $TOOLNAME script will be removed"
  echo "from the system."
  echo
  echo "If you are running this tool a non-root user - only the user configuration will"
  echo "be removed from ~/.$TOOLNAME.conf. Also the tools will be removed from ~/bin/ folder."
  echo
  confirm "Do you want to continue?"
  echo
  if [ $(id -u) -eq 0 ]; then
    if [ -e "$SYSTEM_CONFIG" ]; then
      # get group of system config file
      GROUP=$(stat -c '%G' $SYSTEM_CONFIG)
      ANSW=$(ask_user "Remove $TOOLNAME group '$GROUP' (y/n)?")
      if [ "$ANSW" = "y" ]; then
        groupdel $GROUP || print_error "Removing group '$GROUP'" && \
          print_info "Removed group '$GROUP'"
      else
        print_info "Skipping removal of group '$GROUP'"
      fi
      rm -f "$SYSTEM_CONFIG"
      print_info "Removed system-wide configuration"
    fi
    if which $TOOLNAME &> /dev/null; then
      rm -f $(which myctl)
      print_info "Removed $TOOLNAME from system"
    fi
  fi

  if [ -e "$USER_CONFIG" ]; then
      rm -f "$USER_CONFIG"
      print_info "Removed user configuration"
  fi

  if [ -e "$HOME/bin/$TOOLNAME" ]; then
    rm -f "$HOME/bin/$TOOLNAME"
    print_info "Removed myctl tool from $HOME/bin/"
  fi
}

#
# Main
#

while getopts "hyv" OPTION
do
  case $OPTION in
    h)
        show_help
        ;;
    y)
        CONFIRM=yes
        ;;
    v)
        echo "Version: $VERSION"
        exit
        ;;
    *)
        exit 1
        ;;
  esac
done

shift $((OPTIND-1))

[ -n "$1" ] && COMMAND=$1 || show_help

# load configuration
load_config

# sanity checks
[ "$COMMAND" != "install" ] && sanity_checks

# shift away command
shift 1

# execute give command specific function
GEN=$(compgen -A function myctlcmd_$COMMAND)
if [ -n "$GEN" ]; then
  if [ $(wc -l <<< "$GEN") -gt 1 ]; then
    TMP=$(for TMP in $GEN; do echo -n "${TMP#myctlcmd_} "; done)
    exit_error "More commands match \"$COMMAND\": $TMP" show_help
  fi

  # shell command has no subcommands and accepts some params
  if [ "$COMMAND" == "shell" ]; then
    myctlcmd_${COMMAND} $@
  else
    dispatch_command ${GEN#myctlcmd_} $@
  fi
  exit $?
fi

exit_error "unknown command $COMMAND"

# vim: ai ts=2 sw=2 et