# CHAPTER 3: Query Language Topics

## 3.1 Introduction

This chapter covers topics of general relevance to using the Empress Query Language:

- Joining tables
  Considerations for joining tables - optimizing joins
- SELECTs and Indices
  Utilizing indices to optimize retrieval performance
- Creating and Using View
  Using views for data analysis and manipulation
- Update Type Operations on Views
  Constraints which define updatable views
- Views into Distributed Data
  Creating complex views across multiple databases
- Subqueries
  Creating complex boolean expressions using subqueries
- Date Arithmetic
  Date operators and valid operations that may be performed on date values
- Converting Expression Values to Data Types
  Examples of conversion and reformatting of output values
- Privileges in the Database
  Determining default privileges
  Special user classes (DBA, CREATOR, USER, PUBLIC)
  Classes of privileges
- Transactions
  Execute commands without affecting tables irrevocably and the transactions may subsequently be committed to update or rolled back
- Locks and Table Access
  Prevents access conflicts and preserves data integrity by controlling concurrent accesses to a table

## 3.2 Joining Tables

In a relational database it is possible to extract data from several tables at once. In most cases, this kind of operation, called a join, is done with tables that are logically related to each other through common attributes. For example, the tables `personnel` and `loans` are logically related through their name attributes. This means that given a name, a search of the `loans` and `personnel` tables using this attribute can show both the personnel number of an employee and what loans are currently outstanding.

It is possible to join tables that are logically unrelated. For instance, selecting from two tables without using a `WHERE` clause joins every record of the first table with every record of the second. The interpretation of the results is up to you. This section discusses joining tables using related attributes.

When joining tables on related attributes a join condition is formed by comparing the attributes with the equality (=) operator. When equating attributes in a join, the attributes must be of the same data type. It is not necessary, however, that the attributes have the same name. The attribute `name` in `personnel` could have been called `employees`, without affecting its logical connection to `name` in `loans`. Similarly, the fact that two attributes have the same name does not necessarily mean that a join can be performed on them (e.g., `name` in `personnel` and `name` in `customers`).

### 3.2.1 Designing Joins

If you are not careful, join operations can be quite time-consuming, as the speed of a join operation depends on the way the selection is specified. This usually does not matter, particularly for day-to-day use, but can be important when designing joins to be used in applications.

The `WHERE` clause in a join will generally restrict the records chosen from each of the tables and link the records from tables. For the `repairs` database, a typical join could be:

       SELECT name, credit_limit, amount FROM personnel, loans
          WHERE number = 10
          AND personnel.name = loans.name;

In this case, the condition `number = 10` limits the set of records selected from `personnel` while the condition `personnel.name = loans.name` links records of the two tables.

Approach of Empress' to a join operation can be visualized as follows:

1. Find all records from the first table satisfying the limiting conditions.
2. For each of these records, find all records of the second table that satisfy the limiting conditions on that table and that satisfy clauses which link records of this table to the record of the first.
3. For each record so selected, find records of the third table, and so on.

Note that for each record of the first table, the selection of a record from the second table is done as if each clause mentioning an attribute of the first were in fact a comparison with a constant. Similarly, selection from the third table occurs as if each mention of attributes from the first and second were constants.

In our example, when a selection has been made from `personnel` according to the personnel `number`, the selection from `loans` will be made using the condition that `loans.name` be equal to the `personnel.name` values taken from the `personnel` record. Since these have already been narrowed down to a single value (Kilroy, whose personnel number is 10), this severely limits the number of loans records that must be considered in the join.

In the previous example, had `loans` occurred before `personnel` where the two tables are listed in the first line of the command, no conditions would have been specified in the first selection. The result of this would have been that each of the records from `loans` would have been tested against every record in `personnel` to find the ones in both tables, and then this set of records checked through to find the ones with the right personnel number. With `personnel` specified before `loans`, the records of the `loans` table are only tested against the record already selected from `personnel`.

A join is most efficient if the tables are ordered so that the largest number of records are filtered out as early in the join process as possible. In the example above, specifying `personnel` first filters out all but one record of `personnel`.

## 3.3 Selects and Indices

In the relational database management system, an index on an attribute is a mechanism to speed up data retrieval. When you are looking for a particular record, an index can serve as a logical pointer to the data's physical location. Just like an index in a book, indices helps you to quickly find the information you want to read. However, you have to create the index in order for Empress to use it. Once the index is created, it is up to Empress to decide whether or not to use the index for each query you submit. For example, to create an index on the attribute `name` of the `personnel` table:

       CREATE INDEX personnel_name ON personnel(name);

where `personnel_name` is an index name. With Empress, a `SELECT` command will not always use the indices on the attributes of the table from which records are selected. First, an indexed attribute must be referenced in the `WHERE` clause for its index to be used. Second, the attribute must be appropriately situated in the `WHERE` clause. For example:

       SELECT * FROM personnel WHERE credit_limit > 500;

The above query will not use the index on the `personnel.name`. The Empress system variable `MSQUERYPLAN` (when set) provides an analysis of the query with regard to the indices used and to how joins were performed.

To drop the index on `personnel.name`, type in:

       DROP INDEX personnel_name;

## 3.4 Creating and Using Views

In Empress, you can create a virtual table based on one or more physical tables. This virtual table is called a view. A view is like a movable window, through which you can see data. It is a virtual table because it does not exist as a real table in the database. It exists as a definition of the SELECT statement in the data dictionary. For example, to query information on the `loans` table as well as the credit limit information of the person (in the `personnel` table), you need to type:

       SELECT name, date, amount, credit_limit
          FROM personnel, loan
          WHERE personnel.name = loans.name;

The query involves a lot of typing, but if you create a view called `loansinfo`:

       CREATE VIEW loansinfo
          AS SELECT name, date, amount, credit_limit
          FROM personnel, loans
          WHERE personnel.name = loans.name;

And do:

       SELECT * FROM loansinfo;

