#!/bin/ksh

# fiper4oracle Create, Update, or just check ORACLE database status

usage() {
    cat <<!
Usage: $(basename "$script") PropFile Option
  Create, Update, Clear, or just check the ORACLE Database for the SEE Server.

  PropFile - property file that contains SEE server configuration.
          Edit a copy of file fiper4all.prop in this directory to crete.

  Option - The option to perform:
     create - Create database.  Delete and re-create if it already exists.
     config - Create DB tables.  Drop and re-create if already exist.
     clear  - Empty database by erasing the contents of all tables.
     check  - Confirm that database exists and has tables.  Create if needed.
     all    - default - same as 'check'
!
}

# Load useful shell functions.  Location is always relative to this file.
script="$0"
shell_dir=$(dirname "$script")
. "$shell_dir/fiper-functions.sh"

# Get rest of options and then load properties.
propfile=$(makeAbsolute "$1")
operation=$2

if [ ! "$propfile" -o ! "$operation" ]; then
    usage
    exit 1
fi
 
case $operation in
    create) op_create=1 ;;
    config) op_config=1 ;;
    clear)  op_clear=1 ;;
    check)  op_check=1 ;;
    all)
        # Universal default.  Currently same as check.
        op_check=1
        ;;
    help)
        usage
        exit 0;
        ;;
    *)
        err "Unknown operation: $operation."
        usage
        exit 1
        ;;
esac

# Cascade operations
# NOTE: for Oracle, create and config are identical!
if [ "$op_create" ]; then
    op_config=1
fi

# Default (and normalize) FIPER_HOME
# Use 2 levels up from location of this script if FIPER_HOME not set.
# Set this here as the Properties file may override below,
# but if it doesn't override, it may still need the value.
export FIPER_HOME=$(cannonPath ${FIPER_HOME:-$shell_dir/../../..})

# Read the properties file
msg "Reading property file '$propfile'"
. "$propfile" || fail "Error reading properties file $propfile"

# normalize (but don't set) some variables
FIPER_CONF=$(stdPath "$FIPER_CONF")
LOG_DIR=$(stdPath "$LOG_DIR")
ORACLE_HOME=$(stdPath "$ORACLE_HOME")
DB_USER=$(upcase "$DB_USER")

# Check required variables.
checkVar DB_USER            # User who will own database
checkVar DB_USER_PW         # Password for DB_USER
checkVar SYS_PWD            # Password for Oracle 'sys' user
checkVar ORACLE_TABLEDIR    # Directory to hold application tables.
checkVar ORACLE_TABLESIZE   # Size of user-specific table space, in MB.
if [ ! "$ORACLE_HOME" ]; then
    # Try to default ORACLE_HOME to parent of directory containing sqlplus.exe
    oracmd=$(which sqlplus$CMD)
    if [ $? -eq 0 ]; then
        ORACLE_HOME=$(cannonPath $(dirname "$oracmd")/..)
    fi
fi
checkVar ORACLE_HOME bin/sqlplus$CMD    # Oracle Server or Client install location
defaultVar FIPER_CONF $(cannonPath "$FIPER_HOME/../config")      # used to default LOG_DIR
defaultVar tmp4all "${TMPDIR:-${TEMP}}"        # Directory for temporary files.
defaultVar LOG_DIR "${tmp4all}/fiper4all"      # directory for log files
defaultVar ORACLE_CONNECT "$ORACLE_SID"  # Oracle connect string

checkErr

if [ ! -d "$LOG_DIR" ]; then
    mkdir "$LOG_DIR" || fail "Unable to create log directory '$LOG_DIR'"
fi

# Set the table space name.  Is tied to the DB User.
DB_TS=${DB_USER}TS1              # Name of Table Space belonging to DB_USER

# Directory in workspace that contains oracle configuration.
ORA_CONFIG="$FIPER_HOME/reffiles/SMAFIPserver/db/oracle"

# First, check Oracle version. Allow 10 or 11.
log="$LOG_DIR/oracleVersion.txt"
cmd "'$ORACLE_HOME/bin/sqlplus$CMD' -version" "$log"
if [ $? -ne 0 ]; then
    fail "Unable to determine version of Oracle.  See file $log"
fi
ver=$(cat "$log")
ver=$(expr "$ver" : '.*Release \([^ ]*\) .*')
if [ $? -ne 0 ]; then
    fail "Unable to determine version of Oracle.  See file $log"
elif expr "$ver" : '1[01]\..*' >$NUL; then
    note "Oracle has expected version: $ver"
else
    warn "Oracle has unexpected version '$ver'.  Commands may not work."
fi

