Firebird (Interbase) EOModeler Plugin

To some extent, when you use an advanced framework like the Enterprise-Object-Framework from WebObjects, the database simply becomes a persistence layer. You write your software for EOF and it just uses the database to record data. EOF even takes care of transaction support for databases which don't have it. Today there are several good and free databases we can choose from. In fact, we really don't need to consider the commercial alternatives because they don't provide any added benefit.

This page will talk about the three main open source database alternatives: MySQL, Postgres, and Firebird. All three are good, but we give the nod to Firebird and explain why in as objective a manner as we possibly can.

MySQL - the commercial database killer

MySQL is THE most popular open source database. It runs on many operating systems, is easy to set up and administer, and can be very fast. I have happily used MySQL with WebObjects for several years. Apple provides built-in plugins and support for MySQL allowing you to easily "synchronize the schema" with your database anytime you want. The free "CocoaMySQL" client runs great on Mac OS X. It has a clean user interface, simple backup and restore capabilities, and even allows you double click on binary data in a column and view it (if it is image data like a jpeg or gif). I have successfully used MySQL to serve up true Unicode apps where users can save text in the language of their choice and it all just works.

So why am I moving to Firebird? MySQL recently changed its license to say that you must pay for support if you use any commercial tools in conjunction with your MySQL database. While I haven't heard of anyone being forced to pay license fees, it really isn't free anymore. Also "named constraints" aren't available in MySQL and foreign keys and transaction support are "sort of" available only recently. The "ISAM" format is what traditional MySQL databases run on. It is very fast but doesn't have foreign keys nor transactions. This is the format I have been using for some time. With version 5 of MySQL, they licensed the "InnoDB" format which now gives MySQL true foreign key and transaction support but performance drops considerably. "ISAM" is fast but "InnoDB" is slow.

But wait! the story gets better! "SleepyCat" is the name of the company who created the new "InnoDB" technology. Oracle recently acquired the company in what many deem as an attempt to disrupt the growth of MySQL. When the license renewal time comes up, it is unclear how much Oracle will charge the makers of MySQL to continue using the technology. To counter this, MySQL has hired the "Jim Starkey" as their lead architect. Starkey is one of the key innovators behind FirebirdSQL, so speculation is running wild as to what this may mean - perhaps a flavor of Firebird driving future versions of MySQL.

Postgres - the full featured database

The primary goal of Postgres, from day one, has been to provide as full featured a database as possible with a high level of standards compliance. Early versions had transactions and foreign key constraints, but were slow. For years, Postgres was only happy on a unix box. With the recent release of version 8, Postgres will now happily run on a Microsoft Windows PC. Version 8 is not only full featured, but is very fast. It's a big database, with many settings, and needs to be maintained often with a VACUUM process put in an automated cron job.

From a WebObjects and Mac perspective, Postgres is a solid choice. The open source client "pgAdmin3" runs great on Mac OS X and even helps you set up the VACUUM process to keep Postgres running at its maximum performance. The source code for version 8 does not build a double clickable Mac installer, but a number of kind souls have created simple installers for Mac OS X. The JDBC adaptor does not auto-generate primary keys for WebObjects enterprise objects. There are plugins from Project WOnder and from Hexdreams that use sequences to give you auto-generated primary key support. Neither plugin supports "schema synchronization" which is a must-have feature in my book.

Firebird - the best kept secret

When most people hear "Firebird", they are more likely to think of the web browser "Firefox" which was originally called "Firebird". So why did "Firebird" become "Firefox"? Because the database of the same name was there first. "FirebirdSQL" is actually a branch of the "Interbase" version 6 codebase when it became open source. Interbase has been around for over 20 years and was the first to use BLOBs. It was also used in the M1 Abrams tank because every time the canon fired, all onboard systems would shut off, and Interbase was found to be the fastest to reboot with the least chance of corruption.

When the Firebird project started, many of the Interbase engineers, including Jim Starkey, left to join in the fun. Today, many people mention Firebird and Postgres in the same breath. They both are very standards compliant and support a large set of features. Firebird has had an easy install path on Microsoft Windows for a while, so there is strong .NET and Delphi support. Firebird is the most popular, full featured, database in the PC world. In the unix world, Postgres is the most popular because it has been open source longer. Still, Firebird is happy on unix and even happier on Mac OS X. The default build process from source code will actually build a double clickable OS X installer. How nice!

Firebird is tiny, weighing in at just a couple of megabytes. It is very lean but full featured, I don't know how they do it. It works well as a traditional database server, but can also be embedded in desktop apps for special purpose use. The source code will build Firebird in one of two flavors. The first is "Classic Server" or "CS" for short. This version runs a new process for each user connected to the database (but they share the same DB file on disk). Imagine Microsoft Word being launched five times to work on five different documents and you'll get the idea. The second choice is "Shared Server" or "SS" for short. This is for more advanced operating systems as it offers a single multi-threaded app instance that channels disk access. Think of launching Microsoft Word once but working on 5 documents at once and you'll see the difference. The "Shared Server" is the preferred choice for most uses but both work fine.

"FlameRobin" is the name of the SQL client that runs well on Mac OS X. It has a simple interface that works really well and makes running transactions through raw SQL commands easy. I really love that because most SQL clients I've used make me go hunting to figure out how to invoke transactions. The JDBC driver is called "Jaybird" and it will automatically create the EO_PK table if it is not there and auto-generate primary keys for your WebObjects enterprise objects. No special plugins are necessary. I created a plugin so that we could have intelligently named foreign and primary key constraints as well as a working "synchronize schema" command in EOModeler. Hooray!

Evidently, Firebird is very extensible. There is a fork named "Fyracle" that emulates Oracle, complete with data-types and PL/SQL support. It is good enough at this trick that it even runs the open source content management system named "Compiere" which would only run on Oracle.

Installing Firebird on Mac OS X

The best reference for compiling and using Firebird is David Pugh's excellent notes. That link is to a PDF of Google's cache because David's website appears to be no longer active. It's worth reading just to get an understanding but you should just install from the double clickable installer Firebird-SS-1.5.3.dmg

Next, download our Firebird plugins for WebObjects here: FirebirdPluginV1.zip. Both the source code and precompiled frameworks are included. Go into the "bin" directory and you'll find two frameworks: FirebirdPlugInBundle.EOMplugin, and FirebirdPlugin.framework.

Place "FirebirdPlugInBundle.EOMplugin" in "/Developer/EOMBundles/". This is a java-objective-C bridge framework that allows you to have intelligently named constraints and "schema synchronization."

Place "FirebirdPlugin.framework" in "/Library/Frameworks/". You will need to include this framework in your WebObjects projects. It is a dummy framework but needs to be there because by specifying a plugin in EOModeler, it must find one or else WebObjects won't communicate with your database. This framework doesn't do much else than extend from Apple's JDBCAdaptor.

Here is what the connection dictionary should look like (note: case is sensitive):
url -> jdbc:firebirdsql:hostname:/path/to/database/file/test.fdb
driver -> org.firebirdsql.jdbc.FBDriver
plugin -> FirebirdPlugIn

The Way To WO

The beauty of the "Synchronize Schema" command is that it does *not* destroy data. It preserves it. The only kind of data that might possibly be lost is relationships (like if you deleted foreign key attributes in your schema). Even then, you are given the choice of leaving that column intact or deleting it.

When you have a good EOModel, the app will practically write itself. The way to WO is as follows: