Btrieve uses keys to allow fast direct access to records in a Btrieve file. Because Btrieve has no way of knowing the structure of the records in each file, you define each key by identifying the following:

• The key's offset in bytes from the beginning of the record
• The number of bytes you want to use for the key
• The key's type (identifying how sorting is to be performed)

For example, suppose a particular key begins at the eighth byte of the record and extends for four bytes. When you insert a record into the file, Btrieve extracts four bytes, beginning at the eighth byte, and uses this extracted value to position the record in the index.

When you create a key, you can assign attribute and type information to it. Key attributes and types are closely related and must be used in conjunction with each other.

Appendix B, "Description Files," discusses the various key attributes and key types in term of specifying these items when you create a description file.

Key Attributes

The following key attributes are available:

• "Segmented and Nonsegmented Keys"
• "Duplicatable and Nonduplicatable Keys"
• "Linked-Duplicatable and Repeating-Duplicatable Keys" (6.x)
• "Modifiable and Nonmodifiable Keys"
• "Descending and Ascending Keys" (sort order)
• "Case-Insensitive and Case-Sensitive Keys" (sort order)
• "Alternate Collating Sequence" (sort order)
• "Null Keys (All-Segment and Any-Segment)"

Segmented and Nonsegmented Keys

Keys can consist of one or more segments in each record. A segment can be any set of contiguous bytes in the record. The total length of a key equals the sum of the length of the key segments, and the maximum length is 255 bytes. Different key segments may overlap each other in the record.

A Btrieve file limits the maximum number of key segments (rather than the maximum number of keys). The maximum number of key segments allowed depends on the page size, as shown in Table 9.

A file with a page size of 512 bytes may contain 1 key with 8 segments, 8 keys with 1 segment each, or any combination in between. If a file has a page size of 1,024 bytes, it may contain 1 key with 23 segments, 23 keys with 1 segment, or any combination in between.

The key type can be different for each segment in the key. The sort order, either ascending or descending, can be different for each segment as well.

When a segmented key is a nonduplicatable key (see the following section), the combination of the segments must form a unique value. However, individual segments may contain duplicates. Duplicatable keys

When you are defining this type of segmented key, each segment would have duplicates=no as a key-level attribute even though that particular segment could have duplicates. To ensure that a particular segment is always unique, define it as a separate nonduplicatable key in addition to the segmented key definition.

Duplicatable and Nonduplicatable Keys

If you define a key to be duplicatable, Btrieve allows more than one record to have the same value for that key. If you specify a key as having no duplicates, Btrieve does not allow an application to add multiple records with the same value as this key.

For example, in a file containing customer records, you can define the zip code as a duplicatable key so that many different records can contain the same value for the zip code. However, if you also make the customer number a key, you probably would not want to allow duplicates, since each customer should have a unique number.

NOTE: If one segment of a segmented key allows duplicates, all the segments must allow duplicates. For more information, "Segmented and Nonsegmented Keys".

For information about how Btrieve stores duplicate key values, see the following section and "Indexes".

Linked-Duplicatable and Repeating-Duplicatable Keys

By default in a 6.x file, Btrieve stores duplicatable keys as linked-duplicatable keys.

In this situation, Btrieve stores the key extracted from the first record of a duplicatable key on an index page. When the first record containing a duplicate value for a key is inserted into a file, Btrieve does not store the duplicate key value on the index page.

Instead, it goes to the original record (the one whose key value is duplicated in the new record) and stores a pointer at the end of the record. The pointer points to the new record.

As each new record with a duplicate key value is inserted into the file, Btrieve stores a pointer to that record in the preceding record containing the same key value. In addition to having pointers that point to the next record containing a duplicate key value, Btrieve has pointers that point to the preceding record containing that duplicate key value. Btrieve uses the pointers in its Get Next and Get Previous operations.

This method of storing pointers at the end of data records (as opposed to storing keys and values on index pages) forms a doubly linked list of records with keys containing duplicate values, resulting in the name linked-duplicatable keys.

NOTE: If you create a file with linked-duplicatable keys, Btrieve reserves space in each record of the file for that key's duplicate pointer.