this will achieve the same result. Empress will create a virtual table called `loansinfo` and the information will be retrieved based on the information in the `personnel` and `loans` tables as you query the `loansinfo`. To drop the definition of the view, use:

       DROP VIEW loansinfo;

In this example, the real tables `personnel` and `loans` underlying a view are called base tables. A complex view is a view with more than one base table. Empress allows you to operate on a view as if it is a real table; however, some update operations on a complex view can violate the integrity of the base tables. In the following sections, we will discuss conditions under which Empress will permit update type operations (delete, update or insert).

## 3.5 Update Type Operations on Views

This is a discussion of the conditions under which Empress will permit update type operations on complex views. A view is a virtual table. It is a structure that appears as a table, though no data is stored in it. A view is defined through a SELECT command applied to one or more tables; selecting from the view retrieves the same data from the underlying tables as the SELECT command that defines the view. These underlying tables may themselves be virtual, that is, views on other tables. We shall call the real tables underlying a view its base tables. A complex view is a view with more than one base table. By update type operation, we mean a delete, update or insert operation.

The conditions allowing update type operations on views fall into three general categories:

1. A basic condition that allows delete operations. Three rules are given to determine whether a view satisfies the basic condition.

2. A first additional condition that allows update operations. A view that satisfies the basic condition (that permits deletes) and this additional condition may have records updated.

3. A second additional condition that allows insert operations. A view that satisfies the conditions allowing deletes and updates, and this additional condition, may have records inserted.

### 3.5.1 The Basic Condition

In general, a view must satisfy certain conditions in relation to its base tables in order for any update type operations to be allowable on it. To see why, consider a view `V1` created from a join of tables `T1` with attributes `a` and `b`, and `T2` with attributes `c` and `d`.

       T1(a,b)     T2(c,d)     V1(a,b,c,d) = SELECT FROM T1,T
                                                WHERE T1.a = T2.c
       0 0         0 2         0 0 0 2
       1 1         0 3         0 0 0 3

If the record `V1(0 0 0 2)` were deleted from `V1`, records `T1(0,0)` and `T2(0,2)` should be deleted from the base tables, with the following results:

       T1(a,b)     T2(c,d)     V1(a,b,c,d) = SELECT FROM T1,T
                                                WHERE T1.a = T2.c

       1 1         0 3

Deleting one record from `V1` would result in removing two records from the view.

Note that this outcome is data dependent, in this case the inclusion of record `T1(0,0)` in two records of the view. In general, when a view includes more than one record containing data from a given record in a base table, deleting any of those records will remove all of them from the view. This effect is peculiar to views and does not occur with tables. To prevent views from having this characteristic to distinguish them from tables, Empress will not allow update type operations on views that do not satisfy the following Basic Condition:

Basic Condition | To allow update type operations on a view, the view should satisfy the condition that any record from a base table can, in principle, appear at most once in the view.

Three rules to determine whether a view satisfies the basic condition are given below. These rules concern the join conditions that determine the structure of the view, as expressed in the `WHERE` clause used to define the view, and the attributes involved in the join.

1. Rule One:

   The `WHERE` clause used to define the view must be limited to `AND` conjunctions of a limited set of conditions. The set of conditions is limited to the following:

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

   This rule means that if you wish to do update type operations on a view, the `WHERE` clause that defines the view cannot contain:

   - The `NOT` condition.
   - The `OR` conjunction.
   - Between tables, any comparison other than "=".

   This rule assures that the next rule is not compromised.

2. Rule Two:

   There must be sufficient limiting conditions in the `WHERE` clause of the view to prevent a record of any base table from occurring more than once in the view. In effect, this means that 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 (crossing intermediate points if necessary), then the rule is satisfied.

   To illustrate the rule, consider the tables `T1(a,b,c)`, `T2(d,e)`, `T3(f,g,h)` and `T4(i,j)`. The following joins satisfy the rule. Using the line and dot representation, it is possible to move among all points along lines. Note that it is not important which attribute from each table is involved in each condition.

          SELECT .... FROM T1, T2, T3, T4
             WHERE T1.a = T2.d AND T2.e = T3.h AND T3.g = T4.i

          SELECT .... FROM T1, T2, T3, T4
             WHERE T1.a = T2.d AND T2.e = T3.h AND T2.d = T4.i

          SELECT .... FROM T1, T2, T3, T4
             WHERE T1.a = T2.d AND T1.b = T3.h AND T1.c = T4.i

   The following joins do not satisfy the rule. Using the line and dot representation, it is not possible to move among all points along lines.

          SELECT .... FROM T1, T2, T3, T4
             WHERE T1.a = T2.d

          SELECT .... FROM T1, T2, T3, T4
             WHERE T1.a = T2.d AND T2.e = T4.i

          SELECT .... FROM T1, T2, T3, T4
             WHERE T1.a = T2.d AND T3.h = T4.i

   An adjunct to this rule is that a table cannot be selected twice in the same view. That is, if a table is joined to itself to form a view, no update type operations will be permitted on the view.