# Function to run an sqlplus command as 'sys' and check for failure
# Usage: sqlsys NAME COMMAND
#   Executes commands in multi-line string "COMMAND" as user SYS.
#   Commands are saved in $NAME.sql and log written to $LOG_DIR/$NAME.log
#   Variable cmdfile is set to name of command file.
#   Variable log is set to absolute path to log file
sqlsys() {
    n=$1
    c="$2"
    cmdfile="$LOG_DIR/$n.sql"
    log="$LOG_DIR/$n.log"
    cat <<!EOF >$cmdfile
SET ECHO ON;
${c}
QUIT;
!EOF
    msg "sqlplus -L sys/******@$ORACLE_CONNECT AS SYSDBA @$n.sql >$n.log"
    "$ORACLE_HOME/bin/sqlplus" -L "sys/${SYS_PWD}@$ORACLE_CONNECT" as SYSDBA "@$cmdfile" >"$log" 2>&1
    if [ $? -ne 0 ] || grep '^ORA' "$log" >$NUL; then
        fail "Oracle command $n failed.  See log file $log"
    fi
}

# Usage: sqlusr NAME COMMAND
#   Executes commands in multi-line string "COMMAND" as user DB_USER
#   Commands are saved in $NAME.sql and log written to $LOG_DIR/$NAME.log
#   Variable cmdfile is set to name of command file.
#   Variable log is set to absolute path to log file
sqlusr() {
    n=$1
    c="$2"
    cmdfile="$LOG_DIR/$n.sql"
    log="$LOG_DIR/$n.log"
    cat <<!EOF >$cmdfile
SET ECHO ON;
${c}
QUIT;
!EOF
    msg "sqlplus -L $DB_USER/******@$ORACLE_CONNECT @$n.sql >$n.log"
    "$ORACLE_HOME/bin/sqlplus" -L "${DB_USER}/${DB_USER_PW}@${ORACLE_CONNECT}" "@$cmdfile" >"$log" 2>&1
    if [ $? -ne 0 ] || grep '^ORA' "$log" >$NUL; then
        fail "Oracle command $n failed.  See log file $log"
    fi
}

# Check if the table space exists.
sqlsys oraCheckTS "select TABLESPACE_NAME from DBA_TABLESPACES where TABLESPACE_NAME = '$DB_TS';"
if grep 'no rows selected' $log >$NUL; then
    tablespace_exists=''
else
    tablespace_exists=1
fi

# Check if the User exists
sqlsys oraCheckUser "select USERNAME from DBA_USERS where USERNAME = '$DB_USER';"
if grep 'no rows selected' $log >$NUL; then
    user_exists=''
else
    user_exists=1
fi

# Check for a partly created database.  This is currently unrecoverable.
if [ "$tablespace_exists" -a ! "$user_exists" ]; then
    fail "Database $ORACLE_SID for user $DB_USER in table space $DB_TS partly exists.  Must clean up before it can be re-created."
fi

if [ "$op_config" -a "$user_exists" ]; then
    # Must delete user so it can be re-created below.
    note "Removing Oracle User $DB_USER"
    sqlsys oraDropUser "
DROP USER \"${DB_USER}\" CASCADE;
DROP TABLESPACE \"${DB_TS}\" INCLUDING CONTENTS AND DATAFILES;"

    cmd "rm -rf '${ORACLE_TABLEDIR}/${DB_TS}.DBF'" 
    user_exists=''
fi

if [ ! "$user_exists" ]; then

    # Make very sure table space file doesn't exist
    # as it will cause following command to fail if it does.
    if [ -e "${ORACLE_TABLEDIR}/${DB_TS}.DBF" ]; then
        cmd "rm -rf '${ORACLE_TABLEDIR}/${DB_TS}.DBF'" 
        if [ $? -ne 0 ]; then
            fail "Unable to delete Oracle table space '${ORACLE_TABLEDIR}/${DB_TS}.DBF'"
        fi
    fi

    # Create the user and the table space.
    note "Creating Oracle User $DB_USER"
    sqlsys oraMakeUser "
CREATE SMALLFILE TABLESPACE \"$DB_TS\" DATAFILE '${ORACLE_TABLEDIR}/${DB_TS}.DBF' SIZE ${ORACLE_TABLESIZE}M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER \"${DB_USER}\" PROFILE \"DEFAULT\" IDENTIFIED BY \"${DB_USER_PW}\" DEFAULT TABLESPACE \"${DB_TS}\" TEMPORARY TABLESPACE \"TEMP\" ACCOUNT UNLOCK;
GRANT UNLIMITED TABLESPACE TO \"${DB_USER}\";
GRANT SELECT ON SYS.DBA_PENDING_TRANSACTIONS TO \"${DB_USER}\";
GRANT EXECUTE ON SYS.DBMS_SYSTEM TO \"${DB_USER}\";
GRANT CONNECT TO \"${DB_USER}\";
GRANT RESOURCE TO \"${DB_USER}\";"
    user_exists=1
fi

# Now user exists.  Check if database exists.
sqlusr oraCheckDB "select TABLE_NAME from ALL_TABLES where TABLE_NAME = 'FIPERSYS'; "
if grep 'no rows selected' $log >$NUL; then
    tables_exist=''
else
    tables_exist=1
fi

if [ ! "$tables_exist" ]; then
    # Create database tables using existing script.
    note "Creating Oracle Tables for user $DB_USER"
    sqlusr oraTables "@${ORA_CONFIG}/createtables.sql; "
    tables_exist=1
fi

