NexusDB

By Bill Todd
(also published in several print media)

I had never heard of NexusDB until I saw it mentioned on the Borland newsgroups. My first reaction was, who needs another database? If you want a fast answer to that question just look at the feature list at http://www.nexusdb.com/support/index.php?q=node/509.

Although NexusDB descends from TurboPower’s venerable FlashFiler, which provides a proven foundation, the new version of NexusDB is the result of a major rewrite that adds a new modular extensible architecture and a host of new features. Who needs another database? If you need a small fast SQL 2003 compliant database that offers both server and embedded engines, transaction control, stored procedures, functions, triggers, views, encryption, on-line backup and truly impressive performance over low speed connections the answer is, you do.

The Basics

NexusDB is multi-threaded, supports SMP and hyperthreading, and includes both a database server for multi-user environments and an embedded engine for single user applications. The embedded engine is truly embedded. There are no DLLs to deploy. Everything is compiled into your EXE. To use the embedded engine just drop three components on your main form or data module. If you need to convert your application to client/server just remove the three components and change the database alias to point to the server. There is nothing to change in your code because the embedded engine is the full NexusDB server engine. The embedded engine’s only restriction is that it places an exclusive lock on any database it opens so that only one user can access that database.

If you want the best of both worlds you can use the embedded engine and connect to one or more servers in the same application. If you have users connecting over a low speed WAN you can download all of your lookup tables that change infrequently to the local database at startup so searching these tables will not require any network traffic. This also lets you create the ideal environment for briefcase model applications. With the same database running on both the server and the local PC it is easy to let a traveling user connect to the network, download needed data to the local database, disconnect from the network and hit the road. While traveling the user can insert, update and delete records. Using triggers the local database can log all changes. When the user connects to the server again you can use this change log to update the server database.

NexusDB provides both navigational and SQL data access engines. The embedded navigational engine adds 880 kilobytes to the size of your EXE. This is remarkably small for a full featured relational database. If you need SQL access to data the optional TnxSQLEngine component adds another 620 kb bringing the full embedded engine to 1.5 megabytes.

NexusDB’s navigational engine makes it an ideal choice if you need to convert applications that use Paradox or dBase tables to a database server. NexusDB includes a suite of components designed to replace their BDE counterparts with minimal changes to your code. TnxTable gives you the same high performance that Ttable provides with Paradox and dBase tables and query performance should be significantly better.

NexusDB adds a suite of components to the Delphi palette. In addition to the embedded engine components there is a full suite of TdataSet descendent components that make converting applications that use other databases easy. There are also components for managing the communications link, implementing over the wire encryption, backing up the database and administering the server.

Transactions and Concurrency

NexusDB provides true transaction control including nested transactions and transactions that span multiple databases on the same server. You can ignore transactions if you wish. If you do not explicitly start and end transactions NexusDB starts and commits a transaction for each change to the database. If you use the TnxTable component for navigational access to data these automatic transactions use read committed transaction isolation.

If you run a SELECT statement without explicitly starting a transaction NexusDB uses snapshot transaction isolation. Snapshot isolation provides a stable consistent view of the entire database as it stood at the instant the snapshot transaction started. This not only guarantees that every SELECT returns a logically consistent set of records but also that you will get the same values for any record no matter how many times you reread it during the transaction. Best of all, snapshot isolation does not use locks so other transactions are free to insert, delete and update the records you are reading.

NexusDB implements snapshot isolation in its buffer manager. When a database page is updated while a snapshot transaction is active the original page is retained in the buffer. When a snapshot transaction reads a row it always reads the original page. This scheme works even if the SELECT runs for a long time and there is a high volume of changes because the buffer manager will overflow to disk if it runs out of memory to store original page versions.

If you explicitly start a transaction in code you have your choice of serializable or snapshot transaction isolation. Because snapshot isolation is read only the default is serializable. Serializable isolation ensures that once your transaction reads a row you will get the same value no matter how many times you reread that row during the life of the transaction. NexusDB implements serializable isolation by placing a table lock on each table the first time the table is accessed.

Locking at the table level means that once a serializable transaction has accessed a table no other transaction can update any row in that table until the serializable transaction ends. This means that it is important to keep serializable transactions as short as possible. If a transaction holds a table lock and another transaction needs to update a row in that table the second transaction will be queued and will wait until the transaction holding the lock ends. By queuing transactions NexusDB ensures maximum throughput with minimum conflict errors. To stop a long running transaction from causing other transactions to wait in the queue you can set a maximum transactions wait time.

Although NexusDB manages table locks efficiently they are still a potential bottleneck at high transaction volumes when the transaction mix includes a high percentage of inserts, updates and deletes. Multiple threads and SMP support do not help when many of the threads need to update rows in the same table and must stand in line to make their changes serially.

Communications and Security

I cannot think of any communications or security feature you might want that NexusDB does not have. NexusDB supports TCP/IP, TCP/IP version 4, named pipes, and shared memory transports between the client and server. For low speed connections you can enable over-the-wire compression. If you need secure communication you can use Blowfish encryption with either TCP/IP or named pipes. If you don’t like Blowfish you can add your own encryption module. NexuxDB comes with a collection of sample applications that shows how to implement over-the-wire encryption using Blowfish.

