#!/bin/bash
#
# MySQL Tools.
#
# For suggestion, bug reports, please contact Pierre-Yves Landuré <pierre-yves@landure.fr>


SCRIPT_NAME="$(command basename ${0})"

# Print this script help.
function usage {
  command echo "
This tool ease MySQL server administration from command line.

Usage :

  ${SCRIPT_NAME} create-db database_name [ \"allowed hosts\" [ root_password ] ]
  ${SCRIPT_NAME} create-domain-db www.domain-name.com [ \"allowed hosts\" [ root_password ] ]
  ${SCRIPT_NAME} create-user user_name [ user_password [ \"allowed hosts\" [ root_password ] ] ]
  ${SCRIPT_NAME} clear-db database_name user_name user_password

  * create-db : Create a database and its user.
  * create-domain-db : Create a database for domain
  * create-user : Create a user without priviledge.
  * clear-db : Drop all tables of a database.
"
  exit 1
} # usage



# Get the absolute path for a file or directory.
#
# @param string $path A relative path.
#
# @return ${REALPATH} A absolute path.
REALPATH=""
function realpath {
  command test ${#} -ne 1 && exit 1
  REALPATH=$(/bin/readlink -f "${1}")
} # realpath



# Check if a binary is present
#
# @param string $binary The binary to check.
# @param string $package The package the binary come from.
#
# @return Exit with error if the binary is missing.
function check_binary {
  command test ${#} -ne 2 && exit 1

  # Test the binary presence.
  if [ -z "$(which "${1}")" ]; then
    echo "Error : '${1}' is missing. Please install package '${2}'."
    exit 1
  fi
} # check_binary



# Check if MySQL connection is working
#
# @param string $mysql_host The MySQL host.
# @param string $mysql_user The MySQL user.
# @param string $mysql_password The MySQL password.
# @param string $mysql_db The MySQL DB.
#
# @return Exit with error if connection to MySQL fail.
function check_mysql {
  command test ${#} -ne 4 && exit 1

  # Test the MySQL connection.
  if ! command mysql --execute="SELECT 1" \
            --host="${1}" --user="${2}" --password="${3}" \
            "${4}" 2>&1 > /dev/null; then
    echo "Error : Unable to connect to MySQL. Please provide valid MySQL connection parameters."
    exit 1
  fi
} # check_mysql



# Download a file from the given URL.
#
# @param string $url The URL of the file to download.
#
# @return ${DOWNLOAD_FILE} The path to the downloaded file.
DOWNLOAD_FILE=""
function download_file {
  command test ${#} -ne 1 && exit 1

  # Download a file.
  DOWNLOAD_FILE="$(command mktemp)"
  command wget --quiet "${1}" \
      --output-document="${DOWNLOAD_FILE}"

  if [ ! -s "${DOWNLOAD_FILE}" ]; then
    command rm "${DOWNLOAD_FILE}"
    echo "Error : Unable to download file from '${1}'."
    exit 1
  fi
} # download_file



# Download and uncompress a tgz file from the given URL.
#
# @param string $url The URL of the file to download.
#
# @return ${DOWNLOAD_TGZ} The path to the extracted content.
DOWNLOAD_TGZ=""
function download_tgz {
  command test ${#} -ne 1 && exit 1

  download_file "${1}"

  # Untar the downloaded file and place it at its final location.
  DOWNLOAD_TGZ="$(command mktemp -d)"
  command tar --directory "${DOWNLOAD_TGZ}" -xzf "${DOWNLOAD_FILE}"
  command rm "${DOWNLOAD_FILE}"

  if [ $(command ls -1 --all "${DOWNLOAD_TGZ}" | command wc --lines) -eq 0 ]; then
    echo "Error : unable to untar file downloaded from '${1}'."
    exit 1
  fi
} # download_tgz



# Check for binaries presence
check_binary "basename" "coreutils"
check_binary "dirname" "coreutils"
check_binary "mktemp" "mktemp"
check_binary "sed" "sed"
check_binary "mysql" "mysql-client"
check_binary "apg" "apg"
check_binary "wget" "wget"

# Check if at least one args given.
command test ${#} -eq 0 && usage

case "${1}" in

  create-domain-db )
    # Check if valid number of arguments given.
    command test ${#} -lt 2 && usage

    DOMAIN="${2}"
    ALLOWED_HOSTS="${3}"
    ROOT_PASSWORD="${4}"

    MYSQL_DB=$(echo "${DOMAIN}" | command tr '[a-z].-' '[A-Z]__')

    ${0} create-db "${MYSQL_DB}" "${ALLOWED_HOSTS}" "${ROOT_PASSWORD}"

    exit 0
    ;;



  create-db )
    # Check if valid number of arguments given.
    command test ${#} -lt 2 && usage

    MYSQL_HOST="localhost"
    MYSQL_DB="${2}"
    ALLOWED_HOSTS="localhost ${3}"
    ROOT_PASSWORD="${4}"

    MYSQL_USER="$(command echo "${MYSQL_DB}" \
    | command tr '[A-Z]' '[a-z]' \
    | command cut --characters=-16)"
    MYSQL_PASSWORD="$(command apg -q -a  0 -n 1 -M NCL)"

    if [ -n "${ROOT_PASSWORD}" ]; then
      PASSWORD="--password=${ROOT_PASSWORD}"
    else
      PASSWORD="--password"
    fi

    SQL_QUERY="CREATE DATABASE IF NOT EXISTS \`${MYSQL_DB}\` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;"
    for ALLOWED_HOST in ${ALLOWED_HOSTS}; do
    SQL_QUERY="${SQL_QUERY}
    GRANT ALL PRIVILEGES ON \`${MYSQL_DB}\`.*
            TO \`${MYSQL_USER}\`@\`${ALLOWED_HOST}\`
            IDENTIFIED BY '${MYSQL_PASSWORD}';"
    done

    command echo "${SQL_QUERY}" | command mysql --user=root ${PASSWORD}

    command echo "# Les paramètres d'accès à votre base de données sont :
# Hôte
MYSQL_HOST=localhost
# Nom de la base de données
MYSQL_DB=${MYSQL_DB}
# Identifiant
MYSQL_USER=${MYSQL_USER}
# Mot de passe
MYSQL_PASSWORD=${MYSQL_PASSWORD}"
    exit 0
    ;;



  create-user )
    # Check if valid number of arguments given.
    command test ${#} -lt 2 && usage

    MYSQL_HOST="localhost"
    MYSQL_USER="${2}"
    MYSQL_PASSWORD="${3}"
    ALLOWED_HOSTS="localhost ${4}"
    ROOT_PASSWORD="${5}"

    if [ -z "${MYSQL_PASSWORD}" ]; then
      MYSQL_PASSWORD="$(command apg -q -a  0 -n 1 -M NCL)"
    fi

    if [ -n "${ROOT_PASSWORD}" ]; then
      PASSWORD="--password=${ROOT_PASSWORD}"
    else
      PASSWORD="--password"
    fi

    SQL_QUERY=""
    for ALLOWED_HOST in ${ALLOWED_HOSTS}; do
    SQL_QUERY="CREATE USER \`${MYSQL_USER}\`@\`${ALLOWED_HOST}\`
            IDENTIFIED BY '${MYSQL_PASSWORD}';"
    done

    command echo "${SQL_QUERY}" | command mysql --user=root ${PASSWORD}

    command echo "# Les paramètres d'accès de votre utilisateur sont :
# Hôte
MYSQL_HOST=localhost
# Identifiant
MYSQL_USER=${MYSQL_USER}
# Mot de passe
MYSQL_PASSWORD=${MYSQL_PASSWORD}"
    exit 0
    ;;



  clear-db )
    # Check if valid number of arguments given.
    command test ${#} -eq 3 && usage

    MYSQL_HOST="localhost"
    MYSQL_DB="${2}"
    MYSQL_USER="${3}"
    MYSQL_PASSWORD="${4}"

    SQL="$(command mysql --user="${MYSQL_USER}" --password="${MYSQL_PASSWORD}" \
        --host="${MYSQL_HOST}" "${MYSQL_DB}" --execute="SHOW TABLES;" \
        | command grep -v 'Tables_in' \
        | command sed -e 's/^.*$/drop table &;/')"

    if [ -n "${SQL}" ]; then
      SQL="SET FOREIGN_KEY_CHECKS = 0;
${SQL}
SET FOREIGN_KEY_CHECKS = 1;"

      if command mysql --user="${MYSQL_USER}" --password="${MYSQL_PASSWORD}" \
            --host="${MYSQL_HOST}" "${MYSQL_DB}" --execute="${SQL}"; then
        echo "Tables dropped successfully."
        exit 0
      fi
    fi

    echo "Error during dropping tables."
    exit 1
    ;;

  * )
    echo "Error : '${1}' is not a valid action."
    usage
    ;;
esac

exit 0