In July 2000, Borland (then Inprise) released the source code of their database product, Interbase, under the Interbase Public License. Firebird is the Open Source direct descendent of that database. For more information about Interbase and Firebird, there is an excellent description of the history of events leading up to the release of Interbase to the Open Source community, and the subsequent development of Firebird.
My first contact with Interbase was when I was working for a company reselling Borland products back in 1999. I provided tech support and some training for Delphi, and Interbase was bundled with the client/server version of the Delphi product. Back then, I made a good friend who was crazy about Interbase. I was not all that interested, because I was supporting IBM's DB2 and participating in the open Linux beta at that time.
It was only very recently that I came back to Interbase, and its present Open Source incarnation, Firebird. Someone on the mailing list asked about Open Source databases that could be bundled with applications, and someone else suggested Firebird. I remembered my friend from way back, and I thought that maybe he might enjoy seeing his pet database written about, and that is the reason for this document.
This document is free documentation; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This document is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
Open Source databases generally suffer from 2 common deficiencies : either they are large, such as PostgreSQL and MySQL, or they lack features and documentation, such as HypersonicSQL or McKoi.
Firebird has a relatively tiny footprint, at about 2.6MB for the RPM version. This makes it ideal as an "embedded database", bundled with an application server and an application. Firebird has all the common features of more mature databases, such as support for stored procedures, SQL-compliance, etc. If your background is in DB2 and PostgreSQL, the syntax is very similar, and the data types and data handling may seem very familiar, such as the mandatory "single-quotes" for strings.
The design emphasis for Firebird seems to be on small, fast and minimum management. This is ideal for developers who need a database for storage, but do not want to spend too much time on tuning the database for performance. In many situations you may not even need stored procedures, or do complex table joins. In such cases, Firebird is the ideal compromise between size and functionality.
The Firebird website can be reached at http://firebird.sourceforge.net/index.php, or alternatively at http://www.firebirdsql.org/.
At the time this document was written, the download site for Firebird was at http://sourceforge.net/project/showfiles.php?group_id=9028. Please note that this may have already changed. It is advisable to go to the Firebird website and click on the "Download" link.
There are currently 2 versions of the Firebird server that you can download.
This document covers only the Super Server version of Firebird.
This database, at just over 2.5 MB is really quite tiny, and I suppose, if you can run Linux on your system, you can probably run this database as well.
The only requirements are :
These instructions were tested on Probatus Spectra Linux 1.2, which is equivalent to Red Hat v7.3. It should work on any rpm-based Linux, though, as always, your mileage may vary.
After you have successfully downloaded the rpm file from the website, login as root.
Execute the following command :
|# echo localhost.localdomain >>/etc/hosts.equiv|
This will add localhost to the list of servers it recognizes. Go to the directory where your downloaded rpm file resides and execute the following command.
|# rpm -ivh FirebirdSS-18.104.22.1688-1.i386.rpm|
This will install Firebird and create a startup script in /etc/init.d that will start Firebird automatically on boot. If you encounter any errors, check that you have ncurses4 and the right version of glibc installed.
Interestingly, you will notice that Firebird creates a directory called interbase under /opt, and puts its binaries there. This shows its heritage, because it was derived from Interbase after all. We will need to make the binary files available system-wide, and for that we will edit /etc/profile, inserting the following lines inside :
Save the file and reboot the server and check that Firebird starts up OK.
We will now walk through setting up a sample database and familiarizing ourselves with the operations and administration tools of this database software.
The default system administration account has the username SYSDBA (this username does not appear to be case-sensitive, when I tested it) and the password masterkey. For users of previous versions of Interbase (and people who worked through the Fish Catalog tutorial for Delphi), this will seem very familiar. You will use this account to create another user and the sample database initially.
The administrative tools that are available with the software are :
You will need to run gsec as SYSDBA. To invoke it, execute the following as root or non-root user :
|$ gsec -user sysdba -password masterkey|
This will bring up the GSEC> prompt. You can display current users by typing "display" at the prompt, like so :
It is a good idea to change the SYSDBA password, because the default is so well-known. To change it, we modify the SYSDBA account using the following command :
|GSEC> modify SYSDBA -pw newpassword|
Ok, newpassword is not exactly a strong password. You should generate your own, which should contain both numbers and letters, and they should be changed frequently. But we will not go into that here.
As mentioned previously, isql is analogous to psql for PostgreSQL and SQL*Plus for Oracle. You can type in an SQL command and get the query results from the database.
Firebird comes with an example EMPLOYEE database, and we will use it to test our SQL commands. To begin, execute the following command :
This will connect you to the sample EMPLOYEE database and display an SQL> prompt. You can type in your SQL commands at the prompt. Remember to put a semicolon (;) at the end of the statement to terminate it, before pressing <ENTER> to execute it.
To test, type the following SQL command and press <ENTER>
|SQL> SELECT emp_no, full_name, job_code, job_country FROM employee;|
This should give you :
EMP_NO FULL_NAME JOB_CODE JOB_COUNTRY
If you wish to see all the tables in the database, type the following :
|SQL> SHOW TABLES;|
This will give you all the tables in that database.
To exit from isql, simply type quit; and press <ENTER> .
So far, we have executed our commands as SYSDBA, and used the default examples provided with the software. Now, we are going to create a database of our own, create a user that will have rights to view and modify the database, and try operating on the database.
To create our database, we will need to use the isql tool. Firebird saves its databases under discrete files, and, by convention, the extension is .gdb. Note that this is just a convention, and that you can save the database as any extension you wish. For this demonstration, we will first create a database using the SYSDBA user and save it under a directory called test under $INTERBASE_HOME.
We first create a directory called testdb under /opt/interbase (be sure to assign the appropriate rights to the directory), navigate to it, then launch isql with no arguments
Then we execute the CREATE DATABASE command
|SQL> CREATE DATABASE 'firstdb.gdb' USER 'sysdba' PASSWORD 'masterkey';|
This creates a file called firstdb.gdb inside the current directory. The database is owned by SYSDBA. We will now create a very rudimentary Sales catalog and fill it with data. If you are already familiar with SQL, the following commands should be easily understood. If not, you should probably read up on the ANSI SQL-92 standard.
SQL> CREATE TABLE sales_catalog (
To exit isql, simply type quit; and press <ENTER>.
We now have a database, but it may not be a good idea to create and administer all databases using the SYSDBA account. In some cases, for example, if I am running multiple databases belonging to different people or groups, I may want each user or group to own their respective database, with no rights to view other databases. Another scenario may be a requirement to create a proxy user that will execute all database operations, but which may not have all the superuser rights of SYSDBA.
In this section we will create a database user and assign the account viewing and updating rights.
We will need to use the gsec utility for this operation. So, supposing we want to create a user called TestAdmin with password testadmin (I know, I know, another weak password) and give him viewing, modification and deletion rights to firstdb.gdb, we will execute the following commands. Note that only the first 8 characters are used for the password.
$ gsec -user SYSDBA -password masterkey
Next, we open the database, create an administrator role for the database, assign the appropriate rights to that role, then add TestAdmin to the role.
$ isql firstdb.gdb -user SYSDBA -password masterkey
Now, we are ready to test our database.
First, exit gsec and isql, if you have not already done so. We will login to firstdb.gdb as TestAdmin, run some queries, then exit. Just to test the database. The commands, and the results are shown below :
SQL> DELETE FROM sales_catalog;
If you encounter any SQL errors at any point, you will need to check with the references in the next section.
If everything worked, congratulations ! Your Firebird is now ready to fly ! I'm still discovering stuff about this database, so if you have any suggestions, criticisms, or anything new you would like to add to this write-up, please email me.
At the present time, there are no comprehensive documents available for Firebird. For more information about its operations or SQL commands that it accepts, you will need to refer to the Interbase v6.0 manuals which are available under here.
The Firebird website contains many pointers to interesting articles related to the history of Firebird as well as several White Papers which may be interesting to technology managers.
Hopefully, with the imminent release of version 1.5, the dearth of documentation regarding language and operations will be gradually filled.