Subject: Cool New Tool for ISQL users
Newsgroups: comp.databases.informix

From: [email-masked] (Tim Schaefer)
Date: 8 Jul 1995 16:07:37 GMT

New Tool:        loop_scr

Author:          Tim Schaefer, The Computer Business Company, FL, USA

Requirements:   ISQL, AWK,  sformbld, Bourne shell    
                ISQL            -    should work with most versions
                AWK             -    at least a newer awk, but try for nawk.
                sformbld        -    Not required if you have the ISQL interface
                Bourne shell    -    still the lowest common denominator.

Introduction

Sometimes we have slow days, and look for opportunities to do things to better
our world, in particular, our programming world.  I had an opportunity the
other day to spend a moment on a tool, and thought I'd share it here.  As
Gump would've pointed out, I had no particular reason to write this tool,
other than the desire to try and make it work.  In writing it, I get a 
better understanding of some of the issues surrounding its' use.  And it
does come in handy rather than jumping into the form generator in ISQL.

Of particular curiousity to me ever since I started using INFORMIX SQL and
the 4GL, has been the "HOW" and "WHY" of the ISQL interface.  I'm probably
less interested in the WHY part, and more interested in the HOW.  We never
really get any inside information from INFORMIX folk about the WHY and HOW,
as this would expose them to even more user interaction than would probably
be healthy.  But occasionally they do talk about these products, and I'm
on bended ear.  There's always room for improvement, but this article is
not about improving the product, but improving the USE of the product.
Given that the product cannot or will not be changed, how can I improve my
USE of the product?  

The particular tool I present here is more for experimentation than anything.
I don't presume its' value to you, other than fodder for future use in 
creating your own tools.  The idea of creating tools is a constant curiousity
for me, putting a habitual thought of how to make things better than just
accepting things as they are.  This can also expose oneself to the group
with the "knowledge gap".  This is where your solution is less than optimal
and other folks know better.  But contributions being what they are, in 
this case cost-free, the expense is only the time to try it out.  I look
forward to your suggestions for improvements.  It's probably more 
interesting than vendor bashing too.

The Tool

ISQL is a strange critter.  Not too many sites use it in its' fullest 
measure, with the PERFORM mechanisms, but of late I have seen some postings
that would suggest folks are still PERFORMing.  I like some of the PERFORM
features but really defer to 4GL.  The biggest use I get out of PERFORM
is the cheap-n-easy screen runner, allowing easy access to tables without
having to write SQL, and the SQL scratch-pad, allowing the ad hoc SQL 
interface to the data base.  These two features alone make it a good buy.

A while back I had introduced a screen-code generator that allows you to 
create a screen on the command-line, in several versions, one that creates
bordered screens, and another that produces plain borderless screens.  These
are two tools I use a lot.  But the other day I needed the ability to 
create a screen with more than one table.  What to do?  Well, take an
existing tool, and improve it, of course.

By the way, I've used this tool with both the 5.01 and 7.1 engines, and it
works very well.  Bugs are discussed below.

Let's take a look at the logic of this program:

