#!/bin/bash

# Performs database queries on drupal installations.
#
# This script defines built in queries and allows for users to type in their own.
# This can be a risky bit of bidness...

# include library file
. "`dirname ${0}`/drupalLibrary.sh"


# Dumps out script usage
printUsage() {
	echo -e " "
	echo -e "Performs database queries on a drupal installation (or all installations in a group)."
	echo -e " "
	echo -e "Usage: "
	echo -e "\t`basename $0` [-pretty] [-quiet] ( <SQL query to execute> | <Named Query> <parameters for query> ) ( <INSTALL> | all ) [install group root dir]"
	echo -e " "
	echo -e "-pretty outputs rows with ascii grid surrounding columns (mysql default)."
	echo -e "-quiet suppresses extra script output."
	echo -e " "
	echo -e "<SQL query to execute> - The query to execute against the selected db(s)."
	echo -e "\tThis can be a single query or multiple, queries need to be suffixed with a semicolon."
	echo -e "\tThe query should be identified as a single parameter by enclosing it in quote marks."
	echo -e " "
	echo -e "<Named Query>"
	echo -e "\tNamed queries provide canned, often-used functionality."
	echo -e "\tThe following are available, each noting what parameters are taken, if any:"
	echo -e "\tclearcache - Clears the cache tables on the selected db(s)."
	echo -e "\tclearsessions - Clears the sessions table on the selected db(s)."
	echo -e "\tdisablemodule <module name> - Disables the module with the supplied name. See 'enablemodule', 'modulestatus'."
	echo -e "\tenablemodule <module name> - Enables the module with the supplied name. See 'disablemodule', 'modulestatus'"
	echo -e "\tfileupload - Sets the the file path to 'sites/<sitename>/files'."
	echo -e "\tfindalias - Finds references to a site's aliases in the node_revision table. Implies '-pretty'"
	echo -e "\tfindsitename - Finds references to a site's name in the node_revision table. Implies '-pretty'"
	echo -e "\tfixcron - Fixes a stuck cron run."
	echo -e "\tfixfilename - Fixes bad filename paths in the system and files tables caused by site aliases."
	echo -e "\tgrepnodes <search> - Searches current node revisions for search term.  Implies '-pretty'"
	echo -e "\tlistmodules - Lists the currently active modules."
	echo -e "\tmodulestatus <module name> - returns the status of a named module. Implies '-quiet'. Returns 0 or 1. See 'disablemodule', 'enablemodule'"
	echo -e "\tresetadmin - Resest the password of teh admin user (uid=1) to the db(s) connection password."
	echo -e "\tsetadmin <username> - Sets the admin username."
	echo -e "\tsetoffline - Sets the site in maintenance mode with a canned message."
	echo -e "\tsetonline - Takes the site out of maintenance mode."
	echo -e "\tsetpassword <username> <password> - Sets the password of the specified user."
	echo -e "\tshowcachetables - Shows all cache* tables in the database. Implies '-quiet'"
	echo -e "\tshowtables - Shows all tables in the database. Implies '-quiet'"
	echo -e "\tswitchnodefiles <nid> <new nid> <pattern> - Moves upload module file attachments from one node to another.  Pattern is filename sql pattern"
	echo -e "\tusers - Lists uid and name of all users in the database.  Results tab separated."
	echo -e "\tvariable <name> <value> - Generic query to set value into variable table.  String serializes given value."
	echo -e "\tvariableraw <name> <raw value> - like 'variable', but does do serialization of value -- stores <raw value> directly."
	echo -e " "
	outputInstallGroupUsage foo
	echo -e " "
}

# Generates a set of queries to delete everything from all cache tables.
# $1 - the install name
# $2 - the install group root dir
generateClearCacheQueries() {
	installName="${1}"
	groupRootDir="${2}"

	# Ensure we have an install name -- if not, just bail
	if [ -z "${installName}" -o -z "${groupRootDir}" ]; then
		return
	fi
	
	dbName="`getDBName "${installName}" "${groupRootDir}"`"
	cacheTablesQuery="SHOW TABLES WHERE Tables_in_${dbName} LIKE 'cache%';"
	
	runSQL "${installName}" "${groupRootDir}" "${cacheTablesQuery}" | (
		while read table; do
			echo -n "TRUNCATE ${table}; "
		done
	)	
}

