# This is a shell archive.  Remove anything before this line,
# then unpack it by saving it in a file and typing "sh file".
#
# Wrapped by julian on 20:03:24 ;  21 Декабря 1999, Вторник 
# Contents:  dbpipe.txt dbpipe.sh dbptst.sh
 
echo x - dbpipe.txt
sed 's/^@//' > "dbpipe.txt" <<'@//E*O*F dbpipe.txt//'
dbpipe.sh      v1.2
=========

20.12.99
Julian Zalutsky
julian@cis.belpak.brest.by
Brest, Belarus.


This script allows to execute SQL commands through a single dbaccess from
several ksh scripts or background jobs at the same time.
ESQL/C isn't needed, awk only.

It can be useful during migration from old database (Clipper for example) to
Informix database when real-time answer from Informix database to Clipper
tasks isn't needed.

We have realized a simple "replication of two different databases" through a
self-made "middleware" with following scheme:
   Old Clipper tasks writes files with SQL commands -> ksh scripts scans
   NetWare directories and executes SQL requests (SPs, insert/update/delete).
Backwards:
   Informix database triggers writes a lot of inserted/deleted.field values
   from any tables to one work table -> ksh script checks this table and
   changes a Clipper database.
To prevent a recursion (Clipper db -> Informix db -> Clipper db) global
variable in stored procedure can be used.


Example:

@. dbpipe.sh LOAD workdir                  # load "channel"
@. dbpipe.sh OPEN workdir                  # open first "thread"
if dbpSQL "database ware;"; then          # connect to database

   (if . dbpipe.sh OPEN workdir; then     # open second thread
      while read cmd; do
         if ! dbpSQL "$cmd" ferr1; then   # execute SQL commands in 2nd thread
            echo "Error in SQL command from file file1:"
            cat ferr1
         fi
      done <file1 >>logerr
      . dbpipe.sh CLOSE workdir           # close second thread
   fi)&

   (if . dbpipe.sh OPEN workdir; then     # open third thread
      while read cmd; do
         if ! dbpSQL "$cmd" ferr2; then   # execute SQL commands
            echo "Error in SQL command from file file2:"
            cat ferr2
         fi
      done <file2 >>logerr
      . dbpipe.sh CLOSE workdir           # close thrid thread
   fi)&

   if [ $(jobs -p |wc -l) -eq 2 ]; then

      # execute SQL commands in 1st thread
      if ! dbpSQL "insert into logtable values($LOGNAME,CURRENT);" ferr3; then
         mail -s "Error with logging" administrator <ferr3
      fi
      . . .
   . dbpipe.sh CLOSE workdir              # close first thread
   . dbpipe.sh UNLOAD                     # unload channel



Notes:

1. RESTRICTION: all threads works with a same database opened by first thread.
2. Channel is a set of fore daemons (dbaccess, awk1, awk2, awk3) and 3 pipes.
   Thread is a set of two personal pipes and dbpSQL() function.
3. Following lines removed from dbaccess stdout:
       Error in line 1
       Near character position N
4. It isn't fact that stdout is ready after dbpSQL(), sometimes small waiting
   is needed in main script. Reason:
   End of SQL command determines by dbaccess stderr (I don't know other
   method). UNIX works with /dev/stdout и /dev/stderr asynchronously.
   sleep or control SQL command are bad solutions.
   I don't need stdout usually because we perform EXECUTE PROCEDURE,
   UPDATE and so on, but not SELECT. For example:
       if ! dbpSQL N "<SQL>;" ferr; then
          <error message>
   If stdout is needed for you then:
       dbpSQL N "<SQL>;" ferr fout
       if [ $? -ne 0 ]; then
          <error message>
       else
          <any action(s): variable setting, count increment or other>
          mv fout my_file
5. With threads for different users you must replace mode in two mkfifo
   commands into dbpipe.sh.



dbptst.sh
=========

Three tests generates work files a1,a2;
works up to 15-30 seconds.


==========
That's all.
@//E*O*F dbpipe.txt//
chmod u=rwx,g=rwx,o=rx dbpipe.txt
 
