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
There is only 1 while loop.
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:
Code:
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
Code:
somevariable="this is a line to show CREATE TABLE sometablename is used to create table"
Code:
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 :-)
Code:
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....
Code:
if(/^\s*CREATE TABLE/i) {
and
Code:
last if(/ENGINE=InnoDB/i);
would this two syntax work in shell sh... cause you have used perl here right...
Last edited by Franklin52; 11-24-2011 at 06:51 AM..
Reason: Correcting code tags
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....
Code:
if(/^\s*CREATE TABLE/i) {
and
Code:
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
Code:
#!/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
Code:
# 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)
Code:
#!/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
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..
Hello!
I'm trying to process a text file and am stuck at 2 extractions. Hoping someone can help me here:
1. Given a line in a text file and given a keyword, how can I extract the word preceeding the keyword using a shell command/script?
For example: Given a keyword "world" in the line: ... (2 Replies)
Hello,
I have a file ff.txt that looks as follows
*ABNA.txt
356
24
36
112
*AC24.txt
457
458
321
2
ABNA.txt and AC24.txt are the files in the folder named foo1. Based on the numbers in the ff.txt file, I want to extract the lines from the corresponding files in the foo1 folder and... (2 Replies)
consider below file contents
cat myOutputFIle.txt
8 CCM-HQE-ResourceHealthCheck:
Resource List :
No RED/UNKNOWN resource Health entries found
----------------------------------------------------------
9 CCM-TraderLogin-Status:
Number of logins: 0... (4 Replies)
I have a output file which contains n number of document.Each document has n number of segments and identified using below points
The starting segment is ISA and Ending segment is IEA
Each document has unique number and it will be passed in REF*D9 segment
Each line in sample file is called... (3 Replies)
here below is a part of the file
cat fileName.txt
NAME=APP-VA-va_mediaservices-113009-VA_MS_MEDIA_SERVER_NOT_PRESENT-S
FIXED=false
DATE= 2013-02-19 03:46:04.4
PRIORITY=HIGH
RESOURCE NAME=ccm113
NAME=APP-DS-ds_ha-140020-databaseReplicationFailure-S
FIXED=false
DATE= 2013-02-19... (4 Replies)
Hi,
I am trying to extract lines from a text file given a text file containing line numbers to be extracted from the first file. How do I go about doing this? Thanks! (1 Reply)
hi
i have an input file that contains some thing like this
aaa acc aa abc1 1232 aaa abc2....
poo awq aa abc1 aaa aaa abc2
bbb bcc bb abc1 3214 bbb abc3....
bab bbc bz abc1 3214 bbb abc3....
vvv ssa as abc1 o09 aaa abc4....
azx aaq aa abc1 900 aqq abc19....
aaa aa aaaa abc1 899 aa... (8 Replies)
consider the following is the contents of the file
cat 11.sql
drop procedure if exists hoop1 ;
Delimiter $$
CREATE PROCEDURE hoop1(id int)
BEGIN
END
$$
Delimiter ;
.
.
.
.
drop procedure if exists hoop2;
Delimiter $$
CREATE PROCEDURE hoop2(id int)
BEGIN
END
$$ (8 Replies)
Hi,
I have one file, say file 1, that has data like below where 19900107 is the date,
19900107 12 144 129 0.7380047
19900108 12 168 129 0.3149017
19900109 12 192 129 3.2766666E-02
... (3 Replies)
Hi,
anyone has any ideas on how do we extract lines from a file with format similiar to this: (based on current time)
Jun 18 00:16:50 .......... ............. ............
Jun 18 00:17:59 .......... ............. ............
Jun 18 01:17:20 .......... ............. ............
Jun 18... (5 Replies)
