# CHAPTER 8: The Interactive Interface

## 8.1 Introduction

The Interactive Interface permits you to carry out database operations in a conversational, prompt and respond type of interaction with Empress. The Query Language commands available under the Interactive Interface are:

Table 8-1: Interactive Query Language Commands

Command | Summary
`ALTER` | Changes the structure of an existing table without having to dump and re-load its records.
`CREATE TABLE` | Allows you to set up a table interactively, with prompting for attribute names and data types, and provision for making changes to the attributes as the table is constructed.
`INSERT` | Adds new data to a table.
`UPDATE` | Modifies existing data in a table.

A summary of the commands used to control an interactive session is presented in this chapter. The commands used to invoke the Interactive Interface are also described.

## 8.2 Interactive Interface Commands

Interactive Interface commands are used to control the dialogue with Empress while using the Interface. When entering commands, each command is preceded by a period (.) to distinguish it from data. Where a `Ready` prompt appears the necessary period is supplied.

While entering data, the Interface prompts for whatever is required. It then prompts for some action to be taken regarding the record or attribute definition. After you enter a command, the Interface will either continue prompting or return control to Empress, depending on the operation.

Since a leading period is used to indicate a command, a period that begins an input line must be entered as two periods, (..). The only exception to this is in entering decimal numbers or dollar values. In this case, a period followed by a number is considered data (either decimal data or cents). Fifty cents, for example, may be entered as `.5`.

Interactive Interface commands may be typed in either upper or lower case. Pressing <Return> without entering a command or data causes the Interface to prompt for the next attribute value, or re-prompt for an attribute definition.

Character values are entered without quotes, since the Interactive Interface knows what kind of data is required for a given attribute. Entering data that is of the incorrect form for a data type will result in an error message. Upon an error during `INSERT` and `UPDATE`, the Interface will automatically invoke the line editor for you to make corrections. During `CREATE` and `ALTER`, you will just have to type the value again.

In `CREATE` and `ALTER TABLE` mode, attribute commands apply to the attribute name and data type, whereas record commands apply to an entire attribute definition.

Table 8-2: Interactive Interface Attribute Commands

Command | Mnemonic Name | Description
`.^` | Up | Go to the previous attribute and prompt for its value
`.>attr` | Goto* | Go to the named attribute and prompt for its value.
`.~` | Null* | Set the attribute value to null.
`."` | Ditto* | Set the attribute value to the same value as entered for the attribute in the previous record.
`.z` | Zap* | Invoke the line editor to alter the attribute value.
`.zz` | Zapzap* | Invoke an operating system editor to alter the attribute value. The value will be entered as soon as you leave the editor. Note that pressing the <Interrupt> key immediately before leaving the editor will cause the just entered text to be lost. The editor invoked is specified in the system variable `MSEDITOR`.

* cannot be used for `CREATE TABLE` or `ALTER TABLE` command.

Table 8-3: Interactive Interface Record Commands

Command | Mnemonic Name | Description
`.d` | Display | Display the names and values of the current record or attribute definition.
`.e` | Enter | Enter the record or attribute definition into the table and prompt for the next record or definition.
`.i` | Insert | `ALTER TABLE` command only. Insert a new attribute definition before the current one `.p` and `.i` are invalid during this command.
`.n` | Next | Go to the next record or attribute definition without entering the current one. In `INSERT` or `CREATE` mode it can only be used after a `.p` command.
`.p` | Previous | Return to the previous record or attribute definition and prompt for values. Values already defined are not altered unless a new value is entered or the current value edited. You cannot back up more than one record using `.p`.
`.r` | Reset | Reset the current record values to null in `INSERT` mode or their original values in `UPDATE` mode, and begin prompting from the first attribute. In `CREATE` mode it resets all values to nothing. The record or attribute definition is not entered into the table.
`.x` | Axe | Delete the current record or attribute definition, and prompt for the next. In `UPDATE` mode this can be used to delete records one at a time.
`.u` | Undo | Undoes the preceding `.x` command. Only valid during the record immediately after an `.x` command.

Table 8-4: Interface Commands

