Transactional Architectural Overview

---

The following diagram illustrates the basic architectural components of the Pervasive.SQL transactional interface.

 
Figure 5-4 Pervasive.SQL Transactional Architecture

The transactional architecture can accommodate a variety of configurations, from single user use to client/server. The transactional engine also provides a set of utilities that help you install and manage the software.

Fundamentals

This section introduces fundamental concepts of the transactional architecture. For more information, refer to the Pervasive.SQL Programmer's Guide available in the Pervasive.SQL SDK. This product provides a transactional interface for MicroKernel data files, which contain the actual information that an application stores, retrieves, or modifies. Data files consist of a series of pages. Because data files can be quite large (up to 64 GB), it is not practical or always possible to transfer such a huge file between memory and the disk; therefore, data files must be divided into units called pages.

Data File Components

As discussed in MicroKernel Database Engine , data files consist of records, keys, and indexes. A record can be thought of as a row of data in the data file. A key is a particular field in the record that can be used to sort the data. Each key contains one or more indexes that enable the MicroKernel to determine the physical location of the desired data. The relationship between these file elements is illustrated below.

 
Figure 5-5 Data File Components

The above diagram shows three records from the sample database. These records belong to a data file that contains information about the different departments. Each record contains the department name, phone number, building name, room number, and department head ID.

In this example, the department name is a key. The records are sorted alphabetically by department name. The key contains an index that holds pointers to the physical location of each record in the data file. These pointers enable the MicroKernel to find information.

Enhanced B-trees

Pervasive.SQL transactional engine uses a hierarchical index type called a binary tree, or b-tree. The Pervasive.SQL b-tree is an enhanced, multi-level, or tree-structured, index. Because the MicroKernel searches indexes for information, a single-level index could, depending on the size, take some time to scan. By creating multiple layers, however, this process can be shortened considerably and become much more efficient.

A multi-level index is essentially an index structure containing one or more indexes. The sub-indexes do not contain all of the record entries for the file; if they did, they would be as large as the root (main) index itself. Instead, each sub-index contains a different set of the available entries.

Each index is a node of a b-tree. These nodes provide a search path for the MicroKernel. As the MicroKernel searches for a piece of data, it evaluates the search query at each node; essentially, the node is a fork in the road, so to speak, and the MicroKernel determines whether the information it needs is to the left or to the right. The search proceeds in this manner until the MicroKernel locates the desired record or information. Following is a simplified diagram of a b-tree.

 
Figure 5-6 Btrieve Indexes (B-trees)

The MicroKernel begins at the root index and evaluates which path to take in search of the data. It may look for a physical record number, such as 78. If node 1 contains data for record numbers 41-50, the MicroKernel knows it need to look to the right, where it finds the next node containing numbers 61-70. Again the MicroKernel follows the path to the right and finds the node it requires, where number 78 is stored.