If no room is available to create a linked-duplicatable key (that is, if no duplicate pointers were reserved at file creation, or if no index has been dropped to free existing pointers), Btrieve creates a repeating-duplicatable key.

Btrieve stores every key value of a repeating-duplicatable key both on a data page and on an index page. In other words, the key's value resides in the record on a data page and is repeated in the key entry on an index page.

By default in 6.x files, Btrieve stores duplicatable keys as linked-duplicatable keys. However, Btrieve 6.1x allows you to override that default.

Modifiable and Nonmodifiable Keys

You can also define a key as modifiable or nonmodifiable. If you define a key as modifiable, Btrieve allows your application to update an existing record and change the value of the key.

For example, if the account balance is a key, you may allow a program to modify the value of that key as the customer makes purchases and payments. However, if the customer's account number is a key, you probably would make it a nonmodifiable key because the customer's account number should not change.

NOTE: If one segment of a key is modifiable, all the segments must be modifiable.

Descending and Ascending Keys

Btrieve normally orders key values in ascending order (lowest to highest). However, you can specify that Btrieve order the key values in descending order (highest to lowest) when you define the key segment.

When an application performs a Get Greater (8) operation on a descending key, Btrieve returns the record corresponding to the first key value that is lower than the key value you specify in the key buffer.

For example, consider a file which has 10 records and a descending key of type integer. The actual values stored in the 10 records for the descending key are the integers 0, 1, 2, 3, 4, 5, 6, 7, 8, and 9. If the current record's key value is 5 and you perform a Get Greater operation, Btrieve returns the record containing the key value 4.

Similarly, when an application performs a Get Less Than (10) operation using a descending key, Btrieve returns the record with the next higher value than the one you specify in the key buffer.

Using the preceding example, if the current record's descending key has a value of 5 and the application performs a Get Less Than operation, Btrieve returns the record containing the key value 6.

Case-Insensitive and Case-Sensitive Keys

By default, Btrieve is case sensitive when sorting string keys---that is, it sorts string key values based on whether letters are uppercase or lowercase. Btrieve sorts the values by putting the uppercase value first in the sort. For example, AAA and BBB would sort before aaa.

To make Btrieve ignore case when sorting string keys, you can assign the case-insensitive attribute to a key. When you define a key to be case insensitive, Btrieve assigns the same collating value to characters a through z when sorting the key as it assigns to the corresponding characters A through Z.

NOTE: Case sensitivity does not apply if Btrieve sorts a key according to the collating weights in an ACS.

Alternate Collating Sequence

You can also direct Btrieve to sort string keys (types lstring, zstring, and string) differently from the standard ASCII collating sequence.

By using one or more alternate collating sequences (ACSs), you can sort keys in one of the following ways:

• By a locale-specific collating sequence (such as the German, Swedish, or Finnish character sets)

