Sorting lines based on keywords for MySQL script


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Sorting lines based on keywords for MySQL script
# 1  
Old 01-06-2012
Sorting lines based on keywords for MySQL script

the thing which i require is very very complex.. i tried hard to find the solution but couldnt..
the thing i need to achieve is say i have a file

Code:
 
cat delta.sql
CREATE VIEW Austin
Etc etc
.
.
.
CREATE VIEW Barabara
AS
SELECT blah blah blah
FROM Austin z, Cluster s, Instance i
WHERE s. ZoneId = z.id AND z. InstanceId = i.id;
CREATE VIEW Chester
AS
SELECT z.id Id, z.name name, s. vip, i.name Name
FROM Barbara z, Cluster s, Instance i
WHERE s. ZoneId = z.id AND z. InstanceId = i.id;
 
CREATE VIEW Zithorn
AS
SELECT z.id Id, blah blah blah
FROM Austin z, Cluster s, Instance i
WHERE s. ZoneId = z.id AND z. InstanceId = i.id;

The above order works because Barbara and Zithorn creates view from Austin and Chester creates view
from Barabara…. The think I want to convey is the views access another view once its already created….
The thing where I am stuck is ..
Code:
 
Cat delta.sql
CREATE VIEW Austin
Etc etc
.
.
.
CREATE VIEW Chester
AS
SELECT z.id Id, z.name name, s. vip, i.name Name
FROM Barbara z, Cluster s, Instance i
WHERE s. ZoneId = z.id AND z. InstanceId = i.id;
CREATE VIEW Barabara
AS
SELECT blah blah
FROM Austin z, Cluster s, Instance i
WHERE s. ZoneId = z.id AND z. InstanceId = i.id;
 
CREATE VIEW Zithorn
AS
SELECT z.id Id, blah blah blah
FROM Austin z, Cluster s, Instance i
WHERE s. ZoneId = z.id AND z. InstanceId = i.id;

Here observe that Chester tries to access from Barbara but since the file flows in order
during execution, it will give an error cause when it flows in order and executes view
Chester it tries to verify whether there is a view called Barbara. since its not present at the
moment (only present after this view or few lines below this view) it will give an error…
so I need a script which finds this type of bug and writes it in order that is somewhat like sorting…
I have come up so far as below

Code:
 
while read line
do
                if grep -q -i "create view" <<<$line
                  then
                firstword="$line"
#extracting the a view in a single file viewextract1.sql
                sed -n "/$firstword\$/,/;/p" delta.sql >viewextract1.sql
 
                view_name1=`echo $firstword | awk -F' ' '{print $3}'`
                fromView=$( cat  viewextract1.sql | grep "FROM" | grep 'FROM' | awk '{print $2}' )
#now I don’t know what logic  I can use here from here on to sort the views accordingly.. L L
#one way I thought of doing it is to find the line no of fromView which is present in a line 
“create view $fromView” and also find the line no of current view and if the $fromView in “cerate view” 
 line is greater than the line no of current view then cut paste the “create view $fromView” just above 
current view… 
          fi
 
done < delta.sql

# 2  
Old 01-06-2012
Based on the input given .. Try with this ..
Code:
$ cat file_to_run
for i in $(nawk '/CREATE VIEW /{print $NF}' delta.sql)
do
        parent_view=$(sed -n "/CREATE VIEW $i/,/id;/p" delta.sql | nawk '/FROM/{print $2}')
        sed -n "/CREATE VIEW $i/q;p" delta.sql | grep "CREATE VIEW $parent_view" > /dev/null
        [ $? -eq 0 ] && echo "$i --> Success" || echo "$i --> Failure"
done < delta.sql
$
$ ./file_to_run
Austin --> Failure
Chester --> Failure
Barabara --> Success
Zithorn --> Success
$

This User Gave Thanks to jayan_jay For This Post:
# 3  
Old 01-06-2012
Code:
awk '/CREATE VIEW/{x=$NF;A[x]=NR}/FROM/{B[x]=(($2 in A)?"success":"fail")}END{for(i in A) print i"--->"B[i]}' yourfile