0 ########################### loop_scr: Make an INFORMIX screen #################
1 if [ $# -lt 3 ]; then
2  echo "Usage: loop_scr database  table.lst  output_file  [project]"
3  exit
4 else
5   i=0
6   for TBL in `cat $TAB_LIST`
7   do
8       i=`expr $i + 1`
9       TAG_PFX=`echo ${TAG_PREFIX_ARR} | awk -F":" ' BEGIN { arr_cnt=0
10                               idx_no='${i}'
11                               }
12      { arr_cnt = split( $0, pic_arr , ":" ) }
13      { print  pic_arr[idx_no]  } ' `
14       INFO_ZERO=${TBL}_0.tmp
15        INFO_ONE=${TBL}_1.tmp
16        INFO_TWO=${TBL}_2.tmp
17      INFO_THREE=${TBL}_3.tmp
18      echo $TAG_PFX
19      get_data_types
20      cnvt_data_types
21  done
22  cat *_3.tmp > ${GRAND_ATTR}1.tmp
23  make_attr_list
24  draw_screen
25  build_scr_hdr
26  build_screen
27  build_scr_tbl
28  dump_attrs
29  append_instr
30  add_req_cmts
31  put_borders
32  rm *.tmp
33 fi

Line    Description
0-3     Argument checking
5-21    Build a data file with the raw information needed to create ATTRIBUTES 
        for each table.  Save this information in a separate file for later 
        use.
22      concatenate all the individual data files for each table into one data 
        file.
23      Convert the data into a format that will compile using sformbld or 
        fglform.  Save this data for later use.
24      Start assembling the screen(s).  This first phase builds the fields into
        a data file, and then the screens will be enhanced later.
25      The first screen for the first table needs that DATABASE statement, so
        it's put here.
26      The assembly of the screens starts, and a new "page" is created if 
        either a 15-line limit is reached or a new table name is found, so when
        you run the form it should be theoretically less confusing.
27      Next comes the "TABLES" and "ATTRIBUTES" key words.
28      Now append the actual attributes.  You'll notice that each table has a 
        new prefix on the TAG, so that you can easily differentiate from each
        table visually.  This is not necessary, but an added touch.
29      Append the INSTRUCTIONS portion.
30      There are COMMENTS on each field, that tell the user the data type and 
        length of the field.  But it's also a good safety practice to put the
        REQUIRED attribute on fields that are NOT NULL in the data base.  We
        tracked this feature early on, expressly for this purpose.
31      And now for the gravy.  Let's put borders on each screen, to enhance the
        overall look of the screen.
32      We're done!  Let's get rid of all those nasty tmp files and compile the
        form using sformbld.

        You're now ready to give it a shot.  

    Usage: loop_scr database  table.lst  output_file  [project]

    database    -   a valid data base you can access.
    table.lst   -   a file with a list of some valid tables in the
                    valid data base.
    output_file -   Name of your new multi-table screen.  ( scr.per )
    [project]   -   A 10-char string which is placed in the lefthand
                    portion of the screen, such as a project or your
                    company's initials, or the product name--whatever.

Bugs

    Well, if you're really into experimentation, make a table list with
    about 20 or 30 tables and watch what happens.  Oh yes, the loop_scr
    program will work fine.  But a couple of limitations exist with the
    PERFORM mechanism.  You can only have 26 single-character fields
    in the whole screen form.  Too bad.  Next, PERFORM will only run
    20 pages.  So, yes, you CAN create one big monster PER for your
    tables.  It might be fun to give it a try.  But it ain't gonna work.
    You might try this for all the tables and use it for some kind of 
    documentation tool, as all the fields are referenced here.

    Occasionally, the AWK logic may produce a header and then skip to
    top of page.  This is an easy edit, and the price of the tool is
    low enough to make this a bug to live with.

Epilogue

    I hope this will provide you with some functionality, if not the
    mere exploration of tool creation and use.  I look forward to 
    your suggestions for improvements, and new ideas.  I trust you'll
    find a certain amount of Object Orientated Shell Scripting here as
    well.  

Tim Schaefer
[email-masked]
July 1995

#!/bin/sh
###############################################################################
#                                   loop_scr
###############################################################################
#      AUTHOR:  Tim Schaefer, The Computer Business Company, Inc.
#               Copyright 1991, 1992, 1993, 1994, 1995  ALL RIGHTS RESERVED
#               No warranty expressed or implied.  This program remains the
#               property of Tim Schaefer and The Computer Business Company.
#               You are hereby granted a license to use this program.  You
#               may not reproduce, sell, distribute, publish, circulate, or 
#               commercially exploit the program(s), or any portion thereof,
#               without the written consent of Tim Schaefer and/or The Computer
#               Business Company.  This software is being made available to 
#               you as is, and Tim Schaefer and TCBC are not liable for 
#               any damages or loss from the use of this program.  
#               Use of this program constitutes an agreement with these terms.
###############################################################################
#       LATEST: Sat Jul  8 10:53:22 EDT 1995
#        USAGE: loop_scr  database  table.lst  output_file  [project ]
###############################################################################
DB=$1
TAB_LIST=$2
NEW_PER=$3
PROJECT=$4
AWK=nawk
TAG_PREFIX_ARR="a:b:c:d:e:f:g:h:i:j:k:l:m:n:o:p:q:r:s:t:u:v:w:x:y:z"
if [ -z "$PROJECT" ]; then
   PROJECT=$LOGNAME
fi
SCR_REC=$5
if [ -z "$SCR_REC" ]; then
   SCR_REC=${LOGNAME}_rec
fi
GRAND_ATTR=grand_attr
ATTR_FILE=${GRAND_ATTR}2.tmp
SCREEN_TMP=${NEW_PER}.tmp
###############################################################################
convert_type()
{
coltype_num=$coltype
col_length=$collength
n_null_flag=0
if [ $coltype_num -ge 256 ] ; then
    coltype_num=`expr $coltype_num - 256 `
    n_null_flag=1
fi
#-----------------------------------------------
case $coltype_num in
     0) coltype_nm="char |$col_length|"      ;;
     1) coltype_nm="smallint|6|"             ;;
     2) coltype_nm="integer|11|"             ;;
     3) coltype_nm="float|14|"               ;;
     4) coltype_nm="smallfloat|14|"          ;;
     5) coltype_nm="decimal|20|"             ;;
     6) coltype_nm="serial|11|"              ;;
     7) coltype_nm="date|10|"                ;;
     8) coltype_nm="money|10|"               ;;
     9) coltype_nm="undefined|0|"            ;;
    10) coltype_nm="datetime|20|"            ;;
    11) coltype_nm="byte|10|"                ;;
    12) coltype_nm="text|20|"                ;;
    13) coltype_nm="varchar|20|"             ;;
    14) coltype_nm="interval|20|"            ;;
     *) coltype_nm="undefined|0|"            ;;
