Creating a Data File

Btrieve gives developers tremendous flexibility in optimizing database applications. In providing such flexibility, Btrieve exposes a great deal of the inner workings of the MicroKernel. If you are new to Btrieve, the Create (14) operation may appear quite complex to you, but you do not need all of the features this operation provides to get started. This section highlights the basic requirements by stepping you through the creation of a simple, transaction-based data file. For simplification where necessary, this section uses C interface terminology.

Data Layout

This section uses an example data file that stores employee records. The application will retrieve employee information by providing either a unique employee ID or the employee's last name. Because more than one employee can have the same last name, the database will allow duplicate values for the last name. Based on these criteria, the data layout for the file is as follows:

Now that the basic data layout is established, you can begin applying Btrieve terminology and requirements. This includes determining information about the key structure and file structure before you actually create the file. You must work out these details in advance, because the Create (14) operation creates the file, index, and key information all at once. The following sections discuss the issues to consider in working out these details.

Key Attributes

First, determine any special functionality for the keys. The MicroKernel supports a variety of key attributes you can assign, as shown in the following table.

You assign these key attributes for each key you define. Each key has its own key specification. If the key has multiple segments, you have to provide the specification for each segment. Some of these attributes can have different values for different segments within the same key. Using the previous example, the keys are the last name and the employee ID. Both keys use extended types; the last name is a string and the employee ID is an integer. Both are modifiable, but only the last name is duplicatable. In addition, the last name is case insensitive.

Regarding the data type you assign to a key, Btrieve does not validate that the records you input adhere to the data types defined for the keys. For example, you could define a TIMESTAMP key in a file, but store a character string there or define a date key and store a value for February 30. Your Btrieve application would work fine, but an ODBC application that tries to access the same data might fail, because the byte format could be different and the algorithms used to generate the timestamp value could be different. For complete descriptions of the data types, refer to the SQL Engine Reference.

File Attributes

Next, determine any special functionality for the file. The MicroKernel supports a variety of file attributes you can assign, as follows:

The example data file does not use any of these file attributes, because the records are fixed-length records of small size.

For definitions of file attributes, refer to File Types . For more information about specifying file attributes during the Create operation, refer to the Pervasive.SQL API Programmer's Reference.

Creating File and Key Specification Structures

When you use the Create operation, you pass in a data buffer that contains file and key specification structures. The following structure uses the example employee data file.

Calling the Create Operation

The Create operation (14) requires the following values:

In C, the API call would be as follows:

/* define the data buffer structures */ 
typedef struct 
{ 
 BTI_SINT recLength; 
 BTI_SINT pageSize; 
 BTI_SINT indexCount; 
 BTI_CHAR reserved[4]; 
 BTI_SINT flags; 
 BTI_BYTE dupPointers; 
 BTI_BYTE notUsed; 
 BTI_SINT allocations; 
} FILE_SPECS; 
typedef struct 
{ 
 BTI_SINT position; 
 BTI_SINT length; 
 BTI_SINT flags; 
 BTI_CHAR reserved[4]; 
 BTI_CHAR type; 
 BTI_CHAR null; 
 BTI_CHAR notUsed[2]; 
 BTI_BYTE manualKeyNumber; 
 BTI_BYTE acsNumber; 
} KEY_SPECS; 
typedef struct 
{ 
 FILE_SPECS fileSpecs; 
 KEY_SPECS keySpecs[2]; 
} FILE_CREATE_BUF; 
/* populate the data buffer */ 
FILE_CREATE_BUF databug; 
memset (databuf, 0, size of (databuf)); /* 
initialize databuf */ 
dataBuf.recLength = 72; 
dataBuf.pageSize = 4096; 
dataBuf.indexCount = 2; 
dataBuf.keySpecs[0].position = 1; 
dataBuf.keySpecs[0].length = 25; 
dataBuf.keySpecs[0].flags = EXTTYPE_KEY + 
NOCASE_KEY + DUP + MOD; 
dataBuf.keySpecs[0].type = ZSTRING; 
dataBuf.keySpecs[1].position = 52; 
dataBuf.keySpecs[1].length = 4; 
dataBuf.keySpecs[1].flags = EXTTYPE_KEY; 
dataBuf.keySpecs[1].type = INTEGER; 
/* create the file */ 
strcpy((BTI_CHAR *)keyBuf, 
"c:\\sample\\sample2.mkd"); 
dataLen = sizeof(dataBuf); 
status = BTRV(B_CREATE, posBlock, &dataBuf, 
&dataLen, keyBuf, 0); 

Create Index Operation

If you create files with pre-defined keys, the indexes are populated with each insert, update or delete. This is necessary for most database files. However, there is a class of database files that are fully populated before being read. These include temporary sort files and fully populated files that are delivered as part of a product.

For these files, it may be faster to build the keys with Create Index (31) after the records are written. The file should be created with no index defined so that inserting records can be accomplished faster. Then the Create Index operation will sort the keys and build the indexes in a more efficient manner.

Indexes created this way are also more efficient and provide faster access. There is no need for the MicroKernel to leave empty space at the end of each page during a Create Index (31) because the index pages are loaded in key order. Each page is filled to nearly 100%. In contrast, when an Insert (2) or Update (3) operation fills an index page, the page is split in half, with half of the records being copied to a new page. This process causes the average key page to be about 50 to 65 percent full. If index balancing is used, it may be 65 to 75 percent full.

Another advantage of the Create Index (31) operation is that all the new key pages created are located close together at the end of the file. For large files, this means less distance for the read head to cover between reads.

This technique may not be faster when files are small, such as a few thousand records. The file would also need larger key fields to benefit. Moreover, if all key pages can fit into the Btrieve cache, this method shows no improvement in speed. But if only a small percentage of the key pages are in cache at any one time, this method saves a lot of extra key page writes. A file containing a million records can be built in hours instead of days with this technique. The greater the number of key pages in the file, the faster it is to build indexes with Create Index (31) than it is one record insert at a time.

In summary, the critical thing to avoid in loading a Btrieve file from scratch is not enough cache memory to hold all the key pages. If this is the case, use Create (14) to create the file without indexes and use Create Index (31) when all the data records have been inserted.

See Pervasive.SQL API Programmer's Reference for detailed information on these operations.