#!/usr/bin/ksh
#
# Copyright 2002-2003 by Sun Microsystems, Inc.
# All rights reserved.
#
# @(#)sstrfuncs	1.23 03/03/26
#
# Public NSM primitives and defines.
###############################################################################

#
# Defines

# Added for SSTR 1.2
SYSOS=`uname -r | awk -F'.' '{print $2}'`
## Solaris os
MINI_OS=8
MINI_UPDATE=6

# Boolean
NO=0
YES=1

# User input mode: silent or query.
SILENT=0
QUERY=1
MODE=$QUERY

# Station type: mgmt or agent.
STATION_MGMT="Management"
STATION_AGENT="Agent"
STATION_BOTH="Both"
STATION_TYPE=$STATION_MGMT
STATION_TYPE_ARG=$NO

# Log file - also set in configure
LOG=/var/sadm/install/logs/SUNWnsm.log

# Product name
PRODUCT_NAME="Sun StorEdge(TM) Topology Reporter"

# tmpscript name for verifying database backup
DBVERIFYSCRIPT=dbquery

#
# Public Primitive Functions

function is_user_root {

	user=`/bin/id | grep root | wc -l`
	if [ $user -eq 0 ]; then
		return 1
	fi
	return 0
}

function get_product_dir {

	product_dir=`/bin/pkginfo -r SUNWnsmm 2>&1`
	if [ $? -eq 0 ]; then
		product_dir=$product_dir/SUNWnsm
	else
		product_dir=/opt/SUNWnsm
		if [ ! -d $product_dir ]; then
			return 1
		fi
	fi
	echo "$product_dir"
	return 0
}

PRODUCT_DIR=`get_product_dir`

function logit {
	msg=$1

	if [ $MODE -eq $SILENT ]; then
		echo "$msg" >> $LOG 2>&1
	else
		echo "$msg" | tee -a $LOG 2>&1
	fi
}

function bailout {
	
	logit "Exiting, unsuccessful."
	exit 1
}

function get_station_type {

	if [ $STATION_TYPE_ARG -eq $YES ]; then
		# Station type specified on command line
		return
	fi

	echo
	echo "Select the type of station:"
	PS3="Pick one of the above: "
	select choice in "$STATION_MGMT" "$STATION_AGENT" "$STATION_BOTH"
	do case $choice in
			$STATION_MGMT)
				STATION_TYPE=$choice
				break;;
			$STATION_AGENT)
				STATION_TYPE=$choice
				break;;
                        $STATION_BOTH)
                                STATION_TYPE=$choice
                                break;;
		esac
	done
}

function validate_port_format {
	port=$1

	if [ -z "$port" ]; then
		return 1
	fi

	letter=`echo $port | grep '[^0-9]'`
	if [ -n "$letter" ]; then
		return 1
	elif [ $port -ge 1 ] && [ $port -le 65534 ]; then :
	else
		return 1
	fi

	return 0
}

function is_port_inuse {
	port=$1

	if [ -z "$port" ]; then
		return 1
	fi
	
	found=`netstat -a | grep -w $port`
	if [ -z "$found" ] && [ $? -eq 0 ]; then
		return 0
	fi

	return 1
}

function find_avail_port {
	port=$1

	validate_port_format $port
	if [ $? -ne 0 ]; then
		port=1
	fi		

	while [ $port -le 65534 ]; do
		is_port_inuse $port
		if [ $? -eq 0 ]; then
			echo "$port"
			return
		fi
		port=`expr "$port" + 1`
	done

	# Failure, 0 port means port not available
	echo "0"
}

function find_avail_port_not_in_list {
	port=$1
	used_ports="$2"

	done=0
	while [ $done -eq 0 ]; do
		port=`find_avail_port $port`

		found=0
		for pp in $used_ports; do
			if [ $pp -eq $port ]; then
				found=1
			fi
		done

		if [ $found -eq 0 ]; then
			done=1
		else
			port=`expr "$port" + 1`
		fi
	done

	echo "$port"
}

function is_yes {
	param="$1"

	if [ "$param" = "y" ] || [ "$param" = "Y" ] || \
		[ "$param" = "yes" ] || [ "$param" = "YES" ]; then
			return 0
	fi
	return 1	
}

function query_user {
	query="$1"

	response=`/bin/ckyorn -Q -d y -p "$query"`
	is_yes "$response"
	return $?
}

