#!/bin/bash

###############################################################################
# This script analyzes a MySQL slowlog file in that it counts the number of
# queries per type per database.
# If the slowlog file has been preprocessed/converted to monoline, invoke this
# script with -p.
# Following query types are counted:
# ALTER CREATE DROP RENAME TRUNCATE CALL DELETE DO HANDLER INSERT UPDATE LOAD
# SELECT SET
# Please note that SET are not relevant because it's used also to set timestamp
# for every request.
# Also, JOIN queries are counted, but all counted JOIN queries are also counted
# in SELECT ones.
#
# If there is a DB name but the query doesn't match any type listed above, then
# it's counted an UNKNOWN.
###############################################################################

usage () {
	echo "Usage: ${0##*/} [-p] logfile"
	echo '-p    if logfile has been preprocessed by mysql_convert_slowlog_to_oneline.sh'
	echo 'Logfile can be in gzip or plaintext format.'
	exit 0
}

PREPROCESSED=false
POSITIONAL=()

while [[ $# -gt 0 ]]
do
key="$1"

case $key in
	-p)
		PREPROCESSED=true
		shift
		;;
	*)
		POSITIONAL+=("$1") # save it in an array for later
		shift
		;;
esac
done

set -- "${POSITIONAL[@]}" # restore positional parameters

[[ -f "$1" ]] || usage

declare -A my_array
DB=''
my_dbs=''

preproc() {
while read LINE
do
	if [[ ${LINE} =~ Schema:\ (DB[0-9]*) ]]
	then
		DB="${BASH_REMATCH[1]}"
	else
		DB=''
	fi

	if [[ "$DB" == DB* ]]
	then

		if ! [[ ${my_dbs} == *"$DB"* ]]
		then
			my_dbs="${my_dbs} $DB"
			my_array[${DB}JOIN]=0
			my_array[${DB}SELECT]=0
			my_array[${DB}UPDATE]=0
			my_array[${DB}INSERT]=0
			my_array[${DB}DELETE]=0
			my_array[${DB}SET]=0
			my_array[${DB}ALTER]=0
			my_array[${DB}CREATE]=0
			my_array[${DB}DROP]=0
			my_array[${DB}RENAME]=0
			my_array[${DB}TRUNCATE]=0
			my_array[${DB}CALL]=0
			my_array[${DB}DO]=0
			my_array[${DB}HANDLER]=0
			my_array[${DB}LOAD]=0
			my_array[${DB}UNKNOWN]=0
		fi

		LINE="${LINE^^}"

		if [[ ${LINE} == *" SELECT "* ]]
		then
			((my_array[${DB}SELECT]++))
			if [[ ${LINE} == *" JOIN "* ]]
			then
				((my_array[${DB}JOIN]++))
			fi
		elif [[ ${LINE} == *" UPDATE "* ]]
		then
				((my_array[${DB}UPDATE]++))
		elif [[ ${LINE} == *" INSERT "* ]]
		then
			((my_array[${DB}INSERT]++))
		elif [[ ${LINE} == *" DELETE "* ]]
		then
			((my_array[${DB}DELETE]++))
		elif [[ ${LINE} == *" SET "* ]]
		then
			((my_array[${DB}SET]++))
		elif [[ ${LINE} == *" ALTER "* ]]
		then
			((my_array[${DB}ALTER]++))
		elif [[ ${LINE} == *" CREATE "* ]]
		then
			((my_array[${DB}CREATE]++))
		elif [[ ${LINE} == *" DROP "* ]]
		then
			((my_array[${DB}DROP]++))
		elif [[ ${LINE} == *" RENAME "* ]]
		then
			((my_array[${DB}RENAME]++))
		elif [[ ${LINE} == *" TRUNCATE "* ]]
		then
			((my_array[${DB}TRUNCATE]++))
		elif [[ ${LINE} == *" CALL "* ]]
		then
			((my_array[${DB}CALL]++))
		elif [[ ${LINE} == *" DO "* ]]
		then
			((my_array[${DB}DO]++))
		elif [[ ${LINE} == *" HANDLER "* ]]
		then
			((my_array[${DB}HANDLER]++))
		elif [[ ${LINE} == *" LOAD "* ]]
		then
			((my_array[${DB}LOAD]++))
		else
			((my_array[${DB}UNKNOWN]++))
		fi
	fi
done
}



