From: cortesi@informix.com (David Cortesi)
Message-Id: <1993Jan5.233008.7563@informix.com>
Subject: Repost: making isql output horizontal
Date: 5 Jan 93 23:30:08 GMT
Reply-To: cortesi@informix.com (David Cortesi)
Organization: Informix Software, Inc.
X-Informix-List-Id: <news.2423>

The version of this program posted earlier had 1 and 1/2 bugs,
which are fixed in the following.  Also note that "nawk" as
distributed by Sun is suitable for executing this.

#
#	reform.isql.output.awk
#
#   An awk program to reformat an Informix-ISQL output listing,
#   changing it from vertical format to horizontal report format.
#
#   Usage:
#       gawk -f invsql [dpat="data-pattern"] [lpp=n] [hpat="head-pattern"]
#
#   <data-pattern> is a printf() pattern to format the column values
#   <n> is the number of lines of data per page
#   <head-pattern> is a printf() pattern to format the column headings
#
#   Headings are printed only when lpp is specified as greater than zero.
#   See below for default heading and data patterns.
#
#   As written, supports only 30 columns of output. See the end of the
#   program for how to expand this to more columns if required.
#
#   Requires an "awk" that matches the book by Aho et.al, that is,
#   Gnu awk or SunOS "nawk" -- not the obsolete awk shipped by Sun, NeXT, etc.
#
#   Author: David Cortesi (cortesi@informix.com)
#
# --------------------- User's Guide (wysiwig!) --------------------------
#
#   Standard input to invsql is an ISQL vertical-format report like this:
#
# order_num      1007
# order_date     03/25/1989
# customer_num   117
# backlog        n
# po_num         278693
# ship_date      04/23/1989
# ship_weight    125.90
#
#   We change it to horizontal format with optional page headings:
#
# order_num order_date customer_num backlog po_num  ship_date ship_weight
#      1007 03/25/1989          117       n 278693 04/28/1989      125.90
#      1012 06/05/1989          117       n 278701 06/09/1989       70.80
#
#   The program collects the column values from a group of input lines,
#   then prints one output line using a printf() like this:
#       printf(pattern,col1,col2,...,coln)
#   where each "col" is the string value of that column from the input.
#
#   The default pattern is: "%nns %nns...\n" where each "nn" is the
#   default width of that column, which is: the larger of the width of
#   the heading text for that column, and the width of the data in that
#   column in the very first input group.
#
#   The default is often wrong, but you can specify exact widths, and
#   control the format in many other ways, by specifying a printf()
#   pattern string as the command-line argument dpat="pattern".
#
#   The program can print column headings at the top of each page of
#   data.  The default is to NOT print headings -- you can paginate
#   the output using the pr(1) command for example.  However if you
#   specify lpp=n, n>0, the program will print column headings before
#   each group of n data lines.
#
#   The default column heading display is:
#       printf("\f%nns %nns...\n\n",col1,col2...coln)
#   where each "col" is the heading text of that column from the first
#   input group, and the "nn" values are as for the data pattern.
#   You can supply your own pattern using hpat="pattern" on the command line.
#
#   When writing printf patterns as part of c-shell commands you need
#   only write the string in quotes, like this: dpat="%-5d\t%20f\n"
#   (The c shell does not object to backslashes in such quotes.)
#
# version of 1/4/93 with a bug fixed
# --------------------- The Program  --------------------------

BEGIN { state = 0; colno = 0; recno = 0 }

# Leading blank lines: ignore them

state == 0 && $1 == "" { next }

# First data line of first group: note the length of the prefix
# (the total width of heading and spaces), which is the same on each line,
# even when the data value on a line is null.

state == 0 && $1 != "" {
    match($0,/^[^ ]+ */)
    pfxlen = RLENGTH+1
    state = 1 # now into first data record
}

# Any data line of any group: save the string value of the data line,
# which is the whole line to the right of the prefix.  Data is saved
# in the array v[1..n].

$1 != ""  {
    v[++colno] = substr($0,pfxlen)
}

# Any data line of the first data record: save the column name
# as well, and set the default length of this column.  Column
# names are saved in array h[1..n], and lengths in dlen[1..n].

state == 1 && $1 != "" {
    h[colno] = $1
    lh = length($1)
    lv = length(v[colno])
    dlen[colno] = (lh > lv)?lh:lv
}

# End of first input group (empty line in state==1): build the
# default print patterns, or use the supplied ones.

state == 1 && $1 == "" {
    pl = 0  # no pagination
    if (lpp > 0) pl = lpp
    pd = ""
    for( j = 1; j <= colno; ++j ) pd = pd "%" dlen[j] "s "
    sub(" $","\n",pd)
    ph = "\f" pd "\n"
    if (dpat != "") pd = fixup(dpat)
    if (hpat != "") {
        ph = fixup(hpat)
        if (pl == 0) pl = 60
    }
    if (pl > 0) recno = pl-1 # force starting headings
    state = 2 # no more setting up to do
}

# End of any input group (empty line): print.

$1 == "" { do_output() }

# End of input file: print if the last line wasn't a blank

END { do_output() }

# Output process: print accumulated column values horizontally
# and reset the column counter.

function do_output() {

  if (colno > 0) { # some columns collected
  # the printf statements have to list every possible column 1..colno
  # Columns that do not exist generate no output (because there's no
  # format for them in the pattern :) and awk does not object to you
  # referring to empty array elements.  To support more columns, add
  # more lines to the printf statements below, following the same pattern.

    if ( ++recno == pl ) { # only succeeds when pl > 0
        printf ph \
            ,h[1],h[2],h[3],h[4],h[5],h[6],h[7],h[8],h[9],h[10] \
            ,h[11],h[12],h[13],h[14],h[15],h[16],h[17],h[18],h[19],h[20] \
            ,h[21],h[22],h[23],h[24],h[25],h[26],h[27],h[28],h[29],h[30]
        recno = 0
    }
    printf pd \
            ,v[1],v[2],v[3],v[4],v[5],v[6],v[7],v[8],v[9],v[10] \
            ,v[11],v[12],v[13],v[14],v[15],v[16],v[17],v[18],v[19],v[20] \
            ,v[21],v[22],v[23],v[24],v[25],v[26],v[27],v[28],v[29],v[30]
    colno = 0
  }
}

# As received from the command line, the print patterns still have
# literally "\n" instead of a newline. Convert the 5 possible format
# effectors to the real things.

function fixup(pat) {
    gsub("\\\\f","\f",pat)
    gsub("\\\\n","\n",pat)
    gsub("\\\\r","\r",pat)
    gsub("\\\\t","\t",pat)
    gsub("\\\\v","\v",pat)
    return pat
}