28
Feb
10

How to Grep Multiple Lines


This page describes how to find and replace patterns that span across multiple lines.

Once I was coding up some SQL DDL’s and came across a problem. The program I used to generate the DDL did not generate the foreign key relationships such that they could be read by mySQL. The create table looked like this…

CREATE TABLE pwd_category (
       category_i           SMALLINT NOT NULL,
       passwd_i             INTEGER NOT NULL,
       PRIMARY KEY (category_i, passwd_i), 
       FOREIGN KEY (passwd_i)
                             REFERENCES pwd_master, 
       FOREIGN KEY (category_i)
                             REFERENCES pwd_category_master
);

The problem seen above is that mySQL does not like the above format. Instead it wants the DDL to look like this…

CREATE TABLE pwd_category (
       category_i           SMALLINT NOT NULL,
       passwd_i             INTEGER NOT NULL,
       PRIMARY KEY (category_i, passwd_i), 
       FOREIGN KEY (passwd_i) REFERENCES pwd_master  (passwd_i),
       FOREIGN KEY (category_i) REFERENCES pwd_category_master (category_i)
);


Based on sed(1)’s man page you can execute a series of commands within the { } block.

One of the commands is N;
n N Read/append the next line of input into the pattern space.

Step 1

Detect the pattern “FOREIGN KEY” and read the next line.

/FOREIGN KEY/ – search for lines starting with FOREIGN KEY

sed -e '/FOREIGN KEY/{N;p}'

Step 2

{} – apply the enclosed when the preceeding match is made
N – append the next line to the pattern buffer
p – prints the buffer that was just read

The resulting conversion looks like this…

CREATE TABLE pwd_category (
       category_i           SMALLINT NOT NULL,
       passwd_i             INTEGER NOT NULL,
       PRIMARY KEY (category_i, passwd_i), 
       FOREIGN KEY (passwd_i)
                             REFERENCES pwd_master, 
       FOREIGN KEY (passwd_i)
                             REFERENCES pwd_master, 
       FOREIGN KEY (category_i)
                             REFERENCES pwd_category_master
       FOREIGN KEY (category_i)
                             REFERENCES pwd_category_master
);

As you can see that the lines that matched are duplicated…

Step 4

Next we apply a substution using the following pattern

s/FOREIGN KEY \((.*)\).*REFERENCES \(.*\)/FOREIGN KEY \1 REFERENCES \2 \1,/

sed -e '/FOREIGN KEY/{N;s/FOREIGN KEY \((.*)\).*REFERENCES \(.*\)/FOREIGN KEY \1 REFERENCES \2 \1,/p}'

This results in output like this…

CREATE TABLE pwd_category (
       category_i           SMALLINT NOT NULL,
       passwd_i             INTEGER NOT NULL,
       PRIMARY KEY (category_i, passwd_i), 
       FOREIGN KEY (passwd_i) REFERENCES pwd_master,  (passwd_i),
       FOREIGN KEY (passwd_i) REFERENCES pwd_master,  (passwd_i),
       FOREIGN KEY (category_i) REFERENCES pwd_category_master (category_i),
       FOREIGN KEY (category_i) REFERENCES pwd_category_master (category_i),
);

Step 5

Next we get rid of the dups by appending a:
s/\n.*// – delete everything following the first \n.

sed -e '/FOREIGN KEY/{N;s/FOREIGN KEY \((.*)\).*REFERENCES \(.*\)/FOREIGN KEY \1 REFERENCES \2 \1,/;s/\n.*//p}'

This results in output like this…

CREATE TABLE pwd_category (
       category_i           SMALLINT NOT NULL,
       passwd_i             INTEGER NOT NULL,
       PRIMARY KEY (category_i, passwd_i), 
       FOREIGN KEY (passwd_i) REFERENCES pwd_master,  (passwd_i),
       FOREIGN KEY (category_i) REFERENCES pwd_category_master (category_i),
);

Step 6

Next we need to apply some additional clean-up patterns.

sed -e 's/,.*(/ (/' |
sed -e 's/^);/) ENGINE=InnoDB;/'

This results in…

CREATE TABLE pwd_category (
       category_i           SMALLINT NOT NULL,
       passwd_i             INTEGER NOT NULL,
       PRIMARY KEY (category_i, passwd_i), 
       FOREIGN KEY (passwd_i) REFERENCES pwd_master (passwd_i),
       FOREIGN KEY (category_i) REFERENCES pwd_category_master (category_i),
) ENGINE=InnoDB;

The only thing remaining is to cleanup the extra comma. Ohh well. I will fix this some other time…

Advertisements

0 Responses to “How to Grep Multiple Lines”



  1. Leave a Comment

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 74 other followers

February 2010
S M T W T F S
« Jan   Mar »
 123456
78910111213
14151617181920
21222324252627
28  

Blog Stats

  • 801,303 hits

%d bloggers like this: