#!/bin/sh
#-------------------------------------------------------------------------
#
# reindexdb--
#    reindex a postgres database
#
#    This script runs psql with the "-c" option to reindex
#    the requested database.
#
# Portions Copyright (c) 2002, Townnews.com
# Portions Copyright (c) 1994, Regents of the University of California
#
#-------------------------------------------------------------------------

CMDNAME=`basename "$0"`
PATHNAME=`echo $0 | sed "s,$CMDNAME\$,,"`

PSQLOPT=
verbose=
table=
index=
dbname=
alldb=
quiet=0

while [ "$#" -gt 0 ]
do
  case "$1" in
    --help|-\?)
      usage=t
      break
      ;;

  --host|-h)
    PSQLOPT="$PSQLOPT -h $2"
    shift
    ;;

  -h*)
    PSQLOPT="$PSQLOPT $1"
    ;;
  --host=*)
    PSQLOPT="$PSQLOPT -h "`echo $1 | sed 's/^--host=//'`
    ;;

  --port|-p)
    PSQLOPT="$PSQLOPT -p $2"
    shift
    ;;
  -p*)
    PSQLOPT="$PSQLOPT $1"
    ;;
  --port=*)
    PSQLOPT="$PSQLOPT -p "`echo $1 | sed 's/^--port=//'`
    ;;

  --username|-U)
    PSQLOPT="$PSQLOPT -U $2"
    shift
    ;;
  -U*)
    PSQLOPT="$PSQLOPT $1"
    ;;
  --username=*)
    PSQLOPT="$PSQLOPT -U "`echo $1 | sed 's/^--username=//'`
    ;;

  --password|-W)
    PSQLOPT="$PSQLOPT -W"
    ;;

  --echo|-e)
    ECHOOPT="-e"
    ;;

  --quiet|-q)
    ECHOOPT="$ECHOOPT -o /dev/null"
    quiet=1
    ;;

  --dbname|-d)
    dbname="$2"
    shift;;
  -d*)
    dbname=`echo $1 | sed 's/^-d//'`
    ;;
  --dbname=*)
    dbname=`echo $1 | sed 's/^--dbname=//'`
    ;;

  --all|-a)
    alldb=Y
    ;;

  --table|-t)
    table="$2"
    shift
    ;;
  -t*)
    table=`echo $1 | sed 's/^-t//'`
    ;;
  --table=*)
    table=`echo $1 | sed 's/^--table=//'`
    ;;

  --index|-i)
    index="$2"
    shift
    ;;
  -i*)
    index=`echo $1 | sed 's/^-i//'`
    ;;
  --index=*)
    index=`echo $1 | sed 's/^--index=//'`
    ;;

  --verbose|-v)
    verbose="VERBOSE"
    ;;

  -*)
    echo "$CMDNAME: invalid option: $1" 1>&2
    echo "Try '$CMDNAME --help' for more information." 1>&2
    exit 1
    ;;

  *)
    dbname="$1"
    ;;
  esac
  shift
done

if [ "$usage" ]; then	
  echo "$CMDNAME reindexes a PostgreSQL database."
  echo
  echo "Usage:"
  echo "  $CMDNAME [options] [dbname]"
  echo
  echo "Options:"
  echo "  -h, --host=HOSTNAME             Database server host"
  echo "  -p, --port=PORT                 Database server port"
  echo "  -U, --username=USERNAME         Username to connect as"
  echo "  -W, --password                  Prompt for password"
  echo "  -d, --dbname=DBNAME             Database to reindex"
  echo "  -a, --all                       Reindex all databases"
  echo "  -t, --table='TABLE[(columns)]'  Reindex specific table only"
  echo "  -i, --index='INDEX'             Reindex specific index only"
  echo "  -v, --verbose                   Write a lot of output"
  echo "  -e, --echo                      Show the command being sent to the backend"
  echo "  -q, --quiet                     Don't write any output"
  echo
  echo "Read the description of the SQL command REINDEX for details."
  echo
  exit 0
fi

if [ "$alldb" ]; then
  if [ "$dbname" -o "$table" ]; then
    echo "$CMDNAME: cannot reindex all databases and a specific one at the same time" 1>&2
    exit 1
  fi
  dbname=`${PATHNAME}psql $PSQLOPT -q -t -A -d template1 -c 'SELECT datname FROM pg_database WHERE datallowconn'`

elif [ -z "$dbname" ]; then
  echo "$CMDNAME: missing required argument: database name" 1>&2
  echo "Try '$CMDNAME -?' for help." 1>&2
  exit 1
fi

if [ "$table" ] && [ "$index" ]; then
  echo "$CMDNAME: cannot reindex a specific table and a specific index at the same time." 1>&2
  exit 1
fi

for db in $dbname; do
  [ "$alldb" -a "$quiet" -ne 1 ] && echo "Reindexing $db"

  if [ "$index" ]; then
    ${PATHNAME}psql $PSQLOPT $ECHOOPT -c "REINDEX INDEX $index" -d $db
  elif [ "$table" ]; then
    ${PATHNAME}psql $PSQLOPT $ECHOOPT -c "REINDEX TABLE $table" -d $db
  else
    tables=`${PATHNAME}psql $PSQLOPT -q -t -A -d $db -c "SELECT distinct tablename FROM pg_indexes WHERE tablename NOT LIKE 'pg_%'"`
    for tab in $tables; do
     ${PATHNAME}psql $PSQLOPT $ECHOOPT -c "REINDEX TABLE $tab" -d $db
    done
  fi

  if [ "$?" -ne 0 ]; then
    echo "$CMDNAME: reindex $table $db failed" 1>&2
    exit 1
  fi
done

exit 0