#! /bin/bash
# update_prepsdb -- Update a PRepS database
# Copyright (c) 2001 Kenneth W. Sodemann (stufflehead@bigfoot.com)
#
# $Id: update_prepsdb.in,v 1.21 2001/08/26 18:45:40 stuffle Exp $
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to 
# Free Software Foundation, Inc. 
# 59 Temple Place, Suite 330 
# Boston, MA  02111-1307  USA
#

##
## Initialize some of the variable we will be using.
DB_NAME=""
PG_PATH=""
PSQL="psql"
DROPDB="dropdb"
CREATEDB="createdb"
PG_DUMP="pg_dump"
START_VER=""
TARG_VER=
PLPGSQL_PATH=
QUIET="No"
DB_OWNER=""
POSTGRES_SU=`whoami`
SHOW_SCHEMA="No"
OUTPUT_DIR=""

#-------------------------------------------------------------------------
# check_output_dir
#
# Check that the output dir was set, either by the set_output_dir()
# function or by the user via an option.  Check that the user can
# write to that directory.
#-------------------------------------------------------------------------
check_output_dir() {
  if [ -z "$OUTPUT_DIR" ]
  then
    echo -e "\a\nFailed to find a writable output directory."
    echo    "Please use -o to specify a writable diretory."
    echo -e "Aborting...\n"
    exit 1
  fi

  if [ ! -d "$OUTPUT_DIR" ]
  then
    echo -e "\a\nThere is no directory named $OUTPUT_DIR"
    echo    "Please use -o to specify a writable diretory."
    echo -e "Aborting...\n"
    exit 1
  fi

  if [ ! -w "$OUTPUT_DIR" ]
  then
    echo -e "\a\nYou cannot write to $OUTPUT_DIR"
    echo    "Please use -o to specify a writable diretory."
    echo -e "Aborting...\n"
    exit 1
  fi

  return 0
}

#-------------------------------------------------------------------------
# set_output_dir
#
# If an output directory was not specified by the user, attempt to 
# find one that will work.
#
# Start with the user's home directory, then try tmp, then try the
# current directory.
#-------------------------------------------------------------------------
set_output_dir() {
  if [ -n "$HOME" -a -d "$HOME" -a -w "$HOME" ]
  then
    OUTPUT_DIR="$HOME"
    return 0
  fi

  if [ -d "/tmp" -a -w "/tmp" ]
  then
    OUTPUT_DIR="/tmp"
    return 0
  fi

  PWD=`pwd`
  if [ -w "$PWD" ]
  then
    OUTPUT_DIR="$PWD"
    return 0
  fi

  ## we failed....
  return 1
}  

#-------------------------------------------------------------------------
# check_user()
#
# See if the user is 'postgres'.  If not, ask them if they are a 
# PostgreSQL superuser.
#------------------------------------------------------------------------- 
check_user() {
  if [ "$POSTGRES_SU" = "postgres" ]
  then
    return 0;
  fi

  echo -e "\nThis script MUST be run by the PostgreSQL super user.  You are currently"
  echo "logged in as $POSTGRES_SU.  Is this the PostgreSQL superuser on your system?"
  echo -e "(yes/no)> \c"

  while true
  do
    read X
    case "$X" in
      y | Y | yes | Yes | YES )
        return 0;;
      n | N | no | No | NO )
        return 1;;
      *) echo "Please enter yes or no";;
    esac
  done
}


#-------------------------------------------------------------------------
# clean_up()
#
# delete any working files.
#-------------------------------------------------------------------------
clean_up() {
  if [ -f $OUTPUT_DIR/schema.sql ]
  then
    rm $OUTPUT_DIR/schema.sql
  fi
  
  if [ -f $OUTPUT_DIR/$DB_NAME.data.in ]
  then
    rm $OUTPUT_DIR/$DB_NAME.data.in
  fi

  if [ -f $OUTPUT_DIR/$DB_NAME.data ]
  then
    rm $OUTPUT_DIR/$DB_NAME.data
  fi
}
  
