# CHAPTER 4: Data Definition Commands

## 4.1 Introduction

The Data Definition Language (DDL) commands are commands that deal with the structure of the database. A summary of the available DDL commands is listed below:

Table 4-1: Data Definition Language Commands

Command | Description
`CREATE TABLE` | Creates a new table including its name and the name and data type of each of its attributes.
`DROP TABLE` | Removes an existing table.
`ALTER TABLE` | Changes the structure of an existing table without having to dump and re-load its records. This also includes enabling/disabling trigger, defining table type for replication and changing the checksum setting of a table.
`RENAME` | Changes the name of a table or attribute.
`DISPLAY TABLE` | Shows the structure of a table.
`CREATE INDEX` | Sets up a search-aiding mechanism for an attribute.
`DROP INDEX` | Removes an index on an attribute.
`CREATE RANGE CHECK` | Sets up data validation checks on an attribute.
`DROP RANGE CHECK` | Removes data validation checks on an attribute.
`CREATE REFERENTIAL` | Sets up data referential constraints on attributes.
`DROP REFERENTIAL` | Removes a data referential constraints from attributes.
`CREATE VIEW` | Creates a logical table from parts of one or more tables.
`DROP VIEW` | Removes a logical table.
`CREATE COMMENT` | Attaches a comment to a table, attribute, module or trigger.
`DROP COMMENT` | Removes a comment on a table, attribute, module or trigger.
`DISPLAY PRIVILEGE` | Shows access privileges for a table.
`DISPLAY GRANT PRIVILEGE` | Shows privilege grant options for a table.
`GRANT PRIVILEGE` | Changes access privileges for tables or attributes.
`REVOKE PRIVILEGE` | Removes table or attribute access privileges.
`LOCK LEVEL` | Sets the level of locking on a table.
`CREATE MODULE` | Creates the definition of a persistent stored module into the data dictionary.
`UPDATE MODULE` | Links a persistent stored module definition with the module shared library.
`DROP MODULE` | Removes a persistent stored module definition from the data dictionary.
`DISPLAY MODULE` | Shows the persistent stored module definition.
`CREATE TRIGGER` | Sets up trigger events into data dictionary.
`DROP TRIGGER` | Removes a trigger event from the data dictionary.
`DISPLAY DATABASE` | Shows the tables in the database.
`CREATE REPLICATION MASTER` | Associates a replicate table with a list of master table(s).
`DROP REPLICATION MASTER` | Dis-associates a replicate table with a list of master table(s).
`CREATE REPLICATION REPLICATE` | Associates a master table with a list of replicate table(s).
`DROP REPLICATION REPLICATE` | Dis-associates a master table with a list of replicate table(s).
`CREATE REPLICATE TABLE` | Creates a replicate table from a master table.
`CREATE ROLE` | Creates a role in a database.
`DROP ROLE` | Removes a role from a database.
`GRANT ROLE` | Grants the use of one or more roles to one or more users/roles.
`REVOKE ROLE` | Revokes one or more roles from one or more users/roles.
`DISPLAY ROLE` | Displays role(s) in a database.

## 4.2 CREATE TABLE

The CREATE TABLE command creates a table in a database. It involves naming the table, naming its attributes, specifying the data type of each attribute and the location of the table. If not specified, options are given reasonable defaults.

Syntax 1

      CREATE [TABLE] table [|   (item {, item} [,constr {,constr}]) |]
                            |SET item {, item} [,constr {,constr}]  |

                                             [LOCATE [IN] location];

Syntax 2

         CREATE [TABLE] table [LOCATE [IN] location][FROM file];

  where:

  `item` | `attr [data_type [(param {,param})] [[NOT] NULL] [attr_constr]]`
  1. `constr` | is a Primary Key or Foreign Key constraint. It is one of:
         PRIMARY KEY [key_name] [ON] (attr {, attr}) [| BTREE      |]
                                                      | TIMESERIES |
  2.     FOREIGN KEY [key_name] [ON] (attr {, attr}) [| BTREE      |]
                                                      | TIMESERIES |
                      REFERENCES table (attr {, attr})
  `location` | is a valid and existing directory name. This is any valid system pathname or file specification for a directory. Using a full file specification is recommended. If `location` is the keyword `DATABASE` or the empty string (""), the table is created in the current default database directory.
  `data_type` | is one of the following listed in Table 4-1, where the parameters are enclosed in parentheses. If no `data_type` is specified, it defaults to `INTEGER`.
  1. `attr_const` | is a Primary Key or Foreign Key constraint. It is one of:
           PRIMARY KEY  [key_name] [|BTREE      |]
                                    |TIMESERIES |
  2.       FOREIGN KEY [key_name] [|BTREE      |] REFERENCES table (attr {, attr})
                                   |TIMESERIES |

  Table 4-2: Data Types Parameters and Defaults

  Data Types | Parameters | Defaults
  `CHAR` | `(length, char_type)` | (25,1)
  `BULK` | `(#_bytes_printed, primary_storage, overflow_storage, #_of_directly_accessible_extents)` | (20,20,20,1)
  `TEXT` | `(display_length, primary_storage, overflow_storage, #_of_directly_accessible_extents)` | (20,20,20,1)
  `DATE` | `(date_type)` | (0)
  `TIME` | `(type)` | (0)
  `MICROTIMESTAMP` | `(type)` | (0)
  `DECIMAL` | `(maximum_digits, decimal_places)` | (1,0)
  `DOLLAR` | `(maximum_size, dollar_type)` | (4,1)
  `REAL` | `(precision)` | (n) *
  `FLOAT` | N/A | N/A
  `LONGFLOAT` | N/A | N/A
  `SHORTINTEGER` | N/A | N/A
  `INTEGER` | N/A | N/A
  `LONGINTEGER` | N/A | N/A

  If no parameters are specified for the appropriate data types, they are set to default values.

  `* n` is machine dependent and equal to the precision of the float on the machine.

  Also refer to the chapter on Data Types in this manual.

Notes

1. If no `item` is specified in the `CREATE TABLE` command, the Interactive Interface is invoked. This is the simplest method for creating tables, and is described in The Interactive Interface chapter of this manual.
2. If a database name is specified with the table name (as prefix to the table name, ie, `database:table`), the new table will be created under the specified database. The database must be an existing Empress database. If no database name is specified, the table is created in the current database.
3. Table can be created in directories other than the default current database directory, it is often referred to as Remote Tables. The default location of a table is the current database directory unless otherwise specified with the `LOCATE` option. Although a relative pathname or file specification may be given for the directory, using a full pathname is recommended. The directory may reside on another file system.

   If location specified is the keyword `DB` or the empty string (""), it refers to the current default database directory.

   It is important that a given directory contains tables from one database only. Using a directory to hold tables from more than one database may lead to corruption of your data.

   The location of a table can be determined with the `DISPLAY TABLE` command with the `ALL` option.

   Existing tables can be relocated using the `ALTER TABLE` command.

   The import/export facilities provided by Empress will not preserve the locations of remote tables. On import, all tables will be located in the default directory. Tables must be relocated as needed.

   Although databases without remote tables can be removed simply by removing the database directory using the appropriate operating system command, databases with remote tables should be removed with the `emprmdb` utility. This ensures that all operating system files associated with the database are removed. A manual page for the utility will be found in the Empress: Manual Pages.

4. If an attribute is specified as `NOT NULL`, Empress will not allow `NULL` as a data value for it.
5. A table can also be created from a file. The information for the attributes is contained in the given file and must be in the same format as produced by the `DISPLAY TABLE DUMP INTO file;` command.
6. Empress will not allow two tables with the same name to be created in the same database.

Privileges Required

  `INSERT` privilege on `sys_tables`.

Example

1. Create a Simple Table

   To define the table`personnel` with attributes `number`, `name`, `phone`, and `credit_limit` such that `number` can hold integers up to 32767, `name` can hold up to 25 characters, `phone` can hold up to 15 characters, and `credit_limit` can hold a dollar value up to $999,999.99, use the command:

   |  |  | `CREATE` | `TABLE personnel ( number INTEGER, name CHAR (25,1), phone CHAR (15,1), credit_limit DOLLAR (6,1));`

   The attribute `number` is defined as `INTEGER`, `name` and `phone` are defined as `CHAR` type 1 with maximum lengths 25 and 15 respectively, and the attribute `credit_limit` is defined as `DOLLAR` capable of storing up to six dollar figures.

2. Create Table Where Attribute Name Is an Empress Keyword

   To define the table `loans` with attributes `name`, `date`, and `amount`, use:

   |  |  | `CREATE` | `TABLE loans ( number INTEGER, name CHAR (25,1), date DATE (1), amount DOLLAR (6,1));`

   The attribute `number` is defined as `INTEGER`; `name` is defined as `CHAR` type 1 with a maximum length of 25;`date` as `DATE` type 1 with months shown in full; and`amount` as `DOLLAR` capable of storing six dollar figures.

   Note that attribute name `date` is an Empress keyword. Such attribute name should be treated as a complex name.

3. Create Table with Complex Name and with Range Check on Null Value

   To define the `auto parts` table, use:

   |  |  | `CREATE` | `TABLE "auto parts" ( supplier CHAR (20, 1) NOT NULL, phone CHAR (8, 1), ATTR "part name" CHAR (15, 1), ATTR "part no." LONGINTEGER, price DOLLAR (4, 1));`

   The `NOT NULL` specification for the `supplier` attribute ensures that a supplier name is always entered for each part. Note how the complex table and attribute names must be enclosed in quotes.

4. Create Table with Text Attribute

   The `customers` table contains one `CHAR` and two `TEXT` data type attributes. Ensure that a customer name is always entered by defining `name` as `NOT NULL`.

   |  |  | `CREATE` | `TABLE customers ( name CHAR (20, 1) NOT NULL, address TEXT (30, 124, 30, 1), comments TEXT (25, 0, 80, 1));`

   The parameters for the `address` attribute were chosen because we expect most of the addresses to require no more than four lines, with each line no more than 30 characters long. For `address`, we therefore use a display length of 30, which means any lines longer than 30 characters will be split up. The primary storage length we use is 124, which means there is enough storage for 124 characters in the main table. This is calculated as (4 x (30 + 1)) four 30-character address lines plus an extra character at the end of each line to store the new line itself. The overflow storage length is 30, which means that each new line added to overflow storage can hold up to 30 characters. Since there is enough storage for four lines in the main table, the overflow storage will be used very little, but is still available for unusually long addresses.

   The parameters for the `comments` attribute were chosen as a convenient display length, so that the entire table will print out nicely on ordinary 81/2" x 11" paper. Since comments will be quite variable in length, there is no point in reserving very much space in the main table for this attribute, and the primary storage length is very short on characters. Overflow storage will be used quite often, so the overflow storage length is moderately long with the width of a normal terminal screen. An overflow storage length of 80, or a multiple of 80, is a good length for attributes consisting of plain text, since most people find it natural to break their lines at the screen edge when entering text.

## 4.3 DROP TABLE

The `DROP TABLE` command removes a table from the database. Be caution that removing the table from the database also removes all its records.

Syntax

      DROP table;

Notes

1. Since this command is used to remove the table definition and its records from the database, it should be used with extreme care.
2. The `DROP TABLE` command does not respect referential delete constraints.
3. When a table is dropped, no warning is given of the existence of any views on the table.
4. A view can be dropped with this command.
5. Never use the `DROP TABLE` command on a data dictionary table.

Privileges Required

  `DROP` privilege.

Example

  To delete the table `personnel`, use:

         DROP TABLE personnel;

## 4.4 ALTER TABLE

The `ALTER TABLE` command changes the structure of an existing table. This includes:

- new attributes may be added,
- existing ones removed,
- data types or parameters of existing attributes altered,
- relocate an existing table,
- enable or disable triggers,
- change trigger priority,
- enable or disable replication master entry (entries),
- change replication master order,
- change table type to master, replicate or normal table and,
- change the checksum setting of a table.

Syntax 1

  |  |  | `ALTER [TABLE] table` | `[action { [:] action}];`

Syntax 2

         ALTER [TABLE] table LOCATE [IN] location;

Syntax 3

  |  |  | `ALTER [TABLE] table` | `|ENABLE |DISABLE` | `| |` | `|ALL TRIGGERS |TRIGGER trigger {, trigger}` | `|; |`

Syntax 4

         ALTER [TABLE] table CHANGE TRIGGER trigger {, trigger}

            PRIORITY priority;

Syntax 5

         ALTER [TABLE] replication_table CHANGE REPLICATION MASTER ORDER

          replication_master_info order {, replication_master_info order};

Syntax 6

        ALTER [TABLE] table CHANGE TABLE TYPE TO

             |MASTER [TABLE] [FOR SUBSET ON (attrib [, attrib ...])]|;
             |REPLICATE [TABLE]                                     |
             |NORMAL [TABLE] [WITH FORCE]                           |

Syntax 7

         ALTER [TABLE] replication_table |ENABLE | REPLICATION MASTER
                                         |DISABLE|

                  replication_master_info {, replication_master_info};

Syntax 8

         ALTER [TABLE] replication_table

            |ENABLE | ALL REPLICATION |MASTERS |;
            |DISABLE|                 |MASTER  |

