Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Perl script to extract pattern of specific text for a given directory and all of  its sub-directories

Posted on 2014-09-23
3
Medium Priority
?
138 Views
Last Modified: 2015-08-23
I need to write a Perl program to  search every file (with particular file extension, say .sql) under all sub directories and grep the patterns of

"update "
"delete "
"alter table "

output of the report should be:

directory_path:fileA:NNNN: update  ......    full statement until a ";" is located
directory_path:fileA:NNNN: alter table ....  full statement until a ";" is located
directory_path:fileB:NNNN: delete ....

where [directory_path] should be the path from partent directory , current directory
             NNNNN is the line number in that file


Sample directory path:
Current directory
db-2.0.1.5
    sir-abcd
           fileA.sql
           fileB.sql
    sir-efjk
           fileC.sql
           fileD.sql
db-2.0.1.6
     sir-xyz
           fileA.sql
           fileC.sql
db-2.0.1.6

Note:fileA.sql under db-2.0.1.5/sir-abcd  may not be the same as  fileA.sql under db-2.0.1.6/sir-xyz

Therefore output could be:

db-2.0.1.5/sir-abcd:fileA:1234: update  ......    full statement until a ";" is located
db-2.0.1.6/sir-xyz:fileA:675: alter table ....  full statement until a ";" is located
directory_path:fileB:NNNN: delete ....

Note:  ordering of  db-2.0.1.5, db-2.0.1.6 is important
0
Comment
Question by:tindavid
  • 2
3 Comments
 
LVL 85

Expert Comment

by:ozo
ID: 40338681
If there are several lines between the update  ...... and the full statement until a ";" is located
would you want
directory_path:fileA:NNNN:
on each of those lines, or only on the line that matched "update "?
0
 
LVL 85

Accepted Solution

by:
ozo earned 1500 total points
ID: 40338698
#!/usr/bin/perl
use strict;
use warnings;
use File::Find;
find(sub{
    /\.sql$/ || return;
    open F,$_ or warn "$_ $!";
    my $name="$File::Find::dir:$_";
    while( <F> ){
         print "$name:$.: $_" if s/.*?(?=update |delete |alter table )// .. s/;.*/;/;
    }
    close F;
},".");
0
 

Author Comment

by:tindavid
ID: 40340746
Hi Ozo,

Looks ok, couple of improvement needs to be done.

1) for a DDL statement spread into lines, please contact them into single line
2) some data issue and the raw data needs to be filtered for actual DDL statement:

Assume all DDL must start on a line without any preceeding character and ended with ";" .  However, data in below migh or might now be a vlalid DDL:

          delete from table     <--  valid DDL  even has spaces before delete
rem  delete xxx                   <-- not a valid DDL, due to 'rem" or "Rem" indicates it is a comment line
delete from                         <--  valid DDL
   alter table                         <-- valid DDL
--         delete from table   <-- not a valid DDL, due to '--" indicates it is a comment line
 /*   delete from table    <-- not a valid DDL due to /* is the beginning of comment area, and */ need to end the comment area
     where xxxxx ....;
*/


Please be note that : --,  REM indicates the text after this key word act as comment line.
/* and */   indiactes any text in between will consider as comment
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
Measuring Server's processing rate with a simple powershell command. The differences in processing rate also was recorded in different use-cases, when a server in free and busy states.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Six Sigma Control Plans
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question