#!/bin/ksh

# addle:
# 1. To muddle; confuse: "My brain is a bit addled by whiskey" (Eugene O'Neill).
# 2. To become rotten, as an egg.


if [ $# != 3 ]
then
	print "Usage: $0 table user password"
	print "(creates 'TABLE.sql' script to drop, build & import table)"
	exit
fi

TAB=$(echo "${1}" | tr '[a-z]' '[A-Z]')

UID=$(echo "${2}" | tr '[a-z]' '[A-Z]')

PWD=$(echo "${3}" | tr '[a-z]' '[A-Z]')


exp userid=${UID}/${PWD} file=${TAB}-schema.dmp \
	tables=${TAB} rows=n grants=y indexes=y constraints=y

imp userid=${UID}/${PWD} file=${TAB}-schema.dmp \
	full=y indexfile=${TAB}.sql

# --Perform various processing on the INDEXFILE script.
gawk 'BEGIN {
	print "lock table '${TAB}' in exclusive mode;"

	print "!exp userid='${UID}'/'${PWD}' file='${TAB}'-data.dmp " \
		"tables='${TAB}' rows=y grants=n indexes=n constraints=n " \
		"compress=n feedback=100000"
	print "drop table '${TAB}';"
	finalimp = 0
}
/^CONNECT/ { next }
{
	if($0 ~ /^REM/) sub(/^REM/,"");
	if($0 ~ /CREATE TABLE/ && finalimp == 0) finalimp = 1;

	print
	if(finalimp == 1 && $0 ~ /;/)
	{
		print "!imp userid='$UID'/'$PWD' file='${TAB}'-data.dmp " \
			"tables='${TAB}' ignore=y rows=y grants=n indexes=n " \
			"feedback=100000"

		finalimp = 2
	}
}' ${TAB}.sql > ${TAB}.sql.new

mv ${TAB}.sql.new ${TAB}.sql

# --Extract the grants.
print "connect ${UID}/${PWD}
set pages 0 lin 32767 feedback off verify off heading off

	select 'grant '||PRIVILEGE||' on '||TABLE_NAME||' to '||
			GRANTEE||' with grant option;'
		from user_tab_privs
		where owner='${UID}' and table_name='${TAB}'
			and GRANTABLE='YES';

	select 'grant '||PRIVILEGE||' on '||TABLE_NAME||' to '||
			GRANTEE||';'
		from user_tab_privs
		where owner='${UID}' and table_name='${TAB}'
			and GRANTABLE='NO';

" | sqlplus -silent /nolog | gawk '!/^Connected\.$/' >> ${TAB}.sql

# --Extract the triggers.
print "connect ${UID}/${PWD}
set lin 32767 feedback off serveroutput on size 1000000

DECLARE
	mydesc		user_triggers.description%TYPE;
	mywhen		user_triggers.when_clause%TYPE;
	l_cursor	integer default dbms_sql.open_cursor;
	l_long_val	varchar2(2000);
	l_long_len	number;
	l_curpos	number;
	str1		integer;
	str2		integer;
BEGIN
	dbms_sql.parse(l_cursor,
		'select description, when_clause, trigger_body
			from user_triggers
			where table_name = ''${TAB}''',
		dbms_sql.native);
	dbms_sql.define_column(l_cursor, 1, mydesc, 2000);
	dbms_sql.define_column(l_cursor, 2, mywhen, 2000);
	dbms_sql.define_column_long(l_cursor, 3);
	str1 := dbms_sql.execute(l_cursor);
	while dbms_sql.fetch_rows(l_cursor) > 0 loop
		dbms_sql.column_value(l_cursor, 1, mydesc);
		dbms_output.put('create or replace TrIgGer');
		l_long_val := mydesc;
		l_long_len := length(mydesc);
		str1 := 1;
		str2 := instr(l_long_val, chr(10), str1);
		while str2 != 0 loop
			dbms_output.put_line('\t'||
				substr(l_long_val, str1, str2 - str1));
			str1 := str2 + 1;
			str2 := instr(l_long_val, chr(10), str1);
		end loop;
		dbms_output.put(substr(l_long_val, str1));
		dbms_sql.column_value(l_cursor, 2, mywhen);
		if mywhen is not null then
			dbms_output.put('WHEN (');
			l_long_val := mywhen;
			l_long_len := length(mywhen);
			str1 := 1;
			str2 := instr(l_long_val, chr(10), str1);
			while str2 != 0 loop
				dbms_output.put_line('\t'||
					substr(l_long_val, str1, str2 - str1));
				str1 := str2 + 1;
				str2 := instr(l_long_val, chr(10), str1);
			end loop;
			dbms_output.put(substr(l_long_val, str1));
			-- The following put_line shouldn't be necessary...?
			dbms_output.put_line('');
			dbms_output.put_line(')');
		end if;
		l_curpos := 0;
		loop
			dbms_sql.column_value_long(l_cursor, 3, 250,
				l_curpos, l_long_val, l_long_len);
			l_curpos := l_curpos + l_long_len;
			str1 := 1;
			str2 := instr(l_long_val, chr(10), str1);
			while str2 != 0 loop
				dbms_output.put_line('\t'||
					substr(l_long_val, str1, str2 - str1));
				str1 := str2 + 1;
				str2 := instr(l_long_val, chr(10), str1);
			end loop;
			dbms_output.put('\t'||substr(l_long_val, str1));
			exit when l_long_len = 0;
		end loop;
		dbms_output.put_line('');
		dbms_output.put_line('/');
	end loop;
	dbms_sql.close_cursor(l_cursor);
END;
/
" \
	| sqlplus -silent /nolog \
	| gawk '/^Connected\.$/ { next }
		{
			if($0 ~ /^\t/) sub(/^\t/,"")
			if($0 ~ /TrIgGer\t/) sub(/TrIgGer\t/,"trigger ")
			print
		}' >> ${TAB}.sql

# --Extract the comments.
print "connect ${UID}/${PWD}
set pages 0 lin 32767 feedback off verify off heading off

	select 'comment on table '||TABLE_NAME||' iS #'||
			COMMENTS||'#;'
		from user_tab_comments
		where table_name='${TAB}';

	select 'comment on column '||TABLE_NAME||'.'||COLUMN_NAME||' iS #'||
			COMMENTS||'#;'
		from user_col_comments
		where table_name='${TAB}';" \
	| sqlplus -silent /nolog \
	| gawk '/^Connected\.$/ || /^$/ || /##;$/ { next }
		{
			if($0 ~ /'"'"'/) gsub(/'"'"'/,"'"''"'")
			if($0 ~ /iS #/) sub(/iS #/,"is '"'"'")
			if($0 ~ /#;$/) sub(/#;$/,"'"'"';")
			print
		}' >> ${TAB}.sql

# --Attach an "export show=y" for reference (Oracle 7 needs constraints here).
imp userid=${UID}/${PWD} file=${TAB}-schema.dmp show=y full=y 2>&1 \
	| gawk '!/^$/ {sub(/^ "/,""); sub(/"$/,""); print "--"$0}' >> ${TAB}.sql