Openbase 9.1

Friday 24th February, 2006

For business, there are an increasing number of applications, not to mention web technologies, that provide end-user interface tools, but which require, or claim to work well with, professional back-end Structure Query Language (SQL) based systems (which generally means Oracle, SyBase or DB2). These system run on high-end, non-Mac servers. OpenBase is a cross-platform, SQL–92 server which will happily run on Mac OS X, Linux, and Windows 2000, XP, or Server 2003 and claims to provide that elusive mix - big iron robustness, and Mac ease of use.

One of the problems with other SQL server products (and yes, we’re talking to you MySQL) is that they’re hard to install and administer, or there’s no Mac-based administration tools, or you need to install a third-party product to do the work. OpenBase cracks this by providing a very Mac-like admin tool, displaying the currently running databases in a single window. The status of each database is clearly visible, and all administration tools (browse, setup, user manager, graphs, etc.) are available by clicking on tool-tipped icons. It will even open up a Terminal window connected to a database, so if you really want to use the command line, you can. Even here, there’s a nice GUI version, which makes writing OpenBase SQL queries easier, and provides access to fully delimited table names and store procedures from pop-up menus.

Creating a database is a simple affair too: make a new database, click on the start button, and then use the Schema Design tool to add tables and fields. Once you have a few tables, you can switch to the table view and build relationships between tables using drag-&-drop. When creating new tables, OpenBase automatically provides a primary key ‘_rowid’, and ‘_timestamp’ & ‘_version’ fields. Working with these fields, rather than creating your own Primary key fields makes life much simpler. When you link two tables using drag-&-drop, OpenBase automatically creates a parent ID field in the child table, named for the parent table. So dragging from an Invoice Items table to an Invoice table will create an invoice_id field in Invoice Items. With each relationship created, you can also specify the referential constraints for that relationship - does deleting an invoice delete all the invoice items? - and whether or not invoice item records can exist without a related invoice item.

The Test

These constraints are used to automatically enforce the database consistency. This is important for databases that need to pass the ACID test: Atomicity, Consistency, Isolation, and Durability. In database terms, atomicity, “the state or fact of being composed of indivisible units”, means that when you’re performing a set of operations as part of a transaction either they all succeed, or none of them do. It’s not enough to debit customer A’s account, you need to increment customer B’s account as well. If either operation fails, you should be able to roll everything back and restore the original balances.

To be consistent, the database needs to ensure that any operation leaves data in a state that complies with the database’s design rules. So, if a surname is required, you can’t insert records that don’t have a surname.

Suppose two users want to access the same account at the same time. Isolation ensures that every transaction has its own consistent view of the database, making it appear that each transaction happens either before or after all the other concurrent transactions. If that sounds a bit too Zen, think of it as record locking - only one person can edit a record at any one time. (Although it should be noted that there are alternative approaches to it than just record locking.) Lastly, and most importantly, Durability makes sure that when you issue a request that changes a database, it stays changed, even if the machine crashes then and there.

All OpenBase operations are of course multi-threaded. To get around any possible deadlock issues, OpenBase includes an avoidance mechanism that monitors transactions and where interdependent transactions compete for the same resources, rolls back one of them. And to round things off, OpenBase uses variable length records without pre-allocation - so your database files can expand if necessary, but are only as large as they need to be in practice. Thanks to extensive use of journaling files, and master and working copies of database tables - and unlike say MySQL - OpenBase passes this ACID test.

Team Player

To be any use of course, a back-end system needs to work with a variety of front ends. Take your pick: OpenBase supports APIs for Omnis, WebObjects, PHP, REALBasic, Cocoa, JDBC, Objective-C, C++, and ODBC. If your system needs to be deployed in a number of locations, or as a part of a server-farm for a database driven website, then the ability to cluster your databases is also an important high-end feature.

OpenBase makes it amazingly straightforward. When creating tables, you can specify the number of databases that will be part of the whole system. Primary keys are then allocated in interlaced ranges, so there are never any conflicts when servers are shut down, or when synchronising records between servers. All of which happens automatically.

Buying Advice

If you’re running your own web servers, or maintaining a database across a number of physical sites, then OpenBase is a strong contender that should definitely be considered before deciding on your back-end system.