DBISAM, a BDE Alternative
By Michael Burton - Rimrock Software - firstname.lastname@example.org
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
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 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).
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.
|Product||DBISAM Database System|
|Description||A BDE replacement database with a small footprint|
|Supports Delphi||1, 2, 3, 4, 5|
|Supports C++ Builder||3, 4|
|Database File Format||Proprietary|
|Price||$199 - Free evaluation version available|
|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|
|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 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.
|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|
|Low Level Tasks||Provides properties, methods and events to support file restructuring, batch progress information and copying tables|
|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.|
|RIGHT JOIN||Both INNER and OUTER|
|LEFT JOIN||Both INNER and OUTER|
|FULL OUTER JOIN||Not supported|
|IN||Constant expressions only|
|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|
|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