#! /bin/sh
#
# $DBA/ddl
#
# This script can be used to create a SQL script containing DDL statements to
# drop and then recreate TABLES, INDEXES, and/or REFERENTIAL INTEGRITY
# CONSTRAINTS (for all tables dependent on a given table), or for a specific
# index or constraint name.
#
# Restrictions:
#	1) Requires that the following files exist in $DBA:
#		ddltbls.sql, ddlndxs.sql, ddlndxs2.sql, ddlcon.sql,
#		ddlcons.sql, ddlcons2.sql, istable.sql
#	2) Requires that the following files exist and be found in the PATH:
#		fixcase, trunc, valid_db
#	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`
n=`tput rmso`
echo "\nThis script can be used to create a SQL script containing DDL" \
	"statements to"
echo "drop and then recreate TABLES, INDEXES, and/or REFERENTIAL INTEGRITY"
echo "CONSTRAINTS (for all tables dependent on a given table), or for a" \
	"specific"
echo "index or constraint name."
echo ""
if [ -r ddl_create.sql ]
then
	echo "Error - There is already an existing ddl_create.sql file.\007"
	echo "        You must manually delete or rename this file."
	exit 1
fi
if [ -r ddl_drop.sql ]
then
	echo "Error - There is already an existing ddl_drop.sql file.\007"
	echo "        You must manually delete or rename this file."
	exit 1
fi
if [ -r ddl_create.tmp ]
then
	rm ddl_create.tmp
fi
if [ -r ddl_drop.tmp ]
then
	rm ddl_drop.tmp
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
mytbl=N
myndx=N
myref=N
myrtb=N
index=""
constr=""
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 ""
echo "\nEnter the table name to be processed (or press <RETURN> to enter"
echo "    an Index or Constraint Name): \c"
read table
table=`echo $table | tr '[a-z]' '[A-Z]'`
if [ "$table" = "" ]
then
	echo "\nEnter the index name to be processed"
	echo "    (or press <RETURN> to enter a Constraint Name): \c"
	read index
	index=`echo $index | tr '[a-z]' '[A-Z]'`
	if [ "$index" = "" ]
	then
		echo "\nEnter the constraint name to be processed: \c"
		read constr
		constr=`echo $constr | tr '[a-z]' '[A-Z]'`
		if [ "$constr" = "" ]
		then
			echo "\nTerminating script - No object specified..."
			exit 1
		else
			myref=Y
		fi
	else
		myndx=Y
	fi
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
	echo "\nGenerate DDL to recreate table ${b}$table${n}?" \
		"(y/n;Default=n) \c"
	read tbl
	mytbl=`echo $tbl | tr '[a-z]' '[A-Z]'`
	echo "\nGenerate DDL to recreate INDEXES on table ${b}$table${n}?"
	echo "   (y/n;Default=n) \c"
	read ndx
	myndx=`echo $ndx | tr '[a-z]' '[A-Z]'`
	echo "\nGenerate DDL to recreate REFERENTIAL INTEGRITY CONSTRAINTS"
	echo "   that are ${b}DEPENDENT${n} on table ${b}$table${n}?" \
		"(y/n;Default=n) \c"
	read ref
	myref=`echo $ref | tr '[a-z]' '[A-Z]'`
	if [ "$myref" != "Y" ]
	then
		echo "\nGenerate DDL to recreate REFERENTIAL INTEGRITY" \
			"CONSTRAINTS"
		echo "   on table ${b}$table${n}? (y/n;Default=n) \c"
		read ref
		myrtb=`echo $ref | tr '[a-z]' '[A-Z]'`
	fi
fi
echo ""
if [ "$mytbl" = "Y" ]
then
	echo "  Processing TABLES..."
	sqlplus -s / @$DBA/ddltbls $schema $table
	if [ -s ddl_create.tmp ]
	then
		cat ddl_create.tmp >> ddl_create.sql
		rm ddl_create.tmp
	else
		echo "Warning - No DDL created for Tables!\007"
		if [ -r ddl_create.tmp ]
		then
			rm ddl_create.tmp
		fi
	fi
	if [ -s ddl_drop.tmp ]
	then
		cat ddl_drop.tmp >> ddl_drop.sql
		rm ddl_drop.tmp
	else
		if [ -r ddl_drop.tmp ]
		then
			rm ddl_drop.tmp
		fi
	fi
fi
if [ "$myndx" = "Y" ]
then
	echo "  Processing INDEXES..."
	if [ "$index" = "" ]
	then
		sqlplus -s / @$DBA/ddlndxs $schema $table
	else
		sqlplus -s / @$DBA/ddlndxs2 $schema $index
	fi
	if [ -s ddl_create.tmp ]
	then
		cat ddl_create.tmp >> ddl_create.sql
		rm ddl_create.tmp
	else
		echo "Warning - No DDL created for Indexes!\007"
		if [ -r ddl_create.tmp ]
		then
			rm ddl_create.tmp
		fi
	fi
	if [ -s ddl_drop.tmp ]
	then
		cat ddl_drop.tmp >> ddl_drop.sql
		rm ddl_drop.tmp
	else
		if [ -r ddl_drop.tmp ]
		then
			rm ddl_drop.tmp
		fi
	fi
fi
if [ "$myrtb" = "Y" -o "$myref" = "Y" ]
then
	echo "  Processing REFERENTIAL INTEGRITY CONSTRAINTS..."
	if [ "$myrtb" = "Y" ]
	then
		sqlplus -s / @$DBA/ddlcon $schema $table
	fi
	if [ "$myref" = "Y" ]
	then
		if [ "$constr" = "" ]
		then
			sqlplus -s / @$DBA/ddlcons $schema $table
		else
			sqlplus -s / @$DBA/ddlcons2 $schema $constr
		fi
	fi
	if [ -s ddl_create.tmp ]
	then
		cat ddl_create.tmp >> ddl_create.sql
		rm ddl_create.tmp
	else
		echo "Warning - No DDL created for Referential Integrity" \
			"Constraints!\007"
		if [ -r ddl_create.tmp ]
		then
			rm ddl_create.tmp
		fi
	fi
	if [ -s ddl_drop.tmp ]
	then
		cat ddl_drop.tmp >> ddl_drop.sql
		rm ddl_drop.tmp
	else
		if [ -r ddl_drop.tmp ]
		then
			rm ddl_drop.tmp
		fi
	fi
fi
if [ -r ddl_drop.sql ]
then
	trunc ddl_drop.sql
	echo "Created ddl_drop.sql which contains DROP scripts..."
fi
if [ -r ddl_create.sql ]
then
	trunc ddl_create.sql
	echo "Created ddl_create.sql which contains CREATE scripts..."
fi