hur.cn - 华软源码

 热门搜索
The Unofficial Newsletter of Delphi Users - by Robert Vivrette


DBISAM, a BDE Alternative

By Michael Burton - Rimrock Software - rimrock@rimrocksoftware.com

Overview

Borland's Database Engine (BDE) allows Delphi and C++ Builder programmers to easily create database applications. There are drawbacks to the BDE, however. It is big and makes application deployment hard. It natively supports older database types such as dBase and Paradox, which are not always adequate or efficient. And the BDE has its share of problems, too.

Elevate Software's DBISAM is a replacement for the BDE. DBISAM is compiled directly into an application (or can be distributed with the application as a DPL file), which eliminates the large, clumsy BDE installation. DBISAM also uses its own proprietary database format, which removes the need for licensing requirements with BDE alternatives that support Btrieve or Microsoft databases.

The DBISAM database structure is proprietary. Applying the experience of those databases that came before it, DBISAM borrows the best ideas from all of them. DBISAM's file structure is not monolithic like Microsoft's or Interbase's. Each database table is stored in two or 3 files. Data is stored in a DAT file, all indexes are stored in an IDX file and BLOBs (if present) are stored in a BLB file. Since all databases may eventually become corrupted, these tables can be repaired using either the database administration utility or the method RepairTable, which is built into TDBISAMTable.

DBISAM comes with a very nice administration utility called DBSYS. DBSYS can create, restructure, copy, rename and even empty database tables. It can also search for, examine and edit data and repair corrupted tables. It even lets you write, test, load and save SQL statements. DBSYS is shown in figure 1.


Figure 1. DBSYS - Database System Utility

The DBSYS utility also has a very nice feature called 'Reverse Engineering', which creates a file with the source code necessary to create your database tables. Empty database tables no longer need to be included in the application's setup. The reverse engineering code can also be used in combination with the built-in database table version information to provide a method for updating tables for new versions of your application.

Since DBISAM uses its own proprietary database format, it is useful to have a utility that can convert older database formats to the DBISAM format. The DBISAM utility BDETRAN provides users with this capability. BDETRAN is shown in figure 2.


Figure 2. BDETRAN- BDE Database Transfer Utility

Elevate Software is very honest about the strengths and the weaknesses of their product. This honesty even extends to bugs in the database engine. DBISAM comes with a Customer Support Utility called DBCUST that allows you to view the list of bugs submitted by Elevate's customers, and even submit bugs of your own. DBCUST (see figure 4) is actually a working DBISAM database application.


Figure 3. Trial Version Survey Utility

Elevate Software's concern for the quality of their product even extends to those who are not their customers. The trial version of DBISAM comes with a utility called DBTRIAL (see figure 3) that allows you to send your evaluation of DBISAM to Elevate Software. I am impressed with their thoroughness in this area.


Figure 4. Customer Support Utility

Using DBISAM

DBISAM is installed into Delphi by registering four new VCL components: TDBISAMSession, TDBISAMDatabase, TDBISAMTable and TDBISAMQuery. To use DBISAM, you simply use these components in place of their normal BDE counterparts. Any data aware control that will hook up to TDataSource can then be used.

There are some differences between the DBISAM data components and the standard VCL data components, but those differences are either additions to the the components (like the RepairTable method), or they provide added functionality (like the extra parameters to the RestructureTable which define version information).

SQL is the major area where there are differences between the BDE and DBISAM. TDBISAMQuery understands a subset of SQL 92, while TQuery understands much more of the standard. Overall, if you want to move an application from BDE support to DBISAM support, the area where you may have problems is SQL. Hopefully, this will improve with newer versions of DBISAM. The current version of DBISAM cannot use stored procedures, cached updates, batch moves or referential integrity, which may also cause conversion problems.

DBISAM seems to be very stable and seems to perform very well. Navigation through tables seems fast, even when the tables contain multiple BLOB fields with large quantities of data in them. It would be interesting to see some formal benchmarks comparing DBISAM with other databases.

A real world example of a DBISAM application may be seen with freeVCS, a freeware Visual Control System available from Thomas Hensle at http://www.freevcs.de. The standalone version of freeVCS uses DBISAM as the basis of its file storage. It is hard to determine performance in this instance, though, since the database is implemented in a server application which talks to the main application through a network using TCP/IP. I have not seen any data loss or any problems with DBISAM usage in freeVCS.

DBISAM documentation consists entirely of help files. There is no printed material. Although the help files are fairly good, they are still incomplete. Installation was fairly easy with Delphi 5.

In-memory Tables

In-memory tables are a useful item that can greatly speed access to tables used for data lookup. Once you load the in-memory table with data, there is no more disk accesses, no matter how often you reference the table.

You can also use in-memory tables to handle a sticky user-interface problem. User data entry can be very slow, depending on the amount entered, and keeping a transaction open for the duration of the data entry should be greatly discouraged. An elegant solution to this problem is to load the detail records to be edited into in-memory tables, allow the user to edit those tables, and only write to disk tables when the transaction is committed.

You can do this with DBISAM in-memory tables, since they differ fundamentally from BDE in-memory tables. The BDE in-memory tables are cursor-based, and may be accessed by only one TTable at a time. DBISAM in-memory tables are identical to disk-based tables, and may be accessed by any methods applicable to disk-based tables (including TDBISAMQuery components).

Conclusions

DBISAM is a serious contender for a BDE replacement. It is fast and has a small footprint. It supports most of the database mechanisms in use by other database systems, and also supports importation of legacy databases. I do look forward to being able to use stored procedures and create tables and indexes using SQL, though.

