Option 1: Create New Table Definitions

SQLM menu option 1 enables you to create a new table definition for a Reality file, with associated column and index definitions derived from the data and index definition items in the specified file. For guidance on designing table/column definitions, see also Table/Column Definition Design.

Where a file has no data definitions, SQLM allows you to create any number of new column definitions based on a default column definition.

Note: Privileges for a new table are initially available only to the table’s owner, i.e. the user who created it. Owner privileges provide full access to the table. To grant another user privileges, use SQLM option 4, sub-option 1.

This procedure supports a number of methods for creating table definitions, including:

Automatic conversion of all or selected data/ index definitions to columns/index definitions.

Using this method, column and index definitions are generated automatically for all, or selected, data and index definitions in the specified Reality file. Automatic conversion of selected data/index definitions is enabled by first providing a select list of data/index definition items using a SELECT or GET-LIST command, before executing SQLM. This method is selected by entering Y at the Do you want to run the automatic conversion, (Y/N) ? prompt.
Manual selection of data/index definitions on which to base column/index definitions.

This method allows you to manually select data and index definition items to be converted to column/index definitions from lists of data definition items displayed by SQLM for each attribute and indexes associated with the specified Reality file. This method is selected by entering N at the Do you want to run the automatic conversion, (Y/N) ? prompt.
Creation of column definitions without using existing data definition items.

This method is initiated when the selected Reality file has no existing data definitions. Each column definition is created based on a default definition item.

Initial Procedure

Note: If you want to carry out the automatic conversion of selected data and index definitions, you must first generate a select list of the data and index definition items using the SELECT or GET-LIST command.

To create a new table definition, proceed as follows:

Select option 1 from the main menu. You will be prompted:
```
Define Filename to be Processed...

Enter Filename :
```
Enter the name of the Reality file for which you wish to create an SQL table (see Using the SQLM Menu Options).
Notes:
- You can enter ? to list the file D-pointers (but not Q-pointers) available on your current account.
- If you have entered more than one file name on the command line, the procedure is completed for each file, before going on to create the table definition for the next one.
Each file can be specified as the name of a D- or Q-pointer, optionally followed by a comma and a non-default data section name, or as a ‘dynamic Q-pointer’ (see Conventions). Refer to Danger of Using Q-pointers.

For example, to create a table for the GUESTS file:
```
Define Filename to be Processed...

Enter Filename :GUESTS
```
You are then prompted:
```
Enter name of Table to be Created

Enter Tablename :
```
Enter the name you wish to give to the table, or press RETURN to accept the default (see Using the SQLM Menu Options).

Note: For rules for creating table names, see Naming of Tables, Columns and Indexes.
The procedure following will depend on whether the Reality file contains data definitions. Continue with Step a or b, as appropriate.
1. If the specified file contains data definition items, you are prompted to select the method for converting them to column definitions, with:
```
Do you wish to generate the table(s) AUTOMATICALLY (Y/N/EXit):
```
  This prompt gives you the choice of enabling SQLM to automatically create column and index definitions for all, or selected, data and index definition items in the file dictionary, or of forcing it to prompt you to select data and index definitions for conversion.
  
  Note: When using the automatic option, if you supply a select list of data/index definition items, only the selected data/index definitions will be converted to column/index definitions.
  - To automatically convert all data/index definitions, or a select list of data/index definitions to column/index definitions, enter Y. Continue with the procedure Automatic Conversion of Data/Index Definitions.
  - To select data/index definitions manually for conversion to column/index definitions, enter N. Continue with the procedure Manual Selection of Data/Index Definitions .
2. If the specified file does not contain any data definition items, the following warning prompt is displayed:
```
Processing Selected Files....

File GUESTS > GUESTS
No attribute definition items
Enter number of columns to define (or <CR> to skip file) 
```
  In this case, continue with the procedure Creating a Table for a File with no Data Definition Items.

Automatic Conversion of Data/Index Definitions

Caution

