CREATE
Contents
Purpose
Define the structure of a new database table through a form
Syntax
CREATE <.dbf file> | (<expC>)
See Also
@...GET, ADATABASES(), ALTER INDEX, ALTER TABLE, APPEND, APPEND FROM, BACKUP DATABASE, CHANGE, CLOSE DATABASES, CLOSE TABLES, COMPILE DATABASE, COPY TO, COPY STRUCTURE EXTENDED, CREATE DATABASE, CREATE FROM, CREATE TABLE, CREATE INDEX, CREATE VIEW, DB_MAXWKA, DB_DATADIR, DISPLAY DATABASE, DISPLAY INDEXES, DISPLAY TABLES, DBUSED(), DROP DATABASE, DROP INDEX, DROP TABLE, EDIT, Function Keys, GETENV(), INDEX, INSERT, LIST DATABASE, LIST INDEXES, LIST TABLES, MENU FIELDS, MENU QUERY, MODIFY STRUCTURE, OPEN DATABASE, PACK, QUERY, REBUILD DATABASE, RESTORE DATABASE, SET AUTOCATALOG, SET EXCLUSIVE, SET HIDDENFIELD, SET PROCEDURE, SET RELATION, USE
Description
The CREATE command provides a full-screen forms-based facility used to define a new database table structure. The filename can be substituted with an <expC> enclosed in round brackets that returns a valid filename. If no file extension is specified, '.dbf' will be used. Database table filenames should not begin with numeric characters.
The cursor keys can be used to move around the form in order to fill in the fields. Each row of the form corresponds to a field in the table structure being created. A maximum of 256 fields may be created.
Each column in the CREATE form represents information needed to define a field. Required information includes the field name, the field data type and the field width. Field descriptions are optional, but recommended.
The field name can be a maximum of 32 characters long, beginning with a letter or underscore, followed by any combination of letters (A-Z, case insensitive), digits (0-9) and underscores (_).
As the cursor moves on to the data type column, the value "Character" is filled in automatically. Typing the first letter of any of the data types listed below will complete the full value. Pressing the [HELP] key displays a pop-up choice list of available data types. The spacebar can also be used to cycle through the supported data types.
The field data type can be any one of the following:
| Initial | Type | Description |
|---|---|---|
| B | Byte | 1 byte integer |
| C | Character | Character |
| D | Date | Date |
| F | Float | 8 byte floating point |
| I | Integer | 4 byte integer |
| L | Logical | Logical |
| M | Memo | Memo |
| N | Numeric | Numeric |
| O | Object | Binary large object |
| P | Packed | Packed Decimal (OpenVMS only) |
| Q | Quad | Quadword (OpenVMS only) |
| R | Real | 4 byte floating point |
| S | Short | 2 byte integer |
| T | DateTime | Date and Time |
| V | VAXdate | VMS date (OpenVMS only) |
| Y | Currency | Currency |
| Z | Zoned | Dibol zoned numeric |
The maximum field width for each data type is:
| Initial | Display Width | Physical Storage |
|---|---|---|
| B | 3 (fixed, cannot be input) | 1 byte |
| C | 255 | 255 bytes |
| D | 8 (fixed, cannot be input) | 4 bytes |
| F | 25* | 8 bytes |
| I | 25* | 4 bytes |
| L | 1 (fixed, cannot be input) | 1 byte |
| M | Unlimited (disk & OS permitting) | Variable length |
| N | 25* | 25 bytes |
| O | Unlimited (disk & OS permitting) | Variable length |
| P | 25* | 13 bytes |
| Q | 25* | 8 bytes |
| R | 25* | 4 bytes |
| S | 25* | 2 bytes |
| T | 8 (fixed, cannot be input) | 8 bytes |
| V | 8 (fixed, cannot be input) | 8 bytes |
| Y | 8,4 (fixed, cannot be input) | 8 bytes |
| Z | 20* | 20 bytes |
*Including decimal point and up to nine decimal places.
Field types 'F', 'I', 'P', 'Q', 'R' and 'S' are stored as binary, and the field width specified is the output display width. Fields with fixed length data types have the width filled in automatically. The number of decimal places can only be added when the data type requires it.
The field description can be a maximum of 25 characters long. These field descriptions can be toggled on and off from within the default forms for EDIT, CHANGE, APPEND, QUERY and INSERT. The MENU FIELDS and MENU QUERY commands also display the field descriptions in the FIELDS MENU window. The use of meaningful descriptions is highly recommended.
To create an index tag from a field, place a "Y" in the Index column. Index tags may also be created with the INDEX ON...TAG command. Index tags created in this way form what is called the 'production' index file. The production index file has the same base name as the table and a '.dbx' file extension. The production index is opened automatically whenever the table is opened. No master index tag order is set, but all tags will be updated and can be set to be the master index order. To remove an index tag, replace the "Y" in the Index column with an "N" or use the DELETE TAG command.
It is good practice not to overload one table with redundant fields. The SET RELATION command can be used to join tables together. If the After Image Journal facility is being used, then seven of the maximum number of fields must be left unused for the journal file.
The CREATE work surface provides a menu bar which is activated by pressing the [MENUBAR] key. When the command SET MCONFIRM is OFF, the CREATE menu bar operates as pulldown menus.
The menu options <DICTIONARY>, <TRIGGERS>, <SECURITY> and <PROTECTION> all store their information in the Applications Data Dictionary (.dbd file). The Applications Data Dictionary is called by any commands that need to read, update, view or modify table data. This is very powerful feature allowing the maintenance of referential integrity, help prompts, pop-up choice lists and security in one central repository for each table.
DICTIONARY
You can specify any of the following field 'attributes' by selecting the <DICTIONARY> menu bar item.
| Attribute | Description |
|---|---|
| Picture | An editing picture for data input and output. See @...GET...PICTURE for more information. |
| Validation | A logical expression for field validation. You can perform cross-table lookups with RLOOKUP(). See @ GET...VALID for more information. This can also be used to bypass default field objects, such as the calculator on numeric fields or the calendar on date fields. See @...GET...VALIDATE WITH for more information. |
| Error | An error message to display in an ALERT box if validation fails. See @...GET...ERROR for more information. |
| Choices | A pop-up choice list to be displayed when the [HELP] key is pressed on a character field.
Examples Miss,Mr,Ms,Mrs @suppliers, name+code The Choices attribute also allows you to customize a pop-up choice list by specifying a User Defined Function (UDF). As an alternative to the dynamic @<alias>,<expC> choice list, a UDF could use record selection commands, such as MENU BROWSE, to display the data from records that match particular selection criteria rather than from the entire table. A UDF can also display the choice list anywhere on the screen, whereas the static and dynamic options will always display in the center. UDFs may be entered in the <Choices> attribute by prefixing the UDF name with a question mark. See the FUNCTION command for more information about User Defined Functions. Example ?helpproc("NAME","names",10,10,20,70) |
| Range | A validity range for numerics or dates. The format is minimum, maximum. Specify dates with the {} notation. See @...GET...RANGE for more information
Examples 10,100 (01/01/2000},{01/01/2001} |
| Help | A help message displayed in the message line when the field is selected. See @...GET...HELP for more information. |
| Calculated | The field is calculated by this expression, which can contain references to other tables. See @...GET...CALCULATED BY for more information.
Example ord_value - paid_value |
| Recalculate | A 'trigger', causing all calculated fields to be recalculated and redisplayed when this field is modified in a form. See @...GET...CALCULATE for more information. |
| Must enter | Check that data has been entered in this field when user moves off the field in a form. See @...GET...MUST_ENTER for more information. |
| Default | An expression specifying the 'default' value when APPEND [BLANK] is executed.
Example Date() |
You can move around the database structure in the <DICTIONARY> menu using the [NEXT PAGE] and [PREV PAGE] keys. If you exit the <DICTIONARY> menu with the [EXIT/SAVE] key, changes are saved to the Dictionary (.dbd file), You can override this by exiting the CREATE | MODIFY STRUCTURE work surface with the [ABANDON] key. Pressing the [ABANDON] key in the <DICTIONARY> menu discards any changes made to the dictionary.
TRIGGERS
The <TRIGGERS> menu bar option allows you to specify table level triggers. Triggers are event-driven procedures called before an I/O operation. You may edit a trigger procedure from within the <TRIGGERS> menu by placing the cursor next to the procedure name and pressing the [HELP] key. A text window pops up for editing. If the table triggers are stored in separate <.prg> files, rather than in a procedure library, procedures need not be pre-defined (SET PROCEDURE) before using the table. The following triggers can be selected and associated with a specified procedure name in the <TRIGGERS> menu.
| Trigger | Description |
|---|---|
| UPDATE | The specified procedure is called prior to an update operation on the table. If the procedure returns .F., then the UPDATE is canceled. |
| DELETE | The specified procedure is called prior to a delete operation on the table. If the procedure returns .F., then the DELETE is canceled. |
| APPEND | The specified procedure is called prior to an append operation on the table. If the procedure returns .F., then the APPEND is canceled. |
| OPEN | The specified procedure is called after an open operation on the table. |
| CLOSE | The specified procedure is called prior to a close operation on the table. |
| ROLLBACK | The specified procedure is called when a user presses the [ABANDON] key in a forms based operation. |
SECURITY
The <SECURITY> menu bar option pulls down a menu of table operations for which Access Control Strings can be specified. An Access Control String (ACS) is a range of valid user identification codes, and is used to restrict table operations to certain individuals or groups. Each user on the system is allocated a group number and a user number. The user identification code is the combination of group and user numbers. When constructing an Access Control String of linked user identification codes, wildcard characters may be used.
| Example ACS | Description |
|---|---|
| [1,2] | In group 1, user 2 |
| [100,*] | In group 100, all users |
| [2-7,*] | In groups 2-7, all users |
| [*,100-200] | In all groups, users 100-200 |
| [1,*]&[2-7,1-7] | In group 1, all users, in groups 2-7, users 1-7 |
Please note that the maximum ACS length is 254 characters. OpenVMS group and user numbers are stored and specified in octal. On other Operating Systems, group and user numbers are stored and specified in decimal.
Access Control Strings may be associated with the following operations:
| Operation | Description |
|---|---|
| READONLY | Users specified in the ACS have read-only access to the table.
All other users have update access. |
| UPDATE | Users specified in the ACS have update access to the table.
All other users are restricted to read-only access. |
| APPEND | Users specified in the ACS can append records into the table.
No users can append. |
| DELETE | Users specified in the ACS can delete records from the table.
No users can delete. |
| COPY | Users specified in the ACS can copy records from the table.
No users can copy. |
| ADMIN | Users specified in the ACS can use the following commands:SET DICTIONARY TOMODIFY STRUCTUREPACKZAPREINDEXAll other users cannot, except the creator of the table, who is always granted ADMIN access. |
PROTECTION
Field level security can be defined through the <PROTECTION> menu bar option. The menu will show protection relating to the currently selected field. The [NEXT PAGE] and [PREV PAGE] keys can be used to move between the fields in the table without exiting the menu. The format of the ACS is the same as in <SECURITY> above. The following protection can be defined:
| Operation | Description |
|---|---|
| READONLY | Users specified in the ACS have read-only access to the field. All other users have update access. |
| UPDATE | Users specified in the ACS have update access to the field. All other users are restricted to read-only access. |
| HIDDEN | Users specified in the ACS see the 'hiddenfield' character rather than the data in the field. All other users see the data. |
Hidden fields can be accessed and viewed on a work surface, but the field contains the hiddenfield character, '?'. If the field is referenced in an expression, it will contain the following: blanks for character fields, 'F' for logical fields, 00/00/0000 for date fields and blank for memo fields.
LOAD
The <LOAD> menu bar option displays a popup dialog box prompting for an existing table name. The structure from the existing table can be loaded into the current CREATE worksurface. If fields have already been defined in the worksurface, these will be overwritten when the structure is loaded.
If SET CLIPPER is ON, the CREATE command can be used in programs to create empty structure tables. Structure tables can hold information about the structure of another table and can be used to recreate the table using the CREATE FROM command. The COPY STRUCTURE EXTENDED command can also be used to create a complete structure table including the records that define a table structure.
If a database is open when a new table is saved via the CREATE work surface, a dialog will pop up prompting for a file description. This file description information is stored in the table's record in the database catalog. Databases in Recital are implemented as directories containing files that correspond to the tables and associated files in the database. Operating System file protection can be applied individually to the files for added security. The directories are sub-directories of the Recital data directory. The environment variable / symbol DB_DATADIR points to the current Recital data directory and can be queried using the GETENV() function. Files from other directories can be added to the database using the ADD TABLE command or via the database catalog and SET AUTOCATALOG functionality. Databases are opened using the OPEN DATABASE command.
Example
create patrons
Products
Recital Terminal Developer
