#!/bin/bash

# A set of library functions for working with mysql databases.
# this script has no functionality on its own.

# Locations of MySQL binaries.  These can be specified in the environment.
if [ -z "${MYSQL}" ]; then
	MYSQL="/usr/bin/mysql"
fi
if [ -z "${MYSMYSQLDUMPQL}" ]; then
	MYSQLDUMP="/usr/bin/mysqldump"
fi
if [ -z "${MYMYSQLADMIN}" ]; then
	MYSQLADMIN="/usr/bin/mysqladmin"
fi


# Gets a database authorization string for a database
# Returned in form "-u username --password=password -h host"
# See also getDBName() and getDBConnectionString()
# $1 - the database server hostname; if not supplied, uses "localhost"
# $2 - database username
# $3 - database password
getMySQLAuthString() {
	dbHost="${1}"
	username="${2}"
	password="${3}"

	if [ -z "${dbHost}" ]; then
		dbHost="localhost"
	fi

	echo "--host=${dbHost} --user=${username} --password=${password}"
}

# Gets the full database connection string for a site
# This is the auth string (see above) plus the server name.
# $1 - the database server hostname; if not supplied, uses "localhost"
# $2 - the database name
# $3 - database username
# $4 - database password
getMySQLConnectionString() {
	dbHost="${1}"
	dbName="${2}"
	username="${3}"
	password="${4}"

	authString="`getMySQLAuthString "${dbHost}" "${username}" "${password}"`"

	echo "${authString} --database=${dbName}"
}


# Runs an SQL query on a site database and returns result.
# If multiple columns are selected, the results will have the columns tab separated.
# To process these, the tabs have to be replaced with some other character as they
# won't survive the storage to a variable.  E.g.:
#     # Get results, converting the tab to a pipe ('|') to preserve field bounderies
#     runSQLQuery "connectionString" "select a, b from table;" | 
#       sed 's/\t/|/g' | while read result; do
#         a="`echo "${result}" | cut -d '|' -f 1`"
#         b="`echo "${result}" | cut -d '|' -f 2`"
#         echo "a='${a}' b='${b}'"
#     done
# $1 - connection string
# $2 - the query string; ensure that it is quoted so it remains one parameter
runSQLQuery() {
	connectionString="${1}"
	queryString="${2}"

	${MYSQL} ${connectionString} -B --skip-column-names -e "${queryString}"
}

# Runs an SQL query on a site database and returns result.
# Results return w/ msyql standard format -- with column headers and pipes separating columns.
# $1 - connection string
# $2 - the query string; ensure that it is quoted so it remains one parameter
runSQLQueryPretty() {
	connectionString="${1}"
	queryString="${2}"

	${MYSQL} ${connectionString} -e "${queryString}"
}

# Drops all tables in a database.
# Again, this DROPS ALL TABLES IN A DATABASE.  
# Obviously, don't run this lightly.
# $1 - connection string
dropAllTables() {
	connectionString="${1}"
	
	runSQLQuery "${connectionString}" "SHOW TABLES;" | while read table; do
		runSQLQuery "${connectionString}" "DROP TABLE ${table};"
	done

}

# Runs a connection test against a database using the specified connection string.
# Returns error message (or nothing if connection was successful)
# $1 - the db connection string
testDBConnection() {
	connectionString="${1}"

	test="1234"

	result="`${MYSQL} ${connectionString} -BN -e "select ${test}" 2>/dev/null`"
	if [ "${test}" != "${result}" ]; then
		echo "Unable to connect or run select. (connectionString='${connectionString}')"
	fi

}

# Runs a connection test against a database using the specified connection parameters
# Returns error message (or nothing if connection was successful)
# $1 - the db server hostname
# $2 - the db name
# $3 - the db username
# $4 - the db password
testDBConnectionForParameters() {
	dbHost="${1}"
	dbName="${2}"
	username="${3}"
	password="${4}"

	connectionString="`getMySQLConnectionString "${dbHost}" "${dbName}" "${username}" "${password}"`"

	testDBConnection "${connectionString}"
}