nopreproc() {
while read LINE
do
	if [[ ${LINE} =~ Schema:\  ]]
	then
		if [[ ${LINE} =~ Schema:\ (DB[0-9]*) ]]
		then
			DB="${BASH_REMATCH[1]}"
		else
			DB=''
		fi
	fi

	if [[ "$DB" == DB* ]]
	then
		if ! [[ ${my_dbs} == *"$DB"* ]]
		then
			my_dbs="${my_dbs} $DB"
			my_array[${DB}JOIN]=0
			my_array[${DB}SELECT]=0
			my_array[${DB}UPDATE]=0
			my_array[${DB}INSERT]=0
			my_array[${DB}DELETE]=0
			my_array[${DB}SET]=0
			my_array[${DB}ALTER]=0
			my_array[${DB}CREATE]=0
			my_array[${DB}DROP]=0
			my_array[${DB}RENAME]=0
			my_array[${DB}TRUNCATE]=0
			my_array[${DB}CALL]=0
			my_array[${DB}DO]=0
			my_array[${DB}HANDLER]=0
			my_array[${DB}LOAD]=0
			my_array[${DB}UNKNOWN]=0
		fi

		LINE="${LINE^^}"

		if [[ ${LINE} =~ ^SELECT\  ]]
		then
			((my_array[${DB}SELECT]++))
			if [[ ${LINE} =~ \ JOIN\  ]]
			then
				((my_array[${DB}JOIN]++))
			fi
		elif [[ ${LINE} =~ ^UPDATE\  ]]
		then
			((my_array[${DB}UPDATE]++))
		elif [[ ${LINE} =~ ^INSERT\  ]]
		then
			((my_array[${DB}INSERT]++))
		elif [[ ${LINE} =~ ^DELETE\  ]]
		then
			((my_array[${DB}DELETE]++))
		elif [[ ${LINE} =~ ^SET\  ]]
		then
			((my_array[${DB}SET]++))
		elif [[ ${LINE} =~ ^ALTER\  ]]
		then
			((my_array[${DB}ALTER]++))
		elif [[ ${LINE} =~ ^CREATE\  ]]
		then
			((my_array[${DB}CREATE]++))
		elif [[ ${LINE} =~ ^DROP\  ]]
		then
			((my_array[${DB}DROP]++))
		elif [[ ${LINE} =~ ^RENAME\  ]]
		then
			((my_array[${DB}RENAME]++))
		elif [[ ${LINE} =~ ^TRUNCATE\  ]]
		then
			((my_array[${DB}TRUNCATE]++))
		elif [[ ${LINE} =~ ^CALL\  ]]
		then
			((my_array[${DB}CALL]++))
		elif [[ ${LINE} =~ ^DO\  ]]
		then
			((my_array[${DB}DO]++))
		elif [[ ${LINE} =~ ^HANDLER\  ]]
		then
			((my_array[${DB}HANDLER]++))
		elif [[ ${LINE} =~ ^LOAD\  ]]
		then
			((my_array[${DB}LOAD]++))
		else
			((my_array[${DB}UNKNOWN]++))
		fi
	fi
done
}

# analyzing logfile
if [ ${PREPROCESSED} ]
then
	preproc < <([[ "${1##*.}" == 'gz' ]] && zcat "$1" || cat "$1")
else
	nopreproc < <(zgrep -Eve '^(# User@Host: |# Query_time: |use DB|SET timestamp=)' "$1" | tail -n +4)
fi

# output stats
for QUERYTYPE in ${!my_array[@]}
do
	echo "${QUERYTYPE} ${my_array[$QUERYTYPE]}" | sed -re 's:(DB[0-9]+):\1 :'
done | sort -nk2 | column -t