Option 4: Amend Table Definitions

SQLM menu option 4 allows you to amend privilege grants, primary keys, foreign keys, column and index definitions for a selected table, and to toggle update permissions. The amend procedures are described below.

1. Select option 4 from the SQLM main menu. You will be prompted:

   Define Tablename to be Processed....
   Enter Tablename :

   Enter the name of the SQL table for which you wish to create an alternative view. If you enter ?, a list of tables is displayed, from which you can select. See Using the SQLM Menu Options.

2. An SQL Table Maintenance Menu is displayed, similar to the following:

                 SQL TABLE MAINTENANCE MENU FOR TABLE 'GUESTS'
                 =============================================
   1. Update privilege grants
   2. Update primary keys
   3. Update foreign keys
   4. Update column definitions
   5. Update index definitions
   6. Toggle Updates permission
   
   Enter Selection (1 - 6 / <CR>) :
   
   

   Enter the number of the option you require or press RETURN to exit and return to the main menu.

Updating Privilege Grants

Selecting menu option 1 on the SQL Table Maintenance Menu allows you to change the access privilege granted to a specified user or security profile with respect to the selected table.

The access privilege granted to a user-id determines which operations the user is allowed to carry out on the data in a table. Granting access to a security profile affects all users with that security profile.

See the table below for a list of Privilege Grants. The table owner has full privileges (except where overridden by the ?update permission? field).

Privilege Grant	Permitted Operations	Explanation
0	No privileges	Prevents the user accessing the data.
1	Delete records	Allows the user to delete records (rows) from the table.
2	Insert records	Allows the user to insert records (rows) into the table.
3	Select records	Allows the user to select (report or list) records this table.
4	Update records	Allows the user to update records in the table.
5	Reference records	Allows the user to 'reference' records in the table: required when this table is referenced via a 'foreign key' in another table.
6	All privileges	Allows this user all privileges shown above.

The privileges granted to a particular user comprise the combined privileges assigned to the user?s user-id, the PUBLIC id and any security profiles defined for that user.

Selecting menu option 1 on the SQL Table Maintenance Menu displays the Current Grants assigned to that table, as shown below:

                    Current Grants for Table 'GUESTS'
                    =================================
User_ID / ~Security_Profile ......      Grants ...............................
1. DAVEH                                Owner
2. PUBLIC                               None

Enter Ins,Del,Upd,Copy or <CR> (I/Dn/Un/Cn) :

This screen allows you to insert, delete, update and copy user-ids and security profiles as described in Amendments Screens.

If you insert a new entry or update an existing one, a screen similar to the following will be displayed:

                  Select the privileges to grant to 'JSMITH'
                  ==========================================
Select required privileges
1.  Delete records
2.  Insert records
3.  Select records
4.  Update records
5.  Reference records

Enter # to select (*)=all, (FI)le or (EX)it :

Select the required privileges, as described in Selecting from a List. See the table above for descriptions of the different privileges. When you enter FI  or EX, you will be returned to the Current Grants screen.

Changing the Owner of a Table

You cannot change the privileges for the owner of a table. If you attempt to do this, you will see the following prompt:

WARNING: You cannot change the privileges of the table owner.
Do you want to specify a new table owner (Y/N) :

• To change the owner of the table, enter Y. Then enter the user-id of the new owner.
• To leave the owner of the table unchanged, enter N.

Updating Primary Keys

Selecting option 2 on the SQL Table Maintenance Menu allows you to update the columns defined as the primary key for the selected table.

A screen showing the primary key for the selected table is displayed, as follows:

            Primary Keys Currently Defined for Table 'GUEST_LIST'
            =====================================================

1. ROOM

Enter Ins,Del or <CR> (I/Dn) :

This screen allows you to insert and delete columns as described in Amendments Screens. The primary key is the combination of the columns specified.

• Enter I  to insert a column - you will be prompted to enter the column name.

  Enter column name or <CR>

  If you enter ?, a list of columns is displayed, from which you can select. See Using the SQLM Menu Options.

• Enter D  followed by a line number to delete a column.

Updating Foreign Keys

Selecting menu option 3 on the SQL Table Maintenance Menu displays the message:

Not yet supported. Enter <CR>:

Foreign keys are not currently supported by SQL for Reality.

Updating Column Definitions

Selecting menu option 4 on the SQL Table Maintenance Menu displays a list of columns, defined for the selected table. For example:

               Columns Currently Defined for Table 'GUESTS'
               ============================================

1. ADDRESS
2. CITY
3. FIRST-NAME
4. LAST-NAME
5. ROOM

