Subject: Example Code: Interactive SELECT using sh & awk
From: Daniel Martin <[email-masked]>
Date: Thu, 13 Jun 1996 19:20:11 GMT
Newsgroups: comp.databases.informix

Hoping the below will be one or more of:
A) Entertaining
B) Inspirational
C) Useful

Also hoping not to humiliate myself    :^)

: "select.sh by Dan Martin (DRM/CTG) Mon Jun 10 07:52:39 CDT 1996"
# The Almost-Ultimate Data-Selector squipt.
# Written & tested on SCO UNIX 3.2v4.2 - your awk and /bin/sh may vary
# Temp Files
f1=/tmp/f1x$$            # List of Tables and their Columns
f2=/tmp/f2x$$            # List of Tables only
f3=/tmp/f3x$$            # Scratch File
f4=/tmp/f4x$$            # Scratch File
f5=/tmp/f5x$$            # File of User-Selected columns
sql=/tmp/sql$$           # SQL SELECT Command (as written by awk)
yak=/tmp/yak$$           # Users info to be mailed to him/her/alt
out="/tmp/`logname`.out" # Will contain Users SELECTed data
>$out
chmod 666 $out
eraser() {
rm -f $f1 $f2 $f3 $f4 $f5 $yak $sql
} # EOF eraser()
phoo() { # Required Argument: $1 = A User-Abuse blabbery
echo "\n  $RN $1 $RO"
echo "\n  This Program (\"$0\") has been Terminated.\n"
eraser
exit 0
} # EOF phoo()
trap 'echo "\n  PROGRAM \"$0\" CANCELLED!!\n"; eraser; sleep 2; exit 1' 2
clear
# /u/DB/DAT_otr/data_otr.dbs
# /u2/DB/DAT_pms/inpo_pms.dbs
cat << PAW

  =======================< INFORMIX DATA-SELECTOR >=======================

  The following Informix Databases are available:

PAW
##################################################################
## Substitute all of your own *.dbs that you want to be listed: ##
##################################################################
cat << BOX | awk '{printf("%d^%s^\n", NR, $0)}' >$f1
/u/DB/DAT_bea/inpo_bea.dbs
/u2/DB/DAT_dod/inpo_dod.dbs
/u2/DB/DAT_eni/inpo_eni.dbs
/u/DB/DAT_ftd/inpo_ftd.dbs
/u2/DB/DAT_law/inpo_law.dbs
/u/DB/DAT_pol/inpo_pol.dbs
/u2/DB/DAT_ter/inpo_ter.dbs
/u/spims2/ifxdir/to_do.dbs
BOX
awk -F"^" '{printf(" %2d %27s\n", $1, $2)}' $f1
# . /bin_drm/coddle  # See internal Documentation
# Note: The above contains the getit() function
#       which is used profusely herein.
#       Immediately below is a simplified getit()
#       which will suffice in place of the original:
getit() { # Required argument: A User-Instruction blabbery
ans=''
while [ -z "$ans" ]
do
  echo "$1 \c"
  read ans
done
ans=`echo "$ans" | tr "[a-z]" "[A-Z]"`  # force UPPER CASE
} # EOF getit()
mxx="`awk 'END {print NR}' $f1`"
echo
getit "  Choose a Database NUMBER (1 thru $mxx):" PNO 3
# Did the [ab]User pick a valid #?
>$f3
awk -F"^" "\$1 == \"$ans\" {print \$2}" $f1 >$f3
[ ! -s "$f3" ] && phoo "  Database Number \"$ans\" does not exist!  "
# cat $f3 # debuggerie
IDB=`awk '{len = length($0); printf("%s\n", substr($0,1,(len - 4)))}' $f3`
# echo "\nIDB = \"$IDB\"."  # debugerrie
# eraser; exit 0            # debuggerie
echo '\n  Now Reading Database Structure (takes about two minutes)...\c'
# dbaccess    $IDB - << STOP                     # she only whimpers
# dbaccess -e $IDB - << STOP 2>&1 | tee /tmp/drm # she's a screamer
  dbaccess    $IDB - << STOP 2>/dev/null         # she's real quiet
    CREATE TEMP TABLE colkind (type smallint, column_type char(13));
    INSERT INTO colkind (type, column_type) VALUES (0,   "character");
    INSERT INTO colkind (type, column_type) VALUES (256, "char nonull");
    INSERT INTO colkind (type, column_type) VALUES (1,   "small integer");
    INSERT INTO colkind (type, column_type) VALUES (257, "sm_int nonull");
    INSERT INTO colkind (type, column_type) VALUES (2,   "integer");
    INSERT INTO colkind (type, column_type) VALUES (258, "int nonull");
    INSERT INTO colkind (type, column_type) VALUES (3,   "float");
    INSERT INTO colkind (type, column_type) VALUES (259, "float nonull");
    INSERT INTO colkind (type, column_type) VALUES (4,   "small float");
    INSERT INTO colkind (type, column_type) VALUES (260, "sm_flt nonull");
    INSERT INTO colkind (type, column_type) VALUES (5,   "decimal");
    INSERT INTO colkind (type, column_type) VALUES (261, "dec nonull");
    INSERT INTO colkind (type, column_type) VALUES (262, "serial");
    INSERT INTO colkind (type, column_type) VALUES (7,   "date");
    INSERT INTO colkind (type, column_type) VALUES (263, "date nonull");
    INSERT INTO colkind (type, column_type) VALUES (8,   "money");
    INSERT INTO colkind (type, column_type) VALUES (264, "money nonull");
    INSERT INTO colkind (type, column_type) VALUES (10,  "datetime");
    INSERT INTO colkind (type, column_type) VALUES (266, "dttim nonull");
    INSERT INTO colkind (type, column_type) VALUES (11,  "byte");
    INSERT INTO colkind (type, column_type) VALUES (267, "byte nonull");
    INSERT INTO colkind (type, column_type) VALUES (12,  "text");
    INSERT INTO colkind (type, column_type) VALUES (268, "text nonull");
    INSERT INTO colkind (type, column_type) VALUES (13,  "varchar");
    INSERT INTO colkind (type, column_type) VALUES (269, "varch nonull");
    INSERT INTO colkind (type, column_type) VALUES (14,  "interval");
    INSERT INTO colkind (type, column_type) VALUES (270, "intvl nonull");
         UPDATE STATISTICS;
      UNLOAD TO "$f1" DELIMITER "^"
         SELECT syscolumns.tabid, tabname, systables.tabtype,
                colno, colname, column_type, collength, systables.nrows
           FROM systables, syscolumns, colkind
          WHERE systables.tabid   >= 100              AND  {no system tables}
                systables.tabtype  = "T"              AND  {no Views}
                systables.nrows    > 0                AND  {no empty Tables}
                systables.tabid    = syscolumns.tabid AND
                syscolumns.coltype = colkind.type
       ORDER BY syscolumns.tabid, colno ;
     DROP TABLE colkind ;
      UNLOAD TO "$f2" DELIMITER "^"
         SELECT tabid, tabname
           FROM systables
          WHERE tabid   >= 100     AND  {no system tables}
                tabtype  = "T"     AND  {no Views}
                nrows    >  0           {no empty Tables}
       ORDER BY tabid ;
