NetNews Usenet Archive 1992 #16/NN_1992_16.iso/spool/comp/database/informix/1632

Internet Message Format | 1992-07-29 | 6.9 KB

Path: sparky!uunet!oracle!pyramid!infmx!news
From: [e-mail] (David Cortesi)
Newsgroups: comp.databases.informix
Subject: awk script to flatten isql output
Message-ID: <[e-mail]>
Date: 29 Jul 92 20:14:31 GMT
Sender: [e-mail] (Usenet News)
Reply-To: [e-mail]
Organization: Informix Software, Inc.
Lines: 182


When you drive ISQL (or dbaccess?) from the command line, you can
store its output in a redirected file.
    isql stores - >customer.list  <<@
    select * from customer;
    @
When the output width would exceed 79 chars, ISQL goes to vertical
format, producing like:
    customer_num  118
    fname         Dick
    lname         Baxter
    company       Blue Ribbon Sports
    address1      5427 College
    etc...
This was debated recently on comp.databases.informix.  The following
awk program is one solution.  It takes ISQL output on standard
input and reformats it so each data row is a single, flat line.
It provides quite a bit of power to format the result, as well.

The key insight that makes this useful is, that awk's printf() will
take its pattern from a variable, and a variable can be set from
the command line.  So this script prepares a fairly stupid printf
format based on the first input record contents -- but it lets you
override it to do almost any sort of formatting you want.

----------- cut here & save as "invsql" ---------------------------
#
#   invsql: an awk program to reformat an Informix-ISQL output listing,
#   changing it from vertical format to horizontal report format.
#
#   Usage:
#      [g]awk -f invsql [dpat="data-pattern"] [lpp=n] [hpat="head-pattern"]
#
#   <data-pattern> is a printf() pattern to format the column values
#   <head-pattern> is a printf() pattern to format the column headings
#   <n> is the number of lines of data per page
#
#   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,
#   System V awk or Gnu awk -- not the obsolete awk shipped by Sun, NeXT, etc.
#
#   Author: David Cortesi ([e-mail])
#
# --------------------- User's Guide, so to speak --------------------------
#
#   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 prints each output line using a printf() like this:
#       printf(pattern,col1,col2,...,coln)
#   where each "col" is one value of that column from the input.
#   The default pattern is: "%nns %nns...\n" where each "nn" is the
#   default width of that column, determined by the following rule:
#   The default width is the larger of the width of the heading text
#   and the width of the data value in the first input record.
#
#   The default is often wrong, but you can specify exact widths and
#   control the format in 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 record.  The pattern is essentially the default data pattern
#    with formfeed before and newline after.  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.)
#
# --------------------- 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
# on each line, the name of the first column and some spaces.
# The prefix has the same length on all lines, even when data 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 less the prefix.

$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.

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

# End of first data record: build the print patterns.

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 data record: print.

$1 == "" {
# 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]
}

# 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
}