Newsgroups: comp.databases.informix Subject: Cool New Tool for ISQL users From: tschaefe@gate.net (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 tschaefe@gate.net 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 tschaefe@gate.net 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 =============================================================================