where:

  `action` | can be a combination of :
        |ADD    item {, item} [BEFORE attr]|
        |CHANGE item {, item}              |
        |DELETE attr {, attr}              |
        |ADD    [|LONGVAR|] CHECKSUM       |
                 |RECORD |
        |DELETE [|LONGVAR|] CHECKSUM       |
                 |RECORD |
        |ADD     constr1                   |
        |DELETE  constr2                   |
  `item` | `attr [data_type [(param {, param})] [[NOT] NULL]]`
  1. `constr1` | is a Primary Key or Foreign Key constraint. It is one of:
         PRIMARY KEY [key_name] [ON] (attr {, attr}) [| BTREE      |]
                                                      | TIMESERIES |
  2.     FOREIGN KEY [key_name] [ON] (attr {, attr}) [| BTREE      |]
                                                      | TIMESERIES |

                            REFERENCES table (attr {, attr})
  `constr2` | is one of:
        | PRIMARY KEY                     |
        | FOREIGN KEY [ON] (attr {, attr}) |
  `location` | is a valid and existing directory name.
  `data_type` | is one of the following listed in Table 4-2, where the parameters are enclosed in parentheses. If no `data_type` is specified, it defaults to `INTEGER`.
  `trigger` | is a valid and existing trigger name.
  `priority` | is a float number that can be positive or negative. For example, -1.2, 0, 1 , 2, 2.3 ... etc. The greater number has higher priority.
  `replicate_table` | is the table name that is going to be replicated from a master table.
  `replication_master_info` | Indicates access information to the Replication Master Table. It consists of replication master server name, database name and table name of the replication master table. It's format is:
      | server_name : database_name : table_name  |
      | server_name : database_name .. table_name |
  `replication_replicate_info` | Information to access Replication Replicate Table. It consists of host name, database name and table name of the Replication Replicate Table. It's format is:
       | host_name : database_name : table_name  |
       | host_name : database_name .. table_name |
  Here, the `host_name` is the name of the host where the database resides. If it is in a different network than the `replication_table`, complete network address must be specified. `host_name` can also be IP address. The `database_name` is supposed to be absolute path to a replication database. If not, Empress issues a warning message, and considers this to be a logical database name.
  `order` | is a float number. The order number of replication master table must be unique. The smallest order number is considered first for becoming the "chosen" replication master table.

Notes

1. If no specifications are given after the table name, an interactive `ALTER TABLE` is invoked, which prompts for all the changes. Examples of this are given in the chapter The Interactive Interface of this manual.
2. The command `ALTER table ADD attr;` adds the attribute to the end of the table with integer as the default data type.
3. The `ALTER TABLE` command will create a new table file (`.rel` file) within the database directory before removing the old table file. The disk space required by this command is therefore at least twice the size of the table and may be more if new attributes are added to the table. Once the `ALTER TABLE` is complete, the original table will be deleted so its space is reclaimed by the operating system.

   Since the `ALTER TABLE` command creates a new table file, the UNIX permissions and the ownership on the new file may change depending on the `umask` setting (in UNIX) and on the owner ID.

   If the `ALTER TABLE` command changes the data type of an attribute, all records within the table must be successfully converted for the `ALTER TABLE` to complete. If there are records that do not convert to the new data type, the `ALTER TABLE` will be aborted and the original table remains unchanged.

   Range checks should be removed from attributes before an `ALTER TABLE` command changes the data type of an attribute. The range check cannot be converted along with the data type.

   It is possible to interrupt the `ALTER TABLE` command using certain keyboard interrupt such as `<CTRL+C>`. The `ALTER TABLE` operation is "secure"; it can be interrupted without the possibility of damaging the database. The interrupt which results from using `kill -9` will damage the database.

4. When adding `CHECKSUM` to a table, `LONGVAR` is used to create checksum for the overflow (`.dtf`) file(s) of `BULK` or `TEXT` data type. `RECORD` is used for create checksum on the main (`.rel`) data file. If neither is specified, the checksum will apply to both main (`.rel`) data file and the overflow (`.dtf`) file(s). The checksum added by `ALTER` command is always 4 bytes.
5. The command `ALTER table LOCATE IN location` is used to move the table to another directory. If `location` is the keyword `DB` or the empty string (""), the table is moved to the current default database directory. Refer to the `CREATE TABLE` command for further information on remote tables.
6. This command has no effect on a view.
7. Altering a table by adding and/or changing the attributes, has no effect on triggers. Triggers will function correctly under the new table structure automatically. However, if an attribute is deleted, then all the triggers explicitly connected to that attribute will be deleted, the triggers connected to all attribute implicitly (no attribute specified) keep unchanged.

8. Trigger can be disabled when it is not in used and enabled it when is needed.

9. When multiple triggers are created for the same event, the priority within the trigger definition will determine the order of the trigger procedures to be executed. The priority is set to 0.000 when trigger is created. Use the `ALTER TABLE` command to change the priority of the trigger.