This automatic process uses a “best fit” technique to generate SQL column and index definitions from existing Reality data and index definitions. It is reasonably accurate in simple cases, but cannot be guaranteed to generate correct results in all circumstances. It is therefore important that column and index definitions generated by this process are checked for accuracy (use the SQLM View Table Definitions option). In particular, check the following:

Data type, precision, and scale for each column definition.
Decisions about exploding multivalues and subvalues.
Column(s) chosen as the table’s Primary Key.

If there are a large number of data/index definitions, it is recommended that you use the procedure Manual Selection of Data/Index Definitions to avoid a large number of unnecessary definitions being created.

Entering Y at the prompt

Do you wish to generate the table(s) AUTOMATICALLY (Y/N/EXit):

initiates an automatic conversion process similar to the following:

Processing Selected Files....

File GUESTS > GUESTS
Converting Dictionary item 1               to Column item X1
Converting Dictionary item 2               to Column item X2
Converting Dictionary item 3               to Column item X3
Converting Dictionary item ADDRESS         to Column item ADDRESS
Converting Dictionary item ROOM-CODE       to Column item ROOM_CODE
Converting Dictionary item ROOM-TYPE       to Column item ROOM_TYPE
Converting Dictionary item STATE           to Column item STATE
Converting Dictionary item STAY            to Column item STAY
etc.
 
Converting Index item GSTSURNAME to SQL Index GSTSURNAME
Converting Index item LAST-GSTNAME to SQL Index LAST_GSTNAME

Processing Completed. Enter <CR> :

Press RETURN to return to the SQLM main menu.

Column/index definitions now exist for all, or selected, data/index definitions for the Reality file. Now check that they are accurate and update, if necessary. See the Caution at beginning of this section. Refer to SQL Column and Index Definitions for a description of column and index definition fields.

Composite Primary Keys

In Reality, a unique item-id is sometimes constructed by combining a number of different data elements, none of which is itself unique. The individual data elements are delimited by a separator character and are extracted by using an English group extraction conversion code. In SQL, a similar result is obtained by defining a composite primary key that is made up of the contents of a number of different table columns.

If you are using SQLM to convert a Reality file that uses this kind of item-id, you can set up data definition items to extract the individual data elements (you may already have suitable data definition items). SQLM then automatically creates columns corresponding to these data items and a composite primary key made up of these columns.

Note: The chosen composite primary key is validated against the first ten items in the file. The key is rejected if any item contains fewer data elements than the primary key.

For example, if you have a document database on Reality, where each document has a unique identifier, but can exist in several different versions, you might construct a unique item-id by combining the document identifier with the version number, using a colon as a separator, as follows: documentId:versionNumber (for example, version 2 of document “doc57” would have an item-id “doc57:2”). The document identifier and version number could then be extracted with the group extraction codes G0:1 (to extract the document identifier) and G1:1 (to extract the version number).

When these data definitions are converted using SQLM, separate columns will be created for the document identifier and version number, and the primary key will be set to the combination of these two columns.

When creating data definition items for this purpose, you must follow some simple rules:

The A/AMC attribute (attribute 2) must be 0 (specifying the item-id).
A group extraction code (G) must be used to extract the required data element.
The number of separation characters must be 0 or positive (m >= 0).
The code must extract only one data segment (n = 1).
The code must be placed in attribute 8 of the data definition item.

Note: If the same G code occurs in more than one data definition item that references attribute zero, SQLM will use only one of these.

Examples:

Attribute	Definition 1	Definition 2	Definition 3
1	0	0	0
2	DocNo	DocVersion	Example3
3
4
5
6
7
8	G0:1	G1:1	G2*1
9	L	L	L
10	10	10	10
	Uses a colon (:) as a separator and extracts the first segment.	Uses a colon (:) as a separator and extracts the second segment.	Uses an asterisk (*) as a separator and extracts the third segment.

Refer to the English Reference Manual for details of the group extraction code.

