sqlAutoPatcher.sh

#!/bin/bash

# Configuration of databases
realmdb='realmd'
mangosdb='mangos'
chardb='characters'
sd2db='scriptdev2'

#Configuration of mysql users
mysqluser_mangos='mangos'
mysqluser_char='mangos'
mysqluser_realmd='mangos'
mysqluser_sd2='mangos'

# Configuation of autopath list
path[1]='sql/updates' #; pathDefault[1]='mangos'
path[2]='sql/custom'
path[3]='spell_proc_event/'
path[3]='src/bindings/scriptdev2/sql/updates'
path[4]='src/bindings/scriptdev2/sql/custom'
path[5]="../database/"; pathDefault[5]='mangos'; searchSubPath[5]=true

getUserDBselection()
{
	echo "Parsing Filename failed! Please specify wich database to apply file $file:"
	echo "(m=mangos c=characters s=sd2 r=realm x=skip file)"
	read -n 1 -s input

	if [ "$input" == "m" ]; then
		db=$mangosdb
		pw=$mysqlpw_mangos
        user=$mysqluser_mangos
	elif [ "$input" == "c" ]; then
		db=$chardb
	    pw=$mysqlpw_char
        user=$mysqluser_char
	elif [ "$input" == "s" ]; then
		db=$sd2db
		pw=$mysqlpw_sd2
        user=$mysqluser_sd2
	elif [ "$input" == "r" ]; then
		db=$realmdb
		pw=$mysqlpw_realmd
        user=$mysqluser_realmd
	elif [ "$input" == "x" ]; then
		db="no"
		needCheck=false
	else # invalid selection, try again
		echo "invalid selection"
		getUserDBselection
	fi
}

getUserCheck()
{
	echo "$file will be applied to $db"
	echo "yes(y) / no, and not the next time(n) / skip, and apply next time(s)"
	read -n 1 -s check

	case "$check" in
		yes|YES|Yes|y|Y) do="y";;
		n|N|no|NO|No) do="n";;
		s) do="s";;
		*)
			echo "invalid confirmation"
			getUserCheck
			;;
	esac
}

# $1... name to parse
parseDB()
{
    # parse filename
    if   [[ "${1}" == *_realmd* ]]; then
        db=$realmdb
        pw=$mysqlpw_realmd
        user=$mysqluser_realmd
    elif [[ "${1}" == *_characters* ]]; then
        db=$chardb
        pw=$mysqlpw_char
        user=$mysqluser_char
    elif [[ "${1}" == *_scriptdev2* ]]; then
        db=$sd2db
        pw=$mysqlpw_sd2
        user=$mysqluser_sd2
    elif [[ "${1}" == *_sd2* ]]; then
        db=$sd2db
        pw=$mysqlpw_sd2
        user=$mysqluser_sd2
    elif [[ "${1}" == *_mangos* ]]; then
        db=$mangosdb
        pw=$mysqlpw_mangos
        user=$mysqluser_mangos
    else
        db="no"
    fi
}

