Bash - String verification method

Question

I have a lot of Teradata SQL files (example code of one of this file is below).

create multiset volatile table abc_mountain_peak as(
select 
a.kkpp_nip as nip,
from BM_RETABLE_BATOK.EDETON a 
) with data on commit preserve rows;

create multiset table qazxsw_asd_1 as (
select
a.address_id,
from  DE30T_BIOLOB.HGG994P_ABS_ADDRESS_TRE a,
) with data on commit preserve rows;

create multiset volatile table xyz_sea_depth as(
select 
a.trip,
from tele_line_tryt a
) with data on commit preserve rows;

CREATE multiset table wsxzaq_zxc_2 AS ( 
SELECT
a.bend_data 
FROM lokl_station a , 
) WITH data on commit preserve rows;

CREATE multiset table rfvbgt_ttuop_3 AS ( 
SELECT
a.heret_bini 
FROM fvgty_blumion a , 
) WITH data on commit preserve rows;

DROP qazxsw_asd_1;
DROP wsxzaq_zxc_2;

.EXIT

What I need to do is to create a script (bash), which could verify if the multiset tables are dropped. There are created two kinds of tables:

multiset volatile tables (which shouldn't be dropped), and
multiset tables (which must be dropped)

In my example code, 2 of 3 multiset tables are dropped (which is correct), and one of them is not (which is incorrect). Do You have any idea how to create script which could verify something like that (give information, that one table, or some tables aren't dropped)? I am really beginner in bash. My idea (could be wrong) is to create array holding a names of the multiset tables (but not a multiset volatile tables), and later create another one table with 'drop' and the names of dropped tables, and finaly check if every table from first array is also in second array. What do You think? Any help will be gratefully appreciate.

Which version of Bash are you using? I ask because an associative array of tables might be a good solution, but you need bash version 4 for that. — cdarke
– cdarke, Commented May 18, 2015 at 7:42
GNU bash, version 4.1.2(1)-release (x86_64-redhat-linux-gnu) — euro
– euro, Commented May 18, 2015 at 7:48

David C. Rankin · Accepted Answer · 2015-05-18 08:59:51Z

1

You can do it fairly easily by reading each line in the file, isolate the table names associated with the multiset table commands into one array (dropnames), you then isolate the table names following the DROP statements into another array (droptable). Then it is just a matter of comparing both arrays to find the table in one that is not in the other. A short script like the following will do it for you:

#!/bin/bash

declare -a tmparray                 ## declare array names
declare -a dropnames
declare -a droptable

volstr="multiset volatile table"    ## set query strings
dropstr="multiset table"

## read all lines and collect table names
while read -r line; do

    [[ $line =~ $dropstr ]] && {    ## collect "multiset table" names
        tmparray=( $line )
        dropnames+=( ${tmparray[3]} )
    }

    [[ $line =~ DROP ]] && {        ## collect DROP table names
        tmp="${line/DROP /}"
        droptable+=( ${tmp%;*} )
    }

    unset array

done

## compare droptable to dropnames, print missing table(s)
if [ ${#dropnames[@]} -gt ${#droptable[@]} ]; then

    printf "\n The following tables are missing from DROP tables:\n\n"

    for i in "${dropnames[@]}"; do
        found=0
        for j in "${droptable[@]}"; do
            [ $i = $j ] && found=1 && continue
        done
        [ $found -eq 0 ] && printf "   %s\n" "$i"
    done

elif [ ${#dropnames[@]} -lt ${#droptable[@]} ]; then

    printf "\n The following tables are missing from DROP tables:\n\n"

    for i in "${droptable[@]}"; do
        found=0
        for j in "${dropnames[@]}"; do
            [ $i = $j ] && found=1 && continue
        done
        [ $found -eq 0 ] && printf "   %s\n" "$i"
    done

fi

printf "\n"

exit 0

Output

$ bash sqlfinddrop.sh <dat/sql.dat

 The following tables are missing from DROP tables:

   rfvbgt_ttuop_3

answered May 18, 2015 at 8:59

David C. Rankin

85.1k6 gold badges67 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

euro Over a year ago

Wow! Thank You very much David! It's work! I have one more question - how to add a loop to read files after files from some location. I tried 'for' loop, but I got an 'ambiguous redirect' error and I don't know why. Do You have a time to give me some clues?

David C. Rankin Over a year ago

Sure: for i in dat/sql*.dat; do bash sqlfinddrop.sh <"$i"; done Meaning just put the script call inside the for loop and redirect one file at a time. for i in path/files*; do script <"$i"; done

David C. Rankin Over a year ago

@euro another option is the find command: find path/to/files -name "files*.txt" -exec bash -c "./yourscript.sh <'{}'" \; Note: you must make your script executable. (e.g. chmod 0755 scriptname) Also note, you can easily modify the script to take a filename as an argument and eliminate the redirect. You can even have it respond to both. It just means a few more lines of code.

David C. Rankin Over a year ago

Glad I could help. Bash is a rather large full-featured shell. Combined with GNU Coreutils, there is very little you can't do from a system administration standpoint. Take the time to read ABS, especially ABS-Manipulating Strings and you will begin to see the tip-of-the-iceberg regarding what you can do in bash (just the tip).

teambob · Accepted Answer · 2015-05-18 08:07:27Z

1

I would do it in two parts using sed:

Create list of creates:

sed -ne 's/^.*create multiset $volatile $\?table $\w\+$.*$/\2/Ip' INPUT FILES | sort > creates.txt

Create list of deletes:

sed -ne 's/^.*drop $\w\+$.*$/\1/Ip' INPUT FILES | sort > drops.txt

Tables which were created and dropped:

join creates.txt drops.txt

Tables created and not dropped:

combine creates.txt not drops.txt

edited May 18, 2015 at 8:07

answered May 18, 2015 at 7:46

teambob

2,09215 silver badges19 bronze badges

3 Comments

euro Over a year ago

Thanks teambob! Your method look great, but I have one problem, I mean, with the last line of Your code. It gives me the information 'combine: not found [No such file or directory]' and I don't know why, because everything else work fine, these two txt files are created with correct data 'join creates.txt drops.txt' is also works. Do You know, what might be the problem?

teambob Over a year ago

combine is part of moreutils. What OS are you using? Some form of Linux? Another less tidy approach would be to use diff

euro Over a year ago

WIndows 7 64-bit, but I also have a virtual machine on Linux.

Collectives™ on Stack Overflow

Bash - String verification method

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related