Data Base Definition Language

Data Base Definition Language

1.1 Database Definition

A database schema is defined in an ascii text file that is commonly called the Database Definition. By convention, the file extension of a Database Defenintion file is .dbd.

The syntax rules for a Database Definition file are:

   . At least one space between words 
   . One statement per line 
   . No periods at the end of any line 
   . Blank lines, indentations, and extra spacing are allowed 
   . Upper or lower case allowed; that is, DBDs are case insensitive 
   . The @ sign indicates that everything after it is a comment, all 
     text after an @ sign is ignored by the DBD compile process.

1.2 Database Definition Language

The Database Definition Language is comprised of 10 Database Definition Statement types. Aside from blank lines and comment lines, the first statement of a Database Definition is always the DATA BASE NAME Statement. In addition, a Database Definition must contain at least one TABLE DEFINITION Statement and one DATA DEFINITION Statement.

All other Database Definition Statements are optional. And their placement is discretionary; except that they must follow the DATA BASE NAME Statement, and they must be outside the range of a TABLE DEFINITION Statement. However, the following convention is recommended for placement of Database Definition Statements:

DATA BASE NAME Statement (Required)
SUPPRESS GI Statement (Optional)
FILE Statement(s) (Optional)
TABLE DEFINITION Statement(s); followed by DATA DEFINITION Statement(s) (Required)
UNIQUE Statement(s) (Optional)
UNIQUERTL Statement(s) (Optional)
UNIQUERTB Statement(s) (Optional)
DBOPTION Statement(s) (Optional)
TOKENMAP Statement(s) (Optional)

1.2.1 The DATA BASE NAME statement

Syntax: DATA BASE NAME [IS] <data-base-name>

Description: This establishes the internal name of the database. All Application Interfaces (AIs) refer to this name in order to establish their connection. When you compile the DBD, the output files that are created employ the first 48 characters of this name plus a 3-character extension.

The DATA BASE NAME Statement is required; and it must be the first statement in the Database Definition.

1.2.2 The TABLE DECLARATION Statement

Syntax: TD <tablename> <nnnn> RECORDS [{TEMPORARY | TEMP}] [GROW [NNNNN]] [BUFFn]

      Where:
         BUFFn is the optional page size:
            BUFF4   - 4K page [default]
            BUFF8   - 8K page
            BUFF16  - 16K page
            BUFF32  - 32K page
            BUFF64  - 64K page

             TD <tablename> [{TEMPORARY | TEMP}]
                is interpreted as:
             TD <tablename> 5000 RECORDS [{TEMPORARY | TEMP}] GROW

Description: Table Definitions provide the data picture of each data record type in the database.

Each Table Definition begins with the TABLE DECLARATION Statement, and is followed by one or more DATA DECLARATION Statements.

A Database Definition must have at least one TABLE DEFINITION Statement. Each TABLE DEFINITION Statement must have at least one DATA DEFINITION Statement.

The optional key word TEMPORARY causes the specified table not to be included in the permanent data base. Rather, a TEMPORARY table is instantiated as an empty table each time the data base is opened. There is a separate instance of a TEMPORARY table for each instance of the open data base. Any data that is written to a TEMPORARY table is lost when the data base instance is closed.

A TEMPORARY table cannot have a keyed field.

The optional key word GROW allows the specified table to grow beyond its initial size.

When the Data Base is initially created, storage in the .ddd file is pre-allocated only for the first nnnn records of the GROWable table. But the .ddd file will grow as additional records are written, up to NNNN records.

If the NNNN parameter is omitted, the table can grow to the maximum possible size of 2,147,479,552.

The GROW modifier can also be used with the TEMPORARY modifier. In this case, the table is initially created with <nnnn> records; but it can grow unconditionally to 2,147,479,552 records. [In addition, there can be multiple TEMP GROW tables; and each can grow to the maximum size.]

The maximum number of records in a table is 2,147,479,552 (0x7FFFF000).

For tables without the GROW attribute the aggregate maximum number of records is also 2,147,479,552 (0x7FFFF000). For tables with the GROW attribute there is no aggregate limit.

1.2.3 The DATA DECLARATION Statement (DBD)

Syntax: [:<level>] <fieldname> <data-type> [{[KEY [<key-modifiers>]] | <field-modifiers>}]

Description: Each DATA DECLARATION Statement specifies the name of a field, the field type, the field size, and whether the field is keyed.

