Linux Step By Steps

Firebird Database on Linux

Prepared by Pascal Chong on March 9 2003
Table of Contents
1. Introduction
1.1 Why Am I Writing This ?
1.2 Copyright, License and Important Caveats
1.3 Why should I use Firebird ?
2. Installing Firebird
2.1 Where can I get Firebird ?
2.2 Which version should I get ?
2.3 System Requirements
2.4 Installing Firebird
3. Getting Started With Firebird
3.1 Administration tools
3.2 Creating your first database
3.3 Adding users and roles
3.4 Testing the database

 

1. Introduction

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.

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. Installing Firebird

2.1 Where can I get Firebird ?

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.

2.2 Which version should I get ?

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

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 here. 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 :

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. Getting Started With Firebird

We will now walk through setting up a sample database and familiarizing ourselves with the operations and administration tools of this database software.

 

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 :

 

3.1.1 gsec Security Administrator

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 :

GSEC> display

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.

 

3.1.2 isql Interactive SQL Processor

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 :

isql /opt/interbase/examples/employee.gdb

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

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

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.

       COUNTRY                                CUSTOMER
DEPARTMENT EMPLOYEE
EMPLOYEE_PROJECT JOB
PHONE_LIST PROJECT
PROJ_DEPT_BUDGET SALARY_HISTORY
SALES

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

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 VALUES('001', 'Aluminium Wok', 'Chinese wok used for stir fry dishes');
SQL> INSERT INTO sales_catalog VALUES('002', 'Chopsticks extra-long', '60-cm chopsticks');
SQL> INSERT INTO sales_catalog VALUES('003', 'Claypot', 'Pot for stews');
SQL> INSERT INTO sales_catalog 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 VALUES('001', 'Aluminum Wok', 'Chinese wok');
SQL> INSERT INTO sales_catalog VALUES('002', 'Microwave Oven', '300W Microwave oven');
SQL> INSERT INTO sales_catalog 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.

4. Additional References

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.

There are altogether 7 manuals and the information seems to be quite reliable for Firebird from my own experience. I have used the Operations Guide and the Language Reference for many parts of this document, and for my own development work.

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.