esac 
#-----------------------------------------------
if [ $n_null_flag -eq 1 ] ; then
    coltype_nm="$coltype_nm$TAG_PFX|N|"
else
    coltype_nm="$coltype_nm$TAG_PFX|Y|"
fi
echo "$TBL|$colname|$coltype_nm"
}
#############################################################################
get_data_types()
{
$INX_SQL $DB - > ${INFO_ZERO} <<-!
select tabid from systables where tabname = "$TBL"; 
!
sed -e '/^$/d'  -e '/tabid/d' -e 's/ //g'  ${INFO_ZERO} > FOO.TMP
mv FOO.TMP ${INFO_ZERO}
TABID=`cat ${INFO_ZERO}`
echo "$DB.$TBL : $TABID"
$INX_SQL $DB - > ${INFO_ONE} <<-!
select colname    ,  colno    , coltype , collength  
  from syscolumns where tabid = $TABID 
 order by colno
!
sed -e '/colname/d' -e '/^$/d' -e '/^$/d'  ${INFO_ONE} > ${INFO_TWO}
$AWK ' { printf("'$TBL'|%s|%s|%s|%s|%s|\n", $1, $2, $3, $4,"'${TAG_PFX}'" ) } ' ${INFO_TWO} > ${INFO_ONE}
}
#############################################################################
cnvt_data_types()
{
LINECNT=`wc -l ${INFO_ONE} | $AWK ' { print $1 } '` 
cat ${INFO_TWO} | while read colname col_ord coltype collength
do
 convert_type
done > ${INFO_THREE}
}
#############################################################################
make_attr_list()
{
sed -e 's/ //g' ${GRAND_ATTR}1.tmp > FOO.tmp
mv FOO.tmp ${GRAND_ATTR}1.tmp
$AWK -F"|" ' BEGIN { tag_mkr=0; count=0; tbl_col=""; div_cnt=0; scr_cnt=0
    alpha = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ" 
             one_cntr = 0; sub_str=""; fld_wdth=0; req_string="";
             # tag_pfx="'${TAG_PFX}'"
             tag_pfx=""
    }
{
    tag_pfx=$5
    { if ( $3 == "char" )     {  count=$4  } }
    { if ( $3 == "smallint" ) {  count=$4  } }
    { if ( $3 == "integer" )  {  count=$4  } }
    { if ( $3 == "float" )    {  count=$4  } }
    { if ( $3 == "decimal" )  {  count=$4  } }
    { if ( $3 == "serial" )   {  count=$4  } }
    { if ( $3 == "date" )     {  count=$4  } }
    { if ( $3 == "money" )    {  count=$4  } }
    { if ( $3 == "datetime" ) {  count=$4  } }
    { if ( $3 == "byte" )     {  count=$4  } }
    { if ( $3 == "text" )     {  count=$4  } }
    { if ( $3 == "varchar" )  {  count=$4  } }
    { if ( $3 == "interval" ) {  count=$4  } }
    
    { if ( $6 == "N" )     {  req_string=", REQUIRED "  } }
    { if ( $6 == "Y" )     {  req_string=" "  } }

    fld_wdth=40
#-------
    { if ( count == 1 )
         {
          ++one_cntr
          sub_str = substr( alpha, one_cntr, 1 )
          { printf("%s|%s|%s|, COMMENTS = \"%s - %s %d\"%s;|%d| \n",  sub_str,  $1, $2, $2, $3, count, 
req_string, count ) } 
         }
    }
#-------
    { if ( ( count > 1 )  && ( count <= fld_wdth ) )
         {
         { printf("%s%0u|%s|%s|, COMMENTS = \"%s - %s %d\"%s;|%d|\n", tag_pfx, tag_mkr, $1, $2 , $2, $3, 
count,  req_string, count ) }
         }
    } 
#-------
 { if ( count > fld_wdth )    
      {
        lin_cnt=( count / fld_wdth );         # establish number of lines
        { if ( lin_cnt < 2 ) { lin_cnt=2; } }
        start_pos=-39; end_pos=0;                       # initial values

     #  { printf("%d %d %d \n", start_pos, end_pos, fld_wdth ) }

        { for( i = 0; i <= lin_cnt; i++ )
             {
               start_pos+=fld_wdth;
               end_pos+=fld_wdth;
               
               { if ( end_pos > count )
               {
                     end_pos = count;
                    fld_wdth = count - start_pos;
               }
               }

               { if ( ( start_pos == end_pos ) || ( fld_wdth <= 0 ) )
               {
                    break;
               }
                else
                    {
                    { printf("%s%0u%d|%s|%s|[%d,%d], COMMENTS = \"%s - %s %d\"%s;|%d|\n", 
tag_pfx,tag_mkr,i, $1, $2 ,start_pos, end_pos,  $2, $3, count, req_string, fld_wdth ) }
                    }
               }
            }
        }
      }
  }
    ++tag_mkr;
}
' ${GRAND_ATTR}1.tmp > ${ATTR_FILE}
# grep "=" ${ATTR_FILE} > FOO.tmp
# mv FOO.tmp $ATTR_FILE
}
#############################################################################
draw_screen()
{
$AWK -F"|" ' BEGIN { tag_mkr = 0; count = 0; one_cntr = 0; sub_str=""; tab="" }
   {
    attr=$1
    tab=$2
    column_name=$3
    count=$5
     { if ( count == 1 )
          {
          { printf("%20s:[%s] | %s |\n", column_name, attr, tab ) }
          }
     }
     { if ( count > 1 )
          {
           ln_chk= length( attr )
           count = count - ln_chk
           { printf("%20s:[%s ", column_name, attr ) }
           { for ( i = 1; i < count ; ++i  )
                 {
                   { printf(" ") }
                 }
           }
           { printf("] | %s |\n", tab ) }
          }
     }
     ++tag_mkr
   }
' ${ATTR_FILE} > $SCREEN_TMP
# grep ":" ${SCREEN_TMP} > FOO.tmp
# cp FOO.tmp $SCREEN_TMP
}
#############################################################################
build_scr_hdr()
{
echo "DATABASE $DB" > $NEW_PER
}
#############################################################################
build_screen()
{
$AWK -F"|" '  BEGIN { line_cnt=0; prj="'$PROJECT'"; db="'$DB'"; tbl="";
               page_no=1; last_tab="O";
                    }
  {       
   ++line_cnt
   { if ( last_tab ~ "O" )
          {
          page_no=1;
          scr_header()
          }
   }
   { if ( last_tab !~ $2 && last_tab !~ "O" )
          {
          { printf ("%c\n",125 ) }
          page_no=1;
          scr_header()
          line_cnt=0;
          }
   }
   { if ( line_cnt == 15 ) 
          {
          { printf ("%c\n",125 ) }
          ++page_no;
          scr_header()
          line_cnt=0;
          }
   }
   { printf("%s\n",$1 ) }
   last_tab = $2
  }
  function scr_header()
  {
  { printf("SCREEN\n\%c\n",123 ) }
  { printf(" %8-s %10s.%18s Table Maintenance Screen         %2d \n\n" , prj, db, $2, page_no ) }
  { printf("\\gt-----------------------------------------------------------------------------u\n" ) }  
  }
 ' $SCREEN_TMP >> $NEW_PER
echo "}
" >> $NEW_PER
}
#############################################################################
build_scr_tbl()
{
echo "TABLES\n" >> $NEW_PER
for table_name in `cat $TAB_LIST`
do
    echo "$table_name" >> $NEW_PER
done
echo "\nATTRIBUTES" >> $NEW_PER
}
#############################################################################
dump_attrs()
{
$AWK -F"|" ' { printf("%s = %s.%s %s\n", $1, $2 , $3, $4 )} ' $ATTR_FILE >> $NEW_PER
}
#############################################################################
append_instr()
{
echo "INSTRUCTIONS
DELIMITERS \"  \" 
" >> $NEW_PER
}
#############################################################################
add_req_cmts()
{
sed 's/\", REQUIRED/  - REQUIRED \", REQUIRED/' $NEW_PER > FOO.TMP
mv FOO.TMP $NEW_PER
}
#############################################################################
put_borders()
{
$AWK ' BEGIN { ln_len=0 ; end_pt=0; start_pt=0; cnt=0;
               pad_cnt=0; pad_char=" "; trip_wire=0;
               max_width=76;
             }
/^\{/ {  trip_wire=1; cnt=1 }
/^\}/ {  trip_wire=2; cnt=1 }
{
    ++cnt
    ln_len  = length( $0 )
    pad_cnt = max_width - ln_len 
    # { printf("%d : %d \n", NR, ln_len ) }
 { if ( trip_wire == 2 )
      {
      { if ( ln_len == 1 ) 
           { 
           # { printf("%s\n",$0 ) }  
           { 
printf("\\gb-----------------------------------------------------------------------------d\n" ) }  
           } 
      }
      }
 }
      # { print cnt }
 { if ( trip_wire == 1 )
      {
      pad_cnt = max_width - ln_len 
      { if ( ln_len == 1 ) 
           { 
           { printf("%s\n",$0 ) }  
           { 
printf("\\gp-----------------------------------------------------------------------------q\n" ) }  
           } 
      }
      { if ( ln_len > 1 )
         {
         { if ( cnt == 5 )
               {
              { printf("%s\n" ,$0 ) }
               }
            else
              {
              { printf("\\g\|\\g %s" ,$0 ) }
              { if ( ln_len < max_width )
                   {
                   pad_cnt = max_width - ln_len 
                   # { print pad_cnt " " ln_len }
                   { for ( i=0; i < pad_cnt; i++ )
                         {
                          { printf("%s", pad_char ) }
                         }
                   }
                   }
              }
               { printf("\\g\|\\g\n", $0 ) }
               }
            }
            }
      }
     # { printf("%d : %d : %s \n", NR, ln_len, $0 ) }
      }
    else
       {
       { printf("%s\n", $0 ) }
       }
  }
}