# Generates the queries necessary to set a row in the 'variable' table.
# Performs necessary 'serialization' of value.  This is the 'string' form of serialization.
# $1 - the variable name
# $2 - the value associated with that name
# $3 - if not empty, does not perform serialization of value
generateVariableSetQueries() {
	name="${1}"
	value="${2}"
	noser="${3}"

	# serialize value? (normal operation)
	if [ -z "${noser}" ]; then
		value="`makeSerialized "${value}"`"
	fi

	query="DELETE FROM variable WHERE name = '${name}';"
	query="${query} INSERT INTO variable (name, value) VALUES ('${name}', '${value}');"

	echo "${query}"
}

# Generates a query to 
# $1... - The items to grep for, will account for all.
generateGrepNodesQuery() {

	if [ ${#} -eq 0 ]; then
		# No parameters supplied.
		whereClause="6 = 9"

	else
		whereClause=""
		while [ -n "${1}" ]; do
			if [ -n "${whereClause}" ]; then
				whereClause="${whereClause} OR "
			fi
			whereClause="${whereClause}nr.body LIKE '%${1}%' OR nr.teaser LIKE '%${1}%'"
			shift
		done
	fi


	echo "SELECT n.nid, n.vid, n.title FROM node n, node_revisions nr WHERE n.nid = nr.nid AND n.vid = nr.vid AND ( ${whereClause} );"
}

# A function to get the information about all named (built in) queries
# $1 - the named query name
# $2 - the operator, one of:
#    exists - returns non-empty string if supplied named query parameter is in fact a named query name, 
#            empty string if it does not.
#    pretty - returns "-pretty" if the '-pretty' parameter should be assumed.
#    quiet - returns "-quiet" if the '-quiet' parameter should be assumed.
#    params - returns an integer number of parameters this query takes.
#    query - generates the query text -- requires next two parameters
# $3 - site name of site for which query is being generated
# $4 - group root dir
# $5... - parameters for query generation
namedQueryInfo() {
	query="${1}"
	op="${2}"
	installName="${3}"
	groupRootDir="${4}"
	shift; shift; shift; shift

	# Turn 'op' operator string into numerical value 'opcode'
	case "${op}" in
	exists )
		opcode=1
		;;
	pretty )
		opcode=2
		;;
	quiet )
		opcode=3
		;;
	params )
		opcode=4
		;;
	query )
		opcode=5
		;;
	* )
		return
	esac

	# generate information into the 'info' variable
	# Note: not everyone has bash v4 installed so we'll used 
	# indexed arrays rather than associative arrays.
	opexists=1
	oppretty=2
	opquiet=3
	opparams=4
	opquery=5

	declare -a info
	info[$opexists]=yup
	info[$opparams]=0

	# We check for the query generation as some queries generate errors if the install name/root dir are not supplied.
	case "${query}" in
	clearcache )
		if [ ${op} == "query" ]; then
			info[$opquery]="`generateClearCacheQueries "${installName}" "${groupRootDir}"`"
		fi
		;;

	clearsessions )
		if [ ${op} == "query" ]; then
			info[$opquery]="DELETE FROM sessions;"
		fi
		;;

	disablemodule )
		info[$opparams]=1
		if [ ${op} == "query" ]; then
			moduleName="${1}"
			info[$opquery]="UPDATE system SET status = 0 WHERE type = 'module' AND name = '${moduleName}';"
		fi
		;;

	enablemodule )
		info[$opparams]=1
		if [ ${op} == "query" ]; then
			moduleName="${1}"
			info[$opquery]="UPDATE system SET status = 1 WHERE type = 'module' AND name = '${moduleName}';"
		fi
		;;

	fileupload )
		if [ ${op} == "query" ]; then
			filePath="`getFileUploadDir "${installName}" "${groupRootDir}" foo`"
			info[$opquery]="`generateVariableSetQueries "file_directory_path" "${filePath}"`"
		fi
		;;

	findalias )
		info[$oppretty]="-pretty"
		if [ ${op} == "query" ]; then
			aliasList="`getInstallAliases "${installName}" "${groupRootDir}"`"
			info[$opquery]="`generateGrepNodesQuery ${aliasList}`"
		fi
		;;

	findsitename )
		info[$oppretty]="-pretty"
		if [ ${op} == "query" ]; then
			info[$opquery]="`generateGrepNodesQuery "${installName}"`"
		fi
		;;

	fixcron )
		if [ ${op} == "query" ]; then
			info[$opquery]="DELETE FROM variable WHERE name IN ('cron_last', 'cron_semaphore');"
		fi
		;;

	fixfilename )
		if [ ${op} == "query" ]; then
			siteDir="`getSiteDir "${installName}" "${groupRootDir}" true`"
			info[$opquery]="`getInstallAliases "${installName}" "${groupRootDir}" | while read alias; do
				aliasedSiteDir=${siteDir/${installName}/${alias}}
				echo -n "UPDATE system SET filename = replace(filename, '${aliasedSiteDir}/', '${siteDir}/');"
				echo -n "UPDATE files SET filepath = replace(filepath, '${aliasedSiteDir}/', '${siteDir}/');"
			done`"
		fi
		;;

	grepnodes )
		info[$oppretty]="-pretty"
		info[$opparams]=1
		if [ ${op} == "query" ]; then
			searchTerm="${1}"
			info[$opquery]="`generateGrepNodesQuery "${searchTerm}"`"
		fi
		;;

	listmodules )
		info[$opquiet]="-quiet"
		if [ ${op} == "query" ]; then
			info[$opquery]="SELECT name FROM system WHERE status = 1 AND type = 'module' ORDER BY name;"
		fi
		;;

	modulestatus )
		info[$opquiet]="-quiet"
		info[$opparams]=1
		if [ ${op} == "query" ]; then
			moduleName="${1}"
			info[$opquery]="SELECT status FROM system WHERE type = 'module' AND name = '${moduleName}';"
		fi
		;;

	resetadmin )
		if [ ${op} == "query" ]; then
			dbPassword="`getDBPassword "${installName}" "${groupRootDir}"`"

			# Drupal 6 and older uses a standard MD5 hash. Drupal 7+ uses a salted password. 
			majorVer="`getSiteDrupalMajorVersion "${installName}" "${groupRootDir}"`"
			if [ ${majorVer} -lt 7 ]; then
				info[$opquery]="UPDATE users SET pass = md5('${dbPassword}') WHERE uid = 1;"
			else 
				passwordHash="`generateDrupal7Password "${dbPassword}" "${installName}" "${groupRootDir}"`"
				info[$opquery]="UPDATE users SET pass = '${passwordHash}' WHERE uid = 1;"
			fi		
		fi
		;;

	setadmin )
		info[$opparams]=1
		if [ ${op} == "query" ]; then
			username="${1}"
			info[$opquery]="UPDATE users SET name = '${username}' WHERE uid = 1;"
		fi
		;;

	setoffline )
		if [ ${op} == "query" ]; then
			siteOfflineMsg="${installName} is currently under maintenance. We should be back shortly. Thank you for your patience."
			info[$opquery]="`generateVariableSetQueries "site_offline" "1"`"
			info[$opquery]="${info[$opquery]} `generateVariableSetQueries "site_offline_message" "${siteOfflineMsg}"`"
			info[$opquery]="${info[$opquery]} `generateClearCacheQueries "${installName}" "${groupRootDir}"`"
		fi
		;;

	setonline )
		if [ ${op} == "query" ]; then
			info[$opquery]="DELETE FROM variable WHERE name IN ('site_offline', 'site_offline_message');"
			info[$opquery]="${info[$opquery]} `generateClearCacheQueries "${installName}" "${groupRootDir}"`"
		fi
		;;

	setpassword )
		info[$opparams]=2
		if [ ${op} == "query" ]; then
			username="${1}"
			password="${2}"
			info[$opquery]="UPDATE users SET pass = md5('${password}') WHERE name = '${username}';"
		fi
		;;

	showcachetables )
		info[$opquiet]="-quiet"
		if [ ${op} == "query" ]; then
			tableName="`getDBName "${installName}" "${groupRootDir}"`"
			info[$opquery]="SHOW TABLES WHERE Tables_in_${tableName} LIKE 'cache%';"
		fi
		;;

	showtables )
		info[$opquiet]="-quiet"
		if [ ${op} == "query" ]; then
			info[$opquery]="SHOW TABLES;"
		fi
		;;

	switchnodefiles )
		info[$opparams]=3
		if [ ${op} == "query" ]; then
			nid="${1}"
			newNid="${2}"
			pattern="${3}"
 
 			info[$opquery]="UPDATE upload u INNER JOIN files f ON u.fid = f.fid SET u.nid = ${newNid}, vid = (SELECT vid FROM node WHERE nid = ${newNid}) WHERE u.nid = ${nid} AND f.filename LIKE '${pattern}';"
		fi
		;;

	users )
		if [ ${op} == "query" ]; then
			info[$opquery]="SELECT uid, name FROM users WHERE uid >= 1;"
		fi
		;;

	variable )
		info[$opparams]=2
		if [ ${op} == "query" ]; then
			name="${1}"
			value="${2}"
			info[$opquery]="`generateVariableSetQueries "${name}" "${value}"`"
		fi
		;;

	variableraw )
		info[$opparams]=2
		if [ ${op} == "query" ]; then
			name="${1}"
			value="${2}"
			info[$opquery]="`generateVariableSetQueries "${name}" "${value}" foo`"
		fi
		;;

	* )
		info[$opexists]=""

	esac

	# return
	echo "${info[$opcode]}"

}

#########

# Param handling
if [ $# -eq 0 ]; then
	printUsage
	exit 0
fi

# Handle flags
while (true); do
	case "${1}" in
	-pretty )
		pPretty="-pretty"
		shift
		;;
	-quiet )
		pQuiet="-quiet"
		shift
		;;
	* )
		break
		;;
	esac
done

# handle query parameter
if [ -n "`namedQueryInfo ${1} exists`" ]; then
	pNamedQuery="${1}"
	shift
	if [ -z "${pPretty}" ]; then
		pPretty="`namedQueryInfo ${pNamedQuery} pretty`"
	fi
	if [ -z "${pQuiet}" ]; then
		pQuiet="`namedQueryInfo ${pNamedQuery} quiet`"
	fi
	numParams="`namedQueryInfo ${pNamedQuery} params`"
	x=0
	while [ $x -lt $numParams ]; do
		pParams[$x]="${1}"
		let x=$x+1
		shift
	done
else
	pQuery="${1}"
	shift
fi 
if [ -z "${pQuiet}" ]; then
	echo " "
fi

# process Group Root Dir
pRootDir="`getGroupRootDir "${2}"`"
errorStr="`validateGroupRootDir "${pRootDir}"`"
if [ -n "${errorStr}" ]; then
	echo -e "${errorStr}\n"
	printUsage
	exit 1
fi

pInstall="${1}"
if [ "`toLower "${pInstall}"`" == "all" ]; then
	pInstall="`getSitesInGroupDir "${pRootDir}"`"

else
	# Validate the installation
	errorStr="`validateSite "${pInstall}" "${pRootDir}"`"
	if [ -n "${errorStr}" ]; then
		echo -e "** Error with specified install: ${errorStr}.  Aborting.\n";
		exit 1
	fi
fi

##########################
# Startup

for site in ${pInstall}; do
	if [ -z "${pQuiet}" ]; then
		if [ -n "${pNamedQuery}" ]; then
			echo "Running query '${pNamedQuery}' for site '${site}'"
		else
			echo "Running query for site '${site}'"
		fi
	fi

	# Get named query
	if [ -n "${pNamedQuery}" ]; then
		pQuery="`namedQueryInfo ${pNamedQuery} query "${site}" "${pRootDir}" ${pParams[*]}`"
	fi
	
	if [ -z "${pQuery}" ]; then
		echo -e "** Named Query '${pNamedQuery}' could not be generated.  Aborting.\n";
		exit 1
	fi

	#echo "[debug] Query: ${pQuery}"; continue
	if [ -n "${pPretty}" ]; then
		runSQLPretty "${site}" "${pRootDir}" "${pQuery}"
	else 
		runSQL "${site}" "${pRootDir}" "${pQuery}"
	fi
done

if [ -z "${pQuiet}" ]; then
	echo -e "\n** Done."
fi