CHAPTER 8: The Interactive Interface

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

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.

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

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

Table 8-3: Interactive Interface Record Commands

Table 8-4: Interface Commands

* cannot be used for CREATE TABLE or ALTER TABLE.

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.

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

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
   
   

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
   *
   
   

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

.
.
.

   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 ***

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.