#!/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.
#
#
# Test purpose testdump1.sql and testdump2.sql has 3 table each with 
# few columns names missing.
#
#
counter1=0
counter2=0
len1=0
len2=0
#
#code below to find number of tables present in both mysqldump files
#
perl -lne '(/^\s*CREATE\s+TABLE/i .. /^\s*\)\s+ENGINE=InnoDB/i) && print;' testdump1.sql > onlytables1.sql
perl -lne '(/^\s*CREATE\s+TABLE/i .. /^\s*\)\s+ENGINE=InnoDB/i) && print;' testdump2.sql > onlytables2.sql 
counter1=0
counter2=0
while read line1
do

if [[ `expr match "$line1" ".*CREATE TABLE.*"` != "0" ]]
then
counter1=`expr $counter1 + 1`
fi
done < onlytables1.sql
while read line2
do

if [[ `expr match "$line2" ".*CREATE TABLE.*"` != "0" ]]
then
counter2=`expr $counter2 + 1`
fi
done < onlytables2.sql
echo "no of tables in first file: $counter1"
echo "no of tables in second file: $counter2"
　
#
#function for inserting missed columns
#
insert_column()
{
mysql -udunkin -pdunkin123 ditdb << EOF

ALTER TABLE $1 ADD COLUMN $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()
{
echo "table name: $1"
echo "column name: $2"
mysql -udunkin -pdunkin123 ditdb << EOF

ALTER TABLE $1 DROP COLUMN $2;

QUIT
EOF
if [ $? -eq 0 ]
then
echo "$2 dropped from $1 Successfully "
else
echo "Script failed please verify the sqls"
exit 1
fi
}
#
#function for creating a new table
#
insert_table()
{
mysql -udunkin -pdunkin123 ditdb < tablextract2.sql <<EOF
QUIT
EOF


if [ $? -eq 0 ]
then
table_name=$( awk -F\` '/CREATE TABLE/{print $2}' tablextract2.sql )
echo "$table_name created Successfully "
else
echo "Script failed please verify the sqls"
exit 1
fi
}
#
#function for dropping a table
#
drop_table()
{
mysql -udunkin -pdunkin123 ditdb << EOF

DROP TABLE $1;

QUIT
EOF
if [ $? -eq 0 ]
then
echo "$1 dropped Successfully "
else
echo "Script failed please verify the sqls"
exit 1
fi
}
　
　
#
#Deleting old columns and Tables.
#
#
echo ""
echo "========printing lines not present in new mysql dump(delete)========="
echo ""
rm -r tablextract1.sql
runtimecount=0
while read line1
do
if [[ `expr match "$line1" ".*ENGINE=InnoDB.*"` != "0" ]]; then
echo ")" >> tablextract1.sql

while read line2
do
if [[ `expr match "$line2" ".*ENGINE=InnoDB.*"` != "0" ]]; then
echo ")" >> tablextract2.sql
########################################################################################
#
# this below piece of code is used to find columns/tables not present in new database
# and hence will involve method for deleting that columns/tables.
#
#cat tablextract1.sql
#cat tablextract2.sql
table_name1=$( awk -F\` '/CREATE TABLE/{print $2}' tablextract1.sql )
table_name2=$( awk -F\` '/CREATE TABLE/{print $2}' tablextract2.sql )
echo "table name1 : $table_name1"
echo "table name2: $table_name2"
if [ "$table_name1" = "$table_name2" ]
then
echo "Matched table 1 and 2: $table_name1"
value1=0
value2=0
　
while read linex
do
value1=`expr $value1 + 1`
# the abovel ine gives the number of line in the file
done < tablextract1.sql
while read linexx
do
value2=`expr $value2 + 1`
# the abovel ine gives the number of line in the file
done < tablextract2.sql
echo "value1 : $value1"
echo "value2 : $value2"

while read line111
do
# echo "$line111"
counter=0
len1=${#line111}
while read line222
do
len2=${#line222}
char1=${line111:$len1-1:$len1}
char2=${line222:$len2-1:$len2}
if [ "$char1" = "," ]
then
line11=${line111:0:$len1-1}
else
line11=$line111
fi
if [ "$char2" = "," ]
then
line22=${line222:0:$len2-1}
else
line22=$line222
fi
if [ "$line11" != "$line22" ]
then
counter=`expr $counter + 1`
fi
if [ $counter -eq $value2 ]
then
echo "this line not present : $line11"
#here calling the delete method
table_name=$( awk -F\` '/CREATE TABLE/{print $2}' tablextract1.sql )
val=$line11
#column_name=$( echo $val | awk -F\` '{print $2}' )
column_name=$( echo $line11 | awk -F\` '{print $2}' )
echo "column name is : $column_name"

delete_column "$table_name" "$column_name"
fi
done < tablextract2.sql
done < tablextract1.sql
else
runtimecount=`expr $runtimecount + 1`
echo "runtime count: $runtimecount"
fi
########################################################################################
rm -r tablextract2.sql
else
echo $line2 >> tablextract2.sql
fi
　
done < onlytables2.sql
echo "runtimecounter at end of first loop : $runtimecount"
echo "counter2 : $counter2"
if [ $runtimecount -eq $counter2 ]
then
drop_table "$table_name1"
fi
runtimecount=0 
rm -r tablextract1.sql
else
echo $line1 >> tablextract1.sql
fi
　
done < onlytables1.sql
　
#
#
#Inserting new columns and Tables.
#
echo ""
echo "========printing lines not present in old mysql dump(insert)========="
echo ""
runtimecount=0
while read line2
do
if [[ `expr match "$line2" ".*ENGINE=InnoDB.*"` != "0" ]]; then
echo ")" >> tablextract2.sql
while read line1
do
if [[ `expr match "$line1" ".*ENGINE=InnoDB.*"` != "0" ]]; then
echo ")" >> tablextract1.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.
#
table_name1=$( awk -F\` '/CREATE TABLE/{print $2}' tablextract1.sql )
table_name2=$( awk -F\` '/CREATE TABLE/{print $2}' tablextract2.sql )
echo "table name1 : $table_name1"
echo "table name2: $table_name2"
　
　
if [ "$table_name2" = "$table_name1" ]
then
echo "Matched table 1 and 2: $table_name2"
value1=0
value2=0
　
while read linex
do
value1=`expr $value1 + 1`
# the abovel ine gives the number of line in the file
done < tablextract1.sql
while read linexx
do
value2=`expr $value2 + 1`
# the abovel ine gives the number of line in the file
done < tablextract2.sql
echo "value1 : $value1"
echo "value2 : $value2"
while read line222
do
#echo "$line222"
counter=0
len2=${#line222}
while read line111
do
len1=${#line111}
char1=${line111:$len1-1:$len1}
char2=${line222:$len2-1:$len2}
if [ "$char1" = "," ]
then
line11=${line111:0:$len1-1}
else
line11=$line111
fi
if [ "$char2" = "," ]
then
line22=${line222:0:$len2-1}
else
line22=$line222
fi
if [ "$line11" != "$line22" ]
then
counter=`expr $counter + 1`
fi
if [ $counter -eq $value1 ]
then
echo "this line not present : $line22"
column_name=$line22
#here calling the insert method
table_name=$( awk -F\` '/CREATE TABLE/{print $2}' tablextract2.sql )
echo "sending these: $table_name and also $line22"
insert_column "$table_name" "$column_name"
fi
done < tablextract1.sql
done < tablextract2.sql
else
runtimecount=`expr $runtimecount + 1`
echo "runtime count: $runtimecount"
fi
　
　
########################################################################################
rm -r tablextract1.sql
else
echo $line1 >> tablextract1.sql
fi
　
done < onlytables1.sql
echo "runtimecounter at end of first loop : $runtimecount"
echo "counter1 : $counter1"
if [ $runtimecount -eq $counter1 ]
then
insert_table
fi
runtimecount=0

rm -r tablextract2.sql
else
echo $line2 >> tablextract2.sql
fi
　
done < onlytables2.sql