Command | Mnemonic Name | Description
`.q` | Quit | Quit the Interactive Interface. In `CREATE` mode, it creates the table. In `ALTER` mode, any modifications made to the current attribute definition are not preserved. Attribute definitions following the current one remain unchanged. In `INSERT` and `UPDATE` mode, the `.q` command does not enter the current record into the table. A message telling how many records were entered into the table or updated/deleted is displayed before control is returned to Empress.
`.$` | Abort | `CREATE` or `ALTER` mode only. Quit the Interactive Interface; do not create or alter the table.
`.v` | Verbose* | Switch from terse to verbose prompting, or vice versa. Verbose prompting prints the attribute name and the data type. Terse prompting prints only the attribute name. The default is terse
`.?` | Help | Print a summary of the available commands.
`.!cmd` | System | Escape to or invoke the operating system and execute the command.

* cannot be used for `CREATE TABLE` or `ALTER TABLE`.

## 8.3 Interactive ALTER Command

The Interactive ALTER command changes the structure of an existing table without having to dump and re-load its records.

Syntax

      ALTER table;

Notes

1. Empress presents each attribute of the table in turn, allowing you to modify its name data type, data type parameters, and null or not-null specification.

2. A new attribute may be inserted before the current one using an Interface command.

3. Once all the existing attribute definitions have been processed, the Interface prompts for any new attribute definitions to be added at the end of the table.

4. Note that you cannot change an existing attribute name by this command; instead, the `RENAME` command should be used.

5. A command to quit may be issued at any time. Changes made up to that point will be recorded, except for changes to the current attribute. Changes are entered using the .e command, which always retrieves the next attribute, or prompts for another. All other attributes are preserved.

6. The following commands may be used with the Interactive ALTER command.

   Table 8-5: Interactive ALTER Commands

   Command | Description
   `.?` | Print this help file.
   `.!cmd` | Escape to the operating system to execute the command `cmd`.
   `.^` | Go to previous item and re-prompt for its value.
   `.$` | Abort this session; do not alter the table.
   `.d` | Display all entered values for this item.
   `.e` | Enter the item into the table and begin prompting for the next item.
   `.i` | **Insert a new item before the current one.
   `.n` | *Go on to the next item without entering the current one.
   `.p` | **Returns to the previous item and re-prompt for item values (values already entered are not lost). It is not possible to back up more than one item using `.p`.
   `.q` | Quit the Interactive Interface and return control to Empress. Create the table as entered.
   `.r` | Reset the current item values to null.
   `.x` | *Delete the current item.

   * valid only after `.p` command
   ** not valid after `.p` command

Example

  To illustrate the Interactive `ALTER` command we will alter the loans table. We will reduce the number of characters allotted to the name attribute from 25 to 15, and add two more attributes: a `loan_number` after `name` to hold a sequence-number for each employee's loan, and `date_due` after `amount`, to indicate when the loan should be repaid.

         * ALTER loans;

      Name for Attribute #1: number
      Data Type: integer .n
  | Skip over this attribute.
      Name for Attribute #2: name
      Data Type: char
      Maximum Number of Characters: 25 15
      Type: 1
      Allow Null Values? y
      Ready. e
  | Change length.
      Name for Attribute #3: date
      Data Type: date .i
  | Insert new attribute before date attribute.
      Name for Attribute #3: loan_number
      Data Type: integer

      Ready.e
      Name for Attribute #4: date
      Data Type: date .n
  | Skip over this attribute.
      Name for Attribute #5: amount
      Data Type: dollar .n
  | Skip over this attribute.
      Name for Attribute #6: date_due
      Data Type: date
      Type: 1
      Allow Null Values? y

      Ready .e
  | Prompt for more attributes
      Name for Attribute #6: .q

         * DISPLAY loans;

         *** loans ****

         number             integer
         name               char(15, 1)
         loan_number        integer
         date               date(1)
         amount             dollar 6, 1)
         date_due           date(1)

         * SELECT FROM loans;

         number  name    loan_number  date              amount  due_date

          5      Mosca                 2 February 1990  $150.00
          3      Jones                 7 February 1990   $33.95
         10      Kilroy               16 February 1990  $250.00
         17      Wladislaw            27 February 1990   $55.00
          3      Jones                 3 April 1990      $25.00
          5      Mosca                 4 May 1990       $200.00
         17      Wladislaw            12 May 1990        $25.00
          8      Peterson              6 June 1990       $50.00
         17      Wladislaw            25 June 1990       $75.00
          3      Jones                12 August 1990    $300.00
          4      Scarlatti             9 September 1990 $150.00
          4      Scarlatti            14 October 1990   $125.00

         *

  You may now give the new attributes values using an `UPDATE` command.