echo x - dbpipe.sh
sed 's/^@//' > "dbpipe.sh" <<'@//E*O*F dbpipe.sh//'
#!/bin/ksh
# Channel to Informix OnLine DS through one dbaccess for use in several
# ksh scripts at the same time.
#
#  17.12.99
#  Julian Zalutsky
#  julian@cis.belpak.brest.by
#
# Loading:
#     . dbpipe.sh LOAD workdir
#        workdir  -channel work directory
#
# Open channel thread:
#     . dbpipe.sh OPEN workdir
#
# Execute SQL command:
#     dbpSQL "single_SQL_command;" [ferr [fout]]
#        ferr     -dbaccess stderr log file
#        fout     -dbaccess stdout log file
#
# Close thread:
#     . dbpipe.sh CLOSE workdir
#
# Unloading:
#     . dbpipe.sh UNLOAD workdir
#
# Exit status $?:
#  0  -OK
#  1  -runtime error or SQL error from dbpSQL()
# After dbpSQL() the SQLcmd variable contains a "single_SQL_command;", you can
#  write it to a log file.
#
# Before dbpSQL() you can set the trap_handler variable for trap you script, for
# example:
#  trap_handler="
#    trap 'was_trap=yes; exit_function' 15
#    trap 'was_trap=yes; external_statistics_function SQLcount SQLerrcount' 1"


dbin=$2/dbin.dbp
dbout=$2/dbout.dbp
dberr=$2/dberr.dbp

case $1
in

