home *** CD-ROM | disk | FTP | other *** search
/ NetNews Usenet Archive 1993 #1 / NN_1993_1.iso / spool / comp / sources / misc / 4233 / shql < prev   
Encoding:
Text File  |  1993-01-06  |  20.0 KB  |  850 lines
  1. #!/bin/sh
  2. # use /bin/sh or /bin/ksh
  3. #
  4. # shql - version 1.1
  5. #
  6.  
  7. # DEFINE THESE
  8. SHQL_ROOT="/u/shql"        # system-wide database location
  9. EDITOR="${EDITOR:=/usr/bin/vi}" # default editor if EDITOR not defined
  10. SHELL="${SHELL:=/bin/sh}"     # default editor if EDITOR not defined
  11.  
  12. # Unix table file postfixes:  @ is attrib, ~ is data, % is view
  13.  
  14. DEBUG="N"    # set to Y for debugging
  15.  
  16. [ "$DEBUG" = "Y" ] && set -x           # uncomment for debugging
  17. #set -v
  18. UMASK=`umask`
  19. umask 0000        # share database
  20. trap "echo \"Goodbye\" ; \
  21.     rm -f /tmp/$$ /tmp/$$row /tmp/$$join*" 0 1 2 3 15
  22. set -h            # remember functions
  23.  
  24. if echo '\c' | grep -s c ; then        # to adapt to System V vs. BSD 'echo'
  25.     NOCR1='-n'            # BSD
  26.     NOCR2=""
  27. else
  28.     NOCR1=""            # System V
  29.     NOCR2='\c'
  30. fi
  31. NL='
  32. '
  33. TAB='    '
  34. export _IFS TABLE CMD NOCR1 NOCR2 NL TAB
  35. _IFS="$IFS"
  36.  
  37. if [ "X$1" = "X-q" ] 
  38. then    QUIET="Y"
  39.     shift
  40. fi
  41.  
  42. if [ "X$1" = "X" ]
  43. then    echo "Missing database name." 1>&2
  44.     echo "The database name must be a directory under $HOME/shql" 1>&2
  45.     echo "    or a directory under $SHQL_ROOT" 1>&2
  46.     exit 1
  47. fi
  48. echo "Database: $1"
  49.  
  50. if [ -d $HOME/shql/$1 ]
  51. then    cd $HOME/shql/$1
  52. elif [ -d $SHQL_ROOT/$1 ]
  53. then    cd $SHQL_ROOT/$1
  54. elif [ -d $HOME/$1 ]
  55. then    cd $HOME/$1
  56. elif [ -d $1 ]
  57. then    cd $1
  58. else     echo "Unknown database ($1)" 1>&2
  59.     echo "The database name must be a directory under $HOME/shql" 1>&2
  60.     echo "    or a directory under $SHQL_ROOT" 1>&2
  61.     exit 1
  62. fi
  63.  
  64.  
  65. #
  66. #**************************************************************************
  67. # syntax
  68. #**************************************************************************
  69. syntax(){
  70.     case "$1" in
  71.         create)    cat <<"END"
  72. CREATE TABLE table_name (
  73.     column_name column_width 
  74.     {, ...} 
  75. )     
  76. or
  77. CREATE VIEW view_name (
  78.     table_or_view1.column1 = table_or_view2.column2
  79. )
  80. END
  81. return 0
  82. ;;
  83.         delete) cat <<"END"
  84. DELETE 
  85. FROM table_name    
  86. { WHERE where_clause }
  87. END
  88. return 0
  89. ;;
  90.         drop) cat <<"END"
  91. DROP TABLE table_name
  92. or
  93. DROP VIEW view_name
  94. END
  95. return 0
  96. ;;
  97.         edit) cat <<"END"
  98. EDIT table_name
  99. is a non-standard method of changing a table's field names or display widths.
  100. END
  101. return 0
  102. ;;
  103.         help)    cat <<"END"
  104. HELP ALL
  105. or
  106. HELP TABLES 
  107. or
  108. HELP VIEWS
  109. or
  110. HELP COMMANDS
  111. or
  112. HELP [CREATE | DELETE | DROP | INSERT | SELECT | UPDATE | WHERE | PRINT | EDIT]
  113. or
  114. HELP table_name
  115. Commands must appear in lower case.
  116. END
  117. return 0
  118. ;;
  119.         insert) cat <<"END"
  120. INSERT INTO table_name 
  121.     { ( column_name, ... ) }
  122. VALUES ( expression, ...)
  123. or        
  124. INSERT INTO table_name 
  125.     { ( column_name, ... ) }
  126. subselect
  127. END
  128. return 0
  129. ;;
  130.         print) cat <<"END"
  131. PRINT table_name 
  132. is a non-standard synonym for SELECT * FROM table_name.
  133. END
  134. return 0
  135. ;;
  136.         select) cat <<"END"
  137. SELECT { DISTINCT } 
  138.     [ column_name {,...} | * ]
  139. FROM [ table_name | view_name ]
  140. { WHERE where_clause }
  141. { ORDER BY column_name { NUM } { ASC | DESC } {, ... }
  142. { UNION select statement }
  143. 'NUM' is a non-standard method for sorting numeric fields.
  144. END
  145. return 0
  146. ;;
  147.         update) cat <<"END"
  148. UPDATE table_name
  149. SET column_name = expression {, ... }
  150. { WHERE where_clause }
  151. END
  152. return 0
  153. ;;
  154.         where) cat <<"END"
  155. WHERE [ column_name | value ] [ =, !=, >, <, >=, <=, and, or, not, in ]
  156.       [ column_name | value | subselect ]
  157. Parentheses may be used to group expressions.  
  158. END
  159. return 0
  160. ;;
  161.         syntax)    syntax commands; echo
  162.             syntax create; echo
  163.             syntax delete; echo
  164.             syntax drop; echo
  165.             syntax insert; echo
  166.             syntax select; echo
  167.             syntax update; echo
  168.             syntax where; echo
  169.             syntax print; echo
  170.             syntax edit; echo
  171.             return 0
  172.             ;;
  173.     esac
  174.     return 1
  175. }
  176.  
  177. #
  178. #**************************************************************************
  179. # lookup_field
  180. #**************************************************************************
  181. lookup_field(){
  182.     if [ ! -f $TABLE% ]
  183.     then    RESULT="`grep -n \"^$1    \" $TABLE@`"
  184.     else     RESULT="`grep -n \"^$1    \" $TABLE@ | sed 1q`"
  185.     fi
  186.     if [ ! "$RESULT" ] 
  187.     then     OUTFIELD="$1"
  188.         return 1
  189.     else    OUTFIELDNUM="`expr "$RESULT" : '\([^:]*\)'`"
  190.         OUTFIELD="\$$OUTFIELDNUM" 
  191.         return 0
  192.     fi
  193. }
  194.  
  195. #
  196. #**************************************************************************
  197. # do_aggreg
  198. #**************************************************************************
  199. do_aggreg(){
  200.     if     [ "X$1" = 'Xsum' ]
  201.     then    AGGREG='total'
  202.     elif     [ "X$1" = 'Xavg' ]
  203.     then     AGGREG='(total/cnt)'
  204.     elif     [ "X$1" = 'Xcount' ]
  205.     then     AGGREG='cnt'
  206.     elif     [ "X$1" = 'Xmin' ]
  207.     then     AGGREG='min'
  208.     elif     [ "X$1" = 'Xmax' ]
  209.     then     AGGREG='max'
  210.     else    return 1
  211.     fi
  212.     [ "X$2" != "X(" -o "X$4" != "X)" ] && \
  213.         echo "Bad aggregate syntax" 1>&2 && syntax select && return 1
  214.     AGGFIELD="$3"
  215.     shift 4
  216.     lookup_field "$AGGFIELD"
  217.     [ "$?" -ne 0 ] && echo "Bad field name ($1)" 1>&2 && return 1
  218.     while [ $# -ne 0 ]
  219.     do    
  220.         [ "X$1" = "Xwhere" ] && break;
  221.         [ "X$1" = "Xorder" ] && break;
  222.         [ "X$1" = "Xunion" ] && break;
  223.         shift
  224.     done
  225.  
  226.     OUTFIELD=`( SUBSELECT="Y" ; AGGREGATE="Y"; \
  227.         select_ "select" "$AGGFIELD" "from" "$TABLE" "$@") | \
  228.         awk -F"    " \
  229.         'NR == 1 { min = $1; max = $1 }
  230.              { cnt += 1; total += $1 }
  231.         $1 < min { min = $1 }
  232.         $1 > max { max = $1 }
  233.         END     { printf "%s%s%s", "\"", '$AGGREG', "\"" }'`
  234.     if [ `expr "$RESULT" : '[^    ]*    \(.*\)'` -lt 10 ]
  235.     then    RESULT="$AGGFIELD    10"
  236.     fi
  237.     return 0
  238. }
  239.  
  240. #
  241. #**************************************************************************
  242. # do_join 
  243. #**************************************************************************
  244. do_join(){
  245.     update_view "$1"
  246.     TABLE="$1"
  247.     lookup_field "$2" 
  248.     [ "$?" -ne 0 ] && echo "Bad view specifcation ($1.$2)" 1>&2 && return 1
  249.     JFIELD1="$OUTFIELDNUM"
  250.     JFIELD1L1="`expr $JFIELD1 - 1`"
  251.     update_view "$3"
  252.     TABLE="$3"
  253.     lookup_field "$4" 
  254.     [ "$?" -ne 0 ] && echo "Bad view specifcation ($3.$4)" 1>&2 && return 1
  255.     JFIELD2="$OUTFIELDNUM"
  256.     JFIELD2L1="`expr $JFIELD2 - 1`"
  257.  
  258.     ( grep "^$2    " $1@ ;
  259.       grep -v "^$2    " $1@ ;
  260.       grep -v "^$4    " $3@ ) > $5@
  261.     sort -t\     +$JFIELD2L1 $3~ > /tmp/$$
  262.     sort -t\     +$JFIELD1L1 $1~ | \
  263.         join -t\     -j1 $JFIELD1 -j2 $JFIELD2 \
  264.                         - /tmp/$$ > $5~
  265. }
  266.  
  267. #
  268. #**************************************************************************
  269. # update_view
  270. #**************************************************************************
  271. update_view(){
  272.     [ ! -f "$1%" ] && return 1
  273.     ( do_join `cat $1%` )
  274. }
  275.  
  276. #
  277. #**************************************************************************
  278. # where
  279. #**************************************************************************
  280. where(){
  281.     shift
  282.     while [ $# -gt 0 -a "$1" != "order" -a "$1" != "union" ]
  283.     do
  284.         if [ "X$1" = "Xselect" ]
  285.         then
  286.             set X `( SUBSELECT="Y" ;select_ "$@")`
  287.             if [ "$?" -eq 0 ]
  288.             then     shift
  289.             else     return 1
  290.             fi
  291.         fi
  292.         case "$1" in
  293.             and)     WHERE="$WHERE && ";;
  294.             or)    WHERE="$WHERE || ";;
  295.             not)    WHERE="$WHERE !" ;;
  296.             =)    WHERE="$WHERE == ";;
  297.             'in')     shift
  298.                 set X `( SUBSELECT='Y';select_ "$@" )`
  299.                 if [ "$?" -eq 0 ]
  300.                 then     shift
  301.                 else     return 1
  302.                 fi
  303.                 INWHERE=""
  304.                 COMP="=="
  305.                 LOGIC="||"
  306.                 [ "X$LAST" = "Xnot" ] && COMP="=" && LOGIC="&&"
  307.                 for VALUE
  308.                 do
  309.                     [ "X$INWHERE" != "X" ] && 
  310.                         INWHERE="$INWHERE $LOGIC"
  311.                     INWHERE="$INWHERE ($WHERE$COMP $VALUE) "
  312.                 done
  313.                 WHERE="$INWHERE"
  314.                 break;;
  315.             *)    lookup_field "$1"
  316.                 WHERE="$WHERE $OUTFIELD";;
  317.         esac
  318.         LAST="$1"
  319.         shift
  320.     done 
  321.     [ "$WHERE" ] && WHERE=" ( $WHERE ) " && return 0
  322.     echo "Missing 'where' clause" 1>&2
  323.     syntax where
  324.     return 1
  325. }
  326.  
  327. #
  328. #**************************************************************************
  329. # help
  330. #**************************************************************************
  331. help(){
  332.     if [ ! "$2" ]
  333.     then    echo "Ambiguous syntax, try:" 1>&2 ; syntax help
  334.     elif [ "$2" = "all" ]
  335.     then    ls *@ *% 2>/dev/null | cut -d@ -f1 | cut -d% -f1 | uniq
  336.     elif [ "$2" = "tables" ] 
  337.     then    ls *@ *% 2>/dev/null | cut -d@ -f1 | cut -d% -f1 | uniq -u 
  338.     elif [ "$2" = "views" ] 
  339.     then    ls *% 2>/dev/null | cut -d% -f1 
  340.     elif [ "$2" = "commands" ]
  341.     then    cat << "END"
  342. /p is print
  343. /g is go(execute)
  344. /q is quit
  345. /e is edit
  346. /i is include
  347. /w is write
  348. /r is reset(clear)
  349. /s is shell
  350. /p/g print and go
  351. The number sign(#) may be used at the start of a line for comments.
  352. END
  353.     else    syntax $2 && return
  354.         TABLE="$2"
  355.         update_view "$TABLE"
  356.         if [ -f "$2@" ] 
  357.         then    echo "$NL <$2>" && cat "$2@"
  358.             [ -f "${2}%" ] &&echo $NOCR1 "$NL View:    $NOCR2" && 
  359.                 set X `cat $2%` && shift &&
  360.                 echo "$1.$2 = $3.$4"
  361.             echo "$NL Rows:    "`cat $TABLE~ | wc -l`
  362.         else     echo "$TABLE does not exist." 1>&2
  363.             syntax help
  364.         fi
  365.     fi
  366. }
  367.  
  368. #
  369. #**************************************************************************
  370. # create
  371. #**************************************************************************
  372. create(){
  373.     shift
  374.     if [ -f "$2@" -o -f "$2%" ]
  375.     then    echo "Table already exists." 1>&2
  376.     elif [ "X$1" = "Xview" -a $# -gt 2 ]
  377.     then    shift
  378.         if [ $# -ne 6 ]
  379.         then     syntax create
  380.         else     
  381.             [ "X$2" != "X(" ] && echo "Bad syntax" 1>&2 && 
  382.                             syntax create && return
  383.             TABLE1="`expr $3 : '\([^\.]*\)'`"
  384.             FIELD1="`expr $3 : '[^\.]*.\(.*\)'`"
  385.             TABLE="$TABLE1"
  386.             lookup_field "$FIELD1" 
  387.             [ "$?" -ne 0 ] && echo "Bad table or field name" 1>&2 &&
  388.                                     return
  389.             [ "X$4" != "X=" ] && echo "Bad syntax" 1>&2 && 
  390.                             syntax create && return
  391.             TABLE2="`expr $5 : '\([^\.]*\)'`"
  392.             FIELD2="`expr $5 : '[^\.]*.\(.*\)'`"
  393.             TABLE="$TABLE2"
  394.             lookup_field "$FIELD2" 
  395.             [ "$?" -ne 0 ] && echo "Bad table or field name" 1>&2 &&
  396.                                     return
  397.             [ "X$2" != "X(" ] && echo "Bad syntax" 1>&2 && 
  398.                             syntax create && return
  399.             echo "$TABLE1 $FIELD1 $TABLE2 $FIELD2 $1" > $1%
  400.             update_view "$1"            
  401.         fi
  402.         echo "OK"
  403.     elif [ "X$1" = "Xtable" -a $# -ge 5 ] 
  404.     then
  405.         [ "X$3" != "X(" ] && echo "Bad syntax" 1>&2 && 
  406.                             syntax create && return
  407.         TABLE="$2"
  408.         shift 3
  409.         > $TABLE@
  410.         > $TABLE~
  411.         while [ $# -ge 2 ]
  412.         do
  413.             echo "$1    $2" >> $TABLE@
  414.             shift 2
  415.         done
  416.         [ "X$1" != "X)" ] && echo "Bad syntax" 1>&2 && 
  417.                     rm -f $TABLE@ && syntax create && return
  418.         echo "OK"
  419.     else 
  420.         echo "Improper syntax ($1)" 1>&2
  421.         syntax create
  422.     fi
  423.     return
  424. }
  425.  
  426. #
  427. #*************************************************************************
  428. # drop
  429. #**************************************************************************
  430. drop(){
  431.     [ "$2" != "table" -a "$2" != "view" ] && 
  432.         echo "Syntax error." 1>&2 && syntax drop && return
  433.     [ "$2" = "table" -a -f "$3%" ] &&
  434.         echo "Can not drop, $2 is a view, not a table" 1>&2 && return
  435.     [ "$2" = "view" -a ! -f "$3%" ] &&
  436.         echo "Can not drop, $2 is not a view" 1>&2 && return
  437.     if [ -f "$3@" -o -f "$3%" ] 
  438.     then    rm -f $3@ $3~ $3%
  439.         echo "OK"
  440.     else      echo "No such table" 1>&2
  441.     fi
  442. }
  443.  
  444. #
  445. #**************************************************************************
  446. # insert
  447. #**************************************************************************
  448. insert(){
  449.     shift
  450.     [ "X$1" != "Xinto" ] && echo "Improper syntax ($1)" 1>&2 && 
  451.         syntax insert && return
  452.     shift
  453.     TABLE="$1"
  454.     update_view "$TABLE" && echo "Can not insert into a view" 1>&2 && return
  455.     [ ! -f "$TABLE@" ] && echo "Table does not exist" 1>&2 && return
  456.     shift
  457.     ATTRIB="`cat $TABLE@ | wc -l`"
  458.     XASGN=""
  459.     XECHO="echo \""
  460.     if [ $# -gt 0 -a "X$1" = "X(" ]
  461.     then    ATTRIB2="0"
  462.         shift
  463.         while [ $# -gt 0 -a "X$1" != "X)" ]
  464.         do
  465.             lookup_field "$1" 
  466.             [ "$?" -ne 0 ] && echo "Bad field name. ($1)" 1>&2 && 
  467.                                     return 
  468.             XASGN="$XASGN X$OUTFIELDNUM=\`eval echo \$1\` ; shift;"
  469.             shift
  470.             ATTRIB2=`expr $ATTRIB2 + 1`
  471.         done
  472.         [ "X$1" != "X)" ] && echo "Syntax error ($1)" 1>&2 && 
  473.                         syntax insert && return
  474.         shift
  475.         POS="1"
  476.         while [ "$POS" -le "$ATTRIB" ]
  477.         do
  478.             eval X$POS=""
  479.             [ "$POS" != "1" ] && XECHO="$XECHO\$TAB"
  480.             XECHO="$XECHO\$X$POS"
  481.             POS=`expr $POS + 1`
  482.         done
  483.         XECHO="$XECHO\""
  484.         ATTRIB="$ATTRIB2"
  485.     fi    
  486.     if [ "X$1" = "Xselect" ]
  487.     then     eval set X "`( SUBSELECT='Y' ; select_ "$@" )` \)"
  488.         shift
  489.     elif [ "X$1" != "Xvalues" -o "X$2" != 'X(' ]  
  490.         then     echo "Improper syntax ($1)" 1>&2 && syntax insert && 
  491.                                     return
  492.     else    shift 2
  493.     fi
  494.     for LAST do 
  495.     : ; done
  496.     [ "X$LAST" != "X)" ] && 
  497.         echo "Improper syntax" 1>&2 && syntax insert && return
  498.     if [ "`expr \( $# - 1 \) % $ATTRIB`" -ne 0 ]
  499.     then     echo "Incorrect number of values." 1>&2
  500.     else    ROWS="`expr \( $# - 1 \) / $ATTRIB`"
  501.         while [ $# -gt 1 ]
  502.         do    
  503.             if [ "$XASGN" = "" ]
  504.             then     
  505.                 echo $NOCR1 "`eval echo $1`$NOCR2" >> $TABLE~ 
  506.                 shift
  507.                 while [ "`expr \( $# - 1 \) % $ATTRIB`" -ne 0 ]
  508.                 do
  509.                     echo $NOCR1 "$TAB`eval echo $1`$NOCR2"\
  510.                                  >> $TABLE~
  511.                     shift
  512.                 done
  513.                 echo "" >> $TABLE~
  514.             else    eval $XASGN
  515.                 eval $XECHO >> $TABLE~
  516.             fi
  517.         done
  518.         echo "($ROWS rows)"            
  519.     fi
  520. }
  521.  
  522. #
  523. #*************************************************************************
  524. # delete
  525. #**************************************************************************
  526. delete(){
  527.     TABLE="$3"
  528.     [ "X$2" != "Xfrom" ] && echo "Improper syntax ($2)" 1>&2 && 
  529.         syntax delete && return
  530.     update_view "$TABLE" && echo "You can not delete from a view." 1>&2 &&
  531.                                     return  
  532.     [ ! -f "$TABLE@" ] && echo "$TABLE does not exist." 1>&2 && return
  533.     WHERE=""
  534.     if [ "X$4" = "Xwhere" ]
  535.     then     shift 3
  536.         where "$@" && 
  537.         awk -F"    " "! $WHERE { cnt += 1 ; print } 
  538.             END { printf \"( %1d rows.)\\n\", (NR - cnt) \
  539.             >\"/tmp/$$row\" }" $TABLE~ > /tmp/$$ && 
  540.             mv /tmp/$$ $TABLE~ && cat /tmp/$$row
  541.     else    echo '('`cat $TABLE~ | wc -l`' rows)' 
  542.         > $TABLE~
  543.     fi
  544. }
  545.  
  546. #
  547. #*************************************************************************
  548. # update
  549. #**************************************************************************
  550. update(){
  551.     TABLE="$2"
  552.     update_view "$TABLE" && echo "Can not update a view." 1>&2 && return
  553.     [ ! -f "$TABLE@" ] && echo "$TABLE does not exit." 1>&2 && return
  554.     [ "X$3" != "Xset" ] && echo "Improper syntax." 1>&2 && syntax update && 
  555.                                     return
  556.     shift 3
  557.     ASSIGN=""
  558.     while [ $# -gt 0 -a "X$1" != "Xwhere" ]
  559.     do
  560.         lookup_field "$1" && [ "X$2" = "X=" ] && ASSIGN="$ASSIGN ; "
  561.         ASSIGN="$ASSIGN $OUTFIELD"
  562.         shift
  563.     done
  564.     WHERE=""
  565.     if [ "X$1" = "Xwhere" ] 
  566.     then     where "$@" || return 
  567.     fi
  568.     awk -F"    " "BEGIN { OFS = \"    \" }
  569.         $WHERE     { $ASSIGN; cnt += 1 }
  570.             { print } 
  571.         END     { printf \"( %1d rows)\\n\", cnt >\"/tmp/$$row\" }" \
  572.         $TABLE~ > /tmp/$$ && 
  573.             mv /tmp/$$ $TABLE~ && cat /tmp/$$row
  574. }
  575.  
  576. #
  577. #**************************************************************************
  578. # select_
  579. #**************************************************************************
  580. select_(){
  581. [ "$DEBUG" = "Y" ] && set -x           # uncomment for debugging
  582.     UNION="Y"
  583.     while [ "$UNION" != "" ]
  584.     do
  585.         INAGG=""
  586.         FROM=""
  587.         UNION=""
  588.         TABLE=""
  589.         for ATABLE
  590.         do
  591.             [ "X$ATABLE" = "Xwhere" ] && break
  592.             [ "X$ATABLE" = "Xorder" ] && break
  593.             [ "X$ATABLE" = "Xunion" ] && break
  594.             [ "X$ATABLE" = "Xfrom" ] && FROM="Y" && continue
  595.             if [ "$FROM" ]
  596.             then
  597.             [ ! -f "$ATABLE@" ] && \
  598.             echo "$ATABLE does not exist." 1>&2 && return 1
  599.             if [ ! "$TABLE" ]
  600.             then     TABLE="$ATABLE"
  601.             else    JTABLE="$TABLE"
  602.                 PREV=""
  603.                 PPREV=""
  604.                 FOUND=""
  605.                 for GETJ
  606.                 do
  607.                     if [ "$PREV" = "=" ]
  608.                     then
  609.                     TABLE="$JTABLE"
  610.                     lookup_field "$PPREV" &&
  611.                     TABLE="$ATABLE" &&
  612.                     lookup_field "$GETJ" &&
  613.                     FOUND="Y1" &&
  614.                     break
  615.                     TABLE="$ATABLE"
  616.                     lookup_field "$PPREV" &&
  617.                     TABLE="$JTABLE" &&
  618.                     lookup_field "$GETJ" &&
  619.                     FOUND="Y2" &&
  620.                     break
  621.                     fi
  622.                     PPREV="$PREV"
  623.                     PREV="$GETJ"
  624.                 done
  625.                 [ ! "$FOUND" ] &&
  626.                 echo "Join not found, \c" &&
  627.                 echo "try reordering tables." 1>&2 && return 1
  628.                 if [ "$FOUND" = "Y1" ]
  629.                 then
  630.     echo "$JTABLE    $PPREV    $ATABLE    $GETJ    /tmp/$$join2" >/tmp/$$join2%
  631.                 else
  632.     echo "$ATABLE    $PPREV    $JTABLE    $GETJ    /tmp/$$join2" >/tmp/$$join2%
  633.                 fi
  634.                 update_view /tmp/$$join2
  635.                 mv /tmp/$$join2~ /tmp/$$join~
  636.                 mv /tmp/$$join2@ /tmp/$$join@
  637.                 expr "$RESULT" : '[^:]:*\(.*\)' >>/tmp/$$join@
  638.                 cut -d\     -f1 /tmp/$$join~ | \
  639.                     paste /tmp/$$join~ - >/tmp/$$
  640.                 mv /tmp/$$ /tmp/$$join~
  641.                 TABLE="/tmp/$$join"
  642.             fi
  643.             fi
  644.         done
  645.         [ ! "$FROM" ] && echo "Syntax error." 1>&2 && syntax select &&
  646.                                 return 1
  647.         update_view "$TABLE"
  648.          shift
  649.         DISTINCT=""
  650.         [ "X$1" = "Xdistinct" ] && DISTINCT="Y" && shift
  651.         FIELDS=""
  652.         PRINTF=""
  653.         while [ "X$1" != "Xfrom" ]
  654.         do
  655.             if [ "X$1" = 'X*' ]
  656.             then    shift
  657.                 set X `cat $TABLE@ | cut -d\     -f1` "$@" 
  658.                 shift
  659.             else    lookup_field "$1"
  660.                 if [ "$?" -ne 0 ]
  661.                 then     do_aggreg "$@"
  662.                     if [ "$?" -eq 0 ]
  663.                     then    INAGG="Y"
  664.                         shift 3
  665.                     else 
  666.                       echo "Bad field name ($1)" 1>&2
  667.                       return 1
  668.                     fi
  669.                 fi
  670.                 [ "$FIELDS" ] && FIELDS="$FIELDS,"
  671.                 FIELDS="$FIELDS $OUTFIELD"
  672.                 if [ "$SUBSELECT" = "" ]
  673.                 then     [ ! "$PRINTF" ] && PRINTF="|"
  674.                     WIDTH=`expr "$RESULT" : \
  675.                         '[^    ]*    \(.*\)'`
  676.                     PRINTF="$PRINTF%-$WIDTH.${WIDTH}s|"
  677.                 else    if [ ! "$AGGREGATE" ]
  678.                     then    PRINTF="$PRINTF\\\"%s\\\" "
  679.                     else    PRINTF="$PRINTF%s\n"
  680.                     fi
  681.                 fi
  682.                 shift
  683.             fi
  684.         done
  685.         shift 2
  686.         WHERE=""
  687.         SORT=""
  688.         while [ $# -ne 0 ]
  689.         do    
  690.             if [ "X$1" = "Xwhere" ]
  691.             then
  692.                 where "$@"
  693.                 [ "$?" -ne 0 ] && return 1 
  694.                 WHERE="$WHERE || NR == 1"
  695.                 shift
  696.             elif [ "X$1" = "Xorder" ]
  697.             then     [ "X$2" != "Xby" ] && 
  698.                     echo "Syntax error ($2)" 1>&2 && 
  699.                     syntax select && return 1
  700.                 shift 2
  701.                 while [ $# -gt 0 -a "$1" != "union" ]
  702.                 do
  703.                     if [     "X$1" != "Xasc" -a \
  704.                         "X$1" != "Xdesc" -a \
  705.                         "X$1" != "Xnum" ] 
  706.                     then    lookup_field "$1" 
  707.                         [ "$?" -ne 0 ] &&
  708.                 echo "Bad field name ($1)" 1>&2 && return 1 
  709.                         [ "$SORT" = "" ] && 
  710.                             SORT="sort -t\"    \" "
  711.                         SORTL="`expr $OUTFIELDNUM - 1`"
  712.                         SORT="$SORT +$SORTL"
  713.                         [ "X$2" = "Xnum" ] && 
  714.                             SORT="${SORT}n"
  715.                         [ "X$2" = "Xdesc" ] && 
  716.                             SORT="${SORT}r"
  717.                         [ "X$3" = "Xdesc" ] && 
  718.                             SORT="${SORT}r"
  719.                         SORT="$SORT -$OUTFIELDNUM"
  720.                     fi
  721.                     shift
  722.                 done
  723.             elif [ "X$1" = "Xunion" ]
  724.             then    shift
  725.                 UNION="Y"
  726.                 break
  727.             else    shift
  728.             fi
  729.         done
  730.         [ "$INAGG" ] && WHERE="NR == 1"
  731.  
  732.         if [ "$DISTINCT" != "" ] 
  733.         then    if [ "$SORT" = "" ]
  734.             then    DIST="sort | uniq | tee /tmp/$$row"
  735.             else    DIST="uniq | tee /tmp/$$row"
  736.             fi
  737.         else    DIST="cat"
  738.         fi                    
  739.  
  740.         TABLEFILE="$TABLE~"
  741.         [ "$SORT" != "" ] && cat $TABLE~ | eval "$SORT" > /tmp/$$ &&
  742.                             TABLEFILE="/tmp/$$"
  743.  
  744.         if [ "$SUBSELECT" ]
  745.         then    awk -F"    " "$WHERE {printf \"$PRINTF\", $FIELDS }" \
  746.                             $TABLEFILE |eval "$DIST"
  747.         else    if [ ! "$QUIET" -o "$INAGG" = "Y" ]
  748.             then 
  749.             ( set X `cut -d\     -f1 $TABLE@` ; shift 
  750.               echo $NOCR1 "-$1-$NOCR2" ; shift 
  751.                 for HEADING
  752.               do 
  753.                 echo $NOCR1 "$TAB-$HEADING-$NOCR2" 
  754.                done ; echo "" )
  755.             fi |
  756.             awk -F"    " \
  757.             "$WHERE { cnt += 1 ; printf \"$PRINTF\\n\", $FIELDS }
  758.             END    { printf \"( %1d rows)\\n\", (cnt - 1) \
  759.             >\"/tmp/$$row\" }" - $TABLEFILE | eval "$DIST" \
  760.                 && if [ "$DISTINCT" = "" ]
  761.                 then    cat /tmp/$$row
  762.                 else     X=`expr \`cat /tmp/$$row|wc -l\` - 1`
  763.                     echo '('$X' rows)' 
  764.                 fi
  765.         fi
  766.     done
  767.     return 0
  768. }    
  769.  
  770. #
  771. #**************************************************************************
  772. # main
  773. #**************************************************************************
  774. while :
  775. do
  776.     while :
  777.     do
  778.         echo $NOCR1 "* $NOCR2"
  779.         read LINE || exit 
  780.         SQLPART="`expr "$LINE" : '\(..*\)/.$'`"
  781.         if [ "$SQLPART" != "" ]
  782.         then
  783.             [ "$NEW" = "Y" ] && _CMD=""
  784.             if [ "`expr "$LINE" : '.*/p/g$'`" -ne 0 ]
  785.             then
  786.                 _CMD="$_CMD"`expr "$LINE" : '\(.*\)/p/g$'`"$NL"
  787.                 LINE="/p/g"
  788.                 NEW=""
  789.             else
  790.                 _CMD="$_CMD""$SQLPART""$NL"
  791.                 LINE="`expr "$LINE" : '.*\(/.\)$'`"
  792.                 NEW=""
  793.             fi
  794.         fi
  795.          case "$LINE" in 
  796.             /p|p)  echo "$_CMD";;
  797.             /g|g)  break;;
  798.             /p/g|pg) echo "$_CMD" ; break ;;
  799.             /r|r)  echo "reset" ; _CMD="";;
  800.             /s|s)  umask $UMASK ; $SHELL ; umask 0000;;
  801.             /e|e)  umask $UMASK ; echo "$_CMD" > /tmp/$$
  802.                 $EDITOR /tmp/$$; _CMD="`cat /tmp/$$`"
  803.                 umask 0000;;
  804.             /i|i)  echo $NOCR1 "Enter include file: $NOCR2" 
  805.                 read LINE  
  806.                 [ -f "$LINE" ] && _CMD="$_CMD`cat $LINE`$NL" &&
  807.                 echo "$LINE included";;
  808.             /w|w)  echo $NOCR1 "Enter output file: $NOCR2" 
  809.                 read LINE  
  810.                 [ "$LINE" ] && umask $UMASK && 
  811.                 echo "$_CMD" > "$LINE" && umask 0000 && 
  812.                 echo "$LINE written";;
  813.             /q|q)  exit 0;; 
  814.             \#*)     [ "$NEW" = "Y" ] && _CMD="" ;;
  815.             *)      [ "$NEW" = "Y" ] && _CMD=""
  816.                     _CMD="$_CMD$LINE$NL";;
  817.         esac
  818.         NEW=""
  819.     done
  820.  
  821.     CMD=`echo "$_CMD" | sed \
  822.         -e "s/\'/\"/g" \
  823.         -e 's/\"\([^\"]*\)\"/\"\\\"\1\\\"\"/g' \
  824.         -e 's/\([<>!=][<>!=]*\)/ \1 /g' \
  825.         -e 's/</\\\</g' \
  826.         -e 's/>/\\\>/g' \
  827.         -e 's/\*/\\\*/g' \
  828.         -e 's/(/ \\\( /g' \
  829.         -e 's/)/ \\\) /g'`
  830.     [ ! "$CMD" ] && continue
  831.     IFS="$_IFS,"
  832.     eval set X $CMD
  833.     shift
  834.     IFS="$_IFS"
  835.     NEW="Y"
  836.     case $1 in
  837.         select)     select_ "$@";; 
  838.         create)     create "$@";;
  839.         delete)     delete "$@";;
  840.         drop)         drop "$@";;
  841.         insert)     insert "$@";;
  842.         update)     update "$@";;
  843.         edit)        [ "$2" ] && $EDITOR $2@;;
  844.         help)        help "$@";;
  845.         print)        select_ "select" '*' "from" "$2";;
  846.         *)         echo "Missing or unrecognized command." 1>&2 ;;
  847.     esac
  848. done
  849.  
  850.