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

Major Features Of Firebird

1) It can be used free of charge.
2) The product functionality is not artificialy limited.
3) It is a powerful, stable and technologically developed product.
4) The configuration and functionality is more simple than for example the MS SQL Server.
5) Each database is located in one independent file, that can be located anywhere on the disk.
6) The installation is easy, supporting majority of operation systems (OS Windows and Linux).
7) Easy backup, access rights management, transaction evaluation, replication, etc.
8) Network access without the need of file sharing.
9) Using the internal SQL language for programming data checking, recounts and filters directly into the SQL server.
10) In OS Windows 2000/XP/Vista it is running either as service (running, even if no user is logged in), or as application program (tray icon).
11) It is possible to set some PROMOTIC components to save data into FireBird (e.g. trends).

12)Wire encryption support
13 Database encryption support
14)Wire compression support
15)Authentication plugin improvements
16)Firebird 4 data type bind configuration support
17)Firebird 4 DECFLOAT support
18)Firebird 4 extended numeric precision support
19)Firebird 4 time zone support
20)Firebird 4 statement timeout support
21)JDBC RowId support
22)DatabaseMetaData getPseudoColumns implemented
23)DatabaseMetaData getVersionColumns implemented
24)DatabaseMetaData getFunctions implemented
25)DatabaseMetaData getFunctionColumns implemented
26)Improved JDBC function escape support
27)New JDBC protocol prefix jdbc:firebird:
28)URL encoding in query part of JDBC URL
29)Generated keys support improvements
30)Operation monitoring

1.Support of all major platforms and operation systems

• Firebird supports a number of hardware and software platforms: Windows, Linux, MacOS, HP-UX, AIX, Solaris and more.
• It runs on at x386, x64 and PowerPC, Sparc and other hardware platforms and supports an easy migration mechanism between these platforms.
• Firebird is included into the following Linux repositories: Fedora, OpenSuse, CentOS, Mandriva, Ubuntu.

2.Multi-generation architecture

• One of the key Firebird features is its multi-generational architecture, which enables the development and support of hybrid OLTP and OLAP applications.
• This makes a Firebird database capable of serving simultaneously as both an analytical and an operational data store, because readers do not block writers when accessing the same data under most conditions.

3.Powerful and developer-friendly SQL language: Firebird supports stored procedures and triggers and has comprehensive SQL92 support.

• High compatibility with ANSI SQL
• Common Table Expressions (CTE)
• Flexible transactions management
• Full-blown stored procedures (selectable SP enables joins w/tables)
• Cross-database queries
• Active tables concept and events
• User Defined Functions

4.Logging and monitoring: Firebird offers Trace API and rich set of monitoring tables (MON$)

• Real-time monitoring
• SQL debugging

5.Security: 

a)Standard security

• Users and roles
• GRANT/REVOKE on main operations
• Database owner concept

b)Windows Trusted Authentication

• Single-sign on for end-users
• Integration with Windows domain/Active Directory security

c)Network

• The only network port should be open (3050 by default, configurable)
• Aliases (path to the database is not exposed)

6.Developer Tools(Firebird is supported by numerous database connectivity options)

• Firebird.NET
• JayBird (Java)
• Delphi/C++ Builder drivers (Embarcadero Delphi/C++ Builder IDEs include dbExpress drivers to work with Firebird.)
• FreePascal & Lazarus
• PHP for Firebird
• FireRuby
• and more!

7.More features

True Open Source: Firebird is free for commercial and educational usage: no license fees, installation or activation restrictions. No double licensing – Firebird license is based on Mozilla Public License.

8.Deployment

• Embedded version (in dll) with multi-user support
• Native Windows installer available, localized in most popular languages
• Run as service or as application
• RPM or tar.gz distributions available
• Ability to create custom “100% silent” installers
• Read-only deployments (database and server can be on CD, DVD, Blu-Ray etc)
• Small footprint (minimal installation is 4Mb, standard is 33Mb)

9.Performance

• Choice of architectures to fit all needs – Embedded, SuperServer, SuperClassic and Classic
• Multi-CPU and multi-core SMP scalability for SuperClassic and Classic architecures
• Database up to 20 Terabytes supported
• Thread-Safe Client Library

10.Backup and restore

• Online backup – ability to create backup copy without stopping database
• Online dump – ability to quickly create copy even for very big database
• Incremental backup – partially supported Point-In-Time Recovery.

• Installation Instructions For Windows

Note: How to find PublicDNS in AWS

Step 1) RDP  Connection: To connect to the deployed instance, Please follow Instructions to Connect to Windows  instance on AWS Cloud

1) Connect to the virtual machine using following RDP credentials:

• Hostname: PublicDNS  / IP of machine
• Port : 3389

Username: To connect to the operating system, use RDP and the username is Administrator.
Password: Please Click here to know how to get password .

Step 2) Click the Windows “Start” button and select “All Programs” and then point to  Firebird (Run the setup on desktop so that the default Firebird instance is properly setup)

Step 3) Other Information:

1.Default installation path: will be in your root folder “C:\program files\FireBird3.0.1”

2.Default ports:

• Windows Machines:  RDP Port – 3389
• Http: 80
• Https: 443

Configure custom inbound and outbound rules using this link

AWS Step by Step Screenshots