Firebird

Firebird is a cross-platform SQL-database, forked in 2000 from Borland's Interbase RDBMS. These are quick instructions how to get started with Fedora / CentOS system.

Good to know

• binaries have been renamed with postfix -fb to avoid conflict with ODBC-binaries.
• firebird comes with default database employee.
• server config file is /etc/firebird/firebid.conf
• all databases reside physically in /var/lib/firebird/data directory
• on CentOS /etc/init.d/firebird-superserver daemon arguments (-daemon -forever -pidfile) are in wrong order.
• cli interpreter comes in server package, there is no traditional client-server split/subpackages.
• usernames and passwords are truncated to 8 characters, masterkey becomes masterke, both are valid.
• DSN syntax:
  • absolute file name at server filesystem.
  • <hostname>:<database> where db-file mapping is in server /etc/firebird/aliases file.
• You might want to read about transaction isolation levels, it could be confusing at the beginning.

Packager's notes:

# cat /usr/share/doc/$(rpm -q --qf="%{NAME}-%{VERSION}" firebird-filesystem)/README.Fedora

Installation

There are three versions of Firebird server software. You want superserver.

# yum install -y firebird-superserver
# service firebird-superserver start

Changing the admin password

# gsec -user sysdba -pass masterkey -mo sysdba -pw newpwd1

Creating a user

# gsec -user sysdba  -password masterkey  add dude -pw dudepwd1

Connecting to a database

A localhost database:

# isql-fb -u sysdba -p masterkey employee
SQL> SHOW USERS;
# SYSDBA

SQL> SHOW VERSION;
ISQL Version: LI-V2.5.5.26952 Firebird 2.5
Server version:
Firebird/linux AMD64 (access method), version "LI-V2.5.5.26952 Firebird 2.5"
Firebird/linux AMD64 (remote server), version "LI-V2.5.5.26952 Firebird 2.5/tcp (example.com)/P12"
Firebird/linux AMD64 (remote interface), version "LI-V2.5.5.26952 Firebird 2.5/tcp (example.com)/P12" 
on disk structure version 11.2

SQL> QUIT;
#

A remote host database, with aliases mapping:

# isql-fb -u sysdba -p masterkey example.com:employee

Creating a database

There is no need to connect any database to create a new database into server. When CREATE DATABASE is entered, intrepreter will instantly connect to the new database.

# isql-fb -u sysdba -p masterkey
SQL> CREATE DATABASE '/var/lib/firebird/data/foobar.fdb' PAGE_SIZE 8192 DEFAULT CHARACTER SET UTF8;

SQL> SHOW DATABASE;
Database: /var/lib/firebird/data/foobar.fdb
        Owner: SYSDBA                         
PAGE_SIZE 8192
Number of DB pages allocated = 164
Sweep interval = 20000
Forced Writes are ON
Transaction - oldest = 1
Transaction - oldest active = 2
Transaction - oldest snapshot = 2
Transaction - Next = 6
ODS = 11.2
Default Character set: UTF8
SQL>

Removing a database

Removing a database, deleting all its content - must be done in connected state and can be done when being connected to the database being removed.

# isql-fb -u sysdba -p masterkey
SQL>
SQL> CONNECT foobar;
SQL> DROP DATABASE;
SQL> quit
CON>

This also removes the actual database file from filesystem.

Backup

# gbak -b -v -user sysdba -pass masterkey example.com:foobar /var/tmp/foobar.fbk

Backups are in binary format, not text.

Filesystem and locations

Documentation says that databases should not be moved or backed up as files, but as binary backups with gbak tool.

• /etc/firebird/firebird.conf master configuration file.
• /etc/firebird/aliases.conf mappings between user friendly names and database files.
• /var/lib/firebird/data directory contains database per *.fdb file, like default employee.fdb.
• /var/lib/firebird/system/security2.fdb contains users.

If databases are relocated with alises file, user information still resides in security2.fdb that is probably under /var unless otherwise specificed.

Finally

Change that sysdba password.

External links

• firebirdsql.org
• firebirdfaq.org
• wikipedia.org - Firebird