Overview of the Transactional Interface

The Btrieve API is a low-level interface to the transactional database engine that embodies functional aspects of the database design that might be otherwise transparent in higher-level interfaces such as SQL, Java, or ODBC. For example, the SQL interface operates independently of how the data is physically stored. However, the transactional interface developer must consider lower level aspects such as page size, physical and logical currency, type verification, and data validation. Despite these low-level considerations, the Btrieve API provides excellent flexibility and control over the data.

The transactional database engine stores information in files, which can be up to 256 GB in size for Pervasive PSQL 9.5 and later, (128 GB for earlier 9.x versions, and 64 GB for other earlier versions). Inside each data file are records, which contain bytes of data. A file can contain up to 4 billion records.

The data in a record might represent an employee’s name, ID, address, phone number, rate of pay, and so on. However, the transactional database engine interprets a record only as a collection of bytes; it does not recognize logically discrete pieces of information within a record. To the transactional database engine, a last name, first name, employee ID, and so on do not exist inside a record.

The only discrete portions of information that the transactional interface recognizes in a record are keys. Keys provide both fast, direct access to records and a means of sorting records by key values. Because the transactional interface has no way of knowing the structure of the records in each file, you define each key by identifying the following:

•

Number. This is the key’s order in the list of keys. Version 6.0 and later files can have gaps between key numbers. That is, the transactional database engine does not require keys to be numbered consecutively. When you add a key, you can specify a key number or let the transactional interface assign the lowest available key number. When you drop a key, you can leave the remaining key numbers as is or let the transactional interface renumber them consecutively.

•

Position. This is the key’s offset in bytes from the beginning of the record.

•

Length. This is the number of bytes to use for the key.

•

Type. This is the key’s data type.

•

Attributes. These provide additional information about how you want the transactional interface to handle the key values. The transactional interface supports these key attributes: segmentation, duplicatability, modifiability, sort order, case sensitivity, alternate collating sequence, and null value.

You can create or drop keys at any time. For each key defined in a data file, the transactional interface builds an index. The index is stored inside the data file itself. The index maps each key value in the file to an offset in the actual data. Normally, when accessing or sorting data, the transactional interface does not search through all the records in the file. Instead, it searches the index and then manipulates only those records appropriate to the request.

You can create indexes when you create the data file, or any time thereafter. When you create a data file, you can define one or more keys for the transactional interface to use in building indexes.

You can also define external indexes after creating a file. An external index file is a standard data file that contains records sorted by the key you specify. Each record consists of the following:

•

A 4-byte address identifying the physical position of the record in the original data file

•

A key value

Positioning rules (guidelines governing which record is current, which is next, and so on) are the same, regardless of when you create an index.

If you create an index at the same time that you create the file, the transactional interface stores duplicate key values in the chronological order in which the records are inserted into the file. If you create an index for a file that already exists, the transactional interface stores duplicate key values in the physical order of the corresponding records in the file at the time the index is created. How the transactional interface stores duplicate key values in an index also depends on whether the key is linked-duplicatable or repeating-duplicatable. For more information, refer to Duplicatability.