## 8.4 Interactive CREATE Command

The Interactive CREATE command allows you to set up a table interactively, with prompting for attribute names and data types, and provision for making changes to the attributes as the table is constructed.

Syntax

      CREATE table;

Notes

1. The system will prompt for attributes until you quit. For each attribute, the system will ask for the attribute name, data type, any required data type parameters, and whether the attribute may contain null values.

2. The following commands may be used with the Interactive CREATE command.

   Table 8-6: Commands for Interactive CREATE

   Command | Description
   `.?` | Print this help file.
   `!cmd` | Escape to the shell or invoke the operating system to execute the command `cmd`.
   `.^` | Go to previous item and re-prompt for its value.
   `.$` | Abort this session; do not create the table.
   `.d` | Display all entered values for this item.
   `.e` | Enter the item into the table and begin prompting for the next item.
   `.n` | *Go on to the next item without entering the current one.
   `.p` | **Return to the previous item and re-prompt for item values (values already entered are not lost). It is not possible to back up more than one item using `.p`.
   `.q` | Quit the Interactive Interface and return control to Empress; create the table as entered.
   `.x` | *Delete the current item.

   * valid only after `.p` command
   ** not valid after `.p` command

Example

  The following dialogue is used to illustrate several features of the Interactive Interface. The example sets up the database `repairs` for the first time; the dialogue for creating the `personnel` table should be self-explanatory.

         * CREATE TABLE personnel;

         Name for Attribute #1: number
         Data Type: integer
         Allow Null Values: y

         Ready .e

         Name for Attribute #2: name
         Data Type: char
         Maximum Number of Characters: 20
         Type: 1
         Allow Null Values: y

         Ready .e

         Name for Attribute #3: phone
         Data Type: char
         Maximum Number of Characters: 15
         Type: 1
         Allow Null Values: y

         Ready .e

         Name for Attribute #4: credit_limit
         Data Type: dollar
         Maximum Number of Dollar Digits: 6
         Type: 1
         Allow Null Values: y

         Ready .e

         Name for Attribute #5: .q

## 8.5 Interactive INSERT Command

The Interactive INSERT command adds new data to a table.

Syntax

  Syntax 1:

  |  |  | `INSERT [INTO] table [` | `|(*) |(item {, item})` | `|]; |`

  Syntax 2:

  `INSERT [INTO] table` | `|SET |SHOW` | `| |` | `|(*) |item {, item}` | `| {[:] |` | `|SET |SHOW` | `| |` | `|(*) |item {, item}` | `|}; |`

  where:

  `item` | is `attr [PRINT header]`

Notes

1. The asterisk (*) stands for all attributes.

2. If a list of attributes is specified, only those attributes named after `SET` or enclosed in parentheses are prompted for values. Attributes named after `SHOW` have their names displayed, but do not allow values to be entered for them. Unspecified attributes are set to null. If no attribute list is given, all attributes are prompted for.

3. If `headers` are specified, they are used as the prompts instead of the attribute names.

4. The following commands may be used with the Interactive INSERT.

   Table 8-7: Commands for Interactive INSERT

   Command | Description
   `.?` | Print this help file.
   `.!cmd` | Escape to the shell or invoke the operating system to execute the command `cmd`.
   `.^` | Go to previous attribute and re-prompt for its value.
   `.attr` | Go to the attribute attr and re-prompt for its value.
   `.~` | Set the attribute value to null.
   `."` | Set the attribute value to the value entered for the same attribute in the previous record.
   `.d` | Display all entered values for this record.
   `.e` | Enter the record into the table and begin prompting for the next record.
   `.n` | *Go on to the next record without entering the current one.
   `.p` | **Return to the previous record and re-prompt for attribute values (values already entered are not lost). It is not possible to back up more than one record using `.p`.
   `.q` | Quit the Interactive Interface and return control back to Empress.
   `.r` | Reset the current attribute values to null.
   `.u` | Undo the preceding `.x` command. Only valid during the record immediately after a `.x` command.
   `.v` | Change the prompting from terse to verbose or vice versa.
   `.x` | *Delete the current record.
   `.z` | Invoke the line editor.
   `.zz` | Invoke the system file editor.

   * valid only after `.p` command;
   ** not valid after `.p` command

