Firebird on Linux

1.1 Why Am I Writing This ?

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.

1.2 Copyright and Caveats

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.

1.3 Why Should I Use Firebird ?

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.

2.1 Where can I get Firebird ?

The Firebird website can be reached at http://www.firebirdsql.org/.

Latest download's are available directly from Firebird Project.

2.2 Which version should I get ?

There are currently 2 versions of the Firebird server that you can download.

• Firebird Super Server
• Firebird Classic Server

In a nutshell, the difference between the two versions lies in the design approach. The Super Server uses threads to service multiple clients connecting to it at the same time, while the Classic Server was the original approach used in InterBase, which spawns a separate server process for every connection. A White Paper comparing the two approaches can be found on IBPhoenix. The choice of which server version to use is entirely up to you. For "embedded databases" with few concurrent users, it should not make much of a difference which one you choose. Theoretically, for applications with a wider audience, the Super Server should perform better by reducing process startup times and better sharing of resources. I haven't had a chance to test this in a high volume environment, so I would not be able to speak authoritatively on this subject.

This document covers only the Super Server version of Firebird.

2.3 System Requirements

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 :

• ncurses4 v5.0 or better
• glibc v2.2 or better

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.

2.4 Installing Firebird

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-1.0.2.908-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:

export INTERBASE_HOME=/opt/interbase
export PATH=$PATH:$INTERBASE_HOME/bin

Save the file and reboot the server and check that Firebird starts up OK.

3.1 Administration Tools

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 :

• gsec - This is the security administrator. You will use this command-line tool for creating, modifying and deleting database users, changing passwords, etc.
• isql - This is the interactive SQL tool, similar to Oracle's SQL*Plus and Postgresql's psql command. You can use this to test or run SQL queries.

$ gsec -user sysdba -password masterkey

GSEC> display

GSEC> modify SYSDBA -pw newpassword

isql /opt/interbase/examples/employee.gdb

SQL> SELECT emp_no, full_name, job_code, job_country FROM employee;

  EMP_NO FULL_NAME                             JOB_CODE JOB_COUNTRY
======= ===================================== ======== ===============

      2 Nelson, Robert                        VP       USA
      4 Young, Bruce                          Eng      USA
      5 Lambert, Kim                          Eng      USA
      8 Johnson, Leslie                       Mktg     USA
      9 Forest, Phil                          Mngr     USA
     11 Weston, K. J.                         SRep     USA
     12 Lee, Terri                            Admin    USA
     14 Hall, Stewart                         Finan    USA
     15 Young, Katherine                      Mngr     USA
     20 Papadopoulos, Chris                   Mngr     USA
     24 Fisher, Pete                          Eng      USA
     28 Bennet, Ann                           Admin    England
     29 De Souza, Roger                       Eng      USA
     34 Baldwin, Janet                        Sales    USA

SQL> SHOW TABLES;

COUNTRY                                CUSTOMER
DEPARTMENT                             EMPLOYEE
EMPLOYEE_PROJECT                       JOB
PHONE_LIST                             PROJECT
PROJ_DEPT_BUDGET                       SALARY_HISTORY
SALES

3.2 Creating Your First Database

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:

$ isql

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 (
CON> item_id varchar(10) not null primary key,
CON> item_name varchar(40) not null,
CON> item_desc varchar(50)
CON> );
SQL> INSERT INTO sales_catalog
CON> VALUES('001', 'Aluminium Wok', 'Chinese wok used for stir fry dishes');
SQL> INSERT INTO sales_catalog
CON> VALUES('002', 'Chopsticks extra-long', '60-cm chopsticks');
SQL> INSERT INTO sales_catalog
CON> VALUES('003', 'Claypot', 'Pot for stews');
SQL> INSERT INTO sales_catalog
CON> VALUES('004', 'Charcoal Stove', 'For claypot dishes');
SQL> SELECT * FROM sales_catalog;

ITEM_ID    ITEM_NAME                                ITEM_DESC
================================================================================

001        Aluminium Wok                            Chinese wok used for stir fry dishes
002        Chopsticks extra-long                    60-cm chopsticks
003        Claypot                                  Pot for stews
004        Charcoal Stove                           For claypot dishes

To exit isql, simply type quit; and press <ENTER>.

3.3 Adding Users and Roles

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
GSEC> add TestAdmin -pw testadmin -fname FirstDB -lname Administrator
Warning - maximum 8 significant bytes of password used

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
Database:  firstdb.gdb, User: SYSDBA
SQL> GRANT SELECT, UPDATE, INSERT, DELETE ON sales_catalog TO administrator;
SQL> GRANT administrator TO TestAdmin;
SQL> quit;

Now, we are ready to test our database.

3.4 Testing the 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;
SQL> INSERT INTO sales_catalog
CON> VALUES('001', 'Aluminum Wok', 'Chinese wok');
SQL> INSERT INTO sales_catalog
CON> VALUES('002', 'Microwave Oven', '300W Microwave oven');
SQL> INSERT INTO sales_catalog
CON> VALUES('003', 'Chopsticks extra-long', '60cm chopsticks');
SQL> SELECT * FROM sales_catalog;

ITEM_ID    ITEM_NAME                                ITEM_DESC
========== ===============================================================
001        Aluminum Wok                             Chinese wok
002        Microwave Oven                           300W Microwave oven
003        Chopsticks extra-long                    60cm chopsticks

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.