Article
Listing 1
Listing 1 db_update source
#!/bin/ksh
# Revision 1.0 2001/03/17 18:07:03 eds
# db_update: template for Controlling Enterprise Database Releases
# Author: Ed Schaefer
typeset -l cmmd # downshift the command
typeset -i ret_val # integer
# set up previous and current version
PREVIOUS="2001.1.0"
VERSION="2001.2.0"
# set the database name
DBNAME="testdb"
# Set vendor specfic environment
ENGINE_UP="eval onstat - | grep "Up" | wc -l"
SESSIONS="eval onstat -g sql | grep $DBNAME | wc -l"
ONSTAT_SES="eval onstat -g sql | grep $DBNAME | cut -f1 -d\" \""
ONSTAT_SQL="eval onstat -g sql"
KILL="onmode -z"
INTERLOG=dbchanges.$$
INTERFACE_CMD="dbaccess"
DBCOMMAND="eval $INTERFACE_CMD -e $DBNAME"
DBCOMMAND_OTF="eval $INTERFACE_CMD -e $DBNAME >> $INTERLOG"
# misc. variables
CLEAR="tput clear"
PROGRAM_NAME=$0
cmmd=$1
$CLEAR
# this function contains the RCS revision number
# of this shell script. Return the decimal part of the
# revision string.
function rcs_function {
local str
str="$Revision: 1.0 $"
echo $str|
awk ' {
for (i=1; i <= NF; i++)
if ($i ~ /^[+-]?[0-9]+[.]?[0-9]*$/)
{
print $i
exit
}
# no numeric version found
print "X"
} '
} # end rcs_function
# yesorno: This function takes one argument and if the
# value is Y or y returns 1 else it returns 0. Pressing
# <CR> returns 0.
# usage: $(yesorno $answer)
function yesorno {
case $1 in
y|Y) echo 1;;
*)
echo 0;;
esac
} # end yesorno
function usage {
printf "%s\n" "Usage: $PROGRAM_NAME <update/undo>"
exit 1
} # end usage
# attempt to open the database. return exit code:
# 0 is good and any other value is bad.
function database_check {
# first argument is database name
$INTERFACE_CMD $1 2> /dev/null <<MSG
MSG
echo $?
} # end database_check
# Is the engine running? return 0 if NOT running and 1 if
# is.
function database_engine_running {
if [ `$ENGINE_UP` -eq 0 ]
then # engine is not running
echo 0
else
echo 1
fi
} # end database_engine_running
function main_driver {
case $cmmd in
"update")
printf "\n%s\n" "You are about to UPDATE your $DBNAME database structure"
printf "for the *** %s *** release.\n" $VERSION
LOGNAME=$VERSION"_update.log" # update log name
;;
"undo")
printf "\n%s\n" "You are about to UNDO the $DBNAME database structure"
printf "of changes previously made for the ***** %s ***** release. \n"\
$VERSION
LOGNAME=$VERSION"_undo.log" # undo log name
;;
*)
printf "\n%s\n" "Illegal argument"
usage
;;
esac
printf "\n%s" "Do you wish to continue ? (Y/N) "
read answer
if [ $(yesorno $answer) -eq 0 ]
then
printf "\n%s\n" "Changes ABORTED ..."
exit 1
fi
} # end main_driver
# This function checks if any users are still using the database and if
# there are prompts whether to terminate them and continue processing. Y
# continues processing, but N exits.
function check_session {
sessions_cnt=`$SESSIONS`
if [ $sessions_cnt -gt 0 ]
then
printf "\n%s\n" "There are "$sessions_cnt" sql sessions running that are using"
printf "%s\n" "the $DBNAME database. "
printf "\n%s\n" "*** TO CONTINUE, TERMINATE THESE SESSIONS *** "
printf "\n%s" "Do you wish to continue ? (Y/N) "
read killme
if [ $(yesorno $killme) -eq 1 ]
then
> sql_statements
printf "\n"
for sesid in `$ONSTAT_SES`
do
printf "Killing session: %s" $sesid
$ONSTAT_SQL $sesid >> sql_statements
$KILL $sesid
done
if [ `$SESSIONS` -gt 0 ]
then
printf "\n%s\n" "Could not kill all sessions. "
else
printf "%s\n" "Sessions Terminated. Rerun the script to continue"
printf "%s\n" "View the file sql_statements if you want to see what"
printf "%s\n" "sql's were terminated."
fi
else
printf "\n%s\n" "Aborted ..."
fi
exit 1 # end if any external sessions
fi
} # end check_session
# This function checks if the previous version was installed. If
# not, terminate with an error.
function check_previous {
typeset -i cnt
if [ $PREVIOUS = "N/A" ]
then
return
fi
$DBCOMMAND << MSG
output to temp.txt without headings
SELECT count(*) FROM db_revision
WHERE version = "$PREVIOUS" AND installed = "Y"
MSG
cnt=`sed /^$/d | sed "s/ //g" temp.txt`
rm temp.txt
if [ $cnt -eq 0 ]
then
$CLEAR
printf "\n%s\n" "The required version ( \"$PREVIOUS\" ) is not installed."
printf "%s\n" $VERSION "cannot be installed without \"$PREVIOUS\" being installed"
exit 1
fi
} # end check_previous
# This function checks if the current version is installed. Set
# unix exit code to 1 if version installed else 0 if not
function check_current {
typeset -i cnt
$DBCOMMAND << MSG
output to temp.txt without headings
SELECT count(*) FROM db_revision
WHERE version = "$VERSION" AND installed = "Y"
MSG
cnt=`sed /^$/d | sed "s/ //g" temp.txt`
rm temp.txt
if [ $cnt -gt 0 ]
then # return the unix exit code
return 1
else
return 0
fi
} # end check_current
# If this is the first time running the script, create an
# entry in the db_revision table. If this is a subsequent
# update for a particular version, update the entry.
function register_update {
typeset -i cnt
$DBCOMMAND << MSG
output to temp.txt without headings
SELECT count(*) FROM db_revision
WHERE version = "$VERSION"
MSG
cnt=`sed /^$/d | sed "s/ //g" temp.txt`
rm temp.txt
if [ $cnt -eq 0 ]
then
$DBCOMMAND << MSG
INSERT INTO db_revision VALUES (
"$VERSION",
today,
null,
"Y",
"$1"
)
MSG
else
$DBCOMMAND << MSG
UPDATE db_revision SET
install_date = today,
installed = 'Y',
rcs_version = "$1"
WHERE version = "$VERSION"
MSG
fi
} # end register_update
# if the user performs an undo update the db_revision
# table setting installed to No.
function register_undo {
$DBCOMMAND << MSG
UPDATE db_revision SET
uninstall_date = today,
installed = 'N',
rcs_version = "$1"
WHERE version = "$VERSION"
MSG
} # end register_undo
#-----------------------------------------------------
# This function UPDATES changes to the database
#-----------------------------------------------------
function change_schema {
echo "========== $DBNAME Database Update Started @ `date` ==========" > $INTERLOG
echo >> $INTERLOG
# Place the database changes here
$DBCOMMAND_OTF << MSG
create table wo_table
(
workorder char(15) not null,
mrp_code char(4),
item_code char(15) not null,
itemdesc char(25)
);
MSG
echo "\n========== Database Update Ended @ `date` ==========" >> $INTERLOG
} # end change_schema
#-----------------------------------------------------
# This function reverses the UPDATE changes
#-----------------------------------------------------
function reverse_schema {
echo "========== Database Undo Started @ `date` ==========" > $INTERLOG
printf " " >> $INTERLOG
$DBCOMMAND_OTF << MSG
drop table wo_table
MSG
echo "\n======== $DBNAME Database Undo Ended @ `date` ========" >> $INTERLOG
} # end reverse_schema
# Main Processing starts here
[ "$#" -eq 0 ] && usage
if [ $(database_engine_running) -ne 1 ]
then
printf "\n%s\n" "Database Engine must be Up in order to run $PROGRAM_NAME"
printf "%s\n" "terminating!"
exit 1
fi
if [ $(database_check $DBNAME) -ne 0 ]
then
printf "%s\n" "$DBNAME access error. no database or permissions"
printf "%s\n" "terminating!"
exit 1
fi
main_driver
check_session
# get the revision generated by RCS
revision=$(rcs_function)
case $cmmd in
"update")
check_previous
check_current
if [ "$?" -eq 1 ]
then
$CLEAR
printf "\nRelease %s previously installed.\n" $VERSION
printf "can not install $s without uninstalling it first.\n" $VERSION
exit 1
fi
printf "%s\n" "Running $DBNAME database update ..."
change_schema
register_update $revision
if [ -f $INTERLOG ]
then
mv $INTERLOG $LOGNAME
fi
printf "\n%s\n" "Changes to the $DBNAME database have been installed."
printf "%s\n" "To reverse these changes, type:"
printf "%s undo\n" $PROGRAM_NAME
;;
"undo")
check_current
if [ "$?" -eq 0 ]
then
$CLEAR
printf "\n%s has not been installed.\n" $VERSION
printf "%s connot be reversed without installing it first.\n" $VERSION
exit 1
fi
printf "%s\n" "Undoing $DBNAME database changes ..."
reverse_schema
register_undo $revision
if [ -f $INTERLOG ]
then
mv $INTERLOG $LOGNAME
fi
printf "\n%s\n" "Changes to the $DBNAME database have been reversed."
printf "%s\n" "Rerun the update again if you wish."
;;
esac
printf "\n%s Done.\n" $cmmd
# find "Error" in the log file if any
grep "Error" $LOGNAME
if [ "$?" -ne 0 ]
then # no errors found
printf "\nNo errors detected in log file %s\n" $LOGNAME
else
printf "\nErrors dectected! view log file %s for errors\n" $LOGNAME
fi
|