Example

1. Simple Example

   The simplest command to insert data into the `loans` table is:

          INSERT loans;

   Remember that a null value may be entered in the Interactive Interface using a tilde (.~).

   To insert data into the `loans` table, giving values to only the `name` and `amount` attributes, you may use either one of the following:

          INSERT loans SET name, amount;

          INSERT loans (name, amount);

   To have all the values you have just entered displayed automatically before you insert each record permanently, use:

          INSERT loans SET name, amount SHOW name, amount;

   Now when you enter data, you see the following:

          name: Jones
          amount: 25
          name: Jones
          amount: $25.00

          Ready . e

          name:

   You now need to enter some data into the new table. Continue to use the Interactive Interface in `INSERT` mode.

          * INSERT INTO personnel;

       number: 10
       name: Kilroy
       phone: 426-9681
       credit_limit: 500.00

       Ready .e
       number: 5
       name: Mosca
       phone: 544-2243
       credit_limit: 250.00

       Ready .d
   | Display current record before inserting it.
       number: 5
       name: Mosca
       phone: 544-2243
       credit_limit: $250.00

       Ready .e
       number: 17
       name: Wladisslow
       phone: .^
       name: Wladisslow Wladislaw
       phone: 723-6073
       credit_limit: $50.00

       Ready .e
   | Go back up 1 attribute to correct the mistake. Correct Spelling.
       number: 3
       name: Jones
       phone: 667-2951
       credit_limit: 500.00

       Ready .e
       number: 4
       name: Peterson
       phone: 978-6060
       credit_limit: 250.00
       Ready .>number
   | Go to first attribute to correct mistake.
       number: 4 8
       name: Peterson .e
       number: .v
   | Make prompting verbose.
       number[integer]: 4
       name[char(20,1)]:
       phone[char(15,1)]: .^
       name[char(20,1)]: Scarlatti
       phone[char(15,1)]:961-7363
       credit_limit[dollar(6,1)]: 100.00

       Ready .e
   | <Return> Go up one and fill in missed value.
       number[integer]: .v
   | Make prompting terse.
       number: 9
       name: Jordan
       phone: 964-3335
       credit_limit: 250.00

       Ready .e

       number: .q
       Number of Records Entered = 7

       *

2. Insert Text Data

   Because the `TEXT` data type is a little more complex than the other data types, the following examples show how to enter data into, and update, `TEXT` attributes. Note the use of backslashes ( \ ) to enter new lines into the data.

          * INSERT customers;

          name: Mr. R. Lucas
          address: 1423 Beach Drive,\
          Bayville, CA 92308
          comments: Can always be sold something new and\
          flashy - likes chrome\
          and colored stuff, also little gadgets.

          Ready .e

          name: Mr. J. Phillips
          address: 63 Shore Bvde.,\
          Gull Point, CA 92309
          comments: Pays bills 2 months late - needs to be\
          threatened with a collection agency

          Ready .e

          name: Mr. B. Wilson
          address: 14 Valley View Drive,\
          Bayville, CA 92308
          comments: Good customer - give credit if he asks for it.

          Ready .e

          name: Ms. C. Marshall
          address: 12 Berkeley St.\
          Gull Point, CA 92309
          comments: Suggest vacuum & wash; she keeps 3 dogs.\
          Look for dog hair collecting in engine.

          Ready .e

          name: Mr. J. Lee
          address: 3260 Church St.,\
          Gull Point, CA 92309
          comments: Father pays billsÑcheck with R. Lee before\
          accepting a job,\
          but don't let Johnny know you're doing so.

          Ready .e

          name: .q

          Number of Records Entered = 5

## 8.6 Interactive UPDATE Command

The Interactive UPDATE command modifies existing data in a table.