LOAD)

   rm $2/*.dbp 2>/dev/null
   if mkfifo -m 660 $dbin $dbout $dberr; then

      # awk1 +dbaccess. Execute commands from $dbin (dbpSQL), send log file
      # names to awk2 and awk3, wait of SQL exit code, send it to dbpSQL
      # Input strings:
      # UNLOAD    -exit
      # <fout> <ferr> <pipe1> <pipe2> <SQL> -switcs the awk2,3 log files to
      #        <fout>,<ferr>; send <SQL> to dbaccess;
      #        receive SQL status code from <pipe1>;
      #        send status code to dbpSQL() through <pipe2>.
      # <pipe1>   -response from awk3: switch OK
      # <N>       -response from awk3: SQL exit status 0|1
      # Note:
      # [ sorry, "while ( ! getline pline <pfile ) {}" don't work with some awk
      #   where getline() hasn't exit status code ]
      awk -v finput=$dbin -v dbout=$dbout '
         function Next_line ( pfile, close_flag ) {
            $0 = ""
            while ( $0 == "" ) { getline <pfile }
            if ( close_flag ) close( pfile )
         }
         END {
            while ( 1 ) {
               Next_line( finput, 0 )        # take next parameters
               gsub( "\r", "" )
               if ( $1 == "UNLOAD" ) break
               print "_nEw_",$1,$2,$3 >dbout # send to awk2,3: switch output
               close(dbout)
               pipe1 = $3; pipe2 = $4; SQL = ""
               if ( $NF !~ /.*;$/ ) $NF = $NF ";"
               for ( i=5; i<=NF; i++ ) { SQL = SQL $i " " }
               Next_line( pipe1, 1 )         # take response from awk3
               print SQL                     # send SQL command to dbaccess
               Next_line( pipe1, 1 )         # take SQL status code from awk3
               print $0 >pipe2               # give SQL status code to dbpSQL()
               close(pip2)
            }
         }' /dev/null | dbaccess - - >$dbout 2>$dberr&


      # awk2. dbaccess stdout logging, transfer log file name from awk1 to awk3
      # Input strings:
      # _nEw_ <fout> <ferr> <pipe>  -start log to <fout>;
      #                              resend <ferr> and <pipe> to awk3
      # other     -log it if not empty
      awk -v dberr=$dberr 'BEGIN { fout = "/dev/null" }
         $0 !~ /(^$)|(^Error in line)|(^Near character position)/ {
            if ( $1 == "_nEw_" ) {
               close (fout); fout = $2; print $1,$3,$4 >dberr; close(dberr) }
            else print >>fout
         }' $dbout&


      # awk3. dbaccess stderr logging, reply to awk1
      # Input strings:
      # _nEw_ <ferr> <pipe>   -start log to <ferr>, reply to <pipe>
      # <N>: <not ISAM> ...   -SQL error code N.
      # second_empty -SQL error code = 0
      # other        -log it if not empty
      awk 'BEGIN { ferr=pipe="/dev/null"; empty_count=0 }
         function SQLexited(code) {
            print code >>pipe; close(pipe); empty_count=0 }
         {  if ( NF > 0 ) {
               if ( $1 == "_nEw_" ) {
                  close (ferr); ferr = $2; pipe = $3
                  print SQL_id, ferr >pipe; close(pipe)
               }
               else {
                  print >ferr
                  if ( $1 ~ /[0-9]+:/ ) if ( $2 != "ISAM" ) SQLexited( 1 )
                                        else empty_count = 0
               }
            }
            else if ( ++empty_count > 1 ) SQLexited( 0 )
         }' $dberr&
      pidawk3=$!

   else
      (exit 1)
   fi;;

OPEN)
   if [ -p $dbin ]; then

      # create 2 pipes for thread
      # $Thread_start   -for awk1,3 interchange
      # $Thread_stop    -for receive of SQL exit code from awk1 by dbpSQL()
      Thread_start=$2/$RANDOM
      while [ -e $Thread_start* ]; do
         Thread_start=$2/$RANDOM
      done
      Thread_stop=${Thread_start}stop.dbp
      Thread_start=${Thread_start}start.dbp
      if ! mkfifo -m 660 $Thread_start $Thread_stop; then
         (exit 1)
      fi

      # Function. Send commands to awk1 and wait response
      # $1     -SQL command
      # [$2]   -dbaccess stderr log file
      # [$3]   -dbaccess stdout log file
      # After execution the SQLcmd variable contains a "single_SQL_command;"
      dbpSQL() {
         eval "$trap_handler"
         while ! print -- ${3:-/dev/null} ${2:-/dev/null} \
                     $Thread_start $Thread_stop $1 >$dbin; do
            echo "dbpSQL() warning: awk1 isn't ready, retrying..." >&2
            sleep 1
         done
         while ! read return_code <$Thread_stop 2>/dev/null ||
            [ -n "$was_trap" ]; do
            was_trap=      # dbaccess is not ready or trapped, repeat...
         done 2>/dev/null
         SQLcmd="$1"
         return $return_code
      }

   else
      (exit 1)
   fi;;

CLOSE)
   if rm $Thread_start $Thread_stop; then
      unset Thread_start Thread_stop
      unset -f dbpSQL
      unset dbin dbout dberr
   else
      (exit 1)
   fi;;

UNLOAD)
   echo $1 >$dbin
   kill -9 $pidawk3 2>/dev/null
   unset dbin dbout dberr pidawk3
   sleep 1        # else files $Thread* will appears
   rm $2/*.dbp
   ;;

*)
   echo "dbipie.sh: wrong parameter"
   exit 1;;

esac
@//E*O*F dbpipe.sh//
chmod u=rwx,g=rwx,o=rx dbpipe.sh
 
echo x - dbptst.sh
sed 's/^@//' > "dbptst.sh" <<'@//E*O*F dbptst.sh//'
#!/bin/ksh
database=${2:-stores}

rm a1 a2 2>/dev/null

case $1
in

1)
   . dbpipe.sh LOAD .
   echo LOAD channel, result: $?
   . dbpipe.sh OPEN .
   echo OPEN thread, result: $?
   dbpSQL "DATABASE $database;"
   echo "dbpSQL \"DATABASE $database;\", result: $?"
   if ! dbpSQL "SELECT field FROM Non_existent;" a2; then
      echo "dbpSQL \"SELECT field FROM Non_existent;\", result: not 0"
      cat a2
      rm a2
   fi
   dbpSQL "SELECT tabname FROM Systables
      WHERE tabid=1;"
   echo "dbpSQL \"SELECT tabname FROM Systables WHERE tabid=1;\", result: $?"
   . dbpipe.sh CLOSE .
   echo CLOSE thread, result: $?
   . dbpipe.sh UNLOAD .
   echo UNLOAD channel, result: $?
   ;;
2)
   . dbpipe.sh LOAD .
   . dbpipe.sh OPEN .
   dbpSQL "DATABASE $database;"
   echo "250 selections, wait please..."
   integer i=0
   time while [ $((i+=1)) -le 250 ]; do
      dbpSQL "SELECT tabname FROM Systables WHERE tabid=1;" /dev/null a1
   done
   . dbpipe.sh CLOSE .
   . dbpipe.sh UNLOAD .
   grep -c systables a1
   echo "Look at file a1"
   ;;
3)
   . dbpipe.sh LOAD .
   . dbpipe.sh OPEN .
   dbpSQL "DATABASE $database;"
   echo "(250 selections)& (250 selections)&, wait please..."
   (  integer i=0
      time while [ $((i+=1)) -le 250 ]; do
         dbpSQL "SELECT tabname FROM Systables WHERE tabid=1;" /dev/null a1
      done
   )&
   pid1=$!
   (  . dbpipe.sh OPEN .
      integer i=0
      time while [ $((i+=1)) -le 250 ]; do
         dbpSQL "SELECT owner FROM Systables WHERE tabid=1;" /dev/null a2
      done
      . dbpipe.sh CLOSE .
   )&
   pid2=$!
   wait $pid1 $pid2
   . dbpipe.sh CLOSE .
   . dbpipe.sh UNLOAD .
   grep -c systables a1
   grep -c informix a2
   echo "Look at files a1 a2"
   ;;
*)
   echo "usage:
$0 1|2|3 [database_name]
   default database_name - stores"
   ;;
esac
@//E*O*F dbptst.sh//
chmod u=rwx,g=rwx,o=rx dbptst.sh
 
exit 0