Firebird Road Trip and What's New in V.2.5

A bit of history

Firebird is based on the InterBase 6 source code, which has been published as open source in July 2000 by Borland Software Corp [3]. At this time, the database technology included in InterBase had been stressed for about 24 years, because everything started in 1976, when Jim Starkey [4] worked at DEC until 1981. A company called Groton Database Systems (guess, where the InterBase default database file extension “gdb” came from?) was founded in 1984 and the first InterBase version has been released about one or two year(s) later. InterBase was the second commercial database available with multi-version concurrency control (MVCC), a technology, which got adopted by many database products in the forthcoming three decades for better concurrency control. The first commercial database with MVCC support was not [5] InterBase, it was DEC’s Rdb/ELN, although using a pretty similar technology as in InterBase, because DEC’s product was called JRD, Jim’s Relational Database and guess, who wrote it, while being at DEC?

On July 31, 2000 – only one week after releasing InterBase 6 as open source – the Firebird source tree has been created on SourceForge [6] allowing the public to work on the code base from an infrastructure point-of-view. This is Firebird’s birthday. The original InterBase source code was released under an open source license called InterBase Public License (IPL) [7]. New Firebird modules are licensed under the Initial Developer’s Public License (IDPL) [8]. Both licenses are variants of the Mozilla Public License (MPL) V1.1 [9], thus “commercial-friendly”. This means, Firebird can be used in any projects, even commercial, without paying license fees. Although, people who are using Firebird for free, even if making money out of running their business based on Firebird, are welcome to join the Firebird Foundation [10], which has been founded in 2002 to support and advance the development of Firebird and to provide the non-commercial infrastructure required to accept and manage funds raised. So, a new emerging contender in the field of open source DBMS was born, although with an already established piece of database technology armed. If a “bit” of history was not enough for you and you need a “byte” of that story, then there is further material out there, including some amusing facts, for example the story behind the binary large object (BLOB) data type invented by Jim Starkey. A good starting point therefore is [11].

Where the project is now

Firebird is a stable and mature open source RDBMS offering many ANSI SQL-92/99/2003 features that runs on a broad range of 32-bit and 64-bit platforms natively. With a very small footprint (downloadable package is usually < 10MB in size)and the necessity for only very low maintenance/configuration/tuning resources needed to keep a database in shape, Firebird offers a lot: ACID compliant transactions including various isolation levels and support for two-phase commit (2PC), stored procedures, multiple row-level triggers per table per operation and event type with a user-definable firing order, cascading referential integrity, MVCC for fine-grained multi-user concurrency control following the “readers don’t block writers and writers don’t block readers” paradigm capable to handle mixed OLTP/OLAP environments, monitoring tables, global temporary tables (GTT), common table expressions (CTE), user-defined external functions, careful/consistent write order without the need for a transaction log for recovery purposes, server-side events for asynchronous notifications at the client-side, community-driven free and paid commercial support and a very active third-party tool market makes it an excellent choice. Various installations in the area of 400GB+ have proven that Firebird can handle large databases successfully.

Until October 2009, the Firebird project has released version 1.0, 1.5, 2.0 and 2.1 including various bugfix releases in each branch, undergoing a steep challenge between 1.0 and 1.5 porting the original C to a more-readable and maintainable C++ code. 1.5 was a great release, including the rebirth of the so-called ClassicServer architecture on Windows with its “one process per connection” model. This basically was (and still is) more SMP-friendly than the other architecture called SuperServer, which uses one dedicated server process processing all database requests per instance. Embedded Server – basically a set of files with the server compiled into one DLL – has been introduced for Windows in 1.5, which made it possible to follow the XCOPY paradigm when it comes to deploying a Firebird-based application without running anything through an installer. This is ideally suited for single-user scenarios like demo installations, read-only catalogues on CD-ROMs etc. Optimizer improvements and various SQL language enhancements made it a productivity booster compared to 1.0.

Version 2.0 introduced a new index structure, bypassing an annoying limitation in respect to the maximum index key size (e.g. when indexing character fields larger than 252 bytes), again various improvements of the optimizer, support for expression based indices, user-definable context variables at session and transaction level, an incremental physical page-level backup utility called “Nbackup”, revamped Unicode support, 64-bit support for Linux and more.

