MultiLine Patterns

Shell Programming and Scripting
1

Experts,
I am novice unix user. At my work, most of our DBA's work on creating DDL's to create new tables in production. At every week we need to validate the scripts (do peer review) and it takes a while and also it is not effective when we have like 150 tables created in the scripts. I am working on trying to automate the validation process and need help from experts like you ! The most common problem is everyone can code the DDL in different way. For Ex:

Following SQL is expected in the script for every table:

INSERT INTO ABCXYX.PRIVILEGE_TABLE
(
 USER_NAME
,DATABASE_NAME
,TABLE_NAME
,COLLECT_STATS) 
VALUES 
( 
'ZDDW_SINK_BATCH'
,'ZDDW_SINK_TB'
,'SINK_BASEL_DFLT_TYPE'
,'Y'
);

It could also be written by some as :

INSERT INTO ABCXYX.PRIVILEGE_TABLE
(
 USER_NAME,DATABASE_NAME,TABLE_NAME,COLLECT_STATS) 
VALUES 
( 
'ZDDW_SINK_BATCH'
,'ZDDW_SINK_TB','SINK_BASEL_DFLT_TYPE'
,'Y'
);

and you can imagine there could be 100 different ways to do it.

I have reading a lot about regular expressions, where we can do a pattern search so that new lines, tabs white spaces can be compared with, but the basic problem is the code can span into multi lines, and the above was one condition to check. Every DDL can have another 100-120 conditions to check and i wanted you guide me how to proceed? Can multiline pattern search can be done in sed or awk ? or something totally different like perl?

If someone can give a piece a code which can be used to search for about statement in a file, i will customize to include all other conditions. Thank you very much in advance !

2

Try this (for starters) to get the "INSERT" DML statements in one line:

perl -lne 'if ( $seq = (/^\s*INSERT/i .. /;\s*$/)) {
push @ddl, $_;
if ($seq =~ /E0$/) { print @ddl; @ddl = () }
}' input_file
3

A similar awk:

awk '$1=="INSERT", $NF~/;$/{s=s $0; if($NF~/;$/){print s; s=x}}' file
4

My SQL has become a bit rusty, so I don't remember where semicolons are used to separate items, but for your above and similar cases this might do:

awk '{$1=$1}1' RS=";" ORS=";\n" FS="[ \n]*" OFS=" " file