#!/bin/bash #Auther Sun Ying ##Copy left ##Version: Demo Version ##Basic Compare the datebase changes #ER=`whoami` #if [ $USER != "postgres" ];then # echo -e "\033[31mWrong User,please use postgres user\033[0m" # exit 1 #fi trap "Clear_Temp;exit" SIGINT SIGQUIT SIGHUP cmd_help(){ echo -e "\033[36mUsage: \033[33mdump2sql [smgr|ipoc] [Options]\033[0m" echo -e "\033[32m -d|--dump {-b|--before|-f|--after} Generate the dumpfile\033[0m" echo -e "\033[32m -c|--compare {d|delete} Compare the dump file by Change|Delete Method\033[0m" exit 5 } #check_dump(){ # l=1 # line2=0 # for i in `sed -n -e "/#Table/=" $table_file` # do # if [ $line2 -eq $l ];then # c[$l]=$i # break # fi # table_name=`sed -n "${i}p" $table_file|awk '{print $2}'` # c[$l]=$i # if [ $table_name == "$1" ];then # line1=$i # let line2=$l+1 # fi # # let l=$l+1 # done # sed -n "$line1,${c[$line2]}p" $table_file #} Clear_Temp(){ rm $FullTable &>/dev/null rm $FullTable1 &>/dev/null } generator() { [ -e $TARGET ] && rm -f $TARGET echo echo -e "\033[33mGenrating the output files...Please wait...\033[0m" j=0 for i in `cat $FullTable1` do echo "#Table $i" >>$TARGET 2>/dev/null export PGPASSWORD=$PGPASSWORD;psql -h $HOST -U $ACCOUNT $DATABASE -c "select * from $i">>$TARGET 2>/dev/null echo >>$TARGET echo >>$TARGET echo >>$TARGET printf "*" done } get_all_tables(){ FullTable=`mktemp /tmp/full_table_XXXXXXX` FullTable1=`mktemp /tmp/full_table_XXXXXX` export PGPASSWORD=$PGPASSWORD;psql -h $HOST -U $ACCOUNT $DATABASE -c '\d' > $FullTable cat $FullTable|awk -F "|" '{print $2}' > $FullTable1 } get_information(){ read -p "Please input your Host:" HOST read -p "Please input your User Account:" ACCOUNT read -p "Please input the database your want to get:" DATABASE read -s -p "Please input your pgsql User password:" PGPASSWORD # HOST="localhost" # ACCOUNT="postgres" # DATABASE="avmgmt" # PGPASSWORD="postgres" } smgr(){ HOST="localhost" ACCOUNT="postgres" DATABASE="avmgmt" PGPASSWORD="postgres" } compare_change(){ file_before=/tmp/dump_before file_after=/tmp/dump_after m=1 for i in `sed -n -e "/#Table/=" $file_after` do a[$m]=$i #echo ${a[$m]} let m=m+1 done echo -e "\033[33m-------------Compare change started, please wait-------------\033[0m" n=1 for i in $(diff $file_before $file_after -c|egrep "\-\-\-.*\,.*\-\-\-"|awk -F "," '{print $2}'|awk '{print $1}') do let i=$i-3 b[$n]=$i #echo ${b[$n]} let n=n+1 done for((i=1;i<n;++i)) do if [[ ${b[$i]} -le ${a[$mid]} && ${b[$i]} -ge ${a[$mid_1]} ]];then if [ $mark1 -eq "0" ];then continue else sed -n "${b[$i]}p" $file_after|egrep "^\\(.*row.*\\)|^$|#Table.*" &>/dev/null if [ $? -eq 0 ];then continue else echo -e "\033[33m====Below line also changed in this Table===\033[0m" echo -e "\033[32m`sed -n "${b[$i]}p" $file_after`\033[0m" continue fi fi fi let max=$m-1 let min=1 let mid=$max+$min let mid=$mid/2 let mid_1=$mid-1 let Num=${b[$i]} until [[ $Num -le ${a[$mid]} && $Num -ge ${a[$mid_1]} ]]; do if [ $Num -gt ${a[$mid]} ];then let min=$mid let mid=$min+$max let mid=$mid/2 let mid_1=$mid-1 else let max=$mid let mid=$min+$max let mid=$mid/2 let mid_1=$mid-1 fi done table_name=$(sed -n "${a[$mid_1]}p" $file_after) if [ "$table_name" == "#Table log_store" -o "$table_name" == "#Table sched_completed_job_status_view" -o "$table_name" == "#Table sched_job_status" -o "$table_name" == "#Table sched_jobs" -o "$table_name" == "#Table sched_pending_job_status_view" -o "$table_name" == "#Table sched_qrtz_simple_triggers" -o "$table_name" == "#Table sched_qrtz_triggers" -o "$table_name" == "#Table timer" -o "$table_name" == "#Table sched_qrtz_fired_triggers" -o "$table_name" == "#Table sched_job_parameter" ];then mark1=0 continue fi mark1=1 let lastline=${a[$mid]}-1 let Point_line=$Num sed -n "${Point_line}p" $file_after|egrep "^\\(.*row.*\\)" &>/dev/null [ $? -eq 0 ] && let Point_line=$Point_line-1 sed -n "${Point_line}p" $file_after|grep "#Table" &>/dev/null if [ $? -ne 0 ];then let Point_line_1=$Point_line-1 let Point_line_2=$Point_line+1 # sed -n "${Point_line}p" $file_after sed -n "${a[$mid_1]},${Point_line_1}p" $file_after echo -e "\033[32m`sed -n "${Point_line}p" $file_after`\t\t(Insertd|Modified)\033[0m" sed -n "${Point_line_2},${lastline}p" $file_after|grep -v "^$" else echo -e "\033[33m`sed -n "${a[$mid_1]},${lastline}p" $file_after`\033[0m" fi done } compare_delete(){ file_before=/tmp/dump_before file_after=/tmp/dump_after m=1 for i in `sed -n -e "/#Table/=" $file_before` do a[$m]=$i #echo ${a[$m]} let m=m+1 done echo -e "\033[33m-------------Compare delete started, please wait-------------\033[0m" n=1 for i in $(diff $file_after $file_before -c|egrep "\-\-\-.*\,.*\-\-\-"|awk -F "," '{print $2}'|awk '{print $1}') do let i=$i-3 b[$n]=$i #echo ${b[$n]} let n=n+1 done for((i=1;i<n;++i)) do if [[ ${b[$i]} -le ${a[$mid]} && ${b[$i]} -ge ${a[$mid_1]} ]];then if [ $mark1 -eq "0" ];then continue else sed -n "${b[$i]}p" $file_after|egrep "^\\(.*row.*\\)|^$|#Table.*" &>/dev/null if [ $? -eq 0 ];then continue else echo -e "\033[33m====Below line also changed in this Table===\033[0m" echo -e "\033[32m`sed -n "${b[$i]}p" $file_after`\033[0m" continue fi fi fi let max=$m-1 let min=1 let mid=$max+$min let mid=$mid/2 let mid_1=$mid-1 let Num=${b[$i]} until [[ $Num -le ${a[$mid]} && $Num -ge ${a[$mid_1]} ]]; do if [ $Num -gt ${a[$mid]} ];then let min=$mid let mid=$min+$max let mid=$mid/2 let mid_1=$mid-1 else let max=$mid let mid=$min+$max let mid=$mid/2 let mid_1=$mid-1 fi done table_name=$(sed -n "${a[$mid_1]}p" $file_before) if [ "$table_name" == "#Table log_store" -o "$table_name" == "#Table sched_completed_job_status_view" -o "$table_name" == "#Table sched_job_status" -o "$table_name" == "#Table sched_jobs" -o "$table_name" == "#Table sched_pending_job_status_view" -o "$table_name" == "#Table sched_qrtz_simple_triggers" -o "$table_name" == "#Table sched_qrtz_triggers" -o "$table_name" == "#Table timer" -o "$table_name" == "#Table sched_qrtz_fired_triggers" -o "$table_name" == "#Table sched_job_parameter" ];then mark1=0 continue fi mark1=1 let lastline=${a[$mid]}-1 let Point_line=$Num # sed -n "${Point_line}p" $file_after|grep "#Table" &>/dev/null # if [ $? -ne 0 ];then sed -n "${Point_line}p" $file_before|egrep "^\\(.*row.*\\)" &>/dev/null [ $? -eq 0 ] && let Point_line=$Point_line-1 let Point_line_1=$Point_line-1 let Point_line_2=$Point_line+1 # sed -n "${Point_line}p" $file_after sed -n "${a[$mid_1]},${Point_line_1}p" $file_before echo -e "\033[31m`sed -n "${Point_line}p" $file_before`\t\t(Deleted|Modified)\033[0m" sed -n "${Point_line_2},${lastline}p" $file_before|grep -v "^$" # else # echo -e "\033[33m`sed -n "${a[$mid_1]},${lastline}p" $file_after`\033[0m" # fi done } #####main()##### if [ $# -ne 0 ];then if [ $1 == "SMGR" -o $1 == "smgr" ];then smgr shift fi if [ $1 == "IPOC" -o $1 == "ipoc" ];then ipoc shift fi if [ $1 == "-c" -o $1 == "--compare" ];then if [ $# -lt 2 ];then compare_change exit 0 elif [ $2 == "d" -o $2 == "delete" ];then compare_delete exit 0 else cmd_help fi fi if [ $1 == "-d" -o $1 == "--dump" ];then if [ $# -ge 2 ];then if [ $2 == "b" -o $2 == "before" ];then TARGET="/tmp/dump_before" elif [ $2 == "f" -o $2 == "after" ];then TARGET="/tmp/dump_after" else cmd_help fi elif [ ! -e /tmp/dump_before ];then TARGET="/tmp/dump_before" elif [[ -e /tmp/dump_before && ! -e /tmp/dump_after ]];then TARGET="/tmp/dump_after" elif [[ -e /tmp/dump_before && -e /tmp/dump_after ]];then mv -f /tmp/dump_after /tmp/dump_before TARGET="/tmp/dump_after" else TARGET="/tmp/dump_before" fi [ ! $HOST ] && get_information get_all_tables generator echo echo -e "You can find your output file under \033[33m$TARGET\033[0m" exit 0 fi # if [ $1 == "-k" -o $1 == "--check" ];then # if [ $# -lt 2 ];then # cmd_help # fi # if [ $# -ge 3 ];then # if [ $2 == "b" -o $2 == "before" ];then # table_file=/tmp/dump_before # shift # elif [ $2 == "f" -o $2 == "after" ];then # table_file=/tmp/dump_after # shift # else # cmd_help # fi # fi # shift # table_file=/tmp/dump_after # check_dump $1 # fi Clear_Temp cmd_help else cmd_help fi