Making case insensitive perl statement

Shell Programming and Scripting
1 
 
cat > tble
blah blah 
blah sdfsdf dsdf sdf .d.f ..df .. sdf..
create table NextID (
        id int auto_increment,
        zoneID int,
        entityName varchar(64),
        nextID int,
        lastModified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        primary key(id)
) engine=Innodb;
sdfsdf.
.
.
 
CREATE TABLE  ReplicatorInfo  (
   id  INT(11) NOT NULL AUTO_INCREMENT,
   lastMessageReceivedTime   TIMESTAMP DEFAULT 0,
   lastModified  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY ( id )
)ENGINE=INNODB;
blah blha etc etc
.
.
.
[root@dunkin-ds-dev-103 vivek]#  perl -e 'print reverse <>' "tble"|perl -lne '(/\s+engine=Innodb/i .. /create\s+table/i) && print;'|perl -e 'print reverse <>' > onlytables.sql
[root@vivek]#
[root@vivek]#
[root@vivek]# cat onlytables.sql
create table NextID (
        id int auto_increment,
        zoneID int,
        entityName varchar(64),
        nextID int,
        lastModified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        primary key(id)
) engine=Innodb;
[root@vivek]#

consider the above scenario, the expected output after executing the above perl statement
should be

cat onlytables.sql
create table NextID (
        id int auto_increment,
        zoneID int,
        entityName varchar(64),
        nextID int,
        lastModified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        primary key(id)
) engine=Innodb;
CREATE TABLE  ReplicatorInfo  (
   id  INT(11) NOT NULL AUTO_INCREMENT,
   lastMessageReceivedTime   TIMESTAMP DEFAULT 0,
   lastModified  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY ( id )
)ENGINE=INNODB;

(i dont need new awk statements)i just want to modify the perl statement used above cause the print reverse function is required for other scenarions.. i just want to make it case insensitive for "create table" and "engine=innodb" which apparently is not working as i desplayed the output at the start..

perl -e 'print reverse <>' "tble"|perl -lne '(/\s+engine=Innodb/i .. /create\s+table/i) && print;'|perl -e 'print reverse <>' > onlytables.sql
2

Are your "create table" block separated by an empty line ?

CREATE TABLE ..
bla bla
...
...(END OF CREATE TABLE);

What i mean is : can we consider the empty line as a record separator into the file (and thus, extract Records that start with "CREATE TABLE" ?

3

no no it should start with "create table" and end with "engine=Innodb"... this
constitute a complete table... run the perl script it perfroms the above perfectly... i
only need to modify it to perfrom the same function even if it is "CREATE TABLE"
"ENGINE=InnoDB" or "create table" "enigne=innodb" or any permutations..

4 
awk 'tolower($0)~/^create table/,tolower($0)~/engine=innodb;/' yourfile

$ awk 'tolower($0)~/^create table/,tolower($0)~/engine=innodb;/' tst
create table NextID (
        id int auto_increment,
        zoneID int,
        entityName varchar(64),
        nextID int,
        lastModified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        primary key(id)
) engine=Innodb;
CREATE TABLE  ReplicatorInfo  (
   id  INT(11) NOT NULL AUTO_INCREMENT,
   lastMessageReceivedTime   TIMESTAMP DEFAULT 0,
   lastModified  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY ( id )
)ENGINE=INNODB;
5

thanks the above works but i need the code to be in perl because in the code i am using print reverse.. which is required for some important functionality...

perl -e 'print reverse <>'

is there anyway you could modify same perl code to work case insesitively plz.. :-/

check below page for the situation it might arrise if i dont use print reverse

http://www.unix.com/shell-programming-scripting/173670-extracting-block-lines-file.html
6

Regexp issue:

perl -e 'print reverse <>' "tble"|perl -lne '(/engine=Innodb/i .. /create\s+table/i) && print;'|perl -e 'print reverse <>' > onlytables.sql
1 Like
7

thanks klash your code worked... :slight_smile: