#!/bin/sh
#
# $DBA/cdindex
#
# Creates or drops all non-unique indexes on a given table
#
# Parameters:
#	$1 = Schema Name (uppercased) that owns the table specified in $2.
#	$2 = Table Name (uppercased) to create or drop indexes for.
#	$3 = Action to be performed.  One of:  CREATE, DROP
#
# Restrictions:
#	1) ORACLE_SID and ORACLE_HOME must be set before executing this script.
#	2) $DBA/cdindex.sql must exist.
#	3) The OPS$username account must have the privileges to CREATE and DROP
#	   indexes on the specified table (as well as sufficient quotas).
#	4) The invoking user needs to have SELECT access to the sys.dba_indexes
#	   and sys.dba_ind_columns views.
#
#	Last Change 10/01/97 by Brian Lomasky
#
if [ "$ORACLE_SID" = "" ]
then
	echo "Error:  Terminating cdindex script due to unassigned ORACLE_SID"
	exit 2
fi
if [ "$1" = "" ]
then
	echo "Error:  Terminating cdindex script due to missing schema name" \
		"parameter"
	exit 2
fi
if [ "$2" = "" ]
then
	echo "Error:  Terminating cdindex script due to missing table_name" \
		"parameter"
	exit 2
fi
case "$3" in
	DROP)	if [ -r ${2}_drop.sql ]
		then
			echo "Error:  Indexes for ${2} have already been" \
				"dropped!"
			echo "Aborting \$DBA/cdindex..."
			exit 2
		fi
		if [ -r ${2}_create.sql ]
		then
			rm ${2}_create.sql
		fi
		# Create the two scripts to drop and create the indexes
		sqlplus / @$DBA/cdindex $1 $2
		if [ -r indexest.sql ]
		then
			rm indexest.sql
		fi
		if [ ! -r ${2}_drop.sql ]
		then
			echo "Error:  Can not find ${2}_drop.sql"
			echo "Aborting \$DBA/cdindex..."
			exit 2
		fi
		if [ ! -r ${2}_create.sql ]
		then
			echo "Error:  Can not find ${2}_create.sql"
			echo "Aborting \$DBA/cdindex..."
			exit 2
		fi
		# Execute the "drop" script so as to drop the indexes
		echo "Dropping non-unique indexes on ${2}..."
		sqlplus / @${2}_drop
		echo "Done..."
		;;
	CREATE) if [ ! -r ${2}_drop.sql ]
		then
			echo "Error:  Indexes for ${2} have not been" \
				"previously dropped!"
			echo "Aborting \$DBA/cdindex..."
			exit 2
		fi
		if [ ! -r ${2}_create.sql ]
		then
			echo "Error:  Can not find ${2}_create.sql"
			echo "Aborting \$DBA/cdindex..."
			exit 2
		fi
		# Execute the "create" script so as to create the indexes
		echo "Creating non-unique indexes on ${2}..."
		sqlplus / @${2}_create
		rm ${2}_create.sql
		rm ${2}_drop.sql
		echo "Done..."
		;;
	*)	echo "Error:  Terminating cdindex script due to invalid" \
			"table_name parameter"
		exit 2
		;;
esac