#! /bin/sh
#
# $DBA/cindex
#
# This script can be used to create a SQL script containing DDL statements to
# create a new index on a given table.
#
# Restrictions:
#	1) Requires that the following files be existing and found in the PATH:
#		define, fixcase, trunc, valid_db
#	2) Requires that the following files be existing:
#		cindex.sql, cindexd.sql, istable.sql
#	3) The following restrictions are from the define script:
#	   a) $TOOLS/getsyi, $TOOLS/replpath, and $TOOLS/database must exist.
#	   b) If Oracle Applications are being used, $APPL_TOP/$ENVNAME.env must
#	       exist.
#
# Last Change 09/30/97 by Brian Lomasky
#
b=`tput smso`
x=`tput rmso`
echo "\nThis script can be used to create a SQL script containing DDL" \
	"statements to"
echo "create a new index on a given table."
echo ""
if [ -r cr_index.sql ]
then
	rm cr_index.sql
fi
echo "Enter the ORACLE SID of the database to be accessed: \c"
read sid
mysid=`fixcase $sid`
if [ "$mysid" = "" ]
then
	echo "\nTerminating script - No Oracle SID specified..."
	exit 1
fi
valid_db $mysid
if [ $? -ne 0 ]; then exit 2
fi
SID=$mysid
. define
echo "\nEnter the schema name to be accessed: \c"
read schema
schema=`echo $schema | tr '[a-z]' '[A-Z]'`
if [ "$schema" = "" ]
then
	echo "\nTerminating script - No schema specified..."
	exit 1
fi
echo "\nEnter the table name to create an index on: \c"
read table
table=`echo $table | tr '[a-z]' '[A-Z]'`
if [ "$table" = "" ]
then
	echo "\nTerminating script - No table specified..."
	exit 1
else
	# Verify for a valid table name
	sqlplus -s / @$DBA/istable $schema $table | grep row | cut -c1-2 \
		> istbl.tmp
	if [ -s istbl.tmp ]
	then
		isrstatus=`cat istbl.tmp`
		if [ -r istbl.tmp ]
		then
			rm istbl.tmp
		fi
		if [ "$isrstatus" = "no" ]
		then
			# No rows select - Table does not exist
			echo "\nError - Table does not exist - Aborting...\007"
			exit 1
		fi
	else
		if [ -r istbl.tmp ]
		then
			rm istbl.tmp
		fi
		echo "\nUnexpected error while trying to verify table name" \
			"- Aborting...\007"
		exit 1
	fi
	# Create a list of the table's columns on the screen and in a file
	sqlplus -s / @$DBA/cindexd $schema $table
	if [ ! -s cindexd.tmp ]
	then
		if [ -r cindexd.tmp ]
		then
			rm cindexd.tmp
		fi
		echo "\nUnexpected error while trying to list table columns" \
			"- Aborting...\007"
		exit 1
	fi
	# Create sql script to insert all of the column names that we want to
	# index into a temp table
	cat /dev/null > cindex_temp.sql
	colnam="*"
	n=0
	while [ "$colnam" <> "" ]
	do
		if [ $n -eq 0 ]
		then
			nth="1st"
		else
			if [ $n -eq 1 ]
			then
				nth="2nd"
			else
				if [ $n -eq 2 ]
				then
					nth="3rd"
				else
					z=`expr $n + 1`
					nth="${z}th"
				fi
			fi
		fi
		echo ""
		echo "   Enter ${b}$nth${x} column name to include in the index"
		echo "      (or press <RETURN> if done): \c"
		read colnam
		colnam=`echo $colnam | tr '[a-z]' '[A-Z]'`
		if [ "$colnam" <> "" ]
		then
			# Verify column name as being valid
			if [ -z "`grep xyz${colnam}zy cindexd.tmp`" ]
			then
				# Executed if length of string returned by grep
				# is zero
				echo "\n      Error:  Invalid column name -" \
					"Try again...\007"
				echo "      (Note that LONG columns can not" \
					"be indexed)"
			else
				n=`expr $n + 1`
				echo "insert into cindex_temp values" \
					"($n, '$colnam');" >> \
					cindex_temp.sql
			fi
		fi
	done
	if [ -r cindexd.tmp ]
	then
		rm cindexd.tmp
	fi
	if [ $n -eq 0 ]
	then
		echo "\nTerminating script - No columns specified..."
		rm cindex_temp.sql
		exit 1
	fi
	echo "\nEnter the name of the new index to be created: \c"
	read ind_name
	ind_name=`echo $ind_name | tr '[A-Z]' '[a-z]'`
	if [ "$ind_name" = "" ]
	then
		echo "\nTerminating script - No index name specified..."
		exit 1
	fi
	sqlplus -s / @$DBA/cindex $schema $table $ind_name
	rm cindex_temp.sql
	if [ ! -s cr_index.sql ]
	then
		echo "Error - No index was created for $table!\007"
		if [ -r cr_index.sql ]
		then
			rm cr_index.sql
		fi
	fi
fi
if [ -r cr_index.sql ]
then
	# Remove all trailing spaces from the script file
	trunc cr_index.sql
	echo "Created cr_index.sql which contains index-creation DDL...\n"
	echo "You will have to specify the correct ${b}TABLESPACE${x}" \
		"for the new index.\n"
fi