1.5.2 Tables and Fields

A Table is a collection of records, also called rows, that share a common structure. A Field is an item of data in a record. A Field is also called a column. A Database is a collection of Tables. The maximum number of records in a table is 2,147,479,552 (0xFFFF000). For tables without the GROW attribute, the aggregate maximum number of records is also 2,147,479,552 (0xFFFF000). For tables with the GROW attribute, there is no aggregate limit.

1.5.2.1 Table Names and Field Names

Table Names and Field Names must be at least two characters in length; and can be up to 64 characters in length. Table Names and Field Names are constructed from the character set {'A' thru 'Z', '0' thru '9', '_', '#'}. The first character of a Table Name or Field Name cannot be a numeric character or '#'. Table Names and Field Names are case insensitive; so that 'A' thru 'Z' are equivalent respectively to 'a' thru 'z'. Two tables in the same database cannot have the same name. Within a table, two fields cannot be named the same. But two different tables can share common field names. These are called global fields. The natural relationships that exist between tables are determined by global keys. For example, all of the records in the database associated with a particular customer share the same value for CUST#. You expect that exactly one of those records is in the CUSTOMERS table, the rest are the sales records in the SALES table. Common fields supply the linking mechanism between naturally allied data.

1.5.2.2 Data Types

Data types for a Table Definition are: X(n) X(n.d) Character. Alpha, numeric and all special characters. The n parameter denotes the size of the field in characters; 1 ≤ n ≤ 4088; The form X(n.d) can be used when the X() field is the target for a move from a numeric or binary field. In this case, d represents the number of fractional digits to be stored. However, for the X() data type, the total size of the field is w = n. And n must be large enough to accommodate d fractional digits as well as the decimal point and sign. The n or n.d specifiers can be preceeded by any valid format modifier(s). Internal format: Left justified, blank filled ascii. The presence of an ascii null <0> will cause unpredictable results. VARCHAR(n) VARCHAR(n.d) Variable length character data. The VARCHAR data type is similar to the X() data type, with three important differences: 1) VARCHAR data is stored internally without trailing blank padding; 2) The maximum VARCHAR field length is 262080 characters; whereas the maximum X() field length is 4088 characters; 3) Internally, VARCHAR data uses a two level storage scheme; a fixed portion of the data (by default 20 characters) is stored in the primary data file (the .ddd file); data that exceeds the fixed size is stored in a secondary file that supports variable length data (the .mmm file); In the form VARCHAR(n), the n parameter specifies the maximum field size; 1 ≤ n ≤ 262080; in this case, the fixed data size is MIN(n,20). In the form VARCHAR(n.d), the n parameter specifies the maximum field size; 1 ≤ n ≤ 262080; the d parameter specifies the fixed data size; MIN(n,20) ≤ d ≤ MIN(n,4088); For two special cases: 1) For VARCHAR(n), when n ≤ 20; and 2) For VARCHAR(n.d), when n == d; data is always stored exclusively in the fixed segment. For virtual VARCHAR fields, the fixed data size is set internally to equal the field size; that is, d = n; if the d parameter is specified, it is ignored. Refer to the VARCHAR Data Type description for details. N(n) N(n.d) Numeric. The parameter n denotes the number of digits to the left of the decimal point. The parameter d denotes the number of digits to the right of the decimal point (scale). The total size of the field is w = n + d (precision); 1 ≤ w ≤ 38. Refer to the N Data Type description for the internal format. Byte(n) Arbitrary data. The n parameter denotes the size of the field in bytes; 1 ≤ n ≤ 4088. Internal format: Left justified, zero filled. Refer to the Byte Data Type description for details. Date Special data type for dates. Internally, the DATE data type is stored as an X(8) data type in the form YYYYMMDD. Refer to the Date Data Type description for details. DateTime Special data type for combined date and time. Refer to the DateTime Data Type description for details. DateTimeEx(n) DateTimeEx+Z(n) Special data type for combined date and time with date range from 4713BC to 9999AD, up to seven digit fractional seconds and optional timezone offset. n represents the number of fractional second digits; 0 ≤ n ≤ 7. +Z indicates the inclusion of a timezone offset. Refer to the DateTimeEx Data Type description for details. Time(n) Special data type for time with up to seven digit fractional seconds. n represents the number of fractional second digits; 0 ≤ n ≤ 7. Refer to the Time Data Type description for details. Logical Special data type for data with only two possible values. Refer to the Logical Data Type description for details. I Binary short integer (16 bits); IU Binary unsigned short integer (16 bits); ID Binary long integer (32 bits); IDU Binary unsigned long integer (32 bits); ID64 Binary long integer (64 bits); IDU64 Binary unsigned long integer (64 bits); ID128 Binary signed long integer (128 bits); Refer to the ID128 Data Type description for details. F Single precision floating point(32 bits); FD Double precision floating point(64 bits); MEMO/CLOB/BLOB Arbitrary sized text or binary BLOB Refer to the MEMO Data Type description for details.

1.5.2.3 Indexed Fields

An index is a special structure that facilitates rapid queries on specific field values. Thunderbolt uses balanced binary trees as the basis for its indexing structures. An indexed field is declared in the Data Base Definition by appending the keyword KEY after the data type specification. Any data type except the MEMO data type can be declared as an index. For alphanumeric fields (data type X), the length of an indexed field cannot exceed 256 characters. If a field occurs in more than one table, each instance of the field must have a KEY declaration if that instance is to be included in the index. [It is permissible to have one or more instances of a keyed field that are not keyed.] Additional key-qualifiers can be specified after the KEY keyword to modify the behavior of an index. Key-qualifiers are described in the Indexing document.

1.5.2.4 Temporary Tables

A temporary table is a table that materializes when the database is opened, and de-materializes when the database is closed. A unique instance of a temporary table is created for each user. Temporary Tables are usually used to store computed intermediate results. Because each user has a unique instance of a Temporary Table, multiple users can operate simultaneously on the same Temporary Table without conflict.