#!/bin/sh
# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- #
# Package : reindexdb                 Version : $Revision: 1.2 $
# Date    : 05/07/2002                Author  : Shaun Thomas
# Req     : psql, sh                    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
# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- #

# Get the full name of this program.  Also get the pathname, we'll use it
# later to get to psql.

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

# 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=`${PATHNAME}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
  ${PATHNAME}psql $PSQLOPT $ECHOOPT -c "REINDEX INDEX $index" -d $dbname

# Ok, no index.  Is there a specific table to reindex?
elif [ "$table" ]; then
  ${PATHNAME}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=`${PATHNAME}psql $PSQLOPT -q -t -A -d $db -c "$sql"`
    for tab in $tables; do
      ${PATHNAME}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 $db failed" 1>&2
  exit 1
fi

exit 0