#!/bin/sh
# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- #
# Package : reindexdb                 Version : $Revision: 1.3 $
# Date    : 05/08/2002                Author  : Shaun Thomas
# Req     : psql, sh, perl, sed         Type  : Utility
#
# Descripion:
# -=-=-=-=-=-
# Indexes are known to grow over time.  Being as vacuum doesn't slow or
# clean up after this growth, and there is no command to reindex all tables
# in a database, it made sense to construct this utility to do it.
# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- #

# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- #
# Function Definitions
# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- #

function usage()
{
  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               Reindex specific table only"
  echo "  -i, --index=INDEX               Reindex specific index only"
  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
}

# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- #
# Program Body
# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- #

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

# Try valliantly to get the location of psql, since you can't ever
# really know where it has been placed.  We'll start by trying the
# path.  If that fails, we'll try the directory where this script
# resides.  Then on to whereis, and finally locate.  Wish us luck.

if x=`psql -V 2>/dev/null | grep psql`; then
  PSQL='psql'
elif [ -f ${PATHNAME}psql ]; then
  PSQL=${PATHNAME}psql;
elif x=`whereis -b psql 2>/dev/null | sed 's/.* //'`; then
  PSQL=$x
elif x=`locate -r bin/psql$ -n 1 2>/dev/null`; then
  PSQL=$x
else
  echo "$CMDNAME: Could not find psql to talk to postgres installation."
  echo "Please make sure psql is in your path, or that this script is in"
  echo "the same directory as psql was installed."
  exit 1
fi

# Now, go through all of our command-line options and get each operation
# we said we'd accept in the usage listing.

while [ "$#" -gt 0 ]
do
  # Show help.
  case "$1" in
    --help|-\?)
      usage
      exit 0
      ;;

  # All of the following are postgres options.  We can pass them on
  # directly, without interpreting them in any way.  We don't care.
  # Anything that allows a space, we'll get the next *two* arguments
  # and make sure to pass those along.

    --host|-h|-p|--port|-U|--username)
      PSQLOPT="$PSQLOPT $1 $2"
      shift
      ;;
    -h*|--host=*|-p*|--port=*|-U*|--username=*|-W|--password)
      PSQLOPT="$PSQLOPT $1"
      ;;

  # From this point on, we're setting options that are required for
  # or only valid in This script.  This includes which database(s) to
  # reindex, which tables, or which indexes, and so on.

    # Echoing.  We'll *not* use this in queries we use to get lists.
    --echo|-e)
      ECHOOPT="-e"
      ;;

    # Do not echo messages.  We'll direct all output to /dev/null.
    --quiet|-q)
      ECHOOPT="$ECHOOPT -o /dev/null"
      quiet=1
      ;;

    # Reindex all databases, all tables, all applicable indexes.
    --all|-a)
      alldb=1
      ;;

    # Database to connect to, if not all of them.
    --dbname|-d)
      dbname="$2"
      shift
      ;;
    -d*|--dbname=*)
      dbname=`echo $1 | perl -pn -e 's/^--?d(bname=)?//'`
      ;;

    # Reindex specific Table.  Disables index reindexing.
    --table|-t)
      table="$2"
      shift
      ;;
    -t*|--table=*)
      table=`echo $1 | perl -pn -e 's/^--?t(able=)?//'`
      ;;

    # Reindex specific index.  Disables table reindexing.
    --index|-i)
      index="$2"
      shift
      ;;
    -i*|--index=*)
      index=`echo $1 | perl -pn -e 's/^--?i(ndex=)?//'`
      ;;

    # Yeah, no options?  Whine, and show usage.
    -*)
      echo "$CMDNAME: invalid option: $1" 1>&2
      usage;
      exit 1
      ;;

    # Finally, it's possible that the database name was just the last
    # unlabeled option.  So, let's get that.
    *)
      dbname="$1"
      ;;
  esac

  shift # Shift off each argument as we loop.

done

# Get a list of all databases we'll be using.  This first case is if we
# were asked to do all databases.
if [ "$alldb" ]; then

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

  # Execute a command to pull back all databases the user specified can
  # connect to.  That's the list we'll be using.  It's also why it's
  # a good idea for this to be a super-user.
  dbname=`$PSQL $PSQLOPT -q -t -A -d template1 -c 'SELECT datname FROM pg_database WHERE datallowconn'`

# Ok, if it's not all databases, make sure at least one database is
# specified before continuing.
elif [ -z "$dbname" ]; then
  echo "$CMDNAME: missing required argument: database name" 1>&2
  usage;
  exit 1
fi

# No.  We can't reindex a specific index and table at the same time.
# Complain about this, and move on.
if [ "$table" ] && [ "$index" ]; then
  echo "$CMDNAME: cannot reindex a specific table and a specific index" 1>&2
  echo "at the same time." 1>&2
  exit 1
fi

# If index was set, reindex that index.
if [ "$index" ]; then
  $PSQL $PSQLOPT $ECHOOPT -c "REINDEX INDEX $index" -d $dbname

# Ok, no index.  Is there a specific table to reindex?
elif [ "$table" ]; then
  $PSQL $PSQLOPT $ECHOOPT -c "REINDEX TABLE $table" -d $dbname

# No specific table, no specific index, either we have a specific database,
# or were told to do all databases.  Do it!
else

  sql="SELECT distinct tablename FROM pg_indexes WHERE tablename NOT LIKE 'pg_%'"
  for db in $dbname; do

    # Only print which database we're currently reindexing if not in
    # quiet mode, and we're doing more than one database.
    [ "$alldb" ] && [ -z "$quiet" ] && echo "Reindexing $db"

    # Ok, reindex every table in the database.  Use the same method
    # we used to get a list of databases, and get a list of tables in this
    # database that we may reindex.
    tables=`$PSQL $PSQLOPT -q -t -A -d $db -c "$sql"`
    for tab in $tables; do
      $PSQL $PSQLOPT $ECHOOPT -c "REINDEX TABLE $tab" -d $db
    done

  done

fi

# If any of the commands we've executed above failed in any way, bail
# out with an error.
if [ "$?" -ne 0 ]; then
  echo "$CMDNAME: reindex $index $table $dbname failed" 1>&2
  exit 1
fi

exit 0