mysqldbutils

#!/usr/bin/env bash
# 
# File:
#   mysqldbutils
# 
# Description:
#   Generic RDBMS utility functions for a MySQL database
# 
# Usage:
#   mysqldbutils <databaes_user> <database_name> <action>
# 

if [ "$#" -lt 3 ]; then
  echo -e  'mysqldbutils: invalid number of arguments; database user,\n'\
      '  database name and action required' 1>&2
  exit 1
fi

readonly DB_USER="${1}"
readonly DB_NAME="${2}"
readonly ACTION="${3}"
shift 3

case "${ACTION}" in
  # Action:
  #   create_from_file
  # Description:
  #   Create the database from an SQL file.
  # Usage:
  #   ... create_from_file <sql_file>
  'create_from_file')
    [ "$#" -eq 0 ] && \
        echo "mysqldbutils: no SQL file specified" 1>&2 && \
        exit 1
    [ ! -f "${1}" ] && \
        echo "mysqldbutils: file \"${1}\" not found" 1>&2 && \
        exit 1

    mysql -u"${DB_USER}" -e "CREATE DATABASE IF NOT EXISTS ${DB_NAME}"
    if mysql -u"${DB_USER}" -e "use ${DB_NAME}" 2>/dev/null; then
      bash "${0}" 'drop_all_tables' "${DB_USER}" "${DB_NAME}"
      mysql -u"${DB_USER}" "${DB_NAME}" < "${1}"
    else
      echo "mysqldbutils: database \"${DB_NAME}\" not found" 1>&2
    fi
    ;;
    
  # Action:
  #   drop_all_tables
  # Description:
  #   Drop all tables in the database.
  # Usage:
  #   ... drop_all_tables
  'drop_all_tables')
    query="
      USE ${DB_NAME};
      SET FOREIGN_KEY_CHECKS = 0;
      SET GROUP_CONCAT_MAX_LEN=32768;
      SET @tables = NULL;
      SELECT GROUP_CONCAT('\`', table_name, '\`') INTO @tables
        FROM information_schema.tables
        WHERE table_schema = (SELECT DATABASE());
      SELECT IFNULL(@tables,'dummy') INTO @tables;

      SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
      PREPARE stmt FROM @tables;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
      SET FOREIGN_KEY_CHECKS = 1;
      "
    mysql -u"${DB_USER}" -e "${query}"
    ;;
    
  # Action:
  #   clear_all_tables
  # Description:
  #   Clear all tables in the database.
  # Usage:
  #   ... clear_all_tables
  'clear_all_tables')
    mysqldump -d -u"${DB_USER}" --add-drop-table "${DB_NAME}" > \
        ./db_export_tmp.sql
    mysql -u"${DB_USER}" "${DB_NAME}" < ./db_export_tmp.sql
    rm ./db_export_tmp.sql
    ;;
    
  # Action:
  #   export
  # Description:
  #   Export the database.
  # Usage:
  #   ... export
  'export')
    mysqldump -u"${DB_USER}" "${DB_NAME}" > ./db_"${DB_NAME}"_export.sql
    ;;

  *)
    echo "mysqldbutils: unknown action \"${ACTION}\"" 1>&2
    ;;

esac