Source Code 1994 March/Source_Code_CD-ROM_Walnut_Creek_March_1994.iso/compsrcs/misc/volume34/shql/part01

Text File | 1993-01-05 | 31.0 KB | 1,191 lines

Newsgroups: comp.sources.misc
From: [e-mail] (Bruce Momjian)
Subject: v34i091:  shql - Interactively read and execute SQL commands, v1.1, Part01/01
Message-ID: <[e-mail]>
X-Md4-Signature: e4ac8d117a071b7c1ff054cf369523b1
Date: Thu, 7 Jan 1993 03:26:41 GMT
Approved: [e-mail]

Submitted-by: [e-mail] (Bruce Momjian)
Posting-number: Volume 34, Issue 91
Archive-name: shql/part01
Environment: SQL, UNIX
Supersedes: shql: Volume 25, Issue 15

Here is shql, version 1.1.  It has two added features, the ability to do
SELECT's with multiple tables, and support for aggregates like AVG(),
and SUM().

SHQL is an interactive SQL database engine.  Written as a shell script,
SHQL interprets SQL commands and manipulates flat files based on those
commands.  SHQL is limited in its understanding of SQL constructs.  All
this is outlined in the README file contained in the distribution.  A
demo file is also included to show some examples.

------------------------------------------------------------------------
#! /bin/sh
# This is a shell archive.  Remove anything before this line, then feed it
# into a shell via "sh file" or similar.  To overwrite existing files,
# type "sh file -c".
# Contents:  README CHANGES demo.shql shql
# Wrapped by kent@sparky on Wed Jan  6 21:17:13 1993
PATH=/bin:/usr/bin:/usr/ucb:/usr/local/bin:/usr/lbin ; export PATH
echo If this archive is complete, you will see the following message:
echo '          "shar: End of archive 1 (of 1)."'
if test -f 'README' -a "${1}" != "-c" ; then
  echo shar: Will not clobber existing file \"'README'\"
else
  echo shar: Extracting \"'README'\" \(4243 characters\)
  sed "s/^X//" >'README' <<'END_OF_FILE'
