This document is a work in progress. The Firebird documenters — all volunteers who work on this project in their spare time — will publish more chapters as they go along. As we are but a few, the pace is pretty slow. However, the few chapters that are present are up to date and will hopefully be a useful source of information for you.
If you find any errors or inconsistencies, please report them to the maintainers. Email addresses are at the end of the book.
1.2. Origins
Using Firebird is not a new book. The IBPhoenix editors wrote it years ago for distribution on their Developer’s CD, when Firebird was still at version 1.0. Since then, we have seen the arrival of Firebird 1.5 and 2.0, and most of the book is now in serious need of updating. In 2005 the IBPhoenix company decided to open-source the entire 26-chapter manual and hand it over to the Firebird Project for upgrading and maintenance.
We would like to thank IBPhoenix here for making this work freely available.
1.3. More documentation
If you’re new to Firebird or want to brush up on the basics, read the Firebird Quick Start Guide first. There’s one available for every version of Firebird. Pick up yours from https://www.firebirdsql.org/en/documentation/. This is also a good starting place to find links to more Firebird documentation.
2. About Firebird
Firebird is a powerful, compact client/server SQL relational database management system which can run on a variety of server and client operating systems. Its officially supported platforms are Windows and Linux, but Firebird is also known to run on several other OS’s, such as FreeBSD and Apple Macintosh OS/X.
Firebird features a higher level of SQL standards compliance than most other industrial-strength client/server database management systems on the market today, while implementing some powerful language features in the vendor-specific sphere of procedure programming.
2.1. Firebird’s origins
The product which today we call Firebird has been around, under a variety of names, for well over 20 years. An overview of its interesting and at times stormy history can be found at https://www.firebirdsql.org/en/historical-reference/.
Developed as an ongoing open source project, Firebird is a descendant of Borland’s InterBase 6.0 Open Edition code which was released for open source development in July 2000 under the InterBase Public License (IPL).
The Firebird source code tree is maintained on the international open source code foundry, GitHub, by a large team of professional developers who donate time and expertise voluntarily to fix, develop and enhance this popular and feature-rich database management software.
The Firebird software products are distributed completely free of registration or deployment fees.
2.2. The Firebird Foundation
The Firebird Foundation supports the development of Firebird in several ways, among other things by issuing grants to developers. Many people and companies who find Firebird useful have already become members or sponsors. If you like Firebird, please consider doing the same. Making a one-time donation is also possible. You can find more information at https://www.firebirdsql.org/en/firebird-foundation/.
2.3. Overview of Features
Firebird is true client/server software, architected for use in local and wide-area networks. Accordingly, its core consists of two main software programs:
The database server, which runs on a network host computer.
The client library, through which users on remote workstations connect to and communicate with databases managed by the server.
TCP/IP is the network protocol of choice for Firebird on all platforms, although Windows Networking (NetBEUI) is supported too for networks having Firebird running on a Windows NT, 2000/2003 or XP host server.
It is possible to run both server and client on the same physical machine and have the client connect to the server through TCP/IP local loopback. On Windows machines, a single local client can also connect to a database by sharing inter-process communications memory with the Firebird server. On Linux, even direct I/O to the database file is possible, but only with the so-called Classic Server — more on this later.
2.3.1. Firebird Server
Firebird server runs on a number of platforms, including:
Windows NT 4.0, 2000, and 2003 (Server or Workstation editions)
Windows 95/98 and ME
Windows XP (Home, Professional and .NET editions)
Linux, FreeBSD and several other UNIX-like operating systems
The Firebird Embedded Server is a special variant which contains both the client and the server functionality. You can ship it with your application, unpack it, and it’s ready to roll. You’ll learn more about its up- and downsides later on in this guide.
2.3.2. Firebird clients
A remote workstation or a local client requires only the shared client library — a dynamic link library on Windows and a shared object on other platforms — and an application program which can pass and receive parameters to and from the library’s interface.
Generally, you would also install a copy of the client library on the host server, for use with several of the Firebird command-line utilities and/or any server-based management programs you might use. Many of these utilities can also be run remotely, however. A remote system administrator can manage some of the essential services provided by these utilities by accessing them through a host service controller process.
For Java connectivity, Firebird provides the JDBC/JCA-compliant Jaybird driver. Client applications written against Jaybird can run on any Java-enabled platform, even those that don’t support Firebird server. The legacy InterClient Java driver is no longer maintained, due to its severe limitations.
2.3.3. Summary of features
Table 1. Summary of features
Firebird Feature
Description
Firebird conforms to entry-level SQL-92 requirements. It has support for formal, cascading referential integrity constraints, updatable views, and full, left and right outer joins. Client applications can link to the Firebird API, a messenger function library for client-server communication.
The Firebird server supports development of dynamic SQL client applications. It also ships with a host-language precompiler and in-engine language support for embedded SQL development in host languages such as C/C++ and COBOL.
Several extended SQL features are also implemented. Some of them (e.g. stored procedures and triggers, SQL roles, and segmented blob support) anticipate SQL99 extensions.
Multiuser database access
Firebird is designed to provide for many clients accessing a single database at the same time. In their turn, client applications can have active connections to several databases simultaneously. Firebird will automatically protect cross-database transactions through a two-phase commit mechanism. Triggers and stored procedures can post event messages to inform interested clients of specific events in the database.
User-defined functions (UDFs) can be written and stored on the server machine in external shared object libraries. Once a UDF is declared to a Firebird database as an external function, it is available to any client application accessing the database, as if it were a native function of the SQL language.
This flexibility accounts for the very small footprint of the server engine: Firebird database application solutions are deployed without the extra cargo of a server that supports hundreds of unused functions natively in its engine.
Firebird client applications have full control over the starting, committing, and rolling back of transactions. Every transaction exists in its own consistent context, determining isolation from other transactions and resolution of multi-user conflicts at commit time.
A transaction’s uncommitted view of the state of the database is kept consistent with its initial view and any changes which are made within its own context.
Client applications can isolate multiple tasks in separate transactions simultaneously. A single transaction can bridge a task involving an unlimited number of connected databases, with an automatic two-phase commit mechanism to protect integrity, should a database become unavailable before the transaction completes.
Firebird uses a multi-generational architecture, by which multiple versions of each data row can be created and stored as necessary if a transaction modifies the row. In a background thread, extinct versions are garbage-collected and the current and pending versions are managed, in order to give each transaction a persistent view and to resolve priorities when update conflicts occur.
The multi-generational architecture of Firebird means that readers never block writers. Firebird allows any row to be visible to any transaction, even if other transactions have updates pending for it. Readers may of course see another (older) version of the row than the writer.
The Firebird engine maintains version statistics which it uses, in conjunction with the isolation and lock response attributes of each transaction, to determine which transaction gets priority when conflicting updates are requested.
Optimistic row-level locking
In Firebird, user-initiated locking is unnecessary. The engine locks a row to other transactions only when a transaction signals that it is ready to update it. This is known as optimistic row-level locking. This style of locking has great advantages in increasing throughput and reducing serialisation for client tasks, when compared with systems that lock rows, or even entire tables, from the moment the transaction begins.
Firebird provides the capability for the developer to supply filter code for converting stored BLOBs from one format to another. For example, a BLOB filter could be written to output a text BLOB, stored in RichText format, as XML or HTML; or to output a stored JPEG image in PNG format. The filters, written in the developer’s language of choice and compiled for the server platform OS, are stored on the server machine in a shared object library and must be declared to databases that want to use them, exactly like UDF libraries.
Firebird comes with various command-line utilities for managing databases and servers. Thanks to its open source character, Firebird is also abundantly supported by open source, freeware and commercial GUI database administration utilities. Using his or her preferred constellation of tools, the database administrator can
manage server security;
make and restore database backups;
perform maintenance tasks;
produce database and lock manager statistics.
Firebird maintains a security database storing user names and encrypted passwords. It is located in the root directory of the server installation and controls access to the server itself and all databases in its physical domain. The SYSDBA account has full, destructive privileges to all databases on the server.
Firebird provides the capability to define ROLE s at database level. Within a database, only SYSDBA and the database owner have full privileges; otherwise, all privileges must be granted explicitly to individual users and/or roles. It is possible — and recommended — to define a set of permissions for a role and then grant that role to specific users as required.
SYSDBA can add and delete users’ accounts names and modify the details of an account, including the password. Passwords, once stored, are not human-readable, even by SYSDBA .
Physical database paths can be shielded from the client using aliases. Access to database files, external tables, and UDFs can be restricted to explicitly specified filesystem trees only — or even tighter — by setting the appropriate parameters in the configuration file firebird.conf .
The Firebird server process can — and if possible, should — run as a user other than the system or superuser account ( root , Administrator or localsystem ). This will limit the damage in the unfortunate event that the server should be hacked.
Backups and restores
Firebird comes with two command-line backup/restore tools, each with its own specific advantages and limitations.
The gbak utility backs up a database by dismantling it into a compact structure in which metadata, data and database-level configuration settings are stored separately. It also performs some important housekeeping tasks on the database during the backup process.
The generated backup is not readable as a database file; you need gbak again to restore it. In restore mode, gbak can create a new file or overwrite an existing database.
Because of the useful tasks it performs, experienced Firebird programmers often use a gbak backup-restore cycle to
erase obsolete record versions;
change the database page size;
convert the database from single- to multifile;
safely transfer a database to another operating system;
upgrade InterBase or Firebird databases to a newer version;
make a metadata-only backup in order to create a new, empty database with the same structure.
Several user-friendly GUI front-ends are available for gbak, both as stand-alone tools and as utilities within some of the database administration programs. It is also very simple to set up OS-level scripts, batch files or daemons to perform backups.
A more recent tool by the name of nbackup lacks most of gbak’s housekeeping and compaction features, but has the following advantages:
Incremental backups, which save time and disk space;
Backups at hardware speed;
Backups possible with your own preferred (non-Firebird) tool.
Neither backup tool requires exclusive access to the database. Other clients can remain connected and perform operations on the database while the backup is in progress.
Firebird ships with several other command-line administration tools, including:
An SQL query utility which can run dynamic SQL (DSQL) and several specialised statements interactively or in batch from a script. This is the tool to use for quick access to information about your metadata and for running data definition scripts.
A database housekeeping and repair kit for minor corruptions. This tool is often used in combination with some of the utilities in the gbak program for identifying and recovering damaged data.
A command-line interface to the security database.
A utility for printing out the current configuration and statistics of a running database.
A utility for printing out the Lock Manager report on a running database.
Firebird provides a Services API which developers can use to perform a number of security and management tasks programmatically (and if needed, remotely). Strictly speaking, the Services API (part of the client library) is the interface to the Services Manager (part of the server), but the terms are often used interchangeably.
2.4. Classic and Superserver architectures
Firebird server comes in two distinct architectures for managing multiple client connections: Superserver and Classic Server. For Windows, both architectures are included in a single download. For Linux, there are separate download packages which have either CS or SS in their name, indicating the type of server.
The Classic server starts a separate process for each connection to a database under its control. Each client is allocated its own database cache buffers. Superserver serves many clients simultaneously within a single process. Instead of separate server processes for each connection it uses threads of a single process and pools the database cache buffers for use by all connections.
If you are upgrading from a previous version of Firebird or faced with the choice between Classic and Superserver, the information listed in the comparison table below will help to explain what the differences are and how they affect database operations.
The server architecture does not affect the structure of databases or the way client applications work. Firebird databases built on a Classic server can be operated on by an equivalent Superserver server, and vice versa. The same client library can connect to either server.
In other words, if you begin by installing the Superserver distribution of Firebird on your Linux host machine and, later, decide to change to Classic, any applications you wrote for your Superserver-hosted databases will work unmodified and the databases themselves will continue to behave as they did before.
2.4.1. Comparison of characteristics
The table below gives a quick overview of the main differences between Classic and Superserver. These differences will be discussed in more detail in the subsections that follow.
Table 2. Comparison of Classic and Superserver architectures
Linux: All Firebird versions Windows: Firebird 1.5 and higher
All Firebird versions
Multiple, on demand, one instance per client connection.
Single server process, each client request is handled in its own thread.
Implemented as a thread.
Local access on Linux
Fast, direct I/O to the database file is possible. But you can also connect network-style via localhost .
Network-style access only.
Local access on Windows
Versions 1.x: network-style access only.
Versions 1.x: single (!) local connections can be made using IPC (IPServer). Network-style local connections are also supported.
Firebird 2 and higher: both architectures support safe, multiple local connections on Windows machines through XNET.
One cache per process.
One cache space for all clients.
No. Performance may drop if not properly configured.
Services Manager + API
Partial in Firebird 1.5, full in 1.5.1 and up.
Guardian on Windows
On Firebird 2 Classic/Win only, a bug prevents you from using the Guardian if you run Firebird as an application.
The Guardian functions with all Windows Superservers, whether run as a service or as an application.
Guardian on Linux
You can’t use the Guardian with any Firebird Classic version on Linux. This is by design.
The Guardian functions with all Linux Superservers.
Executable and processes
Runs on demand as multiple processes. When a client attempts to connect to a Firebird database, one instance of the fb_inet_server executable is initiated and remains dedicated to that client connection for the duration of the connection.
Runs as a single process, an invocation of the fbserver executable. fbserver is started once by the owning user or by a boot script. This process runs always, waiting for connection requests. Even when no client is connected to a database on the server, fbserver continues to run quietly. On Linux, the Superserver process does not depend on inetd ; it waits for connection requests to the gds_db service itself.
Lock management
For every client connection a separate server process is started to execute the database engine, and each server process has a dedicated database cache. The server processes contend for access to the database, so a Lock Manager subsystem is required to arbitrate and synchronise concurrent page access among the processes.
The lock manager is implemented as a thread in the fbserver executable. It uses inter-thread communication mechanisms to resolve access issues. Therefore, an external process isn’t needed.
Resource use
Each instance of fb_inet_server keeps a cache of database pages in its memory space. While the resource use per client is greater than in Superserver, Classic uses fewer overall resources when the number of concurrent connections is low.
Employs one single cache space which is shared by client attachments, allowing more efficient use and management of cache memory when the number of simultaneous connections grows larger.
Local access on Linux
On Linux only, the Classic architecture permits application processes that are running on the same machine as the database and server to perform I/O on database files directly. Note that this is only possible if the client process has sufficient filesystem-level access rights to the database as well as some other files. Network-style access to the local server (via localhost or equivalents) is supported on all systems.
You can only connect to local databases via TCP/IP loopback, using localhost or any other host name / IP number that points back to the local machine. (Many clients may let you get away with omitting the hostname though, and supply localhost to the server automatically.)
Local access on Windows
In Windows Classic versions prior to Firebird 2, you can only connect to local databases via network loopback, using localhost or an equivalent. Firebird 2 and higher support local access through the reliable XNET protocol, which permits multiple simultaneous connections in a safe way.
Firebird 1.5 and earlier Superservers use the IPC (IPServer) protocol for single local connections on Windows. This method is not as fast and certainly not as robust as the direct I/O on Linux Classic. Furthermore, IPC needs an internal window to exchange messages. As a consequence, local access on these versions is only available if:
the Firebird process runs as Localsystem (the default), and
the configuration parameter CreateInternalWindow has not been set to 0 (you would set this to 0 if you want to run multiple servers simultaneously).
Firebird 2 uses a different local protocol — XNET — which doesn’t suffer from these restrictions, and supports multiple connections.
Of course if local protocol is disabled you can still connect to any local database via localhost , provided TCP/IP is available on your system.
Multiprocessor support
Supports SMP (symmetrical multi-processor) systems. This improves the performance in case of multiple unrelated connections.
No SMP support. In fact, Superserver performance may drop significantly on multiprocessor Windows systems as a result of processor swapping. To prevent this from happening, set the CpuAffinityMask parameter in the configuration file firebird.conf .
Services Manager and Services API
Versions up to and including 1.5 have a partially implemented Services Manager, supporting tasks like backup/restore, database shutdown etc. over the network. Other service tasks have to be performed locally using the client tools that come with Firebird. Versions 1.5.1 and up have a full Services Manager, just like Superserver.
The Services Manager, present in all Firebird Superserver versions, allows you to perform management tasks (backup/restore, database shutdown, user management, stats, etc.) programmatically. You can connect to the Services Manager over the network and thus perform these tasks remotely.
Use of the Firebird Guardian
The Firebird Guardian is a utility which monitors the server process and attempts to restart the server if it terminates abnormally.
Due to a bug in the Guardian, it can’t be used with Firebird 2 Classic on Windows if run as an application. If Firebird runs as a service, the Guardian works correctly. Since the Windows 9x–ME line doesn’t support services, you can’t use the Guardian with Firebird 2 Classic on those systems. This bug does not exist in Firebird 1.5 versions.
(The Guardian can’t be used at all with Firebird Classic on Linux, but that’s by design, not by accident.)
The Guardian works fine with Superserver on both Linux and Windows, whether as a service or as an application.
2.4.2. Which is better?
In abstract terms, neither architecture is a clear winner. One architecture generally outshines the other under specific workload conditions:
A single application running on the same machine as the server is faster with the Classic architecture.
For a Linux application embedded in an appliance, Classic is better, because it provides a single process from application to disk.
On a single-processor machine, an application with larger numbers of frequently contending clients is faster with Superserver, because of the shared cache.
On SMP machines, small numbers of clients whose data updates do not impact others’ tasks work better in the Classic architecture.
2.4.3. Embedded server
Besides Superserver and Classic, there’s Firebird Embedded Server for Windows, which you can download as a separate package. This is not really a different architecture, but a Firebird client plus Superserver rolled into one DLL for ease of deployment. Although it has a number of downsides, it may be an attractive option if you want to include Firebird with your Windows application. More on Embedded Server in the client-server chapter.
2.5. System Requirements
Firebird makes efficient use of system resources. Both server and clients are modest in their disk space and memory requirements. Some specific details are provided below.
2.5.1. Server Memory (all platforms)
Firebird server process
When there are no connections, the Firebird server uses around 2–4 Mb of memory, depending on the version.
Each client connection uses from 115 Kb to several Mb of additional memory on the server host. The exact load depends on the Firebird version, the structure of the database and the client characteristics.
Memory is also needed for database page caching. The default cache size is configurable, in database pages. Superserver shares a single cache among all connections and increases cache automatically when required. Classic creates an individual cache per connection.
Other server tasks
The server uses additional memory for lock management, in-memory sorting, and so on. For some tasks the amount can be configured.
2.5.2. Disk space
Disk space requirements vary somewhat according to platform, architecture and Firebird version.
