Pervasive Software has released version 7.5 of its ubiquitous database engine and software development kit. I got the chance to take a look at the database and the various tools using an evaluation copy of the software for Windows NT 4.0.
Installation and Configuration
I installed the software from the PervasiveSQL 2000 SDK CD-ROM. Pervasive automatically searches for the presence of a working copy of their workgroup database engine when you install the SDK kit. If you don't have a running copy of PervasiveSQL, the SDK will install the database server kit first. The install routine for the database server gives you the choice of accepting a typical installation or selecting a custom install option. Selecting the custom install for the database server appears to make no difference; the installation program seemed to go ahead and install all the options anyway. All in all the installation went smoothly. After the installation completes, the server runs a small test to ensure that everything was installed properly.
Once the database is running, the SDK kit can be installed. Selecting the custom option for the SDK gives you a number of choices. PervasiveSQL 2000 provides a comprehensive set of tools for interfacing with the database, such as plug-ins for Inprise, Microsoft Visual Studio and Java. The database server is available for Windows NT and Novell NetWare, and Pervasive has announced versions for Solaris and Linux. Pervasive even makes a version of the engine available for various smart-card environments.
The database server uses a small footprint and starts up easily and quickly as an NT service. The underlying technique that serves as the foundation for PervasiveSQL 2000 is an engine that Pervasive calls the MicroKernel Database Engine (MKDE).
The MKDE provides a core layer for all low-level processing, such as index management, caching, transaction control and lock management. The PervasiveSQL engine is built on top of the MKDE with a transaction layer based on Pervasive's Btrieve architecture, and a relational layer based completely on ODBC. Pervasive's SQL interface is abstracted from the MicroKernel by a layer that's called the SQL Relational Database Engine (SRDE) (see Figure 1).
Part of Pervasive's differentiation from the other database players in the marketplace is their support of relational and transactional interfaces based on their Btrieve layer. Pervasive's relational interface fully supports the ODBC standard (and by extension JDBC), and programming against the MKDE using ODBC is easy and quick. Pervasive has essentially built its relational database-access layer around the ODBC standard, making it a straightforward process for embedded developers to work with the relational layer. Although you're free to develop all your applications using the relational layer, the folks at Pervasive make no bones about the fact that one of its advantages is the ability to use the transactional interfaces to access the database at a much lower level. On their Web site, Pervasive provides a detailed description of their product in a paper called "Pervasive Products and Services," where they describe what they believe to be some of the major benefits of the transactional interface, e.g., speed, data integrity and scalability. Although the original Btrieve API is over 15 years old, it continues to evolve. Thus it supports modern RDBMS concepts such as transaction support and on-line backup, which are more commonly identified with relational database engines. A single "logical" database can be accessed by the lower-level transactional APIs as well as by ODBC and SQL. The Pervasive Control Center provides you with tools to add the necessary data dictionary information to a set of Btrieve files that form a logical database. If you wish to access the database using the transaction interface, they provide a suite of ActiveX controls for this purpose, as well as interfaces for Visual Basic, Delphi and C++.
PervasiveSQL also provides an interface from Java that's based on JDBC, but they've added some extensions to account for the fact that JDBC is based upon SQL, which isn't a requirement for the Btrieve API layer. This allows a Java program to access the Btrieve API directly, without relying on column information stored in the relational layer. Current Btrieve programmers will find that the Java interface hides many of the implementation details when compared with the older non-Java API.
Pervasive SQL and Java
I found the Java interface reasonably easy to use. Pervasive provides documentation for working with their sample applications in either Inprise's JBuilder or Symantec's Visual Cafe. However, I was able to work with the sample projects using my copy of Oracle JDeveloper. Pervasive makes use of the factory concept for their Java API, as shown in Figure 2.
There are class objects for the major tasks you'll need to undertake in order to work with your PervasiveSQL database and Java. What makes Pervasive different from working directly with JDBC is you can also access the data using the transactional API. If you have a logical database that includes a relational data dictionary layer, you can use the DATABASE class to work with the metadata, as in a classical relational design. However, you're not required to use this interface in order to access the Btrieve layer. The Java API will allow you to work directly with data files in a loosely coupled database. The standard installation of Pervasive includes a number of sample databases and programs for you to work with. As usual, I bypassed the samples and went directly to creating my own database using the Pervasive Control Center, as shown in Figure 3.
The control center provides a one-stop-shop for accessing all the major tools and interfaces for creating and managing Pervasive databases. There are lots of hidden gems within the various tools that are shown in the outline control on the left-hand panel of the control center. I was easily able to create a new database with a relational layer using the ODBC interface from the control center. I modified a few existing table-and-load scripts and used them with the SQL Query window to create a new table and populate it with data. The control center provides a classic text display as well as a grid display for records, as shown in the panel on the right-hand side of Figure 3. Pervasive provides plenty of tools and utilities for working with the server engine as well as the databases managed by the server. Pervasive's API supposedly allows you to control and tune your databases as well as access records, and I suspect that the development team made use of this API when constructing the various utilities that are called by the control center. There are an abundance of tools, but the user interface between them is somewhat inconsistent. Many of the utilities provide for making detailed modifications at the lowest levels of the database, and the utility interface expects you to know what you're doing. For example, the Maintenance utility gave me low-level access to the UGRADS file that I created inside my new database. However, the utility assumed that I knew what I wanted to do to the file, and there were no wizards to guide me through the modification process.
Once my database was created, it was a relatively simple process to use the Java API to access my UGRADS table through a Java program (although I'll admit that I stuck to using the relational layer with the DATABASE class).
The Pervasive Software Web site offers an Aberdeen research paper that compares the total cost of ownership of PervasiveSQL to other database engines on the marketplace. Given the fact that you can control the entire database and server through the API interface, I believe you can build an application and database that's easy to maintain and manage. If you plan on embedding a database inside an application and are willing to control the database in this manner, Pervasive offers a compelling product. Pervasive will appeal to the developer who wants to exert a fine degree of control over the data while still allowing end users to access the underlying data using friendly SQL interfaces over ODBC.
Jim Milberry is a software consultant with Kuromaku Partners LLC. He has over 15 years of experience in application development and relational databases. Jim can be reached at [email protected], or via the company Web site at www.kuromaku.com.
He can be reached at: [email protected]