用AWK实现DB2 数据库 Schema的同步

核心提示： # 生成卸出指定表的数据的SQL脚本# $1. 旧数据库的SCHEMA文件# $2. 新数据库的SCHEMA文件# $3. 输出的卸出指定表的数据的SQL脚本文件名# $4. 指定的表名# $5. db2move卸出文件时的清单文件genunloadsql(){TMPSQLFILE=.tm

# 生成卸出指定表的数据的SQL脚本

# $1. 旧数据库的SCHEMA文件

# $2. 新数据库的SCHEMA文件

# $3. 输出的卸出指定表的数据的SQL脚本文件名

# $4. 指定的表名

# $5. db2move卸出文件时的清单文件

genunloadsql()
{
TMPSQLFILE=.tmpsql.sql.sql
TMPNEWTBLFLD=.tmpfld.fld.fld
rm -f ${TMPSQLFILE}
# 1. 用新的表结构创建一个临时表
awk '{
if(NR > 1)
{
gsub(""", "", $1); # remove character "
printf("%s
", $0);
}
else
{
printf("create table sihitranstmptbl (
"); # )
}
}' $2 > ${TMPSQLFILE}
# 2. 生成insert into transtmptbl (...) select ... from <oldtbl>的SQL
# 2.1. 生成新旧两个表的字段列表
awk '{
if(NR > 1 && $1 != ";")
{
gsub(""", "", $1); # remove character "
printf("%s
", $1);}
}' $2 > ${TMPNEWTBLFLD}
isFirstFeild=1
Select=""
Into=""
for fldname in `cat ${TMPNEWTBLFLD}`
do
grep ""${fldname}"" $1 1>/dev/null 2>&1
if [ $? -eq 0 ]
then
# 2.2. 取出在新旧两表中都有的字段名，加入到select子句和into子句中
if [ ${isFirstFeild} -eq 0 ]
then
Select="${Select},"
Into="${Into},"
fi
Select="${Select} ${fldname}"
Into="${Into} ${fldname}"
isFirstFeild=0
else
# 2.3. 取出仅在新表中出现的字段，如果该字段不允许null，
# 且没有设置default值，
则按以下原则取默认值放到select子句中，
# 并将字段名放到into子句中
grep ""${fldname}"" $2 |
grep "not null" 1>/dev/null 2>&1
if [ $? -eq 0 ]
then
grep ""${fldname}"" $2
| grep "with default" 1>/dev/null 2>&1
if [ ! $? -eq 0 ]
then
if [ ${isFirstFeild} -eq 0 ]
then
Select="${Select},"
Into="${Into},"
fi
# 计算默认值
# 对于新增的字符型字段，默认值为''；
# 对于新增的数值型字段，默认值为0；
# 对于新增的TIMESTAMP字段，默认值为'';
# 对于新增的SERIAL开字段，默认值为0；
# 对于新增的DATE字段，默认值为；
# 对于新增的DATETIME字段，默认值为；
Const=`grep ""${fldname}"" $2 | awk '{
if(match($2, "int") > 0) # integer, smallint, bigint
printf("0");
else if(match($2, "numeric") > 0)
printf("0.0");
else if(match($2, "decimal") > 0)
printf("0.0");
else if(match($2, "double") > 0)
printf("0.0");
else if(match($2, "float") > 0)
printf("0.0");
else if(match($2, "real") > 0)
printf("0.0");
else if(match($2, "char") > 0) # char, varchar
printf("" "");
else if($2 == "timestamp")
printf("current timestamp");
else if($2 == "date")
printf("current date");
else if($2 == "time")
printf("current time");
else
printf("" "");
}'`
Select="${Select} ${Const}"
Into="${Into} ${fldname}"
isFirstFeild=0
fi
fi
fi
done
tablefullname=`grep -i ""$4"" $5
| awk -F"!" '{printf("%s", $2);}'`
echo "insert into sihitranstmptbl
(${Into}) select ${Select} from
${tablefullname} ;" >> ${TMPSQLFILE}
# 3. 将临时表中的数据导出到文件中
Unloadfile=`grep -i ""$4"" $5 |
awk -F"!" '{printf("%s", $3);}'`
echo "export to ${Unloadfile}
of ixf select * from sihitranstmptbl ;"
>> ${TMPSQLFILE}
# 4. 删除临时表
echo "drop table sihitranstmptbl ;"
>> ${TMPSQLFILE}
cat ${TMPSQLFILE} >> $3
rm -f ${TMPSQLFILE}
rm -f ${TMPNEWTBLFLD}
}
echo "table in both database:"
TABLEINBOTH="`echo ${TABLEINBOTH}
| tr [:upper:] [:lower:]`"
echo > ${UNLOADSQLFILE}
for tblname in ${TABLEINBOTH}
do
gettableschema ${tblname}
${OLDDBSCHEMATMP} ${OLDTBLSCHEMA}
gettableschema ${tblname}
${NEWDBSCHEMATMP} ${NEWTBLSCHEMA}
diff ${OLDTBLSCHEMA}
${NEWTBLSCHEMA} 1>/dev/null 2>&1
if [ ! $? -eq 0 ]
then
# 如果有不同，则生成卸出数据的SQL脚本
echo "different table:" ${tblname}
genunloadsql ${OLDTBLSCHEMA} ${NEWTBLSCHEMA}
${UNLOADSQLFILE} ${tblname} ${DB2MOVELISTFILE}
else
echo "same table:" ${tblname}
fi
done