Shell Programming and Scripting

 
while read line123
do
        echo "line read is $line123"
        insert_funct $line123
done< mysqldump.sql
 
inser_funct()
{
    echo $1
}

insert_funct "$line123"

`reserved2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`reserved3` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`server` text COLLATE utf8_unicode_ci NOT NULL,

this line not present : `reserved6` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
sending these: Active and also `reserved6` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'echo "dollar 1: Active"
        echo "dollar 2: `reserved6` varchar(255) COLLATE utf8_u' at line 1
Script  failed please verify the sqls

#!/bin/bash
insert_func()
{
        column=$( echo $line | awk -F\` '{print $2}' )
        echo $column
}
while read line
do
        insert_func "$line"
done < mysqldump.sql

column=$( echo $line | awk -F\` '{print $2}' )

insert_func() 
{
  val=$1
 column=$( echo $val | awk -F\` '{print $2}' )
 echo $column
 }

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

}

value1 : 22
value2 : 20
========printing lines not present in new mysql dump(delete)=========
this line not present : `reserved1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
column name is : reserved1
./fileread: line 71: column:1:-1: command not found
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'column_name=reserved1
        echo "script to delete the column"
        echo "column : reserv' at line 1
Script  failed please verify the sqls

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

}

value1 : 22
value2 : 20
========printing lines not present in new mysql dump(delete)=========
this line not present : `reserved1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
column name is : reserved1
./fileread: line 71: column:1:-1: command not found
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'column_name=reserved1
        echo "script to delete the column"
        echo "column : reserv' at line 1
Script  failed please verify the sqls

#!/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 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()
{
 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

}

#
# 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 )
   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 < 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"
   column_name=$line22
#   here calling the insert method
   table_name=$( awk -F\` '/CREATE TABLE/{print $2}' testdump2.sql )
   
   echo "sending these: $table_name and also $line22"
   insert_column "$table_name" "$column_name"
                fi
        done < testdump1.sql
done < testdump2.sql