X                              S H Q L  version 1.1
X
X    Shql is a program that reads SQL commands interactively and
X    executes those commands by creating and manipulating Unix files.
X
X    This program requires a bourne shell that understands functions,
X    as well as awk, grep, cut, sort, uniq, join, wc, and sed.
X
X     This script can be invoked with the command
X
X        shql [-q] {database name}
X
X      A directory must be created for the database before you may use it.
X    This directory will house all data files for a single database.
X    All datafiles are created with mode 666 ('rw-rw-rw-'), so create the
X    directory with 777 ('rwxrwxrwx') if you want the database to be
X    sharable, and 700 ('rwx------') to be private.  Of course, multiple
X    databases are possible.  A database called 'mydb' may be created
X    as a directory $HOME/mydb, $HOME/shql/mydb, ./mydb, or as
X    $SHQL_ROOT/mydb, where $SHQL_ROOT is defined below.  The -q
X    option turns off the display of headings so the output of shql
X    can be used by other programs by caputuring all lines that begin
X    the pipe symbol.
X
X      The program is patterned after Ingres' interactive sql terminal
X    monitor program.  Terminal monitor commands begin with either a
X    forward or back-slash.  Forward slashes may appear at the end of a
X    commend line. Back-slashes are accepted for compatability.  The /g
X    is the 'go' command, /p is print, and /q is quit.  Try 'help commands'
X    for a full list.  Because of this, if you need to put a slash as
X    the second to last caracter on a line, you should add a space
X    between the slash and the last character.
X
X    To get started, invoke shql with a database name.  Use the directory
X    name you created above. Type
X
X        shql mydb
X
X    if the directory you created was 'mydb'.  Once shql starts up, you
X    should see the database name displayed, and then a '*'. At this
X    point, the most valuable thing is to type help,
X
X        * help
X        * /g
X
X    You may then go on.  The command 'help syntax' displays syntax
X    for all SQL operations, and 'help commands' displays all shql
X    workspace commands.  Try the demo.
X
X    Shql can execute only one operation at a time, but operations can
X    be spread over several lines.
X
X    Shql operations are allow 'select' operations on multiple tables.
X    Table names are read from left to write in select's 'from'
X    section, so the tables should be ordered with the most central
X    tables first.  In two-table joins, it doesn't matter.  In three
X    table joins, if you join table A-to-B and B-to-C, B must not be
X    the last table in the from clause, because shql will not be able
X    to join tables A-C.  If you get the message 'Join not found, try
X    reordering tables', this is probably the problem.  Also
X    qualified field names are not understood, like tablename.fieldname,
X    so if you are joining my_id in table A with my_id in table B, just
X    say 'my_id = my_id'.  Views can also be used to create
X    multi-table selects.
X
X    Subselects are implemented, but must be the last operand of a
X    'where' clause, most useful with 'in'.
X
X    In most cases, commas are optional.  NULLs are not implemented.
X    Aggregates like AVG() are implemented, but not with GROUP BY.
X
X    When INSERTing strings that contain the characters !,=,>,<,(, or ),
X    spaces or backslashes may be added during the insert.  This is a
X    side-effect of the string manipulation needed to properly
X    parse the command parameters.
X
X    This SQL is type-less, so specify just the column width when creating
X    tables.  This is used only for display purposes.  Shql is
X    case-sensitive, and expects SQL key words to be in lower case.
X
X    Commands can be piped into shql, and the table data files are
X    tab delimited, so awk scripts can be used to generate reports
X    directly from the tables.  To operate on non-shql data files,
X    create a dummy table with the proper fields, then copy your file
X    into your shql data directory, replacing your delimiters with
X    tabs, then run shql on the table, and convert the table back to
X    its original format.  Grave accents (`) may be used to execute
X    unix command from with shql.  Environment variables may also be
X    used. See the demo for an example, i.e. "cat demo.shql | shql mydb".
X
X    If you have comments, suggestions, or bug reports contact:
X
X        Bruce Momjian, [e-mail] ([e-mail])
END_OF_FILE
  if test 4243 -ne `wc -c <'README'`; then
    echo shar: \"'README'\" unpacked with wrong size!
  fi
  # end of 'README'
fi
if test -f 'CHANGES' -a "${1}" != "-c" ; then
  echo shar: Will not clobber existing file \"'CHANGES'\"
else
  echo shar: Extracting \"'CHANGES'\" \(506 characters\)
  sed "s/^X//" >'CHANGES' <<'END_OF_FILE'
XNew to version 1.1
X-----------------------------------
XNow runs under ksh as well as sh.
XMulti-table joins possible without creating views
XAggregates now supported
XLooks in your $HOME/shql/ for database name also
XExecution operators are now '\' and '/',with '/' now possible the end
X    of a line
XWhite-space is not required as it was before
XNew -q option removes table headers, so only taking output that begins
X    with '|' gets you all the data
XDelete syntax now requires a FROM, as it should have all along
END_OF_FILE
  if test 506 -ne `wc -c <'CHANGES'`; then
    echo shar: \"'CHANGES'\" unpacked with wrong size!
  fi
  # end of 'CHANGES'
fi
if test -f 'demo.shql' -a "${1}" != "-c" ; then
  echo shar: Will not clobber existing file \"'demo.shql'\"
else
  echo shar: Extracting \"'demo.shql'\" \(2170 characters\)
  sed "s/^X//" >'demo.shql' <<'END_OF_FILE'
X# Demo for SHQL, version 0.60
X# Create table customer
Xcreate table customer (
X    name 30,
X    age  3,
X    status 1)
X/p/g
X
X# Put one person in the table
Xinsert into customer values ( 'Fred', 32, 'G' )/p/g
X
X# Study the table
Xhelp customer
X/p/g
Xselect * from customer/p/g
X
X# Add two more people
Xinsert into customer values
X( 'Barney', 29, 'G', 'Wilma', 28, 'D' )
X/p/g
Xprint customer
X/p/g
X
X# Get customers with 'G' status
Xselect * from customer
Xwhere status = 'G' /p/g
X
X# Get sorted list of customers by age
Xselect * from customer
Xorder by age num
X/p/g
X
X# Make a table to hold customer status codes and their descriptions
Xcreate table codes (
X    code 1,
X    description 10 )
X/p/g
X
X# Insert status codes
Xinsert into codes values
X( 'G', 'Good', 'B', 'Bad', 'D', 'Dead Beat' )
X/p/g
X
X# Create a view so we can see the customer name and status description
Xcreate view custstat ( customer.status = codes.code )
X/p/g
X
X# Look at the table
Xhelp custstat
X/p/g
Xselect * from custstat
X/p/g
X
Xselect *
Xfrom customer, codes
Xwhere status = code
X/p/g
X
X# Replace 'Barney' with 'Bad Bart'
Xupdate customer
Xset name = 'Bad Bart', status = 'X'
Xwhere age = 29
X/p/g
X
Xprint customer
X/p/g
X
X# Get all customers that have invalid status'es
Xselect * from customer
Xwhere status not in select code
X            from codes
X/p/g
X
X# Remove 'Fred'
Xdelete from customer
Xwhere age = 32
X/p/g
X
X# Get rid of view
Xdrop view custstat
X/p/g
X
X# Create a holding table for old customers
Xcreate table oldcust (
X    name 30,
X    status 1 )
X/p/g
X
X# Copy old customer to new table
Xinsert into oldcust (
X    name status )
Xselect name status
Xfrom customer
Xwhere age > 28
X/p/g
X
Xselect avg(age)
Xfrom customer
X/p/g
X
Xselect name
Xfrom customer
Xwhere age = select min(age)
X        from customer
X/p/g
X
X# Look at table
Xprint oldcust
X/p/g
X
X# Delete customers moved over
Xdelete from customer
Xwhere age > 28
X/p/g
X
Xprint customer
X/p/g
X
X# Try a union of the two tables
Xselect name age
Xfrom customer
Xunion
Xselect name status
Xfrom oldcust
X/p/g
X
X# Show example of executing Unix commands
Xinsert into customer
Xvalues ( '`date`', `ls / | wc -l`, 'Y' )
X/p/g
Xprint customer
X/p/g
X# Clean up
Xdrop table codes
X/p/g
Xdrop table customer
X/p/g
Xdrop table oldcust
X/p/g
X/q
END_OF_FILE
  if test 2170 -ne `wc -c <'demo.shql'`; then
    echo shar: \"'demo.shql'\" unpacked with wrong size!
  fi
  # end of 'demo.shql'
fi
if test -f 'shql' -a "${1}" != "-c" ; then
  echo shar: Will not clobber existing file \"'shql'\"
else
  echo shar: Extracting \"'shql'\" \(20449 characters\)
  sed "s/^X//" >'shql' <<'END_OF_FILE'
X#!/bin/sh
X# use /bin/sh or /bin/ksh
X#
X# shql - version 1.1
X#
X
X# DEFINE THESE
XSHQL_ROOT="/u/shql"        # system-wide database location
XEDITOR="${EDITOR:=/usr/bin/vi}" # default editor if EDITOR not defined
XSHELL="${SHELL:=/bin/sh}"     # default editor if EDITOR not defined
X
X# Unix table file postfixes:  @ is attrib, ~ is data, % is view
X
XDEBUG="N"    # set to Y for debugging
X
X[ "$DEBUG" = "Y" ] && set -x           # uncomment for debugging
X#set -v
XUMASK=`umask`
Xumask 0000        # share database
Xtrap "echo \"Goodbye\" ; \
X    rm -f /tmp/$$ /tmp/$$row /tmp/$$join*" 0 1 2 3 15
Xset -h            # remember functions
X
Xif echo '\c' | grep -s c ; then        # to adapt to System V vs. BSD 'echo'
X    NOCR1='-n'            # BSD
X    NOCR2=""
Xelse
X    NOCR1=""            # System V
X    NOCR2='\c'
Xfi
XNL='
X'
XTAB='    '
Xexport _IFS TABLE CMD NOCR1 NOCR2 NL TAB
X_IFS="$IFS"
X
Xif [ "X$1" = "X-q" ]
Xthen    QUIET="Y"
X    shift
Xfi
X
Xif [ "X$1" = "X" ]
Xthen    echo "Missing database name." 1>&2
X    echo "The database name must be a directory under $HOME/shql" 1>&2
X    echo "    or a directory under $SHQL_ROOT" 1>&2
X    exit 1
Xfi
Xecho "Database: $1"
X
Xif [ -d $HOME/shql/$1 ]
Xthen    cd $HOME/shql/$1
Xelif [ -d $SHQL_ROOT/$1 ]
Xthen    cd $SHQL_ROOT/$1
Xelif [ -d $HOME/$1 ]
Xthen    cd $HOME/$1
Xelif [ -d $1 ]
Xthen    cd $1
Xelse     echo "Unknown database ($1)" 1>&2
X    echo "The database name must be a directory under $HOME/shql" 1>&2
X    echo "    or a directory under $SHQL_ROOT" 1>&2
X    exit 1
Xfi
X
X
X#
X#**************************************************************************
X# syntax
X#**************************************************************************
Xsyntax(){
X    case "$1" in
X        create)    cat <<"END"
XCREATE TABLE table_name (
X    column_name column_width
X    {, ...}
X)
Xor
XCREATE VIEW view_name (
X    table_or_view1.column1 = table_or_view2.column2
X)
XEND
Xreturn 0
X;;
X        delete) cat <<"END"
XDELETE
XFROM table_name
X{ WHERE where_clause }
XEND
Xreturn 0
X;;
X        drop) cat <<"END"
XDROP TABLE table_name
Xor
XDROP VIEW view_name
XEND
Xreturn 0
X;;
X        edit) cat <<"END"
XEDIT table_name
Xis a non-standard method of changing a table's field names or display widths.
XEND
Xreturn 0
X;;
X        help)    cat <<"END"
XHELP ALL
Xor
XHELP TABLES
Xor
XHELP VIEWS
Xor
XHELP COMMANDS
Xor
XHELP [CREATE | DELETE | DROP | INSERT | SELECT | UPDATE | WHERE | PRINT | EDIT]
Xor
XHELP table_name
XCommands must appear in lower case.
XEND
Xreturn 0
X;;
X        insert) cat <<"END"
XINSERT INTO table_name
X    { ( column_name, ... ) }
XVALUES ( expression, ...)
Xor
XINSERT INTO table_name
X    { ( column_name, ... ) }
Xsubselect
XEND
Xreturn 0
X;;
X        print) cat <<"END"
XPRINT table_name
Xis a non-standard synonym for SELECT * FROM table_name.
XEND
Xreturn 0
X;;
X        select) cat <<"END"
XSELECT { DISTINCT }
X    [ column_name {,...} | * ]
XFROM [ table_name | view_name ]
X{ WHERE where_clause }
X{ ORDER BY column_name { NUM } { ASC | DESC } {, ... }
X{ UNION select statement }
X'NUM' is a non-standard method for sorting numeric fields.
XEND
Xreturn 0
X;;
X        update) cat <<"END"
XUPDATE table_name
XSET column_name = expression {, ... }
X{ WHERE where_clause }
XEND
Xreturn 0
X;;
X        where) cat <<"END"
XWHERE [ column_name | value ] [ =, !=, >, <, >=, <=, and, or, not, in ]
X      [ column_name | value | subselect ]
XParentheses may be used to group expressions.
XEND
Xreturn 0
X;;
X        syntax)    syntax commands; echo
X            syntax create; echo
X            syntax delete; echo
X            syntax drop; echo
X            syntax insert; echo
X            syntax select; echo
X            syntax update; echo
X            syntax where; echo
X            syntax print; echo
X            syntax edit; echo
X            return 0
X            ;;
X    esac
X    return 1
X}
X
X#
X#**************************************************************************
X# lookup_field
X#**************************************************************************
Xlookup_field(){
X    if [ ! -f $TABLE% ]
X    then    RESULT="`grep -n \"^$1    \" $TABLE@`"
X    else     RESULT="`grep -n \"^$1    \" $TABLE@ | sed 1q`"
X    fi
X    if [ ! "$RESULT" ]
X    then     OUTFIELD="$1"
X        return 1
X    else    OUTFIELDNUM="`expr "$RESULT" : '\([^:]*\)'`"
X        OUTFIELD="\$$OUTFIELDNUM"
X        return 0
X    fi
X}
X
X#
X#**************************************************************************
X# do_aggreg
X#**************************************************************************
Xdo_aggreg(){
X    if     [ "X$1" = 'Xsum' ]
X    then    AGGREG='total'
X    elif     [ "X$1" = 'Xavg' ]
X    then     AGGREG='(total/cnt)'
X    elif     [ "X$1" = 'Xcount' ]
X    then     AGGREG='cnt'
X    elif     [ "X$1" = 'Xmin' ]
X    then     AGGREG='min'
X    elif     [ "X$1" = 'Xmax' ]
X    then     AGGREG='max'
X    else    return 1
X    fi
X    [ "X$2" != "X(" -o "X$4" != "X)" ] && \
X        echo "Bad aggregate syntax" 1>&2 && syntax select && return 1
X    AGGFIELD="$3"
X    shift 4
X    lookup_field "$AGGFIELD"
X    [ "$?" -ne 0 ] && echo "Bad field name ($1)" 1>&2 && return 1
X    while [ $# -ne 0 ]
X    do
X        [ "X$1" = "Xwhere" ] && break;
X        [ "X$1" = "Xorder" ] && break;
X        [ "X$1" = "Xunion" ] && break;
X        shift
X    done
X
X    OUTFIELD=`( SUBSELECT="Y" ; AGGREGATE="Y"; \
X        select_ "select" "$AGGFIELD" "from" "$TABLE" "$@") | \
X        awk -F"    " \
X        'NR == 1 { min = $1; max = $1 }
X             { cnt += 1; total += $1 }
X        $1 < min { min = $1 }
X        $1 > max { max = $1 }
X        END     { printf "%s%s%s", "\"", '$AGGREG', "\"" }'`
X    if [ `expr "$RESULT" : '[^    ]*    \(.*\)'` -lt 10 ]
X    then    RESULT="$AGGFIELD    10"
X    fi
X    return 0
X}
X
X#
X#**************************************************************************
X# do_join
X#**************************************************************************
Xdo_join(){
X    update_view "$1"
X    TABLE="$1"
X    lookup_field "$2"
X    [ "$?" -ne 0 ] && echo "Bad view specifcation ($1.$2)" 1>&2 && return 1
X    JFIELD1="$OUTFIELDNUM"
X    JFIELD1L1="`expr $JFIELD1 - 1`"
X    update_view "$3"
X    TABLE="$3"
X    lookup_field "$4"
X    [ "$?" -ne 0 ] && echo "Bad view specifcation ($3.$4)" 1>&2 && return 1
X    JFIELD2="$OUTFIELDNUM"
X    JFIELD2L1="`expr $JFIELD2 - 1`"
X
X    ( grep "^$2    " $1@ ;
X      grep -v "^$2    " $1@ ;
X      grep -v "^$4    " $3@ ) > $5@
X    sort -t\     +$JFIELD2L1 $3~ > /tmp/$$
X    sort -t\     +$JFIELD1L1 $1~ | \
X        join -t\     -j1 $JFIELD1 -j2 $JFIELD2 \
X                        - /tmp/$$ > $5~
X}
X
X#
X#**************************************************************************
X# update_view
X#**************************************************************************
Xupdate_view(){
X    [ ! -f "$1%" ] && return 1
X    ( do_join `cat $1%` )
X}
X
X#
X#**************************************************************************
X# where
X#**************************************************************************
Xwhere(){
X    shift
X    while [ $# -gt 0 -a "$1" != "order" -a "$1" != "union" ]
X    do
X        if [ "X$1" = "Xselect" ]
X        then
X            set X `( SUBSELECT="Y" ;select_ "$@")`
X            if [ "$?" -eq 0 ]
X            then     shift
X            else     return 1
X            fi
X        fi
X        case "$1" in
X            and)     WHERE="$WHERE && ";;
X            or)    WHERE="$WHERE || ";;
X            not)    WHERE="$WHERE !" ;;
X            =)    WHERE="$WHERE == ";;
X            'in')     shift
X                set X `( SUBSELECT='Y';select_ "$@" )`
X                if [ "$?" -eq 0 ]
X                then     shift
X                else     return 1
X                fi
X                INWHERE=""
X                COMP="=="
X                LOGIC="||"
X                [ "X$LAST" = "Xnot" ] && COMP="=" && LOGIC="&&"
X                for VALUE
X                do
X                    [ "X$INWHERE" != "X" ] &&
X                        INWHERE="$INWHERE $LOGIC"
X                    INWHERE="$INWHERE ($WHERE$COMP $VALUE) "
X                done
X                WHERE="$INWHERE"
X                break;;
X            *)    lookup_field "$1"
X                WHERE="$WHERE $OUTFIELD";;
X        esac
X        LAST="$1"
X        shift
X    done
X    [ "$WHERE" ] && WHERE=" ( $WHERE ) " && return 0
X    echo "Missing 'where' clause" 1>&2
X    syntax where
X    return 1
X}
X
X#
X#**************************************************************************
X# help
X#**************************************************************************
Xhelp(){
X    if [ ! "$2" ]
X    then    echo "Ambiguous syntax, try:" 1>&2 ; syntax help
X    elif [ "$2" = "all" ]
X    then    ls *@ *% 2>/dev/null | cut -d@ -f1 | cut -d% -f1 | uniq
X    elif [ "$2" = "tables" ]
X    then    ls *@ *% 2>/dev/null | cut -d@ -f1 | cut -d% -f1 | uniq -u
X    elif [ "$2" = "views" ]
X    then    ls *% 2>/dev/null | cut -d% -f1
X    elif [ "$2" = "commands" ]
X    then    cat << "END"
X/p is print
X/g is go(execute)
X/q is quit
X/e is edit
X/i is include
X/w is write
X/r is reset(clear)
X/s is shell
X/p/g print and go
XThe number sign(#) may be used at the start of a line for comments.
XEND
X    else    syntax $2 && return
X        TABLE="$2"
X        update_view "$TABLE"
X        if [ -f "$2@" ]
X        then    echo "$NL <$2>" && cat "$2@"
X            [ -f "${2}%" ] &&echo $NOCR1 "$NL View:    $NOCR2" &&
X                set X `cat $2%` && shift &&
X                echo "$1.$2 = $3.$4"
X            echo "$NL Rows:    "`cat $TABLE~ | wc -l`
X        else     echo "$TABLE does not exist." 1>&2
X            syntax help
X        fi
X    fi
X}
X
X#
X#**************************************************************************
X# create
X#**************************************************************************
Xcreate(){
X    shift
X    if [ -f "$2@" -o -f "$2%" ]
X    then    echo "Table already exists." 1>&2
X    elif [ "X$1" = "Xview" -a $# -gt 2 ]
X    then    shift
X        if [ $# -ne 6 ]
X        then     syntax create
X        else
X            [ "X$2" != "X(" ] && echo "Bad syntax" 1>&2 &&
X                            syntax create && return
X            TABLE1="`expr $3 : '\([^\.]*\)'`"
X            FIELD1="`expr $3 : '[^\.]*.\(.*\)'`"
X            TABLE="$TABLE1"
X            lookup_field "$FIELD1"
X            [ "$?" -ne 0 ] && echo "Bad table or field name" 1>&2 &&
X                                    return
X            [ "X$4" != "X=" ] && echo "Bad syntax" 1>&2 &&
X                            syntax create && return
X            TABLE2="`expr $5 : '\([^\.]*\)'`"
X            FIELD2="`expr $5 : '[^\.]*.\(.*\)'`"
X            TABLE="$TABLE2"
X            lookup_field "$FIELD2"
X            [ "$?" -ne 0 ] && echo "Bad table or field name" 1>&2 &&
X                                    return
X            [ "X$2" != "X(" ] && echo "Bad syntax" 1>&2 &&
X                            syntax create && return
X            echo "$TABLE1 $FIELD1 $TABLE2 $FIELD2 $1" > $1%
X            update_view "$1"
X        fi
X        echo "OK"
X    elif [ "X$1" = "Xtable" -a $# -ge 5 ]
X    then
X        [ "X$3" != "X(" ] && echo "Bad syntax" 1>&2 &&
X                            syntax create && return
X        TABLE="$2"
X        shift 3
X        > $TABLE@
X        > $TABLE~
X        while [ $# -ge 2 ]
X        do
X            echo "$1    $2" >> $TABLE@
X            shift 2
X        done
X        [ "X$1" != "X)" ] && echo "Bad syntax" 1>&2 &&
X                    rm -f $TABLE@ && syntax create && return
X        echo "OK"
X    else
X        echo "Improper syntax ($1)" 1>&2
X        syntax create
X    fi
X    return
X}
X
X#
X#*************************************************************************
X# drop
X#**************************************************************************
Xdrop(){
X    [ "$2" != "table" -a "$2" != "view" ] &&
X        echo "Syntax error." 1>&2 && syntax drop && return
X    [ "$2" = "table" -a -f "$3%" ] &&
X        echo "Can not drop, $2 is a view, not a table" 1>&2 && return
X    [ "$2" = "view" -a ! -f "$3%" ] &&
X        echo "Can not drop, $2 is not a view" 1>&2 && return
X    if [ -f "$3@" -o -f "$3%" ]
X    then    rm -f $3@ $3~ $3%
X        echo "OK"
X    else      echo "No such table" 1>&2
X    fi
X}
X
X#
X#**************************************************************************
X# insert
X#**************************************************************************
Xinsert(){
X    shift
X    [ "X$1" != "Xinto" ] && echo "Improper syntax ($1)" 1>&2 &&
X        syntax insert && return
X    shift
X    TABLE="$1"
X    update_view "$TABLE" && echo "Can not insert into a view" 1>&2 && return
X    [ ! -f "$TABLE@" ] && echo "Table does not exist" 1>&2 && return
X    shift
X    ATTRIB="`cat $TABLE@ | wc -l`"
X    XASGN=""
X    XECHO="echo \""
X    if [ $# -gt 0 -a "X$1" = "X(" ]
X    then    ATTRIB2="0"
X        shift
X        while [ $# -gt 0 -a "X$1" != "X)" ]
X        do
X            lookup_field "$1"
X            [ "$?" -ne 0 ] && echo "Bad field name. ($1)" 1>&2 &&
X                                    return
X            XASGN="$XASGN X$OUTFIELDNUM=\`eval echo \$1\` ; shift;"
X            shift
X            ATTRIB2=`expr $ATTRIB2 + 1`
X        done
X        [ "X$1" != "X)" ] && echo "Syntax error ($1)" 1>&2 &&
X                        syntax insert && return
X        shift
X        POS="1"
X        while [ "$POS" -le "$ATTRIB" ]
X        do
X            eval X$POS=""
X            [ "$POS" != "1" ] && XECHO="$XECHO\$TAB"
X            XECHO="$XECHO\$X$POS"
X            POS=`expr $POS + 1`
X        done
X        XECHO="$XECHO\""
X        ATTRIB="$ATTRIB2"
X    fi
X    if [ "X$1" = "Xselect" ]
X    then     eval set X "`( SUBSELECT='Y' ; select_ "$@" )` \)"
X        shift
X    elif [ "X$1" != "Xvalues" -o "X$2" != 'X(' ]
X        then     echo "Improper syntax ($1)" 1>&2 && syntax insert &&
X                                    return
X    else    shift 2
X    fi
X    for LAST do
X    : ; done
X    [ "X$LAST" != "X)" ] &&
X        echo "Improper syntax" 1>&2 && syntax insert && return
X    if [ "`expr \( $# - 1 \) % $ATTRIB`" -ne 0 ]
X    then     echo "Incorrect number of values." 1>&2
X    else    ROWS="`expr \( $# - 1 \) / $ATTRIB`"
X        while [ $# -gt 1 ]
X        do
X            if [ "$XASGN" = "" ]
X            then
X                echo $NOCR1 "`eval echo $1`$NOCR2" >> $TABLE~
X                shift
X                while [ "`expr \( $# - 1 \) % $ATTRIB`" -ne 0 ]
X                do
X                    echo $NOCR1 "$TAB`eval echo $1`$NOCR2"\
X                                 >> $TABLE~
X                    shift
X                done
X                echo "" >> $TABLE~
X            else    eval $XASGN
X                eval $XECHO >> $TABLE~
X            fi
X        done
X        echo "($ROWS rows)"
X    fi
X}
X
X#
X#*************************************************************************
X# delete
X#**************************************************************************
Xdelete(){
X    TABLE="$3"
X    [ "X$2" != "Xfrom" ] && echo "Improper syntax ($2)" 1>&2 &&
X        syntax delete && return
X    update_view "$TABLE" && echo "You can not delete from a view." 1>&2 &&
X                                    return
X    [ ! -f "$TABLE@" ] && echo "$TABLE does not exist." 1>&2 && return
X    WHERE=""
X    if [ "X$4" = "Xwhere" ]
X    then     shift 3
X        where "$@" &&
X        awk -F"    " "! $WHERE { cnt += 1 ; print }
X            END { printf \"( %1d rows.)\\n\", (NR - cnt) \
X            >\"/tmp/$$row\" }" $TABLE~ > /tmp/$$ &&
X            mv /tmp/$$ $TABLE~ && cat /tmp/$$row
X    else    echo '('`cat $TABLE~ | wc -l`' rows)'
X        > $TABLE~
X    fi
X}
X
X#
X#*************************************************************************
X# update
X#**************************************************************************
Xupdate(){
X    TABLE="$2"
X    update_view "$TABLE" && echo "Can not update a view." 1>&2 && return
X    [ ! -f "$TABLE@" ] && echo "$TABLE does not exit." 1>&2 && return
X    [ "X$3" != "Xset" ] && echo "Improper syntax." 1>&2 && syntax update &&
X                                    return
X    shift 3
X    ASSIGN=""
X    while [ $# -gt 0 -a "X$1" != "Xwhere" ]
X    do
X        lookup_field "$1" && [ "X$2" = "X=" ] && ASSIGN="$ASSIGN ; "
X        ASSIGN="$ASSIGN $OUTFIELD"
X        shift
X    done
X    WHERE=""
X    if [ "X$1" = "Xwhere" ]
X    then     where "$@" || return
X    fi
X    awk -F"    " "BEGIN { OFS = \"    \" }
X        $WHERE     { $ASSIGN; cnt += 1 }
X            { print }
X        END     { printf \"( %1d rows)\\n\", cnt >\"/tmp/$$row\" }" \
X        $TABLE~ > /tmp/$$ &&
X            mv /tmp/$$ $TABLE~ && cat /tmp/$$row
X}
X
X#
X#**************************************************************************
X# select_
X#**************************************************************************
Xselect_(){
X[ "$DEBUG" = "Y" ] && set -x           # uncomment for debugging
X    UNION="Y"
X    while [ "$UNION" != "" ]
X    do
X        INAGG=""
X        FROM=""
X        UNION=""
X        TABLE=""
X        for ATABLE
X        do
X            [ "X$ATABLE" = "Xwhere" ] && break
X            [ "X$ATABLE" = "Xorder" ] && break
X            [ "X$ATABLE" = "Xunion" ] && break
X            [ "X$ATABLE" = "Xfrom" ] && FROM="Y" && continue
X            if [ "$FROM" ]
X            then
X            [ ! -f "$ATABLE@" ] && \
X            echo "$ATABLE does not exist." 1>&2 && return 1
X            if [ ! "$TABLE" ]
X            then     TABLE="$ATABLE"
X            else    JTABLE="$TABLE"
X                PREV=""
X                PPREV=""
X                FOUND=""
X                for GETJ
X                do
X                    if [ "$PREV" = "=" ]
X                    then
X                    TABLE="$JTABLE"
X                    lookup_field "$PPREV" &&
X                    TABLE="$ATABLE" &&
X                    lookup_field "$GETJ" &&
X                    FOUND="Y1" &&
X                    break
X                    TABLE="$ATABLE"
X                    lookup_field "$PPREV" &&
X                    TABLE="$JTABLE" &&
X                    lookup_field "$GETJ" &&
X                    FOUND="Y2" &&
X                    break
X                    fi
X                    PPREV="$PREV"
X                    PREV="$GETJ"
X                done
X                [ ! "$FOUND" ] &&
X                echo "Join not found, \c" &&
X                echo "try reordering tables." 1>&2 && return 1
X                if [ "$FOUND" = "Y1" ]
X                then
X    echo "$JTABLE    $PPREV    $ATABLE    $GETJ    /tmp/$$join2" >/tmp/$$join2%
X                else
X    echo "$ATABLE    $PPREV    $JTABLE    $GETJ    /tmp/$$join2" >/tmp/$$join2%
X                fi
X                update_view /tmp/$$join2
X                mv /tmp/$$join2~ /tmp/$$join~
X                mv /tmp/$$join2@ /tmp/$$join@
X                expr "$RESULT" : '[^:]:*\(.*\)' >>/tmp/$$join@
X                cut -d\     -f1 /tmp/$$join~ | \
X                    paste /tmp/$$join~ - >/tmp/$$
X                mv /tmp/$$ /tmp/$$join~
X                TABLE="/tmp/$$join"
X            fi
X            fi
X        done
X        [ ! "$FROM" ] && echo "Syntax error." 1>&2 && syntax select &&
X                                return 1
X        update_view "$TABLE"
X         shift
X        DISTINCT=""
X        [ "X$1" = "Xdistinct" ] && DISTINCT="Y" && shift
X        FIELDS=""
X        PRINTF=""
X        while [ "X$1" != "Xfrom" ]
X        do
X            if [ "X$1" = 'X*' ]
X            then    shift
X                set X `cat $TABLE@ | cut -d\     -f1` "$@"
X                shift
X            else    lookup_field "$1"
X                if [ "$?" -ne 0 ]
X                then     do_aggreg "$@"
X                    if [ "$?" -eq 0 ]
X                    then    INAGG="Y"
X                        shift 3
X                    else
X                      echo "Bad field name ($1)" 1>&2
X                      return 1
X                    fi
X                fi
X                [ "$FIELDS" ] && FIELDS="$FIELDS,"
X                FIELDS="$FIELDS $OUTFIELD"
X                if [ "$SUBSELECT" = "" ]
X                then     [ ! "$PRINTF" ] && PRINTF="|"
X                    WIDTH=`expr "$RESULT" : \
X                        '[^    ]*    \(.*\)'`
X                    PRINTF="$PRINTF%-$WIDTH.${WIDTH}s|"
X                else    if [ ! "$AGGREGATE" ]
X                    then    PRINTF="$PRINTF\\\"%s\\\" "
X                    else    PRINTF="$PRINTF%s\n"
X                    fi
X                fi
X                shift
X            fi
X        done
X        shift 2
X        WHERE=""
X        SORT=""
X        while [ $# -ne 0 ]
X        do
X            if [ "X$1" = "Xwhere" ]
X            then
X                where "$@"
X                [ "$?" -ne 0 ] && return 1
X                WHERE="$WHERE || NR == 1"
X                shift
X            elif [ "X$1" = "Xorder" ]
X            then     [ "X$2" != "Xby" ] &&
X                    echo "Syntax error ($2)" 1>&2 &&
X                    syntax select && return 1
X                shift 2
X                while [ $# -gt 0 -a "$1" != "union" ]
X                do
X                    if [     "X$1" != "Xasc" -a \
X                        "X$1" != "Xdesc" -a \
X                        "X$1" != "Xnum" ]
X                    then    lookup_field "$1"
X                        [ "$?" -ne 0 ] &&
X                echo "Bad field name ($1)" 1>&2 && return 1
X                        [ "$SORT" = "" ] &&
X                            SORT="sort -t\"    \" "
X                        SORTL="`expr $OUTFIELDNUM - 1`"
X                        SORT="$SORT +$SORTL"
X                        [ "X$2" = "Xnum" ] &&
X                            SORT="${SORT}n"
X                        [ "X$2" = "Xdesc" ] &&
X                            SORT="${SORT}r"
X                        [ "X$3" = "Xdesc" ] &&
X                            SORT="${SORT}r"
X                        SORT="$SORT -$OUTFIELDNUM"
X                    fi
X                    shift
X                done
X            elif [ "X$1" = "Xunion" ]
X            then    shift
X                UNION="Y"
X                break
X            else    shift
X            fi
X        done
X        [ "$INAGG" ] && WHERE="NR == 1"
X
X        if [ "$DISTINCT" != "" ]
X        then    if [ "$SORT" = "" ]
X            then    DIST="sort | uniq | tee /tmp/$$row"
X            else    DIST="uniq | tee /tmp/$$row"
X            fi
X        else    DIST="cat"
X        fi
X
X        TABLEFILE="$TABLE~"
X        [ "$SORT" != "" ] && cat $TABLE~ | eval "$SORT" > /tmp/$$ &&
X                            TABLEFILE="/tmp/$$"
X
X        if [ "$SUBSELECT" ]
X        then    awk -F"    " "$WHERE {printf \"$PRINTF\", $FIELDS }" \
X                            $TABLEFILE |eval "$DIST"
X        else    if [ ! "$QUIET" -o "$INAGG" = "Y" ]
X            then
X            ( set X `cut -d\     -f1 $TABLE@` ; shift
X              echo $NOCR1 "-$1-$NOCR2" ; shift
X                for HEADING
X              do
X                echo $NOCR1 "$TAB-$HEADING-$NOCR2"
X               done ; echo "" )
X            fi |
X            awk -F"    " \
X            "$WHERE { cnt += 1 ; printf \"$PRINTF\\n\", $FIELDS }
X            END    { printf \"( %1d rows)\\n\", (cnt - 1) \
X            >\"/tmp/$$row\" }" - $TABLEFILE | eval "$DIST" \
X                && if [ "$DISTINCT" = "" ]
X                then    cat /tmp/$$row
X                else     X=`expr \`cat /tmp/$$row|wc -l\` - 1`
X                    echo '('$X' rows)'
X                fi
X        fi
X    done
X    return 0
X}
X
X#
X#**************************************************************************
X# main
X#**************************************************************************
Xwhile :
Xdo
X    while :
X    do
X        echo $NOCR1 "* $NOCR2"
X        read LINE || exit
X        SQLPART="`expr "$LINE" : '\(..*\)/.$'`"
X        if [ "$SQLPART" != "" ]
X        then
X            [ "$NEW" = "Y" ] && _CMD=""
X            if [ "`expr "$LINE" : '.*/p/g$'`" -ne 0 ]
X            then
X                _CMD="$_CMD"`expr "$LINE" : '\(.*\)/p/g$'`"$NL"
X                LINE="/p/g"
X                NEW=""
X            else
X                _CMD="$_CMD""$SQLPART""$NL"
X                LINE="`expr "$LINE" : '.*\(/.\)$'`"
X                NEW=""
X            fi
X        fi
X         case "$LINE" in
X            /p|p)  echo "$_CMD";;
X            /g|g)  break;;
X            /p/g|pg) echo "$_CMD" ; break ;;
X            /r|r)  echo "reset" ; _CMD="";;
X            /s|s)  umask $UMASK ; $SHELL ; umask 0000;;
X            /e|e)  umask $UMASK ; echo "$_CMD" > /tmp/$$
X                $EDITOR /tmp/$$; _CMD="`cat /tmp/$$`"
X                umask 0000;;
X            /i|i)  echo $NOCR1 "Enter include file: $NOCR2"
X                read LINE
X                [ -f "$LINE" ] && _CMD="$_CMD`cat $LINE`$NL" &&
X                echo "$LINE included";;
X            /w|w)  echo $NOCR1 "Enter output file: $NOCR2"
X                read LINE
X                [ "$LINE" ] && umask $UMASK &&
X                echo "$_CMD" > "$LINE" && umask 0000 &&
X                echo "$LINE written";;
X            /q|q)  exit 0;;
X            \#*)     [ "$NEW" = "Y" ] && _CMD="" ;;
X            *)      [ "$NEW" = "Y" ] && _CMD=""
X                    _CMD="$_CMD$LINE$NL";;
X        esac
X        NEW=""
X    done
X
X    CMD=`echo "$_CMD" | sed \
X        -e "s/\'/\"/g" \
X        -e 's/\"\([^\"]*\)\"/\"\\\"\1\\\"\"/g' \
X        -e 's/\([<>!=][<>!=]*\)/ \1 /g' \
X        -e 's/</\\\</g' \
X        -e 's/>/\\\>/g' \
X        -e 's/\*/\\\*/g' \
X        -e 's/(/ \\\( /g' \
X        -e 's/)/ \\\) /g'`
X    [ ! "$CMD" ] && continue
X    IFS="$_IFS,"
X    eval set X $CMD
X    shift
X    IFS="$_IFS"
X    NEW="Y"
X    case $1 in
X        select)     select_ "$@";;
X        create)     create "$@";;
X        delete)     delete "$@";;
X        drop)         drop "$@";;
X        insert)     insert "$@";;
X        update)     update "$@";;
X        edit)        [ "$2" ] && $EDITOR $2@;;
X        help)        help "$@";;
X        print)        select_ "select" '*' "from" "$2";;
X        *)         echo "Missing or unrecognized command." 1>&2 ;;
X    esac
Xdone
X
END_OF_FILE
  if test 20449 -ne `wc -c <'shql'`; then
    echo shar: \"'shql'\" unpacked with wrong size!
  fi
  chmod +x 'shql'
  # end of 'shql'
fi
echo shar: End of archive 1 \(of 1\).
cp /dev/null ark1isdone
MISSING=""
for I in 1 ; do
    if test ! -f ark${I}isdone ; then
    MISSING="${MISSING} ${I}"
    fi
done
if test "${MISSING}" = "" ; then
    echo You have the archive.
    rm -f ark[1-9]isdone
else
    echo You still must unpack the following archives:
    echo "        " ${MISSING}
fi
exit 0
exit 0 # Just in case...