#!/bin/ksh

NUMARGS=$#

if [ -f /tmp/db.* ]
then
	rm /tmp/db.*
fi

TEMP1=/tmp/db.load.1.$$
TEMP2=/tmp/db.load.2.$$
TEMP3=/tmp/db.load.3.$$
TEMP4=/tmp/db.load.4.$$
TEMP5=/tmp/db.load.5.$$

FOR1=/tmp/db.for.1.$$

TABLE_LIST=/tmp/db.table_list.1.$$

PLOADSQL=/tmp/db.pload.1.$$.sql

TABLE1=/tmp/db.table.1.$$
FILE1=/tmp/db.file.1.$$
SRCSQL=/tmp/db.sql.1.$$.sql

TABLE_SQL=/tmp/db.table.1.$$.sql
OTHER_SQL=/tmp/db.other.1.$$.sql

WORKINGDIR=`pwd`
REJECTDIR=/tmp

#TABLE_TYPE="RAW"
TABLE_TYPE="OPERATIONAL"

if [ ${NUMARGS} = 0 ]
then
	echo "Syntax: ${0} DBMS"
	exit
fi

SRCDBMS=$1

DBMSDIR=${WORKINGDIR}/${SRCDBMS}.exp
ORIGSQL=${DBMSDIR}/$1.sql
if [ ! -f ${ORIGSQL} ]
then
	echo "Error: Can't find ${ORIGSQL}"
	exit
fi

echo " "
echo "Parsing ${ORIGSQL}..."
echo " "

#
# TODO: Exit
#
#exit

#
# TODO:
#	BlobSpaces
#	Deffered Constarints
#

#
# 7.XX to 8.30
# Remove the comments from DBEXPORT
# Change 'create table' to 'create ${TABLE_TYPE} table'
# Remove all the NULL lines; Blank and all spaces
#
cat ${ORIGSQL} | \
	sed -e "s/^{ TABLE .*}//" | \
	sed -e "s/^{ unload file name =.*}//" | \
	sed -e "s/^create table /create ${TABLE_TYPE} table /" | \
	egrep -v "^$" | \
	egrep -v "^ *$" > ${SRCSQL}
cp ${SRCSQL} x3

#
# 7.XX to 8.30
# Comment out the 'not null constraint'
# With a "," at the end
#
cp ${SRCSQL} ${TEMP1}
cat ${TEMP1} | \
	sed -e "s/not null constraint.*,$/not null,/" > ${SRCSQL}
	#sed -e "s/not null constraint.*,$/not null, {&} /" > ${SRCSQL}

#
# 7.XX to 8.30
# Comment out the 'not null constraint'
# With no "," at the end
#
cp ${SRCSQL} ${TEMP1}
cat ${TEMP1} | \
	sed -e "s/not null constraint.*/not null/" > ${SRCSQL}
	#sed -e "s/not null constraint.*/not null {&}/" > ${SRCSQL}

#
# Make each SQL statement one line
#
cp ${SRCSQL} ${TEMP1}
cp ${SRCSQL} /tmp/x1
cat ${TEMP1} | \
	nawk '{
		printf("%s",$0)
		printf("NL")
		if ( match($0,/;/) )
			printf("\n")
	}' > ${SRCSQL}
cp ${SRCSQL} /tmp/x2

#
# Remove { * }create
#
cp ${SRCSQL} ${TEMP1}
cat ${TEMP1} | \
	sed -e "s/^{ .*}NLcreate /create /" > ${SRCSQL}
cp ${SRCSQL} ${TEMP1}
cp ${SRCSQL} /tmp/x3.0

#
# Extract the names of the table and data files, from the ORIGINAL
#
egrep "{ unload file name" ${ORIGSQL} > ${FILE1}
cat ${FILE1} | sed -e "s/^{ unload file name = //" > ${TEMP1}
cat ${TEMP1} | sed -e "s/ number of rows =.*//" > ${TEMP2}
cp ${TEMP2} /tmp/x4

egrep "{ TABLE" ${ORIGSQL} > ${TABLE1}
cat ${TABLE1} | sed -e "s/{ TABLE //" > ${TEMP3}
cat ${TEMP3} | sed -e "s/ row size = .*//" > ${TEMP4}
cp ${TEMP4} /tmp/x5

paste ${TEMP4} ${TEMP2} > ${TEMP5}
cat ${TEMP5} | sed -e "s/	/|/" > ${FOR1}
cp ${FOR1} /tmp/x6

#
# Extract only the 'create table' statements
# Extract only the 'create ${TABLE_TYPE} table' statements
# TODO: Getting error with string to long
#
cat ${SRCSQL} | \
	egrep -i "^create table|^create ${TABLE_TYPE} table" > ${TABLE_SQL}
cp ${TABLE_SQL} ${TEMP1}
cat ${TEMP1} | \
	nawk '{
		gsub(/NL/,"\n",$0)
		printf("%s",$0)
		if ( match($0,/;/) )
			printf("\n")
	}' > ${TABLE_SQL}
cp ${TABLE_SQL} /tmp/x7