Syntax

  Syntax 1:

  `UPDATE table [` | `` | `|(*) |(item {, item})` | `|] [where_clause]; |`

  Syntax 2:

  `UPDATE table` | `|SET |SHOW` | `| |` | `|(*) |item {, item}` | `| {[:] |` | `|SET |SHOW` | `| |` | `|(*) |item {, item}` | `|} |`

  `[where_clause];`

  where:

  `item` | is `attr [PRINT header]`
  `where_clause` | is `WHERE clause`

Notes

1. The asterisk (*) stands for all attributes. If no attributes are specified, Empress prompts for all attributes.

2. In syntax above, any attributes named after `SHOW` are presented as protected fields. Empress will display the values for these attributes, but will not allow them to be changed; only attributes named after `SET` may have their values changed. Note that the colon separating `SET` and `SHOW` lists is optional.

3. If `headers` are specified, they are used as the prompts instead of the attribute names.

4. If a `WHERE` clause is present, only those records satisfying it will be updated.

5. Remember that to enter a null value in the Interactive Interface you use a tilde (.~).

6. The following commands may be used with the Interactive UPDATE.

   Table 8-8: Commands for Interactive UPDATE

   Command | Description
   `.?` | Print this help file.
   `.!cmd` | Escape to the shell or invoke the operating system to execute the command `cmd`.
   `.^` | Go to previous attribute and re-prompt for its value.
   `.attr` | Go to the attribute `attr` and re-prompt for its value.
   `.~` | Set the attribute value to null.
   `."` | Set the attribute value to the value entered for the same attribute in the previous record.
   `.d` | Display all entered values for this record.
   `.e` | Enter the record into the table and begin prompting for the next record.
   `.n` | Go on to the next record without entering the current one.
   `.p` | **Return to the previous record and re-prompt for attribute values (values already entered are not lost). It is not possible to back up more than one record using `.p`.
   `.q` | Quit the Interactive Interface and return control to Empress.
   `.r` | Reset the current attribute value to its original value.
   `.u` | Undo the preceding `.x` command. Only valid during the record immediately after a `.x` command.
   `.v` | Change the prompting from terse to verbose or vice versa.
   `.x` | Delete the current record.
   `.z` | Invoke the line editor.
   `.zz` | Invoke the system file editor.

   ** not valid after .p command

Example

1. Simple Update

   The following dialogue illustrates the `UPDATE` mode of the Interactive Interface, first showing a way of deleting records and then some examples of changing attribute values. The record for Kilroy is deleted, and Mosca's `phone` and `credit_limit` are changed in the `personnel` table.

          * UPDATE personnel;

       number: 10
       name: Kilroyp
       hone: 426-9681
       credit_limit: 500.00

       Ready . x
   | Delete record.
       number: 5
       name: Mosca
       phone: 544-2243 426-9681
       credit_limit: 500.00 750.00

       Ready .d
   | Type in new values. Display updated record.
       number: 5
       name: Mosca
       phone: 426-9681
       credit_limit: 750.00

       Ready .e

       number: 17 .q

       Number of Records Deleted = 1
       Number of Records Updated = 1

       *

2. Update Few Attributes Only

   The `UPDATE` mode also allows alteration of only one or a few attributes, rather than all of them. In the following example, only the attributes `name` and `phone` of the `personnel` table are prompted for. The attribute `name` cannot be updated and is displayed to verify that the correct record is being updated. Only the attribute `phone` is modifiable. No other attribute values are prompted for.

          * UPDATE personnel SHOW name : SET phone;

       name: Kilroy
       phone: 426-9681

       Ready .n

       name: Mosca
       phone: 544-2243 382-2791
   | Change phone number.
       Ready .d
   | Display record.
       name: Mosca
       phone: 382-2791

       Ready .e

       name: Wladislaw
       phone: 723-6073 811-5110
   | Change phone number.
       Ready .e

       name: Jones .q

       Number of Records Updated = 2

       *

