vi Commands in a Script

Shell Programming and Scripting
1

Perhaps there is a better way to do this, but right now this is all I can think of. If there is a better way to do this, all suggestions are welcome.

I would like to take a file and perform the following actions on it.

  1. Search for CREATE TABLE
  2. Copy that line and paste it one line above
  3. Change the CREATE to a DROP
  4. Change the '(' at the end to a ';'
  5. Run until the EOF is reached, then save

I have a script written that goes as follows:

vi gl.ddl << EOF
/CREATE TABLE
yy
k
p
cw DROP
$
cw ;
n
n
ZZ
EOF

It currently does not work at all, and throws the following message when I try to execute it.

ex: 0602-134 An error occurred while reading input.

I initially thought it was a file permission problem, so set the perms to 777, but still receive the same message.

Thanks in advance for any insights.

Schmitty

2

This'll work for you, unless you really wanted to use vi commands for some reason...

while read LINE
do
 echo $LINE >> resultFile
 echo $LINE | grep -q "CREATE TABLE"
 if [ $? -eq 0 ]; then
  echo $LINE | sed -e 's/CREATE/DROP/' -e 's/($/;/' >> resultFile
 fi
done << someFile
3

The only reason I wanted to use vi commands is because that's what I knew how to do. I did not know there was a LINES keyword that would read in a file line-by-line.

How does the script know what file to read with the LINES keyword?

Here's the problem that I'm finding.

  1. When I run it like you coded, I receive the message 'Syntax error at line 33: `<' is not matched' which refers to the 'done <<' part of the script.

  2. If I take out the '<< fileNAME' after the done, it appears that the file is never created.

Thanks for your help as I do not have experience with a script like this yet.

4

Actually, the structure

while read someVariable
do
  stuff
done < fileName

is just a way to step through a file. With each iteration of the "while" loop, the next line in fileName is stored in someVariable.

So LINE is just what I chose to call the variable. If you take out the part you took out, then the script will hang-up because it doesn't have a filename to read from (at that point, it's actually waiting for the user to provide the input).

However, I screwed up - replace done << someFile with done < someFile.

5

Thank you for the help, the script works. I put the output file name in the done < inputFile part, I didn't realize that was supposed to be in input file.

Thanks for your help, this knowledge will benefit me greatly.

6

I believe your requirement was to print the altered line (the "DROP" line) before the original, i.e., to drop a table and then re-create it. I think this will work as well. If your source file is thousands of lines long, it will also be appreciably faster.

awk '/CREATE TABLE/ {
        s = $0
        k = index(s, "CREATE")
        s = substr(s,1,k-1) "DROP" substr(s,k+6)
        k = index(s, "(")
        s = substr(s,1,k-1) " ;"
        print s
    }
    { print }' < infile > outfile # print all lines

This should work in all awks. See below for nawk notes.

This assumes that your "CREATE TABLE" statement is completely contained on one line, which I think is implicit in the vi keystrokes you originally posted.

Re: nawk
nawk (or gawk or /usr/xpg4/bin/awk or ...) has facilities that make dealing with substitution and mixed case input a bit easier:

nawk 'match(t = toupper($0), /CREATE TABLE/) {
        t = substr(t,1,RSTART-1) "DROP" substr(t,RSTART+6)
        sub(/\(.*/, ";", t)
        print t
    }
    {print}' < infile > outfile

The backslash before the paren in the regexp may not be needed, depending on your version of nawk.

Note that both oombera's solution and mine create a second file, and do not, indeed can not, write the file in place.

7

Actually, you might be creating far more work for yourself than is necessary.

If you are trying to create an sql to create a sequence of drop commands for tables that have been created you might consider accessing the RDBMS catalog tables to do this.

In Informix I would do somthing like:

select "drop ", tabname, ";"
from systables
where tabid > 99;

The results can be saved to a file and subsequently used as an sql with a minumum of editing. You may further qualify the tables to drop with additional clauses to the where part, or by unioning a sequence of similar sqls together.

This is for Informix, and the solution will differ for other RDBMS, like Oracle or Sql Server.

Here is link to a useful BB for databases: www.dbforums.com.

MBB

8

Thanks for the info, mbb. I had not thought about that, and would be a good solution for just drop commands.

However, what I'm trying to accomplish is to take the DDL in PROD and re-create those tables in TEST. So I'm doing a db2look command against PROD to get the DDL, and then adding a line before each CREATE command to DROP the table being created.

It could be two steps though - one for dropping and one for creating. I'll have to see which works better. Thanks again.