These 2 Users Gave Thanks to ctsgnb For This Post:
# 4  
Old 01-06-2012
Awesome .. Smilie
# 5  
Old 01-06-2012
thanks jayan_jay & ctsgnb both the code worked for me:-)
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Split content based on keywords

I need to split the file contents with multiple rows based on patterns Sample: Input: ABC101testXYZ102UKMNO1092testing ABC999testKMNValid Output: ABC101test XYZ102U KMN1092testing ABC999test KMNValid In this ABC , XYZ and KMN are patterns (6 Replies)
Discussion started by: Jairaj
6 Replies

2. Shell Programming and Scripting

Split content based on keywords

I need to split the file contents with multiple rows based on patterns Sample: Input: ABC101testXYZ102UKMNO1092testing ABC999testKMNValid Output: ABC101test XYZ102U KMN1092testing ABC999test KMNValid In this ABC , XYZ and KMN are patterns Continue here./mod] Please read forum... (1 Reply)
Discussion started by: Jairaj
1 Replies

3. Shell Programming and Scripting

Extracting words and lines based on keywords

Hello! I'm trying to process a text file and am stuck at 2 extractions. Hoping someone can help me here: 1. Given a line in a text file and given a keyword, how can I extract the word preceeding the keyword using a shell command/script? For example: Given a keyword "world" in the line: ... (2 Replies)
Discussion started by: seemad
2 Replies

4. Shell Programming and Scripting

Sorting block of lines based on specified strings

Below is the part of contents of a file cat outputFile.txt :APP-MS-service_monitoring-118054-DiskFilling-S Description=Disk is getting filled. If Condition=If Event/Log Level(ANY) and matching substring &quot;DiskFilling&quot; Log: xx.x.x..xx.: 4 days 1:33:54.42 Date and time=03-18-2013... (2 Replies)
Discussion started by: vivek d r
2 Replies

5. Shell Programming and Scripting

Extract lines from file using keywords using script

Hi I need some lines of text from input file using keywords. Inputfile IP IS 10.238.52.65 pun-ras-bng-mhs-01#context bsnl.in Card Status : 1:0, 2:1, 3:1, 4:1, 5:0, 6:0, 7:0, 8:0, 9:1, 10:0, 11:0, 12:0, 13:0, 14:1, Max Circuits: 1: 0, 2: 32768, ... (5 Replies)
Discussion started by: surender reddy
5 Replies

6. Shell Programming and Scripting

HELP: Shell Script to read a Log file line by line and extract Info based on KEYWORDS matching

I have a LOG file which looks like this Import started at: Mon Jul 23 02:13:01 EDT 2012 Initialization completed in 2.146 seconds. -------------------------------------------------------------------------------- -- Import summary for Import item: PolicyInformation... (8 Replies)
Discussion started by: biztank
8 Replies

7. Shell Programming and Scripting

copy range of lines in a file based on keywords from another file

Hi Guys, I have the following problem. I have original file (org.txt) that looks like this module v_1(.....) //arbitrary number of text lines endmodule module v_2(....) //arbitrary number of text lines endmodule module v_3(...) //arbitrary number of text lines endmodule module... (6 Replies)
Discussion started by: kaaliakahn
6 Replies

8. Shell Programming and Scripting

sorting based on alternative lines

Hello, I have a file with multiple entries. @SFGF-GA2-1_58:5:36:11009:999#0/1 NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN +SFGF-GA2-1_58:5:36:11009:999#0/1 ################################################################################... (10 Replies)
Discussion started by: Diya123
10 Replies

9. UNIX for Advanced & Expert Users

Forwarding based on keywords in sendmail

I have an application that runs on the server with root privileges and all emails it sends get sent to root (errors, logs, etc), when they should actually go to one of application admins. I would like to separate these emails from the OS related one sent to root and forward them to that... (2 Replies)
Discussion started by: vostrushka
2 Replies

10. Shell Programming and Scripting

Capture lines based on keywords

Hello everyone, I am trying to write a script that will capture few lines from a text file based on 2 keywords in the first line and 1 keyword in the last one. It could also be based on the first line only + the folllowing 3 lines. Could some one help or give directions. Thanks. (4 Replies)
Discussion started by: nimo
4 Replies
Login or Register to Ask a Question