#-------------------------------------------------------------------------
# show_version()
#
# Show the version and copyright information.
#-------------------------------------------------------------------------
show_version() {
  echo -e "\nupdate_prepsdb version 2.1"
  echo -e "Copyright (c) 2000-2001 Kenneth W. Sodemann (stufflehead@bigfoot.com)\n"
  return 0 
}

#-------------------------------------------------------------------------
# show_final_msg()
#
# Show the final message explaining what has happened.
#-------------------------------------------------------------------------
show_final_msg() {
  echo -e "\n$DB_NAME has been updated to the newest schema version and all of the data"
  echo "has been restored.  No fatal errors have been detected."
  echo -e "\nReview the $OUTPUT_DIR/$DB_NAME.log file for details."
  return 0
}

#-------------------------------------------------------------------------
# show_restore()
#
# Show a warning about the state of the database being unknown.
# Explain how to restore from the backup.
#-------------------------------------------------------------------------
show_restore() {
  echo -e "\nThe database may have been left in an unknonw state.   It is"
  echo "suggested that you restore the database from the backup file $DB_NAME.backup"
  echo -e "\nTo do this, type:"
  echo -e "\tdropdb $DB_NAME"
  echo -e "\tcreatedb -U owner $DB_NAME"
  echo -e "\tpsql -e $DB_NAME < $DB_NAME.backup"
  echo -e "\nowner in the createdb comman should match the original database owner."
  return 0
}

#-------------------------------------------------------------------------
# show_help()
#
# Show some simple usage text.
#-------------------------------------------------------------------------
show_help() {
  echo -e "\nUpdates the schema version of a PRepS database"
  echo -e "Usage: update_prepsdb [options] db_name"
  echo -e "\t-c : show the schema version of the database and exit"
  echo -e "\t-h : show the help information and exit"
  echo -e "\t-o path : specify writable path for output and temp files"
  echo -e "\t-P path : supply the path to PL/pgSQL (plpgsql.so)"
  echo -e "\t-p path : specify full path to the PostgreSQL tools"
  echo -e "\t-u user_name : specify the owner of the database"
  echo -e "\t-v : show the version information for this script and exit\n"
  return 0
}

#-------------------------------------------------------------------------
# get_confirm()
#
# Echo out the data about to be used to setup the database, and get
# confirmation from the user that they wish to proceed.
#
# Return a "no error" condition if they wish to preceed and an "error"
# condition if they do not wish to proceed.
#------------------------------------------------------------------------- 
get_confirm() {
  echo -e "\nAbout to update a PRepS database as follows:"
  echo -e "\tDatabase:     $DB_NAME"
  echo -e "\tFrom Version: $START_VER"
  echo -e "\tTo Version:   $TARG_VER"
  echo -e "\nAre you sure? \c"

  while true
  do
    read X
    case "$X" in
      y | Y | yes | Yes | YES )
        return 0;;
      n | N | no | No | NO )
        return 1;;
      *) echo "Please enter yes or no";;
    esac
  done
}


#------------------------------------------------------------------------- 
# mainline
#------------------------------------------------------------------------- 

##
## Process the command line options.
if ! getopt cho:P:p:u:v "$@" > /dev/null
then
   exit 1;
fi

eval set -- `getopt cho:P:p:u:v "$@"`
while true
do
  case "$1" in
    -c) SHOW_SCHEMA="Yes"; shift;;
    -h) show_help; shift; exit 0;;
    -o) OUTPUT_DIR="$2"; shift 2;;
    -P) PLPGSQL_PATH="$2"; shift 2;;
    -p) PG_PATH="$2"; shift 2;;
    -u) DB_OWNER="$2"; shift 2;;
    -v) show_version; shift; exit 0;;
    --) shift; break;;
     *) echo "Internal error!" ; exit 1 ;;
  esac
