Firebird is an open source relational database system. It is the #31 database overall on DB Rankings, the #19 ranked relational database.
Firebird started as a fork from Borland’s Interbase database. It can be run as a service, or used as an embedded database.
Installation:
Firebird can be downloaded here. Versions are available for Windows, Linux, and Android. There are EXE and ZIPs available. I’m on Windows 11, so I’ve installed the Win64 EXE for Firebird 4.0.4. The download is around 25 MBs.
I ran the executable and took the defaults. You are prompted to set a password for the system admin (SYSDBA), although you can click past to take the default password of masterkey.
Connect:
I’m using DBeaver to connect. I’ve used the defaults, so I’m connecting to localhost on port 3050. I’ll also supply the SYSDBA password.
Under Path, we can navigate to the sample database included with Firebird. Databases are .fdb files. The same database is employee.fdb under Program Files\Firebird\Firebird_4_0\examples\empbuild.
We’ll need to to click on the Driver Properties tab and download the Firebird drivers, if we’ve never connected to Firebird before.
Create Database:
We can also create a new database if we don’t want to use the sample one. From the command line, we’ll navigate to the Firebird install directory, Program Files\Firebird\Firebird_4_0 for me. Type isql to start the Firebird command line. You should see a message “Use CONNECT or CREATE DATABASE to specify a database”, and then a new prompt with SQL>. At this line, we’ll create a new database file:
CREATE DATABASE 'C:\Data\test.fdb';
I was able to create a new database without providing credentials, I assume since it’s a blank file. If the commands line tool needs credentials, the prompt will be CON>, and we’ll provide:
user 'sysdba' password 'masterkey';
Using Firebird:
There are no schemas or namespaces in a database.
Any system tables are in RDB, such as RDB$DATABASE.
There are also monitoring tables in MON, like MON$DATABASE.
PSQL (Procedural SQL) is an extension to the core Firebird SQL, used for writing stored procedures, triggers, functions, etc. Firebird also has packages (like Oracle), which are logical groupings of stored procedures and functions.
There are also foreign keys, constraints, and sequences that can be created.
For concurrency, Firebird uses something it calls Multi-Generational Architecture. Basically, it’s MVCC, where multiple versions of a record are kept, as long as active transactions may need that version.
Firebird has versions of ODS (on-disk structure), which is the database file format. Versions aren’t always backward compatible, for example version 4 can’t load a version 3 database.
SQL Examples:
Creating a table:
CREATE TABLE TestTable (
ID int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
Name varchar(50) NOT NULL UNIQUE,
Balance numeric(6,2) NOT NULL,
IsActive boolean NOT NULL,
CreatedDate TIMESTAMP WITH TIME ZONE
DEFAULT CURRENT_TIMESTAMP
);
We can use GENERATED ALWAYS AS IDENTITY to create an identity columns, which is creating a sequence as well. GENERATED ALWAYS means that the database will set each ID value, the user won’t be able to set those.
Insert records:
INSERT INTO TestTable (Name, Balance, IsActive)
VALUES ('TestRecord1', 50.05, TRUE);
INSERT INTO TestTable (Name, Balance, IsActive)
VALUES ('TestRecord2', 25.00, TRUE);
Comments:
-- Comment /* Comment */
Select the first record with Name in alphabetical order:
SELECT FIRST 1 * FROM TestTable ORDER BY Name;
Get current date/time:
SELECT CURRENT_TIMESTAMP FROM rdb$database;
Concatenate strings:
SELECT 'Atlanta' || ' ' || 'Falcons' as team FROM rdb$database;
We have to select from a table. The rdb$database system table will have one record, so we can use that in this case.
There isn’t a CONCAT function available.
Convert a string to a datetime:
SELECT CAST('2024-01-01' as TIMESTAMP) FROM rdb$database;
Get the month from the current date/time:
SELECT EXTRACT(MONTH FROM CURRENT_TIMESTAMP) as current_month FROM rdb$database;
Create view:
CREATE VIEW vwTestTable AS SELECT ID, Name, Balance, IsActive, CreatedDate FROM TestTable ORDER BY ID;
Get List of tables and views (No INFORMATION_SCHEMA views available):
SELECT rdb$relation_name, CASE WHEN rdb$view_source IS NOT NULL THEN TRUE ELSE FALSE END AS IsView FROM rdb$relations WHERE rdb$system_flag = 0 ORDER BY rdb$relation_name;
Drop the table – There’s no IF EXISTS available, so an error will be raised if we try to drop a table that doesn’t exist.
The view will have to be dropped as well, since it references the table:
DROP VIEW vwTestTable; DROP TABLE TestTable;
Links:
SQL Rob 