#!/bin/bash
showHelp() {
        cat <<'EOF'

 myLoop
 Copyright (C) 2009 Robert Jan Bast

 This software comes with ABSOLUTELY NO WARRANTY. This is free software,
 and you are welcome to modify and redistribute it under the GPL license.

  Options:

   -h, --help                print this help message
   -?                        synonym for -h|--help
   -v, --verbose             verbose mode
   -V, --verbose-more        be even more verbose
   -a, --analyze             run analyze over all found tables
   -o, --optimize            run optimize over all found tables
   -d, --database            limit actions to specified database (may contain * as wildcard)
   -t, --table               limit actions to specified table (may contain * as wildcard)
       --statement-table     statement to run over every table found
       --statement-database  statement to run over every table found

  Statements may contain '%s' as a placeholder for table|database name.


  Usage examples:

   This will analyze and optimize all tables in database named `somedb`:
     $ myLoop -a -o -d somedb

   This will truncate all tables matching tmp_* in database `myproject`:
     $ myLoop -d myproject -t tmp_* --statement-table "TRUNCATE TABLE %s"

   This will drop all tables in every database and then drop the database:
     $ myLoop --statement-table "DROP TABLE %s" --statement-database "DROP DATABASE %s"

EOF
}

# Check if any arguments provided
if (($#)); then
        # Loop through them
        while [[ $1 == -* ]]; do
                # Check if any valid arguments are passed
                case "$1" in
                        -\?|-h|--help) # User wants help
                                showHelp && exit;;
                        -v|--verbose) # User wants verbose mode
                                verbose=1
                                shift;;
                        -V|--verbose-more) # User wants VERBOSEOMGMORE mode
                                verbose=2
                                shift;;
                        -a|--analyze) # User wants to analyze stuff
                                analyze=1
                                shift;;
                        -o|--optimize) # User wants to optimize stuff
                                optimize=1
                                shift;;
                        -d|--database) # User wants to limit loop to databases matching specified name/pattern
                                only_database="$2"
                                shift 2;;
                        -t|--table) # User wants to limit loop to tables matching specified name/pattern
                                only_table="$2"
                                shift 2;;
                        --statement-table) # User passed a statement to be run over table(s)
                                statement_table="$2"
                                shift 2;;
                        --statement-database) # User passed a statement to be run over database(s)
                                statement_database="$2"
                                shift 2;;
                        -*) # Invalid argument passed, fail
                                echo "Invalid argument: $1"
                                showHelp && exit 1;;
                esac
        done
else
        showHelp && exit 1
fi

# List of databases we want to exclude
# Add databases you never want to touch here
exclude_databases=(
        information_schema
        mysql
)

# Get username and password from user
read -rp  "Enter username: " username
read -rsp "Enter password: " password
echo ""

#username="root"
#password=""

# Function to check if element contains pattern
isIn() {
        # Make sure arguments are kept local and do not overwrite variables outside of function scope
        local pattern="$1" element
        shift
        # Loop through element items
        for element; do
                # If we have a match
                [[ $pattern = $element ]] && return
        done
        # If nothing matched
        return 1
}

# Function that loops over all tables in database
loopTables() {
        # Get a list of all tables inside current database
        IFS=$'\n' read -d '' -a tables < <(mysql -u"$username" -p"$password" -D"$database" -s --skip-column-names -e "SHOW TABLES")
        # Loop through tables found
        for table in "${tables[@]}"; do
                # If there was no specific table specified as target, or current table matches specified
                if [[ ! $only_table || $only_table = $table ]]; then
                        # If user wants to analyze
                        if [[ $analyze ]]; then
                                # Check if verbose mode is on
                                if [[ $verbose ]]; then
                                        # Check if extra verbose mode is requested
                                        if [[ $verbose = 2 ]]; then
                                                echo "Analyzing table \`$table\` in \`$database\`"
                                                mysql -u"$username" -p"$password" -D "$database" -e "ANALYZE TABLE \`$table\`"
                                        else
                                                mysql -u"$username" -p"$password" -D "$database" -s -e "ANALYZE TABLE \`$table\`"
                                        fi
                                # Don't be verbose
                                else
                                        mysql -u"$username" -p"$password" -D "$database" -e "ANALYZE TABLE \`$table\`" > /dev/null
                                fi
                        fi
                        # If user wants to optimize
                        if [[ $optimize ]]; then
                                # Check if verbose mode is on
                                if [[ $verbose ]]; then
                                        # Check if extra verbose mode is requested
                                        if [[ $verbose = 2 ]]; then
                                                echo "Optimizing table \`$table\` in \`$database\`"
                                                mysql -u"$username" -p"$password" -D "$database" -e "OPTIMIZE TABLE \`$table\`"
                                        else
                                                mysql -u"$username" -p"$password" -D "$database" -s -e "OPTIMIZE TABLE \`$table\`"
                                        fi
                                # Don't be verbose
                                else
                                        mysql -u"$username" -p"$password" -D "$database" -e "OPTIMIZE TABLE \`$table\`" > /dev/null
                                fi
                        fi
                        # If user wants to run statement on table
                        if [[ $statement_table ]]; then
                                # Prepare statement
                                printf -v statement "$statement_table" "$table"
                                # Check if verbose mode is on
                                if [[ $verbose ]]; then
                                        # Check if extra verbose mode is requested
                                        if [[ $verbose = 2 ]]; then
                                                echo "$database > $table > $statement"
                                                mysql -u"$username" -p"$password" -D "$database" -e "$statement"
                                        else
                                                mysql -u"$username" -p"$password" -D "$database" -s -e "$statement"
                                        fi
                                # Don't be verbose
                                else
                                        mysql -u"$username" -p"$password" -D "$database" -e "$statement" > /dev/null
                                fi
                        fi
                fi
        done
}

# Send 'show databases' to mysql server and parse the returned result
IFS=$'\n' read -d '' -a databases < <(mysql -u"$username" -p"$password" -s --skip-column-names -e "SHOW DATABASES")

# Loop through found databases
for database in "${databases[@]}"; do
        # Check if database found is not in our exclude list
        if ! isIn "$database" "${exclude_databases[@]}"; then
                # If there was no specific database specified as target, or current database matches specified
                if [[ ! $only_database || $only_database = $database ]]; then
                        # If user wants to analyze and/or optimize and/or run a statement on tables
                        if [[ $analyze || $optimize || $statement_table ]]; then
                                loopTables
                        fi
                        # Run database statement if provided
                        if [[ $statement_database ]]; then
                                # Prepare statement
                                printf -v statement "$statement_database" "$database"
                                # Check if verbose mode is on
                                if [[ $verbose ]]; then
                                        # Check if extra verbose mode is requested
                                        if [[ $verbose = 2 ]]; then
                                                echo "$database > $table > $statement"
                                                mysql -u"$username" -p"$password" -D "$database" -e "$statement"
                                        else
                                                mysql -u"$username" -p"$password" -D "$database" -s -e "$statement"
                                        fi
                                # Don't be verbose
                                else
                                        mysql -u"$username" -p"$password" -D "$database" -e "$statement" > /dev/null
                                fi
                        fi
                fi
        fi
done

echo "Execution finished"