You can encrypt data in your database to prevent unauthorized access. Granting the appropriate combination of read, write and admin rights controls each user’s access to data.

SQL Implementation

NexusDB implements almost all of the SQL 2003 core features and many extensions. SQL DML features include left, right and full outer joins, JOIN USING, NATURAL JOIN, the ability to start, commit and rollback transactions and a very good library of built-in functions. It even implements the ASSERT TABLE statement to compare the structure and contents of two tables.

NexusDB’s DDL includes everything you would expect including declarative referential integrity, stored procedures, functions, views, triggers and roles. The procedure and trigger language includes branching and looping constructs, the SIGNAL predicate to raise exceptions and structured exception handling with the try/catch statement. If you need temporary storage, NexusDB provides both local and global temporary tables as well as in-memory tables.

Triggers can fire either before or after insert, update or delete. A single trigger can be attached to more than one event. In the body of the trigger the INSERTING, UPDATING and DELETING functions tell you which event caused the trigger to execute. Both old and new column values are available in the trigger body.

Data Types

In addition to optimized blob support with full text indexing and blob caching NexusDB provides a rich set of data types. Integers include signed and unsigned 8, 16 and 32 bit and signed 64 bit. CHAR, VARCHAR and text blobs provide string storage. The FLOAT, DOUBLE PRECISION and EXTENDED types provided single precision, double precision and extended floating point storage. NexusDB also provides the SQL DATE, TIME and TIMESTAMP types and the BOOLEAN type.

The only limitation in NexusDB data types is that the NUMERIC, DECIMAL and MONEY types all map to a scaled 64 bit integer with a fixed precision of 20 and a fixed scale of four. If you need to represent real numbers with a scale larger than four there is no way to avoid floating point imprecision since the floating point types are your only option.

Client Connectivity

For Delphi users NexusDB includes a complete set of TDataSet descendents that make the transition to NexusDB a snap. If you have been working with dbExpress or DataSnap all you need is the NexusDB dbExpress driver.

For .NET developers there is Delphi IDE integration and Visual Studio .Net integration, .NET direct data access classes, an ADO.NET provider and also provides an ODBC driver, COM based direct cursor access and a PHP interface.

Tools and Utilities

NexusDB’s two principal utilities are the Enterprise Manager and the Data Importer. On startup Enterprise Manager shows all available servers. You can connect to as many servers as you wish. Enterprise Manager also has an embedded server so you can work with a database without connecting to an external server.

The well-designed graphical environment makes it easy to create and modify tables, define referential integrity graphically, create stored procedures, triggers and views, add check constraints and backup and restore your database. Right clicking a table lets you view and edit the data, execute SQL, delete or rename the table, clone the table’s structure, re-index or pack the table, enter or change the table’s password, recover deleted records, empty the table and reset the value of an auto-incrementing field.

The Importer in figure 4 imports data from Flash Filer, ADO, DAO and BDE data sources. CSV and fixed length ASCII importing and exporting is built into Enterprise Manager. Between the two you can easily bring data from almost any data source into NexusDB.

Documentation

The on-line documentation is very good but. The documentation is clear and well written and complete enough to make getting started with NexusDB easy.

NexusDB ships with full source code. If all else fails the source is the ultimate reference for the properties methods and events of the components.

Modular Architecture

The most intriguing aspect of NexusDB is it sub-engine architecture. NexusDB is built from a set of modules that communicate through interfaces. This means that additional sub-engines can be added without having to change any other code.

A good example is the record sub-engine that is responsible for reading and writing table records. NexusDB ships with two record sub-engines. One handles fixed length records while the other uses variable length records. When you define a table you choose the sub-engine that table will use. If you want maximum performance at the expense of disk space use the fixed length sub-engine. For better storage efficiency at the expense of some performance choose the variable length sub-engine. With this highly extensible architecture I expect to see a steady flow of new features and enhancements.

Conclusion

NexusDB is a great choice for anyone who needs to develop client/server and embedded database applications. It stands head and shoulders above its competition with its ability to connect to its embedded engine and one or more remote servers at the same time from the same application and with SQL 2003 support, on-line backup, true transaction control in both the server and embedded engines, and SQL stored procedures and triggers.

Support for temporary tables and for read committed, snapshot and serializable transaction isolation make writing applications that perform complex data analysis while data is being updated easy. With its modular extensible architecture this is a great database that can only get better.

Bill Todd is President of The Database Group, Inc., a database consulting and development firm based near Phoenix. He is co-author of four books and over 100 articles on database programming and is a member of Team Borland, providing technical support on the Borland Internet newsgroups. He has presented over two dozen papers at Borland Developer Conferences in the U.S. and Europe. Bill is also a nationally known trainer and has taught database and programming classes across the country and overseas. Bill can be reached at contact_at_dbginc_dot_com.