通过shell实现对sql业务的升级

主要是通过命令方式对SQL进行全量、更新升级，部分代码，仅供参考

#!/bin/bash
#execute all script in specified directory



#--------------- 全量脚本----------------------
full_upgradesql()
{

MYDATE=`date +%F'-'%T'-'%w`
OraclesqL_PATH=/home/giacp/apps/GiacpMySQL/CompleteSQL #指定的目录
LOG_FILE=/home/log/exec_${MYDATE}.log
CompleteSQL=/home/giacp/apps/GiacpMySQL/CompleteSQL #指定的目录



if [ -d  ${CompleteSQL} ];then
echo "全量文件目录${CompleteSQL}"
else
echo "文件目录${CompleteSQL}不存在"
exit
fi

OraclesqL()
{
echo "-----当前全量sql版本有"
MySqL_Sql_PATHql_PATH=`find ${CompleteSQL}/ -type f|grep '.sql$'|awk -F "/" '{print $(NF)}'|grep 'structure'|awk -F "-" '{print $4}'|awk -F ".sql" '{print $1}'|sort` #查询出所有sql版本


for OraclesqL_file1 in ${MySqL_Sql_PATHql_PATH}
do
echo "$OraclesqL_file1"
done
}


while true
do

#if [ ! $db_name ] ; then #如果没有指定数据库名
        read -p "请输入数据用户名：" db_name
#fi

#if [ ! $db_pass ] ; then #如果没有指定数据库密码
        read -s -p "请输入数据库密码：" db_pass 
		echo ""
#fi

#if [ ! $db_SID ] ; then #如果没有指定数据库服务名
        read  -p "请输入数据库名：" db_SID
#fi


echo "检查是否有${db_SID}库名，没有则创建。并检查账号、密码、服务名是否正确"
sleep 3

#echo "${db_pass}"

mysql -u${db_name} -p${db_pass} -e "create database if not exists ${db_SID}" >& error.log
error_user_or_password=`grep '(using password: YES)' error.log`
error_database=`grep 'Unknown database' error.log`	
if [ -n "$error_user_or_password" ]; then
	echo "用户或密码错误，请重新执行"
	continue
else
	
	OraclesqL
	break;


fi

done




	


CompleteSQL1 ()
{
	echo "你输入要升级的版本是：${db_version}"
	giacp_data_structure=`find ${CompleteSQL}/ -type f|grep '.sql$'|grep "structure"|grep "${db_version}"`
	giacp_data_init=`find ${CompleteSQL}/ -type f|grep '.sql$'|grep -E "init-data|data-init" | grep "${db_version}"`
	for data_structure in ${giacp_data_structure}
	do
		echo "现在开始插入数据结构：$data_structure"
		mysql -u${db_name} -p${db_pass} ${db_SID} < $data_structure >& error.log
	    sleep 5
		for data_init in ${giacp_data_init}
		do
			echo "现在开始插入基础数据：${data_init}"
			mysql -u${db_name} -p${db_pass} ${db_SID} < $data_init >& error.log 
			
			Repeat_insert=`grep 'Duplicate entry' error.log`
			if [ -n "$Repeat_insert" ] ; then
				echo "重复插入！"
			fi
		done
		cat error.log >>${LOG_FILE} #输出执行日志
		error=`grep ERROR error.log` #读取错误日志信息
		if [ -n "$error" ] ; then #如果有错误就退出程序
		echo $error
		echo "直接退出啦！"
		exit
		fi	
	done
	echo "##########升级完成#########"	
	exit
	

}
CompleteSQL2 ()
{
	echo "你退出了执行全量操作！"
	exit
}





	while true
	do

		read -p "请输入要升级的全量数据库版本：例（1.2.3）" db_version
		DATEPATTERN="([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})"
		
			
		if [[ "$db_version" =~ $DATEPATTERN ]]; then
			#echo "正确${db_version}"
			
			for OraclesqL_file2 in ${MySqL_Sql_PATHql_PATH}
			do
				#echo "$OraclesqL_file2"
				if [  "$db_version" != "$OraclesqL_file2" ]; then								
					continue;	
				else 
					
					while true
					do
					read -p "确认执行请输入：（输入yes/no）" choose
					if [ $choose = "yes" ]; then
						CompleteSQL1
					
					elif [ $choose = "no" ]; then

						CompleteSQL2;
					else 
						echo "请输入yes/no"
						continue
					fi
					done
				fi
		
			done
		
		else
			echo "不符合格式，请正确输入！例（1.2.3）"
			continue
		fi
		
	done





}







