#!/bin/bash
#
# sp_dbimport
#
# (c) 2012 SUSE Linux GmbH, Germany.
# GNU Public License. No warranty.
#
# Version: 2012-03-15
#
# mostly taken from migrate.sh
#

EXE="$0"

SM_CFG="/etc/rhn/rhn.conf"

DB_ADM="oracle"
DB_SID="susemanager"
DB_HOME="/opt/apps/oracle/oradata/"
DB_SID="susemanager"
DB_SIZE="2000M"
DB_TMPSQL="/tmp/dbsetup.sql"
FILL_LOG="/tmp/$(basename $0).log"


function help() {
	echo "usage: $0 [OPTION]"
	echo
	echo "fill SUSE Manager database."
	echo
	echo " -g	guess parameters"
	echo " -c	create empty database"
	echo " -i	import dump into empty database"
	echo " -v	show version"
	echo " -h	show help"
	echo
	echo "(mostly taken from migrate.sh)"
}


function ask_yesno() {
	read -p "Continue [yes/no]? " -a YN
	YN=$(echo $YN | tr "YES" "yes")
	test $YN = "yes" -o $YN = "y" || exit 3
}


function guess_db() {
	DB_USER=$(awk '$1=="db_user" {print $3}' $SM_CFG)
	DB_PASS=$(awk '$1=="db_password" {print $3}' $SM_CFG)
	DB_SID=$(awk '$1=="db_name" {print $3}' $SM_CFG)
	id $DB_ADM >/dev/null || DB_ADM=""
	DB_HOME=$(awk -F":" '$1=="'${DB_ADM}'" {print $6}' /etc/passwd)
	DISK_FREE=$(df -hP $(dirname $DB_HOME) |\
		 awk '$1!="Filesystem" {print $4}')
	echo "DB_ADM: $DB_ADM"
	echo "DB_HOME: $DB_HOME"
	echo "DISK_FREE: $DISK_FREE"
	echo "DB_USER: $DB_USER"
	echo "DB_PASS: $DB_PASS"
	echo "DB_SID: $DB_SID"
}


function ask_setup() {
	echo "Need some parameters."
	echo -n "DB_ADM: "; read DB_ADM
	echo -n "DB_SYSPWD: "; read DB_SYSPWD
	echo -n "DB_USER: "; read DB_USER
	echo -n "DB_PASS: "; read DB_PASS
	# TODO DB_SID, DB_HOME s. below
	# echo -n "DB_HOME: "; read DB_HOME
	# echo -n "DB_SID: "; read DB_SID
	echo "DB_SID: $DB_SID"
	echo "DB_HOME: $DB_HOME"
	DISK_FREE=$(df -hP $(dirname $DB_HOME) |\
                 awk '$1!="Filesystem" {print $4}')
	echo "DISK_FREE: $DISK_FREE"
	echo	
	ask_yesno
}


function setup_xe() {
	echo "Creating XE database. Please wait..."


}


function setup_db() {
	echo "Creating database. Please wait..."
	/opt/apps/oracle/setup "$DB_SYSPWD"
	find /opt/apps/oracle/product/ -perm -4000 -exec chmod -s {} \;
	cp /opt/apps/oracle/product/11gR2/dbhome_1/network/admin/tnsnames.ora /etc
	# TODO compute_oracle_mem from migrate.sh ?
	# TODO $DB_HOME/$DB_SID/data_01.dbf instead of '/opt/apps/oracle/oradata/susemanager/data_01.dbf'

	echo "select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
shutdown immediate;
startup mount;
alter system enable restricted session;
alter system set job_queue_processes=0;
alter database open;
alter database character set internal_use utf8;
shutdown immediate;
startup;
select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
create smallfile tablespace data_tbs datafile '/opt/apps/oracle/oradata/susemana
ger/data_01.dbf' size $DB_SIZE autoextend on blocksize 8192;
create user $DB_USER identified by \"$DB_PASS\" default tablespace data_tbs;
grant dba to $DB_USER;
alter system set processes = 400 scope=spfile;
alter system set deferred_segment_creation=FALSE;
alter system set sga_target=$sga scope=spfile;
alter system set pga_aggregate_target=$pga scope=spfile;
alter system set nls_territory='AMERICA' scope=spfile;
BEGIN
dbms_sqltune.set_auto_tuning_task_parameter( 'ACCEPT_SQL_PROFILES', 'TRUE');
END;
/
quit
" >$DB_TMPSQL

	su -s /bin/bash - $DB_ADM -c "ORACLE_SID=$DB_SID sqlplus / as sysdba @$DB_TMPSQL;"
	rm $DB_TMPSQL
}


function import_db() {
	echo "Need some parameters."
	echo -n "DB_USER: "; read DB_USER
	echo -n "DB_SYSPWD: "; read DB_SYSPWD
	echo -n "DB_SID: ";  read DB_SID
	echo -n "DUMP_FILE: "; read DUMP_FILE
	echo "DB_ADM: $DB_ADM"
	echo "FILL_LOG: $FILL_LOG"
	DISK_FREE=$(df -hP $(dirname $DB_HOME) |\
		 awk '$1!="Filesystem" {print $4}')
	echo "DISK_FREE: $DISK_FREE"
	echo
	ask_yesno

	# TODO check for existing

	echo "Filling database. Please wait..."

	# TODO import
	su -s /bin/bash - $DB_ADM -c "imp system/${DB_SYSPWD}@$DB_SID \
		fromuser=$DB_USER touser=$DB_USER ignore=y \
		file=$DUMP_FILE log=$FILL_LOG"
	RC=$?
	test $RC -eq 0 && echo "Database dump successfully imported."
	echo
	echo "RETURN: $RC"
	DB_HOME=$(awk -F":" '$1=="'${DB_ADM}'" {print $6}' /etc/passwd)
	DB_SIZE=$(du -shP $DB_HOME | awk '{print $1}')
	echo "DB_HOME: $DB_HOME"
	echo "DB_SIZE: $DB_SIZE"
	echo "FILL_LOG: $FILL_LOG"
}


function upgrade_schema() {
	spacewalk-schema-upgrade
	if [ $ORACLE_VERSION = "XE" ]; then
		su -s /bin/bash - oracle -c "ORACLE_SID=$DB_SID \
		sqlplus sys/\"$DB_SYSPWD\"@$DB_SID as sysdba <<ENDPLUS @/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/rdbms/admin/utlrp.sql; exit;ENDPLUS"
	else
		su -s /bin/bash - oracle -c "ORACLE_SID=$DB_SID \
		sqlplus sys/${DB_SYSPWD}@$DB_SID as sysdba <<ENDPLUS @/opt/apps/oracle/product/11gR2/dbhome_1/rdbms/admin/utlrp.sql; exit;ENDPLUS"
	fi
}


# main()
case "$1" in
	-g|--guess)
		guess_db
	;;
	-c|--create)
		ask_setup
		# TODO DB_HOME
		DB_HOME=$(awk -F":" '$1=="'${DB_ADM}'" {print $6}' /etc/passwd)
		if [ $DB_HOME = "/usr/lib/oracle/xe" ]; then
			setup_xe
		else
			setup_db
		fi
		upgrade_schema
	;;
	-i|--import)
		import_db
	;;
	-v|--version)
		echo -n "$(basename $EXE) "
		head -11 $EXE | grep "^# Version: "
	;;
	*)
        	help
	;;
esac
#