Elevate Software's support is fast and thorough. They are not afraid of letting their customers see the warts along with the beauty, an attitude that other, much larger, software companies should emulate.

I will be using DBISAM for my future database projects. It is going to get even better than it is now.

 Summary
 
 
General Information
Product DBISAM Database System
Version Reviewed 1.21
Description A BDE replacement database with a small footprint
Vendor Elevate Software
Web Site http://www.elevatesoft.com
E-mail Address info@elevatesoft.com
Supports Delphi 1, 2, 3, 4, 5
Supports C++ Builder 3, 4
Database File Format Proprietary
Included Utilities
  • DBSYS - Database System Utility
  • BDETRAN - BDE Database Transfer Utility
  • DBCUST - Customer Support Utility
  • DBTRIAL - Trial Version Survey Utility
Price $199 - Free evaluation version available
Technical Specifications
Data Aware Component Support Support for all standard data aware components.
Number of Fields per Record 1024
Number of Indexes Primary - 1; Secondary - 30
Fields in Index Key Up to 16
Number of Open Data Files Only limited by available memory and open file handles.
Number of Records per Data File 100 million
Data File Size 2 gigabytes
Number of Blob Fields per Record 32
Decimal Places for BCD Fields 4
Number of Records in a Transaction Limited by available memory
Blob Block Size 64 bytes minimum; 64k bytes maximum
Blob Data Size Delphi 1 - 64k bytes; Others - limited by available memory
In-memory Data File Size Delphi 1 - 64k bytes; Others - limited by available memory
Index Key Size 1024 bytes
Record Size 64k bytes
String Field Size 250 bytes
Versioning Version information built into each table
Differences From BDE
Configuration No database engine configuration is necessary.
Aliases No Aliases are supported. For networked database applications, an Alias system can be created by the programmer.
BLOB Storage Separate BLOB file (unlike Paradox, which stores it in the data record)
Batch Moves Will be supported in version 2.0
Bulk Inserts/Deletes Will be supported in version 2.0
Cached Updates Not supported, but equivalent functionality can be accomplished using in-memory tables and transactions
Multi-user Concurrency Transparent to the programmer
Live Data Support Can be demanded by using StrictChangeDetection property
Deployment Compiled completely into the application, or can use a DPL file. Approximately 300k overhead added to the application.
Data Security Supports data file encryption with a password, but no user-based security mechanism (ala Paradox) is currently supported
Filter Optimization Filters are optimized to use available indexes, if possible
Data File Management All free space in data, index and blob files is transparently recycled
Record Deletion Marked as free and are immediately available for re-use
In-memory Tables
  • Unlike the BDE, in-memory tables can be used exactly as normal tables.

  •  

     
     
     

    In-memory tables can be streamed to or from any TStream descendant. This means you can create a sub-table that resides inside of a BLOB field in one table record.

Indexes
  • Required for each table.
  • All indexes are stored inside one IDX file, similar to CDX and MDX index formats.
  • Indexes can be compressed.
  • Allows descending primary and secondary indexes.
Partial Field Search Allowed, exactly like the BDE
Partial Field Range Allowed, exactly like the BDE
Record Retrieval Supports retrieval of logical record number based on currently active index
Record Count Allows instantaneous and exact record count, even if a range is currently set
International Support
  • Support for over 100 human language variations - can sort properly in any variation.
  • Can display date, time and numbers based on the human language settings for the data file.
Low Level Tasks Provides properties, methods and events to support file restructuring, batch progress information and copying tables
Memory Usage
  • No memory pre-allocation is done for data, index or blob buffers.
  • Memory for caching is allocated on an as-needed basis. Data record buffer max cache is 256k. Index page max cache is 128k. Blob block max cache is 256k.
NULL Support Includes complete NULL support identical to Paradox, but implemented in a safer and more thorough manner
Change Detection Always checks for changes by other users before any read or write. This can impact processes that read large numbers of records in one batch. A solution to this problem is to lock the data file before doing large batch reads, and to wrap many updates into a transaction when doing large batch writes.
Caching The BDE has a Local Share setting that determines whether writes are cached for shared data files. Setting the Local Share setting to False can lead to very disastrous results if the application is unexpectedly terminated since it can cause all updates to be lost forever. DBISAM, on the other hand, never caches writes for shared data files and always instructs the operating system to write all updates to disk after each and every update. If you wish to ensure that the operating system subsequently physically flushes the updates to disk you may use the FlushBuffers method or ForceBufferFlush property.
SQL Support
SELECT X
INSERT X
UPDATE X
DELETE X
HAVING Not supported
RIGHT JOIN Both INNER and OUTER
LEFT JOIN Both INNER and OUTER
FULL OUTER JOIN Not supported
CAST Not supported
IN Constant expressions only
ANY Not supported
EXISTS Not supported
DISTINCT 16 or less columns
ORDER BY 16 or less columns
GROUP BY 16 or less columns
INTO [MEMORY] (Non-BDE) Directs results to a data file
MEMORY (Non-BDE) For querying memory tables
WITH LOCKS (Non-BDE) Put a read lock on query tables
QBE Queries Not supported
Parameterized Queries Supported
Stored Procedures Not supported
Transaction Support No limits - as many records are allowed as will fit in available memory. Transactions are buffered, and are not written to disk until the transaction is committed, or are discarded if the transaction is rolled back.

Unsupported SQL-92 Language Elements