3. Rule Three:

   For every permutation of base table pairs, the set of attributes from the first table that are involved in equality comparisons with attributes of the second table must include a key (unique index). That is, there must either be a unique index on one of the attributes or a unique composite index on a subset of them. (In the case of a composite index, it cannot involve attributes not part of the `WHERE` clause of the join.) This rule is also necessary to prevent two records in a table being combined with the same record in another. Refer back to the first example:

          T1(a,b)     T2(c,d)     V1(a,b,c,d) = SELECT FROM T1,T2
                                                   WHERE T1.a = T2.c
          0 0         0 2         0 0 0 2
          1 1         0 3         0 0 0 3

   This rule demands that unique indices exist on `T1.a` and `T2.c`. Table `T2` cannot satisfy the rule; if `T2` did not have the record `(0 3)` it could satisfy the rule, and consequently the view would not contain the duplicate contribution from `T1(0,0)`.

   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 equality comparisons 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.

   For instance, consider the tables `T1(a,b,c)`, `T2(d,e)` and `T3(f,g,h)` and the following three joins:

   - Given the join:

            SELECT .... FROM T1, T2, T3
               WHERE T1.a = T2.d and T2.d = T3.f

     We can compile the lists:

            T1 to T2: T1.a
            T2 to T1: T2.d
            T2 to T3: T2.d
            T3 to T2: T3.f

     Unique indices must exist for `T1.a`, `T2.d` and `T3.f`.

   - Given the join:

            SELECT .... FROM T1, T2, T3
               WHERE T1.a = T2.d AND T2.e = T3.f

     We can compile the lists:

            T1 to T2: T1.a
            T2 to T1: T2.d
            T2 to T3: T2.e
            T3 to T2: T3.f

     Unique indices must exist for `T1.a`, `T2.d`, `T2.e` and `T3.f`.

   - Given the join:

            SELECT .... FROM T1, T2, T3
               WHERE T1.a = T3.f AND T2.e = T3.g
               AND T2.d = T3.f

     We can compile the lists:

            T1 to T3: T1.a
            T2 to T3: T2.e, T2.d
            T3 to T1: T3.f
            T3 to T2: T3.g, T3.f

     Unique indices must exist for `T1.a` and `T3.f`. Either a unique index must exist for `T2.d` or `T2.e`, or there must be a composite unique index on `T2.d` and `T2.e`.

   Empress permits records to be deleted from views that satisfy the basic condition as expressed in the three basic rules. To permit update and insert operations, additional conditions must be satisfied.

### 3.5.2 First Additional Condition

Consider again tables `T1(a,b)` and `T2(c,d)`, with a view `V1` defined as:

       CREATE VIEW V1 AS SELECT FROM T1, T2
          WHERE T1.a = T2.c;

Suppose also that there are unique indices on `T1.a` and `T2.c`, satisfying Rule Three. The view `V1` then satisfies all the basic rules given above.

       T1(a,b)     T2(c,d)     V1(a,b,c,d) = SELECT FROM T1,T2
                                                WHERE T1.a = T2.c
       0 0         0 2         0 0 0 2
       1 1

Suppose you wished to update the record in `V1` to `(0, 0, 3, 2)`. If this were permitted the record would no longer be included in the view, as it would violate the view definition. In effect, an update operation would appear to delete a record. This effect is caused by the inclusion in the view of two attributes that are equated in the `WHERE` clause.

Again to prevent views from having a characteristic distinguishing them from tables, Empress does not allow an update operation on such views.

When joining tables to form a view, equating pairs of attributes from base tables can result in sets of attributes from two or more tables specified to be equal.

The First Additional Condition states that:

First Additional Condition | For every set of attributes in the base tables that must be equal, only one attribute from each set 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.

Views that violate this condition can give rise to a further problem.

Consider again tables `T1(a,b)` and `T2(c,d)`, with unique indices on `T1.a` and `T2.c`, and a view `V1` defined as:

       CREATE VIEW V1 AS SELECT b, c, d FROM T1, T2
          WHERE T1.a = T1.b AND T1.a = T2.c;

With data:

       T1(a,b)     T2(c,d)     V1(b,c,d) = SELECT b, c, d FROM T1,T2
                                             WHERE T1.a = T1.b AND T1.a = T2.c
       0 0         0 2         0 0 2
       1 1

An attempt to update the record in `V1` to `(0, 1, 2)` gives rise to the ambiguity: the `WHERE` clause defining the view equates attribute `T1.a` to both `T1.b` and `T2.c`; when these two attributes are assigned different values, which value should be assigned to `T1.a`? The condition that restricts the view to containing only one of a set of attributes specified to be equal prevents this ambiguity from occurring.

Empress permits records to be updated in views that satisfy the first additional condition as well as the basic condition.

To specify attributes as equal in the `WHERE` clause of a view they must be of the same data type. The attributes may, however, be defined with different data type parameters, and thus be capable of storing numbers of different sizes or different types of character data. Data that is input to a view will not be truncated to fit any attribute. If the data cannot be inserted into any of the attributes the operation will not proceed and an error message will be printed.

If conversion errors are generated for any of the attributes in the base tables, the operation will not proceed and an error message will be printed.

### 3.5.3 Second Additional Condition

The second additional 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. This is because any attribute of a base table that does not appear in the view will have a null value inserted for it if a record is inserted into the view.

The Second Additional Condition states that:

Second Additional Condition | Any `NOT NULL` attributes in any base tables must be in the view or equal to an attribute in the view.

Empress permits records to be inserted into views that satisfy the first and second additional conditions as well as the basic condition.

If data to be updated or inserted into a view conflict with a unique index constraint on a base table, and the record in the base table is identical to the record that would be inserted into the table, the existing record is taken to satisfy the operation.

Consider the view `V1` defined as:

       CREATE VIEW V1 AS SELECT a, b, d FROM T1, T2
          WHERE T1.a = T2.c;

With data:

       T1(a,b)     T2(c,d)     V1(a,b,d) = SELECT a, b, d FROM T1, T2
                                              WHERE T1.a = T2.c
       0 0         0 0         0 0 0
                   1 1

There are unique indices on `T1.a` and `T2.c`, so records can be inserted in the view. If the record `(1, 1, 1)` is inserted into the view, the result is:

       T1(a,b)   T2(c,d)   V1(a,b,d) = SELECT a, b, d FROM T1, T2
                                          WHERE T1.a = T2.c
       0 0       0 0       0 0 0
       1 1       1 1       1 1 1

The record `(1, 1)` to be inserted into `T2` is identical with the second record already in `T2`. Since there is a unique index on `T2.c` a second `(1, 1)` record cannot be inserted into the table. The existing `(1, 1)` is taken to satisfy the insert operation on `T2`. The insertion into `T1` proceeds normally.

## 3.6 Views Into Distributed Data

Views can be defined to give access to data residing in several databases which may reside on different file systems or nodes. If the views are properly defined to allow update type operations, they are functionally indistinguishable from tables in the default database. This section presents a simple example of a view that illustrates how to access distributed data.

First create two databases:

       empmkdb /usr/db1
       empmkdb /usr2/db2

