Firebird is an open source relational database management system which supports Linux, Windows, and a variety of Unix platforms.This tutorial explains about the main features of 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.

Database Installation
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.

Features in Detail
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?

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.

Replication
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 .

Multiple Data Files
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.

Firebird Superclassic architecture
The  most significant development in Firebird 2.5 is the Superclassic architecture, combining the best of both the Classic and Superserver structures to improve its support of multi-processor systems (SMP). Superclassic can use multiple CPUs with multiple simultaneous connections to a database. The new architecture can be characterized as follows:

A dedicated server process (fb_inet_server –m on Windows or fb_smp_server on POSIX) for all connections (identical to Superserver).
Private page and metadata cache. This implies the identical calculation of the maximum main memory usage for the coaching as with Classic server.
SMP compatibility for simultaneous connections to one or more databases.
SMP compatibility for sweeps and Services API requests.
No exclusive locking on the database file (identical to Classic server)
Cached connection to the user database for a quicker connection establishment (identical to Superserver).
Secure shutdown of all database connections by ending the dedicated server process (identical to Superserver).
Automatic shutdown of all database connections in the event of a server crash (identical to Superserver – a disadvantage).
Target platform: of course deployable on 32-bit systems; however use on 64-bit systems makes more sense, as here there are no limits with regard to the maximum main memory addressing per process (identical to Superserver).
Database access can be safely shared among Classic, Superclassic and Embedded processes:

Compared to the Classic server Superclassic uses less kernel resources (but not memory), it achieves better performance due to local calls inside the lock manager and other in-process optimizations (25% performance boost in TPC-C). Compared to Superserver, Superclassic offer better scalability (the number of connections is not explicitly limited), better concurrency on SMP/multi-core hardware and it is more responsive under a high load.

Firebird Server versions and differences

Firebird is available for various platforms, the main ones are currently 32-bit Windows, Linux (i586 and higher, and x64 for Firebird 2.0 on Linux), Solaris (Sparc and Intel), HP-UX (PA-Risc), FreeBSD and MacOS X. Main development is done on Windows and Linux, so all new releases are usually offered first for these platforms, followed by other platforms after few days

There is also a choice of server architecture: Classic server or Superserver.

Classic server
The Firebird Classic server offers multiple processes per connection and SMP (Symmetric Multi-Processing) support. Each connection uses one process. It supports multi-processor systems but no shared cache. I.e. each user connecting and requesting data, will have his/her data pages loaded into the cache, regardless of whether other users’ requests have already caused the server to load these pages. Which of course leads to a higher RAM necessity. However, as RAM and cache requirements are relevant to the size of the database file and the drive on which it is stored, the effects of this cache connection architecture doesn’t necessarily have to be a bad thing.Classic can be a good choice if the host server has multiple CPUs and plenty of RAM. Should you have sufficient working memory, we recommend you use the Classic Server and set the cache per user somewhat lower.

In ClassicServer each client has its own page cache and is connected to a dedicated process.This dedicated cache is much less efficient. If two clients access the same database area, this area will be copied to each client’s cache. Using the example above, when client B issued the statement, it would not get the benefit of an already filled cache. Instead, Firebird would have to access the disk again to answer the request.Besides, cache synchronizations is done on disk. This considerably increases the I/O cost on high-concurrency environments.

One great benefit of this model is the resilience offered by the multiple processes. If one of them has problems only the client attached to it will be disconnected. Everything else keeps running.The other great benefit is scalability. I believe this is responsible for most Classic deployments out there. Even in cases where the dedicated cache is inferior to the shared cache, Classic’s scalability make up for it. Just add hardware and bingo, your database server is faster.But this scalability does not come free. Imagine you have 200 simultaneous clients. It’s 201 processes. One for each client and another to listen to new connections. Your operating system must manage all this processes and keep them in sync. They consume a lot of kernel resources which means Classic can be relatively slow.

SuperClassic Server:

The Firebird development team decided to build Firebird 3.0 based on Classic. Firebird 3.0 will be completely SMP friendly. SuperClassic is the first step in that direction. It is an evolution and solves Classic’s biggest problem: all those processes make it slow and make maintenance harder.

Welcome to SuperClassic: A single process with a dedicated cache

.

Looking this way and considering the name, it may sound like a hybrid between Classic and Super but it’s not. What they did was put all those processes inside threads. Now each client has a dedicated thread inside a single process.Creating hundreds of threads is much cheaper than creating hundreds of processes and there is no loss of scalability. Cache synchronization is done directly in memory which reduces I/O costs. Other controls that used to be inter-process are now inter-thread and much faster.s.

