#!/bin/bash
#
# sxpsql-reindex
#
# Re-indexes all SCALIX Postgres tables.
# Designed to be run by cron.
#
# Usage: sxpsql-reindex [-i] {[-h hostname] [-p port] [-u dbuser] | [-f]}
#
# Flags
#
#  -f  instructs the script to get DB connection details from the platform
#      properties file (and create a ".pgpass" file if necessary). Flags -h,
#      -p and -u are ignored if -f is used. 
#      *NOTE* This mode needs root privileges as the platform properties file
#      is protected.
#  -h,
#  -p,
#  -u  provide DB connection details (HOST, PORT and DBUSER) via the command
#	   line (opposite of -f).
#  -i  switches on interactive mode which allows administrators to see the
#	   output of the psql REINDEX session.
#

ME=$(basename $0)
LOG=/tmp/reindex.$$
USAGE="Usage: ${ME} [-i] {[-h hostname] [-p port] [-u dbuser] | [-f]}"

DBUSER=""
DBPASS=""
HOST=""
PORT=""
ACCOUNT="$USER"

INTERACTIVE=FALSE
USE_PROPS_FILE=FALSE

# Main #
#
while getopts h:p:u:if OPTION
do
    case ${OPTION} in
		f) USE_PROPS_FILE=TRUE;;
        h) HOST=${OPTARG};;
        p) PORT=${OPTARG};;
        u) DBUSER=${OPTARG};;
        i) INTERACTIVE=TRUE;;    # for debug only
       \?) echo $USAGE ; exit 1;;
    esac
done

if [ "$USE_PROPS_FILE" = "TRUE" ]; then
	. /opt/scalix-postgres/bin/common-cron.sh
	GETINSTDIR="/opt/scalix-postgres/bin/sxpsql-get-inst-dir"
	PG_LOG=$($GETINSTDIR)/pgactivity.log

	# Get DBUSER, DBPASS, HOST and PORT variables
	#
	STATUS_MSG=$(getDbConnectionDetails)
	if [ $? -ne 0 ]; then
		M="*WARNING* - $0 could not run because it could not determine the
           	database connection details for the Postgres 'scalix' account.
           	The most likely cause is the 'platform.properties' file being
           	deployed on another machine.  Manual reconfiguration is therefore
           	required if you want to run REINDEX commands (recommended)."
       	echo "$M" >> $PG_LOG
       	echo "$M"
       	echo "$STATUS_MSG"   # error message from getDbConnectionDetails()
       	exit 1
	fi

	# OK - set the variables returned by getDbConnectionDetails()
	#
	TMP=/tmp/tmp-vars.$$
	echo "$STATUS_MSG" > $TMP
	. $TMP
	rm -f $TMP

	# Ensure .pgpass file exists
	#
	if [ ! -f ${HOME}/.pgpass ]; then
		STATUS_MSG=$(writePgpassFile $HOST $PORT $DBUSER $DBPASS $ACCOUNT)
		if [ $? -ne 0 ]; then
    		M="*WARNING* - $0 could not run because it could not create a
        		.pgpass file in ~${ACCOUNT}/.
        		Manual reconfiguration is therefore required if you want to run 
        		regular REINDEX commands (recommended)."
    		echo "$M" >> $PG_LOG
    		echo "$M"
    		echo "$STATUS_MSG"   # error message from writePgpassFile()
    		exit 1
		fi
	fi
fi

if [ -z "$HOST" -o -z "$PORT" -o -z "$DBUSER" ]; then
	echo $USAGE
	echo "DB connection details must either be provided as arguments using"
	echo "-h/-p/-u  --OR--  use -f (root only)."
	exit 1
fi

PSQL_OPTS="-h $HOST -p $PORT -U $DBUSER -q -t -A -c"

SQL="select schemaname, tablename from pg_tables 
     where schemaname like 'sx_%' AND hasindexes = 't';"
tablenames=$(psql $PSQL_OPTS "$SQL" 2>$LOG)
if [ $? -ne 0 ]; then
	echo "${ME}: Problems executing SQL command <$SQL>"
	echo "	DB connection details: <$(which psql) $PSQL_OPTS>"
	cat $LOG
	rm -f $LOG
	exit 1
fi

# Join schema/table names with '.'
#
tables=$(for t in $tablenames ; do echo $t | sed 's/|/./'; done)

:> $LOG
for tab in $tables ; do
	echo "[$(date +'%F %T')] REINDEX TABLE $tab" >> $LOG
	psql $PSQL_OPTS "REINDEX TABLE $tab;" 2>>$LOG
	if [ $? -ne 0 ]; then
		echo "${ME}: Problems executing SQL command <REINDEX TABLE $tab;>"
		echo "	DB connection details: <$(which psql) $PSQL_OPTS>"
		cat $LOG
		rm -f $LOG
		exit 1
	fi
done

if [ "$INTERACTIVE" = "TRUE" ]; then
	echo -n "See $LOG ? [y] "; read a
	[ "$a" = "y" -o "$a" = "Y" -o -z "$a" ] && less $LOG
fi

rm -f $LOG

exit 0
