Firebird on Linux
Prepared by Pascal Chong on March 9 2003.
Table of Contents:
- 1. Introduction
- 2. Installing Firebird
- 3. Getting Started With Firebird
- 4. Additional References
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://www.firebirdsql.org/.
Latest download's are available directly from IBPhoenix.
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.
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.
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-126.96.36.1998-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.
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 :
- 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.
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:
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:
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>.
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 ( 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>.
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.
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.
At the present time, the only comprehensive documents available for Firebird are on the IBPhoenix CD. However for more information about its operations or SQL commands that it accepts, you can refer to the InterBase v6.0 manuals which are available under on the IBPhoenix downloads page. More upto date documentation for Firebird is available on the IBPhoenix CD/DVD.
- API Guide
- Data Definition Guide
- Developers Guide
- Embedded SQL Guide
- Operations Guide
- Language Reference
- Getting Started
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.