#!/bin/bash
# use /bin/bash
#
# shql - version 1.1
# Bruce Momjian, root@candle.uucp (root%candle.uucp@bts.com)
#

# DEFINE THESE
SHQL_ROOT="/home/gsql/database"		# system-wide database location
EDITOR="${EDITOR:=/usr/bin/vi}" # default editor if EDITOR not defined
SHELL="${SHELL:=/bin/sh}" 	# default editor if EDITOR not defined

if [ ! "x$BASH" = "x" ]
then
	enable -n help
	set -h			# remember functions
	export FS="	"
fi

# Unix table file postfixes:  @ is attrib, ~ is data, % is view

DEBUG="N"	# set to Y for debugging

if [ "X$1" = "X-d" ] 
then	 DEBUG="Y"
	shift
fi

[ "$DEBUG" = "Y" ] && set -x   		# uncomment for debugging
#set -v
UMASK=`umask`
umask 0000		# share database

if [ "X$1" = "X-q" ] 
then	QUIET="Y"
	shift
fi

[ `basename $0` != "ishql" ] && {
	tty >/dev/null || {
		QUIET="Y"
		}
	}

if [ ! "$QUIET" ]
then
	trap "echo \"Goodbye\" ; \
	    rm -f /tmp/$$ /tmp/$$.shql /tmp/$$row /tmp/$$sel /tmp/$$join*" 0 1 2 3 15
else
	trap "rm -f /tmp/$$ /tmp/$$row /tmp/$$sel /tmp/$$join*" 0 1 2 3 15
	trap "rm -f /tmp/$$ /tmp/$$.shql /tmp/$$row /tmp/$$sel /tmp/$$join*" 0 1 2 3 15
fi

if echo '\c' | grep -s c ; then		# to adapt to System V vs. BSD 'echo'
	NOCR1='-n'			# BSD
	NOCR2=""
else
	NOCR1=""			# System V
	NOCR2='\c'
fi
NL='
'
TAB='	'
export _IFS TABLE CMD NOCR1 NOCR2 NL TAB
_IFS="$IFS"

if [ "X$1" = "X" ] && [ ! "$QUIET" ]
then	echo "Missing database name." 1>&2
	echo "The database name must be a directory under $HOME/shql" 1>&2
	echo "	or a directory under $SHQL_ROOT" 1>&2
fi

database() {
	shift 

	if [ ! "$QUIET" ]
	then
		echo "Database: $1"
	fi

	if [ -d $HOME/shql/$1 ]
	then	cd $HOME/shql/$1
	elif [ -d $SHQL_ROOT/$1 ]
	then	cd $SHQL_ROOT/$1
	elif [ -d $HOME/$1 ]
	then	cd $HOME/$1
	elif [ -d $1 ]
	then	cd $1
	else 	echo "Unknown database ($1)" 1>&2
		echo "The database name must be a directory under $HOME/shql" 1>&2
		echo "	or a directory under $SHQL_ROOT" 1>&2
	fi
}

[ $# != 0 ] && database X $1

#
#**************************************************************************
# syntax
#**************************************************************************
syntax(){
	case "$1" in
		create)	cat <<"END"
CREATE TABLE table_name (
	column_name column_width 
	{, ...} 
) 	
or
CREATE VIEW view_name (
	table_or_view1.column1 = table_or_view2.column2
)
END
return 0
;;
		delete) cat <<"END"
DELETE 
FROM table_name	
{ WHERE where_clause }
END
return 0
;;
		drop) cat <<"END"
DROP TABLE table_name
or
DROP VIEW view_name
END
return 0
;;
		edit) cat <<"END"
EDIT table_name
is a non-standard method of changing a table's field names or display widths.
END
return 0
;;
		help)	cat <<"END"
HELP ALL
or
HELP TABLES 
or
HELP VIEWS
or
HELP COMMANDS
or
HELP [CREATE | DELETE | DROP | INSERT | SELECT | UPDATE | WHERE | PRINT | EDIT]
or
HELP table_name
Commands must appear in lower case.
END
return 0
;;
		insert) cat <<"END"
INSERT INTO table_name 
	{ ( column_name, ... ) }
VALUES ( expression, ...)
or		
INSERT INTO table_name 
	{ ( column_name, ... ) }
subselect
END
return 0
;;
		print) cat <<"END"
PRINT table_name 
is a non-standard synonym for SELECT * FROM table_name.
END
return 0
;;
		select) cat <<"END"