When SQLM creates a composite primary key, a log is generated so that the user can review the processing that was performed. The log file is named SQLM-CONV.LOG, with the data stored in an item called LOG. A new LOG item is created for each automatic conversion.

Manual Selection of Data/Index Definitions

Entering N at the prompt:

Do you wish to generate the table(s) AUTOMATICALLY (Y/N/EXit):

allows you to manually define the column definitions for your new table.

SQLM first displays a list of Reality data definitions for attribute 0 (the item-id).

Note: A column definition for attribute 0 (item-id) must always be defined, regardless of whether a data definition item for it exists, since the item-id is normally used as the primary key. If there are no data definition items for attribute 0, you will first be prompted for a name for the column.
```
                   List of Dictionary items for Attribute 0
                   ========================================

   Id        Heading

1. LEAVE-DAT Leave Date
2. MANAGER   Room]Code
3. ROOM      Room
4. ROOM-CODE Room Code
5. ROOM-TYPE Room Type


Enter # to select (*)=all, (FI)le or (EX)it or (Q)uit :
```
Select the data definitions that you want to use as column definitions in the new table, as described in Selecting from a List.

When you enter FI, A 'proposed' column definition (similar to that shown below) is displayed for the first of your selected data definitions.

      SQL COLUMN DEFINITION SCREEN FOR Column 'ROOM', Table 'GUESTS'
      ==============================================================
 1. Column Name                         : ROOM
 2. Column Type                         : 12          VARCHAR
 3. Precision                           : 20
 4. Scale                               :
 5. Attribute Number                    : 0
 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) :

Check the column definition and update it, if necessary, to ensure that it accurately defines the data for that column. In particular, check the following:

Data type, precision, and scale for each column definition.
Decisions about exploding multivalues and subvalues.

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.

Note: Entering ‘?’ in the Column Type field displays the names of all valid types.

To save your changes, enter FI, or to exit without saving your changes, enter EX or Q. The definition screen for the next of your selected data definition items will then be displayed. Repeat step 2 for each data definition item.
When you have defined all the columns you require for the current attribute, the data definitions for the next attribute are listed. Repeat steps 1 to 3 for each attribute and their data definitions.
Any indexes available in the source table are then displayed, one by one. For example:
```
           SQL INDEX DEFINITION SCREEN FOR Index 'MAIN', Table 'GUESTS'
           ============================================================
 1. Index Name                          : MAIN
 2. Created by SQL (Y/N)                : N
 3. Index Type                          : 3          SORTED INDEX
 4. Allow Duplicates (Y/N)              : N
 5. Column Name(s)                      :
 6. Collation(s)                        :
 7. Reality Index Name                  : MAIN


Enter line number to change(\ to null it), FI, EX or Q(uit) :
```
Check each index definition and update it, if necessary, to ensure that it accurately defines the required SQL index.
- 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.

You must now define a primary key for your new table by using SQLM option 4, sub-option 2.

Creating a Table for a File with no Data Definition Items

If there are no data definition items in the file for which you want to create the table definition, the following message prompt is displayed:

Processing Selected Files....
File GUESTS > GUESTS
No attribute definition items
Enter number of columns to define (or <CR> to skip file) :

Continue as follows:

Enter the number of columns required.
You are then prompted to specify a column name for the first attribute.
```
Enter column name for attribute '0' for file 'GUESTS' :
```
Enter a name for this column.
A column definition is then displayed with default values. See Manual Selection of Data/Index Definitions for an example.

Check the column definition and update it, if necessary, to ensure that it accurately defines the data for that column. In particular, check the following:
- Data type, precision, and scale for each column definition.
- Decisions about exploding multivalues and subvalues.
- Column(s) chosen as the table’s primary key.
Note: Refer to SQL Column and Index Definitions for a description of column definition fields.
Save the new column definition and repeat the process for each of each of the remaining columns.

You must now define a primary key for your new table by using SQLM option 4, sub-option 2.