Invoke `empsql` on one of the databases:

       empsql /usr/db1

Now create a table in each database:

       CREATE tab1 (a1, b1);
       CREATE "/usr2/db2":tab2 (a2, b2);

You can now create a view of `db2:tab2` in database `db1`:

       CREATE VIEW tab2 AS SELECT FROM
       "/usr2/db2":tab2;

Note that in Windows NT environment, Backslash (\) convention also works for Windows NT (i.e., using `\usr\db1` instead of `/usr/db1`)when addressing the path.

The view `db1:tab2` is functionally indistinguishable from the table `db2:tab2` insofar as selects, inserts, updates and deletes are concerned provided they have the same user access privileges associated with them.

You can now create a view `tab12` that gives access to both `tab1` and `tab2`. Access to `tab2` can be through either the table `db2:tab2` or the view `db1:tab2`. In this example we will use the view db1:tab2.

       CREATE VIEW tab12 AS SELECT tab1.a1, tab1.b1,tab2.b2
          FROM tab1, tab2 WHERE tab1.a1 = tab2.a2;

This view is a join of `tab1` and `tab2` with sufficient join conditions to allow update type operations on the view. However, to allow insert operations, the attributes of the join condition must have unique indices:

       CREATE UNIQUE INDEX ON tab1.a1;
       CREATE UNIQUE INDEX ON "/usr2/db2":tab2.a2;

Note that the index has to be placed on the table `tab2`, not the view (indices cannot be placed on views).

The view `tab12` can now be manipulated like an ordinary table:

       INSERT tab12 VALUES (1,2,3,4,5,6,7,8,9);
       SELECT FROM tab12;

       a1    b1     b2

       1     2      3
       4     5      6
       7     8      9

       SELECT FROM tab1;

       a1    b1

       1     2
       4     5
       7     8

       SELECT FROM tab2;

       a2    b2

       1     3
       4     6
       7     9

       DELETE FROM tab12 WHERE a1 = 1;
       SELECT FROM tab12;

       a1    b1     b2

       4     5     6
       7     8     9

## 3.7 Subqueries

A subquery is a `SELECT` statement that is nested inside the `WHERE` clause of another statement. In a `WHERE` clause, a subquery may appear on the right side of a comparison. A subquery is:

|  |  | `[` | `|ANY ||ALL` | `|] (select_command) |`

A simple form of `WHERE` clause using this would be:

|  |  | `WHERE attr [NOT]` | `| = |!= | < |<= | > |>= |IN` | `| [ | | | | | |` | `|ANY |ALL` | `|] (select_command) |`

If `ANY` is used, the boolean expression is true if the comparison is true for at least one value returned by the nested select. The boolean expression is false if the comparison is false for every value returned by the nested select, or if the nested select returns no values.

If `ALL` is used, the boolean expression is true if the comparison is true for every value returned by the nested select, or if the nested select returns no values. The result is false if the comparison is false for at least one value returned by the nested select.

A subquery may be used to produce a list of values to be compared with, as in:

       SELECT ... WHERE attr [NOT] IN (SELECT attr...);

In addition, a boolean expression in a `WHERE` clause may consist of:

       EXISTS subquery_command

When `EXISTS` subquery is used, the `WHERE` clause is true if one or more records are selected in the `SELECT` command.

Subqueries can be used in `WHERE` clauses in `UPDATE` and `DELETE` statements as well as `SELECT` statements.

### 3.7.1 Examples of Subquery

The following `WHERE` clause finds all loans less than the average loan amount over all loans, by using a nested select statement to find the average.

       SELECT FROM loans
          WHERE amount < (SELECT AVG (amount) FROM loans);

It produces:

       name                                   date        amount

       Jones                      7 February  1992        $33.95
       Wladislaw                 27 February  1992        $55.00
       Jones                      3 April     1992        $25.00
       Wladislaw                 12 May       1992        $25.00
       Peterson                   6 June      1992        $50.00
       Wladislaw                 25 June      1992        $75.00

The next command retrieves loans to people listed in the `personnel` table, illustrating the use of `ANY`.

       SELECT FROM loans WHERE name = ANY (SELECT name FROM personnel);

It produces:

       name                                   date        amount

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

An equivalent command, illustrating the use of `IN` is:

       SELECT FROM loans WHERE name IN (SELECT name FROM personnel);

It produces:

       name                                   date        amount

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

To find those loans that exceed everyone's credit limits:

       SELECT FROM loans WHERE amount >
          ALL (SELECT credit_limit FROM personnel);

It produces:

       name          date               amount

There are no loans that large.

## 3.8 Date Arithmetic

`DATE` and `TIME` attributes may have arithmetic performed on them, or have their year, month, day, etc. extracted from them. The following keywords may be used in expressions involving dates or times, or simply in expressions by themselves, in which case their results are converted to integers:

       year       years      yearof
       month      months     monthof
       week       weeks      dayof
       day        days       hourof
       hour       hours      minuteof
       minute     minutes    secondof
       second     seconds    weekof

The `yearof`, `monthof` and `dayof` operators produce integers; years are 0 or greater, months range from 1 to 12, weeks from 1 to 52, and days from 1 to the number of days in the month. `hourof`, `minuteof`, and `secondof` also produce integers; hours range from 0 to 23, minutes and seconds from 0 to 59.

Note that trying to find `hourof`, `minuteof`, or `secondof` a `DATE` rather than a `TIME` attribute will give zero as the result.

