Informix Dynamic Server  

Strip_index script update

When I created the original script that is included on the media for both books, I did not check to see if it (the script) would work properly on schemas without constraint statements. Turns out the original script doesn't handle it properly. Here is an update that fixes the problem.

#!/bin/ksh
# 
# strip_index
#
# Created by Valentin Carciu  val@garpac.com
# Modified by Carlton Doe
# @(#)strip_index.sh	1.4 16:08:27 09/29/99
#
# original notes by creator:
#    This script takes a *.sql created by dbexport and takes out all 
#    "create index" statements and puts them in an *.sql.idx file to be 
#    renamed to *.sql and to be used with dbaccess (or isql) after the tables 
#    have been loaded.
#
# Comments by Carlton
#  This script has been reworked to strip out "add constraint" commands as
#  well.  While the original logic of the program is still intact, I 
#  introduced a couple of intermediary files to hold the output from striping
#  the indexes.  These intermediary files were then used to create the
#  final output files.  I'm not sed literate in the least so if you can
#  improve upon this script, please send me e-mail at dbaresrc@xmission.

# Usage: strip_index 

usage()
{
echo ""
echo "usage: strip_index [filename]"
echo ""
}

## Main function
if test $# -eq 0
  then
     usage
     exit 0
fi


input=$1                                                                                                                            
index_output="$1_idx.sql"
index_temp="$1_idx_tmp"
schema_temp="$1_tmp"
schema_output="$1_schm.sql"
temp1="temp1"
temp2="temp2"

## start by finding the index statements and clear those out
echo ""
echo "Parsing out the index statements"

sed -e "/create.*index.*;$/w $index_output" \
    -e "/create.*index.*;$/d" $input > $temp1

sed -e "/create.*index/w /tmp/X$$A" $temp1 > /dev/null
while test `wc -l /tmp/X$$A | awk '{print $1}'` -gt 0
do
        sed -e "/create.*index/N" \
            -e "s/\n/ /" \
            -e "/create.*index.*;$/w $temp2" \
            -e "/create.*index.*;$/d" $temp1 > $schema_temp
        cat $temp2 >> $index_output
        rm  $temp2
        cat $schema_temp > $temp1
        sed -e "/create.*index/w /tmp/X$$A" $temp1 > /dev/null
done

cat $temp1 > $schema_temp
rm $temp1
rm /tmp/X$$A





## now find "alter table add constraint" statements and clean those out
## see the note following this logic block regarding schemas without constraints

echo ""
echo "Parsing out the add constraint statements"

sed -e "/alter.table.*add.constraint.*;$/w $index_temp" \
    -e "/alter.table.*add.constraint.*;$/d" $schema_temp > $temp1

sed -e "/alter.table.*add.constraint/w /tmp/X$$A" $temp1 > /dev/null
while test `wc -l /tmp/X$$A | awk '{print $1}'` -gt 0 
do
        sed -e "/alter.table.*add.constraint/N" \
            -e "s/\n/ /" \
            -e "/alter.table.*add.constraint.*;$/w $temp2" \
            -e "/alter.table.*add.constraint.*;$/d" $temp1 > $schema_output
        cat $temp2 >> $index_output
        rm  $temp2
        cat $schema_output > $temp1
        sed -e "/alter.table.*add.constraint/w /tmp/X$$A" $temp1 > /dev/null
done

# if a schema does not contain any constraints, then the script will exit
# and not leave a schema output file.  This clause checks to see if the
# temporary schema file exists.  If it does, then there were constraints and
# the rest of the constraint statements need to be added to the index output
# file.  if the file does not exist, then copy the fully stripped schema
# temporary file to the schema output file

if [[ ! -a $schema_output ]]; then
   cat $schema_temp > $schema_output
else
   cat $index_temp >> $index_output
fi

rm $index_temp
rm $schema_temp
rm $temp1
rm /tmp/X$$A

echo ""
echo "Done!"
Listing of errors
Main book page