' $NEW_PER > FOO.TMP
mv FOO.TMP $NEW_PER
}
########################### loop_scr: Make an INFORMIX screen #################
if [ $# -lt 3 ]; then
   echo "Usage: loop_scr database  table.lst  output_file  [project]"
   exit
else
    i=0
    for TBL in `cat $TAB_LIST`
    do
        i=`expr $i + 1`
        TAG_PFX=`echo ${TAG_PREFIX_ARR} | awk -F":" ' BEGIN { arr_cnt=0 
                                 idx_no='${i}' 
                                 }
        { arr_cnt = split( $0, pic_arr , ":" ) }
        { print  pic_arr[idx_no]  } ' `
         INFO_ZERO=${TBL}_0.tmp
          INFO_ONE=${TBL}_1.tmp
          INFO_TWO=${TBL}_2.tmp
        INFO_THREE=${TBL}_3.tmp
        echo $TAG_PFX
        get_data_types
        cnvt_data_types
    done
    cat *_3.tmp > ${GRAND_ATTR}1.tmp
    make_attr_list
    draw_screen
    build_scr_hdr
    build_screen
    build_scr_tbl
    dump_attrs
    append_instr
    add_req_cmts
    put_borders
    rm *.tmp
fi

-- 
                                     \\|//
                                     (O|O)
==============================---o00--(_)--00o---============================
Tim Schaefer            [email-masked]      http://www.gate.net/~tschaefe
                           And now a Florida FAQ
-----------------------------------------------------------------------------
Q: What's a REDNECK Special?
A: Catfish, shrimp, and gator balls,  w/french fries, of course
=============================================================================