#-----------------------新增脚本-------------------------------------
upgredesql ()
{


UpdateSQL=/home/giacp/apps/GiacpMySQL/UpdateSQL
SqlScript=/home/giacp/apps/GiacpMySQL/Scripts

current_upgrade()
{

#获取数据库当前版本信息
upgrade=`mysql -u${db_name} -p${db_pass} ${db_SID} < ${SqlScript}/query-current-version.sql|awk 'END{print}'`
echo "你当前数据库的版本信息为：$upgrade"

}

while true
do

	
#if [ ! $db_name ] ; then #如果没有指定数据库名
	read -p "请输入数据用户名：" db_name
#fi

#if [ ! $db_pass ] ; then #如果没有指定数据库密码
	read -s -p "请输入数据库密码：" db_pass
	echo ""
	
#fi

#if [ ! $db_SID ] ; then #如果没有指定数据库服务名
	read -p "请输入数据库名：" db_SID 
	
	
#fi

#执行版本信息sql
mysql -u${db_name} -p${db_pass} ${db_SID} < ${SqlScript}/prepare-update.sql >& upgrade_error.log

error_user_or_password=`grep '(using password: YES)' upgrade_error.log`
error_database=`grep 'Unknown database' upgrade_error.log`
if [ -n "$error_user_or_password" ]; then
	echo "用户或密码错误，请重新执行"
	continue
elif [ -n "$error_database" ]; then
	echo "没有该数据库服务名${db_SID}，请重新执行"
	continue
else
	current_upgrade
	break
fi
	
done
	
	




#获取当前可更新的版本
upgrade1=`mysql -u${db_name} -p${db_pass} ${db_SID} < ${SqlScript}/query-updateble-version.sql`

echo "可更新版本有"
for new_version_ud in ${upgrade1}
do
	echo "${new_version_ud}"
done

while true 
do
        read -p "请输入需要升级到的数据库版本：例（1.2.3）：" db_to_upgrade_version
		DATEPATTERN="([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})"
		if [[ "$db_to_upgrade_version" =~ $DATEPATTERN ]]; then
			#echo "正确${db_version}"
			
			for db_to_upgrade_version_tow in ${upgrade1}
			do

				
				if [ "$db_to_upgrade_version_tow" != "$db_to_upgrade_version" ]; then
					continue
				else
				
				old_version=`echo "${upgrade}"|awk 'END{print}'|awk -F "." '{print $1,$2,$3}'|sed s/[[:space:]]//g` #格式化输出整数
				new_version=`echo "${db_to_upgrade_version}"|awk -F "." '{print $1,$2,$3}'|sed s/[[:space:]]//g` #格式化输出整数


				OraclesqL_PATH=${UpdateSQL}/*.sql #版本指定的文件
			 
					echo "将要执行以下sql脚本："
					sleep 3
					for new_version_ud1 in ${OraclesqL_PATH}
					do
						#echo "$new_version_ud1"

						version_new=`echo "${new_version_ud1}"|awk -F "." '{print $1,$2,$3}'|sed s/[[:space:]]//g|awk -F "-" '{print $4}'` #格式化输出整数
						#echo "$version_new"
						
						if [[ ${old_version} -lt ${version_new} ]] && [[ ${new_version} -ge ${version_new} ]] ; then
					 
							echo "$new_version_ud1"
							 
									mysql -u${db_name} -p${db_pass} ${db_SID} < ${new_version_ud1} >& upgrade_error.log
									character_set_error=`grep '\\xB2\\xE9\\xD1\\xAF\\xCA\\xD3' upgrade_error.log` #读取错误日志信息
									if [ -n "$character_set_error" ] ; then #如果有错误就退出程序
										echo $character_set_error
										exit
									fi
										
						fi
						
					done 

					#执行清空version_lookup的版本
					sleep 3
					mysql -u${db_name} -p${db_pass} ${db_SID} < ${SqlScript}/finish-update.sql >& upgrade_error.log 
					echo "##########升级完成#########"
					exit	
				
				fi
				


			done

		else
			echo "格式不正确"
			continue
		
		fi
		
done


}





echo "##############请选择全量升级sql还是增量sql升级#############"

while true
do

	echo "1.全量->指定版本sql升级"
	echo "2.增量sql升级"
	echo "#####请选择:#####"
	read softType
	if ((softType == 1)); then
	  echo "#####全量/指定版本sql升级#####"
	  full_upgradesql

	elif ((softType == 2)); then
	  echo "#####升级/增量sql升级#####"
	  upgredesql
	else
	 echo "#####输入错误#####"
	 continue
	fi
	

done