i have something like this in a file called mysqldump.sql
--
-- Table structure for table `Table11`
--
DROP TABLE IF EXISTS `Table11`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Table11` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`entityName` enum('Lines','EndUsers') COLLATE utf8_unicode_ci NOT NULL,
`parentAllianceMigrationProjectId` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `Table22`
--
DROP TABLE IF EXISTS `Table22`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Table22` (
`empNo` int(13) NOT NULL AUTO_INCREMENT,
`channels` enum('one','two','three') COLLATE utf8_unicode_ci NOT NULL,
) ENGINE=InnoDB AUTO_INCREMENT=2000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `Table33`
--
DROP TABLE IF EXISTS `Table33`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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,
) ENGINE=InnoDB AUTO_INCREMENT=2000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
what i need is to extract only the create table part of each table and store it in a file, say i need to extract only
CREATE TABLE `Table11` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`entityName` enum('Lines','EndUsers') COLLATE utf8_unicode_ci NOT NULL,
`parentAllianceMigrationProjectId` int(11) NOT NULL,
PRIMARY KEY (`id`)
)
the above part and store it in a file. i need this to be dynamic, i mean i will be using the script in a while loop so in first iteration of while loop i need table11 to be stored in a tmp.sql file and in next iteration the while loop automatically stores table22 in the tmp.sql file. since we need idetifiers to clip we could use "CREATE TABLE" as starting point and "ENGINE=InnoDB" as end point for clipping. this is very easy in java programming i dont know how to do this in shell scripting. any help is deeply appreciated
what this code does is write the file table wise. but in first iteration of while loop table11 is written and next run(interation ) table22 is written and so on. i need same way in shell scripts. thanks for your code, you code works fine but it gives clipped tables (all 3) in one shot. but i need then table wise one after the other since i have to perform some function in while loop. in java i am appending but in shell script i will be over writing to temporary file so that everytime (each run of while loop) writes fresh tables to temporary file.. thanks
@ahamed>> i am trying to learn shell scripting.. making effort step by step. i know all these help is lot to ask, but i am still in basics, but i have learnt few things so far. in time i will learn more... with ofcourse all you guys help
perl -lne '/^\s*CREATE\s+TABLE/i && open FILE, ">tmpfile.sql"; (/^\s*CREATE\s+TABLE/i .. /^\s*\)\s+ENGINE=InnoDB/i) && print FILE; /^\s*\)\s+ENGINE\InnoDB/i && close FILE;' mysqldump.sql
That would overwrite tmpfile.sql with the CREATE TABLE statement everytime a CREATE TABLE statement is found if I understand this is what you are looking for?
Thanks Mr.bean and thanks Ahamed.... you guys are genius... the code is working superb. just the way i wanted them... i will be asking few more help in near future but please dont mind..
i have one more doubt Mr.bean... if i use your code in a while loop will it start and stop from the line where it stoppped in previous run of while loop. like for eg
While loop runs first time, so table11 is written into a tmp file i will perfrom some actions then when while loop runs second time table22 should be in tmp file.. and so on... i havent tried your code in my code yet, had some work today will be trying out tomorrow.. i just executed your code in shell promt and it worked fine, tomorrow am gonna try it out in while loop i my code.. thanks a lot for the help
What the code does is to search for a line matching /create table/, then open-up a FILE handler.
Next would cause any lines matching in between /create table/ and /Engine=InnoDB/ as true which in turns write the stuff to the FILE handler.
Next when the script finds a line matching /Engine=InnoDB/, it would close the file handler that is opened previously for writing.
If you would like to perform some action every time a create statement is found, I suggest you put the code in either the /create table/ match or in the /Engine=InnoDB/ match
Yes Mr.bean.. i will be using it in only one while loop.. for the while loop runs many times(runs as number of tables present in the file) for eg in mysqldump.sql there are 3 tables so the while loop runs 3 times. so in the 1st run of while loop table11 is copied to a temporary file and closes the file. i will be performing some operations on extracting this file content. in the second run of while loop (we can consider for loop insteaded of while loop running on i<3 incrementing i everytime) table22 is written to same file and process goes on. your code does the job but it will be doing it in one shot. even if i try it in for loop or while loop the code which you sent writes the last table to file everytime. cause its designed to overwrite tables in one run or one shot. in the java code which i sent there is a line called br.readLine(). this line is like a pointer it stops and starts from where it left of in prvious run of loop. so in first run br.readLine write table11 in second run br.readLine write table22 and 3rd table33. hope you understood what i am trying to achieve... :-/
example:
While [some condition] // i can use for loop too
(condition say when i=0)
do
your code goes here...which writes table11 to tmp.sql
my code goes here which read from this tmp.sql file which has table11 and do some function
done
in the same loop when i=1 table22 is written to temporary file.. and when i=2 table33 is written to file..
---------- Post updated at 02:52 PM ---------- Previous update was at 02:40 PM ----------
is there any code in shell scripting which performs the function of ".contains" of java
i mean
somevariable has a sentence or long line say
somevariable="this is a line to show CREATE TABLE sometablename is used to create table"
if(somevariable has "CREATE TABLE")
then
counter++;
fi
---------- Post updated at 02:55 PM ---------- Previous update was at 02:52 PM ----------
i got the answer for .contains...
if [[ $var == *CREATE* ]]
then
echo "its present"
fi
---------- Post updated at 03:20 PM ---------- Previous update was at 02:55 PM ----------
sorry the above one dint workout.. however below one worked
while read line1
do
if [[ `expr match "$line1" ".*CREATE TABLE.*"` != "0" ]]
then
counter1=`expr $counter1 + 1`
fi
done < counterfile1.sql
thanks a lot.. the code worked. (after this code i think my code will be almost done. i will send my whole code to you when its finished.. thanks a lot for the help )
one last doubt regarding this....
if(/^\s*CREATE TABLE/i) {
and
last if(/ENGINE=InnoDB/i);
would this two syntax work in shell sh... cause you have used perl here right...
yeah, "last" is just breaking the loop. In bash, use break
#!/bin/bash
while read line
do
if [[ "${line}" =~ "^CREATE TABLE" ]]
then
# add code to begin over-writing temp file
echo "** start over-writing ... or do some actions ... "
echo ${line}
while read line
do
echo ${line}
[[ "{$line}" =~ "ENGINE=InnoDB" ]] && break
done
fi
done
# cat mysqldump.sql | sh parser.sh
** start over-writing ... or do some actions ...
CREATE TABLE `Table11` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`entityName` enum('Lines','EndUsers') COLLATE utf8_unicode_ci NOT NULL,
`parentAllianceMigrationProjectId` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
** start over-writing ... or do some actions ...
CREATE TABLE `Table22` (
`empNo` int(13) NOT NULL AUTO_INCREMENT,
`channels` enum('one','two','three') COLLATE utf8_unicode_ci NOT NULL,
) ENGINE=InnoDB AUTO_INCREMENT=2000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
** start over-writing ... or do some actions ...
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,
) ENGINE=InnoDB AUTO_INCREMENT=2000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
attched is the final code...
thanks to Mr.bean and ahamed... without the help from you guys i wouldnt have been able to complete this :-).... the code is working superb..(it feels great whenever we accomplish what we set out to do)
#!/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
[/SIZE]
what this code does is
.If there is a new column present in table of new database (new mysql dump) and not present in old mysql dump it will add this new column to respective table(it writes the mysql query and executes it)
. if there is a column in a table of old database which is not present in table of new database it deletes the column in respective table .
. In old database if there is a table which is non - existent in new database it will delete that table.
. in new database if there is a table which is not present in old database it will add this new table to database.
---------- Post updated at 04:06 PM ---------- Previous update was at 04:03 PM ----------
i had clearly indented it.. when i pasted the code here it became a mess and unreadable... sorry..