10. Commands describes in Syntax 5, 6, 7 and 8 are related to Empress Replication. Refer to [Empress Replication User's Guide: References] for more information on these commands.

11. Under no circumstances should you attempt to execute an `ALTER TABLE` command on a table in the data dictionary.

Privileges Required

  `ALTER` privilege.

Example

1. Add an Attribute

   To add a `DATE (1)` attribute called `due_date` to the `loans` table, use the following command:

   |  |  | `ALTER` | `loans ADD due_date DATE (1);`

2. Change a Property of an Attribute

   To shorten the `name` attribute in loans from 25 characters to 20, as well as add the new attribute, use:

   |  |  | `ALTER` | `loans ADD due_date DATE (1) CHANGE name CHAR (20, 1);`

   A `DISPLAY TABLE` command on the table will then show:

          *** Table: loans ***

          Attributes:
            number               integer
            name                 char(20,1)
            date                 date(1)
            amount               dollar(6,1)
            due_date             date(1)

## 4.5 RENAME

The `RENAME` command is used to change the names of existing tables or attributes.

Syntax

  Syntax 1:

  |  |  | `RENAME TABLE table [` | `| TO | | AS |` | `] new_table_name;`

  Syntax 2:

  |  |  | `RENAME ATTR[IBUTE] table.attr [` | `| TO | | AS |` | `] new_attr_name;`

  where:

  `new_table_name` | is a legal table name.
  `new_attr_name` | is a legal attribute name.

Notes

1. When renaming an attribute, the original attribute name must be qualified by its table name (ie. prefixed by a table name). However, the new attribute name must not be qualified by the table name.

2. Renaming a table and/or attribute name has no effect on the existing triggers. Triggers will function correctly under the new name automatically.

3. A view name can also be changed with this command.

Privileges Required

  `ALTER` privilege.

Example

1. Rename a Table

   To change the name of the `personnel` table to `employees`, use the command:

          RENAME personnel TO employees;

2. Rename a Complex Table Name

   To change the name of the `auto parts` table to `parts`, use the command:

          RENAME "auto parts" TO parts;

3. Rename a Complex Attribute Name

   To rename the`part no.` attribute to`numbers`, use:

          RENAME parts."part no." TO numbers;

4. Rename a Simple Attribute Name to a Complex Attribute Name

   To rename the attribute `number` in the `personnel` table as `employee number`, use:

          RENAME personnel.number TO "employee number";

## 4.6 DISPLAY TABLE

The `DISPLAY TABLE` command prints the names and data types of the attributes for the named table and shows other information on the table such as whether any attributes are indexed, any comments, range checks or referential constraints, and, if a view, the `SELECT` statement that defines the view.

Syntax

  `DISPLAY table [ON [ATTR]` | | `attr |[NOT] match_op pattern` | `|] |`

  |  |  | `[ALL] [DUMP]` | `[` | `|INTO |ONTO` | `| file]; |`

  where:

  `match_op` | is one of `LIKE`, `MATCH`, `!MATCH`, `SMATCH`, or `!SMATCH`.
  `pattern` | is any pattern accepted by a `WHERE` clause; it must be given as a quoted string.
  `file` | is any filename.

Notes

1. The `DISPLAY TABLE` command without the `ALL` option shows the attributes and any comment, indices and referential constraints on the table. With the `ALL` option, the display includes trigger definitions, comments on attributes, and any range checks. The `ALL` option always displays the creator of the table, the lock level, the checksum variable value (refer to Empress: Database Administrator's Guide manual under Dirty Read), the table number, the record size and the number of records in the table.

2. To restrict the list to certain attributes, use the `ON attr` or the pattern matching options.

3. If `DUMP` is used with the `DISPLAY TABLE` command, the output is condensed and includes information suitable for creating the table from a file. The values in the dump file are separated by a special string recognized by Empress as a value separator. This string is specified by the Empress system variable `MSVALSEP`, and the default is normally <Ctrl+V> (octal 26).

   `DUMP` is most often used when you intend to create a new table from information dumped into a file. Note that an attribute that can take on a null value will be dumped with a value of `n` while an attribute that cannot take on a null value will be dumped with a value of `y`. These values answer the question: `Prohibit null values for this attribute?`.

4. If `INTO file` or `ONTO file` are used, the output is sent to the named file rather than to the terminal. `INTO` overwrites any existing file, while `ONTO` appends to it. Both create the file if necessary.

5. `DISPLAY TABLE` can also be used to display a view.

6. To find the size of the table, do a `DISPLAY ALL` on the table, find out the table number, then use the system command to find the file size of the corresponding system file. For example, in UNIX, The command `ls -l` will do a long listing of the file. If your table number is 15, the corresponding file name for the table will be `0015.rel`, and its size in bytes may be found in the long listing.

Privileges Required

  `DISPLAY` privilege.

Example

1. Find All Attributes in a Table

   To print out the attributes for the `loans` table, use:

          DISPLAY loans;

   which produces the following output:

          *** Table: loans ***

          Attributes:
            number                integer
            name                  char(25,1)
            date                  date(1)
            amount                dollar(6,1)

2. Display Detail Information about a Table

   To include information on the creator, lock level and number of records for the `loans` table, use:

          DISPLAY loans ALL;

   which produces the following output:

          *** Table: loans ***

          Attributes:
            number                integer
            name                  char(25,1)
            date                  date(1)
            amount                dollar(6,1)

          Creator: joe
          Lock Level: RECORD

          Table #: 8
          Records: 12
          Record size: 36

3. Display a Specific Attribute Information in a Table

   To list only information on the `name` attribute in the `loans` table, use:

          DISPLAY loans ON name ALL;

   which produces as output:

          ** Table: loans ***

          Attributes:
            name              char(25,1)

          Creator: joe
          Lock Level: RECORD

          Table #: 8
          Records: 12
          Record size: 36

4. Capture the Table Information into an Operating System File

   To place the description of the table `personnel` into a file called `p_desc`, use:

          DISPLAY personnel INTO 'p_desc';

## 4.7 CREATE INDEX

The `CREATE INDEX` command creates an Empress data structure, called an index, on an attribute or group of attributes. An index speeds up access to the attributes pointed to. Indices may be simple pointing to one attribute or composite pointing to more than one attribute. An index may not be created on variable length data such as `TEXT` and `BULK` data types.

Syntax

  `CREATE [` | `|UNIQUE |DISTINCT |[(]primary[,] overflow[)]` | `|] [index_method] INDEX [name] | |`

  |  |  | `[ON]` | `|table (attr, {, attr}) |table SET (attr, {, attr})` | `|; |`

  where:

  `name` | is the index name given by the user.
  `primary` | is the number of pointers to the indexed attribute in the main index file. The default value is set by the Empress administrative variable `MSDBINDEXPRIMARY` which has a value of 2.
  `overflow` | is the number of pointers to the indexed attribute per overflow segment in the index overflow file. The default value is set by the Empress administrative variable `MSDBINDEXOVERFLOW` which has a value of 15.
  | `index_method` |
  is one of: | `|BTREE |TIMESERIES` | `| |`
  The default is `BTREE`.

Notes

1. `DISTINCT` may be used as a synonym for `UNIQUE`. If `UNIQUE` is used, no duplicate values or null values are allowed for the attribute(s).

2. Composite indices may be created on two or more attributes at once. These indices are intended to ensure that the group of values for those attributes in the record is unique as well as increasing retrieval speed.

3. An index is stored in two areas, a primary area and an overflow area. Pointers to duplicate values of an indexed attribute are stored in the primary area while possible, then in the overflow area. The overflow area can grow indefinitely. `primary` sets the number of duplicate entries allowed for each attribute in primary storage, and `overflow` sets the amount of space (in terms of index entries) granted in each increment of the overflow area.

   Note that if `overflow` is greater than 0, unlimited duplicates and nulls are allowed. If `overflow` is specified as zero, the maximum number of duplicates for each attribute is set by `primary`, and no null values are allowed. Thus, if `primary` is 1 and `overflow` is 0, only one value is allowed for each attribute the same as specifying `UNIQUE`.

4. An index may be created which allows no duplicate or null attribute values, or which allows a maximum number of duplicates for each attribute. For these attributes, duplicates above the limit or nulls, cause an error message on input.

   If you are creating a unique or limited index on an attribute of a table which already contains data and there are too many duplicate values for that attribute, or there is a null value for the attribute, Empress will type out an error message of the following form for each duplicate or null value:

          *** User Error *** duplicate value for 'attribute_value'

   where `attribute_value` is the duplicated value. The index will not be created.

   If data is being loaded from a file into a table with a unique or limited index and an excess duplicate of an existing value for the indexed attribute is encountered, Empress will produce a single error message in the form of:

          *** User Error* ** duplicate value for 'attribute name' near line ###

   where `###` is the line number for the data file.

   An Interactive Interface `INSERT` or `UPDATE` command results in the following error message for excess duplicates when you attempt to enter the record:

          *** Duplicate Value *** attribute 'attribute name'

   The `Ready .` prompt will be re-issued to allow alteration of the duplicate value.

   When inserting into a table that already exists in the database, the Empress import utility, `empimpt`, will stop inserting records if it encounters a record that would cause duplicates unless the `-e` option has been specified. Refer to the utility `empimpt` or the discussion on `empimpt` in the Empress: Database Administrator's Guide.

5. If `index_method` is not specified, the default is `BTREE`.

   The `BTREE` indexing method is recommended for the attributes in which:

   - Index values reoccur periodically.
   - Distribution of index data is fixed.
   - Key index values do not change drastically within a span of time.

   The `TIMESERIES` indexing method is recommended for the attributes in which:

   - Key index values change dramatically over a period of time.
   - Index data is always a new value; no index data is repeated. For example, timestamp.
   - Key index values consist of a large range of values and shift the distribution of the index values to one side of the index tree.

6. For easy reference, it is recommended that you name your indices. The syntax for index names is the same as for any legal table or attribute name.

7. Attributes that are used often for searches should have an index created for them if the table is larger than about 2,000 bytes.

8. Attributes which should not be indexed are:

   - Small tables. For small tables, retrieval is so fast there is nothing to be gained by creating an index. An index, however, takes up additional storage in the database, and in some cases may take up almost as much storage as the table. There is thus a tradeoff between search efficiency and storage space used, with faster searches requiring more storage.

   - An attribute that has only a small number of different values on it. Indexing such an attribute may actually slow down retrieval speed. Examples of the types of attributes which should not be indexed are things like sex which can only have the values `male` or `female`; marital status which has `single`, `married`, `divorced`, or `widowed` for values; and weekday which has only the seven days of the week and so on.

   - Due to buffer limitations, type `CHAR` attributes of over 2000 characters should not be indexed.

9. The `DISPLAY TABLE` command will show the indices on a table.

10. An index cannot be created on a view.

11. If interrupts, i.e. <Rubout>, <Delete>, or <Ctrl+C> is pressed during a `CREATE INDEX` command, a warning message is printed on the terminal, and the `CREATE INDEX` can be aborted by pressing another <Rubout> or <Ctrl+C> within three seconds.

    Creating an index on a very large table may take an appreciable amount of time, so the ability to abort it by non-drastic means is an advantage. The three-second delay guards against accidental interrupts and allows you a chance to change your mind if the interrupt is intentional.

    When an interrupt is pressed, the following message is displayed:

           *** Interrupt *** hit another to abort

    If another interrupt is not pressed within three seconds, the following message appears:

           *** Resuming ***

    and the `CREATE INDEX` continues.

12. `SELECT` commands will not always use the indices on a table. An index on an attribute will only be used if the attribute appears in the `WHERE` clause of the command and is appropriately situated in the `WHERE` clause. Viewing the `WHERE` clause as a binary tree in which the `AND` or `OR` operation with the lowest precedence is the root, and starting from the top of the tree, boolean expressions on both sides of an `AND` operator will use indices, but boolean expressions on neither side of an `OR` operator will use indices. Simple `WHERE` clauses with no `AND` or `OR` operators will use the indices on the attributes. Empress has a variable called `MSQUERYPLAN` which when set, provides an analysis of queries with regard to the indices used.

13. An attempt to access a table through an indexed attribute causes an attempt to place a lock on the index. This is true regardless of the locking on the table. The number of attempts that will be made to create the lock is controlled by the variable `MSINDEXRETRY` (the default is 5) and the interval in seconds between retries is controlled by the variable `MSINDEXSLEEP` (the default is 1).

    In a `SELECT`, if the index cannot be locked, access to the table will proceed without using the index. In the case of an update type operation, the command will not proceed.

Privileges Required

  `INDEX` privilege.

Example

1. Create a Simple Index

   To create the simplest kind of index (no name, for one attribute only, allowing unlimited duplicates and nulls) on the attribute `name` in the table `personnel`, use either one of these command:

          CREATE INDEX ON personnel (name);

2. Create an Unique Index

   To create a unique index on the attribute `number` in the `personnel` table and guarantee that all personnel numbers are unique, use:

          CREATE UNIQUE INDEX ON personnel (number);

3. Create an Index with Limited Duplicate Value

   To limit the number of concurrent `loans` (maximum 3 loans per employee) to any employee, create an index on `name` which specifies `primary` as `3` and `overflow` as `0`:

          CREATE (3, 0) INDEX ON loans (name);

4. Create a Named Index

   Create an index called `employee` on the `name` attribute of the `personnel` table with:

          CREATE INDEX employee ON personnel (name);

5. Create a Composite Unique Index

   To ensure that each employee's name and number combination is unique, create a composite unique index called `pers` on the attributes `number` and `name`:

          CREATE UNIQUE INDEX pers ON personnel (number, name);

6. Display Index Information

   If the `personnel` table has a simple unique index on `number`, a simple non-unique index on `phone`, a simple index named `employee` on `name`, and a unique composite index named `pers` on `number` and `name`, then a `DISPLAY TABLE` command shows:

          DISPLAY personnel;

          *** Table: personnel ***

          Attributes:
            number                 integer
            name                   char(25,1)
            phone                  char(15,1)
            credit_limit           dollar(6,1)

          Indices:  UNIQUE ON (number)
                    NORMAL (2,15) employee ON (name)
                    NORMAL (2,15) ON (phone)
                    UNIQUE pers ON (number, name)

## 4.8 DROP INDEX

The `DROP INDEX` command deletes an index and frees the storage used by it.

Syntax

  `DROP INDEX` | `|name [[ON] table]] |table SET attr {, attr} |table (attr {, attr}` | `|; | |`

Notes

1. If an index is named, its attribute(s) need not be specified when it is dropped. If it is not named, all the index's attributes must be specified in order to drop the index.

2. If the index is named but the table is not, and an index with that name exists on more than one table, then Empress will prompt you with the first table name, asking if you wish to drop the index on that table. Type `y` to drop the index and `n` to be prompted with the next table. Once you drop an index you will not be prompted with another table.

Privileges Required

  `INDEX` privilege.

Example

1. Delete a Simple Unnamed Index

   To delete the simple, unnamed index on `name` in `personnel`, use:

          DROP INDEX ON personnel (name);

2. Delete a Named Composite Index

   To delete the named composite index `pers` on the `number` and `name` attributes of the personnel table, use either:

          DROP INDEX pers;

   or

          DROP INDEX ON personnel (number, name);

## 4.9 CREATE RANGE CHECK

The `CREATE RANGE CHECK` command creates data validation checks for a given attribute. Checks may be made against discrete values, ranges of values, sizes of values, pattern matching templates, or combinations of all these.

Syntax

  `CREATE RANGE [` | `|CHECK |CONSTRAINT` | `|] [ON] table.attr |` | `|[SET] range {, range} |(range {, range})` | `|; |`

  where:

  `range` |
  is: | `[IS]` | `|value |operator value |match_op pattern |BETWEEN value [boundary] [AND] value [boundary] |RANGE value [boundary] [TO] value [boundary]` | `| | | | |`
  `boundary` | is one of `INCLUSIVE`, `INCL`, `EXCLUSIVE` or `EXCL`. `EXCL` is a synonym for `EXCLUSIVE` and `INCL` is a synonym for `INCLUSIVE`. The default is `INCLUSIVE`.
  `operator` | is one of `=`, `!=`, `<`, `<=`, or `>=`.
  `value` | is a legal data value for the attribute data type.
  `match_op` | is one of `LIKE`, `MATCH`, `!MATCH`, `SMATCH`, or `!SMATCH`.
  `pattern` | is any pattern accepted by a `WHERE` clause.

Notes

1. Multiple `range` conditions are implicitly `OR`'d together.

2. `RANGE CHECK` commands are not additive. Creating a range check replaces any previous range checks on the attribute.

3. Creating a range check does not affect, and is not affected by, current data. The check applies only to subsequent data insertion or updating

4. The variable expansion character (`$` by default) does not have any special function in pattern matching; however, to prevent it from causing an attempt to expand a variable, it must be doubled (i.e., `$$` has the value of the string `$`).

5. A range check can be created on a view.

6. Range checks can be displayed using the `DISPLAY TABLE ALL` command.

Privileges Required

  `ALTER` privilege.

Example

1. Create a Simple Range Constraint to an Attribute

   To restrict the `number` attribute of the `personnel` table to values from 1 to 99 inclusive, use the command:

          CREATE RANGE CHECK ON personnel.number
             SET BETWEEN 1 AND 99;

   The `DISPLAY TABLE` command with the `ALL` option displays range checks:

          DISPLAY personnel ALL;

          *** Table: personnel ***

          Attributes:
            number              integer
                  Range: range '1' i '99' i
            name                char(25,1)
            phone               char(15,1)
            credit_limit        dollar(6,1)

          Creator: joe
          Lock Level: RECORD

          Table #: 9
          Records: 7
          Record size: 45

2. Create Constraint on Attribute to Match Specific Pattern

   Loan amounts may be restricted to multiples of $25 by requesting several pattern matches, as follows:

          CREATE RANGE CHECK ON loans.amount
             SET LIKE '*00', LIKE '*25', LIKE '*50', LIKE '*75';

   The star in the patterns allows zero or more digits at that position. The `DISPLAY TABLE` command shows the result:

          DISPLAY loans ALL;

          *** Table: loans ***

          Attributes:
            number             integer
            name               char(25,1)
            date               date(1)
            amount             dollar(6,1)
                  Range: like '*00'
                         like '*25'
                         like '*50'
                         like '*75'

          Creator: joe
          Lock Level: RECORD

          Table #8:
          Records: 12
          Record size: 36

## 4.10 DROP RANGE CHECK

The `DROP RANGE CHECK` command removes data range validation checks from an attribute. This removes all current data range validation specifications on the attribute.

Syntax

  `DROP RANGE [` | `|CHECK |CONSTRAINT` | `|] [ON] table.attr; |`

Notes

1. The attribute must be qualified by its table name.

2. A view name can be used in place of a table name.

Privileges Required

  `ALTER` privilege.

Example

  Remove all restrictions on loan amounts with the command:

         DROP RANGE CHECK ON loans.amount;

## 4.11 CREATE REFERENTIAL

Constraints can be placed on data inserts and deletes using the `CREATE REFERENTIAL` command. A referential constraint establishes a relationship between tables that will prevent inserts, updates and deletes on one table unless the values to be inserted, updated or deleted meet appropriate conditions in the second table.

Syntax

  `CREATE` | `|REFERENTIAL |REFER` | `| [ |` | `|CONSTRAINT |CHECK` | `|] [name] |`

  |  |  | `[ON] constrained` | `|INSERT |DELETE` | `| constraining; |`

  where:

  `name` | is the name of the referential constraint given by the user.
  `constrained` | is the list of constrained attributes following the syntax below:
  `|table (attr {, attr}) |table SET attr {, attr}` | `| |`
  `constraining` | is the list of constraining attributes following the syntax below:
  `|table (attr {, attr}) |table SET attr {, attr}` | `| |`

Notes

1. Two tables are involved in a referential constraint.The constrained table is the table on which `INSERT`, `UPDATE` and `DELETE` operations, will be controlled. The constraining table is the table whose data is used to control operations on the constrained table.

   To establish a referential constraint one or more attributes in the constrained table, the constrained attributes, are associated with one or more corresponding attributes in the constraining table, the constraining attributes.

   These conditions must be met for a referential constraint to be created:

   - The constrained and constraining tables must exist.
   - The constrained and constraining attributes must exist
   - You must have `ALTER` privilege on the constrained table.
   - You must have read privilege on the constraining table.
   - The data types and number of constraining attributes must be identical to the data type and number of the corresponding constrained attributes.

2. Before some operations, such as inserts, updates or deletes, on a constrained table can be performed, a check is done to ensure that the data to be inserted, updated or deleted meet the appropriate criterion for the constraint created. If the constraint is met, the operation is performed as requested. If the constraint is not met, the operation is canceled.

3. Empress currently supports two kinds of referential constraints: insert constraints and delete constraints

   An insert constraint implies that for data to be inserted into the constrained table, the values of the constrained attributes must be equal to the values of the corresponding attributes in at least one record of the constraining table.

   A delete constraint implies that for data to be deleted from the constrained table, the values of the constrained attributes must not be equal to the values of their corresponding attributes in any record of the constraining table. If the constrained table contains duplicate records, operations to delete all but the last of these records will proceed without reference to the constraining table. For the last of the duplicates to be deleted, the criterion of the delete constraint must be satisfied.

   An `UPDATE` statement can be considered as a `DELETE` followed by an `INSERT`. Referential constraints are evaluated accordingly.

4. More than one attribute can be involved in a constraint. It is possible for every attribute in a table to be named in a given constraint. Constraints that specify more than one attribute are called complex constraints. For data operations on the constrained table to be performed under a complex constraint, the constraint must be satisfied by a single record in the constraining table.

5. More than one constraint can be imposed on a pair of tables. A table can be both constrained and constraining within a constraint pair. An insert and a delete constraint can both be placed on the same table.

   The effects of referential constraints are cumulative. Creating a new constraint does not override the effects of previously defined constraints. While all existing constraints must be satisfied for a constrained operation to be performed, it is not necessary for all of the constraints to be satisfied by a single record in the constraining table.

6. Constrained attributes that take on a null value in a given operation are not used in the evaluation of the constraint. If only a single attribute has been constrained and for a given operation this attribute takes on a null value, the effect is as if no constraint had been specified for that operation.

7. A referential constraint differs from a range check in that a referential constraint will compare the data to be inserted or deleted against data in a table, while a range check will compare data to be inserted (but not deleted) against a constant value.

8. During operations that involve referential constraints, tables will be locked at the level specified in the data dictionary.

9. Referential constraints can be imposed on a view.

10. Constraints can be viewed using the `DISPLAY TABLE` command.

11. A referential constraint name is used for easy reference.

Privileges Required

  `ALTER` privilege on constrained table and `SELECT` privilege on constraining table.

Example

1. Create an Insert Constraint

   To prevent `loans` from being granted to non-employees an insert constraint can be created on `loans.name` and constrained by `personnel.name` with the following command:

          CREATE REFER no_loan
             ON loans (name) INSERT personnel (name);

   To view the constraint created, type:

          DISPLAY loans;

          *** Table: loans ***

          Attributes:
            number          integer
            name            char(25,1)
            date            date(1)
            amount          dollar(6,1)

          Referential: no_loan (name) INSERT personnel (name)

   To see the effects of the constraint, we can attempt to insert a name into `loans` that does not exist in `personnel`:

          INSERT INTO loans (name) VALUES ("Doe");

          *** User Error *** insert constraint 'no_loan' violation

2. Create a Delete Constraint

   To prevent the deletion of employees from the `personnel` table while they still have outstanding `loans`, the following constraint can be created:

          CREATE REFER still_owes
             ON personnel (name) DELETE loans (name);

   To view the constraint created, use:

          DISPLAY personnel;

          *** Table: personnel ***

          Attributes:
            number             integer
            name               char(25,1)
            phone              char(15,1)
            credit_limit       dollar(6,1)

          Referential: still_owes (name) DELETE loans (name)

   To see the effects of the constraint we can attempt to `DELETE` an employee from `personnel` while an outstanding loan exists:

          DELETE FROM personnel WHERE name = "Mosca";

          *** delete constraint 'still_owes' violation

3. Create a Complex Insert Constraints

   Create a complex insert constraint on `loans.name` and `loans.number` by `personnel.name` and `personnel.number` with the command:

          CREATE REFER name_number ON
             loans (name, number) INSERT personnel (name, number);

   View the constraint created with the command:

          DISPLAY loans;

          *** Table: loans ***

          Attributes:
            number          integer
            name            char(25,1)
            date            date(1)
            amount          dollar(6,1)

          Referential: name_number (name, number) INSERT
                       personnel (name, number)

   Insert a record into the `loans` table with values of Mosca for the `name` attribute and 5 (Mosca's personnel number) for the `number` attribute with the command:

          INSERT INTO loans (name, number) values ('Mosca' 5);

   Both constraints are satisfied. There is a record in the `personnel` table in which `name` and `number` take on the values to be inserted, therefore, the operation is performed.

   Insert a record into the `loans` table with values of `Mosca` for the `name` attribute and `10` (Jones' personnel number) for the `number` attribute with the command:

          INSERT INTO loans (name, number) VALUES ('Mosca', 10);

   which produces as output:

          *** User Error *** insert constraint 'name_number' violation

   The constraint is not satisfied. There is no single record in the `personnel` table in which the attribute `name` takes on the value `Mosca` and the attribute `number` takes on the value `10`. The operation is not performed. Note that although the components of the constraints are satisfied by different records, they are not satisfied by the same record.

4. Create a Multiple Constraints

   Create an insert constraint on `loans.name` by `personnel.name` with the command:

          CREATE REFER name ON
             loans (name) INSERT personnel (name);

   Create an insert constraint on `loans.number` by `personnel.number` with the command:

          CREATE REFER number ON
             loans (number) INSERT personnel (number);

   View the constraints created with the command:

          DISPLAY loans;

          *** Table: loans ***

          Attributes:
            number              integer
            name                char(25,1)
            date                date(1)
            amount              dollar(6,1)

          Referential: name (name) INSERT personnel (name)
                       number (number) INSERT personnel (number)

   Insert a record into the `loans` table with values of `Mosca` for the `name` attribute and `5` (Mosca's personnel number) for the `number` attribute with the command:

          INSERT loans SET name = 'Mosca', number = 5;

   Both constraints are satisfied. There is a record in the `personnel` table in which `name` and `number` take on the values to be inserted, therefore, the operation is performed.

   Insert a record into the `loans` table with values of `Mosca` for the `name` attribute and `10` (Jones' personnel number) for the `number` attribute with the command:

          INSERT loans SET name = 'Mosca', number = 10;

   Both constraints are satisfied. There is a record in the `personnel` table in which the attribute `name` takes on the value `Mosca` and the attribute `number` takes on the value `10`, therefore, the operation is performed. Note that although both constraints are satisfied, they are not satisfied by the same record.

   Insert a record into the `loans` table with values of `Mosca` for the `name` attribute and `1` (an unused personnel number) for the `number` attribute with the command:

          INSERT loans SET name = 'Mosca', number = 1;

   which produces as output:

          *** User Error *** insert constraint 'number' violation

   While the `name` constraint is satisfied by a record with the value `Mosca` for the attribute `name`, there is no record that satisfies the `number` constraint with a value of `1` for the attribute `number`.

## 4.12 DROP REFERENTIAL

A referential constraint can be removed using the `DROP REFERENTIAL` command.

Syntax

  `DROP` | `|REFERENTIAL |REFER` | `| [ |` | `|CONSTRAINT |CHECK` | `|] [name] |`

  |  |  | `[[ON] constrained` | `|INSERT |DELETE` | `| constraining]; |`

  where:

  `name` | is the name of the referential constraint given by the user.
  `constrained` | is the list of constrained attributes following the syntax below:
  `|table (attr {, attr}) |table SET attr {, attr}` | `| |`
  `constraining` | is the list of constraining attributes following the syntax below:
  `|table (attr {, attr}) |table SET attr {, attr}` | `| |`

Notes

  If, in the case of the above syntax, the constraint name given is not unique, Empress will prompt you with the name of a constrained table and ask for a `y/n` response. Answering `y` causes the constraint on that table to be dropped and the command to end. If you answer `n` , Empress will prompt with the name of the next table, and so on. If answer `n` is given to every prompt, no constraint will be dropped and the command will end.

Privileges Required

  `ALTER` privilege.

Example

  To drop a constraint called `no_loans` on the `loans` table use:

         DROP REFER no_loans;

## 4.13 CREATE VIEW

The `CREATE VIEW` command establishes a logical table (view) on one or more tables. A view defines a structure via a `SELECT` command, usually limiting the attributes and records to be displayed, and that structure can then be used to access the records in the tables concerned. Views as structures are stored permanently in the data dictionary but do not involve the duplication of data in tables.

Syntax

      CREATE VIEW view [(attr { , attr})] AS select_command;

  where:

  `view` | is the name of the view.
  `select_command` | is the `SELECT` statement. Refer to the `SELECT Command` section of this manual.

Notes

1. The tables underlying the view (known as base tables) can reside in several databases on several file systems. A discussion on using views to access distributed data will be found in the Empress SQL: User's Guide.

2. Views can be either simple or complex. A simple view gives access to a single table, and normally gives partial access to the attributes or records in the table. A complex view gives simultaneous access to several tables linked together on a common attribute or attributes. The operation which links records in this manner is called a join. Joins are described in this manual under the `SELECT` command, and in the Empress SQL: User's Guide under Query Language Topics.

3. Attribute names can be specified in the view. Note that surrounding parentheses are required if attribute names are specified.

4. The `SELECT` part of the `CREATE VIEW` command may include functions (`MAX`, `AVG`, etc.), `GROUP BY` or `HAVING` clauses. The `WHERE` clause in the `SELECT` may include expressions involving operators or subqueries. The keyword `DISTINCT` may be used.

5. A view may be created on a view (nested views). That is, the table in the `SELECT` part of the command may be a view. In general, views may be used whenever a table is specified for retrieval operations. Access to data in a view is controlled by the privileges on the view, not by privileges on the underlying tables.

6. If a table underlying a view is dropped, an attempt to select from the view results in an error message saying that the table does not exist.

   When a table is dropped no warning is given of the existence of views on the table.

7. The structure of a view can be displayed using the `DISPLAY TABLE` command. The `SELECT` command used to create the view is also displayed if the `ALL` option to the `DISPLAY TABLE` command is used.

8. A `VIEW` is a logical or virtual `TABLE`. Consequently many of the commands that are directed towards tables can be used on views. These commands are:
   - `CREATE COMMENT`
   - `CREATE RANGE CHECK`
   - `CREATE REFERENTIAL CONSTRAINTS`
   - `DROP REFERENTIAL CONSTRAINTS`
   - `DELETE`
   - `DISPLAY`
   - `DROP COMMENT`
   - `DROP RANGE CHECK`
   - `DROP TABLE`
   - `DROP VIEW`
   - `GRANT/REVOKE PRIVILEGE`
   - `INSERT`
   - `RENAME`
   - `RUN REPORT`
   - `SELECT`
   - `UPDATE`

   Commands that cannot be applied to views are: `ALTER`, `CREATE INDEX`, `DROP INDEX`, `EMPTY`, `LOCK LEVEL`, `SORT`.

9. Records may always be selected from a view, but update type operations are restricted to views that satisfy certain conditions. Simple views usually satisfy the conditions, but some care is required to construct complex views that satisfy them. These conditions are summarized below, and discussed at length in the Empress SQL: User's Guide under Query Language Topics.

   Below is a summary of the conditions under which Empress will permit update type operations on views:

   The first condition for update type operations to be allowed on a view is that a record from a base table can, in principle, appear at most once in a view. This condition can be expressed in three rules:

   - The `WHERE` clause used to define the view must be limited to `AND` conjunctions of simple conditions, where a simple condition is one of:

     - A comparison between an attribute and a constant.
     - A comparison between two attributes of the same table.
     - A condition of equality between two attributes of different tables.

     The rule excludes:

     - The `NOT` condition.
     - The `OR` conjunction.
     - Between tables, other than the equal(=) comparison.

   - There must be sufficient conditions of equality between attributes of the base tables. A technique for determining whether the rule is satisfied is to represent each base table as a point, and a condition of equality joining two tables as a line between the respective points. If it is possible to move from any point to any other point by traveling along lines, then the rule is satisfied. An adjunct to this rule is that a table may not be selected twice in the same view.

   - For each base table, at least one of the attributes participating in each join with another table must contain a key. This means that either at least one of the attributes has a unique index on it, or several of the attributes are combined in a unique composite index.

     A technique for determining whether this rule is satisfied is to pair each table with every table to which it is joined, and for each pair list the attributes of the first table involved in joins with the second table. Either one of the attributes in each list must have a unique index or several of the attributes must have a unique composite index.

   The second condition for update type operations to be allowed on views is: for every set of attributes in the base tables that must be equal, only one may appear in the view. When the view is updated, every attribute in such a set is assigned the value specified for the attribute that does appear.

   The third condition concerns attributes that are defined `NOT NULL` or that have unique indices on them. Since such attributes may not have null values inserted for them, they must either appear in the view or be equal to an attribute that does appear in the view if insert operations are to be permitted on the view.

Privileges Required

  `INSERT` privilege on `sys_tables` and creator of underlying tables.

Example

1. Create a Simple View

   Create a simple view called `employees` on the `personnel` table, using only the `name` and `phone` attributes, with the command:

          CREATE VIEW employees AS
             SELECT name, phone FROM personnel;

   Now, when you enter the command:

          SELECT * FROM employees;

   you will see:

          name          phone

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

   If you now use the `DISPLAY TABLE` command to display the structure of `employees`, you will see:

          DISPLAY employees;

          *** View: employees ***

          Attributes:
            name          char(25,1)
            phone          char(15,1)

   The `DISPLAY TABLE` command with the `ALL` option shows the command used to create the view:

          DISPLAY employees ALL;

          *** View: employees ***

          View: select attr 'name', attr 'phone' from 'personnel'

          Attributes:
            name          char(25,1)
            phone          char(15,1)

          Creator: joe

          Table #: 11
          Records: 7

2. Create a Complex View

   It is often convenient to make a view of a commonly-used join between two or more tables and select from the view, rather than having to specify the join conditions each time. For example, you can create a view called `jonesloans` on the `loans` and `personnel` tables to show Jones' loans and credit limit:

          CREATE VIEW jonesloans AS
             SELECT name, date, amount, credit_limit
             FROM loans, personnel
             WHERE loans.name = 'Jones' AND
             loans.name = personnel.name;

## 4.14 DROP VIEW

The `DROP VIEW` command removes a definition of a view from the data dictionary.

Syntax

      DROP [VIEW] view;

  where:

  `view` | is the name of the view.

Notes

  When a view is dropped, no warning is given of the existence of views on the view.

Privileges Required

  `DROP` privilege.

Example

  Remove the `jonesloans` view with the command:

         DROP VIEW jonesloans;

## 4.15 CREATE COMMENT

The `CREATE COMMENT` command places a comment on a table, attribute, module, routine or trigger. The comment will appear in the output from a `DISPLAY DATABASE` or `DISPLAY TABLE` command.

Syntax

  Syntax 1:

  |  |  | `CREATE COMMENT [ON]` | `|TABLE |ATTR[IBUTE] |MODULE |ROUTINE |TRIGGER` | `table table.attr module module.routine trigger` | `| [SET] comment_string; | | | |`

  Syntax 2:

  |  |  | `COMMENT [ON]` | `|TABLE |ATTR[IBUTE] |MODULE |ROUTINE |TRIGGER` | `table table.attr module module.routine trigger` | `| [IS] comment_string; | | | |`

  where:

  `module` | `is a persistent stored module name.`
  `routine` | `is a routine name. The routine name must prefixed by the module name. It must be quoted (either by single quotation marks or double quotation marks), this is because the routine name is registered as a dynamic keyword in Empress.`
  `trigger` | `is a trigger name.`
  `comment_string` | `is any character string in quotes.`

Notes

1. Comment on attribute must be qualified by its table name.

2. `CREATE COMMENT` can also be used to a view.

3. Routines with the same name within a module share a common comment even though their external names are different.

Privileges Required

  `ALTER` privilege.

Example

1. Create a Comment on a Table

   Put a comment on the `loans` table as follows:

          COMMENT ON TABLE loans IS "Loans to Employees";

   The `DISPLAY TABLE` command shows the comment on the table:

          DISPLAY loans;

          *** Table: loans ***

          Comment: Loans to Employees

          Attributes:

            number             integer
            name               char(25,1)
            date               date(1)
            amount             dollar(6,1)

2. Create a Comment on an Attribute

   Place a comment on the `amount` attribute in `loans` with:

       CREATE COMMENT ON ATTRIBUTE loans.amount
           SET 'Amounts must be multiples of $$25';

   The doubled dollar sign is necessary to avoid interpretation of "$25'' as an Empress variable. See the discussion on Variables and Strings in the chapter Empress Conventions.

   The `DISPLAY TABLE` command with the `ALL` option shows the comment:

          DISPLAY loans ALL;

          *** Table: loans ***

          Comment: Loans to Employees

          Attributes:

            number             integer
            name               char(25,1)
            date               date(1)
            amount             dollar(6,1)

          Comment: Amounts must be multiples of $25

          Creator: joe
          Lock Level: RECORD

          Table #: 8
          Records: 12
          Record size: 36

   The comment on the particular attribute can be displayed with the `DISPLAY TABLE` restricted to the attribute:

          DISPLAY loans ON amount ALL;

          *** Table: loans ***

          Comment: Loans to Employees

          Attributes:

            amount        dollar(6,1)

          Comment: Amounts must be multiples of $25

          Creator: joe
          Lock Level: RECORD

          Table #: 8
          Records: 12
          Record size: 36

## 4.16 DROP COMMENT

The `DROP COMMENT` command removes the comments from a table or attribute.

Syntax

  `DROP COMMENT [ON]` | `|TABLE |ATTR[IBUTE] |MODULE |ROUTINE |TRIGGER` | `table table.attr module module.routine trigger` | `|; | | | |`

Notes

  `DROP COMMENT` can also be used to remove a comment from a view.

Privileges Required

  `ALTER` privilege.

Example

  Remove the comment on the `loans` table with the command:

         DROP COMMENT ON TABLE loans;

## 4.17 GRANT PRIVILEGE

The `GRANT PRIVILEGE` command assigns access privileges on tables to other users or database roles. These other users may also be given the ability to grant their privileges to others. Privileges can be removed with the `REVOKE PRIVILEGE` command. Privileges can be displayed with the `DISPLAY PRIVILEGE` and `DISPLAY GRANT PRIVILEGE` commands.

Syntax

      GRANT privilege {, privilege} [ON] table [TO] grantee {, grantee}

         [AS grantor] [[WITH] GRANT [OPTION]];

  where:

  `privilege` | is one of the privileges in the Table 4-3.
  `grantee` |
  It can be one of: | `|CREATOR |DBA |PUBLIC |username |rolename` | `| | | | |`
  It represents the name of a user, the name of a database role and specifies the person given the privileges. The `CREATOR` is the creator of the table, the `DBA` refers to the Database Administrator, `PUBLIC` refers to all users and `username` is an operating system account name or login name.
  `grantor` |
  It can be one of: | `|CREATOR |DBA |username` | `| | |`
  It refers to the person granting the privilege. If `grantor` is not specified, the default is the name of the user issuing the command.

Notes

1. The following table listed all database privileges:

   Table 4-3: Database Privileges

   Privilege | Permits Commands
   `ALTER` | `ALTER TABLE, CREATE/DROP COMMENT, CREATE/DROP RANGE CHECK, CREATE/DROP REFERENTIAL CONSTRAINT, LOCK LEVEL, RENAME`
   `BYPASS_LOCK` | `SELECT BYPASS_LOCK`
   `DELETE` | `DELETE, SELECT`
   `DISPLAY` | `DISPLAY TABLE`
   `DROP` | `DROP TABLE`
   `EMPTY` | `EMPTY TABLE`
   `INDEX` | `CREATE`/`DROP INDEX`
   `INSERT` | `INSERT`
   `SELECT` | `SELECT`
   `SORT` | `SORT`
   `UPDATE [(attr {, attr})]` | `UPDATE`, `SELECT` If attributes are specified, the privilege applies only to those attributes.

2. Provisions have been made to refer to commonly used groups of privileges by a single name. The names of these groups reflect their use. They are: `DBA` (Database Administrator), `ALL` and `USER`.

   Table 4-4: Groups Database Privileges by Name

   Privilege Type | Privileges
   `DBA` | `ALTER`, `DROP`, `EMPTY`, `INDEX`, `SORT`
   `ALL` | All privileges.
   `USER` | `BYPASS_LOCK`, `DELETE`, `DISPLAY`, `INSERT`, `SELECT`, `UPDATE`

3. The `DBA` is defined in `tabzero` by the variable `MSDBADMINISTRATOR`. The `CREATOR` of a table is defined in `sys_tables` in the data dictionary.

   The `DBA` of the database and the `CREATOR` of a table may be changed by updating the appropriate system data. Granting privileges to the `DBA` and `CREATOR` provides a systematic means to have the privileges inherited by users as they become the `DBA` or `CREATOR`.

4. When anyone tries to access a table, that person's access privileges are checked. If the person also happens to be the `CREATOR` of the table or the `DBA`, privileges accruing to those positions apply.

5. The `CREATOR` or `DBA` may grant any privilege on the table. Privileges may only be revoked by their grantor. However, the `CREATOR` and `DBA` may revoke any privilege as the grantor.

6. The `GRANTOR` type regulates the granting and revoking of privileges. By default, you may only grant those privileges for which you have the grant option.

7. Empress uses the file permissions that are set by the underlying operating system of each database. In order to access a table in a Empress database, a user must have both the appropriate operating system file permissions and the appropriate database privileges. e.g. in UNIX, the DBA must give file permissions to other users of the database according to their level of usage. If a user just wants to do query on the database (e.g. display/select), a read permission on the directory of the database and on the files corresponding to the queried tables would be enough. If a user needs to modify a database (insert/delete/update/alter), the user must be given write permission on the files corresponding to the modified tables and the system table. This can be done by using `chmod` command (refer to your UNIX manual for usage of this command). If the user needs to create new tables on a database, write permission on the directory containing the database must be given to the user.

8. The `DBA` can grant all privileges on all tables. To exercise this prerogative, the grantor must grant the privileges `AS DBA`. Default privileges on the table are granted as specified in the administrative variable `MSDBDBAPRIVS` in `tabzero` of the data dictionary. The default for this variable specifies the following privileges:

   Table 4-5: Default Privileges for DBA on a Table

   Grantor | Grantee | Privilege | Grant Option
   `DBA` | `DBA` | `DBA` privilege: `ALTER`, `DROP EMPTY`, `INDEX`, `SORT` | With no grant option
   `DBA` | `DBA` | `USER` privilege: `BYPASS_LOCK`, `DELETE`, `DISPLAY`, `CREATOR`, `INSERT`, `SELECT`, `UPDATE` | `WITH GRANT OPTION`

9. The creator of a table can grant all privileges on that table. When a table is created, the user issuing the command becomes the `CREATOR` of the table. Default privileges on the table are granted as specified in the administrative variable `MSDBPRIVS` in `tabzero` of the data dictionary. The default for this variable specifies the following privileges:

   Table 4-6: Default Privileges for the Creator of a Table

   Grantor | Grantee | Privilege | Grant Option
   `CREATOR` | `CREATOR` | `DBA` privilege: `ALTER`, `DROP EMPTY`, `INDEX`, `SORT` | With no grant option
   `CREATOR` | username (login name of the creator) | `USER` privilege: `BYPASS_LOCK`, `DELETE`, `DISPLAY`, `CREATOR`, `INSERT`, `SELECT`, `UPDATE` | `WITH GRANT OPTION`

10. Privileges can be granted on a view as well as on a table.

Privileges Required

  The grantor must have the privilege in question with grant authorization.

Example

1. Grant Privileges to Specific Users

   Grant `SELECT` and `INSERT` privileges on the `auto parts` table to Mosca and Kilroy, whose login/user names are `mosca` and `kilroy`, with the command:

          GRANT SELECT, INSERT ON 'auto parts'
             TO mosca, kilroy;

2. Grant Privileges with Grant Option

   Grant the login/user name `office` `INSERT`, `UPDATE`, and `DELETE` privileges on the `customers` table as the creator of the table, with the option to grant these privileges to others, with the command:

          GRANT INSERT, UPDATE, DELETE ON customers
             TO office
             AS CREATOR
             WITH GRANT OPTION;

3. Grant Privilege to All Users

   Grant `SELECT` privileges to everyone for the `customers` table, again as the creator, with:

          GRANT SELECT ON customers
             TO PUBLIC
             AS CREATOR;

## 4.18 DISPLAY PRIVILEGE

The `DISPLAY PRIVILEGE` command prints access privilege information for the named table.

Syntax

  `DISPLAY` | `|PRIVILEGE |PRIV` | `| table [ON [ATTR] |` | `|attr |[NOT] match_op pattern` | `|] |`

  |  |  | `[` | `|ALL |AS | |` | `|DBA |CREATOR |username` | `| | |` | `|]; | | |`

  where:

  `match_op` | is one of `LIKE`, `MATCH`, `!MATCH`, `SMATCH`, or `!SMATCH`.
  `pattern` | is any pattern accepted by a `WHERE` clause; it must be given as a quoted string.
  `username` | is an operating system account name or login name.

Notes

1. The `DISPLAY PRIVILEGE` command without the `ALL` option shows what you can do on the named table. Privileges are listed in pre-assigned columns on the screen. If you have all privileges, the word `ALL` is printed.

2. If you have update privileges on some attributes and not others, the attributes you can update will be listed. To restrict the list to particular attributes, use the `ON attr` or the pattern matching options.

3. With the `ALL` option, your privileges are classified according to how you have them. Lists of privileges are printed for `USER`, `PUBLIC`, `DBA` and `CREATOR`, if applicable.

4. With the `AS DBA` option, the privileges accruing to you as the `DBA` are printed. If you are not the `DBA` no privileges are printed.

5. With the `AS CREATOR` option, the privileges accruing to you as the `CREATOR` of the table are printed. If you are not the `CREATOR`, no privileges are printed.

6. With the `AS username` option, the privileges accruing to the username are printed. This option only works if you are the `DBA` or the `CREATOR`.

7. `DISPLAY PRIVILEGE` can also be used to display privileges on a view.

Privileges Required

  None.

Example

1. Display Privileges on a Table

   To find out your privileges on the `loans` table, do:

          DISPLAY PRIVILEGE loans;

   which produces:

          ALL

2. Display a Classification of the Privileges

   To include a classification of the privileges, use:

          DISPLAY PRIVILEGE loans ALL;

   which produces:

          USER: delete display insert select update
          CREATOR: alter drop empty index sort

## 4.19 DISPLAY GRANT PRIVILEGE

The `DISPLAY GRANT PRIVILEGE` command prints grant privilege information for the named table.

Syntax

  `DISPLAY GRANT` | `|PRIVILEGE |PRIV` | `| table [ON [ATTR] |` | `|attr |[NOT] match_op pattern` | `|] |`

  |  |  | `[` | `|ALL |AS | |` | `|DBA |CREATOR |username` | `| | |` | `|]; | | |`

  where:

  `match_op` | is one of `LIKE`, `MATCH`, `!MATCH`, `SMATCH`, or `!SMATCH`.
  `pattern` | is any pattern accepted by a `WHERE` clause; it must be given as a quoted string.
  `username` | is an operating system account name or login name.

Notes

1. The `DISPLAY GRANT PRIVILEGE` command without the `ALL` option shows what you can grant on the named table under your own username. Privileges are listed in pre-assigned columns on the screen. If you have the grant option on all privileges, the word ALL is printed.

2. If you can grant update privileges on some attributes and not others, the attributes you can grant update on will be listed. To restrict the list to particular attributes, use the ON attr or the pattern matching options.

3. With the `ALL` option, all your grant options are printed and classified according to how you have them. Lists of privileges are printed for `USER`, `PUBLIC`, `DBA` and `CREATOR`, if applicable.

4. With the `AS DBA` option, the grant options accruing to you as the DBA are printed. If you are not the `DBA` no grant options are printed.

5. With the `AS CREATOR` option, the grant options accruing to you as the `CREATOR` of the table are printed. If you are not the `CREATOR`, no grant options are printed.

6. With the `AS username` option, the grant options accruing to the `username` are printed. This option only works if you are the `DBA` or the `CREATOR`.

7. `DISPLAY GRANT PRIVILEGE` can also be used to display grant options on a view.

Privileges Required

  None.

Example

1. Display Default Grant Options

   To find out your default grant options on the `loans` table, type:

          DISPLAY GRANT PRIVILEGE loans;

   which produces:

          USER: delete display insert select update

2. Display Full List of Grant Options

   To print a full list of your grant options, use:

          DISPLAY GRANT PRIVILEGE loans ALL;

   which produces:

          USER: delete display insert select update
          CREATOR: all
          DBA: all

## 4.20 REVOKE PRIVILEGE

The `REVOKE PRIVILEGE` command removes access privileges on tables from users or database roles.

Syntax

      REVOKE privilege {, privilege} [ON] table [FROM] grantee {, grantee}

         [AS grantor];

  where:

  `privilege` | is one of the privileges listed in the TABLE 4-3.
  `grantee` |
  It can be one of: | `|CREATOR |DBA |PUBLIC |username |rolename` | `| | | | |`
  It represents the name of a user, and specifies the person given the privileges. The `CREATOR` is the creator of the table, the `DBA` refers to the Database Administrator, `PUBLIC` refers to all users and `username` is an operating system account name or login name.
  `grantor` |
  It can be one of: | `|CREATOR |DBA |username` | `| | |`
  It refers to the person granting the privilege. If `grantor` is not specified, the default is the name of the user issuing the command.

Notes

1. Three privilege types (`DBA`, `ALL` and `USER`) are provided for convenience. They are an easy way to refer to groups of privileges and is listed in Table 4-4.

2. Privileges granted by a grantor may, in general, only be revoked by the same grantor. However, the `CREATOR` and `DBA` may revoke any privilege as whoever granted the privilege.
3. Privileges can be granted and revoked on views as well as on tables.

Privileges Required

  Must be grantor of the privilege in question.

Example

  Revoke Mosca's `INSERT` privilege on the `auto parts` table with the command:

         REVOKE INSERT ON 'auto parts'
            FROM mosca;

## 4.21 LOCK LEVEL

The `LOCK LEVEL` command sets the level of locking on a table. The new lock level remains in force for all users of the table until the next `LOCK LEVEL` command.

Lock levels are provided so that you can control access if you export your database to a multi-user system.

Syntax

  `LOCK LEVEL [ON] table [IS]` | `|TABLE |GROUP [(n)] |RECORD [(n)] |NULL` | `|; | | |`

Notes

1. If `NULL` is specified, all locking is disabled for the given table.

2. If `TABLE` is specified as the locking level, the entire table is locked for the duration of each Query Language command.

3. If `GROUP` is specified, all records affected by a command (rather than the entire table) are locked for the duration of the command.

   Unless the attributes referred to in a command are indexed, group level locking is effectively the same as table level locking, since Empress cannot tell in advance which records will be seen by the command.

4. Specifying `RECORD` locks only each record as it is encountered in a command.

5. Specifying `RECORD (n)` locks each record as it is encountered in a command plus (n-1) surrounding records. n records, locked in this case, are constituting a PAGE lock

6. Setting lock levels on views has no effect.

7. When table is created, the setting of the lock level is controlled by the system variable `MSDBLOCKLEVEL` in the data dictionary `tabzero` file. The default is record level locking.

Privileges Required

  `ALTER` privilege on the table to be locked.

Example

1. To Set No Locking on a Table

   To set no locking on the `personnel` table with the command:

          LOCK LEVEL ON personnel IS NULL;

2. To Set Record Level Locking on a Table

   To set record-level locking on the `loans` table with:

          LOCK LEVEL ON loans IS RECORD;

## 4.22 CREATE MODULE

The `CREATE MODULE` command is used to define the definition of the PSM (Persistent Stored Modules) module into the data dictionary. This creates an entry point for the user defined functions, operators, procedures or aggregate functions. The `UPDATE MODULE` command is necessary for linking the module definition with the module loadable shared library.

Syntax

      CREATE MODULE module_name

         [LANGUAGE C [(lang_param {, lang_param})]]

  |  |  | `|module_procedure |module_function |module_operator |module_agg_func` | `|; { | | |` | `|module_procedure |module_function |module_operator |module_agg_func` | `|;} | | |`

         END MODULE;

  where:

  `module_name` | is the module name. Module name is case sensitive.
  `lang_param` | is an identifier. This is not currently used by Empress.
  | `module_procedure` | is defined as:
      [DECLARE] PROCEDURE procedure_name

         ([[| IN  |] [parameter_name] parameter_type
            | OUT |
            |INOUT|

         {, [| IN  |] [parameter_name] parameter_type}])
             | OUT |
             |INOUT|

         [ALLOCATE WORKSPACE |  nbytes  |]
                             | variable |

         EXTERNAL [BEGIN EXPRESSION external_routine_name]
                  [NAME external_routine_name]
                  [END EXPRESSION external_routine_name]

         [|PARAMETER STYLE SQL    |]
          |PARAMETER STYLE GENERAL|
  `module_function` | is defined as:
      [DECLARE] FUNCTION function_name

         ([[IN] [parameter_name] parameter_type

         {, [IN] [parameter_name] parameter_type}])

         RETURNS return_type

         [ALLOCATE WORKSPACE |  nbytes  |]
                             | variable |

         EXTERNAL [BEGIN EXPRESSION external_routine_name]
                  [NAME external_routine_name]
                  [END EXPRESSION external_routine_name]

         [|PARAMETER STYLE SQL    |]
          |PARAMETER STYLE GENERAL|
  `module_operator` | is defined as:
  `[DECLARE] OPERATOR` | `|PREFIX` | `|POSTFIX |INFIX [precedence] |COMPARISON |EQUALITY` | `| operator_name | | | |`
         ([[IN] [parameter_name] parameter_type

         {, [IN] [parameter_name] parameter_type}])

         RETURNS return_type

         [ALLOCATE WORKSPACE |  nbytes  |]
                             | variable |

         EXTERNAL [BEGIN EXPRESSION external_routine_name]
                  [NAME external_routine_name]
                  [END EXPRESSION external_routine_name]

         [|PARAMETER STYLE SQL    |]
          |PARAMETER STYLE GENERAL|
  `module_agg_func` | is defined as:
      [DECLARE] AGGREGATE FUNCTION aggr_function_name

         ([IN] parameter_name] parameter_type

         {, [IN] [parameter_name] parameter_type})

         RETURNS return_type

         [ALLOCATE WORKSPACE | nbytes |]
                             |variable|

         EXTERNAL [BEGIN EXPRESSION external_routine_name]
                  BEGIN GROUP external_routine_name
                  BODY external_routine_name
                  END GROUP external_routine_name
                  [END EXPRESSION external_routine_name]

         [|PARAMETER STYLE SQL    |]
          |PARAMETER STYLE GENERAL|

Notes

1. Currently only C Programming Language is supported by Empress Persistent Stored Modules. In future, other languages may be supported.

   The C programming Language in the language specification can be either `C` or `"C"`; both upper and lower case are allowed.

2. There are three procedure parameter modes: `IN`, `OUT` and `INOUT` for input, output and input/output respectively. If the procedure parameter mode is not specified, the default is `IN`.

3. There are three different groups of `parameter_type`:

   MS Data Types | is Empress SQL Data Types. Refer to the Data types chapter of this manual.
   Generic Data Types | is Empress Generic Data Types. Refer to the Empress: User Defined Functions manual under Data Type Correspondences.
   C Data Type
   | is a C programming language data types. The keyword `C` must be specified before the C data type and must be in quotes, for example, `"C long"`.

   Parameters may belongs to different data type groups. For example:

            (number "C long",
             string CHAR,
             amount GENERIC FLOAT)

4. The `return_type` may have different data type group as `parameter_type`.

5. There are five operator classes:

   - `PREFIX`
   - `POSTFIX`
   - `INFIX [precedence]`
   - `COMPARISON`
   - `EQUALITY`

   The `precedence` for the `INFIX` operator class is: `1`, `2`, `3`, `4`, `5`, `6` or `right`. For boolean operator, only `1`, `2` is valid, default is `2`; for arithmetic operator, all are valid, default is `4`.

6. The parameter style can be either `PARAMETER STYLE GENERAL` or `PARAMETER STYLE SQL`. If not specified, the default is `PARAMETER STYLE GENERAL`.

7. In the text below, routine is generically refer to the procedure, function, operator and aggregate function.

   Rules for the names:

   - within a database, no duplicate module name;
   - within a module,
     - duplicate routine names are allowed provided the routines are of the same keytag (the correspondence between SQL-invoked routine and the keytag is defined in the User Defined Function manual) and the parameters are of different data types;
     - duplicate external routine names for `BEGIN EXPRESSION` are allowed;
     - duplicate external routine names for `BEGIN GROUP` are allowed;
     - no duplicate external routine name for `BODY`;
     - duplicate external routine names for `END GROUP` are allowed.
     - duplicate external routine names for `END EXPRESSION` are allowed;
   - no duplicate routine names across modules;

   The leading and trailing blanks of the module name, routine name and external name are ignored. The leading character should be either underscore (_) or alpha-character, remaining characters can consists of underscore, alpha-character or digit. The maximum length is 31 characters.

8. The `external_routine_name` fall into five classes:

   - external begin expression routine name
   - external begin group routine name
   - external body routine name
   - external end group routine name
   - external end expression routine name

   For aggregate function, all five classes may exist; for other routines, begin group and end group routines never exist.

   External body routine name is specified by body clause (`BODY`) for aggregate function, and by external name clause (`NAME`) for other routines. The default external body name is the same as routine name.

   If not explicitly specified, we assume begin expression routine, end expression routine do not exist.

   External begin group and end group routine names must be explicitly specified for aggregate function. In general, begin group expression routine is used for initialization and end group routine is used for returning result.

9. If `ALLOCATE WORKSPACE` is specified, Empress will allocate and deallocate the workspace for the PSM routines automatically. You should not allocate another workspace in the `BEGIN EXPRESSION` function or deallocate the workspace that system allocated.

   If variable name is given for the workspace, it must be defined in the external routine with the same variable name.

10. After `CREATE MODULE` is completed, you must use `UPDATE MODULE` to link the module definition with the module loadable shared library in order to use the procedures, functions, operators or aggregate functions.

11. Refer to User Defined Functions manual for detail information on Persistent Stored Modules (PSM).

Privileges Required

  `DBA` privilege.

Example

1. The following example creates a module procedure `procmodule`. The example procedure `log_event` is used to create audit trail log.

          CREATE MODULE procmodule
             PROCEDURE log_event ()
                EXTERNAL;
             END MODULE;

2. The following example creates a module function `funcmodule`. This module function declares two functions: `replacenull` and `roundup`.

   The example function `replacenull` replaces the `NULL` value by a specified character string on output. The example function `roundup` rounds a number to the specified decimal digits.

          CREATE MODULE funcmodule
             FUNCTION replacenull (stringvalue CHAR, defaultstring CHAR)
                RETURNS CHAR
                EXTERNAL;
             FUNCTION roundup (number GENERIC FLOAT, digits GENERIC FLOAT)
                RETURNS GENERIC FLOAT
                EXTERNAL;
             END MODULE;

   Note that `replacenull` and `roundup` functions are equivalent to the Empress Build-In Functions `nullval` and `round` respectively. Both `nullval` and `round` are Empress keyword which cannot be used in this example.

3. The following example creates a module operator `opermodule`. The example operator `concatenation` concatenates character data. This operator is equivalent to the Empress Operators `concat`.

          CREATE MODULE opermodule
             OPERATOR INFIX concatenation
                (str1 "C char", str2 "C char")
                RETURNS "C char"
                EXTERNAL;
             END MODULE;

4. The following example creates a module aggregate function `aggrmodule`. The example aggregate function `ssd` produces the final aggregate value associated with the squared standard deviation of the statistic x data.

          CREATE MODULE aggrmodule
             AGGREGATE FUNCTION ssd (LONGFLOAT)
                RETURNS LONGFLOAT
                ALLOCATE WORKSPACE somespace
                EXTERNAL BEGIN GROUP ssdigrp
                         BODY ssdrfns
                         END GROUP ssdtgrp;
             END MODULE;

   The external routine of this example is shown in the EMPRESS: User Defined Functions manual.

## 4.23 UPDATE MODULE

The `UPDATE MODULE` command links the module definition with the module loadable shared library.

Syntax

      UPDATE MODULE module_name [FOR system_name] [|FROM | dll_file];
                                                   |USING|

  where:

  `module_name` | is the module name.
  `system_name` | is a system id that has valid Empress RDBMS license.
  | `dll_file` | is the location of the dynamic loadable shared library object file.

Notes

1. `UPDATE MODULE` command is usually used with `FROM dll_file` or `USING dll_file` options to update the Module with an implementation of PSM routines.

2. If a module is converted from an old version (e.g. Version 8.20) to a newer version (e.g. Version 8.62), its old `wrap.dll` cannot be run on the new version. Use `UPDATE MODULE` command without `FROM dll_file` or `USING dll_file` options only if the database is upgraded to a newer version. In this case it just regenerates the `wrap.dll` file for a given module.

3. `UPDATE MODULE` command can be used as many times as needed after the module is created to change the content or path of the dynamic loadable shared library object file.

4. `system_name` can be obtained by using:

          empvers

   command at the operating system prompt. For example, it will produce the following information:

         EMPRESS V8.62
         (c) Copyright Empress Software Inc. 1983, 2006
       for Intel x86 running Linux OS Release 2.0 with libc.so.6 (ELF)
       [linux-libc6-x86]
       Port Code DOCS-08.62-A-00-S-ENG
       Port Code DOSV-08.62-A-00-S-ENG
       Port Code HYPM-08.62-A-00-S-ENG
       Port Code JDBC-08.62-A-00-S-ENG
       Port Code ODBC-08.62-A-00-S-ENG
       Port Code RDBM-08.62-A-00-S-ENG
       Installed in: /home/staff/joe/empress/v8.62

   The system name is indicated within the square brackets, i.e. `linux-libc6-x86`.

Privileges Required

  `DBA` privilege.

Example

  The following command updates the module `procmodule` from the dynamic loadable shared library object file `procmodule.dll`:

         UPDATE MODULE procmodule FROM 'procmodule.dll';

## 4.24 DROP MODULE

The `DROP MODULE` command removes the module definition from the database data dictionary. Any functions, procedures, operators and aggregate functions that are defined in the module will no longer recognized by Empress.

Syntax

      DROP MODULE module_name;

  where:

  `module_name` | is the module name.

Notes

  When a module is dropped, no warning is given; use it with care.

Privileges Required

  `DBA` privilege.

Example

  The following command drops a module with the module name `procmodule`:

         DROP MODULE procmodule;

## 4.25 DISPLAY MODULE

The `DISPLAY MODULE` command displays the module definition from the database data dictionary.

Syntax

  `DISPLAY MODULE [module_name] [ALL] [DUMP] [` | `|INTO |ONTO` | `| file]; |`

  where:

  `module_name` | is the module name.

Notes

1. If the module name is not specified, `DISPLAY MODULE` command will display the definition of all modules in the database.

2. The `DISPLAY MODULE` command without the `ALL` option lists the systems that this module is available to, all the SQL-invoke routines in the module and their external name.

   If module is not available for any system (indicated by `NONE`) means the module has been created (`CREATE MODULE`) but has not been linked (`UPDATE MODULE`).

3. The `DISPLAY MODULE` command with the `ALL` option provides additional information on the module, such as: parameter information, return data type, parameter style, creator of the module, module number and the programming language used to create the module (currently only C programming language).

4. If `DUMP` is used, the output is condensed and can be used to create a script file.

5. If `INTO file` or `ONTO file` are used, the output is sent to the named file rather than to the terminal. `INTO` overwrites any existing file, while `ONTO` appends to it. Both create the file if necessary.

Privileges Required

  `DISPLAY` privilege.

Example

  The following example shows different `DISPLAY MODULE` commands and their outputs:

         * DISPLAY MODULE;

           aggrmodule
           funcmodule
           opermodule
           procmodule

         * DISPLAY MODULE funcmodule;

           ****  Database: repairs  ****

           ***  Module: funcmodule  ***

               Available for systems:
                 NONE

               replacenull                     function
                 External Name:                  replacenull

               roundup                         function
                 External Name:                  roundup

         * DISPLAY MODULE ALL;

           ****  Database: repairs  ****

           ***  Module: aggrmodule  ***

               Available for systems:
                 NONE

               ssd                             aggregate function

           ***  Module: funcmodule  ***

               Available for systems:
                 NONE

               replacenull                     function
               roundup                         function

           ***  Module: opermodule  ***

               Available for systems:
                 NONE

               fact                            postfix operator

           ***  Module: procmodule  ***

               Available for systems:
                 NONE

               log_event                       procedure

         * DISPLAY MODULE funcmodule ALL;

           ***  Module: funcmodule  ***

               Available for systems:
                 NONE

               replacenull                     function
                 Number of Parameters:           2
                      1)   in      stringvalue         character(25,1)
                      2)   in      defaultstring       character(25,1)
                 Return Data Type:               character(25,1)
                 Parameter Style:                general
                 External Name:                  replacenull

               roundup                         function
                 Number of Parameters:           2
                      1)   in      number              generic float
                      2)   in      digits              generic float
                 Return Data Type:               generic float
                 Parameter Style:                general
                 External Name:                  roundup

               Creator:         zsu
               Module #:        2
               Language:        C

         * DISPLAY MODULE DUMP;

           aggrmodule
           funcmodule
           opermodule
           procmodule

## 4.26 CREATE TRIGGER

The `CREATE TRIGGER` command creates the trigger definition into the database data dictionary.

Syntax

  `CREATE TRIGGER trigger_name` | `|BEFORE |AFTER` | `| |`

  |  |  | `|DELETE |INSERT |UPDATE [ OF (attr {, attr})] |SELECT [ OF (attr {, attr})]` | `| {, | | |` | `|DELETE |INSERT |UPDATE [ OF (attr {, attr})] |SELECT [ OF (attr {, attr})]` | `| } | | |`

         ON table [FOR EACH ROW [when_clause]]

         EXECUTE procedure_name;

  where:

  `trigger_name` | is the trigger name. Trigger name is case sensitive. The rules for trigger name is the same as table name and attribute name.
  `when_clause` | has the following syntax:
  |  |  | `WHEN` | `|b_expr |(b_expr) |b_expr AND b_expr |b_expr OR b_expr |NOT b_expr` | `| | | | |`
  `b_expr` is a boolean expression. Details on the boolean expression is described in the WHERE Clause of this manual with the following exceptions: It does not support subquery. Attribute name must prefixed with the qualifier `NEW.` or `OLD.`. The referenced attribute can only come from the table that trigger was created on.
  `procedure_name` | is a PSM (Persistent Stored Modules) procedure name.

Notes

1. When creating a trigger, the `procedure_name` must be declared first through the `CREATE MODULE` command or error message will occur.

   `UPDATE MODULE` command must be issued before trigger can be used or an error message complaining non-existence of `procedure_name` will occur.

2. The procedure for the trigger is a PSM procedure with no parameters. If a module procedure with parameter is used with the `CREATE TRIGGER` command, error message will be given.

3. Trigger that is created without `FOR EACH ROW` option is called statement trigger. It triggers the function once when SQL command is executed; even if the operation is applied to multiple rows.

   Trigger that is created with `FOR EACH ROW` option is called row trigger. It triggers the function each time the operation is applied to a row of data.

4. The trigger can be specified to fire either before the statement/operation (`INSERT`, `DELETE`, `UPDATE`) is attempted on a row/record or after the operation has been completed. In the case of the `SELECT` trigger however, it can be specified to fire only after the `SELECT` operation has been completed.

5. `UPDATE` and `SELECT` trigger can specify the corresponding attribute lists. If no attribute is specified, it means all attributes of a table.

6. The `when_clause` is associated with `FOR EACH ROW` option. If `FOR EACH ROW` is not assigned in `CREATE TRIGGER` command, the `when_clause` cannot be assigned. Once the `when_clause` is assigned for `FOR EACH ROW` option, only in the case when the accessed row satisfies the condition, the trigger will be fired.

7. The condition in `when_clause` is similar to that in the `WHERE` clause, except that all attributes must be in the table associated with the trigger and qualified by either `new.` or `old.` . Subqueries are not supported.

8. When using attribute values in the `when_clause`,

   - for `UPDATE` triggers, you can refer to old (existing) attribute value (specified as: `old.attr`) and new attribute value (specified as: `new.attr`)
   - for `INSERT` triggers, you can only refer to new attribute value; the old attribute value is not meaningful
   - for `BEFORE DELETE` triggers, you can only refer to old attribute values; the new attribute value is not meaningful
   - for `AFTER DELETE` triggers, the value of neither the old attribute nor the new attribute is meaningful
   If the qualifier is misused, no error message will be given and the attribute value will be treated as `NULL`.

9. When using an update trigger:

   - For row trigger, the values of old record and new record are compared to decide whether the trigger will be fired. When an attribute list is used, only those attibutes in the list are checked; without the attribute list, all attributes in the table are checked. If the corresponding values are the same, the trigger will not be fired.
   - For statement trigger, the values are not checked to fired the trigger. Even if the values are the same before and after update, the trigger will still be fired.

10. Renaming the table and/or attribute name (`RENAME` command) has no effect on the existing triggers. Triggers will function correctly under the new name automatically.

11. Altering the table by adding and/or changing the attributes (`ALTER TABLE` command), has no effect on triggers. Triggers will function correctly under the new table structure automatically. However, if an attribute is deleted, then all the triggers explicitly connected to that attribute will be deleted, the triggers connected to all attribute implicitly (no attribute specified) keep unchanged.

12. It is possible to create a trigger on a view.

13. Trigger can be temporarily disabled or enabled by using the `ALTER TABLE` command. When trigger is created, it is automatically enabled.

14. When multiple triggers are created for the same event, the priority within the trigger definition will determine the order of the trigger procedures to be executed. The priority is set to 0.000 when trigger is created. Use the `ALTER TABLE` command to change the priority of the trigger.

15. Trigger definition can be viewed by using `DISPLAY TABLE table_name ALL;` command.

Privileges Required

  `ALTER` privilege.

Example

  Create a trigger `history_log` whenever change is made to the `loans` table, a procedure `log_event` is executed to record the change.

  Step one is to create a user defined procedure. In this example, `user.c` contains such procedure.

         #include   <usrfns.h>
         #include   <time.h>

         static void  print_rec (void*   mr,
                                 void*   rec,
                                 char*   tag)
         {
            extern  char*   mrganame (addr);

            void*           attr;
            int             i;
            char*           val;

            for (i = 1; ; ++i)
            {
               attr = mrigeta (mr, i);
               if (attr == 0)
               break;

               val = mrgetvs (rec, attr);
               if (val == 0)
                  val = "(NULL)";

               printf ("\t%s %s = '%s'\n",
                        tag, mrganame (attr), val);
            }
         }

         static void  trigger_proc (char*  procname)
         {
            extern  char*   mrgname ();
            extern  int     strcmp ();

            time_t  t;
            void*   mr;
            void*   newrec;
            void*   oldrec;
            char*   s;

            mspsm_trig_get_record_info (& mr, & oldrec, & newrec);

            printf ("+++ Trigger Procedure +++  %s  for table '%s'\n",
                     procname, mrgname (mr));
            time (& t);
            printf ("\ttime: %s", ctime (& t));
            if (oldrec == 0)
                printf ("\tNo Old Record\n");
            else
                print_rec (mr, oldrec, "Old");
            if (newrec == 0)
                printf ("\tNo New Record\n");
            else
                print_rec (mr, newrec, "New");
         }

         void    log_ins_upd_del ()
         {
             trigger_proc ("log_ins_upd_del");
         }

  Compile the source file `user.c` and create a shared object file `user.dll` using `emppsmcc` from the operating system prompt:

         emppsmcc -o user.dll user.c

  Next step is to define module and trigger definition. Then link the shared object file `user.dll` to the module definition:

         CREATE MODULE for_trigger
            PROCEDURE log_event ( )
            EXTERNAL NAME log_ins_upd_del;
         END MODULE;

         CREATE TRIGGER history_log BEFORE
            INSERT OR DELETE OR UPDATE
            ON loans FOR EACH ROW
            EXECUTE log_event;

         UPDATE MODULE for_trigger FROM 'user.dll';

  The command:

         DISPLAY TABLE loans ALL;

  will produce the following result:

         *** Table: loans ***

         Attributes:
           number                integer
           name                  char(25,1)
           date                  date(1)
           amount                dollar(6,1)

         Triggers:
           history_log
             before
             insert or delete or update
             for each row
             execute log_event
             priority 0.000
             enable

         Creator: joe
         Lock Level: RECORD

         Table #: 8
         Records: 12
         Record size: 36

  The following is the sample output from the trigger:

         INSERT loans VALUES (5, 'Mosca', '2 Mar 1998', '999'
                              3, 'Jones', '7 Feb 1998', '800');

         +++ Trigger Procedure +++  log_ins_upd_del  for table 'loans'
                 time: Tue Mar  3 10:55:49 1998
                 No Old Record
                 New number = '5'
                 New name = 'Mosca'
                 New date = '2 March     1998'
                 New amount = '$999.00'
         +++ Trigger Procedure +++  log_ins_upd_del  for table 'loans'
                 time: Tue Mar  3 10:55:49 1998
                 No Old Record
                 New number = '3'
                 New name = 'Jones'
                 New date = '7 February  1998'
                 New amount = '$800.00'

         UPDATE loans SET amount = '999.99' WHERE date > '31 Dec 1997';

         +++ Trigger Procedure +++  log_ins_upd_del  for table 'loans'
                 time: Tue Mar  3 10:55:49 1998
                 Old number = '5'
                 Old name = 'Mosca'
                 Old date = '2 March     1998'
                 Old amount = '$999.00'
                 New number = '5'
                 New name = 'Mosca'
                 New date = '2 March     1998'
                 New amount = '$999.99'

         DELETE loans WHERE amount > 800;

         +++ Trigger Procedure +++  log_ins_upd_del  for table 'loans'
                 time: Tue Mar  3 10:55:49 1998
                 Old number = '5'
                 Old name = 'Mosca'
                 Old date = '2 March     1998'
                 Old amount = '$999.99'
                 No New Record

  If the above trigger was created without `FOR EACH ROW` option, then the following command:

         INSERT loans VALUES (5, 'Mosca', '2 Mar 1998', '999'
                              3, 'Jones', '7 Feb 1998', '800');

  will generate the following output:

         +++ Trigger Procedure +++  log_ins_upd_del  for table 'loans'
                 time: Tue Mar  3 10:55:49 1998
                 No Old Record
                 New number = '5'
                 New name = 'Mosca'
                 New date = '2 March     1998'
                 New amount = '$999.00'
                 No Old Record
                 New number = '3'
                 New name = 'Jones'
                 New date = '7 February  1998'
                 New amount = '$800.00'

## 4.27 DROP TRIGGER

The `DROP TRIGGER` command removes the trigger definition from the database data dictionary.

Syntax

      DROP TRIGGER trigger_name

  where:

  `trigger_name` | is the trigger name.

Notes

1. `DROP TRIGGER` will remove the trigger definition from the data dictionary. It will not remove the module procedure that trigger is used. Use `DROP MODULE` to delete the trigger procedure.

2. Trigger can be temporarily disabled or enabled by using the `ALTER TABLE` command instead of removing the trigger definition.

Privileges Required

  `ALTER` privilege.

Example

  To drop the trigger `history_log`:

         DROP TRIGGER history_log;

  will remove the trigger definition from the table `loans`.

## 4.28 DISPLAY DATABASE

The `DISPLAY DATABASE` command prints the name of the database and the names of selected tables in the database.

Syntax

  `DISPLAY` | `|DATABASE |DB` | `| [database] [ON [TABLE] |` | `|table |[NOT] match_op pattern` | `|] |`

  |  |  | `[ALL] [DUMP]` | `[` | `|INTO |ONTO` | `| file]; |`

  where:

  `match_op` | is one of `LIKE`, `MATCH`, `!MATCH`, `SMATCH`, or `!SMATCH`.
  `pattern` | is any pattern accepted by a `WHERE` clause; it must be given as a quoted string.
  `file` | is any filename.

Notes

1. The keyword `DATABASE` may be abbreviated to `DB`.

2. If a database name is specified, the names of the tables in that database are listed. If no database name is specified, the names of the tables in the current database are listed.

3. If a table name is specified, only information for that table is listed. If a pattern match is specified, information for tables that match the pattern is listed.

4. If `ALL` is specified, the structure of each user-created table in the database is also shown. `DUMP` produces output in a condensed form, listing just the table names in the database. If `ALL` is used with `DUMP` the condensed output includes table definitions as well.

5. If `INTO file` or `ONTO file` is used, the output is sent to the named file rather than to the terminal. `INTO` overwrites any existing file, while `ONTO` appends to it. Both create the file if necessary.

6. If there are no tables in the database, the command will print just the name of the database.

7. Views will be displayed along with the tables in the database.

Privileges Required

  None required to display the database itself but only tables on which privileges have been granted will be displayed.

Example

1. List All Tables in a Database

   To list all the tables in the default database, use:

          DISPLAY DB;

   which produces the following output:

          **** Database: repairs ****

          auto parts
          customers
          loans
          personnel

2. List Structure of All Tables in a Database

   To show the structure of all the tables as well, use:

          DISPLAY DB ALL;

   which produces:

          ***Database: repairs ****

          *** Table: auto parts ***

          supplier            char(20,1) Not Null
          phone               char(8,1)
          part name           char(25,1)
          part no.            longinteger
          price               dollar(4,1)

          *** Table: customers ***

          name                char(20,1) Not Null
          address             text(30,124,30,1)
          comments            text(25,1,80,1)

          *** Table: loans ***

          number              integer
          name                char(25,1)
          date                date(1)
          amount              dollar(6,1)

          *** Table: personnel ***

          number              integer
          name                char(25,1)
          phone               char(15,1)
          credit_limit        dollar(6,1)

3. Display the Structure of a Specific Table in a Database

   To restrict the information listed to the `loans` table, use:

          DISPLAY DB ON loans ALL;

   which gives as output:

          **** Database: repairs ****

          *** Table: loans ***

          number             integer
          name               char(25,1)
          date               date(1)
          amount             dollar(6,1)

4. Store the List of Tables in a Database into an Operating System File

   To place the list of tables in the current database into a file called `dblist`, use:

          DISPLAY DB INTO 'dblist';

## 4.29 CREATE REPLICATION MASTER

The `CREATE REPLICATION MASTER` claims a table to be a candidate Replication Master Table of a Replication Table (usually a Replicate Table). This command adds a Replication Master Entry for a replication table.

Syntax

      CREATE REPLICATION MASTER replication_master_info

         ON replication_table [WITH FORCE];

  where:

  `replication_master_info` | Information to access Replication Master Table. It consists of replication master server name, database name and table name of the replication master table. This is the table to be claimed as a candidate replication master table. It's format is:
       | server_name : database_name : table_name  |
       | server_name : database_name .. table_name |
  `replication_table` | is the name of the table for which the replication master entry will be added.

Notes

Notes and examples for this command can be found in [ Manual F1: Replication User's Guide: References].

Privileges Required

  `ALTER` privilege on `replication_table`.

## 4.30 DROP REPLICATION MASTER

The `DROP REPLICATION MASTER` command unclaims one or more tables from being candidate Replication Master Tables of a Replication Table (usually a Replicate Table). This command removes one or more Replication Master Entries for a Replication Table.

Syntax 1

      DROP REPLICATION MASTER replication_master_info

         ON replication_table [WITH FORCE];

Syntax 2

      DROP ALL REPLICATION |MASTERS | ON replication_table [WITH FORCE];
                           |MASTER  |

where:

`replication_master_info` | Information to access Replication Master Table. It consists of replication master server name, database name and replication master table name. This is the table to be unclaimed from being a candidate replication master table. It's format is:
     | server_name : database_name : table_name  |
     | server_name : database_name .. table_name |
`replication_table` | is the name of the table for which the replication master entry will be removed.

Notes

Notes and examples for this command can be found in [ Manual F1: Replication User's Guide: References].

Privileges Required

  `ALTER` privilege on `replication_table`.

## 4.31 CREATE REPLICATION REPLICATE

The `CREATE REPLICATION REPLICATE` command authorizes a Replication Table to have a table as its candidate Replication Replicate Table. This command creates a Replication Replicate Entry for a Replication table.

Syntax:

         CREATE REPLICATION REPLICATE replication_replicate_info

            ON replication_table [WHERE srsc];

  where:

  `replication_replicate_info` | Information to access Replication Replicate Table. It consists of host name, database name and table name of the Replication Replicate Table. It's format is:
       | host_name : database_name : table_name  |
       | host_name : database_name .. table_name |
  Here, the `host_name` is the name of the host where the database resides. If it is in a different network than the `replication_table`, complete network address must be specified. `host_name` can also be IP address. The `database_name` is supposed to be absolute path to a replication database. If not, Empress issues a warning message, and considers this to be a logical database name. In addition, `database_name` is case sensitive. Hence improper use of characters in specifying disk drive or path in Microsoft Windows environment may produce error in creating replicate table.
  `replication_table` | is the name of the replication table for which the Replication Replicate Entry will be added.
  `srsc` | Subset Replication Search Condition. This is a restricted SQL search condition for subset replication. SRSC does not allow: explicit database and table references joins with other tables Sub-query statements aggregate function grouping clauses

Notes

Notes and examples for this command can be found in [ Manual F1: Replication User's Guide: References].

Privileges Required

  This command is to be used by DBA, or the creator of `replication_table` (Note that this is different from `DBA` group privilege).

## 4.32 DROP REPLICATION REPLICATE

The `DROP REPLICATION REPLICATE` command takes away the authorization from a Replication Table to have a table as its candidate Replication Replicate Table. This command removes one or more Replication Replicate Entries for a Replication Table.

Syntax 1

      DROP REPLICATION REPLICATE replication_replicate_info

          ON replication_table;

Syntax 2

      DROP ALL REPLICATION |REPLICATES | ON replication_table;
                           |REPLICATE  |

where:

`replication_replicate_info` | Information to access Replication Replicate Table. It consists of host name, database name and table name of the Replication Replicate Table. It's format is:
     | host_name : database_name : table_name  |
     | host_name : database_name .. table_name |
Here, the `host_name` is the name of the host where the database resides. If it is in a different network than the `replication_table`, complete network address to reach the host must be specified. `host_name` can also be IP address. The `database_name` is supposed to be absolute path to a replication database. If not, Empress issues a warning message, and considers this to be a logical database name.
`replication_table` | is the name of the replication table for which the replication replicate entry will be dropped.

Notes

Notes and examples for this command can be found in [ Manual F1: Replication User's Guide: References].

Privileges Required

  This command is to be used by DBA, or the creator of `replication_table` (Note that this is different from `DBA` group privilege).

## 4.33 CREATE REPLICATE TABLE

The `CREATE REPLICATE TABLE` command is used to create a table as a Replication Replicate Table of a Replication Master table.

Syntax

         CREATE [AND INSERT INTO] REPLICATE [TABLE] replicate_table

            FROM replication_master_info;

  where:

  `replication_master_info` | Indicates access information to the replication master table from which `replicate_table` will be created. It consists of replication master server name, database name and table name of the master table. It's format is:
       | server_name : database_name : table_name  |
       | server_name : database_name .. table_name |
  `replicate_table` | is the name of the table that is going to be a replicate of the replication master table.

Notes

Notes and examples for this command can be found in [ Manual F1: Replication User's Guide: References].

Privileges Required

  `INSERT` privilege on the system table `sys_tables` of the database to contain `replicate_table`. This is the same privilege required for creating any database table.

## 4.34 CREATE ROLE

The `CREATE ROLE` command creates a role in a database. A role generally refers to a title or a set of duties, which is a named bundle of zero or more privileges. Roles are designed to ease the administration of privileges. They control access to database tables and permit easier management in the database.

Syntax

  `CREATE ROLE role`;

  where:

  `role` | is a name of a role.

Notes

1. Roles allow to collect users into a single unit against which privileges can be applied.
2. The `CREATE ROLE` command specifically defines `role` to be a role rather than a User. This `role` cannot be a defined user in the database schema, such as `DBA, USER, PUBLIC,` etc.

Example

Create a Role

To create a role `helpdesk`, do:

       CREATE ROLE helpdesk;

## 4.35 DROP ROLE

The `DROP ROLE` command removes a role from a database.

Syntax

  `DROP ROLE role`;

  where:

  `role` | is a name of a role.

Notes

1. Only creator of the `role` can drop it.
2. For each user/role, it was granted use of the role, the following revoke command will be called:

           REVOKE role FROM grantee RESTRICT

Example

Drop a Role

If the user `joe` enters the following sequence of commands:

       CREATE ROLE helpdesk;
       CREATE ROLE tech;
       GRANT helpdesk TO peter WITH ADMIN OPTION;
       GRANT helpdesk TO tech;
       DROP ROLE helpdesk;

where `peter` is another user and `helpdesk` and `tech` are roles, `DROP ROLE` command will implicitly invoke the following two commands:

       REVOKE helpdesk FROM tech RESTRICT;
       REVOKE helpdesk FROM peter RESTRICT;

However, if `peter` grants `helpdesk` role to another user before `joe` tries to drop the `helpdesk` role, `DROP ROLE` command will fail since it cannot remove dependant roles.

To drop a role `tech`, do:

       DROP ROLE tech;

## 4.36 GRANT ROLE

The `GRANT ROLE` command grants the use of one or more roles to one or more users/roles.

Syntax

  `GRANT `role` {, `role` } TO `grantee` {, `grantee` } [ WITH ADMIN OPTION ]`

  where:

  `role` | is a name of a role.
  `grantee` |
  It can be one of: | `|username |role |PUBLIC` | `| | |`
  It represents the name of a user/role. `PUBLIC` refers to all users and `username` is an operating system account name or login name.

Notes

1. The optional `WITH ADMIN OPTION` clause defines a grantable role; one that `grantee` may, in turn, grant to other users/roles.
2. The grantor of the roles (i.e one who grants the roles) must hold these roles as grantable roles (that is, `WITH ADMIN OPTION`).

Example

Grant a Role

To grant a grantable role `helpdesk` to user `peter`, do:

       GRANT helpdesk TO peter WITH ADMIN OPTION;

## 4.37 REVOKE ROLE

The `REVOKE ROLE` command revokes one or more roles from one or more users/roles.

Syntax

         REVOKE [ ADMIN OPTION FOR ] role {, role }

                FROM grantee {, grantee } | RESTRICT |
                                          | CASCADE  |

  where:

  `role` | is a name of a role.
  `grantee` |
  It can be one of: | `|username |role |PUBLIC` | `| | |`
  It represents the name of a user/role. `PUBLIC` refers to all users and `username` is an operating system account name or login name.

Notes

1. The optional `ADMIN OPTION FOR` clause allows one to revoke only the grantability of a role(s).
2. If the `REVOKE ROLE` command specifies `CASCADE`, it cascades down to revoke roles that would otherwise be abandoned. If the `REVOKE ROLE` command includes `RESTRICT`, the revoke succeeds only if the role being revoked has no dependent roles.

Example

Revoke a Role

To revoke a role `helpdesk` from the user `peter`, do:

       REVOKE helpdesk FROM peter;

## 4.38 DISPLAY ROLE

The `DISPLAY ROLE` command displays role(s) in a database and it can be used in Empress interactive SQL.

Syntax

         DISPLAY ROLE [role] [ALL] [| INTO | file];
                                    | ONTO |

  where:

  `role` | is a name of a role.
  `file` | is a file name.

Notes

1. If `role` is not specified, the information on all database roles will be displayed. More details can be displayed with a keyword `ALL`.
2. The output of the command may be diverted from the terminal into a new file or appended to an existing file by specifying `INTO` or `ONTO` respectively, and a file name.

Example

Display a Role

To display a role `helpdesk`, do:

       DISPLAY ROLE helpdesk;

which produces the following output:

       ***  Role: helpdesk  ***

         Creator:         joe
         Admin Option:    Y
         Grantee:         tech
         Grantee:         peter (Admin)