#!/bin/ksh
# procit.ksh by Dan Martin (DRM/CTG) 12/08/99
# ALERT: Output filenames will begin with the initial portion, up to the
#        first underscore character ("_"), of the input file name ($1).
#        Therefore, $1 should contain at least one _ character in its name.
#        Final Line of Input file ($1) MUST (!!) end with the string "-- EOF"

# B U G: Has problem when any comments or concatenated ("||") strings within
#        the input file contain one or more semicolon characters (";"): in
#        such cases a syntax error(s) will be introduced into those
#        specific procedure file(s) generated (sorry).  The bug IS fixable
#        but the time needed to do so is not deemed "worth it" for now.

  export duhba="ON" # If "ON", all procs will be made to run as DBA
# export duhba="OFF"
  export infmx="ON" # If "ON", all procs will be made owned by "informix".
# export infmx="OFF"

export mndir=/fcicrd/drm     # Anchor directory
export lgdir="${mndir}/LOGS" # Dir under which the Log Files are kept

_usage()
{
cat << BOX

  USAGE: procit.ksh input

  WHERE: "input" is any file containing ALL the Stored Procedures
         for a database such as output by the schemame.sh script.

ACTIONS: The input file remains unmolested, but the output will be a
         file for each individual procedure.  This helps in debuggery
         and also facilitates easier future mods on a proc-by-proc basis.

         Beautification is performed for the output files such as replacing
         tabs with a single space, upshifting certain words and phrases,
         deleting extra spaces, and making sure that any run-on lines are
         eventually terminated at the next ; character.

 CAVEAT: If any comments or concatenate ("||") strings contain a semicolon
         character (";"), then a syntax error will be introduced into
         that specific procedure file as generated (sorry).

BOX
[[ "$duhba" = "ON" ]] && echo "\tALERT: All Procedures will be made DBA!\n"
[[ "$infmx" = "ON" ]] && echo "\tAll Procs will be owned by \"informix\".\n"
exit 1
} # EOF _usage()

_dodate()
{
 date +%Y/%m/%d-%H:%M:%S
} # EOF _dodate

[[ -d "$mndir" ]] || exit 0
[[ -d "$lgdir" ]] || mkdir $lgdir

# Set up new Log file:
export iam=`basename $0`
export lgfi="${lgdir}/${iam}.log" # These MUST be manually cleaned up
>${lgfi}
chmod 666 ${lgfi}

_yak()
{ # Required Arguments: $1 = scriptname        # DRM 06/02/99
  #                     $2 = logfilename
  #                     $3 = blabbery text
  # e.g.,               _yak "$0" "$lgfi" "This is a blabbery"
  ts=`_dodate`
  dwp=`pwd`
  cat << BOX >>$2

    Time: "$ts"   Logfile: "$lgfi"
  Script: "$1" (Dir: "$dwp")
   Event: $3
BOX
} # EOF _yak()

_dang()
{ # Required Arguments: $1 = scriptname
  #                     $2 = logfilename
  #                     $3 = bail-out blabbery
  # e.g.,               _dang "$0" "$lgfi" "File not found."
  _yak "$1" "$2" "$3"
  echo "\nBAILING OUT!\n" | tee -a ${lgfi}
  echo "See logfile: \"${lgfi}\"!\n"
  echo "Environment at time of Bail-Out:\n" >>${lgfi}
  env|sort >>${lgfi}
  echo "\n# EOF ${lgfi} log" >>${lgfi}
  [ ! -s "$lgfi" ] && rm -f ${lgfi}
  exit 1
} # EOF _dang()

# Make sure All is Well
[[ -z "$1" ]] && _usage || infi="$1"
[[ -f "$infi" ]] || _dang "$0" "$lgfi" "Input File \"$infi\" is MISSING!"
[[ -s "$infi" ]] || _dang "$0" "$lgfi" "Input File \"$infi\" is EMPTY!"
export oufi="/tmp/proc$$"  # scratch file - be sure to clean up
export oudi="./all_proc/" # output directory - MUST end in /
[[ -d "$oudi" ]] || mkdir $oudi; chmod 777 $oudi

