# $Id$
# mysql functions for bash

mysql_command()
{
	
	local SQL_USER
	local SQL_PASS
	local SQL_HOST
	local SQL_DB
	local SQL_COMMAND

	SQL_USER="$1"
	SQL_PASS="$2"
	SQL_HOST="$3"
	SQL_DB="$4"
	SQL_COMMAND="$5"

	# fallback (SQL_OPTS not, they are optional )
	if [[ -z ${SQL_USER} ]] ||
		[[ -z ${SQL_PASS} ]] ||
		[[ -z ${SQL_HOST} ]] ||
		[[ -z ${SQL_DB} ]] ||
		[[ -z ${SQL_COMMAND} ]]
	then
		echo
		echo "Usage:"
		echo -e "      mysql_command user pass host db \"sql-command\""
		echo
		return 1
	fi

	mysql \
		--user="${SQL_USER}" \
		--password="${SQL_PASS}" \
		--host="${SQL_HOST}" \
		--database="${SQL_DB}" \
		--batch \
		--skip-column-names \
		--execute="${SQL_COMMAND}" \
		|| return 1

	return 0
}

mysql_enum_colums()
{
	local SQL_USER
	local SQL_PASS
	local SQL_HOST
	local SQL_DB
	local SQL_TABLE

	local SHOWTABLES
	local i
	local read
	local field
	local type
	local null
	local key
	local default
	local extra

	SQL_USER=$1
	SQL_PASS=$2
	SQL_HOST=$3
	SQL_DB=$4
	SQL_TABLE=$5

	# show the column names ?
	if [ -n "${6}" -a "${6}" == "show" ] 
	then
		SHOWTABLES=true
	else
		 SHOWTABLES=false
	fi

	# fallback (SQL_OPTS not, they are optional )
	if [[ -z ${SQL_USER} ]] ||
		[[ -z ${SQL_PASS} ]] ||
		[[ -z ${SQL_HOST} ]] ||
		[[ -z ${SQL_DB} ]] ||
		[[ -z ${SQL_TABLE} ]]
	then
		echo
		echo "Usage:"
		echo -e "      mysql_enum_columns user pass host db table [show]"
		echo
		return 1
	fi

	declare -i i=0
	while read field type null key default extra
	do
		i=${i}+1
		[ "${SHOWTABLES}" == true ] && echo "${field}"
	done << EOF
$(mysql_command ${SQL_USER} ${SQL_PASS} ${SQL_HOST} ${SQL_DB} "show columns from ${SQL_TABLE};")
EOF
	[ "${SHOWTABLES}" == true ] || echo "${i}"
}

beep3x()
{
	echo -en "\a"
	sleep 0.7
	echo -en "\a"
	sleep 0.7
	echo -en "\a"
}

reach_mysql_server()
{
	# set connection timeout to 20 secs
	mysql --connect_timeout=20 \
		--user="${SQL_USER}" \
		--password="${SQL_PASS}" \
		--host="${SQL_HOST}" \
		--database="${SQL_DB}" \
		--batch \
		--execute="" &> /dev/null && return 0 || \
		(
			echo -e "${COLRED}"
			echo "Cannot reach a mysql-server at '${SQL_HOST}'."
			echo "None of the current settings of this client will be touched."
			echo "Please inform your system-administrator."
			echo -e "${COLDEFAULT}"
			beep3x
			sleep 5
			return 1
		)
}

mysqldo()
{
	mysql_command \
		"${SQL_USER}" \
		"${SQL_PASS}" \
		"${SQL_HOST}" \
		"${SQL_DB}" \
		"$@" \
		&& return 0 || return 1
}

# read tables and evaluate all variables
# cmd:    evaluate_table cfg_network
# result: cfg_network_hostname=DUMMY_HOSTNAME
evaluate_table()
{
	local table="$1"
	local where_statement

	if [[ -z $2 ]]
	then
		where_statement="where serial='${ALX_SERIAL}'"
	else
		where_statement="$2"
	fi

	eval $(mysql \
		--user="${SQL_USER}" \
		--password="${SQL_PASS}" \
		--host="${SQL_HOST}" \
		--database="${SQL_DB}" \
		--xml \
		--execute="select * from ${table} ${where_statement}" \
		| xml sel -T -t -m //row/field \
			-v "concat('${table}_', @name, '=', '\"', self::field, '\"')" -n)
}

# runs a statement and evaluate the resulting columns
# cmd:    evaluate_command "select serial from cfg_serial where serial=10"
# result: serial=10
# or with a prefix given
# cmd:    evaluate_command "select serial from cfg_serial where serial=10" "cfg_serial"
# result: cfg_serial_serial=10
evaluate_statement()
{
	local statement="$1"
	local prefix="$2"

	if [[ ! -z ${prefix} ]]
	then
		prefix="${prefix}_"
	fi

	eval $(mysql \
		--user="${SQL_USER}" \
		--password="${SQL_PASS}" \
		--host="${SQL_HOST}" \
		--database="${SQL_DB}" \
		--xml \
		--execute="${statement}" \
		| xml sel -T -t -m //row/field \
			-v "concat('${prefix}', @name, '=', '\"', self::field, '\"')" -n)
}