Release 2.1 added monitoring tables for a point-in-time snapshot on what’s currently going on in the database, database triggers (table-bound triggers have been available from the beginning) which fire on events like ON CONNECT, ON DISCONNECT, TRANSACTION START etc., global temporary tables (GTT), common table expressions (CTE) including support for recursive queries, Windows authentication support, SQL language enhancements and 64-bit support for Windows.

Beside the Firebird core engine, there are stable connectivity drivers (JDBC, .NET, ODBC, etc.) available. THE excellent printed Firebird book [12] by Helen Borrie fills the often criticised “documentation gap”, although not open source, thus not free, but worth every penny/cent. What’s next? Right: Firebird 2.5. The next sections will give you a very brief overview on the most important changes in 2.5. At the time of writing, the project was pretty close on releasing V2.5 Release Candidate 3.

SuperClassic architecture – better SMP-capabilities

The most important change in 2.5 is a new architecture called SuperClassic, which is a mixture of the SuperServer and ClassicServer architecture, with improved SMP-capabilities. This new architecture shall serve as an important intermediate development path towards 3.0. To goal for 3.0 is to have a fine-grained multi-threading architecture using a shared page and metadata cache per database. The characteristics of SuperClassic in 2.5 are:

One dedicated server process per instance as in SuperServer.
Connection-bound non-shared (= private) page and metadata cache per database as in ClassicServer.
SMP-support for simultaneous connections to one or more databases.
SMP-support for sweep and services API requests.
No exclusive lock on the database file necessary (as in ClassicServer).
Cached connection to the user/security database for faster connect times (as in SuperServer).
Safe shutdown of all database connections per instance (as in SuperServer).
Termination of all database connection per instance upon crash (as in SuperServer).
Although usable on 32-bit, the target platform is 64-bit, due to the OS’ extended memory addressing capabilities per process.

A more detailed Firebird 2.5 architecture comparison sheet is available on my blog [13].

Embedded Server is now based on SuperClassic

Formerly based on SuperServer, the Embedded Server is now based on SuperClassic. This means:

SMP-capabilities as in SuperClassic, if a separate connection per thread is used in the client application
No exlusive lock on the database file needed, thus allowing to access one database via several Embedded Server processes and regular SuperClassic and ClassicServer instances
Thread-safety

Embedded is based on the regular server code base. A regular server installation has an appropriate dedicated server process (depending on the architecture and platform: fbserver, fb_inet_server or fb_smp_server) communicating e.g. over TCP/IP with the client. Embedded Server on the other hand runs in the same address space as the client application. So, for multi-user installations, you really should (prior 2.5 you must) go with a regular server, although Embedded Server in Firebird 2.5, due to relaxing the exlusive file lock “issue”, allows a mixed environment as mentioned in the bullet list above. For any single-user deployment scenario, Embedded Server is a perfect fit. The nice thing here is that the physical database file layout isn’t different in the various architectures, so you can start with working on a database with Embedded Server and immediately begin using it with a regular Firebird server setup, when your (demo) database grows to a multi-user environment.

Audit and Trace Services

A long-awaited feature to continuously audit/trace the server/databases has been added with the audit and trace services. Basically, this is a configurable mechanism to trace database operations (connect, disconnect, transaction start, statement prepare, execution time, execution plan …) and services API calls into a text file. There is a difference between a system audit and a user trace. A system audit is started by the engine upon server start-up automatically. The configuration resides in a configuration file, usually in the Firebird installation folder. A user trace must be started by user request via a special services API call with a given trace configuration. A user trace does not survive a server restart, thus needs to be started by the user again, which is not the case for a system audit.

Enhancements in the monitoring tables

Two new monitoring tables have been added in 2.5. MON$MEMORY_USAGE reports the current memory usage at database, session, transaction and statement level. MON$CONTEXT_VARIABLES allows to keep track of user-defined context variables currently set. Furthermore, 2.5 allows to terminate a database connection entirely by executing a DELETE statement on the monitoring table MON$ATTACHMENTS. The previous version 2.1 only supported to cancel a SQL statement by deleting records in MON$STATEMENTS.

