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
109 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 84

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 84

Accepted Solution

by:
ozo earned 500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
In threads here at EE, each comment has a unique Identifier (ID). It is easy to get the full path for an ID via the right-click context menu. However, we often want to post a short link within a thread rather than the full link. This article shows a…
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

737 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