SELECT { DISTINCT } 
	[ column_name {,...} | * ]
FROM [ table_name | view_name ]
{ WHERE where_clause }
{ ORDER BY column_name { NUM } { ASC | DESC } {, ... }
{ UNION select statement }
'NUM' is a non-standard method for sorting numeric fields.
END
return 0
;;
		update) cat <<"END"
UPDATE table_name
SET column_name = expression {, ... }
{ WHERE where_clause }
END
return 0
;;
		where) cat <<"END"
WHERE [ column_name | value ] [ =, !=, >, <, >=, <=, and, or, not, in, like ]
      [ column_name | value | subselect ]
Parentheses may be used to group expressions.  
END
return 0
;;
		syntax)	syntax commands; echo 1>&2
			syntax create; echo 1>&2
			syntax delete; echo 1>&2
			syntax drop; echo 1>&2
			syntax insert; echo 1>&2
			syntax select; echo 1>&2
			syntax update; echo 1>&2
			syntax where; echo 1>&2
			syntax print; echo 1>&2
			syntax edit; echo 1>&2
			return 0
			;;
	esac
	return 1
}

#
#**************************************************************************
# lookup_field
#**************************************************************************
lookup_field(){
	if [ ! -f $TABLE% ]
	then	RESULT="`grep -n \"^$1	\" $TABLE@`"
	else 	RESULT="`grep -n \"^$1	\" $TABLE@ | sed 1q`"
	fi
	if [ ! "$RESULT" ] 
	then 	OUTFIELD="$1"
		return 1
	else	OUTFIELDNUM="`expr "$RESULT" : '\([^:]*\)'`"
		OUTFIELD="\$$OUTFIELDNUM" 
		return 0
	fi
}

#
#**************************************************************************
# do_aggreg
#**************************************************************************
do_aggreg(){
	if 	[ "X$1" = 'Xsum' ]
	then	AGGREG='total'
	elif 	[ "X$1" = 'Xavg' ]
	then 	AGGREG='(total/cnt)'
	elif 	[ "X$1" = 'Xcount' ]
	then 	AGGREG='cnt'
	elif 	[ "X$1" = 'Xmin' ]
	then 	AGGREG='min'
	elif 	[ "X$1" = 'Xmax' ]
	then 	AGGREG='max'
	else	return 1
	fi
	[ "X$2" != "X(" -o "X$4" != "X)" ] && \
		echo "Bad aggregate syntax" 1>&2 && syntax select && return 1
	AGGFIELD="$3"
	shift 4
	lookup_field "$AGGFIELD"
	[ "$?" -ne 0 ] && echo "Bad field name ($1)" 1>&2 && return 1
	while [ $# -ne 0 ]
	do	
		[ "X$1" = "Xwhere" ] && break;
		[ "X$1" = "Xorder" ] && break;
		[ "X$1" = "Xunion" ] && break;
		shift
	done

	OUTFIELD=`( SUBSELECT="Y" ; AGGREGATE="Y"; \
	    select_ "select" "$AGGFIELD" "from" "$TABLE" "$@") | \
	    awk -F"	" \
		'NR == 1 { min = $1; max = $1 }
			 { cnt += 1; total += $1 }
		$1 < min { min = $1 }
		$1 > max { max = $1 }
		END	 { printf "%s%s%s", "\"", '$AGGREG', "\"" }'`
	if [ `expr "$RESULT" : '[^	]*	\(.*\)'` -lt 10 ]
	then	RESULT="$AGGFIELD	10"
	fi
	return 0
}

#
#**************************************************************************
# do_join 
#**************************************************************************
do_join(){
	update_view "$1"
	update_view "$3"

	TABLE="$1"
	lookup_field "$2" 
	[ "$?" -ne 0 ] && echo "Bad view specifcation ($1.$2)" 1>&2 && return 1
	JFIELD1="$OUTFIELDNUM"
	JFIELD1L1="`expr $JFIELD1 - 1`"

	TABLE="$3"
	lookup_field "$4" 
	[ "$?" -ne 0 ] && echo "Bad view specifcation ($3.$4)" 1>&2 && return 1
	JFIELD2="$OUTFIELDNUM"
	JFIELD2L1="`expr $JFIELD2 - 1`"

	[ ! -f $5~ -o $1~ -nt $5~ -o $3~ -nt $5~ ] && {
		( grep "^$2	" $1@ ;
		  grep -v "^$2	" $1@ ;
		  grep -v "^$4	" $3@ ) > $5@

		sort -t\	 +$JFIELD2L1 $3~ > /tmp/$$
		sort -t\	 +$JFIELD1L1 $1~ | \
			join -t\	 -j1 $JFIELD1 -j2 $JFIELD2 \
						- /tmp/$$ > $5~
	}
}

#
#**************************************************************************
# update_view
#**************************************************************************
update_view(){
	[ ! -f "$1%" ] && return 1
	( do_join `cat $1%` )
}

#
#**************************************************************************
# where
#**************************************************************************
where(){
	shift
	while [ $# -gt 0 -a "$1" != "order" -a "$1" != "union" ]
	do
		if [ "X$1" = "Xselect" ]
		then
#			set X `( SUBSELECT="Y" ;select_ "$@")`
#			if [ "$?" -eq 0 ]
#			then 	shift
#			else 	return 1
#			fi
			( SUBSELECT="Y" ;select_ "$@" )
			[ ! "$?" -eq 0 ] && return 1
			read tmp </tmp/$$sel || return 1
			set X "$tmp"
		fi
		case "$1" in
			and) 	WHERE="$WHERE && ";;
			or)	WHERE="$WHERE || ";;
			not)	WHERE="$WHERE !"
				if [ "x$2" = xlike ]
				then
					WHERE="$WHERE~ "
					shift
				fi
				;;
			=)	WHERE="$WHERE == ";;
			like)	WHERE="$WHERE ~ ";;
			'in') 	shift
