数据库验证.sh

admin

摘要: #check.sh#!/bin/shecho '#check_sub.sh' >./check_sub.shecho '#!/bin/sh' >>./check_sub.shecho 'rm -f ./check.log' >>./check_sub.shfor files in `find ./t

正文：
#check.sh
#!/bin/sh
echo '#check_sub.sh' >./check_sub.sh
echo '#!/bin/sh' >>./check_sub.sh
echo 'rm -f ./check.log' >>./check_sub.sh
for files in `find ./test -type f -name '*' | awk '{print $1}' | sort`
do
files1=`basename $files`
if which dos2unix;then
echo ''>/dev/null
else
yum -y install dos2unix.x86_64
fi
yes | cp ./test/$files1 ./
/usr/bin/dos2unix ./$files1
sed -i "/^$/d" ./$files1
sed -i "/^--/d" ./$files1
sed -i "/^ --/d" ./$files1
A=1
while [[ $A -le $(((`cat $files1 | awk 'BEGIN{RS="";OFS=" "}{NF=NF;print}' | grep -o ';' |wc -m`)/2)) ]]
do
echo -ne "cat $files1 | awk 'BEGIN{RS=\"\";OFS=\" \"}{NF=NF;print}' | awk -F\; '{print \$$A}' >>./check.log" >>./check_sub.sh
A=$(($A+1))
done
done
echo -ne "sed -i 's/$/;/g' ./check.log" >>./check_sub.sh
echo -ne "sed -i '{s/create /CREATE /g;s/alter /ALTER /g;s/drop /DROP /g;s/insert /INSERT /g;s/ into / INTO /g;s/update /UPDATE /g;s/ table / TABLE /g;s/ constraint / CONSTRAINT /g;s/)values /) VALUES/g;s/values(/VALUES (/g;s/ values / VALUES /g;s/ primary / PRIMARY /g;s/ set / SET /g }' ./check.log" >>./check_sub.sh
echo -ne "sed -i \"{s/^ //g;s/, /,/g;s/)VALUES/) VALUES/g;s/VALUES(/VALUES (/g;s/( /(/g;s/ )/)/g;s/ where / WHERE /g;s/ / /g}\" ./check.log" >>./check_sub.sh