• By your own user-defined sorting order, which may require a sorting sequence that mixes alphanumeric characters (A-Z, a-z, and 0-9) with nonalphanumeric characters (!, #, and so on).

  NOTE: Only files using the format for Btrieve 6.1x or later can have more than one ACS.

Locale-Specific ACS

Btrieve 6.1x allows you to sort an index by languages other than English. This locale (national language) support originates in the operating system. Therefore, refer to your operating system's documentation for specific information.

When you create a key of type string, lstring, or zstring (described in "Key Types"), you can instruct Btrieve to sort the values for that key according to a specified locale's collating sequence. The application can designate the locale by passing to Btrieve a locale's country ID and code page number.

Alternatively, the application can specify the ACS for the default locale---that is, the locale for which the operating system running Btrieve is configured. For client applications making calls to server-based Btrieve, this is the server's locale.

When an application calls Btrieve to create an index that is to be sorted according to a locale-specific ACS, Btrieve queries the operating system to retrieve the desired collating sequence. When the operating system returns the collating sequence, Btrieve stores it in the Btrieve file.

If the Btrieve file is moved to a different locale, or if the same computer is reconfigured to support a different locale, Btrieve still sorts the index according to the original locale's collating sequence, thereby allowing Btrieve to insert new key values correctly.

NOTE: Because Btrieve obtains the locale's collating sequence by making operating system calls, it cannot successfully create an index for a locale that is not supported by the operating system. Instead, Btrieve returns an error code to the application. The NetWare operating system supports only one locale at a time, so only the server's default locale is supported.

To create an ACS that will sort a Btrieve file according to a locale-specific collating sequence, an application must place 265 bytes directly into the data buffer of a Create (14) operation, using the format shown in Table 10.

The 265-byte ACS definition should follow the last key specification block in the data buffer.

NOTE: To use the default locale-specific ACS, specify a value of 0xFFFF for the Country ID and the Code page ID.

User-Defined ACS

To create an ACS that will sort a Btrieve file according to a user-defined collating sequence, the application must place 265 bytes (using the format shown in Table 11) directly into the data buffer of a Create (14) operation following the last key specification block in the data buffer.

Following are a 9-byte header and a 256-byte body that represent a collating sequence named UPPER. The header appears as follows:

AC 55 50 50 45 52 20 20 20

The 256-byte body appears as follows (with the exception of the offset values in the leftmost column):

00: 00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F 10: 10 11 12 13 14 15 16 17 18 19 1A 1B 1C 1D 1E 1F 20: 20 21 22 23 24 25 26 27 28 29 2A 2B 2C 2D 2E 2F 30: 30 31 32 33 34 35 36 37 38 39 3A 3B 3C 3D 3E 3F 40: 40 41 42 43 44 45 46 47 48 49 4A 4B 4C 4D 4E 4F 50: 50 51 52 53 54 55 56 57 58 59 5A 5B 5C 5D 5E 5F 60: 60 41 42 43 44 45 46 47 48 49 4A 4B 4C 4D 4E 4F 70: 50 51 52 53 54 55 56 57 58 59 5A 7B 7C 7D 7E 7F 80: 80 81 82 83 84 85 86 87 88 89 8A 8B 8C 8D 8E 8F 90: 90 91 92 93 94 95 96 97 98 99 9A 9B 9C 9D 9E 9F A0: A0 A1 A2 A3 A4 A5 A6 A7 A8 A9 AA AB AC AD AE AF B0: B0 B1 B2 B3 B4 B5 B6 B7 B8 B9 BA BB BC BD BE BF C0: C0 C1 C2 C3 C4 C5 C6 C7 C8 C9 CA CB CC CD CE CF D0: D0 D1 D2 D3 D4 D5 D6 D7 D8 D9 DA DB DC DD DE DF E0: E0 E1 E2 E3 E4 E5 E6 E7 E8 E9 EA EB EC ED EE EF F0: F0 F1 F2 F3 F4 F5 F6 F7 F8 F9 FA FB FC FD FE FF

The header and body forming this ACS are shipped with this version of Btrieve as the file UPPER.ALT. UPPER.ALT provides a way to sort keys without regard to case, although Btrieve 6.x makes this method obsolete by allowing an application to specify in bit 10 of a key's flag values whether the key is to be sorted using case sensitivity.

However, UPPER still provides a good example for use when writing your own ACS.

Offsets 0x61 through 0x7A in the example have been altered from the standard ASCII collating sequence. In the standard ASCII collating sequence, offset 0x61 contains a value of 0x61 (representing lowercase 'a'). When a key is sorted with the UPPER ACS, Btrieve sorts lowercase 'a' (0x61) with the collation weight found at offset 0x61: 0x41. Thus, the lowercase 'a' is sorted as if it were uppercase 'A' (0x41).

Therefore, for sorting purposes UPPER converts all lowercase letters to their uppercase equivalents when sorting a key.

Each 1-byte position in the map corresponds to the code point having the same value as the position's offset in the map. The value of the byte at that position is the collating weight assigned to the code point.

For example, to force code point 0x61 ('a') to sort with the same weight as code point 0x41 ('A'), place the same values at offsets 0x61 and 0x41.

The following 256-byte body basically performs the same function as UPPER.ALT's body except that ASCII characters preceding the ASCII space (0x20) are now sorted after all other ASCII characters:

00: E0 E1 E2 E3 E4 E5 E6 E7 E8 E9 EA EB EC ED EE EF 10: F0 F1 F2 F3 F4 F5 F6 F7 F8 F9 FA FB FC FD FE FF 20: 00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F 30: 10 11 12 13 14 15 16 17 18 19 1A 1B 1C 1D 1E 1F 40: 20 21 22 23 24 25 26 27 28 29 2A 2B 2C 2D 2E 2F 50: 30 31 32 33 34 35 36 37 38 39 3A 3B 3C 3D 3E 3F 60: 40 21 22 23 24 25 26 27 28 29 2A 2B 2C 2D 2E 2F 70: 30 31 32 33 34 35 36 37 38 39 3A 5B 5C 5D 5E 5F 80: 60 61 62 63 64 65 66 67 68 69 6A 6B 6C 6D 6E 6F 90: 70 71 72 73 74 75 76 77 78 79 7A 7B 7C 7D 7E 7F A0: 80 81 82 83 84 85 86 87 88 89 8A 8B 8C 8D 8E 8F B0: 90 91 92 93 94 95 96 97 98 99 9A 9B 9C 9D 9E 9F C0: A0 A1 A2 A3 A4 A5 A6 A7 A8 A9 AA AB AC AD AE AF D0: B0 B1 B2 B3 B4 B5 B6 B7 B8 B9 BA BB BC BD BE BF E0: C0 C1 C2 C3 C4 C5 C6 C7 C8 C9 CA CB CC CD CE CF F0: D0 D1 D2 D3 D4 D5 D6 D7 D8 D9 DA DB DC DD DE DF

In this body, different collating weights have been assigned so that a character's weight no longer equals its ASCII value. For example, offset 0x20, representing the ASCII space character, has a collating weight of 0x00; offset 0x41, representing the ASCII uppercase `A', has a collating weight of 0x21.

To achieve the ability to sort keys without regard to case, offsets 0x61 through 0x7A in the last example have been altered. As in the body for UPPER.ALT, offset 0x61 now has the same collating weight as offset 0x41: 0x21. By having the same collating weight, offset 0x41 (uppercase `A') sorts the same as offset 0x61 (lowercase `a').

ACSs and Multiple Btrieve Files

If you have multiple files with different ACSs, each sequence should have a different name to avoid confusing two sequences that have the same name but that collate differently.

Multiple ACSs in a Single Btrieve File

You can use multiple ACSs in a single Btrieve file. Btrieve allows one ACS per key. Therefore, if the key is segmented, each segment must use either the key's specified ACS or no ACS at all.

In a Btrieve file in which a key has an ACS designated for some segments but not for others, Btrieve sorts only the ACS segments by the specified ACS.

Null Keys (All-Segment and Any-Segment)

NOTE: Previous editions of the Btrieve documentation used the term manual key. This edition replaces that term with any-segment null key.

You can direct Btrieve to exclude certain records from an index by defining null keys. When you define a null key, you specify a null value by which Btrieve can recognize the key as being a null key.

You can use a null value in a key when the data for the key is unavailable or when you do not want Btrieve to include a record for that key in the index. Null keys allow you to avoid searching through meaningless records in an index path and to eliminate the overhead time required to update the index each time a key with no meaningful data is inserted.

When data becomes available for that key, and when you want to include the record in the index, you can update the record, replacing the null value with meaningful data.

If you define one segment of a key as a null key, you must define all segments of that key as a null key. However, Btrieve allows you to define different null values for different segments in a segmented key. The most commonly used null values are ASCII blank (0x20) and binary 0 (0x00).

Btrieve can use one of two different methods to determine whether a key is a null key. You determine which method Btrieve uses by specifying one of the two possible null key attributes:

• All segments
• Any segmen t

If you specify the all-segment null key attribute, Btrieve excludes from the index those records in which all the bytes in all segments of a key contain a null value. (Previous editions of the Btrieve documentation called this attribute the null key attribute.)

If you specify the any-segment null key attribute, Btrieve excludes from the index those records in which every byte in any segment of a key contains the null value. (Previous editions of the Btrieve documentation called this attribute the manual key attribute.)

Often, an any-segment null key is used as a flag that indicates whether the key is to be indexed. If the segment contains only the null value that you defined for that segment, Btrieve does not include the key in the index even though the rest of the segments in the key contain non-null values.

By updating the flag segment with any value other than the specified null value, you can instruct Btrieve to include the record in the index.

IMPORTANT: If you specify both the all-segment and any-segment null key attributes, Btrieve uses only the any-segment null key attribute.

The following diagram shows three segments of a key in a given record:

The null value defined for the first and last segments of the key is the ASCII blank (0x20). The null value defined for the middle segment is binary 0 (0x00). As the diagram shows, all the bytes in each segment contain their defined null value. Therefore, Btrieve excludes this record from its index if you specify either the all-segment or the any-segment null key attribute.

The following diagram shows three segments of a key in a different record:

The null values defined for the segments in this diagram are the same as before (0x20 for the first and last segment, 0x00 for the middle segment).

As the second diagram shows, all the bytes in the first segment contain their defined null value. However, the second and third segments contain non-null values. Therefore, Btrieve excludes this record from its index only if you specify the any-segment null key attribute. If you specify the all-segment null key attribute, Btrieve includes the record in its index.

The second diagram shows a situation in which the first key segment could be used as a flag to indicate whether the key is to be indexed.

For example, an application would first define the segments as any-segment null keys. If the application wanted to include a record in the index, it would place a non-null value in the first segment. Conversely, if the application did not want to include a record in the index, it would place a null value in the first segment.

Key Types

Btrieve provides standard and extended key types. These types allow Btrieve to recognize and collate key values based on the internal storage format of the 15 data types that compilers most commonly use. This capability provides greater flexibility when you design the keys of your Btrieve files.

For historical reasons, the two standard key types, string and unsigned binary, are also offered as extended key types.

Internally, Btrieve compares string keys on a byte-by-byte basis, from left to right. Btrieve sorts string keys according to their ASCII value. However, for string keys, you can specify either case-insensitive sorting or an ACS.

Btrieve compares unsigned binary keys a word at a time. It compares these keys from right to left because the Intel 8086 family of processors reverses the high and low bytes in an integer.

Table 12 lists the extended key types and their associated codes.

The following sections, arranged alphabetically by key type, describe the extended key types.

autoincrement

An autoincrement key is a signed Intel-style integer that can be either two or four bytes long. Internally, autoincrement keys are stored in Intel binary integer format, with the high-order and low-order bytes reversed within a word.

Btrieve sorts autoincrement keys by their absolute values, comparing the values stored in different records a word at a time from right to left. Autoincrement keys are incremented each time you insert a record into the file.

The following restrictions apply to autoincrement keys:

• An autoincrement key cannot allow duplicate key values.
• An autoincrement key cannot be segmented or included as a segment of another key.
• An autoincrement key cannot overlap another key.

Btrieve treats autoincrement key values as follows when you insert records into a file:

• If you specify a value of binary 0 for the autoincrement key, Btrieve assigns a value to the key based on the following criteria:
• If you are inserting the first record in the file, Btrieve assigns the value of 1 to the autoincrement key.
• If records already exist in the file, Btrieve assigns the key a value that is one number higher than the highest existing absolute value in the file.
• If you specify a nonzero value for the autoincrement key, Btrieve inserts the record into the file and uses the specified value as the key value. If a record containing that value already exists in the file, Btrieve returns a nonzero status and does not insert the record.

When you delete a record containing an autoincrement key, Btrieve completely removes the record from the file. Btrieve does not reuse the deleted key value unless you specify that value when you insert another record into the file.

As mentioned previously, Btrieve always sorts autoincrement keys by their absolute values. For example, you can handle this key type in the following ways:

• Specify a negative value for an autoincrement key when you insert a record.
• Update a record and negate the value for the autoincrement key.

Either way, Btrieve sorts the key according to its absolute value. This allows you to use negation to flag records without altering the record's position in the index. In addition, when an application performs a Get operation and you specify a negative value in the key buffer, Btrieve treats the negative value as the absolute value of the key.

bfloat

A field with a bfloat type is a single- or double-precision real number. A single-precision real number is stored with a 23-bit mantissa, an 8-bit exponent biased by 128, and a sign bit.

The representation of a double-precision real number is the same as that for a single-precision real number except that the mantissa is 55 bits instead of 23 bits. The least significant 32 bits are stored in bytes 0 through 3. The bfloat type is commonly used in BASIC applications.

date

A date-type field is stored internally as a 4-byte value. The day and the month are each stored in 1-byte binary format. The year is a 2-byte binary number that represents the entire year value.

Btrieve places the day into the first byte, the month into the second byte, and the year into a two-byte word following the month.

decimal

A decimal-type field is stored internally as a packed decimal number with two decimal digits per byte.

The sign nibble is either 0xF or 0xC for positive numbers and 0xD for negative numbers. The decimal point is implied---that is, no decimal point is stored in the decimal-type field. The application is responsible for tracking the location of the decimal point for the value in a decimal-type field.

All the values for a decimal key type must have the same number of decimal places in order for Btrieve to collate the key correctly. The decimal type is commonly used in COBOL applications.

float

A float type is consistent with the IEEE standard for single- and double-precision real numbers. The internal format for a 4-byte float consists of a 23-bit mantissa, an 8-bit exponent biased by 127, and a sign bit.

A float-type field with 8 bytes has a 52-bit mantissa, an 11-bit exponent biased by 1,023, and a sign bit.

integer

An integer type is a signed whole number and must contain an even number of bytes. Internally, integer-type fields are stored in Intel binary integer format, with the high-order and low-order bytes reversed within a word.

Btrieve evaluates the key from right to left, a word at a time. The sign must be stored in the high bit of the rightmost byte. The integer type is commonly used in C language applications.

logical

The logical extended key type is stored as a 1- or 2-byte value. Btrieve collates logical key types as a string. This allows an application to determine the stored values that represent true or false.

lstring

An lstring type in Btrieve has the same characteristics as a regular string type except that the first byte of the string contains the binary representation of the string's length. The length stored in byte 0 of the lstring determines the number of significant bytes. Btrieve ignores any values beyond the specified length of the string.

The lstring type is commonly used in Pascal applications.

money

The internal representation of the money type is the same as that for the decimal type.

numeric

Numeric values are stored as ASCII strings, right justified with leading zeros. Each digit occupies one byte internally. For positive numbers, the rightmost digit can be represented by 1 through 0 instead of A through {. Btrieve processes positive numbers represented either way.

The numeric type is commonly used in COBOL applications.

sign trailing separate

The sign trailing separate data type (sometimes called numeric STS) is a COBOL data type that has values resembling those of the numeric data type. Sign trailing separate values are stored as ASCII strings and right justified with leading zeros.

However, the rightmost byte of a sign trailing separate string is either '+' (ASCII 0x2B) or '-' (ASCII 0x2D). This differs from numeric values that embed the sign in the rightmost byte along with the value of that byte.

string

A string type in Btrieve is a sequence of characters ordered from left to right. Each character is represented in ASCII format in a single byte except when Btrieve is determining whether a key value is null. (See "Null Keys (All-Segment and Any-Segment).")

time

A time-type field is stored internally as a 4-byte value. Hundredths of a second, minute, and hour are each stored in 1-byte binary format. Btrieve places the hundredths of a second value into the first byte, followed respectively by the second, minute, and hour values.

unsigned binary

Btrieve sorts unsigned binary keys as unsigned integer keys. An unsigned binary key must contain an even number of bytes (2, 4, 6, and so on). Btrieve compares unsigned binary keys a word at a time, from right to left.

An unsigned binary key is sorted in the same manner as an integer key. The differences between an unsigned binary key and an integer key are that an integer has a sign bit, while an unsigned binary type does not, and an unsigned binary key can be longer than 4 bytes.

zstring

A zstring type in Btrieve corresponds to a C string. It has the same characteristics as a regular string type except that a zstring type is terminated by a byte containing a binary 0. Btrieve ignores any values beyond the first binary 0 it encounters in the zstring except when Btrieve is determining whether a key value is null. (See "Null Keys (All-Segment and Any-Segment).")