#!/bin/bash
#
# sp_dbinit
#
# (c) 2012 SUSE Linux GmbH, Germany.
# GNU Public License. No warranty.
#
# Version: 2012-03-20
#
# 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_SGA=146800640
DB_PGA=16777216
DB_TMPSQL="/tmp/dbsetup.sql"
FILL_LOG="/tmp/$(basename $0).log"


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


function calc_ram() {
	DB_SGA=$( awk '$1=="MemTotal:" {
		sm=146800640 
		s=$2*1024*0.25; if (s <= sm) s=sm
		printf "DB_SGA=%i\n", s}' /proc/meminfo
	)
	DB_PGA=$( awk '$1=="MemTotal:" {
		pm=16777216 
		p=$2*1024*0.1; if (p <= pm) p=pm
		printf "DB_PGA=%i\n", p}' /proc/meminfo
	)
}


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}')
	calc_ram

	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"
	echo "DB_SGA=$DB_SGA"
	echo "DB_PGA=$DB_PGA"
}


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
	echo -n "DB_SID: "; read DB_SID
	echo -n "DB_HOME: "; read DB_HOME
	DISK_FREE=$(df -hP $(dirname $DB_HOME) |\
                 awk '$1!="Filesystem" {print $4}')
	echo "DISK_FREE=$DISK_FREE"
	echo	
	ask_yesno
}


function stop_db() {
	/etc/init.d/susemanager stop
	test -x /etc/init.d/oracle-xe && /etc/init.d/oracle-xe stop
	test -x /etc/init.d/oracle && /etc/init.d/oracle stop

	LN=$(ps aux | awk '$1=="LISTENER" {print $1}') 
	test -z $LN || ( kill -2 $LSN ; kill -9 $LSN )
}


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

	test -r $DB_HOME/oradata/XE/data_01.dbf && exit 4
	mkdir -p $DB_HOME/oradata/XE
	chown $DB_ADM $DB_HOME/oradata/XE

	TAB=/etc/oratab
	test -r $TAB || (
		 echo "*:${DB_HOME}/app/oracle/product/10.2.0/server:N" >$TAB
		 echo "XE:${DB_HOME}/app/oracle/product/10.2.0/server:N" >>$TAB
	)
	cp -a $DB_HOME/app/oracle/product/10.2.0/server/dbs/init.ora $DB_HOME/app/oracle/product/10.2.0/server/dbs/initxe.ora

	echo -e "9055\n\n$DB_SYSPWD\n$DB_SYSPWD\n" | /etc/init.d/oracle-xe configure
	sed -i s,:/usr/lib/oracle/10.2.0.4/client.*:,:${DB_HOME}/app/oracle/product/10.2.0/server:,g /etc/oratab
	. /etc/profile.d/oracle.sh
	cp -a /etc/tnsnames.ora /etc/tnsnames.ora.BAK
	cp -a $DB_HOME/app/oracle/product/10.2.0/server/network/admin/tnsnames.ora /etc
	restorecon -v /etc/tnsnames.ora
	/etc/init.d/oracle-xe start || exit 5

	echo "create smallfile tablespace data_tbs datafile '/usr/lib/oracle/xe/oradata/XE/data_01.dbf' SIZE 3800M;
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 \"_optimizer_filter_pred_pullup\"=false scope=spfile;
alter system set \"_optimizer_cost_based_transformation\"=off scope=spfile;
quit
" >$DB_TMPSQL

	cat $DB_TMPSQL
	exit

	echo "su -s /bin/bash - $DB_ADM -c \"ORACLE_SID=$DB_SID sqlplus sys/\"$DB_PASS\"@$DB_SID as sysdba @/tmp/dbsetup.sql;\""
	su -s /bin/bash - $DB_ADM -c "ORACLE_SID=$DB_SID sqlplus sys/\"$DB_PASS\"@$DB_SID as sysdba @/tmp/dbsetup.sql;"

#	rm $DB_TMPSQL
}


function setup_db() {
	echo "Creating database. Please wait..."
	echo
	stop_db

	/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=$DB_SGA scope=spfile;
alter system set pga_aggregate_target=$DB_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 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)
		shift
		if test -r $1 ; then
			echo "Reading parameters from file $1"
			source $1

			DISK_FREE=$(df -hP $(dirname $DB_HOME) |\
		 		awk '$1!="Filesystem" {print $4}')
			echo "DB_ADM: $DB_ADM"
			echo "DB_SYSPWD: $DB_SYSPWD"
			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"
			echo
			ask_yesno
			echo
		else	
			ask_setup
		fi
		# 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
	;;
	-v|--version)
		echo -n "$(basename $EXE) "
		head -11 $EXE | grep "^# Version: "
	;;
	*)
        	help
	;;
esac
#