function check_disk_space {
	recSpace=$1
	availableSpace=`df -k $BASEDIR | awk '{print $4}' | egrep '^[0-9]'`
	if [ $recSpace -gt $availableSpace ]; then
		if [ $MODE = $SILENT ]; then
			logit "$BASEDIR does not have the recommended disk space ($recSpace KB) available."
			bailout
		fi
         	echo "$BASEDIR does not have the recommended disk space ($recSpace KB) available."
                query_user "Continue with installation"
                if [ $? -ne 0 ]; then
                        bailout
                fi
	fi

}

function validate_ip_addr {
	ipaddr=$1
	rval=0

	save=$IFS
	IFS='.'
	count=0
	for num in $ipaddr; do
		letter=`echo $num | grep '[^0-9]'`
	 	if [ -n "$letter" ]; then
	 		rval=1
			break
	 	elif [ $num -ge 0 ] && [ $num -le 255 ]; then :
		else
	 		rval=1
			break
	 	fi
		count=`expr "$count" + 1`
	done
	IFS=$save

	if [ $count -ne 4 ] || [ $rval -ne 0 ]; then
		return 1
	fi

	return 0
}

# Store fully qualified name and ip address
NSMFUNCS_FQN=
NSMFUNCS_IP=

function get_machine_info {

	# Get fully qualified machine name and IP address
	ftmp=/tmp/name$$
	uname -n | nslookup > $ftmp 2>&1
	if [ $? -eq 0 ]; then
		# Use a file descriptor other than 0.
		exec 4<$ftmp
		index=0
		while read -u4 line; do
			tmp[$index]="$line"
			index=`expr "$index" + 1`
		done
		# Close file descriptor.
		exec 4<&-

		index=0
		while [ $index -lt ${#tmp[@]} ]; do
			found=`echo ${tmp[$index]} | grep Name:`
			if [ -n "$found" ]; then
				NSMFUNCS_FQN=`echo ${tmp[$index]#Name:} | tr -d " "`
				index=`expr "$index" + 1`
				NSMFUNCS_IP=`echo ${tmp[$index]#Address:} | tr -d " "`
				break
			fi
			index=`expr "$index" + 1`
		done
	fi
	rm -f $ftmp

	if [ -z "$NSMFUNCS_FQN" ] || [ -z "$NSMFUNCS_IP" ]; then
		return 1
	fi

	return 0
}

function get_host_name {

	if [ -z "$NSMFUNCS_FQN" ]; then
		get_machine_info
		if [ $? -ne 0 ] || [ -z "$NSMFUNCS_FQN" ]; then
			return 1
		fi
	fi

	echo "$NSMFUNCS_FQN"

	return 0
}

function get_host_ip_addr {

	if [ -z "$NSMFUNCS_IP" ]; then
		get_machine_info
		if [ $? -ne 0 ] || [ -z "$NSMFUNCS_IP" ]; then
			return 1
		fi
	fi

	echo "$NSMFUNCS_IP"

	return 0
}

function setup_expect {

	# Look for expect
	exp=
	if [ -f $PRODUCT_DIR/sbin/expect ]; then
		exp=$PRODUCT_DIR/sbin/expect
	fi

	echo "$exp"

	if [ -z "$exp" ]; then
		return 1
	fi

	return 0
}

EXPECT=`setup_expect`

# Last package to add or rm.
NSMFUNCS_LAST_PACKAGE=

function get_last_package {

	echo "$NSMFUNCS_LAST_PACKAGE"
}

function find_packages {
	list=$1
	opts=$2
	results=

	for pkg in $list; do
		NSMFUNCS_LAST_PACKAGE=$pkg
		/bin/pkginfo -q $opts $pkg
		if [ $? -eq 0 ]; then
			if [ -z "$results" ]; then
				results="$pkg"
			else
				results="$results $pkg"
			fi
		fi
	done

	echo "$results"

	return 0
}

function get_install_patch {
	patch=$1
	patchID=`echo $patch | awk -F- '{print $1}'`
	installPatch=`showrev -p | awk '/'$patchID'/ {print $2}'`
	echo $installPatch
}
function get_install_patch_rev {
	patch=$1
	patchID=`echo $patch | awk -F- '{print $1}'`
	installPatchRev=`showrev -p | awk '/'$patchID'/ {print $2}' | sed 's/-//'`
	echo $installPatchRev
}

function get_correct_patch_rev {
	patch=$1
	correctPatch=`echo $patch | sed -e 's/-//'`
	echo $correctPatch
}

function find_package_version {
	list=$1

	version=`pkgparam $list VERSION | awk -F, '{print $1}'`

	echo "$version"
}

function find_partial_packages {
	list=$1

	find_packages "$list" "-p"

	return $?
}

function find_full_packages {
	list=$1

	find_packages "$list" "-i"

	return $?
}

function find_any_packages {
	list=$1
	results=

	results=`find_partial_packages "$list"`
	tmp=`find_full_packages "$list"`
	if [ -n "$results" ]; then
		results="$results $tmp"
	else
		results="$tmp"
	fi

	echo "$results"
}

function remove_packages {
	list=$1
	opts=$2
	
	for pkg in $list; do
		echo "/usr/sbin/pkgrm $opts $pkg"
		NSMFUNCS_LAST_PACKAGE=$pkg
		/usr/sbin/pkgrm $opts $pkg
		if [ $? -ne 0 ]; then
			return 1
		fi
	done

	return 0
}

function add_packages {
	list=$1
	opts=$2

	for pkg in $list; do
		echo "/usr/sbin/pkgadd $opts $pkg"
		NSMFUNCS_LAST_PACKAGE=$pkg
		/usr/sbin/pkgadd $opts $pkg
		if [ $? -ne 0 ]; then
			return 1
		fi
	done

	return 0
}

function package_param {
	pkg=$1
	opts=$2
	param=$3

	value=`pkgparam $opts $pkg $param`
	if [ $? -ne 0 ]; then
		return 1
	fi

	echo "$value"
	return 0
}

function find_version_eq_packages {
	pkgs=$1
	dir=$2
	results=

	pkgs=`find_full_packages "$pkgs"`

	for pkg in $pkgs; do
		cur=`package_param $pkg " " "VERSION"`
		new=`package_param $pkg "-d $dir" "VERSION"`
		if [ $cur = $new ]; then
			if [ -z "$results" ]; then
				results=$pkg
			else
				results="$results $pkg"
			fi
		fi
	done

	echo "$results"
}

# Last patch to add or rm.
NSMFUNCS_LAST_PATCH=

function get_last_patch {

	echo "$NSMFUNCS_LAST_PATCH"
}

function find_obsolete_patches {
	list="$1"
	results=

	for patch in $list; do
		NSMFUNCS_LAST_PATCH=$patch

		save_patch=$patch

		patch=`echo $patch | awk '{
			pos=index($patch, "T");
			s=$patch
			if (pos > 0)
				s=substr($patch, 2);
			print s
		}'`

		req_patch=`echo $patch| awk '{ \
								split($patch,pid,"-");print pid[1]}'`
		req_patchrev=`echo $patch | awk '{ \
								split($patch,pid,"-");print pid[2]}'`

		#check for obsoleted patch
		for obPatch in `/usr/sbin/patchadd -p | grep $req_patch | awk '{FS=":"; print $3}'`
		do
			if [[ $obPatch = *\, ]]; then
				obPatch=`echo $obPatch | cut -d"," -f1`
			fi

			obPatchid=`echo $obPatch| awk '{ \
				 split($obPatch,pid,"-");print pid[1]}'`

			if [ $obPatchid = $req_patch ]; then

				 obPatchidrev=`echo $obPatch| awk '{ \
					 split($obPatch,pid,"-");print pid[2]}'`

				[ "${obPatchidrev}" = "" ] && obPatchidrev=0
				[ "${req_patchrev}" = "" ] && req_patchrev=0
				if [[ $((obPatchidrev)) -ge $((req_patchrev)) ]]; then

					if [ -z "$results" ]; then
						results=$save_patch
					else
						results="$results $save_patch"
					fi
					break;
				fi
			fi
		done

	done

	echo "$results"

	return 0
}

function find_patches {
	list=$1
	results=

	for patch in $list; do
		save_patch=$patch

		patch=`echo $patch | awk '{
			pos=index($patch, "T");
			s=$patch
			if (pos > 0)
				s=substr($patch, 2);
			print s
		}'`

		req_patch=`echo $patch  | awk '{ \
			split($patch,pid,"-");print pid[1]}'`
		req_patchrev=`echo $patch | awk '{ \
			split($patch,pid,"-");print pid[2]}'`

		for instPatch in `/usr/sbin/patchadd -p | grep $req_patch | awk '{print $2}'`
		do
			patchid=`echo $instPatch | awk '{\
				split($instPatch,pid,"-");print pid[1]}'`

			patchrev=`echo $instPatch | awk '{\
				split($instPatch,pid,"-");print pid[2]}'`

			if [ ${patchid} = ${req_patch} ]; then
				[ "${patchrev}" = "" ] && patchrev=0
				[ "${req_patchrev}" = "" ] && req_patchrev=0
				if [ ${patchrev} -ge ${req_patchrev} ]; then
					if [ -z "$result" ]; then
						result=$save_patch
					else
						result="$result $save_patch"
					fi
				fi
			fi
		done
	done

	echo "$result"

	return 0
}

function remove_patches {
	list=$1
	opts=$2

	for patch in list ; do
		echo "/usr/sbin/patchrm $opts $patch"
		NSMFUNCS_LAST_PATCH=$patch
		/usr/sbin/patchrm $opts $patch
		if [ $? -ne 0 ]; then
			return 1
		fi
	done

	return 0
}

function add_patches {
	list=$1
	dir=$2

	for patch in $list ; do
		echo "/usr/sbin/patchadd -M $dir $patch"
		NSMFUNCS_LAST_PATCH=$patch
		/usr/sbin/patchadd -M $dir $patch
		if [ $? -ne 0 ]; then
			return 1
		fi
	done

	return 0
}

function apply_patches {
	patches="$1"
	dir=$2

	echo "Patches: $patches"

	obsolete=`find_obsolete_patches "$patches"`
	echo "Obsolete Patches: $obsolete"

	remaining=`list_a_minus_b "$patches" "$obsolete"`

	found=`find_patches "$remaining"`
	echo "Already Installed Patches: $found"

	needed=`list_a_minus_b "$remaining" "$found"`
	echo "Patches To Install: $needed"

	add_patches "$needed" "$dir"

	return $?
}

function reverse_list {
	list=$1
	results=

	for elem in $list; do
		if [ -z "$results" ]; then
			results="$elem"
		else
			results="$elem $results"
		fi
	done

	echo "$results"
}

function item_in_list {
	list=$1
	item=$2

	for elem in $list; do
		if [ $elem = $item ]; then
			return 1
		fi
	done

	return 0
}

function list_a_minus_b {
	list_a=$1
	list_b=$2
	results=

	results=$list_a
	for bb in $list_b; do
		results=
		for aa in $list_a; do
			if [ $bb = $aa ]; then :
				# b is in list a, so remove a from list
			else
				# b is not in list a, so keep a in list
				if [ -z "$results" ]; then
					results=$aa
				else
					results="$results $aa"
				fi
			fi
		done
		list_a=$results
	done

	echo "$results"
}

function list_rm_duplicates {
	list_a=$1
	results=

	for aa in $list_a; do
		found=0
		for rr in $results; do
			if [ $aa = $rr ]; then
				found=1
			fi
		done
		if [ $found -eq 0 ]; then
			if [ -z "$results" ]; then
				results=$aa
			else
				results="$results $aa"
			fi
		fi
	done

	echo "$results"
}

function spinner {
	ppid=$1
	msg=$2

	if [ -z "$ppid" ]; then
		return
	fi

	thingy[0]="-"
	thingy[1]="\\"
	thingy[2]="|"
	thingy[3]="/"
	
	done=0
	count=0
	while [ $done -eq 0 ]; do
		if [ -n "$msg" ]; then
			printf "$msg"
		fi
		printf "\r"
		printf "${thingy[$count]}"
		count=`expr "$count" + 1`
		if [ $count -ge ${#thingy[@]} ]; then
			count=0
		fi	
		sleep 1

		# Check parent is alive
		ps -p $ppid > /dev/null 2>&1
		if [ $? -ne 0 ]; then
			# Parent died
			done=1
		fi
	done	
}

NSMFUNCS_SPINNER_PID=
function spinner_start {
	msg="$1"

	ppid=$$
	spinner "$ppid" "$msg" &
	NSMFUNCS_SPINNER_PID=$!
	if [ $NSMFUNCS_SPINNER_PID -lt 0 ]; then
		NSMFUNCS_SPINNER_PID=
	fi
}

function spinner_stop {

	if [ -n "$NSMFUNCS_SPINNER_PID" ]; then
		done=0
		while [ $done -eq 0 ]; do
			/bin/kill -KILL $NSMFUNCS_SPINNER_PID
			if [ $? -eq 0 ]; then
				done=1
			else
				ps -p $NSMFUNCS_SPINNER_PID > /dev/null 2>&1
				if [ $? -eq 0 ]; then
					done=1
				else
					sleep 1
				fi
			fi
		done
		NSMFUNCS_SPINNER_PID=
		printf "\r                                         \r"
	fi
}

function append_file {
	src="$1"
	str="$2"

	if [ ! -f "$src" ]; then
		return 1
	fi

	found=`grep "^$str" "$src"`
	if [ -z "$found" ]; then
		echo "$str" >> "$src"
		echo "Appended file $src with $str." >> $LOG
	else
		echo "File $src already contains $str." >> $LOG
	fi

	return 0
}

function sed_file {
	src="$1"
	dst="$2"
	regexp="$3"

	if [ ! -f "$src" ]; then
		return 1
	fi

	ftmp=/tmp/nsmsed$$

	sed "$regexp" "$src" > $ftmp
	if [ $? -ne 0 ]; then
		return 1
	fi

	echo "diff "$src" $ftmp" >> $LOG
	diff "$src" $ftmp >> $LOG 2>&1
	if [ $? -eq 0 ]; then
		echo "ERROR: No sed file modification made!" >> $LOG
		return 1
	fi
	echo "Sed file modification made!" >> $LOG

	mv "$ftmp" "$dst"
	if [ $? -ne 0 ]; then
		return 1
	fi

	return 0
}

function update_nsm_scope {
        # Remove device scope string from the SCOPE string.
        CURRENT_SCOPE=`echo $SCOPE | sed -e "s/$NSM_DEVICE_SCOPE,//"`
        # Remove net.slp.useScopes= from the SCOPE string
        NSM_SCOPE="`echo $CURRENT_SCOPE | sed -e 's/net.slp.useScopes=//'`,$NSM_SCOPE"

        # Update the sstr.properties file with the new NSM_SCOPE
        conf_update
}

function update_slp {
	cp /etc/inet/slp.conf /etc/inet/slp.conf.bak
	sed -e "s/$SCOPE/net.slp.useScopes=$NSM_SCOPE/g" /etc/inet/slp.conf.bak \
	>/etc/inet/slp.conf;
	rm /etc/inet/slp.conf.bak
}

function verify_S86 {

	echo "Verifying Solaris 8 10/01 ..." >> $LOG
	cat /etc/release | read line
	echo "*** $line ***" >> $LOG
	oslevel=`echo $line | awk '{
		count=split($line, a, " ");
		if (count != 5)
			print "error: release count=" count;

		count=split(a[3], b, "/");
		if (count != 2)
			print "error: mm/yy count=" count;

		mm=b[1];
		yy=b[2];

		if (a[1] !~ /Solaris/) # os
			print "error: os name=" a[1];
		else if (a[2] !~ /8/) # version
			print "error: os version=" a[2];
		else if (a[5] !~ /SPARC/)
			print "error: os arch=" a[5];
		else if (yy < 01) # year 01
			print "error: os year=" yy " less than min year=01";
		else if (yy == 01 && mm != 10) # month 10
			print "error: os month=" mm " less than min month=10";
		else
			print "correct"
	}'`
	echo "*** $oslevel ***" >> $LOG
	if [[ $oslevel != "correct" ]]; then
		echo "Incorrect Solaris version." >> $LOG
		return 1
	fi

	echo "Correct Solaris version." >> $LOG
	return 0
}

### The following is added at SSTR 1.2

####
# Compare system OS update to the required
# OS update.
# Arguments:
#   $1: the first line of /etc/release
#   $2: required update
###
function CheckOSUpdate
{
	sysupdate=`echo "$1" |nawk '
    match($0, pat) {
        print substr($0, RSTART+2, RLENGTH-2)
    }' pat="\_u[0-9]+"`
	
	if [ -n "$sysupdate" ]; then
		if [ $sysupdate -lt $2 ]; then
			echo "unsupported OS update: $1; Required: update $2"
			return 2
		fi
	else
		echo "unsupported OS update: $1; Required: update $2"
		return 1
	fi

	return 0
}

####
# verify the OS (based on Solaris version,
# update, or assembled date)
#
####
function VerifySupportOS
{
	# exit if it's lower than the minimum OS
	if [ $SYSOS -lt $MINI_OS ]; then
		echo "unsupported Solaris OS: $SYSOS. Minimum OS required: $MINI_OS"
	    return 1	
	else
		# check update/assembled date if it's the same as minimum
		if [ $SYSOS -eq $MINI_OS ]; then
			# check for os update
			cat /etc/release |read releaseline
			CheckOSUpdate "$releaseline" "$MINI_UPDATE"
			return $?
		fi
	fi

	return 0
}

#
# start db process only 
#
function start_db_only
{
    ## this function will exit if any of shutdown fails
    insureshutdown

    # Check if PostgreSQL is already running.
    # If it's not running, then start it.
    if [ -n "`findprocpgsql`" ]; then
        echo "INFO: The PostgreSQL is already running."
    else
        echo "Starting PostgreSQL..."
        echo /usr/bin/su $SSTR_DBOWNER -c "$SSTR_DBSTART"
        /usr/bin/su $SSTR_DBOWNER -c "$SSTR_DBSTART"
        check_db_status
        if [ $? -ne 0 ]; then
            echo "ERROR: Starting PostgreSQL failed..."
            exit 1  
        fi
    fi
}

##
# creates an sql file to convert old annotations (string) to new
# (byte array)
# Args:
#  $1 pg_dump file created by dumping old annotation table
#  $2 new sql file - running it will convert to new annotations
function update_annotation
{
    oldannot=$1
    newannot=$2

    cat - >$newannot <<EOT
-- temp table (looks like old annotation table - change so that old
-- annotations are copied into here)
CREATE TABLE Annotation2 ( --- No key, duplicates are allowed.
    text TEXT,
    ipaddr VARCHAR,
    StorageHostKey VARCHAR, -- may be NULL,This will be pointing to actual
    HBAKey VARCHAR, -- may be NULL          physical table.
    StorageSubSystemKey VARCHAR, -- may be NULL
    SwitchKey VARCHAR -- may be NULL
);

-- converts old string based annotations (stored into 'annotation2' table)
-- to byte array annotations (stored in new 'annotation' table)
CREATE FUNCTION convert_annotations () RETURNS INTEGER AS '
DECLARE
    annotrec            RECORD;
    first               BOOLEAN;
    counter             INTEGER;
    insertString        VARCHAR;
    charstr             VARCHAR;
BEGIN
    FOR annotrec IN
        SELECT * FROM annotation2
    LOOP
        first := ''t'';
        insertString := ''INSERT INTO annotation (ipaddr,storagehostkey,hbakey,storagesubsystemkey,switchkey,text) VALUES ( '';
        IF (annotrec.ipaddr ISNULL)
        THEN
            insertString := insertString || '' null, '';
        ELSE
                insertString := insertString || '''''''' || annotrec.ipaddr || '''''''' || '','';
        END IF;
        IF (annotrec.storagehostkey ISNULL)
        THEN
            insertString := insertString || '' null, '';
        ELSE
            insertString := insertString || '''''''' || annotrec.storagehostkey || '''''''' || '','';
        END IF;
        IF (annotrec.hbakey ISNULL)
        THEN
            insertString := insertString || '' null, '';
        ELSE
            insertString := insertString || '''''''' || annotrec.hbakey || '''''''' || '','';
        END IF;
        IF (annotrec.storagesubsystemkey ISNULL)
        THEN
            insertString := insertString || '' null, '';
        ELSE
            insertString := insertString || '''''''' || annotrec.storagesubsystemkey || '''''''' || '','';
        END IF;
        IF (annotrec.switchkey ISNULL)
        THEN
            insertString := insertString || '' null, '';
        ELSE
            insertString := insertString || '''''''' || annotrec.switchkey || '''''''' || '','';
        END IF;
        IF ( annotrec.text ISNULL )
        THEN
            insertString := insertString || '' null '';
        ELSE
            counter := 1;
            insertString := insertString || ''''''{ '';
            LOOP
                IF (counter <= char_length(annotrec.text))
                THEN
                    IF first
                    THEN
                        first := ''f'';
                    ELSE
                        insertString := insertString || '', '';
                    END IF;
                    charstr := substring(annotrec.text from counter for 1);
                    insertString := insertString || ascii(charstr);
                    counter := counter + 1;
                ELSE
                    EXIT;
                END IF;
            END LOOP;
            insertString := insertString || ''}'''''';
        END IF;
        insertString := insertString || '');'';
        EXECUTE insertString;
    END LOOP;

    RETURN 1;
END;
' LANGUAGE 'plpgsql';

-- this part was created by the pg_dump call, and then altered by sed to change
-- "annotation" to "annotation2"
EOT

    # add old file, changing table name
    /usr/bin/sed -e 's/INSERT INTO \"annotation\"/INSERT INTO \"annotation2\"/' $oldannot >> $newannot

    # add the call to function that transforms and clean up
    cat - >>$newannot <<EOT
-- convert tables
select convert_annotations();
drop table annotation2;
drop function convert_annotations();
EOT

}
    

#
# back up db table
#  Arguments: 
#   $1  db backup directory
#
function run_db_upgrade
{
    dbdir=$1

    logit "An ESM 1.0 database has been detected. It contains information that can be automatically migrated to the ESM 1.2 database."

    if [ $MODE -ne $SILENT ]; then
	query_user "Would you like to upgrade the database"
	[ $? -ne 0 ] && return 0
    fi

    # check file owner for dbstart script (need sstr001 to run)
    owner=`ls -l $SSTR_DBSTART |awk '{print $3}'` 
    if [[ ! $owner = "$SSTR_DBOWNER" ]]; then
	logit "ERROR: unable to upgrade. Please check file permission of $SSTR_DBSTART." 
        return 1
    fi
 
    logit "Backing up $OLD_VERSION database into $dbdir ..."
    spinner_start

    ## start db only
    start_db_only >> $LOG 2>&1

    ## create dbdir
    if [ -d $dbdir ]; then
	echo "Removing previous $dbdir..." >> $LOG 2>&1
        /usr/bin/rm -rf $dbdir
    fi
    /usr/bin/mkdir -p $dbdir
    /bin/chmod 777 $dbdir

    ## back 4 tables
    /usr/bin/su $SSTR_DBOWNER -c ". $SSTR_DBBIN/postgres.env;\
    LD_LIBRARY_PATH=$SSTR_DBHOME/lib:$SSTR_DBHOME/nsm1/lib;\
    export LD_LIBRARY_PATH;\
    $SSTR_PGDUMP -a -D -i -R -t annotation -f $dbdir/tmp.annotation.insert;\
    $SSTR_PGDUMP -a -D -i -R -t guiattribute -f $dbdir/guiattribute.insert ;\
    $SSTR_PGDUMP -a -D -i -R -t userpo -f $dbdir/userpo.insert;\
    $SSTR_PGDUMP -a -D -i -R -t alarm -f $dbdir/alarm.insert;" >> $LOG 2>&1

    # annotation table has field changing from string to byte array to
    # support different charsets.  Add SQL to convert the old into the new.
    update_annotation "$dbdir/tmp.annotation.insert" "$dbdir/annotation.insert"
    # rm $dbdir/tmp.annotation.insert >> $LOG 2>&1

    # save a query for future verify
    make_dbfile "$dbdir/$DBVERIFYSCRIPT"
    create_query $dbdir "db_before" $DBVERIFYSCRIPT >> $LOG 2>&1 

    spinner_stop
    logit "Database backup is done."
    return 0
}

#
# clean up temp directory for the database backup
#
function clean_dbtmp
{
    if [ -d $dbdir ]; then
        echo "Cleaning up $dbdir..."
        /usr/bin/rm -rf $dbdir
    fi
}

#
# stop database 
#
function stop_db
{
    if [ -n "`findprocpgsql`" ]; then
        echo "Shutting down PostgreSQL..."
        /usr/bin/su $SSTR_DBOWNER -c "$SSTR_DBSTOP"
        sleep 3
    else
        echo "INFO: PostgreSQL is already shutdown."
    fi
}

#
# restore database tables 
#
function restore_db
{
    dbdir=$1

    # start db only
    start_db_only
       
    #restore db
    echo "Restoring database tables from $OLD_VERSION $dbdir"
    /usr/bin/su $SSTR_DBOWNER  -c ". $SSTR_DBBIN/postgres.env;\
    LD_LIBRARY_PATH=$SSTR_DBHOME/lib:$SSTR_DBHOME/nsm1/lib;\
    export LD_LIBRARY_PATH;\
    $SSTR_PSQL -f $dbdir/annotation.insert;\
    $SSTR_PSQL -f $dbdir/guiattribute.insert;\
    $SSTR_PSQL -f $dbdir/userpo.insert; \
    $SSTR_PSQL -f $dbdir/alarm.insert; "

    # query after the restore to verify 
    create_query $dbdir "db_after" $DBVERIFYSCRIPT
    verify_restore $dbdir
    [ $? -ne 0 ] && return 1

    # stop db
    stop_db
	
    # clean up db 
    clean_dbtmp

    return 0
}

#
# helper method for checking db status
#
function count_keywords
{
    num=0

    new_version=$DB_NEWVERSION

    out=`/usr/bin/su $SSTR_DBOWNER -c ". $SSTR_DBBIN/postgres.env;\
    LD_LIBRARY_PATH=$SSTR_DBHOME/lib:$SSTR_DBHOME/nsm1/lib;\
    export LD_LIBRARY_PATH;\
    $SSTR_HOME/util/pgsql/bin/psql -V"`

    echo $out | /usr/bin/grep $new_version > /dev/null

    if [ $? -eq 0 ]; then
        id_words="DEBUG:  database system is ready"
    else
        ## not checking for old version yet
        id_words="DEBUG:  database system is in production state"
    fi
       
    [[ ! -f $NSMLOG ]] && sleep 2
    num=`/usr/bin/grep -c "$id_words" $NSMLOG`
    echo "$num"
}

#
# check db status 
#
function check_db_status
{
    counter=0
    max_time=5

    # in a loop
    while :
    do
        sleep 5
        find_after=0
        ## grep for keyword, if found in the log, break
        find_after=`count_keywords`
        if [ $find_after -eq 1 ]; then
            break
        fi

        ## if counter is over 5, exit with failure
        if [ $counter -gt $max_time ]; then
            echo "ERROR: Starting PostgreSQL failed."
            return 1
        fi
        counter=`expr $counter + 1`
    done

    echo "INFO: PostgreQL has been started."
    return 0
}

#
# make a db script for verifying db
# Arguments:
#  $1: file name
#
function make_dbfile 
{
    tmpfile=$1	

    echo "/* Annotation table */
    SELECT ipaddr,
       storagehostkey,
       hbakey,
       storagesubsystemkey,
       switchkey
    FROM   Annotation
    ORDER BY ipaddr, storagehostkey, hbakey,
         storagesubsystemkey, switchkey
    ;
    /* UserPO table */
    SELECT auser,
       role
    FROM   UserPO
    ORDER BY auser, role
    ;
    /* Alarm */
    SELECT subject,
	source,
	subjecttime,
	sourcetime,
	sourcesequencenumber,
	postsequencenumber,
	topic,
	severity,
	type,
	agenttype,
	agenturl,
	ttl,
	serializedevent
    FROM alarm
    ORDER BY subject, source, subjecttime,
        sourcetime, sourcesequencenumber, postsequencenumber,
        topic, severity, type, agenttype, agenturl,
        ttl, serializedevent
    ;

    /* GUIattribute table */
    SELECT akey,
        value
    FROM guiattribute
    ORDER BY akey, value
    ;
    " > $tmpfile

    # change owner
    /usr/bin/chown $SSTR_DBOWNER:$SSTR_DBOWNER $tmpfile 
}

#
# query database and save result for checking backup /restore
# Arguments:
#  $1  database directory
#  $2  database files name
#  $3  script to run the database query
function create_query
{
    dbdir=$1
    dbfile=$2
    dbscript=$3

    #restore db
    echo "Creating $OLD_VERSION query tables for database verification..."
    /usr/bin/su $SSTR_DBOWNER  -c ". $SSTR_DBBIN/postgres.env;\
    LD_LIBRARY_PATH=$SSTR_DBHOME/lib:$SSTR_DBHOME/nsm1/lib;\
    export LD_LIBRARY_PATH;\
    $SSTR_PSQL -f $dbdir/$dbscript;" > $dbdir/$dbfile
}

#
# verify db restore
#  Arguments: 
#   $1 db backup directory 
#
function verify_restore
{
    dbdir=$1

    if [ -f $dbdir/db_before -a -f $dbdir/db_after ]; then
	echo "Comparing $dbdir/db_before and $dbdir/db_after..."
	/bin/diff $dbdir/db_before $dbdir/db_after 
	[ $? -eq 0 ] && return 0
    else
	echo "Unable to find: $dbdir/db_before or $dbdir/db_after"
    fi		

    return 1
}
