SQL compliance

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

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.

Transactions

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.

Multigenerational architecture

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.

BLOB filters

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.

Database administration

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

Security

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 ROLEs 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

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:

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.

Other tools

Firebird ships with several other command-line administration tools, including:

Services API

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.