Things that bother me a lot in Firebird SQL

After I received a lot of heat about my post regarding why I find MySQL to be so bad (part I) – (mostly behind the scenes), I decided to also write something about Firebird SQL.

The first time I worked with Interbase was back in 2002, when I started working in a new work place where I needed to get into an existing Delphi + PHP code. The main database engine there was Interbase 6.1 and I have upgraded it to 6.5. All that was running on RedHat 7.3 Linux server, and a Windows 2000 desktops. Naturally, the database and Apache+PHP ran on Linux.

Interbase was the first database I had experience with that I actually felt was serious enough to work with. However, we had a lot of problems with it. There was no usage of RAID or shadow databases to the working copy of Interbase, and the hard-drive that was in use, had bad sectors, where some of the database data actually existed. So the first thing I have learned about Interbase was how to recover data rather then how to actually work with it.

Since then, I always monitored and tested the Firebird database – the main open source development off-shoot of Interbase 6.1.

After several problems with MySQL databases on production servers (costing money to my clients due to documented problems with the database), I started looking for an alternative for the development of my tools – in cases where I can decide what is the database that is involved (usually when the customer does not care, as long as it works). So I'm playing again with few "demo" apps that I write using Firebird SQL, and I really enjoy working on it.

That's my foreword about Firebird/Interbase (because they are not that familiar to people).

Now to my original intention:

In the short run, triggers, stored procedures, views etc. really help me to create some sort of API to use in the database, and to make my life easier. However, they have their down sides, too. Updating database logic can be painful, and the more features you use the harder it gets. Therefore, I think Firebird should have some way to ease the processes of upgrading things without destroying or triggering things that should not run (like triggers and stored procedures that will be executed by the upgrade and in turn will change data that should not be changed by upgrading). Maybe an "upgrade" mode to the database that can disable every trigger and stored procedure from running on "its own", unless it was configured to continue working. But we should really think it much further before providing a solution for such a problem.

The most major problem I find with Firebird is the lack of official documentation and examples. Firebird have an approach of distributing resources such as documentation, examples etc. IBPhonix is a great web site, but not everything is present there, as some exists under firebirdsql.org and some located under the web sites of the Firebird developers. I think everything should be under one roof including examples on everything (maybe us – the community can help by providing examples for things).

Another big problem with Firebird is the legacy of Interbase's Unix origin that have a very un-modern Unix approach for installation and work. Every Linux distribution that I worked with, pack Firebird differently, on different paths renaming isql differently (some keep it as-is, but in order not to make any race condition with ODBC, it just place it under the bin directory inside the Firebird /usr/share or /opt/firebird/bin directory). If I had time, this is the first contribution that I think I would do for Firebird -> make it more Unix/Linux friendly installation and easier for distribution.

Another very bothering issue is the UDF or lack of them. There are many open source UDF for Firebird with common extensions, however Firebird does not seem to implement them as built in methods or supply their own basic UDF with the most used/needed functions, so I don't find it a good way to work. It's not that Firebird should have "everything"™ inside, but common tools should arrive by default with the database, at least in my opinion.

The Firebird database has roots since the 80's when Interbase was created. While technology such as SMP are work in progress, tools such as clustering and other popular and used tools are not in sight. I think it's time to see a more clear path for Firebird. At least I can not see one other then the merges of other Interbase-based projects.

It seems like every major framework is passing on Firebird and is ignoring its existence. You see tools such as Ruby On Rails and Django that do not contain support for it by default, and it actually hurts to see how they ignore it. But it also says a lot about Firebird and how the bigger open source community (does not) see it in their eyes.

Last but not least: Flamerobin. It's a great tool, but it lacks of build-in help, and some more GUI tools to create tables, triggers etc.. It's not always easy to figure out that the SQL syntax I'm writing actually belongs to a different database (such as MySQL), and it can drive people crazy (or just strengthen what is already there 😉 ). I think/hope an easier way to write queries will make life easier. Flamerobin however is still a work in progress tool that does introduce new features all of the time, so these are two points that I hope will be taken in consideration.

I have more things I find bothering about Firebird, but I think we should first focus on the above points first in order to get started.