Enter Ins,Del,Upd,Copy or <CR> (In/Dn/Un/Cn) :

This screen allows you to insert, delete, update and copy columns as described in Amendments Screens.

Inserting a New Column Definition

If you insert a new entry, you will be prompted:

Enter New name :

Enter the name of the new column. You will then be prompted:

Do you wish to use an existing Dictionary entry (Y/N) :

If you enter Y, you will then be prompted for the name of the entry to use:

Enter Dictionary name or <CR> :

Enter the name of the Reality data definition item you wish to use. If you enter ?, a list is displayed, from which you can select. See Using the SQLM Menu Options.

A screen similar to the following is then displayed:

      SQL COLUMN DEFINITION SCREEN FOR Column 'ADDRESS', Table 'GUESTS'
      =================================================================
 1. Column Name                         : ADDRESS
 2. Column Type                         : 12          VARCHAR
 3. Precision                           : 20
 4. Scale                               :
 5. Attribute Number                    : 4
 6. Multivalue                          : 1
 7. Subvalue                            : 1
 8. Data Generation                     :
 9. Output Format                       :
10. Input Processing                    : =
11. Update Processing                   : =
12. Maximum Header Length               : 20
13. Nullable     (Y/N)                  : N
14. Primary Key  (Y/N)                  : N
15. Unique                              : N
16. Special NULL Handling               : N
17. Remarks                             :

Enter line number to change(\ to null it), FI, EX or Q(uit) :

• To change a column attribute, enter the corresponding line number. Then enter the new value for the attribute. You can clear the attribute by entering a backslash (\).

  Refer to SQL Column and Index Definitions for a description of column definition fields.

  Notes:

  • Entering "?" in the Column Type field displays the names of all valid types.
  • You cannot change the value of the Unique field.
• To save your changes, enter FI.
• To exit without saving your changes, enter EX  or Q.

Updating a Column Definition

If you update an existing entry, a column definition screen similar to that shown above will be displayed.

Copying a Column Definition

If you copy an existing entry, you will be prompted to enter the name and position for the new entry. A column definition screen similar to that shown above will then be displayed.

Updating Index Definitions

Selecting menu option 5 on the SQL Table Maintenance Menu displays a list of indexes currently defined for the selected table, as follows:

               Indexes Currently Defined for Table 'GUESTS'
               ============================================

1. GUEST_DATE
2. GUEST_NAME

Enter Ins,Del,Upd,Copy or <CR> (I/Dn/Un/Cn) :

This screen allows you to insert, delete, update and copy indexes as described in Amendments Screens.

If you insert, copy or update an index, the following screen will be displayed to allow you to set up or modify the index.

    SQL INDEX DEFINITION SCREEN FOR Index 'GUEST_NAME', Table 'GUESTS'
    ==================================================================
 1. Index Name                          : GUEST_NAME
 2. Created by SQL (Y/N)                : N
 3. Index Type                          : 3          SORTED INDEX
 4. Allow Duplicates (Y/N)              : N
 5. Column Name(s)                      : NAME
 6. Collation(s)                        : A
 7. Reality Index Name                  : GUEST.NAME

Enter line number to change(\ to null it), FI, EX or Q(uit) :

• To change the value of a field, enter the corresponding line number. Then enter the new value for the field. You can clear the field by entering a backslash (\).

  Refer to SQL Column and Index Definitions for a description of column definition fields.

  Notes:

  • Multiple entries in fields 5 and 6 must be comma separated.
  • In field 6, valid entries are A (ascending) and D (descending).
  • There are certain restrictions on which SQL columns can be indexed. Refer to Application Index Design for details.
• To save your changes, enter FI.
• To exit without saving your changes, enter EX  or Q.

Toggling Update Permission

Selecting option 6 on the SQL Table Maintenance Menu allows you to toggle the update permission status of the table. When this is set to N, no user can update the table, regardless of privileges. When set to Y, updating is controlled by privileges (see Updating Privilege Grants). The default for a newly created table is N.

When you select option 6 from the SQL Table Maintenance Menu, the current update permission setting is displayed. For example:

            Update Permission Currently Defined for Table 'GUESTS'
            ======================================================

Update Permission currently set to NO

Do you wish to toggle this setting (Y/N) :

• If you enter Y, the change is confirmed. For example:

Update Permission changed from NO to YES. Enter <CR> :

• If you enter N, you are prompted:

Update Permission NOT changed. Enter <CR> :

Press RETURN to return to the SQL Table Maintenance Menu.