Superserver
The Firebird Superserver has one process and multiple threads, but no SMP (Symmetric Multi-Processing), i.e. a dual-core machine. It serves many clients at the same time using threads instead of separate server processes for each client. Multiple threads share access to a single server process, improving database integrity because only one server process has write access to the database. The main advantage is however that all connected users share the database cache. If a data page has already been loaded for one user and the second user needs to access data on the same page, it doesn’t need to be reloaded a second time into the cache.

Superserver’s shared page cache, properly configured, can be beneficial for performance where many users are working concurrently. On the other hand, Superserver for Windows does not “play nice” with multiple CPUs on most systems and has to be set for affinity with just one CPU, as requests are serialized per database internally by the engine. However using Superserver with simultaneous connections to several databases can be distributed across multiple CPUs.

In SuperServer there is only one page cache and it is shared among all client connections.Because it is shared, this cache is very efficient. When several clients access the same database areas every client benefit from a big and well fed cache.

For example, when client A issues:

SELECT NAME FROM CUSTOMERS WHERE ID = 1;

a few pages related to table CUSTOMERS and to the primary key index are loaded into the cache.

When client B issues:

SELECT NAME, ADDRESS, PHONE FROM CUSTOMERS WHERE ID = 2;

it benefits from the shared cache because the pages this statement needs are already in cache.Also note that there is only a single process that all clients connect to.

Embedded server

Firebird 2.5 Embedded server
The Embedded server in Firebird 2.5 is now based on the Superclassic architecture and includes all SMP advantages. There is no longer an exclusive lock on the database file, which opens up the potential fields of application. A mixture of multiple embedded processes and regular Classic and Superclassic servers is possible, by which the same database file can be accessed in both read and write mode. The necessary synchronization occurs via a global lock table that can be found, for example in Windows, under C:\Documents and settings\All users\Application data\Firebird. Superserver cannot be used in this multi-connect context, because it requires an exclusive lock on the database file.

Thread-safe means that an independent database connection per thread is no longer required, but one connection handle can be used by several threads. The connection level synchronization routines necessary for this can be found in the Embedded server or in the client libraries, i.e. the client libraries (gds32.dll, fbclient.dll etc.) are now thread-safe. However a thread synchronization is necessary in the client application, in order to avoid unwanted side effects, for example when a transaction handle is collectively used by multiple threads. One independent connection and transaction per thread will avoid any unwanted side effects. Furthermore the Superclassic SMP ability can only be fully utilized in Embedded with a separate connection per thread.

Pre-2.5 Embedded server
The Embedded server is based on the Superserver architecture and allows only one local process per database, which of course means that it is unsuitable for a web server! The Firebird 2.1 Embedded Server version provides a useful enhancement: the client library is embedded in the server, this combination performing the work of both client and server for a single attached application. Only a few files are required without installation. It mainly consists of a slightly larger fbclient.dll, which is capable of providing the database server service to all installations. It is not necessary to install or start anything. This is particularly advantageous, for example, in the following situation:

You have an accounting application in an old 1997 version that you need to start today to view old data that was created and processed using this version. Normally you would have to search for the old version, install it, and – if for whatever reason it doesn’t work anymore (or maybe you never managed to find it in the first place!) – you can’t get to your data. Solution: pack your accounting application onto a DVD together with the correct Firebird embedded version. You can then start the application directly from the DVD without having to search and install anything. This is particularly useful when archiving data.

Firebird is, by the way, one of the few database systems that can read a database on a read-only medium.

Firebird is a powerful and lightweight open source SQL relational database management system for Windows and Linux. Features include full support for stored procedures and triggers, full ACID compliant transactions, incremental backups and multiple access methods (e.g. native/API, ODBC, OLEDB, .NET, Python, PHP and Perl).

Firebird is a relational database offering many ANSI SQL standard features that runs on Linux, Windows and a variety of Unix platforms. Firebird offers excellent concurrency, high performance and powerful language support for stored procedures and triggers.

Firebird is owned by Firebird (https://firebirdsql.org/) and they own all related trademarks and IP rights for this software.

Firebird is a fully featured and powerful RDBMS. It can handle databases from just a few KB to many Gigabytes with good performance and almost free of maintenance!

Firebird on Cloud runs on Amazon Web Services (AWS) and Azure and is built to offer many ANSI SQL standard features that runs on Linux, Windows and a variety of Unix platforms.

Cognosys provides hardened images of Firebird on all public cloud i.e. AWS marketplace and Azure.

Firebird on cloud for AWS