#
# Extract everything but  the 'create table' statements
# Extract everything but  the 'create ${TABLE_TYPE} table' statements
#
cat ${SRCSQL} | \
	egrep -v -i "^create table|^create ${TABLE_TYPE} table" > ${OTHER_SQL}
cp ${OTHER_SQL} ${TEMP1}
cat ${TEMP1} | \
	nawk '{
		gsub(/NL/,"\n",$0)
		printf("%s",$0)
		if ( match($0,/;/) )
			printf("\n")
	}' > ${OTHER_SQL}
cp ${OTHER_SQL} /tmp/x8

#
# TODO: Exit
#
#exit

#
# Check the number of tables to be created
#
FORCNT=`wc -l ${FOR1} | awk '{ print $1 }'`
TABCNT=`egrep -c "^create " ${TABLE_SQL} | awk '{ print $1 }'`
if [ ${FORCNT} -ne ${TABCNT} ]
then
	echo "Error: ${0}"
	echo "For count: ${FORCNT}"
	echo "Tab count: ${TABCNT}"
	exit
fi

#
# Remove the ${PLOADSQL}
#
if [ -f ${PLOADSQL} ]
then
	rm ${PLOADSQL}
fi

#
# Remove the ${TABLE_LIST}
#
if [ -f ${TABLE_LIST} ]
then
	rm ${TABLE_LIST}
fi

#
# Loop through the table and data file pairs
#
for LINE in `cat ${FOR1}`
do

	#
	# Extract Table
	#
	TABLE=`echo ${LINE} | sed -e "s/|.*//"`
	FILE=`echo ${LINE} | sed -e "s/.*|//"`

	#
	# Save the list of table names
	#
	echo ${TABLE} >> ${TABLE_LIST}

	#
	# Use the data file as the name of the external table
	#
	TABLE_EXT=`echo ${FILE} | sed -e "s/.unl$//" `
	TABLE_EXT=${TABLE_EXT}_ext

	#
	# TODO: Reject files
	#
	cat << EOF >> ${PLOADSQL} 2>&1


        	create  external table ${TABLE_EXT}
                	sameas ${TABLE}
                	using (
                        	format "delimited",
				rejectfile "${REJECTDIR}/db.${TABLE_EXT}.rej",
                        	datafiles (
                                	"disk:1:${DBMSDIR}/${FILE}"
                        	)
                	);

		begin work;

		lock table ${TABLE} in exclusive mode;

        	insert into ${TABLE} select * from ${TABLE_EXT};

        	drop table ${TABLE_EXT};

		alter table ${TABLE} type ( OPERATIONAL );

		commit work;

EOF

done

#
# TODO: Exit
#
#exit

#
# Drop the existing database
#
echo " "
echo "Dropping database ${SRCDBMS}..."
echo " "
timex dbaccess -e - -<<EOF
	drop database ${SRCDBMS};
EOF

#
# Create the database
#
echo " "
echo "Creating database ${SRCDBMS}..."
echo " "
timex dbaccess -e - -<<EOF
	create database ${SRCDBMS};
EOF

#
# Create the Tables ( Tables only )
#
echo " "
echo "Creating tables..."
echo " "
timex dbaccess -e ${SRCDBMS} ${TABLE_SQL}

#
# Pload all the tables in one script
#
echo " "
echo "Ploading tables..."
echo " "
timex dbaccess -e ${SRCDBMS} ${PLOADSQL}

#
# Create the database ( Everything else )
#
echo " "
echo "Running other DDL: Indexes, Alters, etc..."
echo " "
timex dbaccess -e ${SRCDBMS} ${OTHER_SQL}

#
# Update statistics medium
#
echo " "
echo "Running update statistics medium..."
echo " "
timex dbaccess -e ${SRCDBMS} -<<EOF
	update statistics medium;
EOF

#
# TODO: Exit
#
echo " "
echo "Exiting..."
echo " "
exit

#
# Clean up temp files
#
if [ -f ${FILE1} ]
then
	rm ${FILE1}
fi

if [ -f ${TABLE1} ]
then
	rm ${TABLE1}
fi

if [ -f ${TEMP1} ]
then
	rm ${TEMP1}
fi

if [ -f ${TEMP2} ]
then
	rm ${TEMP2}
fi

if [ -f ${TEMP3} ]
then
	rm ${TEMP3}
fi

if [ -f ${TEMP4} ]
then
	rm ${TEMP4}
fi

if [ -f ${TEMP5} ]
then
	rm ${TEMP5}
fi

if [ -f ${SRCSQL} ]
then
	rm ${SRCSQL}
fi

if [ -f ${TABLE_SQL} ]
then
	rm ${TABLE_SQL}
fi

if [ -f ${OTHER_SQL} ]
then
	rm ${OTHER_SQL}
fi

if [ -f ${FOR1} ]
then
	rm ${FOR1}
fi

if [ -f ${TABLE_LIST} ]
then
	rm ${TABLE_LIST}
fi

if [ -f ${REJECTDIR}/*.rej ]
then
	rm ${REJECTDIR}/*.rej
fi

if [ -f ${PLOADSQL}/*.rej ]
then
	rm ${PLOADSQL}/*.rej
fi


exit