STOP
clear
cat << FUR

  =======================< INFORMIX DATA-SELECTOR >=======================

  Database Selected: "$IDB"   Files available:

FUR
awk -F"^" '{printf("%d %s\n", $1, $2)}' "$f2" | pr -4to2 -w77
echo
getit "  Choose a File NUMBER:" PNO 3
# Did the [ab]User pick a valid #?
>$f3
awk -F"^" "\$1 == \"$ans\" {print \$2; exit}" $f1 >$f3
[ ! -s "$f3" ] && phoo "  File Number \"$ans\" does not exist!  "
TAB1=`head -1 $f3`
awk -F"^" "\$1 == \"$ans\" {print \$8; exit}" $f1 >$f3
nrec=`head -1 $f3`
# echo "GAWWKK_K_K-k-k-k...\c"; read lksjfg # debuggerie
clear
cat << FUR

  =======================< INFORMIX DATA-SELECTOR >=======================

  Database="$IDB" File="$TAB1" Records="$nrec" Fields:

FUR
# awk fields in $f1:
# $1,    $2,      $3,      $4,    $5,      $6,          $7         $8
# tabid, tabname, tabtype, colno, colname, column_type, collength, nrows
awk -F"^" '{if ($1 == tn) printf("%s\n",$5)} END {printf("ROWID\n")}' \
    tn="$ans" $f1 | tee $f3 | pr -4to2 -w77
echo
getit '  Select <A>ll Fields above, or just <S>ome? (A/S):' ESL 3 1
echo
case "$ans" in
  A|S) : ;;
  *) phoo "  Option \"$ans\" Not Offered!" ;;
esac
cols="$ans"
[ "$cols" = "A" ] && cat $f3 >$f5 || {
icnt=`awk 'END {print NR}' $f3`    # Number of Columns
sl=1
while [ "$sl" -le "$icnt" ]
do
  cat $f3 | sed -n ${sl}p >$f4
  awk -F"^" '{printf("  Select column %18s ? (Y/N): ", $1)}' $f4
  read yorn
  yorn=`echo "$yorn" | tr "[a-z]" "[A-Z]"`  # force UPPER CASE
  [ "$yorn" = "Y" ]   && cat $f4 >>$f5
  sl=`expr $sl + 1`
done
} # EOT [ "$cols" = "A" ]
clear
cat << FUR | tee $yak

  =======================< INFORMIX DATA-SELECTOR >=======================

  Database="$IDB" File="$TAB1" Records="$nrec" Fields:

FUR
awk '{printf("%d %s\n", NR, $1)}' $f5 | tee $f4 | pr -4to2 -w77 | tee -a $yak
npg=`awk 'END {printf("%d\n", (rex/60))}' rex="$nrec" $f5`
cat << EYE

  File "$TAB1" contains $nrec Records.  Unless a search-for value is
  used to seek out specific data within one of its Fields, the output
  will include every Record in the File (approx. $npg Pages).

  Enter a Field Number within which you want to seek specific data, or
  enter 0 (zero) if you want the information for all $nrec Records.

EYE
getit '  Field NUMBER or 0 (zero):' GEZ 3
col="$ans"
if [ "$col" -gt "0" ]
then
  # did the [ab]User enter a valid Column Number?
  >$f3
  awk '{if (NR == loc) print $0}' loc="$col" $f5 >$f3
  [ ! -s "$f3" ] && phoo "  Field Number \"$col\" is not available!  "
  fld=`awk '{if ($1 == loc) {printf("%s\n", $2); exit}}' loc="$col" $f4`
  echo
  box=''
  while [ -z "$box" ]
  do
    echo "  Enter search-value for Field \"$fld\": \c" ANY 3
    read box
  done
  # Did the [ab]User enter Text or Numeric $box for for column $fld?
  t=`expr "$box" + 1 2>&1 | awk '{print $1}'`
  echo
  if [ "$t" = "expr:" ]
  then
    getit "  Should \"$box\" be shifted into UPPER case? (Y/N):" ESL 3 1
    [ "$ans" = "Y" ] && box=`echo "$box" | tr "[a-z]" "[A-Z]"`
    # Did [ab]User enter any "*"'s ?
    o=`echo "$box" | awk '{duh = index($0,"*")} END {print duh}'`
    if [ "$o" = "0" ]
    then
      WH="WHERE $fld = \"$box\""
    else
      WH="WHERE $fld MATCHES \"$box\""
    fi
  else
    WH="WHERE $fld = $box"
  fi
else
  WH=''
fi
tput clear
cat $yak
[ "$col" -gt "0" ] && echo "\n  NOTE: Limit Output to data $WH "
getit '\n  Enter Field NUMBER on which to Sort: ' GEZ 3
scol="$ans"
# did the [ab]User enter a valid Column Number?
>$f3
awk '{if (NR == loc) print $0}' loc="$scol" $f5 >$f3
[ ! -s "$f3" ] && phoo "  Field Number \"$scol\" is not available!  "
sfld=`awk '{if ($1 == loc) {printf("%s\n", $2); exit}}' loc="$scol" $f4`
OBY="ORDER BY $sfld ; "
clear
cat $yak
cat << EYE

  ALERT: Your Data will be Extracted and placed into file "$out".
         As to file-content, you may choose either:

         1  Non-delimited, fixed-length Records
            (the first two lines of this file in will be blank).
         2  Field-delimited, variable-length Records
            (The delimiter used will be the caret character: ^).

EYE
getit '  Which type of content? (1/2):' PNO 3
case "$ans" in
  1) PRE="OUTPUT TO \"$out\" WITHOUT HEADINGS "
     FIN="Non-delimited, fixed-length Records."
     ;;
  2) PRE="UNLOAD TO \"$out\" DELIMITER \"^\" "
     FIN='Field-delimited (with ^), variable-length Records.'
     ;;
  *) phoo "Sorry, but the choice of \"$ans\" was not offered!" ;;
esac
# Now, Generate the SQL statement:
awk '
BEGIN \
  {up = 0  # How many fields are "up" on the current line
   cs = 0  # Comma Switch (0 = no comma, 1 = comma OK)
   printf("  %s\n", pre)
   printf("  SELECT ")
  }
# main()
  {if (cs == 1) printf(", ")
   printf("%s", $1)
   ++up
   cs = 1
   if (up == 4) {printf(",\n         "); up = 0; cs = 0}
  }
END \
  {printf("\n")
   printf("    FROM %s\n", tb)
   if (wh > " ") printf("   %s\n", wh)
   printf("   %s\n", ob)
  }' tb="$TAB1" wh="$WH" ob="$OBY" pre="$PRE" $f5 >$sql
clear
cat $yak
echo "\n  The SQL COMMAND TO BE USED FOR DATA EXTRACTION IS:\n"
cat $sql
echo " " | tee -a $yak
getit '  Begin data-extraction process using the Query above? (Y/N):' ESL 3 1
[ "$ans" != "Y" ] && phoo 'By Your Command:'
# GET THE DATA (finally):
dbaccess -e $IDB - < $sql 2>&1 | tee -a "$yak" # she's a screamer
# cat $sql >>$yak
echo "  File \"$out\" has $FIN" >>$yak
echo "\n  END REPORT (ID: \"$0\") AS OF: `date`\n" >>$yak
# SHELLS/help_me2.sh $yak
pg $yak
eraser
# EOF select.sh