# CHAPTER 2: The UNIX Shell Interface

## 2.1 Introduction

The UNIX Shell Interface allows a shell program to execute an Empress Query Language command in the UNIX environment. There are two commands you can use to execute Empress Query Language commands from the UNIX shell:

1. `empcmd` to execute a single command.
2. `empbatch` to execute a batch of commands.

The Shell Interface allows rapid packaging of prototype database management interactions. It allows existing UNIX programs, C routines, and other shell programs to be invoked by simple commands, encouraging the rapid design and development of custom interfaces to the database management system.

## 2.2 Execute a Single SQL Command from UNIX Shell

The command `empcmd` is used to have the UNIX shell execute one Empress SQL command. The syntax of the command is as follows:

`empcmd database "command"`

where:

`database` | is the name of the database directory.
`command` | is the Empress SQL command to be executed.

Example 1: Retrieve an Attribute Values from a Table

The following command is issued from the UNIX prompt which lists all employees from the `personnel` table in the database `repairs`:

       empcmd repairs "SELECT name FROM personnel"

The above command executes the Empress SQL command "`SELECT name FROM personnel`" on the database called `repairs`, without the need to invoke `empsql` directly. Once the command has been executed, control returns to the UNIX shell.

The above `empcmd` command can also be stored in a command text file called `names`. Once this is done, typing in `names` at the UNIX prompt will produce the list of employee names.

Example 2: Passing Argument to the Command File

The following command, stored in an executable file called `phone`, produces the telephone number of the employee given as its argument.

       empcmd repairs "select phone from personnel dump
            where name = '$1'"

The `dump` keyword has been included in the `empcmd` command to suppress printing the heading `phone`. To find Jones' phone number, type in:

       phone Jones

and get the answer:

       667-2951

Example 3: Command File with Error Checking

A more fool proof version of the `phone` program would check to see that one and only one name has been supplied, and give a suitable error message if this is not so. The improved version is:

       USAGE = "Usage: phone employee's name"
       case "$#" in

            1)
                 mscmd repairs "select phone from
                      personnel
                 dump where name = '$1'"
                 ;;
            *)
                 echo $USAGE
                 exit 1
                 ;;
            esac

Example 4: Interactive Command File

If a more interactive implementation is preferred, another version of the phone program can be written as follows:

       echo "
       Find phone number given employee's name.
       To stop, enter '.q'

       "
       NAME =
       NULL =
       : reprompt for name if no name is given
       while true
       do
            echo -n "Employee name: "
            read NAME
            case $NAME in
                 $NULL)
                      continue
                      ;;
                 .q)
                      exit 0
                      ;;
                 esac
                 echo ""
                 empcmd repairs "select phone from personnel
                           dump where name = '$NAME'"
                 echo ""
       done

This version also has the advantage of more than one phone number being retrieved at a time.

A sample dialogue for running the interactive `phone` program is given below. A percent sign (%) is to indicate UNIX operating system prompt. User responses are shown in boldface.

       % phone

       Find phone number given employee's name.
       To stop enter '.q'

       Employee name: Jones

       667-2951

       Employee name: Petersen

       978-6060

       Employee name:              (You press <Return> by mistake)
       Employee name: Mosca

       544-2243

       Employee name: .q
       %

Example 5: A Simple Report

Another example of a small shell program with tabular output is the following, called `employees`, which retrieves the names and phone numbers for all employees, counts them, and prints them with a heading:

       : PRINT LIST OF CURRENT EMPLOYEES
       echo "Current Employees"
       echo ""empcmd repairs "select name, phone from personnel"
       EMPLOYEES='empcmd repairs "select count(*) from personnel dump"'
       echo ""
       echo "Total number of Employees = $EMPLOYEES"

Typing in `employees` produces the following output:

       Current Employees

       name          phone

       Kilroy        426-9681
       Jones         667-2951
       Mosca         544-2243
       Wladislaw     723-6073
       Peterson      978-6060
       Scarlatti     961-7363
       Jordan        964-3335

       Total number of Employees = 7

Example 6: More Complex Report

As a further example, a shell program called `loans` which takes a number of employee names as arguments and prints out details of all loans currently outstanding for each, the total amount for each, and the grand total over all the specified employees, is:

       : PRINT details, total, and grand total of loans
       : outstanding to the specified employees

       DATABASE=repairs
       USAGE="Usage: $0 Employee_name ..."
       case "$#" in
            0)
            echo $USAGE
            exit 1
            ;;
       esac

       for I
       do
            echo "Loans currently outstanding to $I:"
            echo ""
            empcmd $DATABASE "select date, amount from loans
                      where name = '$I'"
            echo ""
            echo -n "Total amount owing for $I: "
            empcmd $DATABASE "select sum (amount) from loans
                      dump where name = '$I'"
            echo ""
            echo ""
       done

       : construct a WHERE clause with all the names
       : given as arguments, joined by ORs

       clause="where name = '$1'"
       shift
       for I
       do
            CLAUSE="$clause or name = '$I'"
       done
       echo -n "Total amount outstanding over all of the above: "
       empcmd $DATABASE "select sum (amount) from loans dump $CLAUSE"

To find the loans outstanding for Jones and Mosca, type in:

       loans Jones Mosca

which gives the following output:

       Loans currently outstanding to Jones:

       date                         amount

        7 February 1992             $33.95
        3 April 1992                $25.00
       12 August 1992              $300.00

       Total amount owing for Jones: 358.95

       Loans currently outstanding to Mosca:

       date                         amount

       2 February 1992             $150.00
       4 May 1992                  $200.00

       Total amount owing for Mosca: 350.00

       Total amount outstanding over all of the above: 708.95

## 2.3 Execute a Batch SQL Commands from UNIX Shell

The command `empbatch` allows you to submit a batch of Empress SQL commands from the operating system. The syntax of `empatch` is as follows:

`empbatch database`

where:

`database` | is the name of the database directory.

The command `empbatch` works in interactive mode; it is similar to being in Empress SQL but there are no prompts. A semicolon (;) is used to end each Query Language command, and a command may span several lines. The program exits on a syntax error.

For example if you type in from UNIX:

       empbatch repairs

the cursor will wait on the next line for a command. Typing in a command brings an immediate response:

       SELECT * FROM loans
            WHERE name = "Jones";

will produce:

       name     date                  amount

       Jones     7 February 1992      $33.95
       Jones     3 April 1992         $25.00
       Jones    12 August 1992       $300.00

<Ctrl+D> or the <Rubout> or <Interrupt> key will return you to the operating system.

You can store a batch of Empress SQL commands in a UNIX command file, for example, a file called `queries` contains the following:

       empbatch repairs << '}'
         set MSPAGER to 'more -c';
         set MSQLAUTOPAGE to true;
         select * from loans where name = "Jones";
       }

Then submit the batch by typing:

`queries`

The `queries` uses input redirection to tell UNIX that input for the `empbatch` command follows, in this case, until a "}" character is read.