<fieldname> Declaration

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

A field cannot have the same name as a table. Within a table, two fields cannot have the same name.

<data-type> Declaration

X(n) X(n.d)	Character. Alpha, numeric and all special characters. The n parameter denotes the size of the field in characters. 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 accomodate 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). The maximum permissible size for the X() data type is 4088 characters. 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; however, there are 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 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; 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. Refer to the VARCHAR Data Type description for details.
N(n) N(n.d)	Numeric. Only numbers, sign, and decimal point allowed. 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. The total size of the field is w = n + d.
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 integer (32 bits);
IDU	Binary unsigned 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	Arbitrary sized text or binary BLOB

<Key> Declaration

An indexed field is declared 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.]

<Key-modifiers> Definition

Primary key

PRIMARY

Case sensitivity

SENSITIVE

Auto increment

IDENTITY [(<seed>,<increment>)]

Node architecture

FIXED [Default]	fixed length node
--------------------------	--------------------------
VARIABLE	variable length node

List architecture

STANDARD [Default]	two level list
--------------------------	--------------------------
REALTIME	list-tree hybrid

File architecture

INTEGRAL [Default]	Multiple indexes per file
--------------------------	--------------------------
DISCRETE	Single index per file

File block size (.ttt)

BUFF4 [Default]	4K block size
--------------------------	--------------------------
BUFF8	8K block size
--------------------------	--------------------------
BUFF16	16K block size
--------------------------	--------------------------
BUFF32	32K block size
--------------------------	--------------------------
BUFF64	64K block size

VARCHAR
trailing blank mode

NOT_SIGNIFICANT [Default]	Trailing Blanks Not Significant
--------------------------	--------------------------
SIGNIFICANT	Trailing Blanks Significant

Data connection

[Default]	Auto based on field
--------------------------	--------------------------
EXTERNAL [(n)] VIRTUAL	User defined data User defined record pointer

List data content

[Default] EXTERNAL [(n)] VIRTUAL	Record pointer User defined data User defined record pointer

<Field-modifiers> Definition

VARCHAR
trailing blank mode

NOT_SIGNIFICANT [Default]	Trailing Blanks Not Significant
--------------------------	--------------------------
SIGNIFICANT	Trailing Blanks Significant

Data location

[Default]	Data is resident in data base
--------------------------	--------------------------
VIRTUAL	Data is not resident

<level> Declaration

The level attribute is a decimal number between 1 and 99, preceded by a colon (':'). Level numbers within a fieldname hierarchy must be increasing. Level numbers may be used to create fieldname aliases, field breakdowns and field groupings.

When using multiple levels, the aggregate field lengths of each level must be equal; e.g.,

     DATA1     X(50)
  :1 DATA2     X(30)
  :1 DATA3     X(20)

is OK; but

     DATA1     X(50)
  :1 DATA2     X(30)
  :1 DATA3     X(10)

would give a length error; for the sake of completeness,

     DATA1
  :1 DATA2     X(30)
  :1 DATA3     X(20)

is acceptable; in this case dbd comp would calculate the length of DATA1 and by default create it as an X(50) data type

1.2.4 The SUPPRESS GI Statement

Syntax: SUPPRESS GI

Description: Without this clause the DBD compile process generates a General Interface (GI) that provides access to the entire database. The name of this GI is the same as the first 8 characters of the database name (with no extension).

When you are generating your own Application Interfaces (AIs) you have no particular need for a GI. The use of the SUPPRESS GI clause prevents the generation of a GI at compile time, and thus restricts access only to those who know the whereabouts of an AI.

1.2.5 The DBOPTION Statement

Syntax: DBOPTION <option>

Description: The purpose of the DBOPTION clause is to allow the database developer to specify tuning or construction options. The available options are:

   DbOption Bnn    @ DBD; Specify default page size; nn = {4, 8, 16, 32, 64}; default is 4;
   DbOption Gnnn   @ DBD; Allow nnn records Grow Room in DRL; this allows tables to be added
                   @ at the end of the dbd with DBDCOMP/S if the table has the GROW attribute,
                   @ only the fixed record count counts against nnn;
                   @ the available GROWROOM can be queried with VERIFY DBD GROWROOM;
   DbOption I      @ DBD; Use integral .ttt & .lll files
   DbOption L      @ DBD; Set "No Cache" flag
   DbOption O      @ DBD; Do not Pack on DBD COMP/R
   DbOption T      @ DBD; Use separate .ttt & .lll files
   DbOption U      @ DBD; Use large Update Strand
   DbOption W      @ DBD; Suppress record numbers in record