3. Conditional Updates

   A `WHERE` clause used with the `UPDATE` command limits the records updated to those satisfying the `WHERE` clause. In the following examples, first the entries for Mosca and Jones are updated, and then Wladislaw's `credit_limit` is changed.

          * UPDATE personnel
               WHERE name = 'Mosca' OR name = 'Jones';

          number: 5
          name: Mosca
          phone: 382-2791
          credit_limit: 250.00  500.00

          Ready .e

          number: 3
          name: Jones
          phone: 667-2951 454-6672
          credit_limit: 500.00

          Ready .e

          Number of Records Updated = 2

          * UPDATE personnel SHOW name : SET credit_limit
               WHERE name = 'Wladislaw';

          name: Wladislaw
          credit_limit: 50.00 10.00

          Ready .d

          name: Wladislaw
          credit_limit: 10.00

          Ready .e

          Number of records updated = 1

          *

4. Update and Verification of the Record

   Note that more than one attribute may be listed after `SET` or `SHOW`, and that any number of `SET` lists and `SHOW` lists may be used, separated by colons. For example, the following command provides a way to automatically verify that the correct record is being updated, and check the new values entered without using the `.d` command:

          UPDATE loans SHOW name, date, amount : SET DATE, AMOUNT
             : SHOW date, amount;

5. Update Text Data

   When a `TEXT` attribute is displayed for updating, only the first display length line is printed out, followed by a message indicating how many other characters there are in the attribute value. Either the Empress line editor or an operating system editor could then be invoked by the `.z` or `.zz` command to make the alteration. (If the entire attribute value is only slightly longer than the display length, the entire value will be printed out.)

   The following example shows how to alter the address for Mr. Lucas with the Empress line editor, and how to use `/bin/ed` to alter the comments about him:

          * UPDATE customers WHERE name MATCH '*lucas*';

       name: Mr. R. Lucas
       address: 1423 Beach Drive,
       Bayville, CA 92308 .z
       1423 Beach Drive,
          8
   | Change the 3 to 8.

       1428 Beach Drive,
       Bayville, CA 92308
       comments: Can always be sold something
       [75 More Characters] .zz
       101
       1,$p
       Can always be sold something new and flashy - likes chrome and
       colored stuff, also little gadgets.
       a
       Try to sell him some of the new Italian car seat covers
       - he'll probably take them.
       w
       186
       q

       Ready .e

       Number of Records Updated = 1
       *

## 8.7 Using Editors

The preceding examples have shown correcting mistakes in `INSERT` mode, or altering attribute values in `UPDATE` mode, by re-typing the entire attribute value. If the alteration is not very complex, the Empress line editor provides a convenient way to correct or update attribute values. To invoke the line editor from the Interactive Interface, `.z` is used. If the corrections are complex, or the data value is very long, such as text data, it is usually easier to invoke a system editor by using the `.zz` command. Which editor is invoked depends on the value of the Empress system variable `MSEDITOR`. Setting system variables is described under Empress System Variables in the Empress: Database Administrator's Guide.

The following example shows how to correct a mistake in Mosca's name while entering data into the `loans` table using the line editor.

       * INSERT loans;

       name: Moosca
       date: .^
       name: Moosca .z
       Moosca
        #

       Mosca
       date: 2 February 1983
       amount: 150

       Ready .e

       name: .q

       Number of Records Entered = 1

       *

The following continuation of our example shows how to use the operating system editor to correct a mistake in entering a `supplier` into the`auto parts` table. The system editor is, of course, most useful for updating a `TEXT` attribute, which is shown in the next section.

       * INSERT INTO 'auto parts'

       supplier: AAA Automotive
       phone: .^
       supplier: AAA Automotive .zz

    .
    .
    .
| Invoke the system editor.

       part name: hubcap
       part no.: 9836653
       price: 18.40

       Ready .e

       supplier: .q

       Number of Records Entered = 1

If an illegal character (backspace, escape, or control character) is entered while the Interactive Interface is being used, or the data entered is invalid for that data type, the line editor will be invoked immediately, entering edit mode. For example, if you enter an invalid date while inserting data into the loans table, the following happens:

       * INSERT loans;

    name: Mosca
    date: 12 mey 1990

    ** Conversion Error *** invalid month
    ** Entering LINE EDITOR ***
| <Interrupt> will abort editing.
    12 mey 1990
        a

    12 may 1990
    amount: 32.50

    Ready .e

If you are working in UNIX and you do not wish to edit the incorrect value, simply press the <Interrupt> key (sometimes labeled <Delete> or <Rubout>). Note that on some operating systems, you will need to press <Return> after the <Interrupt> key.