Note also that when supplying a date to an expression, it must either be in universal date format (yyyymmdd) or correspond to the day, month, year order specified by the date picture variable `MSDATEPIC`, in order to be recognized (for more information on the `MSDATEPIC` variable, refer to the Empress: Database Administrator's Guide).

The following examples illustrate date arithmetic.

       SELECT date + 2 months CONVERT TO DATE (1) FROM loans;

It produces:

       convert (date + (2 months)) to date

                          2 April     1992
                          7 April     1992
                         16 April     1992
                         27 April     1992
                          3 June      1992
                          4 July      1992
                         12 July      1992
                          6 August    1992
                         25 August    1992
                         12 October   1992
                          9 November  1992
                         14 December  1992

The following uses the `dayof` operator:

       SELECT dayof date FROM loans;

It produces:

       (dayof date)

                  2
                  7
                 16
                 27
                  3
                  4
                 12
                  6
                 25
                 12
                  9
                 14

Adding two complete dates does not make sense, but subtracting them does. The result is the interval in days between them. To find how old each of the loans is, taking the current date as Jan 1, 1992, and assuming `MSDATEPIC` has its default value of "dd aaaaaaaaa yyyy", the command is:

       SELECT name, "31 Dec 1992" - date FROM loans;

Empress displays:

       name                 ("31 Dec 1992" - date)

       Mosca                                   333
       Jones                                   328
       Kilroy                                  319
       Wladislaw                               308
       Jones                                   272
       Mosca                                   241
       Wladislaw                               233
       Peterson                                208
       Wladislaw                               189
       Jones                                   141
       Scarlatti                               113
       Scarlatti                                78

The value before `years`, `months`, `days`, etc. may be an expression, as long as the expression evaluates to an integer.

The following will generate a heading "outstanding" for the expression.

       SELECT name, '31 Dec 1992' - date PRINT "outstanding" FROM loans;

Empress displays:

       name                       outstanding

       Mosca                              333
       Jones                              328
       Kilroy                             319
       Wladislaw                          308
       Jones                              272
       Mosca                              241
       Wladislaw                          233
       Peterson                           208
       Wladislaw                          189
       Jones                              141
       Scarlatti                          113
       Scarlatti                           78

## 3.9 Converting Expression Values to Data Types

Values resulting from expressions are implicitly converted to the most appropriate generic data type in a Query Language `SELECT` command. A generic data type is a data type without explicit parameter definitions (size, etc.). When this type is not what you need, you may also explicitly convert the data to a specific type. In fact, this is generally the most convenient way to produce nicely formatted output.

The general form for explicitly converting an expression to a data type is either one of the following:

       CONVERT expr [TO] data_type

       expr CONVERT TO data_type

where `data_type` is one of the standard data types (e.g., `CHAR` (40,1), `DATE`(3), `SHORTINTEGER`, `DOLLAR` (4,2), etc.), or the keyword `GENERIC` followed by `CHAR`, `INTEGER`, `DECIMAL`, `FLOAT`, `DATE`, or `EXTERNAL`. `GENERIC EXTERNAL` prints values as though they were all character strings.

Note that conversions may be nested.

To give some examples, the following command:

       SELECT amount, amount * 1.05 CONVERT TO DOLLAR(4,1)
          PRINT newamount FROM loans;

produces:

       amount        newamount

       $150.00         $157.50
       $33.95           $35.64
       $250.00         $262.50
       $55.00           $57.75
       $25.00           $26.25
       $200.00         $210.00
       $25.00           $26.25
       $50.00           $52.50
       $75.00           $78.75
       $300.00         $315.00
       $150.00         $157.50
       $125.00         $131.25

The command:

       SELECT date, date + 6 months
          CONVERT TO DATE(1)
          PRINT Õdue dateÕ WIDTH 20
          FROM loans WHERE name = 'Jones';

produces:

                    date                 due date

        7 February  1992         7 August    1992
        3 April     1992         3 October   1992
       12 August    1992        12 February  1993

The next example calculates simple interest at 24% per annum on all loans more than three months old on August 1, 1992. This requires finding the interval in days between the date the loan was made and the August 1 date. The application must then check whether it is more than three months: if it is, the number of days is multiplied by 0.24/365 to find the interest, then this is converted to a dollar amount. With the date picture variable `MSDATEPIC` set to its default value of "dd aaaaaaaaa yyyy", the command is:

       SELECT date, amount,
          (amount * ("1 August 1992" - date) * 0.24 / 365)
          CONVERT TO DOLLAR(4,1)
          PRINT interest FROM loans
          WHERE "1 August 1992" > (date + 3 months);

and produces:

                    date        amount        interest

        2 February  1992       $150.00          $17.85
        7 February  1992        $33.95           $3.92
       16 February  1992       $250.00          $27.45
       27 February  1992        $55.00           $5.64
        3 April     1992        $25.00           $1.97

## 3.10 Privileges in the Database

The person creating a database is the Database Administrator. The Database Administrator (as `DBA`) has the power to grant any privilege on any table in the database. When the database is created, Empress also creates a data dictionary which containing system tables. By default, the Database Administrator is given `USER` privileges (delete, display, insert, select, update) on the system tables in the data dictionary, with grant authorization. This means that the Database Administrator can exercise these privileges on the tables in the data dictionary, and can grant these privileges to others. Note that the Database Administrator can always grant `ALL` privileges on the system tables to anyone including himself or herself at any time; however, the Database Administrator should never need to exercise the `ALTER`, `DROP`, or `EMPTY` privileges on the data dictionary tables.

In order to create new tables, a user must have insert privilege in the `sys_tables` table in the data dictionary. Since only the Database Administrator has this privilege by default (it is one of the `USER` privileges), initially only the Database Administrator can create tables in the database. To allow someone else to create tables, the Database Administrator must grant insert privilege on `sys_tables` to the other person, preferably as `DBA` (the grantor). Granting privileges as the Database Administrator rather than as a user means that if the Database Administrator changes, the new Database Administrator can easily identify (and revoke, if necessary) privileges previously granted.

When someone creates a table they become the Creator of the table. The Creator (as `CREATOR`) has the power to grant any privilege on that table. The Creator of each table is named in the `sys_tables` table in the data dictionary. The Database Administrator or anyone with sufficient privileges can always change the value of `CREATOR` of any table.

When a table is created, certain privileges are granted on that table to the Creator. These are defaults specified by the administrative variable `MSDBPRIVS` in `tabzero`. The default specification is to grant the `CREATOR` the `DBA` type privileges and the `USER` type privileges to the the user name (ie. the system User ID as `USER`) who created the table. Note that if no privileges were granted by default, the `DBA` of the database and `CREATOR` of the table could still grant themselves or anyone else any privilege on the table.

If the user who created the table wants to allow others to access the table, the user (who creates the table) must grant them or everybody (as the `PUBLIC`) the appropriate privileges. Where privileges are granted with the `GRANT OPTION`, the privileges may also be granted to other users by the grantee; however the `DBA` and `CREATOR` retain ultimate control over privileges on the table since they may revoke any privileges granted; in this way they may revoke any privilege on the table as any grantor who granted the privilege. This aspect of the Database Administrator's control extends to every table in the database.

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.

## 3.11 Transactions

A transaction is a set of Query Language commands that are treated as a unit. When a transaction is in progress, all operation on all databases accessed during the transaction are treated as part of the transaction. Transactions are useful for two reasons:

1. While a transaction is in progress, changes made by commands, such as altering a table, or inserting, deleting, or updating records, do not have an irrevocable effect upon any database until a command is issued to make them permanent. The commands may all be rolled back instead of being made permanent if you wish, leaving the databases in the state they were in before the beginning of the transaction.

2. In case of a disk failure or network crash, the integrity of transactions is ensured by the Empress warm restart facilities. The integrity of transactions spanning several databases is ensured with a two phase-commit protocol. Transactions interrupted by any kind of system failure will be resolved as a unit.

Dividing work-sessions into transactions gives greater flexibility and security in manipulating databases. If commands in a transaction produce unsatisfactory results, they may be undone and tried again. Thus, a major user error, such as emptying the wrong table, is reduced to the minor annoyance of re-running a couple of commands.

A transaction is:

- initiated by a `START WORK` command,
- made permanent by a `COMMIT WORK` command, and
- canceled by a `ROLLBACK WORK` command.

Also:

- A save point can be set in a transaction with the `SAVEPOINT` command.
- The status of a transaction can be displayed with the `DISPLAY WORK` command.

Table 3-1 Transaction Commands

Command | Description
`START WORK;` | Initiates a transaction.
`SAVEPOINT savepoint;` | Defines a save point.
`ROLLBACK WORK TO savepoint;` | Rolls back to a save point.
`DISPLAY WORK;` | Displays status of current transaction.
`ROLLBACK WORK;` | Rolls back and end the transaction.
`COMMIT WORK;` | Commits and end the transaction.

For detail syntax information of the above transaction commands, please refer to the Empress SQL: Reference manual.

### 3.11.1 Nested Transactions and Save Points

Transactions can be nested by marking save points to which you have the option of rolling back. Once in a transaction, you can set a save point in the transaction with the `SAVEPOINT` command. This starts a nested transaction. In the nested transaction you can issue a `ROLLBACK WORK` command naming the save point. This rolls back all commands after the save point, leaving the original transaction in the state it was in when you started the nested transaction.

There is no practical limit to the length of transactions or to how deeply transactions can be nested. Within nested transactions, rollbacks can occur to any previously named save point, or the entire transaction can be rolled back.

Transactions cannot be committed in stages. The `COMMIT WORK` command will always commit all commands in the transaction.

### 3.11.2 Automatic Transactions

It is possible to have Empress treat each Query Language command as a transaction, committing it only when the next command is successfully entered. With this feature, called automatic transactions, your last command may be canceled by issuing a `ROLLBACK WORK` command before you issue another command. Invalid commands or any of the executive commands `CHANGE`, `EDIT`, `HELP`, `HOLD`, `RECALL`, `SET`, `STORE`, or `UNSET` do not commit the previous command or start a new transaction. A `RUN` command may or may not commit the transaction, depending on what the previous command was. If it was a command from the above list, the transaction is not committed. If it was anything else, the transaction is committed and a new transaction started.

To enable automatic per-command transactions, set the Empress system variable `MSQLTRANSACTION`:

       SET MSQLTRANSACTION TO X;

To disable automatic transactions, unset `MSQLTRANSACTION` and then issue a `COMMIT WORK` or `ROLLBACK WORK` command to commit or cancel your last command:

       UNSET MSQLTRANSACTION;
       COMMIT WORK;

or the following:

       UNSET MSQLTRANSACTION;
       ROLLBACK WORK;

### 3.11.3 Locking on Transactions

It is advisable to have locking set at the table level (see the `LOCK LEVEL` command in the Empress: SQL Reference) during a transaction. If the Empress system variable `MSTRANSTABLELOCK` is set, and some level of locking has been set on the target table with the `LOCK LEVEL` command, then locking is automatically set to table level for any tables involved in a transaction for the duration of the transaction. This ensures that successive reads of a given table will show the same records in the same order.

This variable can be set by:

       SET MSTRANSTABLELOCK TO X;

and unset by:

       UNSET MSTRANSTABLELOCK;

Table locking for the duration of a transaction is effective during automatic transactions as well. This means that a table is locked from the time you type in a command to the time you type in the next command.

The command `LOCK TABLE` is also available for locking tables explicitly when in a transaction. Again, some level of locking has to be defined for the table for the `LOCK TABLE` command to have effect.

### 3.11.4 Placing Comments to the Transaction Log

The Empress system variable `MSTRANSCOMMENT` can be used to place a comment on transactions. The value of `MSTRANSCOMMENT` will be displayed as the comment on the transaction by the `DISPLAY WORK` command.

Comments are useful when several people are running transactions on a database. If comments are used to identify the person running a transaction, This information will be available to the `DISPLAY WORK` command.

### 3.11.5 Displaying Transaction Status

The Query Language `DISPLAY WORK` command displays the status of transactions. This section discusses the general use of the command. The full syntax for the command is presented in the Empress SQL: Reference.

There are three forms of the `DISPLAY WORK` command:

1. `DISPLAY WORK;`

   This command displays status of current transaction. It can only be used while in a transaction.

2. `DISPLAY WORK ON database [ALL];`

   This command is used to identify transactions in progress and determine their status in the database. (Summary information is listed without the `ALL` option, full information is listed with the `ALL` option.) Typed after a system failure, it will list the identification code of all unresolved transactions affecting the database.

3. `DISPLAY WORK id;`

   This command prints full status information on the transaction with the identification code `id`. The identification code of the transaction, optionally preceded by `database:`. If no `database:` prefix is specified, the current default database is assumed. In any case the command is a query on the status of the transaction on the database. (If the transaction does not affect the database, the transaction with the transaction code `id` will not be recognized, even if the identification code is valid.)

   This form of the `DISPLAY WORK` command can be used to list full details of the transaction status, including status information on the coordinator and each participant, as well as the overall status of the transaction.

### 3.11.6 Two-Phase Commit

A transaction can affect tables in several databases. Although a transaction affecting several databases can normally be treated conceptually and operationally as a single transaction, Empress internally manages and coordinates a separate transaction on each database involved. To secure data integrity in this situation, transaction processing is coordinated using a two-phase commit protocol.

Empress recognizes transactions that span multiple databases and enforces the two-phase commit protocol to ensure consistent recovery over all the affected databases. The transaction you run is controlled as a unit. The `DISPLAY WORK` and warm restart facilities treat the transaction over all databases as a unit so that data integrity is preserved. Under normal circumstances you need not be aware of or concerned with the individual databases affected in a transaction.

### 3.11.7 Transactions Over Several Databases

If a transaction spans several databases, the first database accessed is identified as the coordinator for that transaction. The coordinator's significance is that it is the final arbiter as to whether the transaction can be committed or rolled back. The coordinator maintains status information on the progress of the transaction including any actions taken on the transaction after the system failure both for itself and for the other databases involved. The other databases involved in the transaction are known as participants.

The coordinator is fully informed of the status of the transaction at all times. If a warm restart is necessary, the coordinator can always determine whether the transaction is to be committed or rolled back. Most of the time a participant also has information on the status of the transaction, and can determine independently of the coordinator whether the transaction is to be committed or rolled back. However, there are moments in a transaction at which a participant will require access to the coordinator to determine the correct action.

When the warm restart utility is run on a database it may encounter several transactions involving the database that were in progress at the time of the system failure. For each transaction it encounters it must determine the correct course of action to be taken to recover the transaction on the database. For those transactions for which the database is the coordinator, the status of the transaction will be determined and any appropriate action taken. For those transactions for which the database is a participant, an attempt will be made to determine the status of the transaction but no action will be taken.

### 3.11.8 Warm Restart

Warm restart facilities are provided to guarantee the security of your data if a transaction is interrupted due to any kind of system failure. In the event of failures such as a failure of the executing task, a failure of the operating system, or a disk failure, the warm restart facilities allow you to recover your databases to a consistent state, provided that database operations were carried out within transactions at the time of failure.

To be able to recover databases, the warm restart facilities require that no disk corruption has occurred. If physical data has been lost from a disk, use the `emprecov` utility to recover from an archived database.

When you run a transaction in Empress, you retain the option of ultimately committing all the operations in the transaction permanently to the database(s), or rolling them back. Should you be interrupted in the midst of a transaction, the transaction is unresolved and the database(s) may contain inconsistent or corrupted data. The warm restart facilities can be used to restart the affected databases and resolve the unresolved transactions. When a database is restarted, an interrupted transaction involving that database can either be completed or rolled back.

The automatic warm restart utility `empwarm` is described in the Empress: Database Administrator's Guide.

### 3.11.9 Manual Warm Restart

Manual overrides are provided for use in either of two circumstances:

1. You do not want to lose an entire transaction by allowing the automatic warm restart utility to roll it back.

   If a transaction is valuable to you, because of time invested or effort expended, for example, you may wish to set save points in the transaction, so that in the event the transaction is interrupted you will have the alternative of recovering the transaction to a save point. In this case you can examine the status of the transaction, and if it would be rolled back by a warm restart, you can recover the transaction to a save point instead. See Recovering to a Save Point below.

2. You need to resolve the transaction on a participant immediately, and communication cannot be established with the coordinator.

   If you need to resolve a transaction on a participant and communication cannot be established with the coordinator, you can force a resolution on the participant. In most cases the `DISPLAY WORK` command at the participant will tell you the correct resolution (commit or rollback). However, it can happen that a transaction spanning several databases is interrupted leaving a participant unable to determine whether to commit or roll the transaction back. This will be the case if the participant cannot independently determine the appropriate course of action, and if it also cannot access the coordinator to learn the appropriate course of action (recall that the coordinator can always determine whether the transaction should be committed or rolled back). In this case, you should either wait until communication is re-established or contact someone at the coordinator's site and have them use the `DISPLAY WORK` command to determine the correct course of action.

   When you have determined the correct action to take to resolve the transaction, use either the `COMMIT WORK` (forced) or `ROLLBACK WORK` (forced) command described below to commit or rollback the transaction at the participant.

   If you force a transaction to be committed or rolled back at a participant the status information for the participant is removed from the participant database. Normally the participant will inform the coordinator of the forced action. However, if the coordinator is not accessible it is not possible to inform it of the final status of the participant. Moreover, the coordinator will not be able to obtain it when communication is re-established. The coordinator will assess this situation as one in which disk corruption may have occurred, and the automatic warm restart utility will not take any action. It will therefore be necessary for you to use manual overrides to force the resolution of the transaction at the coordinator and all other participants. It will also be your responsibility to ensure that manual overrides are used in a consistent manner on all databases involved.

Manual overrides are effected using Query Language commands. These options should be used with caution and only in concert with all sites involved in the transaction. Careless or irresponsible use of manual overrides can result in inconsistent or incorrect data residing in your databases, since a forced commit or rollback at a participant can conflict with recovery procedures at the coordinator or other participants.

The following commands are used to identify transactions, display their status, and recover or resolve them. They can only be used when not currently in a transaction.

Table 3-2: Transaction Recovery Commands

Command | Description
`DISPLAY WORK ON ...` | Displays all transactions in database; progress (that is, not completed) that involves the database.
`DISPLAY WORK id;` | Displays status of the transaction.
`START WORK id FROM ...` | Recovers the transaction by rolling savepoint; back to the save point.
`ROLLBACK WORK id;` | Force a rollback of the transaction.
`COMMIT WORK id;` | Force a commit of the transaction.

### 3.11.10 Recovering to a Save Point

If an unprotected transaction is interrupted before a commit command is issued and logged, the automatic warm restart utility will roll the transaction back. To prevent the loss of entire transactions, a means of recovering partially completed transactions using save points within the transaction is available. By setting save points in the transaction, you can recover the transaction to any save point after an interrupt. Recovering to a save point resolves the transaction; when a transaction has been recovered to a save point it will not be affected by subsequent execution of the warm restart utility.

If a transaction is unprotected, the recovery to a save point must occur before any execution of the warm restart utility. If a transaction is protected, the warm restart utility will ignore the transaction if it can be recovered to a save point (and the `-p` option to override protection is not used). The recovery to a save point can therefore occur after the warm restart utility is run.

A save point is set in a transaction with the command:

       SAVEPOINT savepoint;

where `savepoint` is the name of a save point. It must be a string starting with a letter. Save points are also used to start a nested transaction within the current transaction.

A transaction is recovered to a save point with the command:

       START WORK [database:]id [FROM savepoint];

where `database` is the name of a database. If no database is specified the current default database is assumed. Each transaction has an ID that can be determined using the `DISPLAY WORK` command described below. If no savepoint is specified, the transaction will be recovered to the last defined save point. A transaction with no save points cannot be recovered with this command.

You can use this command when you are not in a transaction. It restarts the transaction at the appropriate save point, and you can then continue the transaction.

### 3.11.11 Transaction Protection

Transactions can be protected from the automatic warm restart utility. The system variable `MSTRANSWARMPROTECT` is a flag that controls whether transactions will be acted on (resolved) by the automatic warm restart utility `empwarm`. If this variable is set to any value when a transaction is started, the transaction is protected from the warm restart utility, that is, the utility empwarm will ignore the transaction if it can be recovered to a save point. This feature is useful if the automatic warm restart is included as part of the system re-boot process. Since an unprotected transaction will certainly be rolled back unless a commit command has been issued, unprotected long transactions that are interrupted will tend to be rolled back. Protecting a transaction allows you to recover a transaction to a save point with the `START WORK FROM savepoint` command after the warm restart utility has been run.

The variable is not set by default, so the warm restart utility will resolve all interrupted transactions by default. To set the variable, do:

       SET MSTRANSWARMPROTECT TO TRUE;

## 3.12 Locks and Table Access

Locking prevents access conflicts and preserves data integrity by controlling concurrent accesses to a table. Every time Empress receives a command involving a table it attempts to determine the lock status of the table, which is stored either in live shared memory or in a file on disk. No other Empress process may access this lock manager while the lock level is being checked.

If an update is in progress, the lock manager will let no other operation proceed. If a read is in progress, another read can proceed. If Empress can proceed with the command, it marks the file with the kind of operation it is, and continues.

If Empress attempts to access a table and fails because the table is already locked, it will try again.

The number of attempts made and the interval between attempts, are controlled by the Empress system variables `MSEXCLRETRY`, `MSIAEXCLSLEEP`, `MSLOCKRETRY`, `MSLOCKSLEEP`, `MSIAEXCLRETRY`, `MSIAEXCLSLEEP`, `MSIALOCKRETRY` and `MSIALOCKSLEEP`. These variables provide sufficient control to avoid instances of a deadlock "hung'' system due to repeated attempts to access an inaccessible table.

`MSEXCLRETRY` determines the number of attempts Empress makes to read the lock. The default is 30 attempts. `MSEXCLSLEEP` determines the number of seconds between each attempt, and its default is one second. If you enter the Query Language command:

       SET MSEXCLRETRY TO 5;
       SET MSEXCLSLEEP TO 2;

Then try to access a table whose lock is in use, Empress will make five attempts to read the lock at two-second intervals, for a total of eight seconds, before giving up.

`MSLOCKRETRY` determines the number of attempts made to write the lock once access to the lock has been achieved. For example, the table may be locked in update mode, which prevents other commands from writing the lock. The default is 30 retries.

`MSLOCKSLEEP` sets the number of seconds between attempts to write the lock, and its default is one second. If you enter the command:

       SET MSLOCKRETRY TO 5;
       SET MSLOCKSLEEP TO 2;

Then try to write the lock for a table, Empress will make five attempts to do so at two-second intervals, for a total of eight seconds, before giving up.

`MSIAEXCLRETRY` determines the number of attempts Empress makes to read the lock when using the Interactive Interface. It will override the setting of `MSIAEXCLRETRY`. The default is two attempts. `MSIAEXCLSLEEP` determines the number of seconds between each attempt, and its default is one second. If you enter the Query Language command:

       SET MSIAEXCLRETRY TO 3;
       SET MSIAEXCLSLEEP TO 3;

Then try to access a table using the Interactive Interface, Empress will make three attempts to read the lock at three-second intervals, for a total of six seconds, before giving up.

`MSIALOCKRETRY` determines the number of attempts made to write a lock for a table when using the Interactive Interface. It will override the setting of `MSLOCKRETRY`. The default is two attempts, since response should be fast in the Interactive Interface.

`MSIALOCKSLEEP` sets the number of seconds between attempts to write a lock in the Interactive Interface, and its default is one second. If you enter the command:

       SET MSIALOCKRETRY TO 3;
       SET MSIALOCKSLEEP TO 3;

Then try to write a lock for a table, Empress will make three attempts to do so at three-second intervals, for a total of six seconds, before requesting further instructions.

During an interactive `UPDATE`, if the table cannot be accessed or Empress cannot start to find the necessary records, you may either retry or quit. Once the update has started, if a given record is inaccessible you may either retry, skip the record, or quit. During an interactive `INSERT`, you may either retry or quit.