if [[ "$duhba" = "ON" ]]
then
  echo "\t\nALERT: All Procedures will be made DBA!\n" | tee -a "$lgfi"
  export dvar="DBA"
else
  export dvar=''
fi
if [[ "$infmx" = "ON" ]]
then
  echo "\tAll Procedures will be owned by \"informix\".\n" | tee -a "$lgfi"
  export ivar='"informix".'
else
  export ivar=''
fi
cat << BOX

 WARNING: If any comments or concatenate ("||") strings within file
          "$infi" contain one or more semicolon
          characters (";"), then a syntax error(s) will be introduced
          into those specific procedure file(s) generated (sorry).

   ALERT: EXECUTE privilege on ALL Procedures will be GRANTed to PUBLIC

          Sleeping 10 seconds to allow manual abort (Ctrl-C) if desired

BOX
sleep 10
_yak "$0" "$lgfi" "Initial processing of input file \"$infi\" begins."
echo "\nInitial processing of input file \"$infi\" begins...\c"
# Clean up run-on lines in $1 with awk and beautify with sed:
nawk '
BEGIN \
{ RS = ";"  # controls run-on line length
}
# main()
{idx = index($0,"-- EOF")
 if (idx > 0) exit    # quenches extra ending ; in output
 printf("%s;\n", $0)  # Every physical line of output will now end with ;
}' $infi |
sed "s/create procedure /CREATE $dvar PROCEDURE $ivar/g
     s/Create Procedure /CREATE $dvar PROCEDURE $ivar/g
     s/Create  Procedure /CREATE $dvar PROCEDURE $ivar/g
     s/create  procedure /CREATE $dvar PROCEDURE $ivar/g
     s/CREATE  PROCEDURE /CREATE $dvar PROCEDURE $ivar/g
     s/CREATE PROCEDURE /CREATE $dvar PROCEDURE $ivar/g
     s/create dba procedure /CREATE DBA PROCEDURE $ivar/g
     s/create DBA procedure /CREATE DBA PROCEDURE $ivar/g
     s/end procedure/END PROCEDURE/g
     s/End Procedure/END PROCEDURE/g
     s/end  procedure/END PROCEDURE/g
     s/End  Procedure/END PROCEDURE/g
     s/document/DOCUMENT/g
     s/Document/DOCUMENT/g
     s/insert into /INSERT INTO /g
     s/Insert Into /INSERT INTO /g
     s/insert  into /INSERT INTO /g
     s/Insert  Into /INSERT INTO /g
     s/delete from /DELETE FROM /g
     s/Delete From /DELETE FROM /g
     s/delete  from /DELETE FROM /g
     s/Delete  From /DELETE FROM /g
     s/update /UPDATE /g
     s/Update /UPDATE /g
     s/system /SYSTEM /g
     s/foreach/FOREACH/g
     s/Foreach/FOREACH/g
     s/drop /DROP /g
     s/Drop /DROP /g
     s/values /VALUES /g
     s/Values /VALUES /g
     s/trace /TRACE /g
     s/Trace /TRACE /g
     s/debug /DEBUG /g
     s/Debug /DEBUG /g
     s/ temp / TEMP /g
     s/ Temp / TEMP /g
     s/                / /g
     s/               / /g
     s/              / /g
     s/             / /g
     s/            / /g
     s/           / /g
     s/          / /g
     s/         / /g
     s/        / /g
     s/       / /g
     s/      / /g
     s/     / /g
     s/    / /g
     s/   / /g
     s/  / /g
     s/  / /g
     s/	/ /g
     s///g
 " >$oufi
# last two sed commands above seek, respectively, Ctrl-I and Ctrl-M
if [[ -s "$oufi" ]]
then
  _yak "$0" "$lgfi" "Primary processing OK in \"$oufi\". Launching secondary."
  echo "\n\nPrimary processing OK in \"$oufi\". Launching secondary."