#check_select.sh
#!/bin/sh
source $HOME/.bash_profile
cd `dirname $0`
check_home=`pwd`
sh -x ./check_sub.sh
rm -f ./check_select.sql
sort ./check.log >./check.log_new
yes | cp ./check.log_new ./check.log
rm -f ./check.log_new ./test.log
while read file
do
if echo $file | grep -w ^ALTER | grep -w TABLE;then
table=`echo $file | awk '{print $3}'`
COLUMN=`echo $file | awk '{print $5}'`
if [[ $COLUMN = PRIMARY ]] || [[ $COLUMN = CONSTRAINT ]] ;then
echo -ne "--$file--" >>./check_select.sql
else
echo -ne "--$file--" >>./check_select.sql
echo -ne "SELECT $COLUMN FROM $table WHERE 1=0;" >>./check_select.sql
fi
fi
if echo $file | grep -w ^CREATE | grep -w TABLE;then
table1=`echo $file | awk '{print $3}'`
echo -ne "--$file--" >>./check_select.sql
echo -ne "SELECT * FROM $table1 WHERE 1=0;" >>./check_select.sql
fi
if echo $file | grep -w ^DROP | grep -w TABLE;then
table2=`echo $file | awk '{print $3}'`
echo -ne "--$file--" >>./check_select.sql
echo -ne "SELECT * FROM $table2 WHERE 1=0;" >>./check_select.sql
fi
if echo $file | grep -w ^INSERT | grep -w INTO;then
table3=`echo $file | awk '{print $3}'`
echo -ne "--$file--" >>./check_select.sql
COLUMN01=`echo $file | awk -F' VALUES' '{print $1}' | awk -F'(' '{print $2}' | awk -F')' '{print $1}'`
COLUMN02=`echo $file | awk -F'VALUES ' '{print $2}' | sed '{s/^ //g;s/^(//g;s/);$//g}'`
if [[ `echo $COLUMN01 | grep -o ',' | wc -m` -eq `echo $COLUMN02 | grep -o ',' | wc -m` ]];then
A=1
test=""
while [[ $A -le $(((`echo $COLUMN01 | grep -o ',' |wc -m`)/2+1)) ]]
do
test01=`echo $COLUMN01 | cut -f $A -d ','`
test02=`echo $COLUMN02 | cut -f $A -d ','`
test="$test$test01=$test02 and "
echo $test
A=$(($A+1))
done
if echo $table3 | grep '(' ; then
echo -ne "SELECT $COLUMN01 FROM `echo $table3 |awk -F'(' '{print $1}'` where $test;" >>./check_select.sql
else
echo -ne "SELECT $COLUMN01 FROM $table3 where $test;" >>./check_select.sql
fi
fi
fi
if echo $file | grep -w ^UPDATE ; then
table4=`echo $file | awk -F'^UPDATE ' '{print $2}' | awk -F' SET' '{print $1}'`
COLUMN03=`echo $file | awk -F'^UPDATE ' '{print $2}' | awk -F' SET' '{print $2}'`
echo -ne "--$file--" >>./check_select.sql
if echo $COLUMN03 | grep ',' | grep ' WHERE ';then
if [[ `echo $COLUMN03 | grep -o 'WHERE' |wc -m` -gt 6 ]];then
test03=`echo $COLUMN03 | awk -F'WHERE' '{print $2}' | awk '{print $1}'`
COLUMN04=`echo $COLUMN03 | sed "{s/,/ and /g;s/ WHERE $test03/ and $test03/g}"`
else
COLUMN04=`echo $COLUMN03 | sed '{s/,/ and /g;s/ WHERE / and /g}'`
fi
echo -ne "SELECT * FROM $table4 where $COLUMN04" >>./check_select.sql
fi
if echo $COLUMN03 | grep ',' | grep -E -v ' WHERE ' ;then
COLUMN05=`echo $COLUMN03 |sed 's/,/ and /g'`
echo -ne "SELECT * FROM $table4 where $COLUMN05" >>./check_select.sql
fi
if echo $COLUMN03 | grep ' WHERE ' | grep -E -v ',' ;then
if [[ `echo $COLUMN03 | grep -o 'WHERE' |wc -m` -gt 6 ]];then
test04=`echo $COLUMN03 | awk -F'WHERE' '{print $2}' | awk '{print $1}'`
COLUMN06=`echo $COLUMN03 | sed 's/ WHERE $test04/ and $test04/g}'`
else
COLUMN06=`echo $COLUMN03 | sed 's/ WHERE / and /g'`
fi
echo -ne "SELECT * FROM $table4 where $COLUMN06" >>./check_select.sql
fi
if echo $COLUMN03 | grep -E -v ',' | grep -E -v ' WHERE ' ;then
echo -ne "SELECT * FROM $table4 where $COLUMN03" >>./check_select.sql
fi
fi
done <./check.log
sed -i "{s/ / /g;s/ / /g;s/=NULL/ IS NULL/g;s/=null/ IS NULL/g;s/ and ;$/;/g}" ./check_select.sql
C=1
while read line
do
if echo $line | grep -E -v '^-';then
B=2
while [[ $B -le $(((`echo $line | grep -o '=' |wc -m`)/2)) ]]
do
if echo $line | awk -F ' where ' '{print $2}' | cut -f $B -d '=' | cut -f 1 -d ' ' | grep -E -v "'" | grep -E -v '\(' | grep [^0-9];then
sed -i "$I s/`echo $line | awk -F ' where ' '{print $2}' | cut -f $(($B-1)) -d '=' | cut -f 2 -d ' '`=`echo $line | awk -F ' where ' '{print $2}' | cut -f $B -d '=' | cut -f 1 -d ' ' | grep -E -v "'" | grep -E -v '\(' | grep [^0-9]` and//g" ./check_select.sql
fi
B=$(($B+1))
done
fi
C=$(($C+1))
done <./check_select.sql
sed -i "{s/ / /g;s/ / /g;s/^ //g;}" ./check_select.sql