#                               set X `( SUBSELECT='Y';select_ "$@" )`
				( SUBSELECT='Y';select_ "$@" )
				[ ! "$?" -eq 0 ] && return 1
#				if [ "$?" -eq 0 ]
#				then 	shift
#				else 	return 1
#				fi
				INWHERE=""
				COMP="=="
				LOGIC="||"
				[ "X$LAST" = "Xnot" ] && COMP="=" && LOGIC="&&"
#				for VALUE
				while read VALUE
				do
					[ "X$INWHERE" != "X" ] && 
						INWHERE="$INWHERE $LOGIC"
					INWHERE="$INWHERE ($WHERE$COMP $VALUE) "
				done </tmp/$$sel
				WHERE="$INWHERE"
				break;;
			*)	lookup_field "$1"
				WHERE="$WHERE $OUTFIELD";;
		esac
		LAST="$1"
		shift
	done 
#	echo " ( $WHERE ) "
	[ "$WHERE" ] && WHERE=" ( $WHERE ) " && return 0
	echo "Missing 'where' clause" 1>&2
	syntax where
	return 1
}

#
#**************************************************************************
# help
#**************************************************************************
help(){
#	[ "$QUIET" ] && return 0
	if [ ! "$2" ]
	then	echo "Ambiguous syntax, try:" 1>&2 ; syntax help
	elif [ "$2" = "all" ]
	then	for x in *@
		do
			TABLE=`basename $x @`
			update_view "$TABLE"
			if [ -f $TABLE% ]
			then
				echo View: `basename $x @` 1>&2
				set X `cat $TABLE%` && shift &&
				echo "      $1.$2 = $3.$4" 1>&2
			else
				echo Table: $TABLE 1>&2
			fi
			echo 1>&2
			cat $x 1>&2
			echo "$NL Rows:	"`cat $TABLE~ | wc -l` 1>&2
			echo 1>&2
		done
	elif [ "$2" = "tables" ] 
	then	ls *@ *% 2>/dev/null | cut -d@ -f1 | cut -d% -f1 | uniq -u 
	elif [ "$2" = "views" ] 
	then	ls *% 2>/dev/null | cut -d% -f1 
	elif [ "$2" = "commands" ]
	then	cat << "END"
/p is print
/g is go(execute)
/q is quit
/e is edit
/i is include
/w is write
/r is reset(clear)
/s is shell
/p/g print and go
The number sign(#) may be used at the start of a line for comments.
END
	else	syntax $2 && return
		TABLE="$2"
		update_view "$TABLE"
		if [ -f "$2@" ] 
		then	echo "$NL <$2>" 1>&2 && cat "$2@" 1>&2
			[ -f "${2}%" ] && echo $NOCR1 "$NL View:	$NOCR2" 1>&2 && 
				set X `cat $2%` && shift &&
				echo "$1.$2 = $3.$4" 1>&2
			echo "$NL Rows:	"`cat $TABLE~ | wc -l` 1>&2
		else 	echo "$TABLE does not exist." 1>&2
			syntax help
		fi
	fi
}

#
#**************************************************************************
# repair
#**************************************************************************

repair()
{	if [ ! "$2" ] 
	then
		syntax repair
		return 0
	fi

	shift
	for X in $*
	do
		if [ ! -f $X~ ]
		then
			echo Table $X does not exist 1>&2
		else
			[ ! "$QUIET" ] && echo Repairing $X~ 1>&2
			sed 's/ *	/	/' <$X~ | sed 's/ *$//' >/tmp/$$repair
			cat /tmp/$$repair >$X~
			rm /tmp/$$repair
		fi
	done
	}
#
#**************************************************************************
# create
#**************************************************************************
create(){
	shift
	if [ -f "$2@" -o -f "$2%" ]
	then	echo "Table already exists." 1>&2
	elif [ "X$1" = "Xview" -a $# -gt 2 ]
	then	shift
		if [ $# -ne 6 ]
		then 	syntax create
		else 	
			[ "X$2" != "X(" ] && 
				echo "Bad syntax" 1>&2 && 
				syntax create && return
			TABLE1="`expr $3 : '\([^\.]*\)'`"
			FIELD1="`expr $3 : '[^\.]*.\(.*\)'`"
			TABLE="$TABLE1"
			lookup_field "$FIELD1" 
			[ "$?" -ne 0 ] && 
				echo "Bad table or field name" 1>&2 &&
				return
			[ "X$4" != "X=" ] &&
				echo "Bad syntax" 1>&2 && 
				syntax create && return
			TABLE2="`expr $5 : '\([^\.]*\)'`"
			FIELD2="`expr $5 : '[^\.]*.\(.*\)'`"
			TABLE="$TABLE2"
			lookup_field "$FIELD2" 
			[ "$?" -ne 0 ] && 
				echo "Bad table or field name" 1>&2 &&
				return
			[ "X$2" != "X(" ] &&
				echo "Bad syntax" 1>&2 && 
				syntax create && return
			echo "$TABLE1 $FIELD1 $TABLE2 $FIELD2 $1" > $1%
			update_view "$1"			
		fi
		[ ! "$QUIET" ] && echo "OK" 1>&2
	elif [ "X$1" = "Xtable" -a $# -ge 5 ] 
	then
		[ "X$3" != "X(" ] && 
			echo "Bad syntax" 1>&2 && 
			syntax create && return
		TABLE="$2"
		shift 3
		> $TABLE@
		> $TABLE~
		while [ $# -ge 2 ]
		do
			echo "$1	$2" >> $TABLE@
			shift 2
		done
		[ "X$1" != "X)" ] && 
			echo "Bad syntax" 1>&2 && 
			rm -f $TABLE@ && syntax create && return
		[ ! "$QUIET" ] && 
			echo "OK" 1>&2
	else 
		echo "Improper syntax ($1)" 1>&2
		syntax create
	fi
	return
}

#
#*************************************************************************
# drop
#**************************************************************************
drop(){
	[ "$2" != "table" -a "$2" != "view" ] && 
		echo "Syntax error." 1>&2 && syntax drop && return
	[ "$2" = "table" -a -f "$3%" ] &&
		echo "Can not drop, $2 is a view, not a table" 1>&2 && return
	[ "$2" = "view" -a ! -f "$3%" ] &&
		echo "Can not drop, $2 is not a view" 1>&2 && return
	if [ -f "$3@" -o -f "$3%" ] 
	then	rm -f $3@ $3~ $3%
		[ ! "$QUIET" ] && echo "OK" 1>&2
	else  	echo "No such table" 1>&2
	fi
}

#
#**************************************************************************
# insert
#**************************************************************************
insert(){
	shift
	[ "X$1" != "Xinto" ] && echo "Improper syntax ($1)" 1>&2 && 
		syntax insert && return
	shift
	TABLE="$1"
	update_view "$TABLE" && echo "Can not insert into a view" 1>&2 && return
	[ ! -f "$TABLE@" ] && echo "Table does not exist" 1>&2 && return
	shift
	ATTRIB="`cat $TABLE@ | wc -l`"
	XASGN=""
	XECHO="echo \""
	if [ $# -gt 0 -a "X$1" = "X(" ]
	then	ATTRIB2="0"
		shift
		while [ $# -gt 0 -a "X$1" != "X)" ]
		do
			lookup_field "$1" 
			[ "$?" -ne 0 ] && echo "Bad field name. ($1)" 1>&2 && 
									return 
			XASGN="$XASGN X$OUTFIELDNUM=\`eval echo \\\"\$1\\\"\` ; shift;"
			shift
			ATTRIB2=`expr $ATTRIB2 + 1`
		done
		[ "X$1" != "X)" ] && echo "Syntax error ($1)" 1>&2 && 
						syntax insert && return
		shift
		POS="1"
		while [ "$POS" -le "$ATTRIB" ]
		do
			eval X$POS=""
			[ "$POS" != "1" ] && XECHO="$XECHO\$TAB"
			XECHO="$XECHO\"\$X$POS\""
			POS=`expr $POS + 1`
		done
		XECHO="$XECHO\""
		ATTRIB="$ATTRIB2"
	fi	
	if [ "X$1" = "Xselect" ]
	then 
#		eval set X "`( SUBSELECT='Y' ; select_ "$@" )` \)"
#		shift
		( SUBSELECT='I' ; select_ "$@" )
		[ ! "$?" -eq 0 ] && return
		if [ "$XASGN" = "" ]
		then 	cat /tmp/$$sel >> $TABLE~
		else 	# set -x
			OIFS="$IFS"
			IFS=''
			while read line
			do
				IFS='	'
				set X $line
				shift
				eval $XASGN
				eval $XECHO >>$TABLE~
			done </tmp/$$sel
			IFS="$OIFS"
			# set +x
		fi
		X=`cat /tmp/$$sel | wc -l | sed "s/^ *//"`
		echo "$X rows inserted." >&2
	elif [ "X$1" != "Xvalues" -o "X$2" != 'X(' ]  
	then	
		echo "Improper syntax ($1)" 1>&2
		syntax insert
		return
	else
	   shift 2
	   for LAST do 
	   : ; done
	   [ "X$LAST" != "X)" ] && 
		echo "Improper syntax" 1>&2 && syntax insert && return
	   if [ "`expr \( $# - 1 \) % $ATTRIB`" -ne 0 ]
	   then 	echo "Incorrect number of values." 1>&2
	   else	ROWS="`expr \( $# - 1 \) / $ATTRIB`"
		while [ $# -gt 1 ]
		do	
			if [ "$XASGN" = "" ]
			then 	
				echo $NOCR1 "`eval echo $1`$NOCR2" >> $TABLE~ 
				shift
				while [ "`expr \( $# - 1 \) % $ATTRIB`" -ne 0 ]
				do
					echo $NOCR1 "$TAB`eval echo $1`$NOCR2"\
							 	>> $TABLE~
					shift
				done
				echo "" >> $TABLE~
			else	eval $XASGN
				eval $XECHO >> $TABLE~
			fi
		done
		echo "$ROWS rows inserted." >&2
	   fi
	fi
}

#
#*************************************************************************
# delete
#**************************************************************************
delete(){
	TABLE="$3"
	[ "X$2" != "Xfrom" ] && echo "Improper syntax ($2)" 1>&2 && 
		syntax delete && return
	update_view "$TABLE" && echo "You can not delete from a view." 1>&2 &&
									return  
	[ ! -f "$TABLE@" ] && echo "$TABLE does not exist." 1>&2 && return
	WHERE=""
	if [ "X$4" = "Xwhere" ]
	then 	shift 3
		where "$@" && 
		awk -F"	" "! $WHERE { cnt += 1 ; print } 
			END { printf \"%1d rows deleted.\\n\", (NR - cnt) \
			>\"/tmp/$$row\" }" $TABLE~ > /tmp/$$ && 
			mv /tmp/$$ $TABLE~ && cat /tmp/$$row >&2
	else	echo `cat $TABLE~ | wc -l`' rows deleted.' >&2
		> $TABLE~
	fi
}

#
#*************************************************************************
# update
#**************************************************************************
update(){
	TABLE="$2"
	update_view "$TABLE" && echo "Can not update a view." 1>&2 && return
	[ ! -f "$TABLE@" ] && echo "$TABLE does not exit." 1>&2 && return
	[ "X$3" != "Xset" ] && echo "Improper syntax." 1>&2 && syntax update && 
									return
	shift 3
	ASSIGN=""
	while [ $# -gt 0 -a "X$1" != "Xwhere" ]
	do
		lookup_field "$1" && [ "X$2" = "X=" ] && ASSIGN="$ASSIGN ; "
		ASSIGN="$ASSIGN $OUTFIELD"
		shift
	done
	WHERE=""
	if [ "X$1" = "Xwhere" ] 
	then 	where "$@" || return 
	fi
	awk -F"	" "BEGIN { OFS = \"	\" }
		$WHERE 	{ $ASSIGN; cnt += 1 }
			{ print } 
		END 	{ printf \"%1d rows updated.\\n\", cnt >\"/tmp/$$row\" }" \
		$TABLE~ > /tmp/$$ && 
			mv /tmp/$$ $TABLE~ && cat /tmp/$$row >&2
}

#
#**************************************************************************
# select_
#**************************************************************************
select_(){
[ "$DEBUG" = "Y" ] && set -x   		# uncomment for debugging
	UNION="Y"
	while [ "$UNION" != "" ]
	do
		INAGG=""
		FROM=""
		UNION=""
		TABLE=""
		for ATABLE
		do
		    [ "X$ATABLE" = "Xwhere" ] && break
		    [ "X$ATABLE" = "Xorder" ] && break
		    [ "X$ATABLE" = "Xunion" ] && break
		    [ "X$ATABLE" = "Xfrom" ] && FROM="Y" && continue
		    if [ "$FROM" ]
		    then
			[ ! -f "$ATABLE@" ] && \
			echo "$ATABLE does not exist." 1>&2 && return 1
			if [ ! "$TABLE" ]
			then 	TABLE="$ATABLE"
			else	JTABLE="$TABLE"
				PREV=""
				PPREV=""
				FOUND=""
				for GETJ
				do
				    if [ "$PREV" = "=" ]
				    then
					TABLE="$JTABLE"
					lookup_field "$PPREV" &&
					TABLE="$ATABLE" &&
					lookup_field "$GETJ" &&
					FOUND="Y1" &&
					break
					TABLE="$ATABLE"
					lookup_field "$PPREV" &&
					TABLE="$JTABLE" &&
					lookup_field "$GETJ" &&
					FOUND="Y2" &&
					break
				    fi
				    PPREV="$PREV"
				    PREV="$GETJ"
				done
				[ ! "$FOUND" ] &&
				echo "Join not found, \c" 1>&2 && 
				echo "try reordering tables." 1>&2 && return 1
				if [ "$FOUND" = "Y1" ]
				then
	echo "$JTABLE	$PPREV	$ATABLE	$GETJ	/tmp/$$join2" >/tmp/$$join2%
				else
	echo "$ATABLE	$PPREV	$JTABLE	$GETJ	/tmp/$$join2" >/tmp/$$join2%
				fi
				update_view /tmp/$$join2
				mv /tmp/$$join2~ /tmp/$$join~
				mv /tmp/$$join2@ /tmp/$$join@
				expr "$RESULT" : '[^:]:*\(.*\)' >>/tmp/$$join@
				cut -d\	 -f1 /tmp/$$join~ | \
					paste /tmp/$$join~ - >/tmp/$$
				mv /tmp/$$ /tmp/$$join~
				TABLE="/tmp/$$join"
			fi
		    fi
		done
		[ ! "$FROM" ] && echo "Syntax error." 1>&2 && syntax select &&
								return 1
		update_view "$TABLE"
 		shift
		DISTINCT=""
		[ "X$1" = "Xdistinct" ] && DISTINCT="Y" && shift
		FIELDS=""
		PRINTF=""
		while [ "X$1" != "Xfrom" ]
		do
			if [ "X$1" = 'X*' ]
			then	shift
				set X `cat $TABLE@ | cut -d\	 -f1` "$@" 
				shift
			else	lookup_field "$1"
				if [ "$?" -ne 0 ]
				then 	do_aggreg "$@"
					if [ "$?" -eq 0 ]
					then	INAGG="Y"
						shift 3
					else 
					  echo "Bad field name ($1)" 1>&2
					  return 1
					fi
				fi
				[ "$FIELDS" ] && FIELDS="$FIELDS,"
				FIELDS="$FIELDS $OUTFIELD"
				if [ "$SUBSELECT" = "" ]
				then if [ ! "$QUIET" ]
				     then
					    [ ! "$PRINTF" ] && PRINTF="|"
					    WIDTH=`expr "$RESULT" : \
						'[^	]*	\(.*\)'`
					    PRINTF="$PRINTF%-$WIDTH.${WIDTH}s|"
				     else
					    [  "$PRINTF" ] && PRINTF="$PRINTF|"
					    WIDTH=`expr "$RESULT" : \
						'[^	]*	\(.*\)'`
					    PRINTF="$PRINTF%-$WIDTH.${WIDTH}s"
				     fi
				else
					if [ ! "$AGGREGATE" ]
					then	
					    [  "$PRINTF" ] && PRINTF="$PRINTF	"
					    PRINTF="$PRINTF%s"
					else	PRINTF="$PRINTF%s\n"
					fi
				fi
				shift
			fi
		done
		shift 2
		WHERE=""
		SORT=""
		while [ $# -ne 0 ]
		do	
			if [ "X$1" = "Xwhere" ]
			then
				where "$@"
				[ "$?" -ne 0 ] && return 1 
#				[ ! $SUBSELECT ] && [ ! "$QUIET" ] \
#					&& WHERE="$WHERE || NR == 1"
				shift
			elif [ "X$1" = "Xorder" ]
			then 	[ "X$2" != "Xby" ] && 
					echo "Syntax error ($2)" 1>&2 && 
					syntax select && return 1
				shift 2
				while [ $# -gt 0 -a "$1" != "union" ]
				do
					if [ 	"X$1" != "Xasc" -a \
						"X$1" != "Xdesc" -a \
						"X$1" != "Xnum" ] 
					then	lookup_field "$1" 
						[ "$?" -ne 0 ] &&
				echo "Bad field name ($1)" 1>&2 && return 1 
						[ "$SORT" = "" ] && 
							SORT="sort -t\"	\" "
						SORTL="`expr $OUTFIELDNUM - 1`"
						SORT="$SORT +$SORTL"
						[ "X$2" = "Xnum" ] && 
							SORT="${SORT}n"
						[ "X$2" = "Xdesc" ] && 
							SORT="${SORT}r"
						[ "X$3" = "Xdesc" ] && 
							SORT="${SORT}r"
						SORT="$SORT -$OUTFIELDNUM"
					fi
					shift
				done
			elif [ "X$1" = "Xunion" ]
			then	shift
				UNION="Y"
				break
			else	shift
			fi
		done
		[ "$INAGG" ] && WHERE="NR == 1"

		if [ "$DISTINCT" != "" ] 
		then	if [ "$SORT" = "" ]
			then	DIST="sort | uniq | tee /tmp/$$row"
			else	DIST="uniq | tee /tmp/$$row"
			fi
		else	DIST="cat"
		fi					

		TABLEFILE="$TABLE~"
		[ "$SORT" != "" ] && cat $TABLE~ | eval "$SORT" > /tmp/$$ &&
							TABLEFILE="/tmp/$$"

		if [ "$AGGREGATE" ]
		then	awk -F"	" "$WHERE {printf \"$PRINTF\", $FIELDS }" \
							$TABLEFILE |eval "$DIST"
		elif [ "X$SUBSELECT" = "XI" ]
		then
			awk -F"	" "$WHERE {printf \"$PRINTF\\n\", $FIELDS }" \
				$TABLEFILE | eval "$DIST" >/tmp/$$sel
		elif [ "$SUBSELECT" ]
		then
			awk -F"	" "$WHERE {printf \"$PRINTF\\n\", $FIELDS }" \
				$TABLEFILE | eval "$DIST" \
				| tr "	" "\\n" >/tmp/$$sel
		else	if [ ! "$QUIET" ] # -o "$INAGG" = "Y" ]
			then 
			( set X `cut -d\	 -f1 $TABLE@` ; shift 
			  echo $NOCR1 "-$1-$NOCR2" ; shift 
		  	  for HEADING
			  do 
				echo $NOCR1 "$TAB-$HEADING-$NOCR2" 
	 		  done ; echo "" ) | awk -F"	" \
			"{ cnt += 1 ; printf \"$PRINTF\\n\", $FIELDS }"
			fi
			awk -F"	" \
			"$WHERE { cnt += 1 ; printf \"$PRINTF\\n\", $FIELDS }
			END	{ printf \"%1d rows selected.\\n\", (cnt) \
			>\"/tmp/$$row\" }" $TABLEFILE | eval "$DIST" \
				&& if [ "$DISTINCT" = "" ]
				then	cat /tmp/$$row >&2
				else 	X=`expr \`cat /tmp/$$row|wc -l\` - 1`
					echo "$X rows selected." >&2
				fi
		fi
	done
	return 0
}	

unload()
{	shift
	if [ $1 = "to" ]
	then
		shift
	fi
	F=$1
	shift
	(SUBSELECT="I"; select_ "$@")
	if [ -f /tmp/$$sel ]
	then
		X=`cat /tmp/$$sel|wc -l`
		echo "$X rows unloaded." >&2
		mv /tmp/$$sel $F
	else
		echo "0 rows unloaded." >&2
	fi
}

#
#**************************************************************************
# main
#**************************************************************************
while :
do
	while :
	do
		if [ ! "$QUIET" ]
		then
			echo $NOCR1 "* $NOCR2"
		fi
		read LINE || exit 

#		LINE=`echo "$LINE" | sed "s/
$//"`

		echo "$LINE" >> /tmp/$$.shql
#		SQLPART="`expr \"$LINE\" : '\(..*\)/.$'`"
#		if [ "$SQLPART" != "" ]
#		then
#			[ "$NEW" = "Y" ] && _CMD=""
#			if [ "`expr \"$LINE\" : '.*/p/g$'`" -ne 0 ]
#			then
#				_CMD="$_CMD"`expr \"$LINE\" : '\(.*\)/p/g$'`"$NL"
#				LINE="/p/g"
#				NEW=""
#			else
#				_CMD="$_CMD""$SQLPART""$NL"
#				LINE="`expr \"$LINE\" : '.*\(/.\)$'`"
#				NEW=""
#			fi
#		fi
 		case "$LINE" in 
			/p|p)  echo "$_CMD";;
			""|/g|g)  break;;
			/p/g|pg) echo "$_CMD" ; break ;;
			/r|r)  echo "reset" ; _CMD="";;
			/s|s)  umask $UMASK ; $SHELL ; umask 0000;;
			/e|e)  umask $UMASK ; echo "$_CMD" > /tmp/$$
				$EDITOR /tmp/$$; _CMD="`cat /tmp/$$`"
				umask 0000;;
			/i|i)  echo $NOCR1 "Enter include file: $NOCR2" 
				read LINE  
				[ -f "$LINE" ] && _CMD="$_CMD`cat $LINE`$NL" &&
				echo "$LINE included";;
			/w|w)  echo $NOCR1 "Enter output file: $NOCR2" 
				read LINE  
				[ "$LINE" ] && umask $UMASK && 
				echo "$_CMD" > "$LINE" && umask 0000 && 
				echo "$LINE written";;
			/q|q)  exit 0;; 
			/v|v)  umask $UMASK ; $EDITOR /tmp/$$.shql
				umask 0000;;
			\#*) 	[ "$NEW" = "Y" ] && _CMD="" ;;
			*)  	[ "$NEW" = "Y" ] && _CMD=""
			    	_CMD="$_CMD$LINE$NL";;
		esac
		NEW=""
	done

	CMD=`echo "$_CMD" | sed \
		-e "s/'/\"/g" \
		-e 's/\"\([^\"]*\)\"/\"\\\"\1\\\"\"/g' \
		-e 's/\([<>!=][<>!=]*\)/ \1 /g' \
		-e 's/</\\\</g' \
		-e 's/>/\\\>/g' \
		-e 's/\*/\\\*/g' \
		-e 's/(/ \\\( /g' \
		-e 's/)/ \\\) /g'`
	[ ! "$CMD" ] && continue
	IFS="$_IFS,"
	eval set X $CMD
	shift
	IFS="$_IFS"
	NEW="Y"
	case $1 in
		select) 	select_ "$@";; 
		create) 	create "$@";;
		delete) 	delete "$@";;
		drop) 		drop "$@";;
		insert) 	insert "$@";;
		update) 	update "$@";;
		edit)		[ "$2" ] && $EDITOR $2@;;
		help)		help "$@";;
		print)		select_ "select" '*' "from" "$2";;
		repair)		repair "$@" ;;
		database)	database "$@" ;;
		unload)		unload "$@" ;;
		begin)		;;
		commit)		;;
		*) 		echo "Missing or unrecognized command." 1>&2 ;;
	esac
	_CMD=""
done

