#!/bin/bash
#
###############################################################################
#
# NOTE: You'd prefer to use the wrapper mysql_count_querytype_multicore.sh
# instead of using directly this script.
#
###############################################################################
#
# 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 they're also used to set
# timestamp for every request in our logs.
# Also, JOIN queries are counted, but all counted JOIN queries are also counted
# in SELECT ones (because basically they're part of a SELECT, not queries).
#
# If there is a DB name but the query doesn't match any type listed above, then
# it's counted an UNKNOWN.
#
###############################################################################
#
# As as last note, please consider preprocessing your files with 
# mysql_convert_slowlog_to_oneline.sh as it makes processing single line input
# much faster than multiline input.
#
###############################################################################

errcho () {
	>&2 echo "$@"
}

usage () {
	errcho "Usage: ${0##*/} [-p] logfile"
	errcho '-p    if logfile has been preprocessed by mysql_convert_slowlog_to_oneline.sh'
	errcho '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=''

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

	if [[ "$DB" == DB* ]]
	then
		LINE="${LINE^^}"

		if [[ ${LINE} == *" SELECT "* ]]
		then
			if [ ${my_array[${DB}SELECT]+_} ]
			then
				((my_array[${DB}SELECT]++))
			else
				my_array[${DB}SELECT]=1
			fi
			if [[ ${LINE} == *" JOIN "* ]]
			then
				if [ ${my_array[${DB}JOIN]+_} ]
				then
					((my_array[${DB}JOIN]++))
				else
					my_array[${DB}JOIN]=1
				fi
			fi
		elif [[ ${LINE} == *" UPDATE "* ]]
		then
			if [ ${my_array[${DB}UPDATE]+_} ]
			then
				((my_array[${DB}UPDATE]++))
			else
				my_array[${DB}UPDATE]=1
			fi
		elif [[ ${LINE} == *" INSERT "* ]]
		then
			if [ ${my_array[${DB}INSERT]+_} ]
			then
				((my_array[${DB}INSERT]++))
			else
				my_array[${DB}INSERT]=1
			fi
		elif [[ ${LINE} == *" DELETE "* ]]
		then
			if [ ${my_array[${DB}DELETE]+_} ]
			then
				((my_array[${DB}DELETE]++))
			else
				my_array[${DB}DELETE]=1
			fi
		elif [[ ${LINE} == *" SET "* ]]
		then
			if [ ${my_array[${DB}SET]+_} ]
			then
				((my_array[${DB}SET]++))
			else
				my_array[${DB}SET]=1
			fi
		elif [[ ${LINE} == *" ALTER "* ]]
		then
			if [ ${my_array[${DB}ALTER]+_} ]
			then
				((my_array[${DB}ALTER]++))
			else
				my_array[${DB}ALTER]=1
			fi
		elif [[ ${LINE} == *" CREATE "* ]]
		then
			if [ ${my_array[${DB}CREATE]+_} ]
			then
				((my_array[${DB}CREATE]++))
			else
				my_array[${DB}CREATE]=1
			fi
		elif [[ ${LINE} == *" DROP "* ]]
		then
			if [ ${my_array[${DB}DROP]+_} ]
			then
				((my_array[${DB}DROP]++))
			else
				my_array[${DB}DROP]=1
			fi
		elif [[ ${LINE} == *" RENAME "* ]]
		then
			if [ ${my_array[${DB}RENAME]+_} ]
			then
				((my_array[${DB}RENAME]++))
			else
				my_array[${DB}RENAME]=1
			fi
		elif [[ ${LINE} == *" TRUNCATE "* ]]
		then
			if [ ${my_array[${DB}TRUNCATE]+_} ]
			then
				((my_array[${DB}TRUNCATE]++))
			else
				my_array[${DB}TRUNCATE]=1
			fi
		elif [[ ${LINE} == *" CALL "* ]]
		then
			if [ ${my_array[${DB}CALL]+_} ]
			then
				((my_array[${DB}CALL]++))
			else
				my_array[${DB}CALL]=1
			fi
		elif [[ ${LINE} == *" DO "* ]]
		then
			if [ ${my_array[${DB}DO]+_} ]
			then
				((my_array[${DB}DO]++))
			else
				my_array[${DB}DO]=1
			fi
		elif [[ ${LINE} == *" HANDLER "* ]]
		then
			if [ ${my_array[${DB}HANDLER]+_} ]
			then
				((my_array[${DB}HANDLER]++))
			else
				my_array[${DB}HANDLER]=1
			fi
		elif [[ ${LINE} == *" LOAD "* ]]
		then
			if [ ${my_array[${DB}LOAD]+_} ]
			then
				((my_array[${DB}LOAD]++))
			else
				my_array[${DB}LOAD]=1
			fi
		else
			if [ ${my_array[${DB}UNKNOWN]+_} ]
			then
				((my_array[${DB}UNKNOWN]++))
			else
				my_array[${DB}UNKNOWN]=1
			fi
		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
		LINE="${LINE^^}"
		if [[ ${LINE} =~ ^SELECT\  ]]
		then
			if [ ${my_array[${DB}SELECT]+_} ]
			then
				((my_array[${DB}SELECT]++))
			else
				my_array[${DB}SELECT]=1
			fi
			if [[ ${LINE} =~ \ JOIN\  ]]
			then
				if [ ${my_array[${DB}JOIN]+_} ]
				then
					((my_array[${DB}JOIN]++))
				else
					my_array[${DB}JOIN]=1
				fi
			fi
		elif [[ ${LINE} =~ ^UPDATE\  ]]
		then
			if [ ${my_array[${DB}UPDATE]+_} ]
			then
				((my_array[${DB}UPDATE]++))
			else
				my_array[${DB}UPDATE]=1
			fi
		elif [[ ${LINE} =~ ^INSERT\  ]]
		then
			if [ ${my_array[${DB}INSERT]+_} ]
			then
				((my_array[${DB}INSERT]++))
			else
				my_array[${DB}INSERT]=1
			fi
		elif [[ ${LINE} =~ ^DELETE\  ]]
		then
			if [ ${my_array[${DB}DELETE]+_} ]
			then
				((my_array[${DB}DELETE]++))
			else
				my_array[${DB}DELETE]=1
			fi
		elif [[ ${LINE} =~ ^SET\  ]]
		then
			if [ ${my_array[${DB}SET]+_} ]
			then
				((my_array[${DB}SET]++))
			else
				my_array[${DB}SET]=1
			fi
		elif [[ ${LINE} =~ ^ALTER\  ]]
		then
			if [ ${my_array[${DB}ALTER]+_} ]
			then
				((my_array[${DB}ALTER]++))
			else
				my_array[${DB}ALTER]=1
			fi
		elif [[ ${LINE} =~ ^CREATE\  ]]
		then
			if [ ${my_array[${DB}CREATE]+_} ]
			then
				((my_array[${DB}CREATE]++))
			else
				my_array[${DB}CREATE]=1
			fi
		elif [[ ${LINE} =~ ^DROP\  ]]
		then
			if [ ${my_array[${DB}DROP]+_} ]
			then
				((my_array[${DB}DROP]++))
			else
				my_array[${DB}DROP]=1
			fi
		elif [[ ${LINE} =~ ^RENAME\  ]]
		then
			if [ ${my_array[${DB}RENAME]+_} ]
			then
				((my_array[${DB}RENAME]++))
			else
				my_array[${DB}RENAME]=1
			fi
		elif [[ ${LINE} =~ ^TRUNCATE\  ]]
		then
			if [ ${my_array[${DB}TRUNCATE]+_} ]
			then
				((my_array[${DB}TRUNCATE]++))
			else
				my_array[${DB}TRUNCATE]=1
			fi
		elif [[ ${LINE} =~ ^CALL\  ]]
		then
			if [ ${my_array[${DB}CALL]+_} ]
			then
				((my_array[${DB}CALL]++))
			else
				my_array[${DB}CALL]=1
			fi
		elif [[ ${LINE} =~ ^DO\  ]]
		then
			if [ ${my_array[${DB}DO]+_} ]
			then
				((my_array[${DB}DO]++))
			else
				my_array[${DB}DO]=1
			fi
		elif [[ ${LINE} =~ ^HANDLER\  ]]
		then
			if [ ${my_array[${DB}HANDLER]+_} ]
			then
				((my_array[${DB}HANDLER]++))
			else
				my_array[${DB}HANDLER]=1
			fi
		elif [[ ${LINE} =~ ^LOAD\  ]]
		then
			if [ ${my_array[${DB}LOAD]+_} ]
			then
				((my_array[${DB}LOAD]++))
			else
				my_array[${DB}LOAD]=1
			fi
		else
			if [ ${my_array[${DB}UNKNOWN]+_} ]
			then
				((my_array[${DB}UNKNOWN]++))
			else
				my_array[${DB}UNKNOWN]=1
			fi
		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 -nk3 | column -t

