Jenkins基础篇 系列之-—07 实现SQL脚本批量执行
公司内部推广DevOps,全部项目配置CI/CD流水线(Jenkins+SVN+ANT),在代码实现持续集成后,SQL语句自动执行的实现提上日程
一、环境
- Linux环境
- 安装ANT工具,包括ant扩展包---ant-contrib-1.0b3.jar,maven链接
- 下载oracle连接ojdbc5.jar包。
二、思路
步骤1: 从SVN的【脚本提交路径下】下载SQL脚本
步骤2:【shell脚本】:获取日期文件夹名为今天的文件夹下的所有sql脚本,排除其中已执行成功的脚本,拷贝未执行的脚本到待执行文件夹中;
步骤3:【ant脚本】:对待执行脚本排序后,循环顺序执行每条sql语句,执行成功的sql脚本备份到bak文件夹。
三、具体实现
3.1 源码管理
3.2 执行shell
if [ $# -ne 1 ] then echo "Usage: $0 foldername" echo " $0 Exec SQL" exit fi Workspace=$1 year=`date +%Y` today=`date +%Y%m%d` sqlfolder=${Workspace}/svn/${year}/${today} bakfolder=${Workspace}/sqlbak/${year}/${today} logfolder=${Workspace}/sqllog #如果待执行sql文件夹已存在就清空该文件夹,否则创建 if [ -d "runSqlInFolder" ];then echo "-----Clean the executed sql folder:/runSqlInFolder-----" rm -rf runSqlInFolder/* else mkdir -p ${Workspace}/runSqlInFolder fi if [ ! -d "${logfolder}" ];then mkdir ${logfolder} else echo "-----Dir ${logfolder} is exist" fi #将今天提交到SVN的sql脚本中还未执行过的拷贝到runSqlInFolder文件夹 if [ -d "${sqlfolder}" ];then echo '-----Copy the sql file into folder' cp -r ${sqlfolder}/* runSqlInFolder find runSqlInFolder/ -name '*.jar' |xargs rm -rf find runSqlInFolder/ -type d | grep .svn$ | xargs rm -rf if [ -d "${bakfolder}" ];then cd ${bakfolder} find * -type f -name *.sql|xargs -i rm -f ../../../runSqlInFolder/{} else echo "-----create back folder: ${bakfolder}" mkdir -p ${bakfolder} fi else echo dir ${sqlfolder} not exist fi
3.3 Invoke ANT
3.3.1 定义一个target:runSqlInFolder
使用try catch包裹for 循环,for循环中调用execSQL标签(自定义公共方法块,即宏),顺序逐条执行SQL脚本。
<taskdef resource="net/sf/antcontrib/antlib.xml" classpath="${lib}/ant-contrib-1.0b3.jar"/> <target name="runSqlInFolder"> <echo>Run the SQL at Folder: ${sqlfolder}</echo> <echo>DB Host: ${v7uatdb.host}</echo> <echo>DB Name: ${v7uatdb.name}</echo> <echo>DB User: ${v7uatdb.user}</echo> <trycatch property="errMsg"> <try> <for param="folder"> <path> <sort xmlns:rcmp="antlib:org.apache.tools.ant.types.resources.comparators"> <dirset dir="${sqlfolder}" includes="*" /> </sort> </path> <sequential> <echo>SQL Folder: @{folder}</echo> <for param="file"> <path> <sort xmlns:rcmp="antlib:org.apache.tools.ant.types.resources.comparators"> <fileset dir="@{folder}" includes="*/*.sql" casesensitive="false"/> </sort> </path> <sequential> <echo>SQL: @{file}</echo> <execsql dbhost="${v7uatdb.host}" dbport="${v7uatdb.port}" dbname="${v7uatdb.name}" dbuser="${v7uatdb.user}" dbpwd="${v7uatdb.pwd}" sqlfile="@{file}" logfile="${Sqllogfile}"/> </sequential> <!--<move file="@{file}" todir="${sqlbakdir}/@{folder}"/> folder 包含路径和文件名,所以直接复制file还有点问题,需要截取文件名--目前待研究 --> </for> <move file="@{folder}" todir="${sqlbakdir}"/> </sequential> </for> <echo>Finished running all SQL</echo> <echo>File moved to backup folder:</echo> <echo>${sqlbakdir}</echo> </try> <catch> <echo>Error found when running SQL</echo> <echo>Log file can be found in:</echo> <echo>${sqlbakdir}/err</echo> <move file="${Sqllogfile}" todir="${sqlbakdir}/err"/> <fail>Error Occur</fail> </catch> <finally> </finally> </trycatch> </target>
3.3.2 定义execsql标签
通过sql标签执行sql文件,通过record标签记录对应的执行日志并输出。
注意:如果执行procedure就需要设置delimiter,本例中通过SQL文件的命名来区分是不同的SQL类型(procedure,declare等)。
<macrodef name="execsql" description="Run single SQL file."> <attribute name="dbhost" description="Host Name/ IP of the DB"/> <attribute name="dbport" description="DB Port"/> <attribute name="dbname" description="DB name"/> <attribute name="dbuser" description="DB User name"/> <attribute name="dbpwd" description="DB Password"/> <attribute name="sqlfile" description="SQL file to be run"/> <attribute name="logfile" default="sql.log" description="Log file"/> <sequential> <echo>Log file @{logfile}</echo> <record name="@{logfile}" action="start"/> <if> <contains string="@{sqlfile}" substring="PROCEDURE"/> <then> <sql driver="${oracleDriver}" url="jdbc:oracle:thin:@@@{dbhost}:@{dbport}:@{dbname}" userid="@{dbuser}" password="@{dbpwd}" classpathref="classpath" encoding="${encoding}" print="true" autocommit="true" delimiter="/" delimitertype="row"> <transaction src="@{sqlfile}"/> </sql> </then> <else> <sql driver="${oracleDriver}" url="jdbc:oracle:thin:@@@{dbhost}:@{dbport}:@{dbname}" userid="@{dbuser}" password="@{dbpwd}" encoding="${encoding}" classpathref="classpath" autocommit="true" print="true"> <transaction src="@{sqlfile}"/> </sql> </else> </if> <record name="@{logfile}" action="stop"/> </sequential> </macrodef>
更新后
<macrodef name="execsql" description="Run single SQL file."> <attribute name="dbhost" description="Host Name/ IP of the DB"/> <attribute name="dbport" description="DB Port"/> <attribute name="dbname" description="DB name"/> <attribute name="dbuser" description="DB User name"/> <attribute name="dbpwd" description="DB Password"/> <attribute name="sqlfile" description="SQL file to be run"/> <attribute name="logfile" default="sql.log" description="Log file"/> <sequential> <echo>Log file @{logfile}</echo> <record name="@{logfile}" action="start"/> <if> <contains string="@{sqlfile}" casesensitive="no" substring="PROCEDURE"/> <then> <sql driver="${oracleDriver}" url="jdbc:oracle:thin:@@@{dbhost}:@{dbport}:@{dbname}" userid="@{dbuser}" password="@{dbpwd}" classpathref="classpath" encoding="${encoding}" print="true" autocommit="true" delimiter="/" delimitertype="row"> <transaction src="@{sqlfile}"/> </sql> </then> <elseif> <contains string="@{sqlfile}" casesensitive="no" substring="DECLARE"/> <then> <sql driver="${oracleDriver}" url="jdbc:oracle:thin:@@@{dbhost}:@{dbport}:@{dbname}" userid="@{dbuser}" password="@{dbpwd}" classpathref="classpath" encoding="${encoding}" print="true" autocommit="true" delimiter=";;"> <transaction src="@{sqlfile}"/> </sql> </then> </elseif> <else> <sql driver="${oracleDriver}" url="jdbc:oracle:thin:@@@{dbhost}:@{dbport}:@{dbname}" userid="@{dbuser}" password="@{dbpwd}" encoding="${encoding}" classpathref="classpath" autocommit="true" print="true"> <transaction src="@{sqlfile}"/> </sql> </else> </if> <record name="@{logfile}" action="stop"/> </sequential> </macrodef>
3.4 SVN中存放SQL的文件夹设置
3.5 成功执行效果
3.6 注意
在这个过程中,需要DBA 介入人工审核:
第一类:是在提交脚本之后。审核的内容主要是变更内容是否合法、方式是否得当、是否影响业务等等。
第二类:是在提交生产变更后。审核的主要的内容是,判断变更是否会对当时的生产系统产生影响。比如,订单表的更新、大表的变化等,就不允许在业务高峰期进行。
四、遇到的问题处理
4.1【问题】如果执行的SQL语句中存在中文,执行完在数据库中保存的数据是乱码
目前常用的两种数据库编码格式为:GBK、UTF-8,也是两种编码风格(ANSI和UNICODE)中的代表
经确认公司数据库采用GBK编码,所以将encoding改为GBK,乱码问题解决,ant文件头设置
<property name="encoding" value="GBK" /><!--UTF-8-->
4.2【问题】如果执行的SQL语句中,有declare,声明变量的,执行declare过程中会报错
delete zssys.WEB_APP_TEMPLATE where C_INTERFACE_ID = '0000-aa';; declare response clob; begin response := 'aaa'; insert into zssys.WEB_APP_TEMPLATE (C_PK_ID, C_INTERFACE_ID, C_SYSCODE, C_APP_NAME, C_TEMPLATE_REQUEST, C_TEMPLATE_RESPONSE) values (SYS_GUID(), '0000-Ga', '*', 'test', null, response); end;
4.3【问题】目前备份脚本是按照模块整体备份的,没有做到单个sql脚本备份,后期优化
五、 参考资料
【Quality】
Quality is the value to someone who matters。做测试,首先要找到这个someone是谁,以及这个 someone重视的 value是什么。