Introduction to the Firebird Database
In the open-source world, much has been made about the need for a full-featured, robust and reliable database that can handle substantial loads and yet be able to play well with such open-source tools as Perl and PHP. The two main contenders are PostgreSQL and MySQL, but a new arrival in this arena is the Firebird RDBMS. It offers a substantial feature set, a mature code base and a proven track record in its previous incarnation as the Borland InterBase RDBMS. As discussed later in this article, Firebird provides a lot of the features available in commercial databases, including stored procedures, triggers, hot backups (backups while the database is running) and replication. As with many mature products, Firebird carries some amount of legacy baggage, which can be a bit confusing to a new user. So before we plunge into exploring the features this database provides, let us look at some common issues that may confront a new user.
Firebird originally started its life as the Borland InterBase database. As the product reached version 6.0, Borland decided Borland the product was going to be aged out, and so the code was released under an open-source license. Later on, however, Borland apparently had a change of mind about aging out the product. To this day, internally, Borland continues to develop the InterBase database, with the latest version being 7.1. Firebird 1.0 essentially was the open-source code behind InterBase 6.0. As of this writing, the first major development effort of the Firebird branch is Firebird 1.5.
For the new user, Firebird has two confusing aspects. First, the database is available in two flavors; second, various flavors of SQL dialects can be used, each carrying its own implications. Let's first look at the architecture issue and then move on to the SQL dialects as they relate to Firebird SQL.
As noted, the Firebird database comes in two variations, classic server and super server. Classic server is the original architecture behind the database. In the classic architecture, a separate process is spawned off for every connection made to the database, with the help of the inetd or the xinted dæmon. When there are few database connections, classic server uses less resources than does the super server architecture.
The super server architecture has been slated as the future direction in which the Firebird database will develop. It is a multi-threaded, multi-client architecture that requires few resources when additional connections are spawned off. Resource allocation and lock management are much more efficient in the super server architecture, because separate processes do not have to wait for others to finish before they can be addressed. One issue for the programmer to take into consideration when writing against a super server is any user-defined functions, including any external program that interacts with the super server database, needs to be thread safe. For those familiar with Oracle's architecture, an easy way to look at the connections' allocation is that classic is akin to dedicated connections while super server is more like the shared connection allocation system. For more information on this topic see the Resources section.
Another aspect of this database that might confuse the new user is the three separate dialects of SQL that Firebird offers. Dialects pertain mostly to the date-time format and the precision of a numerical data type. A good rule of thumb: if you are not familiar with the different dialects of Firebird, go with dialect 3. This dialect not only conforms closely to the latest ANSI SQL specification, it also should be familiar to users of Sybase and MySQL. Dialects are set up at runtime and can be changed for the client at connection time or with a set sql dialect command.
As the super server is the future direction of the Firebird database, this article concentrates on super server as the architecture of choice. As of this writing, the release candidate for Firebird 1.5 was available only in a binary tar, bz2 format. Unfortunately, no formal documentation is available for the installation of this, so here is the installation in a nutshell.
To install this tar file, bunzip the file and, as root, untar it in /usr/local directory. This creates the /usr/local/firebird directory. If you want the database to start by default at boot-up, run the minitinstall.sh scripts in the /usr/local/firebird directory. Otherwise, run the firebird.initd:
# sh ./firebird.initd start
By default, the database runs on port 3050. The binary used for this article took the libstdc++5.so, which is available with glibc 3.2, so make sure it is available. Also, add /usr/local/firebird/lib in /etc/ld.so.conf and then run ldconfig so the system can look inside that directory for the libraries. Finally, many third-party tools (Perl, PHP and so on) look in libgds.so for client communication purposes; this file does not exist in super server. The workaround I found involves putting a soft link from libgds.so to libfbclient.so, which seems to satisfy the applications.
Right off the bat, it is important to answer the questions that have cropped up around other open-source databases, especially MySQL. The primary concerns in their forms are answered below.
Does it support transactions?
Yes. A transaction can be committed or rolled back. In fact, Firebird even supports the concept of savepoints and rollback to savepoints, similar to what is found in Oracle. This feature is useful when, in a stored procedure or trigger, one wants to roll back to a certain point in logic. In Firebird, one can make a savepoint and then roll back to that specific point. If one is running a script of SQL commands, one can put the savepoints and rollbacks appropriately.
Does it offer foreign key support?
Yes. Firebird uses a standard SQL syntax to enforce foreign key support. For example, the following SQL text enables foreign key support for the child table:
SQL> create table parent (parent_id int not null primary key, CON> parent_val varchar(10)); SQL> create table child (child_id int not null, parent_id CON> int not null, child_val varchar(10), CON> primary key (child_id), CON> foreign key (parent_id) references parent(parent_id));
Does it support row level locks?
Yes. By default, Firebird uses a multi-version concurrency system. This means all the sessions see old data until the new data is committed. An alternative locking technique involves making a lock exclusive with the select... for update with lock SQL command. In either case, the lock granularity lies on the row level. Here is an example: assume two sessions are accessing the abovementioned parent table:
Session # 1:
SQL> select * from parent;
PARENT_ID PARENT_VAL
============ ==========
1 Preimage
SQL> update parent set parent_val = 'Postimage' where parent_id = 1;
SQL> select * from parent;
PARENT_ID PARENT_VAL
============ ==========
1 Postimage
Session #2:
SQL> select * from parent;
PARENT_ID PARENT_VAL
============ ==========
1 Preimage
Session #1:
SQL> commit;
Session #2:
SQL> commit;
SQL> select * from parent;
PARENT_ID PARENT_VAL
============ ==========
1 Postimage
As one can see, the non-committed transactions in session #1 did not stop the second session from accessing the data. Session #2, however, accessed the old value and not the new one.
The other important locking mechanism is "select for update" locking. This is convenient as it allows a session to have exclusive locks on a row; it guarantees that no other session can change the data until the session is done processing it. This way the session is assured the data does not change from the time it did a select to when it does an update.
Session #1:
SQL> select * from parent where parent_id = 1 for update with lock;
PARENT_ID PARENT_VAL
============ ==========
1 Postimage
Session #2:
SQL> update parent set parent_val = 'Postimage2' where parent_id = 1;
(Hangs)
As you can see, session #2 cannot update the parent_val until session #1 issues either a commit or a rollback. This sophisticated level of locking is not offered in MySQL, even with innoDB tables.
Does it support stored procedures and triggers?
Yes. In this area Firebird really shines. The advantage of having a standardized stored procedure language is developers that have come from other database background can be up and running in no time. In fact, it took me less than 10 minutes to learn to write productive stored procedures in Firebird. Even though PostgreSQL offers a stored procedure language, there are many different languages (Perl, Tcl, Python) in which one can write the stored procedures. This variation could lead to some problems when one developer leaves and other joins the project. Using a standard stored procedure language, however, avoids this problem. The triggers in Firebird are more like the ones in Informix/Oracle, with the option of having the before and after insert/update or delete, rather than Sybase's virtual INSERTED/UPDATED/DELETED table type.
Let's create a simple stored proc that shows the power of the language. The following example puts a row in the child table for a parent called os. If the os is Linux, it puts another row in the child table and puts the entry LinuxRocks in it as well.
SET TERM !! ; CREATE PROCEDURE insert_into_child (os_type varchar(10)) AS BEGIN insert into child (child_id, parent_id, child_val) values (gen_id(gen_child_id, 1), (select parent_id from parent where parent_val='os'), :os_type); if (:os_type = 'linux') then begin insert into child (child_id, parent_id, child_val) values (gen_id(gen_child_id, 1), (select parent_id from parent where parent_val='os'), 'LinuxRocks'); end else begin insert into child (child_id, parent_id, child_val) values (gen_id(gen_child_id, 1), (select parent_id from parent where parent_val='os'), 'Boo!'); end EXIT; END !! SET TERM ; !!
Here is an example of a trigger that checks for inserts and updates in the child table to see if the entry is LinuxRocks; if true, it puts another entry in the table called YouSaidIt!
SET TERM !! ; CREATE trigger you_said_it for child after insert or update AS BEGIN if (new.child_val = 'LinuxRocks') then insert into child (child_id, parent_id, child_val) values (gen_id(gen_child_id, 1), (select parent_id from parent where parent_val='os'), 'YouSaidIt!'); EXIT; END !! SET TERM ; !!
Now that we have looked at the questions most frequently asked of an open-source database, we should discuss the other great features this database offers.
Many solutions for replication have been developed by various entities. Most of these rely on trigger-based mechanisms that keep track of inserts, updates or deletes from a given table and then take those changes and propagate them to another database. As far as I can determine, all of the solutions are commercial in nature and can be used to administer databases on different platforms, including Window and UNIX/Linux. Additional information on this can be found on the IBPhoenix Web site (see Resources).
A single database can span to the data files, which gives the administrator the flexibility to load balance the database from a disk perspective. It is not unusual for databases to have local hot spots where an inordinate amount of activity occurs. Having the database laid out on multiple data files, which could reside in turn on multiple disks, alleviates the problem to a certain extent. Additionally, a single table also can be put on a separate file, and in this way load can be distributed to an even finer granularity.
Some people might wonder why they should make the effort to learn a new database, especially if they already are familiar with MySQL or PostgreSQL. From my perspective, Firebird offers a comfortable migration path from closed-source, commercial databases to an open-source database. I have found the task of converting from Oracle or Sybase to MySQL or PostgreSQL to be a bit daunting, as the nature of these databases is quite different from the commercial offerings. If the reader already is familiar with any of the large popular RDBMSs, the concepts he or she has learned over the years in those databases can convert smoothly to Firebird It offers virtually every common feature available in high-end databases without any significant impact on performance, as compared to the speed demons of the Linux platform. If you are looking for a database for your next project, think about Firebird as a viable option.