# $1 directory, $2 defaultDB
patchFilesInDir()
{
	# set current patch directory
	dir="$1"

	# read timestamp from file
	timestamp="0"
	if [ $applyOldFiles == false ]; then
		if [ ! -f "$dir.lastupdate" ]; then # create file if not exists
			touch "$dir.lastupdate"
		else
			timestamp=$(cat $dir.lastupdate) # read timestamp of last update
		fi
	fi

	if [ -z "$timestamp" ]; then
		timestamp="0"
	fi

	newTimestamp="0"

    # Check if there are *.sqls in the directory
    check=$(ls ${dir}*.sql 2> /dev/null | wc -l)
    if [ "$check" == "0" ]; then
        echo "No *.sql in dir ${dir}. skipping..."
        return
    fi

	# start patching files
	for file in ${dir}*.sql ; do

		currFiledate=$(stat -c %Y $file) #get timestamp of current file
		if [ $applyOldFiles == true ] || [ $currFiledate -gt $timestamp ]; then # apply if checkUpdateTime disabled or file is newer than the timestamp

            # parse DB
            filename=$(basename $file)
            parseDB $filename
            # take default, if parse was not successfull
            if [ "$db" == "no" ] && [ $# -gt 1 ]; then
				parseDB "X_$2.X"
			fi

            # DB found?
            if [ "$db" == "no" ]; then
				getUserDBselection
			fi

            # manual check?
            do="n"
            if [ "$db" != "no" ]; then
			    if [ $needCheck == true ]; then
			        getUserCheck
				else
				    do="y"
			    fi
			fi

            # run the query
			if [ "$do" == "y" ]; then
				mysql "-u" $user "--password="$pw  $db < $file
				if [ $? -eq 0 ]; then
					echo -e "$filename was applied to database $db"
					[ $needCheck == true ] && echo ""

                    if [ $currFiledate -gt $newTimestamp ]; then # check if current file is the latestly modified
						newTimestamp=$currFiledate
					fi
				else
				    # Touch the file, so that it will not be ignored in future script executions,
				    # if queries processed afterwards have a later date and are working.
				    touch $file
					echo -e "$file was not applied to database $db! mysql error"
					[ $needCheck == true ] && echo ""
				fi
			elif [ "$do" == "s" ]; then
			    touch $file
				echo -e "file $file was not applied to database $db! File skipped and will be applied next time.\n"
			else
			    # "no" -> Don't apply, even on future executions of the script.
                if [ $currFiledate -gt $newTimestamp ]; then # check if current file is the latestly modified
						newTimestamp=$currFiledate
				fi
				echo -e "file $file was not applied to database $db!'\n"
			fi
		fi
	done

	if [ $applyOldFiles == false ] && [ $newTimestamp -gt $timestamp ]; then # save last update into file
		echo "$newTimestamp" > "$dir.lastupdate"
	fi
}

#### Get Passwords ####
getPw()
{
    stty -echo

    echo -ne "SQL Passwort mangos-DB                        :"
    read mysqlpw_mangos

    echo -ne "\nSQL Password realmd-DB     (<return> for same):"
    read mysqlpw_realmd
    if [ -d $mysqlpw_realmd ] ; then
        mysqlpw_realmd="$mysqlpw_mangos"
    fi

    echo -ne "\nSQL Password characters-DB (<return> for same):"
    read mysqlpw_char
    if [ -d $mysqlpw_char ] ; then
        mysqlpw_char="$mysqlpw_realmd"
    fi

    echo -ne "\nSQL Password sd2-DB        (<return> for same):"
    read mysqlpw_sd2
    if [ -d $mysqlpw_sd2 ] ; then
        mysqlpw_sd2="$mysqlpw_char"
    fi
    echo ; echo
    stty echo
}

# $1... list of paths
parsePaths()
{
	# parse path
	if [ "$#" -ne "0" ] ; then
		unset path
		unset pathDefault
		unset searchSubPath
		useAutoPath=false

		i=1
		for param in "$@" ; do
			path[i]="$param"
		    i=$(($i+1))
		done
	fi
}

checkPaths()
{
    # check path
	for i in $(seq 1 "${#path[*]}"); do
		# End-Slash überprüfen
		if [ "${path[i]##*/}" != "" ] ; then
			path[i]=${path[i]}/
		fi

		# path überprüfen
		if [ ! -d "${path[i]}" ] ; then
			echo -e "Path \"${path[i]}\" not valid. \nAborting...\n\n"
			exit
		fi
		i=$(($i+1))
	done
}


#### main script ####

useAutoPath=true
needCheck=false
applyOldFiles=false

# parse options
while getopts “hca” OPTION
do
     case $OPTION in
         h)	echo -e "\nThe Orangevirus mangos SQL-AutoPatcher.\n\nUse this script to execute all SQL-queries of the directorys specified by the dir-list or PATH-parameter on mangos/scripdev2 databases."
            echo -e "\nUsage: ./sqlAutopatcher.sh [OPTION] [PATHS]"
            echo -e "\nOptions:"
            echo -e "\t-c\tUser check before executing query."
            echo -e "\t-a\tExecute all Queries (even old ones)."
            echo -e "\t-h\tDisplay this help."
            exit 1;;
         c) needCheck=true;;
         a) applyOldFiles=true;;
         ?) echo -e "Use -h for help."
            exit;;
     esac
done
shift $(($OPTIND - 1))

# get and check paths
parsePaths $@
checkPaths

getPw

# Let's go!
for i in $(seq 1 "${#path[*]}"); do
    # only root dir
    if [ ! ${searchSubPath[i]} ]; then
        patchFilesInDir ${path[i]} ${pathDefault[i]}
    # search subpath
    else
        unset subPath
        subPath=$(find ${path[i]%*/} -type d \( ! -regex '.*/\..*' \))

        for sub in ${subPath[@]}; do
            patchFilesInDir $sub/ ${pathDefault[i]}
        done
    fi
done

echo "Everything done!"