Shell Programming and Scripting

CREATE TABLE `Table11` (
`id` int(11) NOT NULL ,
`entityName` enum('Lines','EndUsers') COLLATE utf8_unicode_ci NOT NULL,
`parentAllianceMigrationProjectId`  varchar(255) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `Table22` (
`empNo` int(13) NOT NULL AUTO_INCREMENT,
`channels` enum('one','two','three') COLLATE utf8_unicode_ci NOT NULL,
`reserved6` varchar(255),
`accountStatus` enum('Active','Locked','Expired','Deactivated') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Active',
PRIMARY KEY (`empno`)
) ENGINE=InnoDB AUTO_INCREMENT=2000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `HOOOP` (
  `allianceSiteId` int(11) DEFAULT NULL,
  `trunkGroupsId` int(11) DEFAULT NULL,
  `lastModified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `allianceSiteId` (`allianceSiteId`,`trunkGroupsId`)
) ENGINE=InnoDB AUTO_INCREMENT=2000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `Table33` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`allianceId` int(11) NOT NULL,
`migratedAt` datetime DEFAULT NULL,
`migrationDetail` text COLLATE utf8_unicode_ci,
`unigyId` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `HOOOP` (
  `allianceSiteId` int(11) DEFAULT NULL,
  `trunkGroupsId` int(11) DEFAULT NULL,
  `lastModified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `allianceSiteId` (`allianceSiteId`,`trunkGroupsId`)
)

firstword : CREATE TABLE `AllianceSiteTrunkGroupTrunkGroupsMap` (
lastword : ) ENGINE=InnoDB AUTO_INCREMENT=2000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

sed '/"firstword"/,/""lastword/d' bigfile.sql

 sed '/"$firstword"/,/"$lastword"/d' bigfile.sql

sed "/$firstword/,/$lastword/d" bigfile.sql

sed '/'$firstword'/,/'$lastword'/d' bigfile.sql

table=HOOOP 
sed '/CREATE TABLE `'$table'`/,/^(/ d' bigfile.sql

 
tablename="HOOOP"
startpoint="CREATE TABLE `$tablename` ("
endpoint=") ENGINE=InnoDB AUTO_INCREMENT=2000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;"

CREATE TABLE `HOOOP` (
  `allianceSiteId` int(11) DEFAULT NULL,
  `trunkGroupsId` int(11) DEFAULT NULL,
  `lastModified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `allianceSiteId` (`allianceSiteId`,`trunkGroupsId`)
)

perl -lne '(/^\s*CREATE\s+TABLE/i .. /^\s*\)\s+ENGINE=InnoDB/i) && print;' bigfile.sql >tmp.sql

tablename="HOOOP" 
startpoint="CREATE TABLE `$tablename` (" 
endpoint=") ENGINE=InnoDB AUTO_INCREMENT=2000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;"

tablename="HOOOP"
startpoint="CREATE TABLE \`$tablename\` ("
endpoint=") ENGINE=InnoDB AUTO_INCREMENT=2000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;"

echo start $startpoint

sed "/$startpoint/,/$endpoint/ w tmp.sql" < mysql.big
sed "/$startpoint/,/$endpoint/ d" mysql.big > mysql.new

sed "/$startpoint/,/$endpoint/ w tmp.sql " < mysql.big

 
if $startpoint exists in the file
then
     sed "/$startpoint/,/$endpoint/ w tmp.sql " < mysql.big
else
      flag=1
fi

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

cat 1.sql | grep -i CREATE | wc | awk -F' ' '{print $1}'

cat testdump1.sql | grep -i "CREATE TABLE \`$tablename\` (" | wc | awk -F' ' '{print $1}'

sed "/$startpoint/,/$endpoint/ w tmp.sql " < mysql.big
if [ -s tmp.sql ] ; then  #is file non-empty?
   echo pattern found
else
   echo not found  #empty or doesnt exist
fi

grep -c "CREATE TABLE \`$tablename\` (" testdump1.sql