Language enhancements

Various language enhancements in different areas – DDL, DML and PSQL (the procedural language for stored procedures and triggers) – have been added in 2.5. This includes regular DDL statements for user management (CREATE/ALTER/DROP USER), extensions to the way of defining views, ALTER TABLE for non-persistent COMPUTED BY fields, support for regular expressions with a new SIMILAR TO predicate. PSQL enhancements are: autonomous transactions and an extended version of EXECUTE STATEMENT to execute cross-database SQL statements. The following code shows an example on using an autonomous transaction block in PSQL:

create table log (
logdate timestamp,
msg varchar(60)
);

create exception e_conn 'Connection rejected';

set term !!;
create trigger t_conn on connect
as
begin
if (current_user = 'BAD_USER') then
begin
in autonomous transaction do
begin
insert into log (
        logdate
        , msg
) values (
        current_timestamp
        , 'Connection rejected'
);
end
exception e_conn;
end
end!!
set term ;!!

An ON CONNECT trigger logs connection attempts of BAD_USER into a table LOG and throws an user-defined exception e_conn which aborts the connection attempt. Although an exception is thrown, the log record gets inserted, because this happens in an AUTONOMOUS TRANSACTION block. Without this concept, raising an exception would lead to rolling back the entire calling transaction including the insertion in the log table, thus the log record would never appear.

Firebird does not support database links. A second example shows the usage of an extension of EXECUTE STATEMENT, which allows accessing data in other databases on the same or remote servers. In the provided example on the same server by accessing a server instance running on port 3051 locally.

SET TERM !!;
EXECUTE BLOCK
RETURNS (COMPANY_NAME VARCHAR(40))
AS
BEGIN
  FOR
    EXECUTE STATEMENT 'SELECT COMPANY_NAME FROM CUSTOMER'
    ON EXTERNAL 'localhost/3051:mydb2.fdb'
    AS USER 'U1'
    PASSWORD 'u1'
    INTO :COMPANY_NAME
  DO
  BEGIN
    SUSPEND;
  END
END
!!
SET TERM ;!!

Although EXECUTE STATEMENT is only available in PSQL, it is not limited to the usage in EXECUTE BLOCK, which basically is executing PSQL on the client without the need to create a stored procedure. EXECUTE STATEMENT is also available in stored procedures and triggers.

The show must go on

Firebird 2.5 is a very nice release to leverage SMP systems more efficiently than in any other previous Firebird version. The audit and trace facility is long-awaited and especially useful to identify performance bottlenecks in case of bad written SQL statements or for compliance reasons. But 2.5 is not the end of the flagpole. The following might appear in 3.0 (no guarantee):

Shared page- and metadata cache with fine-grained multi-threading support
Support for packages
Support for schema
Support for stored functions
Longer identifiers beyond 31 bytes/characters
DDL triggers
DDL security
Java-based stored procedures
and possibly more

Also, check out the Firebird roadmap at [14], which got updated in February 2010.

At the end, I would like to congratulate the Firebird project for winning the SourceForge Community Choice Award 2009 (they did it again after 2007) in the category “Best Project for the Enterprise” [15]. Well-deserved guys!

About the author

Thomas Steinmaurer is an Industrial Researcher at the Software Competence Center Hagenberg [16] (Austria) in the area of data management and data warehousing specialized for the industrial manufacturing domain. Furthermore, he is responsible for the LogManager series (an auditing solution for various DBMS backends) and Firebird Trace Manager at Upscene Productions [17] and was a co-founder of the Firebird Foundation [10]. The author can be reached at thomas.steinmaurer@scch.at or t.steinmaurer@upscene.com.

Firebird Road Trip and What's New in V.2.5

A bit of history

Where the project is now

SuperClassic architecture – better SMP-capabilities

Embedded Server is now based on SuperClassic

Audit and Trace Services

Enhancements in the monitoring tables

Language enhancements

The show must go on

About the author

References

Author

Reading Time

Published

Category

Tags