#!/bin/sh
#=======================================================================#
# - COPYRIGHT NOTICE -                                                  #
#                                                                       #
# Copyright 2000-2006 OpenSpirit Corporation.                           #
#                                                                       #
# The information contained herein and which follows is proprietary     #
# information and is the property of OpenSpirit Corporation. This       #
# information is not to be divulged, released, copied, reproduced,      #
# or conveyed to unauthorized personnel, companies, or other            #
# institutions without the direct and expressed approval in writing     #
# of OpenSpirit Corporation.                                            #
#                                                                       #
#=======================================================================#

PATH=/usr/bin:$PATH; export PATH

# This is for OpenSpirit v2.9
#
# This script does the following:
# 1. Create the OpenSpirit User query account in GeoFrame, OpenWorks,
#    or Finder.

# This script requires a few environment variables to be set prior to 
# running.
#
# The following variables are used in this script. Default values are 
# provided for everything except those noted. 
#
# No default values provided.
# ORACLE_HOME    - Oracle Home for the instance where the user account
#                  will be created.
# TNS_ADMIN      - TNS_ADMIN for the instance where the user account
#                  will be created.
# DB_TWO_TASK    - The TWO_TASK value for the Oracle instance.
# DBA_USER_NAME      - A DBA privileged account.
# DBA_USER_PASSWD    - And password
#
# These values need to be set if running outside installation
# as there are no defaults provided.
ORACLE_HOME=[your ORACLE_HOME]
export ORACLE_HOME
TNS_ADMIN=[your TNS_ADMIN]
export TNS_ADMIN
DB_TWO_TASK=[your TWO_TASK]
export DB_TWO_TASK
DBA_USER_NAME=SYSTEM
DBA_USER_PASSWD=[password for the SYSTEM account]

# Set the following to do the OpenWorks or GeoFrame instance.
# Set to "OPENWORKS98" "OPENWORKS" "GEOFRAME" or "FINDER" (no quotes).
QUERY_ACCOUNT_TYPE=[OPENWORKS98, OPENWORKS, GEOFRAME or FINDER]
# You can use the default username/password of openspirit/openspirit
# or change it to something else.
QUERY_ACCOUNT_NAME=openspirit
QUERY_ACCOUNT_PASSWORD=openspirit
#
# **********************************************************
#     NOTHING ELSE BELOW THIS LINE NEEDS TO BE CHANGED 
# **********************************************************
# ******* unless you really know what you are doing.********
# **********************************************************

echo "    Creating OpenSpirit query account $QUERY_ACCOUNT_NAME for $QUERY_ACCOUNT_TYPE."
echo "      ORACLE_HOME=$ORACLE_HOME"
echo "      TNS_ADMIN=$TNS_ADMIN"
echo "      DB_TWO_TASK=$DB_TWO_TASK "
echo "      DBA_USER_NAME=$DBA_USER_NAME"

# Then let's create it for OpenWorks 98.x

if [ "$QUERY_ACCOUNT_TYPE" = "OPENWORKS98" ]; then

${ORACLE_HOME}/bin/sqlplus -SILENT ${DBA_USER_NAME}/${DBA_USER_PASSWD}@${DB_TWO_TASK} <<EOF
set TERMOUT off
set echo on
create user $QUERY_ACCOUNT_NAME identified by $QUERY_ACCOUNT_PASSWORD temporary tablespace TEMP;
grant create synonym to $QUERY_ACCOUNT_NAME;
grant create session to $QUERY_ACCOUNT_NAME;
create synonym ${QUERY_ACCOUNT_NAME}.OW_SYSP_PRJ_SECURITY for owsysp.OW_SYSP_PRJ_SECURITY;
connect OWSYSP/OWSYSP
grant select on OW_SYSP_PRJ_SECURITY to $QUERY_ACCOUNT_NAME;
EOF
echo "    Completed creating OpenSpirit query account $QUERY_ACCOUNT_NAME for $QUERY_ACCOUNT_TYPE."

elif [ "$QUERY_ACCOUNT_TYPE" = "GEOFRAME" -o "$QUERY_ACCOUNT_TYPE" = "OPENWORKS" ]; then

# Then let's create it for GeoFrame or OpenWorks
${ORACLE_HOME}/bin/sqlplus -SILENT ${DBA_USER_NAME}/${DBA_USER_PASSWD}@${DB_TWO_TASK} <<EOF
create user $QUERY_ACCOUNT_NAME identified by $QUERY_ACCOUNT_PASSWORD temporary tablespace TEMP;
grant create session to $QUERY_ACCOUNT_NAME;
EOF
echo "    Completed creating OpenSpirit query account $QUERY_ACCOUNT_NAME for $QUERY_ACCOUNT_TYPE."

elif [ "$QUERY_ACCOUNT_TYPE" = "FINDER" ]; then

# Then let's create it for Finder
${ORACLE_HOME}/bin/sqlplus -SILENT ${DBA_USER_NAME}/${DBA_USER_PASSWD}@${DB_TWO_TASK} <<EOF
create user $QUERY_ACCOUNT_NAME identified by $QUERY_ACCOUNT_PASSWORD temporary tablespace TEMP;
grant create session to $QUERY_ACCOUNT_NAME;
grant insert any table to $QUERY_ACCOUNT_NAME;
CONNECT $QUERY_ACCOUNT_NAME/$QUERY_ACCOUNT_PASSWORD@${DB_TWO_TASK};
INSERT INTO codes.r_source(TYPE, CODE) VALUES('OPENSPIRIT', 'OPENSPIRIT');
CONNECT ${DBA_USER_NAME}/${DBA_USER_PASSWD}@${DB_TWO_TASK};
revoke insert any table from $QUERY_ACCOUNT_NAME;
EOF
echo "    Completed creating OpenSpirit query account $QUERY_ACCOUNT_NAME for $QUERY_ACCOUNT_TYPE."

else
echo "You didn't say whether you wanted to create the account in GeoFrame, OpenWorks or Finder"
fi

exit 0