#!/bin/bash

# Help!?!?!!!!1111omgwtfbbq
showHelp() {
	cat <<'EOF'

  Options:

   -?, --help                print this help message
   -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 named tmp_something 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 [[ $1 ]]; then

	# Loop through them
	while [[ $1 == -* ]]; do

		# Check if any valid arguments are passed
		case "$1" in

			-\?|--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 ""

# 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 0

	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 "$database > $table > tANALYZE TABLE $table"
						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 "$database > $table > tOPTIMIZE TABLE $table"
						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 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