The U option affects the size of an internal structure that we call the "update strand." For databases larger than a half million records that do more than trivial updates, it is advantageous to use the DbOption U option.

The W option eliminates reduntant system housekeeping data from the data record, reducing the size of each data record by 8 bytes. The result of this option is that deleted records are not purged on DBD COMP/R;

1.2.6 The FILE Statement

Syntax: FILE DATA [ON] <drive-path>
               FILE TREE [ON] <drive-path>
               FILE LIST [ON] <drive-path>
               FILE TTT <key-name> [ON] <drive-path>
               FILE LLL <key-name> [ON] <drive-path>
               FILE RTL <key-name> [ON] <drive-path>
               FILE RTB <key-name> [ON] <drive-path>

Description: If you have more than one disk storage drive you can use the FILE clause to get drive separation of the three most often accessed files of a database.
Drive separation reduces wear and tear on disk drives, and can improve overall efficiency. When we say drive separation we really mean head separation, if you have two drive letters mapped to the same physical drive it does you no good to use the FILE clause.

The three largest and most often accessed files of a Thunderbolt database are the data space, the tree space, and the list space. The data space is going to be on the same drive with the DBD, but you can use the FILE clause to specify different drives for the tree and/or list space, or both if you have three drives.

When you specify the drive and folder for the tree or list (or both) files, you can use the UNC (Universal Naming Convention). Thus you avoid problems if your installation is the type that has different drive letter mappings for different workstations.

1.2.7 The UNIQUE statement

Syntax: UNIQUE <keyname> <number>
UNIQUE <keyname> SZ(tablename)
UNIQUE <keyname> SZ(tablename)*factor

Description: The UNIQUE clause is used to tune tree structure space, preventing wasted space.

1.2.8 The UNIQUERTL statement

Syntax: UNIQUERTL <keyname> <RTL-Block-Count> [<RTL-Block-Size> [<Local-List-Size>]]

Where:

keyname represents the name of the subject index;
RTL-Block-Count represents the number of 4K blocks to be pre-allocated to the .RTL file; the default is 1;
RTL-Block-Size represents the block size; possible values are: {128, 256, 512, 1024, 2048, 4096}; default is 1024;
Local-List-Size represents the size of the Local List that is attached to the node; must be in the range 4 thru 64; default is 4;

Description:

1.2.9 The UNIQUERTB statement

Syntax: UNIQUERTB <keyname> <RTB-Block-Count> [<RTB-Threshhold> [<RTB-List>]]

Where:

keyname represents the name of the subject index;
RTB-Block-Count represents the number of bitmaps to be pre-allocated to the .RTB file; the default is 0;
RTB-Threshhold represents a decimal fraction between 0.00 and 1.00; this is the density where an RTL is automatically converted to an RTB; the recommended value is in the range 0.03 to 0.05; the default is 0;
RTB-List this is an optional list of values that are unconditionally indexed as RTBs; the form of the list is {V1,V2,...Vn}; the list can also include values in the form ~Vm which causes value Vm to be omitted from the index; finally, the list specifier may be in the form ALL; the following are valid RTB-Lists:
      {TX,CA,NY,FL,IL}
      {1,~0}
      ALL
      ALL {~0}
      ALL {~' '}

Description:

1.2.10 The TOKENMAP statement

Syntax: TOKENMAP [SENSITIVE] <Table-Name> <Schema-ColName> <Table-ColName> <Column-ColName> <Mapped-Table-ColName> <Mapped-Column-ColName>

Where:

Table-Name is the [internal] name of the table that contains token mapping data;
Schema-ColName is the [internal] name of the column that contains the external schema name;
Table-ColName is the [internal] name of the column that contains the external table name;
Column-ColName is the [internal] name of the column that contains the external column name;
Mapped-Table-ColName is the [internal] name of the column that contains the internal table name;
Mapped-Column-ColName is the [internal] name of the column that contains the internal column name;

Description:

If the SENSITIVE qualifier is include, all references to schema-names, table-names and column-names are cases sensitive. In addition:
1) In Calc-Object, all references to local variables are case sensitive;
2) All schema-names, table-names and column-names created by ddlCreateTempTable() are case sensitive;
3) All schema-names, table-names and column-names imported by ddlAttach() are case sensitive;