else
  _dang "$0" "$lgfi" "Output file \"$oufi\" is EMPTY!"
fi
echo "\nSee logfile: \"$lgfi\".\n"
# exit 0 # debuggerie
# parse $oufi into discrete files
cat $oufi |
nawk -v odr="$oudi" -v lfi="$lgfi" -v ifi=`basename $infi` \
     -v pfi="$oufi" -v xfi="$infmx" '
BEGIN \
{# odr = # output directory    (passed in from shell)
 # lfi = # pathname of logfile (passed in from shell)
 # ifi = # name of input file  (e.g., afs_proc.sql)
 pfc =  0  # Procedure File Counter
 jn1 =  0  # Junk Number One
 jn2 =  0  # Junk Number Two
 jn3 =  0  # Junk Number Three
 jn4 =  0  # Junk Number Four
 js1 = "x" # Junk String One
 js2 = "x" # Junk String two
 # Figure out common name string:
 split(ifi, com, "_")
 bfn = com[1] # Beginning File Name string
 spn = "x"    # Stored Procedure Name
 ofn = "x"    # Output File Name
 js2 = sprintf("\n\t  Beginning awk-processing of file \"%s\".",pfi)
 blab(js2)    # blab() appends a newline character to the js2 string
 js2 = sprintf("\t  File names will all begin with \"%s\".",bfn)
 blab(js2)
 js2 = sprintf("\t  Output files will all be placed under \"%s\".",odr)
 blab(js2)
 s1  =  0  # State-of-search counter
}
# main()
{if (s1 == 0) # start sniffing around...
    {jn1 = index($0, "CREATE ")
     jn2 = index($0, "PROCEDURE ") # MUST include ending space
     if   (jn1 > 0 && jn2 > 0) s1  = 2
     else                      {jn1 = jn2 = 0; js1 = "x"}
     if   (s1 == 2)  # Liftoff: Create name and send output into new file
          {++pfc     # We have a Procedure
           jn3 = index($0, "(")
           if (substr($0, (jn3 -1), 1) == " ") --jn3
           jn4 = (jn3 - (jn2 + 10))          # If you can figure this out
           js1 = substr($0, (jn2 + 10), jn4) # please tell me how it works
           g = split(js1, ak, ".")           # otherwise,
           spn = ak[g]                       # do not worry, be happy
           ofn = sprintf("%s%s_%s.sql", odr, bfn, spn)
           js2 = sprintf("\t%3d New Filename = \"%s\"!.", pfc, ofn)
           blab(js2)
           printf("-- File: %s_%s.sql  Procedure: %s\n\n", bfn, spn, spn) >ofn
           printf("-- DROP PROCEDURE %s ;\n\n", spn)           >>ofn
           s1 = 3  # we are now ready to write the body of the file
          }
    }
 if (s1 >= 3) printf("%s\n", $0) >>ofn
 if (index ($0, "END PROCEDURE") > 0)  s1 = 4
 if (s1 == 4 && index($0, ";")   > 0)
    {s1 = 0
     if   (xfi == "ON")
           printf("\nGRANT EXECUTE ON %s TO public AS informix ;\n", spn) >>ofn
     else
           printf("\nGRANT EXECUTE ON %s TO public ;\n", spn)        >>ofn
     printf("\n-- EOF: %s_%s.sql  Procedure: %s\n\n", bfn, spn, spn) >>ofn
     close(ofn)
    }
}
END \
{js2 = sprintf("\n\t  END of awk processing!")
 blab(js2)
}
function blab(yak)
{printf("%s\n", yak)  >"/dev/tty"   # Display yak on screen
 printf("%s\n", yak) >>lfi          # Append  yak to log file
}'
rm -f $oufi   # uncomment after debuggerie
echo "\nNormal termination. See logfile: \"$lgfi\".\n"
_dang "$0" "$lgfi" "Normal Program Termination.  All is OK!"
# EOF procit.ksh