#!/bin/sh
#
# Here we are considering testdump1.sql as old mysql dump of database
# and testdump2.sql as latest mysql dump file to be changed to hence
# forth there will be two functions namely to insert missing 
# columns from new mysql dump to database, and also deleting new 
# columns from database which are not present in new mysql dump.
#
#
# Test purpose testdump1.sql and testdump2.sql has one table each with 
# few columns names missing.
#
#
value1=0
value2=0

while read line1
do
       value1=`expr $value1 + 1`
#       the abovel ine gives the number of line in the file
 
done < testdump1.sql
while read line2
do
        value2=`expr $value2 + 1`
#       the abovel ine gives the number of line in the file 
  
done < testdump2.sql
echo "value1 : $value1"
echo "value2 : $value2"
counter1=0
counter2=0
len1=0
len2=0
#
#function for inserting missed columns
#
insert_column()
{
        mysql -udunkin -pdunkin123 ditdb << EOF
        ALTER TABLE $1 ADD $2;
        QUIT
        EOF
        if [ $? -eq 0 ]
        then
                  echo "$2 added to $1 Successfully "
                  else
                  echo "Script  failed please verify the sqls"
                  exit 1
        fi

}
#
#function for deleting the extra column
#
delete_column()
{
 mysql -udunkin -pdunkin123 ditdb << EOF
        ALTER TABLE $1 drop $2;
        QUIT
        EOF
        if [ $? -eq 0 ]
        then
                   echo "$2 dropped from $1 Successfully "
                   else
                   echo "Script  failed please verify the sqls"
                   exit 1
        fi

}

#
# this below piece of code is used to find columns not present in new database
# and hence will involve method for deleting that column.
#
echo "========printing lines not present in new mysql dump(delete)========="
echo ""
while read line1
do
 
# echo "$line1"
 counter2=0
 len1=${#line1}
 while read line2
 do
  len2=${#line2}
  char1=${line1:$len1-1:$len1}
  char2=${line2:$len2-1:$len2}
  if [ "$char1" = "," ]
  then
   line11=${line1:0:$len1-1}
  else
   line11=$line1
  fi
  
  if [ "$char2" = "," ]
                then
   line22=${line2:0:$len2-1}
  else
                        line22=$line2
  fi
  if [ "$line11" != "$line22" ]
  then
   counter2=`expr $counter2 + 1`
  fi
  
  if [ $counter2 -eq $value2 ]
  then
   echo "this line not present : $line11"
#   here calling the delete method   
   table_name=$( awk -F\` '/CREATE TABLE/{print $2}' testdump2.sql )
#   column_name=$
#   delete_column $table_name $column_name
   
  fi
    
 done < testdump2.sql
done < testdump1.sql

#
# this below piece of code is used to find columns not present in old database
# and hence will involve method for inserting that column.
#
echo ""
echo "========printing lines not present in old mysql dump(insert)========="
echo ""
while read line2
do
#       echo "$line2"
        counter1=0
 len2=${#line2}
        while read line1
        do
  
  len1=${#line1}
 
  char1=${line1:$len1-1:$len1}
                char2=${line2:$len2-1:$len2}
                if [ "$char1" = "," ]
                then
                        line11=${line1:0:$len1-1}
                else
                        line11=$line1
                fi
                if [ "$char2" = "," ]
                then
                        line22=${line2:0:$len2-1}
                else
                        line22=$line2
                fi
                if [ "$line11" != "$line22" ]
                then
                        counter1=`expr $counter1 + 1`
                fi
                if [ $counter1 -eq $value1 ]
                then
                        echo "this line not present : $line22"
#   here calling the insert method
   table_name=$( awk -F\` '/CREATE TABLE/{print $2}' testdump2.sql )
   insert_column $table_name $line22
                fi
        done < testdump1.sql
done < testdump2.sql