done

if [ $# -eq 0 ]
then
  echo -e "\a\nERROR:  You must supply a database name...\n"
  exit 2
fi

if [ $# -gt 1 ]
then
  echo -e "\a\nERROR:  Too many args."
  echo -e "Format: update_prepsdb [options] db_name\n"
  exit 3
fi
  
DB_NAME="$1"

##
## If we do not have an output directory specified by the user,
## attempt to find one.  Either way, check it.
if [ -z "$OUTPUT_DIR" ]
then
  set_output_dir
fi
check_output_dir

##
## if a path to the tools was specified, prefix each tool with it.
if [ ! -z "$PG_PATH" ]
then
  PSQL=$PG_PATH/$PSQL
  DROPDB=$PG_PATH/$DROPDB
  CREATEDB=$PG_PATH/$CREATEDB
  PG_DUMP=$PG_PATH/$PG_DUMP
fi

##
## Make sure we can connect to the given database...
if ! $PSQL $DB_NAME -c "select max( schema_ver ) from version" -q > /dev/null
then
   echo -e "\a\nFailed to use $PSQL to connect to $DB_NAME.\n"
 
   exit 4
fi

##
## If the user did not specify a specific start version, query the
## database to find out what the current schema version is.
if [ "$START_VER" = "" ] || [ "$SHOW_SCHEMA" = "Yes" ] 
then
   START_VER=`$PSQL $DB_NAME -c 'select max( schema_ver ) from version' -qtA`
fi

##
## If the user wanted the current schema, print it out and exit.
if [ "$SHOW_SCHEMA" = "Yes" ]
then
   echo -e "\nCurrent schema ver for $DB_NAME: $START_VER\n"

   exit 0
fi

##
## Check for the PostgreSQL superuser.
if ! check_user
then
  echo -e "\nPlease login as the PostgreSQL superuser, and attempt to run this"
  echo "script again."
  exit 99 
fi

##
## Perform some simple sanity checks based on the version
## information that we have.
if [ "$TARG_VER" -lt "$START_VER" ]
then
   echo -e "\a\nERROR: Cannot go from higher version to a lower version."
   echo -e "\tCurrent schema version: $START_VER"
   echo -e "\tTarget schema version:  $TARG_VER\n"
 
   exit 5
fi

##
## Make sure the files we are going to need exist
if [ ! -f "/schema.in" ]
then
  echo -e "\a/schema.in is missing"
  echo "aborting..."
  exit 7
fi

if [ ! -f "$PLPGSQL_PATH/plpgsql.so" ]
then
  echo -e "\aPL/pgSQL (plpgsql.so) not found in $PLPGSQL_PATH"
  echo "You may need to use -P to specify the path"
  echo "aborting..."
  exit 7
fi

##
## Make sure the user actually wants to do this.
if ! get_confirm
then
   echo "Aborting..."
   exit 6
fi

##
## Steps:
##
## 0. Create the working files we will need
## 1. Create a backup of the existing database
## 2. Create a dumpfile of the data
## 3. Drop the database
## 4. Create the database
## 5. Load the schema
## 6. Restore the data
## 7. Clean up the files 
##
while [ -z "$DB_OWNER" ]
do
  echo "Who should own the recreated database? [default: $POSTGRES_SU]:"
  read TEMP
  DB_OWNER="${TEMP%%" "}"
  if [ -z "$DB_OWNER" ]
  then
    DB_OWNER="$POSTGRES_SU"
  fi
done

echo "Creating schema file"
if ! sed -e "s#XXDBOWNERXX#$DB_OWNER#g" \
         -e "s#XXPOSTGRESSUXX#$POSTGRES_SU#g" \
         -e "s#XXLIBDIRXX##g" \
         -e "s#XXPGLIBDIRXX#$PLPGSQL_PATH#g" \
         /schema.in > $OUTPUT_DIR/schema.sql
then
  echo -e "\aFailed to create schema definition file"
  clean_up
  exit 9
fi

echo "Creating back of database, file: $OUTPUT_DIR/$DB_NAME.backup"
if ! $PG_DUMP $DB_NAME > $OUTPUT_DIR/$DB_NAME.backup
then
  echo -e "\aBackup failed, aborting..."
  clean_up
  exit 10
fi

echo "Dumping current data"
if ! $PG_DUMP -ad $DB_NAME > $OUTPUT_DIR/$DB_NAME.data.in
then
  echo -e "\aFailed to create data dump file, aborting..."
  clean_up
  exit 11
fi

##
## When pg_dump creates a data-only dump file, it disables the triggers as
## the first step when restoring the data.  At the end of the dump is code
## that attempts to re-enable the triggers.  However, if the DB owner is
## not the PostgreSQL super user, this fails.  Insert a statement before
## that section to change the user to the PostgreSQL superuser.
echo "Fixing the data dump file"
if ! sed -e "/BEGIN TRANSACTION;/c\\
\\\connect - $POSTGRES_SU\\
BEGIN TRANSACTION;" $OUTPUT_DIR/$DB_NAME.data.in > $OUTPUT_DIR/$DB_NAME.data
then
  echo -e "\aFailed to fix the data dump file, aborting..."
  clean_up
  exit 12
fi

echo "Dropping current database"
if ! $DROPDB $DB_NAME
then
  echo -e "\aFailed to drop the database, aborting..."
  show_restore
  clean_up
  exit 13
fi

echo "Recreating the database"
if ! $CREATEDB -U $DB_OWNER $DB_NAME
then
  echo -e "\aFailed to recreate the database, aborting..."
  show_restore
  clean_up
  exit 14
fi

echo "Loading the schema"
if ! $PSQL -e $DB_NAME < $OUTPUT_DIR/schema.sql > $OUTPUT_DIR/$DB_NAME.log 2>&1
then
  echo -e "\aFailed to install the schema, aborting..."
  show_restore
  clean_up
  exit 15
fi

echo "Droping sequences"
if ! $PSQL -e $DB_NAME < /drop_seq.sql > /dev/null 2>&1
then
  echo -e "\aFailed to drop the sequences, aborting..."
  show_restore
  clean_up
  exit 16
fi

echo "Restoring data"
if ! $PSQL -e $DB_NAME < $OUTPUT_DIR/$DB_NAME.data >> $OUTPUT_DIR/$DB_NAME.log 2>&1
then
  echo -e "\aFailed to restore the data, aborting..."
  show_restore
  clean_up
  exit 17
fi

echo "Performing final database cleanup"
$PSQL -e $DB_NAME < /final_cleanup.sql > /dev/null 2>&1

echo "Updating version information"
DESCR=`$PSQL $DB_NAME -c "select descr from version where schema_ver = (select max( schema_ver ) from version where descr is not null and descr <> '')" -qtA`
if $PSQL -q -U $DB_OWNER \
         -c "insert \
               into version \
             values ($TARG_VER, '$DB_NAME', '$DESCR', now())" $DB_NAME
then
  echo -e "\aDone..."
  ## 
  ## When looking for errors, ignore the ones about the sequences
  ## already existing.  There is no harm in both the schema and the
  ## data dump files attemptint to create them.
  ERRORS=`grep -c '^ERROR:' $OUTPUT_DIR/$DB_NAME.log`
  if [ $ERRORS -gt 0 ]
  then
    grep '^ERROR:' $OUTPUT_DIR/$DB_NAME.log
    echo -e "\a\nThe $OUTPUT_DIR/$DB_NAME.log file shows $ERRORS errors..."
  else
    show_final_msg
  fi
  clean_up

  exit 0
fi

##
## If we are here, something must have went wrong, and we did not 
## catch it.
echo -e "\a\nERROR: Unknown Error!!!\n"
show_restore
exit 99
